乐趣区

关于mysql:MySQL-文档翻译理解查询计划

jiezi

原文地址:【MySQL 文档翻译】了解查问打算

欢送拜访我的博客: http://blog.duhbb.com/

官网文档

MySQL 官网文档地址: 8.8 Understanding the Query Execution Plan

引言

MySQL 优化器会依据 SQL 语句中的表, 列, 索引和 WHERE 子句中的条件的详细信息, 应用许多技术来无效地执行 SQL 查问. 能够在不读取所有行的状况下对一个微小的表执行查问; 能够在不比拟每个行组合的状况下执行波及多个表的连贯. 优化器抉择执行最无效查问的一组操作称为 查问执行打算 (query execution plan), 也称为 EXPLAIN plan. 你的指标是意识到 EXPLAIN 打算表明查问已优化好, 如果发现一些低效的操作, 能够通过学习 SQL 语法和索引技术来改良查问打算.

应用 EXPLAIN 优化查问

EXPLAIN 语句提供无关 MySQL 如何执行指标语句的信息:

  • EXPLAIN 能够与 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 语句一起应用.
  • 当 EXPLAIN 与可解释语句 (explainable statement) 一起应用时, MySQL 会显示来自优化器的无关语句执行打算的信息. 也就是说, MySQL 解释了它将如何解决该语句, 包含无关表 如何连贯 以及以 何种程序 连贯的信息. 无关应用 EXPLAIN 获取执行打算信息的信息, 请参阅第 8.8.2 节 EXPLAIN 输入格局.
  • 当 EXPLAIN 与 FOR CONNECTION connection_id 而不是可解释的语句一起应用时, 它显示在命名连贯中执行的语句的执行打算. 请参阅第 8.8.4 节 获取命名连贯的执行打算信息.
  • 对于 SELECT 语句, 应用 SHOW WARNINGS 可是使 EXPLAIN 生成并显示的附加执行打算信息. 请参阅第 8.8.3 节 扩大 EXPLAIN 输入格局.
  • EXPLAIN 对于查看波及分区表的查问很有用. 请参阅第 24.3.5 节 获取无关分区的信息.
  • FORMAT 选项可用于抉择输入格局. TRADITIONAL 以表格格局显示输入. 如果没有 FORMAT 选项, 这是默认设置. 当 FORMAT 的选项值为 JSON 能够显示 JSON 格局的信息.

在 EXPLAIN 的帮忙下, 能够查看应该在哪里为表增加索引, 以便通过应用索引查找行来更快地执行语句. 您还能够应用 EXPLAIN 查看优化器是否以最佳程序连贯表. 要提醒优化器应用与语句中表命名程序绝对应的连贯程序, 请以 SELECT STRAIGHT_JOIN 语句结尾, 而不是 SELECT. (请参阅 第 13.2.10 节 SELECT 语句.) 然而, STRAIGHT_JOIN 可能会阻止应用索引, 因为它禁用了 半连贯转换. 看第 8.2.2.1 节 应用半连贯转换优化 IN 和 EXISTS 子查问谓词.

优化器跟踪 有时可能会提供比 EXPLAIN 更具体的信息. 然而, 优化器跟踪格局和内容可能会因版本而异. 无关详细信息, 请参阅 MySQL 外部: 跟踪优化器.

如果您认为应该用到的索引在查问打算中确没有被应用, 请运行 ANALYZE TABLE 以更新表统计信息, 例如键的基数, 这可能会影响优化器所做的抉择. 请参阅第 13.7.3.1 节 ANALYZE TABLE 语句.

Explain 的输入格局

EXPLAIN 为 SELECT 语句中应用的每个表返回一行信息. 它依照 MySQL 在解决语句时读取它们的顺序排列的. 这意味着 MySQL 从第一个表中读取一行, 而后在第二个表中找到匹配的行, 而后在第三个表中, 以此类推. 解决完所有表后, MySQL 会输入选定的列并通过表列表回溯, 直到找到匹配行较多的表. 从此表中读取下一行, 并持续解决下一个表.

Explain 输入的列

本节介绍由 EXPLAIN 生成的输入列. 前面的局部提供了无关 type 和 Extra 列的附加信息.

EXPLAIN 的每个输入行提供无关一个表的信息. 每一行都蕴含 表 8.1 解释输入列 中总结的值, 并在表后进行了更具体的形容. 列名显示在表格的第一列; 第二列提供了 FORMAT=JSON 应用时输入中显示的等效属性名称.

