【MySQL】2-MySQL索引P2-执行计划

欢迎来到啾啾的博客🐱。
记录学习点滴。分享工作思考和实用技巧，偶尔也分享一些杂谈💬。
有很多很多不足的地方，欢迎评论交流，感谢您的阅读和评论😄。

[MySQL]2-MySQL索引这一篇关于执行计划的部分，因为有检索需求所以单独列出来。

EXPLAIN

explain output 执行计划输出解释

• 官方文档
  https://dev.mysql.com/doc/refman/5.7/en/
  https://dev.mysql.com/doc/refman/5.7/en/explain.html
  https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

官方解释是：EXPLAIN语句提供MySQL如何执行语句的信息。
可以查看应在哪些表添加索引以通过索引查找行来加快语句执行速度；
检查是否以最佳顺序连接表（如对SQL有理解，用户可以使用 SELECT STRAIGHT_JOIN指定连接顺序）。

​

explain output columns 列名	json name 使用format=json时输出中显示的效属性名称	meaning含义	详细解释
id	select_id	The SELECT identifier select标识符	这是查询中 SELECT 的顺序号。 id 号每个号码，表示一趟独立的查询 , 一个 sql 的查询趟数越少越好 如果该行引用其他行的并集结果，则值可以是 NULL 。在这种情况下， table 列显示一个类似于 的值，以指示该行引用具有 id 的 M 和 N 值的行并集。 可以查看执行顺序。 多表联查，id值越大，表查询越先执行。 id值相同，看输出顺序
select_type	None 无	The SELECT type SELECT 类型	查询类型，说明单表查询、关联查询、子查询等信息 ​编辑
table	table_name	The table for the output row 输出行所引用的表	表名。 explain输出的每条记录都对应着都某个单表的访问方法。 M,N>该行指的是具有 M 和 N 的 id 值的行的并集 N>指的是具有 id 值为 N 的行的派生表结果。例如，派生表可能由 FROM 子句中的子查询生成 N>指的是具有 id 值为 N 的行的物化子查询的结果
partitions	partitions	The matching partitions 匹配分区	显示分区，对于非分区表值为null
type	access_type	The join type 连接类型	https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types 描述了如何连接表。以下连接类型按照最好到最差排列： system 该表只有一行（=系统表）。const连接类型的特殊情况 const 该表最多一行匹配，该行在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器其余部分视为常数。const 表非常快，因为它们只读取一次。 eq_ref当索引的所有部分都被连接使用且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，使用此类型。 通常发生在连表查询中，关联的条件是某一张表的主键或者UNIQUE唯一非空索引。 ref ref 可用于使用 = 或 操作符比较的索引列。如果连接不能根据键值选择单行，则使用 ref 。 满足索引最左匹配原则，且不走主键或者唯一非空索引则为ref，非聚簇索引查询一般都为ref。 fulltext 使用fulltext索引进行连接操作 ref_or_null 类似于 ref ，但增加了 MySQL 对包含 NULL 值的行的额外搜索。这种连接类型优化最常用于解决子查询。 在ref基础上增加null值查询则为这个。 index_merge 此连接类型表示使用了索引合并优化。 一般是单表多个索引条件的查询，将多个搜索结果合并为一个，统一回表降低查询代价。 unique_subquery这种类型替换了以下形式的某些 IN 子查询中的 eq_ref。unique_subquery 是一个索引查找函数，它完全替换子查询以提高效率。 index_subquery 此连接类型类似于 unique_subquery 。它替换 IN 子查询，但适用于以下形式的子查询中的非唯一索引： range 仅检索给定范围内的行，使用索引选择行. key_len 包含使用的最长键部分。 ref 列是针对此类型的 NULL 当键列使用任何 = 、 <> 、 > 、 >= 、 < 、 、 BETWEEN 、 LIKE 或 IN() 运算符与常量进行比较时，可以使用 range index index 连接类型与 ALL类似，扫描索引树 ALL 全表扫描，尽量避免
possible_keys	possible_keys	The possible indexes to choose 可能的索引选择	表示 MySQL 可以从其中选择以查找此表中行的索引
key	key	The index actually chosen 实际选择的索引	表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用 possible_keys 索引之一来查找行，则该索引被列为键值。
key_len	key_length	The length of the chosen key 所选索引的长度	表示 MySQL 决定使用的键的长度（即：字节数）。 帮你检查是否充分的利用上了索引，值越大越好,主要针对于联合索引，有一定的参考意义。
ref	ref	The columns compared to the index 列与索引的比较	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	rows	Estimate of rows to be examined 估计要检查的行数	表示 MySQL 认为必须检查的行数以执行查询。对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确。 值越小越好
filtered	filtered	Percentage of rows filtered by table condition 行过滤比例	表示由表条件过滤的表行估计百分比
Extra	None 无	Additional information 附加信息	https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information 查询type为const时为null distinct MySQL 正在寻找不同的值，因此它在找到第一个匹配的行之后，就会停止为当前行组合搜索更多行。 Full scan on NULL key这发生在子查询优化中，作为当优化器无法使用索引查找访问方法时的后备策略。 Impossible HAVING HAVING 子句始终为假，无法选择任何行。 Impossible WHERE WHERE 子句始终为假，无法选择任何行。 Using filesortMySQL 必须进行额外遍历来确定如何以排序顺序检索行。排序是通过遍历所有行（根据连接类型）并存储所有匹配 WHERE 子句的行的排序键和行指针来完成的。然后对这些键进行排序，并按排序顺序检索行。请参阅第 8.2.1.14 节，“ORDER BY 优化”。 using index 该列信息仅通过索引树中的信息从表中检索，无需额外查找实际行。当查询仅使用单个索引中的列时，可以使用此策略。 using index condition 表通过访问索引元组并首先测试它们来确定是否读取完整的表行。这样，索引信息被用来延迟（“推下”）读取完整的表行，除非这是必要的。 Using index for group-by 与 Using index 表访问方法类似， Using index for group-by 表示 MySQL 找到一个可以用来检索 GROUP BY 或 DISTINCT 查询的所有列的索引，而无需对实际表进行任何额外的磁盘访问 Using MRR使用多范围读取优化策略读取 Using temporary为了解决查询，MySQL 需要创建一个临时表来存储结果。这通常发生在查询包含 GROUP BY 和 ORDER BY 子句，这些子句以不同的方式列出列时。 Using where 一个 WHERE 子句用于限制与下一个表匹配的行或发送给客户端的行。除非你明确打算检索或检查表中的所有行，否则如果 Extra 值不是 Using where ，并且表连接类型是 ALL 或 index ，你的查询可能有问题。

