学习《MySQL 高级》高阳老师解说索引课程的笔记，本篇偏重对 where 索引的剖析
建表
运行环境：MySQL 5.7.32
# 建表
CREATE TABLE test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
select * from test03;
# 建设 c1, c2, c3, c4 复合索引
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;索引剖析
explain 跑在 MySQL 5.7.32 上
explain select * from test03 where c1 = 'a1';
explain select * from test03 where c1 = 'a1' and c2 = 'a2';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';1. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c3 = ‘a3’ and c4 = ‘a4’;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 164 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+全值匹配，查问用到了全副 4 个字段的索引（通过 key 字段判断应用了复合索引，type 类型为 ref，通过 ref 字段判断应用 4 个索引）
2. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c4 = ‘a4’ and c3 = ‘a3’;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 164 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+全值匹配，查问用到了全副 4 个字段的索引，阐明与字段书写程序无关，只有两头不间断即可。
3. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c3 > ‘a3’ and c4 = ‘a4’;
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | range | idx_test03_c1234 | idx_test03_c1234 | 123 | NULL | 1 | 20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+查问应用到了 c1、c2、c3 三个字段的索引（通过 key 字段判断应用了复合索引，type 类型为 range，通过索引长度 key_len 判断应用了 3 个索引），因为 c3 是范畴，c4 索引生效用不到（范畴之后全是生效），type 类型也从 ref 变为了 range。
4. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c4 > ‘a4’ and c3 = ‘a3’;
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | range | idx_test03_c1234 | idx_test03_c1234 | 164 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+查问应用了 c1、c2、c3、c4 四个索引（通过索引长度 key_len 判断的），因为 c4 是范畴，type 类型也从 ref 变为了 range。
5. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c4 = ‘a4’ order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 82 | const,const | 1 | 20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+ 查问应用到了 c1、c2 两个字段的索引；
排序时因为 c1、c2、c3，c3 索引能够用于排序，所以不会呈现文件排序（filesort）。
6. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 82 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+ 查问应用到了 c1、c2 两个字段的索引；
排序时因为 c1、c2、c3，c3 索引能够用于排序，所以不会呈现文件排序（filesort）。
7. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ order by c4;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 82 | const,const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+ 查问时应用了 c1、c2 两个字段的索引；
排序时，因为 c4 和 c1、c2 之间断掉了 c3，所以无奈应用索引排序，会呈现文件排序。
8.1 explain select * from test03 where c1 = ‘a1’ and c5 = ‘a5’ order by c2, c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 41 | const | 1 | 20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+ 查问时只用 c1 一个字段的索引；
排序时，c1、c2、c3 索引用于排序，所以无 filesort（文件排序）。
8.2 explain select * from test03 where c1 = ‘a1’ and c5 = ‘a5’ order by c3, c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 41 | const | 1 | 20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+ 查问时用 c1 的索引；
排序时 c3，c2 因为不合乎复合索引的程序，所以无奈利用索引排序，会呈现 filesort。
9. explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ order by c2, c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 82 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+ 查问时应用 c1、c2 字段的索引；
排序时，c1、c2、c3 字段索引用于排序，所以无 filesort。
10.1 explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c5 = ‘a5’ order by c2, c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 82 | const,const | 1 | 20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+ 查问时应用 c1、c2 字段的索引；
排序时，c1、c2、c3 字段索引用于排序，所以无 filesort。
10.2 explain select * from test03 where c1 = ‘a1’ and c2 = ‘a2’ and c5 = ‘a5’ order by c3, c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 82 | const,const | 1 | 20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+ 查问时应用 c1、c2 字段的索引；
排序时，c3 字段索引用于排序，c2 字段已为定值无需排序，所以无 filesort。
10.3 explain select * from test03 where c1 = ‘a1’ and c5 = ‘a5’ order by c3, c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 41 | const | 1 | 20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+ 查问时用到了 c1 索引；
排序时，c1、c3、c2 不是复合索引的程序，所以排序时会呈现 filesort。
11. explain select * from test03 where c1 = ‘a1’ and c4 = ‘a4’ group by c2, c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 41 | const | 1 | 20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+ 查问时用到了 c1 索引，c4 和 c1 之间断掉了，所以查问仅应用 c1 索引；
group by 分组基本上都是须要排序的，可按 order by 剖析。
排序时，c1、c2、c3 合乎复合索引的程序，所以排序时不会呈现 filesort，分组也不会呈现长期表。
12. explain select * from test03 where c1 = ‘a1’ and c4 = ‘a4’ group by c3, c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 41 | const | 1 | 20.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+ 查问时用到了 c1 索引，c4 和 c1 之间断掉了，所以查问仅应用 c1 索引；
排序时，c1、c3、c2 不是复合索引的程序，所以排序时会呈现 filesort，分组也会呈现长期表。
复合索引索引应用状况总结
| Where 语句 | 索引是否被应用到 |
|---|---|
| where a = 3 | Y，应用到 a |
| where a = 3 and b = 5 | Y，应用到 a，b |
| where a = 3 and b = 5 and c = 4 | Y，应用到 a，b，c |
| where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
| where a = 3 and c = 5 | 应用到 a，然而 c 不能够，因为 b 中间断了 |
| where a = 3 and b > 5 and c = 5 | 应用到 a 和 b，因为 c 不能用在范畴之后，b 断了（范畴前面全生效） |
| where a = 3 and b like ‘kk%’ and c = 4 | Y，应用到了 a，b，c（ 与大于小于号的范畴不同，这里能够应用到索引 ） |
| where a = 3 and b like ‘%kk’ and c = 4 | Y，只用到了 a |
| where a = 3 and b like ‘%kk%’ and c = 4 | Y，应用到了 a |
| where a = 3 and b like ‘k%kk%’ and c = 4 | Y，应用到了 a，b，c |
优化口诀总结
全值匹配我最爱，最左前缀要恪守;
带头大哥不能死，两头兄弟不能断;
索引列上少计算，范畴之后全生效;
LIKE 百分写最右，笼罩索引不写星;
不等空值还有 or，索引生效要少用;
[参考]
B 站《MySQL 高级》44. 索引面试题剖析
B 站《MySQL 高级》45. 索引优化答疑补充和总结口诀
MySQL 高级篇（高阳）建表 sql 语句大全