列 JSON 名称 含意
id select_id SELECT 标识符
select_type None SELECT 类型
table table_name 输入行的表
partitions partitions 匹配的分区
type access_type 联接 (join) 类型
possible_keys possible_keys 可供选择的索引
key key 理论抉择的索引
key_len key_length 所选 key 的长度
ref ref 与索引比拟的列
rows rows 预计要查看的行数
filtered filtered 按表条件过滤的行百分比 (过滤得越多阐明成果越好)
Extra None 附加信息

JSON 格局的输入中的 NULL 不会在 JSON 格局的 EXPLAIN 信息中显示.

  • id
    SELECT 标识符. 这是查问中 SELECT 的序号. 如果该值是 NULL 则示意该行援用其余行的联结后果 (union result). 在这种状况下, table 列显示的是 <unionM,N> , 示意该行是 M 和 N 在 id 上联结 (union) 的行.
  • select_type
    SELECT 的类型, 能够是下表中显示的任何一种.
select_type 价值 JSON 名称 意义
SIMPLE 没有任何 简略 SELECT(不应用 UNION 或子查问)
PRIMARY 没有任何 最外层 SELECT
UNION 没有任何 UNION 中的第二个或靠后的 SELECT 的语句
DEPENDENT UNION dependent(true) UNION 中的第二个或前面 SELECT 的语句, 取决于内部查问
UNION RESULT union_result UNION 后的后果.
SUBQUERY 没有任何 子查问中的第一个 SELECT
DEPENDENT SUBQUERY dependent(true) 子查问中第一个 SELECT, 依赖于内部查问
DERIVED 没有任何 派生表
DEPENDENT DERIVED dependent(true) 派生表依赖于另一个表
MATERIALIZED materialized_from_subquery 物化子查问
UNCACHEABLE SUBQUERY cacheable(false) 一个子查问, 其后果无奈缓存, 必须为内部查问的每一行从新计算
UNCACHEABLE UNION cacheable(false) UNION 中第二个或靠后的 SELECT, 属于不可缓存子查问 (请参阅 参考资料 UNCACHEABLE SUBQUERY)

DEPENDENT 个别指关联子查问的应用. 详情见 Section 13.2.11.7 Correlated Subqueries

DEPENDENT SUBQUERY 和计算和 UNCACHEABLE SUBQUERY 的计算不太一样.DEPENDENT SUBQUERY 中的子查问对外部上下文中的不同汇合的值只计算一遍. 而 UNCACHEABLE SUBQUERY, 会对外部上下文中的每一行都从新计算.

  • table
    输入行所援用的表的名称. 这也能够是以下值之一:

    • <union M,N>: 该行是指具备 M 和 N 并.
    • <derived N>: 该行是指值为 N 的派生表后果. 例如, 派生表可能来自 FROM 子句中的子查问.
    • <subquery N>: 该行指的是 N 的物化子查问的后果. 请参阅第 8.2.2.2 节 应用物化优化子查问.
  • partitions
    查问将匹配记录的分区. 如果该值为 NULL 则表明是非分区表.
  • type
    联接类型. 无关不同类型的形容, 请参阅 EXPLAIN 连贯类型. 联接类型还挺重要的.
  • possible_keys (重要)
    该 possible_keys 列批示 MySQL 能够抉择从中查找此表中的行的索引. 请留神, 此列齐全独立于输入中显示的表格程序 EXPLAIN. 这意味着某些键在 possible_keys 理论中可能无奈与生成的表程序一起应用.
    如果此列是 NULL(或在 JSON 格局的输入中未定义), 则没有相干索引. 在这种状况下, 您能够通过查看 WHERE 子句来查看它是否援用了适宜索引的某些列或列, 从而进步查问的性能. 如果是这样, 请创立一个适当的索引并 EXPLAIN 再次查看查问.
    要查看表有哪些索引, 请应用: SHOW INDEX FROM tbl_name
  • key (重要)
    该 key 列批示 MySQL 理论决定应用的键 (索引). 如果 MySQL 决定应用其中一个 possible_keys 索引来查找行, 则该索引被列为键值.
    可能 key 中的值中不存在的索引 possible_keys 中. 如果 possible_keys 没有适合的索引进行查问则会呈现这种状况, 但查问抉择的所有列都是其余索引的列, 则可能会产生这种状况. 也就是说, 命名索引笼罩了选定的列, 因而尽管它不用于确定要检索哪些行, 但索引扫描比数据行扫描更无效. (感觉就是查问的时候, 所有的索引都不太可用, 而后就走全表扫描, 然而所选的列又是被二级索引笼罩了, 所以会优先去扫描二级索引, 我了解的是这样的 )
    对于 InnoDB, 即便查问还抉择了主键, 二级索引也可能笼罩选定的列, 因为 InnoDB 每个二级索引都存储了主键值. 如果 key 是 NULL, 则 MySQL 没有找到可用于更无效地执行查问的索引.
    要强制 MySQL 应用或疏忽 possible_keys 列中列出的索引, 请在查问中应用 FORCE INDEX, USE INDEX 或 IGNORE INDEX.
    对于 MyISAM 表, 运行 ANALYZE TABLE 有助于优化器抉择更好的索引. 对于 MyISAM 表, myisamchk --analyze 也是如此.
  • key_len
    key_len 列批示 MySQL 决定应用的 key 的长度. key_len 的值使您可能确定 MySQL 理论应用的 multi-part key 的多少局部. 如果 key 是 NULL, key_len 也是 NULL.
    因为 key 存储格局的起因, 能够为空的 key 的长度要不不能为空的 key 的长度多 1.
    (这个应该是联结索引能够用到前几个的状况)
  • ref
    ref 列显示将哪些列或常量与 key 列中指定的索引进行比拟以从表中抉择行.
    如果值为 func, 则应用的值是某个函数的后果. 要查看哪个性能, 请在 EXPLAIN 后应用 SHOW WARNINGS 以 EXPLAIN 扩大输入. 该函数实际上可能是一个运算符, 例如算术运算符.
  • rows
    rows 列批示 MySQL 认为它必须查看以执行查问的行数.
    对于 InnoDB 表格, 这个数字是一个估计值, 可能并不总是精确的.
  • filtered
    filtered 列批示按表条件过滤的表行的预计百分比. 最大值为 100, 这意味着没有过滤行. 从 100 开始减小的值示意过滤量减少. rows 显示查看的预计行数, $rows × filtered$ 显示与下表连贯的行数. 例如, 如果 rows 是 1000 并且 filtered 是 50.00 (50%), 则要与下表连贯的行数是 $1000 × 50% = 500$.
  • Extra
    此列蕴含无关 MySQL 如何解析查问的附加信息. 无关不同值的形容, 请参阅 EXPLAIN 额定信息.

