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

ゝ一世哀愁。 2022-07-13 13:54 346阅读 0赞 
  1.     在项目中验证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

查询类型，有如下几种值

  1. 2.1 simple  表示简单查询，没有子查询和union 如图1所示
  3. 2.2 primary 最外边的select，在有子查询的情况下最外边的select查询就是这种类型如图2所示
  5. 2.3 union  union语句的后一个语句执行的时候为该类型如图2.1所示

EXPLAIN SELECT COUNT(*) FROM blog UNION SELECT id from blog where id = 1 20161129135217427

图2.1

![Image 1][]

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

3.table

  1. 使用的表名

4.type

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

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

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

  1.    EXPLAIN SELECT \* FROM  blog where id =1 ![20161129135244178][] ![Image 1][]
  3.    下面这种情况搜索到一条数据但是没有用到主键或索引  所以type不是const  关于all的含义将在下文介绍
  5.    EXPLAIN SELECT \* FROM blog LIMIT 1 ![20161129135322928][]

![Image 1][]

  1. 4.3、eq\_ref
  3.     根据mysql官方手册的解释： "对于每个来自于前面的表的行组合，从该表中读取一行。这可能是除了const类型最好的联接类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY" 。eq\_ref可以用于使用=比较带索引的列。看下面的语句
  5.     EXPLAIN SELECT \* FROM blog , author where blog.blog\_author\_id = author.id ![20161129135346413][]

![Image 1][]

  1.     EXPLAIN SELECT \* FROM author,blog where blog\_author\_id = author.id ![20161129135404663][]
  3. 4.4、ref
  5.      对于所有取自前表的行组合，所有的匹配项都是通过索引读出的。 也可以理解为连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。 
  7.     如下图。
  9.     EXPLAIN SELECT \* FROM blog  where blog\_author\_id = 2  其中blog\_author\_id有索引 ![20161129135457648][]

![Image 1][]

  1.     写到这里 相信大家还是对以上各种类型的解释有点迷迷糊糊。下面看一个等值连接的例子，会加深对索引和以上解释的理解。
  3.     SELECT \* FROM author,blog where  author.id=blog.blog\_author\_id  and author.id = 2
  5.     这条语句查出作者2发表的所有博客。id为author表主键，mysql会自动为主键创建唯一索引。而blog\_author\_id是blog一个普通字段，如果对其加个索引看一下运行的效果。 ![20161129135530491][]

![Image 1][]

  1.     先观察下一下这个执行计划，可以看出mysql对sql语句的执行已经做了很好的优化.这里可以看到其中一条优化规则，先做选择操作缩小连接操作的集合维度，再做连接操作，详细可查看mysql生成执行计划的优化策略。
  3.     解释一下：第一行代表mysql生成的第一个执行计划。即select \* from author where id= 2. 由于id是author表的主键，且表包含多条数据但仅命中一行，所以其类型为const。
  5.     第二行:对于blog表中auhorid为2的记录有多个，且是通过索引读出的。满足ref的条件。
  7.     自然而然 如果把blog表中的author\_id所以去除掉，则其类型应该不会再是ref。让我们来验证这个想法。
  9.     drop index author\_id on blog
  11.     再来执行以下查询语句 ![20161129135555869][]

![Image 1][]

  1.     可以看到type类型变为ALL了，这种类型的效率非常慢，同时你可以看到rows这一行数据也发生了变化。由于没有索引，所以需要扫描全表。详细关于ALL类型和rows列的含义将在下文中介绍。
  3. 下面接着看下一个类型。
  5. 4.5 ref\_or\_null    
  6.     如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。   或解释为MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。

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

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

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

  1. 4.6  index\_merge    
  2.      该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key\_len包含了使用的索引的最长的关键元素。查看下面这条sql
  4.      EXPLAIN SELECT \* FROM blog where blog\_title = "first" and blog\_author\_id = 1 ![20161129135644308][]

