Mysql查询性能优化-善用Explain语句

ゝ一世哀愁。 2022-07-13 13:54 287阅读 0赞

在项目中验证sql语句执行效率的时候最直观的方式就是查看其执行时间，但是在线上环境中如果不慎运行一个效率十分低下的sql导致数据库down掉了，那就悲剧了。并且只看执行时间，并无法有效的定位影响效率的原因。因此通过EXPLAIN命令查看SQL语句的执行计划，根据执行计划可以对SQL进行相应的优化。理解SQL执行计划各个字段的含义这时候显得十分重要。

如下图

EXPLAIN SELECT COUNT(\*) FROM blog ![20161129135012474][]

![Image 1][]

这是一个简单的sql的执行计划，可以看到其包含十个字段来描述这个执行计划。其中比较重要的字段有select\_type、Type、ref、Extra

下面为更好的理解执行计划，这里对每个字段进行相应的解释。

**1.id**

一个复杂的sql会生成多执行计划如下图：

![Image 1][]

EXPLAIN SELECT COUNT(\*) FROM (SELECT id from blog where id = 1) a ![20161129135151942][]

图1

可以看到含有子查询的sql产生了两条记录，分别表示该条sql的执行顺序。

**2.select\_type**

查询类型，有如下几种值

2.1 simple  表示简单查询，没有子查询和union 如图1所示

2.2 primary 最外边的select，在有子查询的情况下最外边的select查询就是这种类型如图2所示

2.3 union  union语句的后一个语句执行的时候为该类型如图2.1所示    
EXPLAIN SELECT COUNT(\*) FROM  blog UNION SELECT id from blog where id = 1 ![20161129135217427][]

图2.1

![Image 1][]

2.4 union result union语句的结果  如图2.1所示。

。。。。。。

**3.table**

使用的表名

**4.type**

连接类型，十分重要的字段   按照代表的效果由最优到最差情况进行介绍。

4.1、system 表仅有一行   const的特例。

4.2、const 最多匹配一行并且使用primarykey 或 unique索引，才会是const。

EXPLAIN SELECT \* FROM  blog where id =1 ![20161129135244178][] ![Image 1][]

下面这种情况搜索到一条数据但是没有用到主键或索引  所以type不是const  关于all的含义将在下文介绍

EXPLAIN SELECT \* FROM blog LIMIT 1 ![20161129135322928][]

![Image 1][]

4.3、eq\_ref

根据mysql官方手册的解释： "对于每个来自于前面的表的行组合，从该表中读取一行。这可能是除了const类型最好的联接类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY" 。eq\_ref可以用于使用=比较带索引的列。看下面的语句

EXPLAIN SELECT \* FROM blog , author where blog.blog\_author\_id = author.id ![20161129135346413][]

![Image 1][]

EXPLAIN SELECT \* FROM author,blog where blog\_author\_id = author.id ![20161129135404663][]

4.4、ref

对于所有取自前表的行组合，所有的匹配项都是通过索引读出的。 也可以理解为连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。

如下图。

EXPLAIN SELECT \* FROM blog  where blog\_author\_id = 2  其中blog\_author\_id有索引 ![20161129135457648][]

![Image 1][]

写到这里 相信大家还是对以上各种类型的解释有点迷迷糊糊。下面看一个等值连接的例子，会加深对索引和以上解释的理解。

SELECT \* FROM author,blog where  author.id=blog.blog\_author\_id  and author.id = 2

这条语句查出作者2发表的所有博客。id为author表主键，mysql会自动为主键创建唯一索引。而blog\_author\_id是blog一个普通字段，如果对其加个索引看一下运行的效果。 ![20161129135530491][]

![Image 1][]

先观察下一下这个执行计划，可以看出mysql对sql语句的执行已经做了很好的优化.这里可以看到其中一条优化规则，先做选择操作缩小连接操作的集合维度，再做连接操作，详细可查看mysql生成执行计划的优化策略。

解释一下：第一行代表mysql生成的第一个执行计划。即select \* from author where id= 2. 由于id是author表的主键，且表包含多条数据但仅命中一行，所以其类型为const。