解释连贯类型

EXPLAIN 输入中的 type 列形容了表是如何连贯的. 在 JSON 格局的输入中, 则放到了 access_type 属性. 以下列表形容了连贯类型, 按从最佳到最差的顺序排列:

  • system
    该表只有一行 (= 零碎表). 这是 const 连贯类型的一个特例.

  • const
    该表最多有一个匹配行, 在查问开始时读取. 因为只有一行, 所以这一行中列的值能够被优化器的其余部分视为常量. const 表十分快, 因为它们只被读取一次.
    当您将 PRIMARY KEY 或 UNIQUE 索引的所有局部与常量值进行比拟时就是应用 const. 在以下查问中, tbl_name 能够用作 const 表:

    SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;

  • eq_ref (间接查问主键或者非空索引)
    对于先前表中的每个行组合, 从该表中读取一行. 除了 system 和 const 类型, 这是最好的连贯类型. 当连贯应用索引的所有局部并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时就是这种类型.
    eq_ref 可用于应用 = 运算符比拟的索引列. 比拟值能够是常量或应用在此表之前读取的表中的列的表达式. 在以下示例中, MySQL 能够应用 eq_ref 连贯来解决 ref_table:

    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 (最左前缀或键不是 PRIMARY KEY 或 UNIQUE 索引)
    对于先前表中的每个行组合, 从该表中读取具备匹配索引值的所有行. 如果连贯仅应用键的最左前缀或键不是 PRIMARY KEY 或 UNIQUE 索引 (换句话说, 如果连贯不能基于键值抉择单行), 则是 ref. 如果应用的键只匹配几行, 这是一个很好的连贯类型.
    ref 可用于应用 = 或 <=> 运算符比拟的索引列. 在以下示例中, MySQL 能够应用 ref 连贯来解决 ref_table:

    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
    连贯是应用 FULLTEXT 索引执行的.

  • ref_or_null(相比 ref 加了一个能够为 null)
    这种连贯类型相似于 ref, 但另外 MySQL 会额定搜寻蕴含 NULL 值的行. 这种连贯类型优化最罕用于解析子查问. 在以下示例中, MySQL 能够应用 ref_or_null 连贯来解决 ref_table:

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

    请参阅第 8.2.1.15 节 IS NULL 优化.

  • index_merge
    此连贯类型表明应用了索引合并优化. 在这种状况下, key 输入行中的列蕴含所用索引的列表, 并 key_len 蕴含所用索引的最长键局部的列表. 无关更多信息, 请参阅第 8.2.1.3 节 索引合并优化.

  • unique_subquery
    此类型可将通过上面的 IN 子查问替换 eq_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 于这种类型.
    range 能够在应用运算符中的任何一个与常量进行比拟:

    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 (齐全扫描索引了)
    连贯类型与 index 雷同 ALL, 只是扫描了索引树. 这有两种形式:

    • 如果索引是查问的笼罩索引并且能够用于满足表中所需的所有数据, 则仅扫描索引树. 在这种状况下, 该 Extra 列 显示 Using index. 仅索引扫描通常比仅索引扫描更快, ALL 因为索引的大小通常小于表数据.
    • 应用从索引中读取以按索引程序查找数据行来执行全表扫描. Uses index 没有呈现在 Extra 列中.

    当查问仅应用属于单个索引的列时,MySQL 能够应用此连贯类型.

  • ALL(全表扫描了)
    对先前表中的每个行组合进行全表扫描. 如果该表是第一个未被标记为 const 的表 , 这通常不好, 并且在所有其余状况下通常十分蹩脚. 通常, 您能够 ALL 通过增加索引来防止基于先前表中的常量值或列值从表中检索行.

