大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

MySQL优化---EXPLAIN(mysq版本8.0)

作者:拔剑少年

简书地址:https://www.jianshu.com/u/dad4d9675892
博客地址:https://it18monkey.github.io
转载请注明出处

Explain命令是MySQL提供的内置命令,它的作用是向我们展示MySQL是如何执行sql语句的。SELECT, DELETE, INSERT, REPLACE, UPDATE 语句都可以使用Explain命令。

EXPLAIN为SELECT语句中使用的每个table返回一行信息。它以MySQL在处理语句时的读取顺序列出所有的table。MySQL使用嵌套循环的方式解决所有的表连接(join)。这意味着MySQL从第一个table读取一行,然后在第二个table、第三个table中查找匹配的行,以此类推。当所有的table都被处理过时,MySQL会输出所选的列并在所有的table中进行反向跟踪,直到找到一个有更多匹配行的table。从该table中读取下一行,并继续处理下一个table。

理解Explain输出的信息含义对SQL语句的优化有着至关重要的作用。 下面让我们详细解析下Explain命令输出的各字段的含义。

Explain 输出的列:

列名 JSON 名(FORMAT=JSON) 含义
id select_id SELECT 语句的id
select_type SELETE 语句的类型
table table_name 表名
partitions partitions 分区
type access_type join 类型
possible_keys possible_keys 可供选择使用的索引
key key 实际使用的索引
key_len key_length 实际使用索引的长度
ref ref 与索引比较的列
rows rows 预估检测行数
filtered filtered 依据表条件过滤行占比
Extra 其他信息
  1. <span id ='explain_id'>id</span>

    SELECT的标识符。这是查询中的SELECT的序号。如果当前行引用了其他行的联合结果,则该值为空。举个例子,当table列显示<unionM,N>时,表示该行引用了id值为M和N的行的联合结果,这个时候id列的值就会为空。

    如下sql:

     EXPLAIN SELECT *FROM(SELECT 2 UNION ALL SELECT 3 )t;
    

    执行结果:


    1532432243263.png
  2. <span id="explain_select_type">select_type</span>

    SELECT的类型,下表中展示了所有可能的值及其含义。

    名称 JSON 名 含义
    SIMPLE 简单的SELECT(没有使用UNION或者子查询)
    PRIMARY 最外层的查询
    UNION 在一个UNION中第二或后面的SELECT语句
    DEPENDENT UNION dependent (true) 在一个UNION中第二或后面的SELECT语句,并且依赖于外层查询
    UNION RESULT union_result UNION的结果
    SUBQUERY 子查询中的第一个SELECT
    DEPENDENT SUBQUERY dependent (true) 子查询中的第一个SELECT,并且依赖于外层查询
    DERIVED 派生表(Derived table)
    MATERIALIZED materialized_from_subquery 实例化子查询(Materialized subquery)
    UNCACHEABLE SUBQUERY cacheable (false) 不能缓存结果的子查询,并且必须为外部查询的每一行重新计算结果
    UNCACHEABLE UNION cacheable (false) 在一个UNCACHEABLE SUBQUERY的UNION语句中第二或后面的SELECT语句

    DEPENDENT通常意味着使用了关联子查询。

    DEPENDENT SUBQUERY 不同于 UNCACHEABLE SUBQUERY 。对于DEPENDENT SUBQUERY,对来自其外部上下文的每一组变量的不同值只重新计算一次子查询。对于UNCACHEABLE SUBQUERY,将会为外层上下文的每一行重新计算子查询。

    对于非查询语句,select_type的值为语句类型。比如,对于一个DELETE语句,值为DELETE

  3. <span id="explain_table">table</span>

    当前行引用的表名,除此之外也可能是以下几种值:

    • <union*M*,*N*>:该行引用了id值为M和N的行的联合结果
    • <derived*N*>:该行引用了id值为N的行的派生表结果。比如引用了从子查询中生成的派生表。
    • <subquery*N*>:该行引用了id值为N的行的实例化子查询结果。
  4. <span id="explain_partitions">partitions</span>

    查询语句将从其中匹配记录的分区。对于非分区表,值为null。

  5. <span id="explain_type">type</span>

    本列用于向我们展示MySQL使用哪种方式join当前表的。下面按照最好到最坏的顺序详细解析可能出现的值。

    • system

      该表只有一行数据。这种类型是下面const类型的一种特例。

    • const

      该表最多只有一行数据被匹配,在查询开始时就会被读取。因为只有一行,所以这一行中列的值可以被优化器的其他部分视为常量。const表非常快,因为它们只需要读取一次。

      当把一个表的主键或唯一索引和一个常量值作比较时,const就会被使用。下面的语句中,t1就可以被作为一个const表。

      SELECT * FROM  t1 WHERE id=1;
      SELECT * FROM  t2 WHERE unique_index_part1=1 and unique_index_part1=2;
      
    • eq_ref

      在与前一个表的每一行做联合时,只需要从该表中读取一行。除了systemconst类型,这个是最好的join类型。当一个join语句中使用了主键或一个非空唯一索引的全部部分时,join类型就为eq_ref

      eq_ref可用于使用=运算符进行比较的索引列。比较值可以是一个常量,也可以是这个表之前读取的表中的列。

      SELECT * FROM ref_table,other_table
        WHERE ref_table.key_column=other_table.column;
      
      SELECT * FROM ref_table,other_table
        WHERE ref_table.key_column_part1=other_table.column
        AND ref_table.key_column_part2=1;
      
    • ref

      在与前表的每个行组合中,从该表中读取具有匹配索引值的所有行。当join仅仅使用一个索引的左前缀,或者使用的索引不是一个主键,又或者不是唯一索引,ref就会被使用。如果使用的索引匹配的行数很少,这也不失为一个很好的join类型。

      eq_ref可用于使用=<=>运算符进行比较的索引列。

      SELECT * FROM ref_table WHERE key_column=expr;
      
      SELECT * FROM ref_table,other_table
        WHERE ref_table.key_column=other_table.column;
      
      SELECT * FROM ref_table,other_table
        WHERE ref_table.key_column_part1=other_table.column
        AND ref_table.key_column_part2=1;
      
    • fulltext

      使用全文索引执行join

    • ref_or_null

      这个join类型和ref有点像,但是对于包含null值行,MySQL会做一个额外的查询。这种join类型优化最常用于解析子查询。

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

      这个join类型表示使用了索引合并优化。这个时候,key列显示了一系列使用到的索引,key_len显示了使用到的索引的最长键长。

    • unique_subquery

      在以下形式的某些in子查询中,这个join类型会替代eq_ref

      value IN (SELECT primary_key FROM single_table WHERE some_expr)
      

      unique_subquery只是一个索引查找函数,它完全替代了子查询以提高效率。

    • index_subquery

      这个join类型和unique_subquery有些类似。它在子查询中替换,但在以下形式的子查询中适用于非唯一索引:

      value IN (SELECT key_column FROM single_table WHERE some_expr)
      
    • range

      当使用一个索引去选择行时只检索了给定范围的行。key列显示了使用了哪个索引,key_len显示了使用到的索引的最长键长。当join类型为range时,ref列值为null。

      range可用于使用 =, <>, >, >=, <, <=, IS NULL, <=>,BETWEEN, LIKE, 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

      这个join类型和ALL相似,区别是扫描了索引数。以下两种情况:

      • 当索引是一个覆盖索,即包含(或覆盖)所有需要查询的字段的值,那么就只需要扫描索引树而无需回表,这个时候Extra列的值就会为Using indexindex类型通常要比ALL类型要快,因为索引的长度通常要比表数据小得多。
      • 按索引顺序查找数据行来执行全表扫描,这个时候Extra列的值不会为Using index
    • ALL

      为了和前表的每一行做组合而全表扫描。这种类型通常不太好,应当尽量避免。我们可以通过给表添加索引来避免这种情况。

  6. <span id="explain_possible_keys">possible_keys</span>

    这一列显示了可供MySQL选择用来查询使用的所有索引。需要注意的是其中的一些索引在实际运行时可能不会被使用。

    如果这一列为null,那就表示没有想关的索引。这个时候,可以检查where子句引用的列是否适合建立索引,通过建立合适的索引增强执行性能。索引建立完成后,可以再次执行EXPLAIN查看效果。

    想要知道表已经建立了哪些索引,可以通过SHOW INDEX FROM table_name查看。

  7. <span id="explain_key">key</span>

    本列显示的MySQL实际选择使用的索引。如果本列为空,则表示MySQL没有找到索引来提升执行效率。

    这个索引可能不在possible_keys里。如果所有possible_keys索引都不适合查找行,但是其他有个索引包含了查询语句列举出的所有的列,就会发生这种情况,换句话说,这个索引覆盖了查询列。尽管它不能用来确定要检索哪些行,但扫描索引的效率比扫描数据行要高。

    InnoDB引擎中,即使查询列包含主键,辅助索引也可能会覆盖所选列。因为InnoDB将主键值存储在每个辅助索引中。

    我们可以通过在查询语句中使用FORCE INDEX, USE INDEX, IGNORE INDEX 来强制MySQL使用或忽略possible_keys列中列出的索引。

  8. <span id="explain_key_len">key_len</span>

    本列显示了MySQL决定使用的索引的长度。本列的值能够使你确定MySQL实际使用了复合索引的多少部分。如果key列的值为null,那么key_len列的值也为null。

    由于索引存储格式,一个允许空值的列,索引长度要比非空列要长。

  9. <span id="explain_ref">ref</span>

    本列显示了哪一列或那个常量被用来和key列中的索引做比较的从而从表中筛选行。

    如果值为func,表明使用了某些函数的结果。这个函数实际上可能是一个运算符,比如算术运算符 。

  10. <span id="explain_rows">rows</span>
    本列表示MySQL认为执行查询语句时必须查找的行数。在InnoDB引擎中,这个数值是个预估值,可能会不精确。

  11. <span id="explain_filtered">filtered</span>
    本列表示将被表条件过滤的表行的估计百分比。最大值为100,这意味着没有对行进行筛选。

  12. <span id="explain_extra">Extra</span>

本列展示了MySQL解析查询时一些额外的信息。下面介绍下本列可能出现的值。

  • const row not found
  • Deleting all rows
  • Distinct
  • FirstMatch
  • Full scan on NULL key
  • Impossible HAVING
  • Impossible WHERE
  • Impossible WHERE noticed after reading const tables
  • LooseScan
  • No matching min/max row
  • no matching row in const table
  • No matching rows after partition pruning
  • No tables used
  • Not exists
  • Plan isn't ready yet
  • Range checked for each record (index map: N)
  • Recursive
  • Select tables optimized away
  • Start temporary, End temporary
  • Using filesort
  • Using index
  • Using index condition
  • Using index for group-by
  • Using index for skip scan
  • Using join buffer
  • Using MRR
  • Using sort_union(...), Using union(...), Using intersect(...)
  • Using where
  • Using where with pushed condition
  • Zero limit