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

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

如下图

EXPLAIN SELECT COUNT(*) FROM blog

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

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

1.id

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

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

图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

图2.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

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

       EXPLAIN SELECT * FROM blog LIMIT 1

    4.3、eq_ref

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

        EXPLAIN SELECT * FROM blog , author where blog.blog_author_id = author.id

        EXPLAIN SELECT * FROM author,blog where blog_author_id = author.id

    4.4、ref

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

        如下图。

        EXPLAIN SELECT * FROM blog  where blog_author_id = 2  其中blog_author_id有索引

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

        SELECT * FROM author,blog where  author.id=blog.blog_author_id  and author.id = 2

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

        先观察下一下这个执行计划，可以看出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

        再来执行以下查询语句

        可以看到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

        大致解释一下索引和并优化的概念，这时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") 

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

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

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

    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")

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

    4.9  range   

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

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

        EXPLAIN SELECT * FROM blog where id > 2

    4.10 index

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

        EXPLAIN SELECT * FROM blog ORDER BY id

    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

    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

       这条语句是要查出写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