重点

type列（连接类型，判断索引使用情况）

结果值从最好到最坏依次是： system > const > eq_ref> ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。
range效率不高
index和all都是全表扫描

extra （额外信息，看SQL执行情况）

using index 覆盖索引 查询的数据都有索引

using index condition 索引下推

using where 所有过滤动作都由server层处理，没有索引（需要优化）

using MRR

using join buffer （BAK|BNL）

using union（indexs） 使用联合索引，多个查询条件都是索引列会发生索引合并

usring temporaray 使用了临时表，尽量使用索引，而非临时表，例如group by、union、distnct语句会使用到临时表

using filesort 通常出现在order by语句，查询之后需要额外进行排序。可以将条件与排序字段组成联合索引，由于有联合索引，排序字段本身就是有序的，不需要额外排序。

总结：避免临时表，尽量覆盖索引，像排序和函数什么的尽量避免

rows * filtered

rows列与filtered列通常与type列一起来看。

通常希望 rows * filtered的值越小越好，rows尽量小，filtered尽量高。

只要rows小就是好

rows:越小说明需要检查的行越少，即扫描的越少，语句越精准

filtered:越大说明扫描的行都被保留了，说明效率很高。

两者的值越小，说明性能越高。

异常情况：rows大，filtered低，说明有过滤，但是初始扫描行数多了，需要优化索引，联合索引最左侧选择性不高就可能出现这个问题。