![Image 1][]

  1.     大致解释一下索引和并优化的概念，这时mysql针对sql使用多个索引进行查询时的优化方案。通俗的说就是mysql会把同一个表的多个索引扫描的结果进行合并。详细的去看看相关博客。
  3.     解释一下上述的例子，分别对blog\_title和authorid创建索引，这时用and查询满足以上两种条件的结果，如果查到一条的话它就是ref  但是如果匹配多条的话他就会进行索引合并。
  5. 4.7unique\_subquery
  7.       顾名思义   subquery可以看出这种类型跟子查询有关系，同时大家知道子查询在mysql中是十分不建议使用的一种查询方式，当遇到子查询时多思考如果通过连接查询来优化。尽可能少的使用IN语句。
  9.       在某些 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 

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

![Image 1][]

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

20161129135727497

![Image 1][]

  1.      可见这种查询就是用到了索引。效率可想而知。
  3. 4.8 index\_subquery 
  5.        在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique\_subquery 类似,但是查询的是非唯一性索引:  
  6.        value IN (SELECT key\_column FROM single\_table WHERE some\_expr)
  8.  EXPLAIN SELECT \* FROM author where id in ( SELECT blog\_author\_id from blog where blog\_title = "secend")

20161129135836883

![Image 1][]

  1.  同样的要尽量避免使用这种方式的查询。
  3. 4.9  range   
  5.     顾名思义，range意思就是范围。因此可以解释为：只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。
  7.     这种类型解释的很清楚了 稍微举个栗子大家看看吧。
  9.     EXPLAIN SELECT \* FROM blog where id > 2

20161129135854696

![Image 1][]

  1. 4.10 index
  3.     这种类型的意思也十分明显，查询过程中使用到了索引。解释为： 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。举个栗子
  5.     EXPLAIN SELECT \* FROM blog ORDER BY id ![20161129135940891][]

![Image 1][]

  1. 4.11 all
  3.    最坏的情况,从头到尾全表扫描 。性能最差的一种类型  遇到这种类型 你得想想  为什么不建索引！  为什么 不改造 sql！   改造sql也是为了让mysql运行的时候尽可能的使用到索引，  这里又牵扯出一个问题  如何建索引 数据库维护索引也是一件十分费时费力的事情。详细内容自行查询 本人还未总结~~~
  5.    这个就不举例子了   大家看看上边的例子 有很多连接查询计划中都存在all类型，顺便想想如何优化。

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

5.possible_keys

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

6.key

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

7.key_length

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

8.ref

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

9.rows

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

10 Extra

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

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

![Image 1][]

  1. 10.3 Using  temporary
  3.     表示进行查询时使用到临时表。当使用到临时表时，表示sql的效率需要进行相应的优化了。这种类型可能会在连接排序查询中出现。  
  4.     为了便于理解先举一个例子。

EXPLAIN SELECT * FROM author,blog where author.id=blog.blog_author_id and blog.blog_title=”first” order by author.id desc
20161129135045427 

  1.    这条语句是要查出写first这篇博客的博主信息，并按用户id排序。
  3.    先来看看mysql连接查询算法 Nested Loop Join  通过驱动表的结果集，一条一条的按照连接条件查询下个表中的记录。
  5.    这里出现了一个名词 驱动表
  7.    驱动表定义：
  9.    1.当连接条件确定时，查询条件筛选后记录少的为驱动表。
  11.    2.当连接条件不确定时，行数少的表为驱动表。
  13.   按照上述定义，由于blog\_tiltle经过筛选条件后查询得到的记录数为2，而未对author表进行条件过滤，因此该sql的驱动表为blog。
  15.   将过滤后的blog表的记录一条条的对author表查询，而后合并，这时需要按照author表的id字段进行排序，因此需要对合并结果(临时表)进行排序。
  17.   如果按照驱动表排序，则可以直接排序而无需临时表。
  19.   EXPLAIN SELECT \* FROM author,blog where  author.id=blog.blog\_author\_id  and blog.blog\_title="first"  order by blog.id desc

20161129143112776

[Image 1]:

发表评论

表情：
评论列表 （有 0 条评论，346人围观）

还没有评论，来说两句吧...

相关阅读