Mysql数据库索引优化1—explain命令

港控/mmm° 2022-12-17 15:56 164阅读 0赞

平凡也就两个字: 懒和惰;  
成功也就两个字: 苦和勤;  
优秀也就两个字: 你和我。  
跟着我从0学习JAVA、spring全家桶和linux运维等知识，带你从懵懂少年走向人生巅峰，迎娶白富美！  
关注微信公众号【**IT特靠谱** 】，每天都会分享技术心得~

# Mysql数据库索引优化1—explain命令 #

## 1 数据准备 ##

创建数据库表：user\_t，并插入图中4条测试数据（Tips: 若需要学习mysql相关操作，请关注微信公众号"IT特靠谱"后给你教程）。

![20201024131810354.png][]

## 2 explain命令 ##

### 2.1 explain命令简介 ###

在工作中有时会遇到个别慢查询的sql，严重时甚至会导致锁表或者mysql服务宕机。为了分析出sql响应慢的原因，我们可以通过mysql的explain这个命令来查看sql语句的执行计划，查看该SQL语句有没有使用索引，有没有做全表扫描等，从而对sql语句或数据库表进行优化。

下面的示例图分别为有无explain命令的sql语句执行情况。

普通查询数据sql命令：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX01vc3Q_size_16_color_FFFFFF_t_70][]

explain命令查看sql执行计划：

![2020102413250781.png][]

从上图可以看到，explain出来的执行计划信息有12列，分别是id、select\_type、table、partitions、type、possible\_keys、key、key\_len、ref、rows、filtered和Extra。下面对这12列数据进行详解。

### 2.2 explain信息详解 ###

（1） id：选择标识符。

SELECT识别符。这是SELECT的查询序列号。我的理解是SQL执行的顺序的标识。

1）id相同时，执行顺序由上至下；

2）如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；

3）id如果相同，可以认为是一组，相同组内从上往下顺序执行；不同组中id值越大，优先级越高，越先执行。

（2）select\_type:表示查询的类型。

1）SIMPLE(简单SELECT，不使用UNION或子查询等)；

2）PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)；

3） UNION(UNION中的第二个或后面的SELECT语句)；

4） DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)；

5）UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)；

6）SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)；

7）DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)；

8）DERIVED(派生表的SELECT, FROM子句的子查询)

9）UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

（3）table:输出结果集的表

显示执行这一步查询时所访问数据库中表名称，有时不是真实的表名字，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。如果是尖括号括起来的<subquery N>，这个表示子查询结果被物化，之后子查询结果可以被复用

（4）partitions:匹配的分区（数据所在表的分区）

该列显示的为分区表数据被命中的分区情况。非分区表该字段为空（null）。

（5）type:表示表的连接类型

表示在表中找到所需行数据的方式，又称“访问类型”。

查询速度依次从好到差：system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL。除了ALL之外，其他的type都可以使用到索引，除了index\_merge之外，其他的type只可以用到一个索引！  
1）system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index。  
2）const：使用唯一索引或者主键，返回记录一定是1行记录。其他数据库也叫做唯一索引扫描。  
3）eq\_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq\_ref。  
4）ref：不像eq\_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。  
5）fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。  
6）ref\_or\_null：与ref方法类似，只是增加了null值的比较。实际用的不多。  
7）index\_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref\_or\_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range。  
8）unique\_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。  
9）index\_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。  
10）range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。  
11）index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

在工作中，我们尽量优化索引使得sql查询数据时访问类型（type）在range以上，最差也得时range！！

（6）possible\_keys:表示查询时，可能使用的索引

查询（select）可能使用到的索引都会在这里列出来。查询条件（where）涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）。  
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果没有走索引，那么我们就需要建一个索引来提高查询效率！

（7）key:表示实际使用的索引

查询真正使用到的索引，必然包含在possible\_keys中。select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible\_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

（8）key\_len:索引字段的长度

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的）。在不损失精确性的情况下，长度越短越好 ！

（9）ref:列与索引的比较

如果是使用的常数等值查询，这里会显示const；如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

（10）rows:扫描出的行数(估算的行数)

这里是执行计划中估算的扫描行数，不是精确值。表示MySQL根据表统计信息及索引选用情况，估算的查询到我们想要的记录所需要扫描的行数。

（11）filtered:按表条件过滤的行百分比

表示返回结果的行数占需扫描读取行数的百分比(注意是百分比，不是具体记录数)，Filtered列的值越大越好，Filtered列的值依赖于统计信息。尽量优化索引提高Filtered百分比值！

（12）Extra:执行情况的描述和说明

对于extra列，官网上有这样一段话：

If you want to make your queries as fast as possible, look out for Extra 
    column values of Using filesort and Using temporary, or, in JSON-formatted 
    EXPLAINoutput, for using_filesort and using_temporary_table properties equal
    to true.

大概的意思就是说，如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

这个列可以显示的信息非常多，有几十种，常用的有：  
1）distinct：表示在select部分使用了distinc关键字。  
2）no tables used：不带from字句的查询或者From dual查询。  
3）using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。  
4）using index：表示直接通过索引就可以获取查询的数据。  
5）using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。  
6）using sort\_union，using\_union，using intersect，using sort\_intersection：  
　　using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集。  
　　using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集。  
　　using sort\_union和using sort\_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。  
7）using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used\_tmp\_table，used\_tmp\_disk\_table才能看出来。  
8）using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition。  
9）Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。  
K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个。

## 3 总结 ##

1） EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；  
2）EXPLAIN不考虑各种Cache；  
3）EXPLAIN不能显示MySQL在执行查询时所作的优化工作；  
4）部分统计信息是估算的，并非精确值；  
5）EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

下一章将详解不走mysql索引查询的几种情况，以及sql案例，动动你的小手指，关注微信公众号支持一下博主吧~

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX01vc3Q_size_16_color_FFFFFF_t_70 1][]

[20201024131810354.png]: /images/20221122/d1a133e0e3ea455da105445d424fdc08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX01vc3Q_size_16_color_FFFFFF_t_70]: /images/20221122/3e6d13bce6f84e53949128901550cbc4.png
[2020102413250781.png]: /images/20221122/47cf26cddc5d40938547bbe02ccf1a0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX01vc3Q_size_16_color_FFFFFF_t_70 1]: /images/20221122/9d2db4eb47bd44f89e2c4bbca9d9fc1e.png