MySQL-Advanced-Optimization-02-执行计划和索引优化 | Eloise's Paradise
0%

MySQL-Advanced-Optimization-02-执行计划和索引优化

Posted on Edited on In
Symbols count in article: 2.7k Reading time ≈ 2 mins.

本章主要探讨SQL执行计划explain(参见官网)和索引优化的细节.

EXPLAIN 语句提供了MySQL是如何执行语句的细节信息. 他能与 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE 语句协同执行.针对查询中每张表的查询 EXPLAIN将返回一行具体的信息. 此信息输出表的顺序与**MySQL读取他们的顺序一致.

下面的小节给出了explain的输出列的描述. type 和 Extra 列将在后续章节讨论.

explain输出的每一行给出一张表的信息. 具体的每一列的描述见下表. (第二列是属性设置为:FORMAT=JSON时的)

Table 8.1 EXPLAIN Output Columns

Column Meaning
id 查询标识符
select_type 查询类型
table The table for the output row
partitions 匹配的分区
type 连接类型
possible_keys 可能供选择的索引键
key 实际选用的索引键
key_len 实际索引键的长度
ref The columns compared to the index
rows 预估的检索行数
filtered 条件过滤行数所占的百分比
Extra 额外信息

  • The SELECT identifier. 当前查询所对应的标识符. 如果当前行是其他查询union的结果, 那么该值为null.

  • select_type (JSON name: none)

    The type of SELECT, 查询类型, 有如下可能值.

    select_type Value Meaning
    SIMPLE 不包含任何union, join等的简单查询.
    PRIMARY 如果包含子查询, primary则表示最外层的 SELECT
    UNION UNION 合并的第二或其后的查询
    DEPENDENT UNION UNION 合并的第二或其后的查询, 取决于最外层查询
    UNION RESULT UNION的结果.
    SUBQUERY 子查询中的第一个查询语句
    DEPENDENT SUBQUERY 子查询中的第一个查询语句 ,取决于外层查询.
    DERIVED Derived table
    DEPENDENT DERIVED 衍生于其他表的表.
    MATERIALIZED Materialized subquery
    UNCACHEABLE SUBQUERY 其结果不能直接存储, 而是需要对外层查询进行重新语句计算后才能得到的子查询
    UNCACHEABLE UNION 合并的第二或其后的查询 且满足UNCACHEABLE SUBQUERY子查询的union查询.

DEPENDENT : 通常表示关联子查询的使用. See Section 13.2.11.7, “Correlated Subqueries”.

table: 当前行输出结果对应的表名.

partitions : 该查询结果记录对应的分区. 只有在分区关键字被用到时才会显示这列结果. 未分区表的该列结果值为null. See Section 19.3.5, “Obtaining Information About Partitions”.

ALL–>

type: 该列的值描述了表的连接类型. 不同类型(性能由最优至最差)的描述如下: 官网描述EXPLAIN Join Types.

  • system

    查询表只有一行记录(= system table). 这是const类型的特例.

  • const

    单表中至多有一个匹配行记录,查询非常迅速. 因为只有一行, 所以该行的所有列的值对于优化器的其余部分可以看做是常量. [const`](https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#jointype_const) 是非常快的, 因为它只被读取一次.

    例如: 当查询的条件是将主键或索引的所有部分与常量值进行比较时, type的值会取const.

  • eq_ref

    类似ref, 区别就是在使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配. 简单来说就是多表连接中使用primary key或者unique index作为关联条件.

  • ref

如果满足任意一种: 1. 使用主键最左前缀关联 2.非主键索引关联 3. 唯一索引关联. 那么type就是ref. 换句话说, 也就是join无法从条件键筛选出唯一一行记录, 那么type就是ref. 在使用 = or <=> 运算符的操作中, ref可以用来作为索引列的值.

使用全文索引的关联.

  • ref_or_null

    与ref类似, 区别在于, MySQL会对null值做额外的检索. 该类型最多是用在优化解析子查询. 如下案例中,MySQL会使用 ref_or_null join处理ref_table.

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

    See Section 8.2.1.12, “IS NULL Optimization”.

  • index_merge

    这种类型的join表示有用到索引合并优化. 此时, 输出行的key列应该有若干个索引项, 而key_len列也列出了使用到的key列表中最长key的长度. Section 8.2.1.3, “Index Merge Optimization”.

  • unique_subquery

    与eq_ref类似, 只是子查询中有 in 条件.只是一个用来优化子查询 提高其效率的索引搜索函数. 如: 

    1
    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery

    这种类型的join与unique_subquery类似. 区别在于子查询中查询列为非唯一索引列.

    This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

    1
    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range

    该类型的join常见于where条件中将索引列与查量值进行: =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()操作的情况.

  • index

扫描整颗索引数来匹配满足条件的行.

MySQL扫描全表来找到匹配行.

Explain命令输出数结果的extra列的信息:

-------------本文结束感谢您的阅读-------------