用好 explain 妈妈再也不用担心我的 SQL 慢了

大家好，我是聪，一个乐于分享的小小程序员。在不久之前我写了一个慢 SQL 分析工具，可以用来分析 Java Mybatis 项目的 SQL 执行情况，其中刚好涉及到了 explain 的使用。感兴趣的可以了解一下。

Github 地址⭐：https://github.com/lhccong/sql-slow-mirror

那么开始我们今天的主题吧，今天的聪碰见了一个当面试官的朋友问我，用过 explain 吗？说说怎么分析的？

聪：一脸正经的回答道💡

聪：你好面试官，我当然用过 explain，我平时都会那它去查看 SQL 语句是否还能优化。接下来我从主要属性跟实际例子来讲解：

主要的属性

1） 🌱 id

查询中每个 SELECT 子句的标识符。简单查询的 id 通常为 1，复杂查询（如包含子查询或 UNION）的 id 会有多个。

2） 🌱 select_type

描述查询的类型。比如：简单查询显示为 SIMPLE，子查询显示为 SUBQUERY，UNION 中的第二个和后续查询显示为 UNION。

3）🌱 table

表名称这个就不用再详细解释了吧哈哈。

4） 🌱 partitions

表示查询涉及到的分区。如果你有使用分区表的话才需要关注此字段。

5） 🌱 type（重点记忆⭐）

表示访问的类型，这里也可以看出你的 SQL 的性能。可能的值从最好到最差包括：system、const、eq_ref、ref、range、index、ALL。其中 ALL 表示全表扫描，效率最低。

• system：

  表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。

• const：

  表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较，以下是一个使用主键查找的例子：

  EXPLAIN SELECT * FROM employees WHERE employee_id = 12345;
  

• eq_ref：

  表示对于每个来自前一张表的行，MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况下，例子如下：

  EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
  

• ref：

  MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引），例子如下使用了非唯一索引进行查找：

  EXPLAIN SELECT * FROM employees WHERE department_id = 5;
  

• range：表示 MySQL 会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中（如 BETWEEN、>, <, >=, <=）。下面是范围查询：

  EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
  

• index：表示 MySQL 扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引。以下是使用索引扫描例子：

  EXPLAIN SELECT name FROM employees;
  

• all（性能最差）：表示 MySQL 需要扫描表中的所有行，即全表扫描。这通常出现在没有索引的查询条件中。以下是全表扫描例子：

  EXPLAIN SELECT * FROM employees;
  

6） 🌱 possible_keys

表示查询可能使用的索引列表。

7） 🌱 key

实际使用索引的长度。如果没有使用索引，该字段显示为 NULL。

8） 🌱 key_len

这个字段表示使用的索引的长度。该值是根据索引的定义和查询条件计算的。

9） 🌱 rows

MySQL 会估计为了找到所需的行，需要读取的行数。该值是一个估计值，不是精确值。

10）🌱 filtered

显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。

11）🌱 Extra

额外信息，如 Using index（表示使用覆盖索引）、Using where（表示使用 WHERE 条件进行过滤）、Using temporary（表示使用临时表）、Using filesort（表示需要额外的排序步骤）。

看完这个是不是一目了然了捏，那么接下来跟着我看看实际的分析例子吧！！

实际例子🌰

1.创建 employees 表

CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,salary DECIMAL(10, 2),hire_date DATE,INDEX (department_id)
);

我们要执行以下查询来查找部门 ID 为 5 且薪水在 50000 到 100000 之间的员工，并按薪水降序排序：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

2.我们先使用 explain 分析计划进行分析：

EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

输出结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	ref	department_id	department_id	4	const	5000	20.00	Using where; Using filesort

3.分析执行计划

从执行计划中看出，type 为 ref，表示使用了 department_id 索引，这是个非唯一索引。key 为 department_id 这个索引，而且 rows 为 5000，表示扫描了 5000 行匹配的 department_id = 5 的条件。从 Extra 看出在应用 WHERE 条件后，还需要进行文件排序来满足 ORDER BY 子句。

4.找出问题

尽管查询使用了索引，但由于索引不完全覆盖查询的条件和排序，查询需要进行额外的文件排序。这可能会导致性能瓶颈，特别是在结果集较大时。

5.优化解决它！

创建复合索引

创建一个包含 department_id 和 salary 的复合索引，这样可以覆盖查询的 WHERE 和 ORDER BY 条件：

CREATE INDEX idx_department_salary ON employees (department_id, salary);

复合索引可以使查询在扫描 department_id 列时，同时按 salary 列排序，避免额外的文件排序。

再次执行计划分析

优化后的 EXPLAIN 输出如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	range	idx_department_salary	idx_department_salary	5	NULL	500	100.00	Using where

6.分析优化后的结果

从新的 EXPLAIN 输出中可以看出：

• type: range，表示使用范围扫描，这是个相对高效的访问类型。
• key: idx_department_salary，表示实际使用了复合索引。
• rows: 500，估计读取的行数减少了，因为索引更精确地覆盖了查询条件。
• Extra: 仅显示 Using where，不再需要文件排序，因为索引已经覆盖了排序需求。

是不是分析起来很简单咧，完结撒花！！！！，除了新增联合索引的方式，你们还知道什么优化策略吗？