mysql索引覆盖

背景

在调试数据库时发现一个比较奇怪的现象：
有表table_a：

CREATE TABLE `table_a` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `A` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    `B` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    `C` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `A` (`A`,`B`,`C`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看执行计划：

explain select * from table_a where C='a';

+----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | table_a | NULL       | index | NULL          | A   | 909     | NULL |    1 |      100 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
1 row in set

现在是有走索引，只不过是扫描全部索引。 改动一下这个表，新增一个普通字段。

ALTER TABLE `table_a` ADD COLUMN `D`  varchar(100) NULL AFTER `C`;

再看一下执行计划：

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | table_a | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |      100 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

可以看到现在变成全表扫描了

原因

1. 先明白几个概念：
   • 主键索引：叶子节点保存数据。
   • 辅助索引：叶子节点保存主键值。
   • 数据查询过程：上述，如果使用辅助索引获取到数据就必须先从辅助索引取到主键的值，再使用主键的值去主键索引上查询，直到找到叶子节点上的数据返回。这个过程也叫回表。
   • 索引覆盖：如果辅助索引上已经存在我们需要的数据，那么引擎就不会拿主键回表取数据了。
2. 一开始我们的表中所有的字段都创建了索引，所以无论怎么查（包括 * 号查询），都属于是索引覆盖，但会有全索引扫描；当我们又向表中新增一个没有索引的普通字段，再用 * 号查询时，就会触发回表，所以就会有全表扫描。
3. 所以通常说的避免星号查询的原因之一也是为了避免回表。

扩展

1. 条件：C='a'

   mysql> explain select A,B,C from table_a where C='a';
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   | id | select_type | table   | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                    |
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   |  1 | SIMPLE      | table_a | NULL       | index | NULL          | A   | 909     | NULL |    1 |      100 | Using where; Using index |
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   1 row in set

2. 条件：B='b' and C='a'

   mysql> explain select A,B,C from table_a where B='b' and C='a';
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   | id | select_type | table   | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                    |
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   |  1 | SIMPLE      | table_a | NULL       | index | NULL          | A   | 909     | NULL |    1 |      100 | Using where; Using index |
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   1 row in set

3. 条件：A='b' and C='a'，左前缀原则，从A字段断点，C字段没走索引。

   mysql> explain select A,B,C from table_a where A='b' and C='a';
   +----+-------------+---------+------------+------+---------------+-----+---------+-------+------+----------+--------------------------+
   | id | select_type | table   | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra                    |
   +----+-------------+---------+------------+------+---------------+-----+---------+-------+------+----------+--------------------------+
   |  1 | SIMPLE      | table_a | NULL       | ref  | A             | A   | 303     | const |    1 |      100 | Using where; Using index |
   +----+-------------+---------+------------+------+---------------+-----+---------+-------+------+----------+--------------------------+
   1 row in set

4. 条件：B='b' and C='a'

   mysql> explain select A,B,C from table_a where B='b' and C='a';
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   | id | select_type | table   | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                    |
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   |  1 | SIMPLE      | table_a | NULL       | index | NULL          | A   | 909     | NULL |    1 |      100 | Using where; Using index |
   +----+-------------+---------+------------+-------+---------------+-----+---------+------+------+----------+--------------------------+
   1 row in set