【MySQL 文档翻译】理解查询计划

eq_ref (直接查询主键或者非空索引)
对于先前表中的每个行组合, 从该表中读取一行. 除了 system 和 const 类型, 这是最好的连接类型. 当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时就是这种类型.

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 索引 (换句话说, 如果连接不能基于键值选择单行), 则是

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;

ref_or_null(相比

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

请参阅第 8.2.1.15 节 IS NULL 优化.

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 可以使用此连接类型.

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 

假设这 (c1, c2) 是一个覆盖索引. 使用此索引, c1

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

在这种情况下, 第一个索引行 c1 = 10 包含最小值 c2 . 只需读取一行即可生成返回的行.
对于维护每个表的精确行数的存储引擎 (例如 MyISAM, 但不是 InnoDB), 对于缺少

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 值不是均匀分布的.

最初, 在执行任何优化之前, 该 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)

因为对于每个表

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

现在

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 倍. 这个版本在几秒钟内执行.

可以进行第二次更改以消除

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 假定

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 通过将产品与查询返回的实际行数进行比较, 检查这些数字是否更接近事实. 如果数字完全不同, 您可能会通过

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` ,
          (` test` .` t1` .` a` ,` test` .` t1` .` a` in
         (  (/* select#2 */ select ` test` .` t2` .` a`
         from ` test` .` t2` where 1 having 1 ),
          (` test` .` t1` .` a` in
          on 
         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 显示的扩展输出中的特殊标记:

• 获取命名连接的执行计划信息
  估计查询性能
  在大多数情况下, 您可以通过计算磁盘寻道 (
  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/
  好了，本文到此结束，带大家了解了《【MySQL 文档翻译】理解查询计划》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！