面试官听到我对 explain 使用的回答竖起了大拇指

引言：在 Java 数据库操作中，理解 SQL 语句的执行计划是优化查询性能的关键之一。Explain 是一个重要的 SQL 语句，它能够显示数据库如何执行某个查询，帮助开发者识别性能瓶颈和优化查询。在面试中，被问到如何使用 Explain 以及如何分析其输出结果，是对开发者理解数据库优化的一个重要考核点。本文将带你深入了解 Explain 语句的使用方法和分析技巧，帮助你在面试和实际开发中，能够自信且清晰地阐述查询优化的工作原理和应用场景。

题目

面试官听到我对 explain 使用的回答竖起了大拇指

推荐解析

今天碰见一个面试官问我：用过 explain 吗？说说怎么分析的？

我：哈哈哈撞我枪口上了，让我来震惊你一下！

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

主要的属性

id

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

select_type

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

table

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

partitions

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

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;
  

possible_keys

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

key

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

key_len

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

rows

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

filtered

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

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，不再需要文件排序，因为索引已经覆盖了排序需求。

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

推荐文章和书籍

文章：https://zhuanlan.zhihu.com/p/86293659

书籍：《 Java 核心技术卷 I 》

欢迎交流

当谈到Explain 时，我们可以探讨以下几个问题：

1）如何判断查询是否使用了索引？

2）Explain 输出中的 type 列代表什么？哪些类型表示查询性能较好？

3）如何通过 Explain 输出中的 Explain 列优化查询？

这些问题将帮助我们深入了解 Explain 执行计划的概念、应用和原理，从而更好用来解决实际问题。