哇偶, 好厉害, 感觉这个得把握一下哦

解释额定信息

输入列 Extra 蕴含 EXPLAIN 无关 MySQL 如何解析查问的附加信息. 上面的列表解释了能够呈现在此列中的值. 每个我的项目还为 JSON 格局的输入批示哪个属性显示该 Extra 值. 对于其中一些, 有一个特定的属性. 其余显示为 message 属性的文本.

如果您想尽可能快地进行查问, 请留神查看 Extra 值是否蕴含 Using filesortUsing 还是 Using temporary.

Extra 提供了更多的信息, 比方是否用了长期表, 是否走了文件排序

  • Backward index scan
    优化器可能对 InnoDB 表应用降序索引. 与 Using index 一起显示. 无关更多信息, 请参阅第 8.3.13 节 降序索引.
  • Child of ‘table’ pushed join@1(不重要)
    该表被援用为 table 能够下推到 NDB 内核的连贯中的子项. 仅在启用下推连贯时实用于 NDB Cluster.ndb_join_pushdown.
  • const row not found
    对于诸如 SELECT ... FROM tbl_name 的查问, 表是空的.
  • Deleting all rows(删除的时候是否走捷径?)
    对于 DELETE, 一些存储引擎 (例如 MyISAM) 反对一种处理程序办法, 该办法以简略疾速的形式删除所有表行. 如果引擎应用此优化, Extra 则会显示此值.
  • Distinct(是否 distinct?)
    MySQL 正在寻找不同的值, 因而它在找到第一个匹配行后进行为以后行组合搜寻更多行.
  • FirstMatch(tbl_name)
    对 tble_name 这个表应用了 semijoin FirstMatch 连贯快捷策略.
  • Full scan on NULL key
    当优化器无奈应用索引查找拜访办法时, 子查问优化会产生这种状况作为回退策略.
  • Impossible HAVING
    HAVING 子句始终为 false, 不能抉择任何行.
  • Impossible WHERE
    WHERE 子句始终为 false, 不能抉择任何行.
  • Impossible WHERE noticed after reading const tables
    MySQL 已读取所有 const(and system) 表并留神到该 WHERE 子句始终为 false.
  • LooseScan(m..n)
    应用半连贯 LooseScan 策略. m 并且 n 是关键部件号.
  • No matching min/max row
    没有行满足查问的条件, 例如: SELECT MIN(...) FROM ... WHERE condition
  • no matching row in const table
    对于带有连贯的查问, 有一个空表或没有满足惟一索引条件的行的表.
  • No matching rows after partition pruning
    对于 DELETE or UPDATE, 优化器在分区修剪后没有发现要删除或更新的内容. 它与 SELECT 语句 Impossible WHERE 的含意类似.
  • No tables used
    查问没有 FROM 子句, 或有 FROM DUAL 子句.
    对于 INSERT 或 REPLACE 语句, 当没有 SELECT 局部时 EXPLAIN 显示此值. 执行 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL 会显示这个.

  • Not exists
    MySQL 可能对 LEFT JOIN 查问进行优化, 并且在找到与条件匹配的行后, 不会查看该表中的前一行组合的更多行. 以下是能够通过这种形式优化的查问类型的示例:

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

    假如 t2.id 定义为 NOT NULL. 在这种状况下, MySQL 扫描 t1 并查找行 t2 中与 t1.id 值相等的行. 如果 MySQL 在 t2 中找到匹配的行 , 它就晓得 t2.id 永远不可能为 NULL, 并且不会扫描具备雷同值的其余行. 换句话说, 对于 t1 中的每一行, MySQL 只须要在 t2 中进行一次查找, 而不论在 中理论匹配了多少行.
    在 MySQL 8.0.17 及更高版本中, 这也能够批示 WHERE 中的 NOT IN (subquery) 或 NOT EXIST (subquery) 已在外部转换为反连贯 (antijoin). 这将移除子查问并将其表带入最顶层查问的打算中, 从而提供改良的成本计划. 通过合并半连贯 (semijoins) 和反连贯 (antijoins), 优化器能够更自在地从新排序执行打算中的表, 在某些状况下会产生更快的打算.(牛逼了)
    您能够通过 EXPLAIN 后执行 SHOW WARNINGS 后的 Message 列或在 EXPLAIN FORMAT=TREE 的输入中查看对给定查问是否执行反连贯转换.

    笔记
    反连贯是半连贯 table_a 和 table_b 在 condition 上的补充. 反连贯返回 table_a 中所有没有在 condition 上匹配 table_b 的所有行.

  • Plan isn’t ready yet
    EXPLAIN FOR CONNECTION 当优化器尚未实现为在命名连贯中执行的语句创立执行打算时, 会呈现此值. 如果执行打算输入蕴含多行, 则任何或所有行都可能具备此 Extra 值, 具体取决于优化器在确定残缺执行打算时的进度.
  • Range checked for each record (index map: N)
    MySQL 没有找到能够应用的好的索引, 但发现某些索引可能会在之前表中的列值已知后应用. 对于后面表格中的每个行组合, MySQL 查看是否能够应用 range 或者 index_merge 拜访办法来检索行. 这不是很快, 但比执行齐全没有索引的连贯要快. 利用的准则在 第 8.2.1.2 节 范畴优化 和第 8.2.1.3 节 索引合并优化 中所述, 但上表的所有列值都是已知的并被视为常量.
    索引从 1 开始编号, 程序与表中所示的雷同 SHOW INDEX. 索引映射值 N 是批示哪些索引是候选索引的位掩码值. 例如, 值 0x19(二进制 11001) 示意思考索引 1,4 和 5.
  • Recursive
    这表明该行实用于 SELECT 递归专用表表达式的递归局部. 请参见第 13.2.15 节 WITH(专用表表达式).

  • Rematerialize
    Rematerialize (X,...) 显示在 EXPLAIN T 中, 其中是在读取 X 新行时触发从新实现的任何横向派生表. 例如:

    SELECT
  ...