第二行:对于blog表中auhorid为2的记录有多个，且是通过索引读出的。满足ref的条件。

自然而然 如果把blog表中的author\_id所以去除掉，则其类型应该不会再是ref。让我们来验证这个想法。

drop index author\_id on blog

再来执行以下查询语句 ![20161129135555869][]

![Image 1][]

可以看到type类型变为ALL了，这种类型的效率非常慢，同时你可以看到rows这一行数据也发生了变化。由于没有索引，所以需要扫描全表。详细关于ALL类型和rows列的含义将在下文中介绍。

下面接着看下一个类型。

4.5 ref\_or\_null    
        如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。   或解释为MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。

这种类型没搞明白  做实验都没出现这种类型  希望各位朋友给个例子。

**但是上面说的这五种类型是属于总体来说效果很不错的了。如果能满足以上类型的查询 基本上不需要太大的优化、**

**下面介绍效率较低几种类型  当出现以下几种类型的查询  就要好好考虑做做优化了**。

4.6  index\_merge    
         该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key\_len包含了使用的索引的最长的关键元素。查看下面这条sql

EXPLAIN SELECT \* FROM blog where blog\_title = "first" and blog\_author\_id = 1 ![20161129135644308][]

![Image 1][]

大致解释一下索引和并优化的概念，这时mysql针对sql使用多个索引进行查询时的优化方案。通俗的说就是mysql会把同一个表的多个索引扫描的结果进行合并。详细的去看看相关博客。

解释一下上述的例子，分别对blog\_title和authorid创建索引，这时用and查询满足以上两种条件的结果，如果查到一条的话它就是ref  但是如果匹配多条的话他就会进行索引合并。

4.7unique\_subquery

顾名思义   subquery可以看出这种类型跟子查询有关系，同时大家知道子查询在mysql中是十分不建议使用的一种查询方式，当遇到子查询时多思考如果通过连接查询来优化。尽可能少的使用IN语句。

在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary\_key FROM single\_table WHERE some\_expr)

EXPLAIN SELECT \* FROM blog where blog\_author\_id in (SELECT id from author where author\_name = "test1")

![20161129135703414][]

即使对authorname创建索引也是相同的执行计划

![Image 1][]

对于这种情况你可以将其改写成一个left join语句   
        SELECT blog.\* FROM blog LEFT JOIN author ON blog\_author\_id = author.id WHERE author\_name = "test1"  
         一样的执行结果  但是执行计划就是不同的如下图

![20161129135727497][]

![Image 1][]

可见这种查询就是用到了索引。效率可想而知。

4.8 index\_subquery

在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique\_subquery 类似,但是查询的是非唯一性索引:  
           value IN (SELECT key\_column FROM single\_table WHERE some\_expr)

EXPLAIN SELECT \* FROM author where id in ( SELECT blog\_author\_id from blog where blog\_title = "secend")

![20161129135836883][]

![Image 1][]

同样的要尽量避免使用这种方式的查询。

4.9  range

顾名思义，range意思就是范围。因此可以解释为：只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。

这种类型解释的很清楚了 稍微举个栗子大家看看吧。

EXPLAIN SELECT \* FROM blog where id > 2  
![20161129135854696][]

![Image 1][]

4.10 index

这种类型的意思也十分明显，查询过程中使用到了索引。解释为： 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。举个栗子

EXPLAIN SELECT \* FROM blog ORDER BY id ![20161129135940891][]

![Image 1][]

4.11 all

最坏的情况,从头到尾全表扫描 。性能最差的一种类型  遇到这种类型 你得想想  为什么不建索引！  为什么 不改造 sql！   改造sql也是为了让mysql运行的时候尽可能的使用到索引，  这里又牵扯出一个问题  如何建索引 数据库维护索引也是一件十分费时费力的事情。详细内容自行查询 本人还未总结~~~

这个就不举例子了   大家看看上边的例子 有很多连接查询计划中都存在all类型，顺便想想如何优化。

解释到这里大家对执行计划所代表的效率含义基本上有个认识了，现在对后面的字段进行介绍。

