MySQL EXPLAIN，数据库调优的秘密通道

EXPLAIN 是 MySQL 中一个非常有用的工具，它用于分析 SQL 查询的执行计划。通过 EXPLAIN，你可以获取 MySQL 是如何准备执行你的 SQL 语句的，包括使用的索引、连接类型、扫描的行数等信息。这些信息对于优化查询性能、识别性能瓶颈至关重要。

使用方法

EXPLAIN SELECT * FROM your_table WHERE some_column = 'some_value';

输出字段

1. id:
   查询的标识符。如果查询包含多个子查询或联合查询，MySQL 会为每个子查询分配一个唯一的 id。
   如果 id 相同，表示这些查询是并行执行的。
   如果 id 不同，通常是外层查询的 id 较小，内层查询的 id 较大。
2. select_type:
   查询的类型，常见的类型有：
   • SIMPLE: 简单的 SELECT 查询，不使用 UNION 或子查询。
   • PRIMARY: 查询中最外层的 SELECT。
   • UNION: UNION 中的第二个或后续的 SELECT 语句。
   • DEPENDENT UNION: UNION 中的第二个或后续的 SELECT 语句，依赖于外部查询。
   • SUBQUERY: 子查询中的第一个 SELECT。
   • DEPENDENT SUBQUERY: 子查询，依赖于外部查询。
   • DERIVED: 派生表的 SELECT（子查询在 FROM 子句中）。
3. table:
   显示这一行数据是关于哪张表的。
4. partitions:
   匹配的分区。
5. type:
   连接类型，表示 MySQL 在找到所需行时使用的访问方法。常见类型有：
   • ALL: 全表扫描，性能最差。
   • index: 全索引扫描（索引的每一行都会被扫描）。
   • range: 使用索引选择给定范围的行。
   • ref: 使用非唯一性索引或唯一性索引的前缀扫描来查找单个匹配行。
   • eq_ref: 使用唯一性索引查找单个匹配行。
   • const, system: 表中最多有一个匹配行，通常在主键或唯一索引中查找。
   • NULL: 不用访问表或索引，例如从子查询中返回的行。
6. possible_keys:
   显示可能应用在这张表上的索引。
7. key:
   实际使用的索引。如果为 NULL，则表示没有使用索引。
8. key_len:
   使用的索引的长度。在某些情况下，不是索引的全部部分都会被使用。
9. ref:
   显示索引的哪一列或常量被用于查找值。
10. rows:
    估计为了找到所需的行而要检查的行数。这是一个估计值，不一定完全准确。
11. filtered:
    表示返回结果的行占开始查找行的百分比。
12. Extra:
    包含不适合在其他列中显示但对执行计划非常重要的额外信息。常见值有：

• Using where: 使用 WHERE 过滤。
• Using temporary: 使用临时表来存储中间结果。
• Using filesort: MySQL 需要额外的步骤来执行排序。
• Using index: 只通过索引树读取数据（覆盖索引）。
• Impossible WHERE: WHERE 子句中的条件永远为 false，不能返回任何行。
• Distinct: MySQL 正在查找不同的值。

基本使用

假设有一个名为 employees 的表，包含 id, name, department_id, salary 等列，并且有一个索引 idx_department_id 在 department_id 列上。

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

输出

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_department_id	idx_department_id	4	const	100	Using where

• type 为 ref，表示使用了非唯一索引查找。
• key 为 idx_department_id，表示实际使用的索引。
• rows 为 100，表示估计需要检查的行数。

案例

定义

我们有一个名为 employees 的表，包含以下列：id（主键），name，department_id，salary。我们想要查询 department_id 为 10 的所有员工信息。

-- 假设 employees 表已经存在并且包含数据
EXPLAIN
SELECT *
FROM employees
WHERE department_id = 10;

输出

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_department_id	idx_department_id	4	const	100	Using where

• id: 查询的标识符，这里只有一个查询，所以为 1。
• select_type: 查询类型，SIMPLE 表示简单的 SELECT 查询。
• table: 查询的表名，这里是 employees。
• type: 连接类型，ref 表示使用非唯一索引或唯一索引的前缀来查找匹配的行。
• possible_keys: 可能使用的索引，这里是 idx_department_id。
• key: 实际使用的索引，这里是 idx_department_id。
• key_len: 使用的索引的长度。
• ref: 显示索引的哪一列或常量被用于查找值，这里是 const 表示是一个常量值。
• rows: 估计为了找到所需的行而要检查的行数。
• Extra: 额外的信息，这里是 Using where 表示使用了 WHERE 子句进行过滤。

全表扫描

现在我们想要查询所有员工的姓名，并且我们没有在 name 列上创建索引。

-- 假设 employees 表已经存在并且包含数据，且 name 列上没有索引
EXPLAIN
SELECT name
FROM employees;

输出

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ALL	NULL	NULL	NULL	NULL	1000000	NULL

• type: 这里是 ALL，表示全表扫描，因为 name 列上没有索引。
• possible_keys 和 key: 都是 NULL，因为没有可用的索引。
• rows: 估计需要扫描的行数，这里是一个较大的数，表示全表扫描。

使用覆盖索引

假设我们现在在 name 和 salary 列上创建了一个复合索引，并且我们想要查询这两个列的值。

-- 首先创建复合索引（如果尚未存在）
CREATE INDEX idx_name_salary ON employees (name, salary);-- 然后使用 EXPLAIN 查看查询计划
EXPLAIN
SELECT name, salary
FROM employees
WHERE name = 'John Doe';

输出

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_name_salary	idx_name_salary	767	const	1	Using index

• type: 这里是 ref，表示使用非唯一索引查找。
• key: 使用了我们刚刚创建的复合索引 idx_name_salary。
• Extra: Using index 表示只通过索引树读取数据（覆盖索引），因为查询的列 name 和 salary 都包含在索引中。