MySQL —— explain 查看执行计划与 MySQL 优化_explain查看执行计划-优快云博客

文章目录

explain 查看执行计划

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

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

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

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

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

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

表的读取顺序（id）

id 列指示了各个表在查询中的读取顺序。对于联合查询，id 的值越大，表示该表的读取优先级越高。相同的 id 表示这些操作是同时进行的。
- id 相同，执行顺序由上到下。如下：t1->t3->t2。
- id不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。如下：t3->t1->t2。
- id同时存在相同与不同。id 越大越先执行，id 相同从上到下顺序执行。

规则：

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

查询类型（select_type）

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

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

数据库表名（table）

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

联接类型（type）

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

system：这是 const 类型的特例，仅当表中只有一行数据（且为系统表，例如 information_schema 中的表）时出现。是最优的连接类型，无需扫描，直接获取数据。

在这种情况下，MySQL 不需要进行任何查找，直接返回结果。
const：通过主键索引或唯一索引查询，且匹配条件是常量值，最多返回一行数据。const （常量值）与 primary key 或者 unique 索引比较，最多只匹配一行数据。性能极佳，只需一次索引查找即可定位数据。
```
-- 找到一行数据，where 条件使用 =
-- id为主键
EXPLAIN SELECT * FROM tb1 WHERE id = 1; 
```

eq_ref：在多表连接中，对于前表的每一行，后表通过主键或唯一索引返回唯一一行匹配的数据。常见于 JOIN 语句中被连接表的连接条件是主键或唯一索引的场景。

-- 找到一行数据，where 条件使用 =
-- 在多表连接中，使用主键或唯一索引进行连接
-- users表的 type 可能为 eq_ref（对于 orders表的每一行，users表有唯一匹配）。
EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;

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

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

index_merge：MySQL 对同一个表使用多个索引，并将多个索引的结果合并（如 AND 或 OR 组合），以优化查询，依赖于合并的索引效率和结果集大小。
```
-- id 和 name 均有索引
EXPLAIN SELECT * FROM user WHERE id = 100 OR name = '张三'; 
```
range：使用索引进行范围扫描。检索给定范围的行，返回一个范围的行。一般就是 where 语句中出现了 between，<，>，in 等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。
```
-- 找到多行数据，where 条件使用范围条件
-- price  是一个索引；
EXPLAIN SELECT * FROM products WHERE price BETWEEN 10 AND 20;
```
index：全索引扫描，表示扫描整个索引而不是表。all 与 index 区别为 index 类型只遍历索引树。通常比全表扫描快，因为索引文件比数据文件小很多。（相当于索引覆盖，只在索引文件查找便能获取结果）
```
-- name 是一个索引；
EXPLAIN SELECT * FROM products WHERE name LIKE 'A%';
```
all：全表扫描，效率最低。遍历全表以找到匹配的行。（不使用索引或索引失效）

注意：一般保证查询至少达到 range 级别，最好能达到 ref。
```
SELECT * FROM users;
```