【MySQL】索引性能分析-demo

Bertha 。 2022-05-22 12:42 232阅读 0赞

# 前言 #

通过使用Explain分析，可以帮助选择更好的索引和写出更优化的查询语句。

# Explain列解释 #

id:select的查询序列号，表示查询的顺序

select\_type:显示对应的查询是简单还是复杂SELECT

table:显示这一行数据是关于哪张表的

type:显示连接使用了何种类型

possible\_keys:可能应用再这张表中的索引，一个或多个，但是不一定被查询实际使用。

key:实际使用的索引，如果为null,表示没有使用索引，查询中若是用来覆盖索引，则该索引仅出现在key列表中

key\_len:表示索引中 使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好。

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

Extra:包含不适合在其他列显示但十分重要的额外信息。在这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢。

# SQL脚本 #

CREATE TABLE `test03` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` char(10) DEFAULT NULL,
      `c2` char(10) DEFAULT NULL,
      `c3` char(10) DEFAULT NULL,
      `c4` char(10) DEFAULT NULL,
      `c5` char(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `test03` VALUES ('1', 'a1', 'a2', 'a3', 'a4', 'a5');
    INSERT INTO `test03` VALUES ('2', 'b1', 'b2', 'b3', 'b4', 'b5');
    INSERT INTO `test03` VALUES ('3', 'c1', 'c2', 'c3', 'c4', 'c5');
    INSERT INTO `test03` VALUES ('4', 'd1', 'd2', 'd3', 'd4', 'd5');
    INSERT INTO `test03` VALUES ('5', 'e1', 'e2', 'e3', 'e4', 'e5');

创建复合索引

CREATE index idx_test03_c1234 on test03(c1,c2,c3,c4);

查看索引使用情况

1.explain select * from test03 where c1='a1';

![70][]

2.explain select * from test03 where c1='a1'and c2='c2';

![70 1][]

3.explain select * from test03 where c1='a1'and c2='a2' and c3='a3';

![70 2][]

4.explain select * from test03 where c1='a1'and c2='a2' and c3='a3' and c4='a4';

![70 3][]

ref里的值个数表示用到了几个值的索引

5.explain select * from test03 where c1='a1'and c2='a2' and c4='a4'and c3='a3';

![70 4][]

6.explain select * from test03 where c4='a4'and c3='a3'and c2='a2' and c1='a1';

![70 5][]

4、5、6这三个语句使用的索引个数都是4个，全部使用的时候和顺序无关，最好按照索引的创建顺序使用

7.explain select * from test03 where c1='a1' and c2='a2'and c3>'a3'and  c4='a4';

![70 6][]

8.explain select * from test03 where c1='a1' and c2='a2' and  c4>'a4' and c3='a3';

![70 7][]

9.explain select * from test03 where c1='a1' and c2='a2' and  c4='a4' ORDER BY c3;

索引两大功能：排序和查找，c3在这里作用排序，没有统计到里面

![70 8][]

10.explain select * from test03 where c1='a1' and c2='a2' ORDER BY c3;

![70 9][]

11.explain select * from test03 where c1='a1' and c2='a2' ORDER BY c4;

![70 10][]

12.explain select * from test03 where c1='a1' and c5='a5' ORDER BY c2,c3;

只有c1一个字段索引，c2,c3用于排序，无filesort

![70 11][]

13.explain select * from test03 where c1='a1' and c5='a5' ORDER BY c3,c2;

出现filesort,我们建的索引是1234没有按照顺序来，3 2颠倒了

![70 12][]

14.explain select * from test03 where c1='a1' and c2='a2' ORDER BY c2,c3;

![70 13][]

15.explain select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c2,c3;

用c1、c2两个字段索引，但c2、c3用于排序无filesort

![70 14][]

16.explain select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c3,c2;

已有常量c2,c2,c3用于排序无filesort

![70 15][]

17.explain select * from test03 where c1='a1' and c5='a5' ORDER BY c3,c2;

![70 16][]

18.explain select * from test03 where c1='a1' and c4='a4' GROUP BY  c2,c3;

![70 17][]

19.explain select * from test03 where c1='a1' and c4='a4' GROUP BY c3,c2;

![70 18][]

# 优化口诀 #

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or,索引失效要少用；

VAR引号不可丢，SQL高级也不难！

[70]: /images/20220522/79b35d217c914af5a333cde26faf2907.png
[70 1]: /images/20220522/25d860dcaf854c0dae36d5882b2f5642.png
[70 2]: /images/20220522/5a76a8d31c2248d485ad3270375dd0b9.png
[70 3]: /images/20220522/0b7192c4820e4a5baee1ec6ea38be3b7.png
[70 4]: /images/20220522/5c78dcce1b3b4251affea1ccf9a42a9b.png
[70 5]: /images/20220522/0a251d8aa0cd48cbb89d9a4ca1d7f517.png
[70 6]: /images/20220522/ee7c682850734596a4a30f59bcd28c7c.png
[70 7]: /images/20220522/25455baf44a647d7a1786663b360cdeb.png
[70 8]: /images/20220522/1e4ccc2b9bfb443daabc199c5659c3d0.png
[70 9]: /images/20220522/59f88276c2e24d269e1d52b8fd619640.png
[70 10]: /images/20220522/1572abeb16834cea98aeaeb01b8e0841.png
[70 11]: /images/20220522/6443fa6df64041da9ffcf6c609015d33.png
[70 12]: /images/20220522/ff193139074944f085590c6d8ad10091.png
[70 13]: /images/20220522/a2d915639d244412aef2c891dc8c7cdd.png
[70 14]: /images/20220522/3777a0bcc4aa4c55ad77500a1588771d.png
[70 15]: /images/20220522/a2715ef80b854dc2be4761cc5d6bb55c.png
[70 16]: /images/20220522/4a4cb06609a54990b72f99fdc21a26c7.png
[70 17]: /images/20220522/60a4743840d84a63bc264c3c3f4f9e86.png
[70 18]: /images/20220522/1587c405029847dcacd8b5541a6a3b63.png