FROM
  t,
  LATERAL (derived table that refers to t) AS dt
...

    每次 t 顶部查询处理新行时, 派生表的内容都会从新实现以使其放弃最新状态.

  • Scanned N databases
    这示意在处理表查问时服务器执行了多少目录扫描 INFORMATION_SCHEMA, 如第 8.2.3 节 优化 INFORMATION_SCHEMA 查问 中所述. N 的值能够是 0, 1 或 all.

  • Select tables optimized away
    优化器确定 1) 最多应该返回一行, 以及 2) 要生成这一行, 必须读取一组确定性的行. 当在优化阶段能够读取要读取的行时 (例如, 通过读取索引行), 在查问执行期间不须要读取任何表.
    当查问被隐式分组 (蕴含聚合函数但没有 GROUP BY 子句) 时, 第一个条件失去满足. 当每个应用的索引执行一次行查找时, 满足第二个条件. 读取的索引数决定了要读取的行数.
    思考以下隐式分组查问:

    SELECT MIN(c1), MIN(c2) FROM t1;

    假如 MIN(c1) 能够通过读取一个索引行 MIN(c2) 来检索它, 并且能够通过从不同的索引读取一行来检索它. 也就是说, 对于每一列 c1 和 c2, 都存在一个索引, 其中该列是索引的第一列. 在这种状况下, 通过读取两个确定性行来返回一行.
    如果要读取的行不确定, 则 Extra 不会呈现此值. 思考这个查问:

    SELECT MIN(c2) FROM t1 WHERE c1 < = 10;

    假如这 (c1, c2) 是一个笼罩索引. 应用此索引, c1 <= 10 必须扫描所有行以找到最小值 c2. 相比之下, 思考这个查问:

    SELECT MIN(c2) FROM t1 WHERE c1 = 10;

    在这种状况下, 第一个索引行 c1 = 10 蕴含最小值 c2 . 只需读取一行即可生成返回的行.
    对于保护每个表的准确行数的存储引擎 (例如 MyISAM, 但不是 InnoDB), 对于短少 WHERE 子句的 COUNT(*) 或始终为真且没有 GROUP BY 子句的查问, Extra 可能会呈现此值 .(这是一个隐式分组查问的实例, 其中存储引擎影响是否能够读取确定的行数.)

  • Skip_open_table, Open_frm_only, Open_full_table
    这些值示意实用于 INFORMATION_SCHEMA 表查问的文件关上优化.

    • Skip_open_table: 表格文件不须要关上. 该信息已从数据字典中取得.
    • Open_frm_only: 表信息只须要读取数据字典.
    • Open_full_table: 未优化的信息查找. 表信息必须从数据字典中读取并通过读取表文件.
  • Start temporary, End temporary
    这表明长期表用于 semijoin Duplicate Weedout 策略.
  • unique row not found
    对于诸如 SELECT ... FROM tbl_name 的查问, 没有行满足 UNIQUE 索引或 PRIMARY KEY.
  • Using filesort(重要)
    MySQL 必须做一个额定的过程来找出如何按排序程序检索行. 排序是通过依据连贯类型遍历所有行并存储排序键和指向与 WHERE 子句匹配的所有行的行的指针来实现的. 而后对键进行排序, 并按排序程序检索行. 请参见第 8.2.1.16 节 排序优化.
  • Using index
    仅应用索引树中的信息从表中检索列信息, 而无需执行额定的查找来读取理论行. 当查问仅应用属于单个索引的列时, 能够应用此策略.
    对于 InnoDB 具备用户定义的汇集索引的表, 即便 Extra 这一列中不存在 Using index 也能够应用该索引 Extra. 如果 type 是 index 而 key PRIMARY 就是这种状况.
  • Using index condition
    通过拜访索引元组并首先对其进行测试以确定是否读取残缺的表行来读取表. 这样, 除非有必要, 否则索引信息用于提早 (“ 下推 ”) 读取全表行. 请参阅第 8.2.1.6 节 索引条件下推优化.
  • Using index for group-by
    与 Using index 表拜访办法相似, Using index for group-by 表明 MySQL 找到了一个索引, 该索引可用于检索 GROUP BY 或 DISTINCT 查问的所有列, 而无需对理论表进行任何额定的磁盘拜访. 此外, 索引以最无效的形式应用, 因而对于每个组, 只读取几个索引条目. 无关详细信息, 请参阅第 8.2.1.17 节 GROUP BY 优化.
  • Using index for skip scan
    示意应用了 跳过扫描拜访 (Skip Scan) 办法. 请参阅跳过扫描范畴拜访办法.
  • Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join)
    来自晚期连贯的表被局部读入连贯缓冲区, 而后从缓冲区中应用它们的行来执行与以后表的连贯. (Block Nested Loop) 批示应用块嵌套循环算法,(Batched Key Access) 批示应用批量 key 拜访算法, (hash join) 示意应用散列连贯. 也就是说, EXPLAIN 输入前一行的表中的键被缓冲, 匹配的行从呈现的行所代表的表中批量提取, 显示 Using join buffer.
    在 JSON 格局的输入中, 的值 using_join_buffer 始终是 Block Nested Loop,Batched Key Access 或之一 hash join.
    哈希连贯从 MySQL 8.0.18 开始可用; 在 MySQL 8.0.20 或更高版本的 MySQL 中不应用 Block Nested-Loop 算法. 无关这些优化的更多信息, 请参阅第 8.2.1.4 节,” 哈希连贯优化 ” 和 块嵌套循环连贯算法.
    无关批量密钥拜访算法的信息, 请参阅批量密钥拜访连贯.
  • Using MRR
    应用多范畴读取优化策略读取表. 请参见第 8.2.1.11 节 多范畴读取优化.
  • Using sort_union(…), Using union(…), Using intersect(…)
    这些批示显示了如何为 index_merge 连贯类型索引扫描的特定算法. 请参阅第 8.2.1.3 节 索引合并优化.
  • Using temporary
    为了解析查问, MySQL 须要创立一个长期表来保留后果. 如果查问蕴含以不同形式列出列的 GROUP BY 和 ORDER BY 子句, 通常会产生这种状况.
  • Using where
    WHERE 子句用于限度与下一个表匹配或发送到客户端的行. 除非您特地打算从表中获取或查看所有行, 否则如果 Extra 值不是 Using where 并且表连贯类型是 ALL 或 index, 则您的查问可能有问题.
  • Using where with pushed condition
    此项仅实用于 NDB 表.
  • Zero limit
    该查问有一个 LIMIT 0 子句, 不能抉择任何行.

