keep coding
0%

explain

发表于 更新于 分类于 Disqus：
本文字数： 4.8k 阅读时长 ≈ 4 分钟

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。
explain的执行的结果：
explain

explain包含的字段

  • Id 对于select查询语句的标记序号，或者再有子查询时标识selelct子句或者操作表的顺序，数字越大越先执行。如果数字一样从上往下先执行。

  • Select_type 查询类型，表示语句执行的类型，如simple表示不包含union或者select子查询的简单select的查询。

  • table 查询的表名字

  • partitions 查询的分区

  • type 查询的类型，all,index,rang,ref,eq_ref,const,system,null等类型表示查询执行的优劣。

  • possible_keys 可能使用的索引，但是不一定使用

  • key使用到的索引

  • key_len 使用的索引的长度

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

  • rows 表示MySQL认为执行查询必须检查的行数

  • filtered 指示将被表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示将与下表连接的行数。例如，如果 rows为1000且 filtered为50.00（50％），则与下表连接的行数为1000×50％= 500.需要和rows合并起来看，如最上边的多，使用常量数值进行查询，此值为100表示为过滤。

  • extra 有关MySQL如何解析查询的其他信息。如use index,等等。

type 查询的连接类型

以下列表描述了连接类型，从最佳类型到最差类型：

  • system 该表只有一行，这是const 链接类型的特例

  • const 常量查询，一般都是在唯一键，匹配到一行的因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次。

  • eq_ref 对于先前表中的每行组合，从此表中读取一行。除了 system和 const类型，这是最好的联接类型。当连接使用索引的所有部分且索引为PRIMARY KEY（主键）或UNIQUE NOT NULLindex（唯一索引）时使用。在使用联表的时候其实也是两个使用都是主键或是唯一索引。

    eq_ref可以用于使用=运算符进行比较的索引列 。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。在以下示例中，MySQL可以使用 eq_ref联接进行处理 *ref_table*：

    * FROM *ref_table*,*other_table* WHERE *ref_table*.*key_column*
    1

  • Ref 与eq_ref的却别是使用不是唯一索引，有个一列或是常量值查询可能匹配到多个行数据.可用于使用=或<=> 运算符进行比较的索引列

    * FROM *ref_table* WHERE *key_column*
    1

  • Fulltext 使用全文索引

  • Ref_or_null 连接类型类似于 ref，但是除了MySQL还会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询。在以下示例中，MySQL可以使用 ref_or_null联接进行处理*ref_table*：

    * FROM *ref_table* WHERE *key_column*
    1

  • Index_merge 表示使用了索引合并的优化方法。一般实在有索引的查询使用了or查询。

    * FROM *tbl_name* WHERE *key1* 
    1

  • unique_subquery 此类型替换 以下形式的eq_ref某些 IN子查询,子句查询的是主键或是唯一索引

    IN (SELECT `primary_key` FROM *single_table* WHERE *some_expr*)
    1

    1
    2
    3
    4
    5
    6
    7
    8
    9

    * index_subquery 此类型和unique_subquery查询类型差不多，但是子句查询的是 不是唯一索引类型。它代替`IN`子查询，但适用于以下形式的子查询中的非唯一索引

    * range 使用索引选择行，仅检索给定范围内的行。的`key` 输出行中的列指示使用哪个索引。将`key_len`包含已使用的时间最长的关键部分。

      [`range`](https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range)当一个键列使用任何的相比于恒定可使用 [`=`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal)， [`<>`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_not-equal)， [`>`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_greater-than)， [`>=`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_greater-than-or-equal)， [`<`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_less-than)， [`<=`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_less-than-or-equal)， [`IS NULL`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null)， [`<=>`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to)， [`BETWEEN`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between)， [`LIKE`](https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like)，或 [`IN()`](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_in)

    ```SELECT * FROM *tbl_name*  WHERE *key_column* = 10; SELECT * FROM *tbl_name*  WHERE *key_column* BETWEEN 10 and 20; SELECT * FROM *tbl_name*  WHERE *key_column* IN (10,20,30); SELECT * FROM *tbl_name*  WHERE *key_part1* = 10 AND *key_part2* IN (10,20,30);

  • Index 类型是all的一种特例表示不是全扫描表数据，而是全部扫描索引树。因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。发生在，查询是覆盖索引值如 select id from table.或者是在查询数据单数按照主键索引排序读取的时候。如：

    index-2

如果不是order by type将是all

  • All 表示全表扫描表示是最慢的查询。

select_type表示查询语句的类型。

  • simple 一般都是单表查询。

  • primary 查询中若包含任何复杂的子部分，最外层查询则被标记为主查询.如 value in (select id from table1)语句

  • Subquery 子查询的意思，表示在where或是select中的子查询语句

  • union 表示使用union 的联表语句如：select * from t1 union select * from t2;

  • Derived 表示from的子查询语句需要递归的执行，

Extra 查询的一些额外信息，可以提示是否使用的索引，排序是否使用到索引等等

  • Use filesort 既是用不到索引的排序，需要额外排序，一般是order by的不是索引字段或者使用不上索引造成。filesort 使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数 sort_buffer_size 的值，默认为2M。当排序记录太多 sort_buffer_size 不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序

  • Use temporary用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by 例子：

  • Use index 使用到索引.表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

  • Use where 使用where条件

  • Impossible where where条件匹配不到值。

  • Using join buffer 使用连接缓存

  • distinct 一般是在链表时出现，一旦mysql匹配到联合行就不匹配了，链表的字段都是唯一索引。

  • select tables optimized away select操作已经优化到极致了