**5.possible\_keys**

很明显了  它的意思就是有可能使用到的索引。

**6.key**

MySQL 实际从 possible\_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引。

**7.key\_length**

使用索引的长度。当然在不失精度的情况下  长度越小越好！

**8.ref**

显示索引的那一列被引用到了。

**9.rows**

MYSQL 认为必须检查的用来返回请求数据的行数，越大越不好。说明没有很好的使用到索引。

**10 Extra**

表示mysql解决查询的详细信息。

10.1 Using Index

表示使用到索引

10.2  using filesort

表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序” 常见于 order by 和group by语句中。 注意如果你对排序列创建索引mysql仍然会提示你使用的是filesort，所以对于这个字段应该有自己的判断。  
         EXPLAIN SELECT \* FROM blog order by blog\_title ![20161129140042681][]

![Image 1][]

10.3 Using  temporary

表示进行查询时使用到临时表。当使用到临时表时，表示sql的效率需要进行相应的优化了。这种类型可能会在连接排序查询中出现。  
        为了便于理解先举一个例子。  
EXPLAIN SELECT \* FROM author,blog where  author.id=blog.blog\_author\_id  and blog.blog\_title="first"  order by author.id desc  
![20161129135045427][]

这条语句是要查出写first这篇博客的博主信息，并按用户id排序。

先来看看mysql连接查询算法 Nested Loop Join  通过驱动表的结果集，一条一条的按照连接条件查询下个表中的记录。

这里出现了一个名词 驱动表

驱动表定义：

1.当连接条件确定时，查询条件筛选后记录少的为驱动表。

2.当连接条件不确定时，行数少的表为驱动表。

按照上述定义，由于blog\_tiltle经过筛选条件后查询得到的记录数为2，而未对author表进行条件过滤，因此该sql的驱动表为blog。

将过滤后的blog表的记录一条条的对author表查询，而后合并，这时需要按照author表的id字段进行排序，因此需要对合并结果(临时表)进行排序。

如果按照驱动表排序，则可以直接排序而无需临时表。

EXPLAIN SELECT \* FROM author,blog where  author.id=blog.blog\_author\_id  and blog.blog\_title="first"  order by blog.id desc

![20161129143112776][]

[20161129135012474]: /images/20220713/d4e027db35744a918c49c09a50b0e024.png
[Image 1]: 
[20161129135151942]: /images/20220713/0df892f1dd894b82bb87921399e0842f.png
[20161129135217427]: /images/20220713/d4f169c5bdd84681a82df9298a98e53f.png
[20161129135244178]: https://img-blog.csdn.net/20161129135244178
[20161129135322928]: /images/20220713/4591d9049a3146239221ea878cccfc6c.png
[20161129135346413]: /images/20220713/bdb50e7de0c44d7c8b6aae65941b46cf.png
[20161129135404663]: /images/20220713/0be533e495c444c98e92efed0b2fcb34.png
[20161129135457648]: /images/20220713/841262202fd84c6a8a25a88f8d393ce6.png
[20161129135530491]: /images/20220713/b9d0a61c2b7e4d6688b703256ff6b787.png
[20161129135555869]: /images/20220713/b9e8feb23e274616a180ee4e4fc581db.png
[20161129135644308]: /images/20220713/49c5eff51822494090ea3a93f3df1415.png
[20161129135703414]: /images/20220713/f9904b7c25d546dd9aa839be0688df7b.png
[20161129135727497]: /images/20220713/fbddaf34fe244d299d305598beceb40a.png
[20161129135836883]: /images/20220713/c04f76a5f6d14717be4b5adb76911eea.png
[20161129135854696]: /images/20220713/069e4487549d49b588a67771547a1fd4.png
[20161129135940891]: /images/20220713/eee6703831114c0aab10a642a7e3c095.png
[20161129140042681]: /images/20220713/5eeeb28f65be477f8546dda268b18c1a.png
[20161129135045427]: /images/20220713/fac383adb475478bb00a9d5c9ee3f2dc.png
[20161129143112776]: /images/20220713/62a34fe724d340d084add4c30bcb839b.png