EXPLAIN 输入解释

通过获取 EXPLAIN 输入 rows 列中值的乘积, 您能够很好地理解连贯的好坏. 这应该大抵告诉您 MySQL 必须查看多少行能力执行查问. 如果您应用 max_join_size 零碎变量限度查问, 则此行积还用于确定 SELECT 要执行哪些多表语句以及要停止哪些语句. 请参见第 5.1.1 节 配置服务器.

以下示例显示了如何依据 EXPLAIN 提供的信息逐渐优化多表连贯.

假如您有如下的 SELECT 语句, 并且您打算应用 EXPLAIN 命令对其进行查看 :

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于此示例, 作出以下假如:

  • 被比拟的列已申明如下.
表 列 数据类型
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)
  • 这些表具备以下索引.
表 索引
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID(主键)
do CUSTNMBR(主键)
  • 这些 tt.ActualPC 值不是均匀分布的.

最后, 在执行任何优化之前, 该 EXPLAIN 语句会生成以下信息:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

因为对于每个表 type 都是 ALL, 这个输入表明 MySQL 正在生成所有表的笛卡尔积; 也就是说所有行的组合. 这须要相当长的工夫, 因为必须查看每个表中行数的乘积. 对于以后的状况, 这个乘积是 $74 × 2135 × 74 × 3872 = 45,268,558,720$ 行. 如果表更大, 您只能设想须要多长时间.

