覆盖索引&最左前缀
2018/10/8大约 4 分钟
说明
之所以把覆盖索引和最左前缀放一起讲，是因为我在验证最左前缀原则时，无意间使用了索引覆盖，导致验证结果不符合我的预期，造成对最左前缀理解差点出现问题。
索引覆盖参考
https://juejin.cn/post/6844903967365791752
环境准备
mysql：8.0
create table user
(
id bigint unsigned auto_increment
primary key,
name varchar(16) null,
age int null,
address varchar(16) null,
create_by varchar(16) null,
create_at datetime null
);
--组合索引
create index user_name_age_address_index
on user (name, age, address);索引覆盖实验
开始实验验证索引覆盖以及最左前缀原则。
实验1
mysql> explain select name,age from user where age = 3 and address = 'hubei';
+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | user | NULL | index | user_name_age_address_index | user_name_age_address_index | 139 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)为了测试最左前缀原则，这里我故意把查询条件跳过了组合索引中的第一个字段name，结果发现explain后居然还有用索引，原因就是我查询的字段是name,age，这两个字段都在组合索引里，触发了索引覆盖，无需回表，所以看起来像是没有遵守“索引最左匹配原则”。
实验2
mysql> explain select name,age,create_at from user where age = 3 and address = 'hubei';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)把实验1的查询字段稍微调整一下，额外查询一个没有被索引覆盖的字段create_at，发现这次就没有走索引了，其实主要是因为查询字段包含了非索引字段。
实验3
#使用了索引覆盖，name,age都在索引中，所以走了索引
mysql> explain select name,age from user;
+----+-------------+-------+------------+-------+---------------+-----------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | user_name_age_address_index | 139 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 没有使用索引覆盖，只查询create_at发现没有走索引
mysql> explain select create_at from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)最左前缀
最左匹配原则的定义
组合索引（name,age,address）按照顺序，最左前缀原则指的是查询条件必须从索引的最左侧字段开始，并且不能跳过中间的字段。比如，索引是name、age、address，那么有效的查询条件需要包含name，或者name和age，或者全部三个字段。如果跳过中间的字段，比如只有name和address，那么address就不能利用索引进行查找，只能利用到name的部分。
实验
⚠️验证“最左前缀“一定要记得查询的字段不能全部都被包含在索引里面的字段，否则会触发索引覆盖，无法验证”最左前缀“，可以只查询一个非索引字段，或者非索引字段+其他索引字段。
以下例子是匹配最左前缀原则，可以看到结果走了索引，区别就是len不一样，第一个例子name走了索引，第二个例子使用了name和address跳过了age，所以可以看到两个例子的len是一样的，都是67，那是因为第二个例子其实也是只有name索引生效了，第三个例子三个字段都走了索引，len就变为了139
mysql> explain select create_at from user where name='tom' ;
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | user_name_age_address_index | user_name_age_address_index | 67 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select create_at from user where name='tom' and address='hubei' ; #67
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | user_name_age_address_index | user_name_age_address_index | 67 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select create_at from user where name='33' and age = 3 and address = 'hubei'; #139
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | user_name_age_address_index | user_name_age_address_index | 139 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)以下例子验证不匹配最左前缀原则，都没有用第一个索引name，所以两个例子都没有走索引
mysql> explain select create_at from user where age=3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select create_at from user where age=3 and address='hubei' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)