MySQL表的列数不同, 匹配最左前缀的执行效果也不同?

Question

生成表和索引的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了

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

Answer 1

你的表里有数据吗?

Answer 2

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

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

Answer 3

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

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能够包含表的所有字段, 也就不存在覆盖索引了

如果有不对的话, 求指教