这里的一个问题是, 如果将列申明为雷同的类型和大小, MySQL 能够更无效地应用列上的索引. 在这种状况下, 如果 VARCHAR 和 CHAR 被申明为雷同的大小, 则认为它们是雷同的.tt.ActualPC 被申明为 CHAR(10) , 而 et.EMPLOYID 被申明为 CHAR(15), 因而存在长度不匹配.

要修复列长度之间的这种差别, 请应用 ALTER TABLE 将 ActualPC 从 10 个字符缩短到 15 个字符:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

当初 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15). 再次执行该 EXPLAIN 语句会产生以下后果:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这并不完满, 但要好得多: 这些 rows 值的乘积小了 74 倍. 这个版本在几秒钟内执行.

能够进行第二次更改以打消 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 比拟的列长度不匹配:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

批改后, EXPLAIN 产生如下所示的输入:

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

在这一点上, 查问简直被尽可能地优化了. 剩下的问题是, 默认状况下, MySQL 假设 tt.ActualPC 列中的值是均匀分布的, 而表 tt 并非如此. 侥幸的是, 通知 MySQL 剖析 key 散布很容易:

mysql> ANALYZE TABLE tt;

应用附加的索引信息, 连贯是完满的并 EXPLAIN 产生以下后果:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN 输入中的 rows 列是来自 MySQL 连贯优化器的有依据的猜想. rows 通过将产品与查问返回的理论行数进行比拟, 查看这些数字是否更靠近事实. 如果数字齐全不同, 您可能会通过 STRAIGHT_JOIN 在 SELECT 语句中应用并尝试在 FROM 子句中以不同的程序列出表来取得更好的性能.(然而, STRAIGHT_JOIN 可能会阻止应用索引, 因为它禁用了 半连贯转换. 请参阅第 8.2.2.1 节 应用半连贯转换优化 IN 和 EXISTS 子查问谓词.

在某些状况下, 当 EXPLAIN SELECT 与子查问一起应用时, 能够执行批改数据的语句; 无关更多信息, 请参阅第 13.2.11.8 节 派生表.

扩大 EXPLAIN 输入格局

该 EXPLAIN 语句产生额定的信息, 这些信息不是 EXPLAIN 输入的一部分, 但能够通过在 EXPLAIN 后接着 SHOW WARNINGS 语句来查看. 从 MySQL 8.0.12 开始, 扩大信息可用于 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 语句. 在 8.0.12 之前, 扩大信息仅实用于 SELECT 语句.

SHOW WARNINGS 输入的 Message 显示优化器在 SELECT 语句如何限定表名和列名, SELECT 利用重写和优化规定后的样子, 以及可能无关优化过程的其余正文.

EXPLAIN 后的 SHOW WARNINGS 仅针对 SELECT 语句生成扩大信息. 其余可解释语句 (DELETE, INSERT, REPLACE 和 UPDATE) 则显示的空后果.

这是扩大 EXPLAIN 输入的示例:

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select ` test` .` t1` .` a` AS ` a` ,
         < in_optimizer> (` test` .` t1` .` a` ,` test` .` t1` .` a` in
         ( < materialize> (/* select#2 */ select ` test` .` t2` .` a`
         from ` test` .` t2` where 1 having 1 ),
         < primary_index_lookup> (` test` .` t1` .` a` in
         < temporary table> on < auto_key>
         where ((` test` .` t1` .` a` = ` materialized-subquery` .` a`))))) AS ` t1.a
         IN (SELECT t2.a FROM t2)` from ` test` .` t1`
1 row in set (0.00 sec)

因为显示的语句 SHOW WARNINGS 可能蕴含非凡标记以提供无关查问重写或优化器操作的信息, 所以该语句不肯定是无效的 SQL, 并且不打算执行. 输入还可能蕴含带有 Message 值的行, 这些值提供无关优化器所采取的操作的附加非 SQL 解释性阐明.

以下列表形容了能够呈现在由 SHOW WARNINGS 显示的扩大输入中的非凡标记:

  • <auto_key>
    为长期表主动生成的键.
  • <cache>(expr)
    表达式 (例如标量子查问) 执行一次, 后果值保留在内存中供当前应用. 对于由多个值组成的后果, 能够创立一个长期表并 <temporary table> 改为显示.
  • <exists>(query fragment)
    将子查问谓词转换为 EXISTS 谓词, 并对子查问进行转换, 以便它能够与 EXISTS 谓词一起应用.
  • <in_optimizer>(query fragment)
    这是一个没有用户意义的外部优化器对象.
  • <index_lookup>(query fragment)
    应用索引查找来解决查问片段以查找符合条件的行.
  • <if>(condition, expr1, expr2)
    如果条件为真, 则计算为 expr1, 否则 为 expr2.
  • <is_not_null_test>(expr)
    验证表达式不为 NULL.
  • <materialize>(query fragment)
    应用了子查问的物化.
  • materialized-subquery.col_name
    A reference to the column col_name in an internal temporary table materialized to hold the result from evaluating a subquery.
  • <primary_index_lookup>(query fragment)
    应用主键查找来解决查问片段以查找符合条件的行.
  • <ref_null_helper>(expr)
    这是一个没有用户意义的外部优化器对象.
  • /* select#N */ select_stmt
    SELECT 与非扩大 EXPLAIN 输入中 N 的 id 值相关联.
  • outer_tables semi join (inner_tables)
    半连贯操作. inner_tables 显示未拉出的表. 请参阅第 8.2.2.1 节,” 应用半连贯转换优化 IN 和 EXISTS 子查问谓词 ”.
  • <temporary table>
    这示意为缓存两头后果而创立的外部长期表.

当某些表属于 const 或 system 类型时, 波及这些表中的列的表达式由优化器提前求值, 而不是显示语句的一部分. 然而, 应用 FORMAT=JSON 时, 某些 const 表拜访会显示为应用 const 值的 ref 拜访.

获取命名连贯的执行打算信息

临时用不上, 这部分地址: 获取命名连贯的执行打算信息

预计查问性能

在大多数状况下, 您能够通过计算磁盘寻道 (disk seeks) 次数来预计查问性能. 对于小型表, 通常能够在一次磁盘查找中找到一行 (因为索引可能已缓存). 对于更大的表, 您能够预计, 应用 B-tree 索引, 您须要这么屡次查找能力找到一行:

$$
\frac{log(row\\\_count)}{log(index\\\_block\\\_length / 3 * 2 / (index\\\_length + data\\\_pointer\\\_length))} + 1
$$

在 MySQL 中, 索引块通常为 1024 字节, 数据指针通常为 4 字节. 对于一个 500000 行的表, 键值长度为 3 个字节 (大小为 MEDIUMINT), 公式示意 $log(500000)/log(1024/3\*2/(3+4)) + 1= 4$ seeks.

该索引须要大概 $500000 \* 7 \* 3/2 = 5.2MB$ 的存储空间 (假如典型的索引缓冲区填充率为 2/3), 因而您可能在内存中有很多索引, 因而只须要一两次调用读取数据以查找行.

然而, 对于写入, 您须要四个查找申请来查找搁置新索引值的地位, 通常须要两次查找来更新索引并写入行.

后面的探讨并不意味着您的应用程序性能会以 $log(N)$ 的速度迟缓降落. 只有所有都被操作系统或 MySQL 服务器缓存, 随着表变大, 事件只会略微变慢. 在数据变得太大而无奈缓存后, 事件开始变得慢得多, 直到您的应用程序仅受磁盘搜寻 (减少 log N) 的束缚. 为防止这种状况, 请随着数据的增长而减少 key 的缓存大小. 对于 MyISAM 表, 键缓存大小由 key_buffer_size 零碎变量管制. 请参见第 5.1.1 节 配置服务器.

笔者的验证 Demo

没有刻意减少 Explain 的应用 Demo, 后续的开发中会找机会验证的.

原文地址:【MySQL 文档翻译】了解查问打算

欢送拜访我的博客: http://blog.duhbb.com/

退出移动版