《MySQL高级》索引分析和优化笔记(上)
学习《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语句大全