MySQL系列二（Explain执行计划详解、执行计划的局限性）

1. Explain执行计划

EXPLAIN是MySQL的一条关键字，用于分析查询语句的执行计划。通过执行EXPLAIN语句，可以获得查询的详细信息，包括使用的索引、表的读取顺序、连接操作的类型等。这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果，所以可能会存在分析错误的情况，也就是存在执行计划并不是最优的情况。

通过explain关键字知道MySQL是如何执行SQL查询语句的，分析select 语句的性能瓶颈，从而改进我们的查询。

1.1 id

查询块的唯一标识符

• id值不同：id值越大优先级越高，越先被执行；
• id值相同：从上往下依次执行；
• id列为null：表示这是一个结果集，不需要使用它来进行查询。

1.2 select_type

查询的类型，例如简单查询、子查询、联接查询等。

• simple：简单查询。查询不包含子查询和union
• primary：复杂查询中最外层的 select
• subquery：包含在 select 中的子查询（不在 from 子句中）
• derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含
  义）
• union：在 union 中的第二个和随后的 select

1.3 table

查询涉及的表名。

1.4 type

表示MySQL查询使用的连接类型，例如ALL（全表扫描）、index（索引扫描）等。表示MySQL在表中如何查找，查找数据行记录的大概范围。新能从最差到最优依次是： ALL<index<range<ref<eq_ref<const<system<NULL（从左到右，性能从差到好），一般来说，得保证查询达到range级别，最好达到ref。

• NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
• const、system：当MySQL对查询某部分进行优化，并转换为一个常量时。用于primary key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取一次，速度比较快。system是const的特例，当查询的表只有一行的情况下，是system。
• eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。
• ref：相比qs_ref，不使用唯一索引，可能会查到多个符合条件的行。
• range：范围扫描通常出现在in()、between、>、<、>=等操作中。使用一个索引来检索给定范围的行。
• index： 扫描全索引就能拿到结果，一般是扫描某个非主键索引（二级索引），这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子结点遍历和扫描，速度还是比较慢的。index与ALL区别为index类型只遍历索引树。
• ALL：全表扫描将遍历全表以找到匹配的行。

1.5 possible_keys

查询可能使用哪些索引来查找。explain时可能会出现possible_keys有值，而key显示NULL的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的所有来提高查询性能，然后用explain查看效果。

1.6 key

显示MySQL实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。如果强制MySQL使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index。

1.7 key_len

实际使用到的索引的字节数，通过key和key_len就可以算出使用了索引中的哪些列。

key_len计算规则如下：

1. 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节。char(n)：如果存汉字长度就是 3n 字节，varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串。
2. 数值类型：tinyint：1字节，smallint：2字节，int：4字节，bigint：8字节
3. 时间类型：date：3字节，timestamp：4字节，datetime：8字节
4. 如果字段允许为 NULL，需要1字节记录是否为 NULL

1.8 ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名。

1.9 rows

表示MySQL根据表统计信息及索引选用情况，大致估算此处查询需要读取的行数，不是精确值。

1.10 Extra

MySQL的EXPLAIN语句用于分析查询语句的执行计划，并提供有关查询执行的详细信息。其中，EXPLAIN的输出结果中的"Extra"列提供了关于查询执行过程中的额外信息。以下是一些常见的MySQL EXPLAIN Extra的解释：

1. “Using index”：表示查询使用了覆盖索引。当查询所需的列都包含在索引中时，MySQL可以直接使用索引来返回结果，而无需再访问表的数据行。这可以提高查询性能。
2. “Using where”：表示在执行查询时，MySQL使用了WHERE子句进行过滤。这意味着MySQL会在索引之后进行进一步的行级别过滤操作，以获取满足条件的结果。
3. “Using temporary”：表示查询需要使用临时表来存储中间结果。通常在排序、分组或连接操作中会出现这个标记。在执行这些操作时，MySQL可能需要创建一个临时表来存储中间结果集。
4. “Using filesort”：表示查询需要进行文件排序。当MySQL无法利用索引的排序功能时，它将使用文件排序算法进行排序操作。这可能会对性能产生影响，特别是在处理大量数据时。当查询中包含ORDER BY子句，并且无法使用索引的排序功能时，MySQL会选择使用文件排序。文件排序涉及将查询结果读入临时文件中，然后对文件中的数据进行排序操作。这是因为MySQL的内存缓冲区有限，无法容纳整个结果集，因此需要使用磁盘上的临时文件来存储和排序数据。

使用文件排序可能会对性能产生负面影响，因为它涉及磁盘I/O操作和额外的排序过程。磁盘I/O是相对较慢的操作，而排序过程本身也需要消耗一定的时间和资源。为了优化查询中的文件排序，可以考虑以下几个方面：

1. 确保相关列上存在适当的索引：尽量使用可以覆盖ORDER BY子句的索引，以避免进行文件排序操作。
2. 调整查询语句和索引：通过优化查询语句或调整索引，使MySQL能够更好地利用索引的排序功能。
3. 增加内存缓冲区大小：通过调整MySQL的相关配置参数，增加内存缓冲区的大小，使MySQL能够在内存中处理更多的数据，减少使用文件排序的需求。
4. 优化查询逻辑：如果可能的话，可以重新考虑查询的逻辑，尝试减少或优化排序操作的需求。

5. “Using join buffer (Block Nested Loop)”：表示查询使用了连接缓冲区，通常在连接操作中会出现。连接缓冲区用于存储连接操作的中间结果。"Block Nested Loop"表示使用了块嵌套循环连接算法。
6. “Using index condition”：表示查询使用了索引条件过滤。MySQL可以在索引上执行部分WHERE子句的条件判断，以减少不必要的数据访问。这可以提高查询性能。
7. “Using index for group-by”：表示在GROUP BY操作中使用了索引。当GROUP BY的列包含在索引中时，MySQL可以利用索引进行分组操作，避免额外的排序操作。
8. “Using index for distinct”：表示在DISTINCT操作中使用了索引。当DISTINCT的列包含在索引中时，MySQL可以利用索引来查找并去重结果，避免额外的排序操作。

这些是一些常见的MySQL EXPLAIN Extra的解释。在面试中，理解EXPLAIN输出中的"Extra"列的含义，可以帮助你分析查询的执行计划和性能瓶颈。

2. Explain执行计划的局限性

1. EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
2. EXPLAIN不考虑各种Cache；
3. EXPLAIN不能显示MySQL在执行查询时所作的优化工作；
4. 部分统计信息是估算的，并非精确值；
5. EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看。