0

生成表和索引的sql:

DROP SCHEMA IF EXISTS sakila;
CREATE SCHEMA sakila;
USE sakila;

CREATE TABLE payment (
  payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   
  -- 取消注释下面的列, 会对explain的结果产生影响
  -- customer_id SMALLINT UNSIGNED NOT NULL,

  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  constraint id_UNIQUE
        unique (payment_id)
);


alter table payment add index
idx_union_index_test (amount, payment_date, last_update);

explain:

explain select * from payment where
payment_date ='2006-02-15 22:12:32' 
and last_update = '2006-02-15 22:12:32';

疑问:
如果注释掉customer_id列, 执行explain发现是使用了索引 idx_union_index_test 的, 但是根据"匹配最左前缀"原则, 应该无法利用到索引的啊
如果取消注释customer_id列, 再次生成表和索引并explain, 这个时候发现type=ALL了

为什么会产生这样不同的结果呢?

2019-05-13 提问
3 个回答
0

你的表里有数据吗?

0

这不是最左匹配了,应该是覆盖索引的作用。我觉得是这样:
当注释掉customer_id列的时候,可以把idx_union_index_test作为覆盖索引,一般情况下覆盖索引的代价要比全表扫描要低,能选则选,但是在这种情况下,其实两者都一样;
当没注释customer_id列,首先用不到覆盖索引,需要回表,其次统计信息不确定,所以选择全表。

最后说一点,没有数据的查询,没有实际的统计信息,执行计划有可能是不准确的,优化器只能按照大部分情况去判断。

0

自己学习了下, 原因确实是覆盖索引, 不过还有其他因素.
覆盖索引的定义是: 如果一个索引包含了所有需要查询的字段的值, 就称之为覆盖索引.

  1. 当注释掉字段customer_id时, 表是有2个索引的, 一个是唯一索引id_UNIQUE, 一个是二级索引idx_union_index_test.
    又因为没有定义主键, 所以InnoDB聚簇索引会自动地选择唯一索引id_UNIQUE聚集数据
    而二级索引自动包含了聚簇索引的键值
    这样的结果就是: 二级索引idx_union_index_test包含了表的所有字段!
    所以即使sqlselect *查询所有字段, 也符合覆盖索引的定义, explain为using index
  2. 当没有注释到字段customer_id时, 没有任何索引包括二级索引idx_union_index_test能够包含表的所有字段, 也就不存在覆盖索引了

如果有不对的话, 求指教

撰写答案

推广链接