MySQL —— explain 查看执行计划与 MySQL 优化

explain 查看执行计划

使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。可以获得关于查询的执行计划的详细信息，分析查询语句或者表结构的性能瓶颈。在 5.6 以及以后的版本中，除了 select，其他比如 insert，update 和 delete 均可以使用 explain 查看执行计划，从而知道 mysql 是如何处理 sql 语句，分析查询语句或者表结构的性能瓶颈。

explain 的作用——查看执行计划

通过 explain+sql 语句可以知道如下内容：

1. 表的读取顺序（对应 id）；
2. 数据读取操作的操作类型（对应 select_type）；
3. 可以使用的索引（对应 possible_keys）；
4. 实际使用的索引（对应 key）；
5. 表之间的引用（对应 ref）
6. 每张表被优化器查询的行数 。（对应 rows）；

explain 查看执行计划返回信息详解

查看执行计划返回列如下：

表的读取顺序（id）

• id 列指示了各个表在查询中的读取顺序。对于联合查询，id 的值越大，表示该表的读取优先级越高。相同的 id 表示这些操作是同时进行的。

  • id 相同，执行顺序由上到下。如下：t1->t3->t2。

    

  • id不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。如下：t3->t1->t2。

    

  • id同时存在相同与不同。id 越大越先执行，id 相同从上到下顺序执行。

    

规则：

1. id 相同，执行顺序由上至下；
2. id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。

查询类型（select_type）

表示查询中每个选择的类型。主要用于区别普通查询，联合查询，子查询等的复杂查询。常见的值包括：

1. simple —— 简单的 select 查询，查询中不包含子查询或者 UNION 查询；
2. primary —— 查询中若包含任何复杂的子部分，最外层查询被标记；
3. subquery —— 在 select 或 where 列表中包含的子查询；
4. derived —— 在 from 列表中包含的子查询被标记为 derived（衍生），MySQL 会递归执行这些子查询，把结果放到临时表中。
5. union —— 如果第二个 select 出现在 UNION 之后，则被标记为 UNION；如果 union 包含在 from 子句的子查询中，外层 select 被标记为 derived。
6. union result：从 UNION 表获取结果的 SELECT。

数据库表名（table）

• 显示这一行的数据是关于哪张表的。

联接类型（type）

显示查询使用的连接方法，按照性能从好到坏的排序：system > const > eq_ref > ref > range > index > ALL。

1. system：表示表只有一行（即系统表，例如 information_schema 中的表），是最优的连接类型。

   在这种情况下，MySQL 不需要进行任何查找，直接返回结果。

2. const：通过主键或唯一索引在表中找指定常量值的一行记录。const （常量值）与 primary key 或者 unique 索引比较，最多只匹配一行数据。

   -- 找到一行数据，where 条件使用 =
   -- id为主键
   EXPLAIN SELECT * FROM tb1 WHERE id = 1; 
   

3. eq_ref：通过主键或唯一索引扫描，通常在连接查询中使用，每个匹配的行只返回一行数据。

   -- 找到一行数据，where 条件使用 =
   -- 在多表连接中，使用主键或唯一索引进行连接
   EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;
   

4. ref：通过非唯一性索引扫描，返回匹配的多个行，本质上也是一种索引访问，可能会找多个符合条件的行，属于查找和扫描的混合体。

   -- 找到多行数据，where 条件使用 =，也就是有多行满足等于条件的行
   -- ategory_id 是一个非唯一索引
   EXPLAIN SELECT * FROM articles WHERE category_id = 5;
   

5. range：使用索引进行范围扫描。检索给定范围的行，返回一个范围的行。一般就是 where 语句中出现了 between，<，>，in 等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。

   -- 找到多行数据，where 条件使用范围条件
   -- price  是一个索引；
   EXPLAIN SELECT * FROM products WHERE price BETWEEN 10 AND 20;
   

6. index： 全索引扫描，表示扫描整个索引而不是表。all 与 index 区别为 index 类型只遍历索引树。通常比全表扫描快，因为索引文件比数据文件小很多。（相当于索引覆盖，只在索引文件查找便能获取结果）

   -- name 是一个索引；
   EXPLAIN SELECT * FROM products WHERE name LIKE 'A%';
   

7. all：全表扫描，效率最低。遍历全表以找到匹配的行。（不使用索引或索引失效）

   注意：一般保证查询至少达到 range 级别，最好能达到 ref。

   SELECT * FROM users;
   

可用的索引（possible_keys）

• 显示可能应用在这张表中的索引。查询涉及的字段上若存在多个索引，则索引将被列出，但不一定被查询实际使用。如果该字段为 NULL，表示没有可用的索引。（可能会列出如 idx_name、idx_age 等）

实际使用的索引（key）

• 显示 MySQL 实际使用的索引。如果该字段为 NULL，表示查询没有使用索引。查询中如果使用覆盖索引，则该索引和查询的 select 字段重叠。（如果查询使用了 idx_age 索引，则显示为 idx_age）
  

使用的索引长度（key_len）

• 表示 MySQL 在查询中使用的索引的字节数，帮助评估索引选择的效率。在不损失精度的情况下，长度越短越好。如果键是 NULL，则长度为 NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。如果 key_len 为 4，表示使用的索引长度为 4 字节。
  

表之间的引用（ref）

• 指出与哪个列或常量进行比较，通常用于指示索引的连接条件。即在索引查找过程中，用于与索引相匹配的列或常量。通常用于说明如何通过某个索引查找相关的记录。
• ref 字段可能包含以下几种值：
  • 常量：如果查询条件中使用了常量，ref 字段的值将是 const，表示该常量用于查找索引。
  • 列名：如果是通过其他列进行查找，ref 字段会显示相应的列名。例如，如果通过主键或其他索引列查找，可能会显示为表名和列名（如 student.id）。
  • NULL：如果没有使用索引进行查找，该字段将显示为 NULL。
• 如；