《MySQL高级》索引分析和优化笔记（上）

算法听星使
• 阅读 1966
学习《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 = 3Y，使用到 a
where a = 3 and b = 5Y，使用到 a，b
where a = 3 and b = 5 and c = 4Y，使用到 a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N
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 = 4Y，使用到了a，b，c （与大于小于号的范围不同，这里可以使用到索引）
where a = 3 and b like '%kk' and c = 4Y，只用到了a
where a = 3 and b like '%kk%' and c = 4Y，使用到了a
where a = 3 and b like 'k%kk%' and c = 4Y，使用到了a，b，c

优化口诀总结

全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖索引不写星;
不等空值还有or，索引失效要少用;

[参考]

B站《MySQL高级》44.索引面试题分析
B站《MySQL高级》45.索引优化答疑补充和总结口诀
MySQL高级篇（高阳）建表sql语句大全

mysql
点赞
收藏
评论区
推荐文章
Peter20 Peter20
•
4年前
什么是索引？Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创
1851
0
0
Easter64 Easter64
•
4年前
MySQL语句优化
在MySQL数据库怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及orderby涉及的列上建立索引。　　建立索引不是建的越多越好，原则是：　　第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及
1470
0
0
Wesley13 Wesley13
•
3年前
mysql监控
服务器监控　　查看mysql性能SQL语句　　　　mysql的基本指令　　　　SHOW INDEX FROM<table：用于查看数据库表上的索引技术　　　　SHOWPLUGINS：查看mysql的插件，例如搜索引擎　　　　SHOW\FULL\ PROCESSLIST
1035
0
0
Wesley13 Wesley13
•
3年前
MySQL千万级别优化·中
MySQL千万级别的查询优化手段·中单列索引（假设在v\_record表中存在id列的索引）1、WHERE条件使用​EXPLAINSELECT\FROMv\_recordWHEREid2​结论：利用索引进行回表查询2、SELECT字段使用
990
0
0
Wesley13 Wesley13
•
3年前
MySQL索引的索引长度问题
MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。在MyISAM表中，创建组合索引时，创建的索引长度不能超过1000，注意这里索引的长度的计算是根据表字段设定的长度来标量的，例如：createtabletest(idint,name1varchar(300),name2varchar(300),nam
916
0
0
Wesley13 Wesley13
•
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景：表结构：主键（非自增）contentCode（varchar），过滤条件列为updateTime（timeStamp），已经为timestamp建立索引。搜索sql为：SELECTFROMmy_hello_tableWHEREupdat
1071
0
0
Wesley13 Wesley13
•
3年前
MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗？
看面试题的时候，总能看到MySQL在什么情况下用不上索引，如下：MySQL的WHERE子句中包含ISNULL、ISNOTNULL、!这些条件时便不能使用索引查询，只能使用全表扫描。不耽误大家时间，告诉大家结论：<fontcolor"5CACEE"MySQL中决定使不使用某个索引执行查
831
0
0
Wesley13 Wesley13
•
3年前
SQL语句优化
SQL语句优化规范：1\.使用mysqlexplain对sql执行效率进行检测，explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。1)使用方法：在select语句前加上explain即可2)explain分析结果形式如下：table|type
998
0
0
Wesley13 Wesley13
•
3年前
mySql索引优化分析
MySQL索引优化分析为什么你写的sql查询慢？为什么你建的索引常失效？通过本章内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。还在等啥子？撸起袖子就是干！案例分析我们先
911
0
0
Wesley13 Wesley13
•
3年前
MySQL 的索引是什么？怎么优化？
索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。一、导致SQL执行慢的原因
728
0
0
Wesley13 Wesley13
•
3年前
MySQL 什么是索引？
_该文为《MySQL实战45讲》的学习笔记，感谢查看，如有错误，欢迎指正_<br<fontcolorblue一、索引简介</font<fontcolorred索引就类似书本的目录，作用就是方便我们更加快速的查找到想要的数据。</font索引的实现方式比较多，常见的有哈希表，有序数组，
723
0
0
算法听星使
算法听星使
Lv1
不再提及只是遗憾失言于你没能安定至终
文章
5
粉丝
0
获赞
0
热门文章
提升网站页面速度的14条最佳实践（一）
微信小游戏跳一跳Node版本 - 半自动版
Spring Cloud 配置中心客户端读取配置
经理、总监、VP、CTO，如何通过CEO思考问题的方式，来提升自己