mysql优化—explain

EXPLAIN：

      为用于SELECT语句中的每个表返回一行信息，表以它们在处理查询过程中将被MySQL读入的顺序被列出。

SHOW WARNINGS：

     显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优

     化过程的其它注解。

例：

 

 行信息解释：

1. id

SELECT识别符，这是SELECT的查询序列号。id的情况有三种，分别是：

•  id相同：表示加载表的顺序是从上到下。
•  id不同：id值越大，优先级越高，越先被执行。
•  id有相同，也有不同，同时存在：id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，

        优先级越高，越先执行。

2. select_type

 SELECT类型，可以为以下任何一种：

•  SIMPLE：简单SELECT(不使用UNION或子查询)
•  PRIMARY：最外面的SELECT
•  UNION：UNION中的第二个或后面的SELECT语句
•  DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
•  UNION RESULT：UNION的结果
•  SUBQUERY：子查询中的第一个SELECT
•  DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
•  DERIVED：导出表的SELECT(FROM子句的子查询)

3. table

       输出的行所引用的表。

4. type

      联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：

• system：多表

       表仅有一行(=系统表)。这是const联接类型的一个特例。

       例: id为主键

    

         

        注意：上例中如果不加limit 1，则外层查询不会优化为system类型

   

       

• const：单表+多表

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。

const表很快，因为它们只读取一次！const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。

        例：id为主键， city_name为唯一索引

         

        

        

• eq_ref：多表

对于每个来自于前面的表的行组合，从该表中读取一行(这就要求对方表的关联字段也得是UNIQUE或PRIMARY KEY)。

这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY

(这要求当前表的关联字段必须是UNIQUE或PRIMARY KEY)。

eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

出现在要连接这个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为

not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

       例：id为主键， city_name为唯一索引

        

        说明：表连接条件双方都必须是PRIMARY KEY或UNIQUE，当t1表加上索引字段查询条件时，会被进一步优化，如下：

      

• ref：单表+多表

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

如果使用的键 仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

       例：age为普通索引，num没有被索引

      

      

     

       

      

       说明： 和eq_ref不同的是，ref只要求关联表字段(t2.age)是普通索引，而对方表(t1.num)不要求被索引。

• ref_or_null：单表+多表

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值(要求字段被索引且不能为空)的行。

在解决子查询中经常使用该联接类型的优化。

        在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

   SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

       例1：age被索引，可为空

      

      实际上会被优化为 where ((`test`.`t1`.`age` = 22) or isnull(`test`.`t1`.`age`))

      例2：age被索引，不能为空

      

      

     实际上会被优化为 where (`test`.`t1`.`age` = 22)

•   index_merge

   该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的

   最长的关键元素。

     a.  索引合并交集：

          该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件，每个条件为下面之一：

·         以这种形式，即索引有确切的N部分(即包括了所有索引部分)：

                key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

      例：city_name 和 age被索引

      EXPLAIN select * from t_city where city_name = '123' and  age = 25;

   b.  索引合并并集：

          该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候，每个条件为下面之一：

·         以这种形式，即索引有确切的N部分(即包括了所有索引部分)：

                key_part1=const1 OR key_part2=const2 ... OR key_partN=constN

          例: age和city_name分别被索引

          

    c.  索引合并排序并集：没有测试出来

        该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件，但索引合并方法联合算法并不适用的时候。

      下面是一些例子：

   SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
   SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

      排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID，然后在返回记录前对它们进行排序

•   unique_subquery： 没有测出来

       该类型替换了下面形式的IN子查询的ref：

         value IN (SELECT primary_key FROM single_table WHERE some_expr)

       unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

•   index_subquery：没有测出来

       该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

         value IN (SELECT key_column FROM single_table WHERE some_expr)

•    range

        只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

        当使用 = 、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN,IN或者like(右模糊匹配)操作符，用常量比较关键字列时，可以使用range。

        例1： num被索引

       

       例2： num被索引，但使用全表扫描，是因为当搜索引擎评估全表扫描比使用索引用时更短，会使用全表扫描

      

       例3：num被索引，但查询字段只有num，这种情况下，一定会走索引

       

•   index

        该联接类型与ALL相同，除了索引树被全扫描， 当出现index类型时，数据是默认按照索引的升序排列，这通常比ALL快，因为索引文件通常比数据文件小。

        type的index和Extra的Using index不是一个含义，前者和覆盖索引没有关系，只表示查询访问数据的方式(需要遍历整个索引树)，后者和覆盖索引有关系，表示索引中覆盖了所有的查询列

        例1：age被索引

       

      

      例2：explain select * from t_city order by id;

      主键比较特殊，这里的index表明，遍历了整个索引树，取到最小id值，然后直接到数据页中按顺序查询(数据页数据本身就是按照id顺序排列的)

      

     例3：province_id被索引

     explain select id from city;

     

    注意此处为什么用了province_id字段上的索引(根据province_id排序)，体现了mysql的一个原则：优化器会尽力选择一个信息量比较大的索引，province_id索引上包含了province_id和id字段，所以被优先选择了。

• ALL

     对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

5. possible_keys

     possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

     如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

6. key

    key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7. key_len

    key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

8. ref

    ref列显示使用哪个列或常数与key一起从表中选择行。

9. rows

    rows列显示MySQL认为它执行查询时必须检查的行数。

10. Extra

      该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：

• Distinct

        MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

• Not exists

        MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的          行。

      下面是一个可以这样优化的查询类型的例子：

       SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL；

       假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。

• range checked for each record (index map: #)

        MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。

        这并不很快，但比执行没有索引的联接要快得多。

• Using filesort

        MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

• Using index

        在覆盖索引场景中使用，即从索引树中可以获取所有需要的列，不用再回表查询。      

       (查询字段和排序字段使用相同的索引(对于联合索引要满足索引最左前缀原则)

      例：province_id被索引

       explain select province_id from city where province_id = 1;

      

• Using where

        在查询数据行之后(不管是否走索引)，还要通过其它过滤条件(不管是否被索引)再次过滤

        例：province_id被索引，city_name没有索引

         explain select * from city where province_id = 1 and city_name = '苏州2';

        

        explain select * from city where city_name = '苏州2';

       

• Using where; Using index

        using index表明查询列都能直接从索引树中获取到，不用回表查询。同时出现using where, 表明索引树被用来执行索引键值的查找。如果没有同时出现using where，表明索引树用来读取数据而非执行查找操作。

        例： province_id被索引

       explain select province_id from city2 where province_id > 3;

     explain select province_id from city2 where id > 3;

   

   注意上面第一个type是range，第二个type是index

•  Using index condition

       这种和Using where; Using index类似，都需要查找索引树，不同的是，这里所需的查找列不能直接从索引树中直接获取到，要回表查询。

      例： province_id被索引

      explain select * from city2 where province_id > 3;

    

     explain select * from city2 where id > 3;

   

     注意这里：将province_id改为id后Extra变成了Using where,是因为id为聚合索引，可以通过id=3直接定位到数据行，然后直接向下一行查找(因为数据就是按照id顺序存储的，而非聚合索引必须要通过聚合索引转化)，然后检查id是否大于3。

• Using temporary

     为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

• Using sort_union(...), Using union(...), Using intersect(...)

     这些函数说明如何为index_merge联接类型合并索引扫描。

     如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

• Using index for group-by

     类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或           DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

参考博客：

      https://www.cnblogs.com/xiaoqiang-code/p/11404149.html