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

头像
Mr_houzi
    阅读 14 分钟
    English
    1
    学习《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
    Mr_houzi
    964 声望22 粉丝
    « 上一篇
    max_allowed_packet引起MySQL迁移丢失数据的问题
    下一篇 »
    《MySQL高级》索引分析和优化笔记（下）

    引用和评论

    推荐阅读
    头像
    令人困惑的 Go time.AddDate

    Mr_houzi阅读 2.5k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 519

    头像
    紧急避坑 | MySQL 含有下划线的数据库名在特殊情况下导致权限丢失

    爱可生开源社区赞 1阅读 327

    头像
    Mysql梳理1

    cashew赞 1阅读 2.2k

    头像
    MySQL 内存异常增长分析

    zbdba赞 1阅读 462

    头像
    mysql梳理2

    cashew赞 1阅读 1.6k

    头像
    关于建表字段是否该使用not null这个问题你怎么看?

    威哥爱编程赞 1阅读 407

    0 条评论
    得票最新