MySQL中EXPLAIN详细讲解

好的，我会进一步详细讲解 EXPLAIN 语句的每个字段，并深入说明它们的意义和作用，帮助你更全面地理解 MySQL 查询的执行计划。

EXPLAIN 语句的字段详解：

1. id：查询标识符

• 作用：id 表示查询计划中的唯一标识符，它主要用于区分不同的查询步骤。在复杂的查询中，尤其是包含子查询、联合查询等，id 用于标识每个子查询和查询部分的顺序。

  • 基本规则：

    • 对于简单查询，id 会是 1，表示查询是单一的。
    • 对于包含子查询的查询，id 会标记主查询和子查询的执行顺序，id 小的先执行。
    • id 值相同的行表示它们属于同一个查询块（子查询）。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

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

  • 在包含多个查询的情况下：

    sql

    复制代码

    EXPLAIN SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 1);

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	PRIMARY	orders	ref	order_id_idx	order_id_idx	4	const	50	Using where
    2	SUBQUERY	order_items	ref	product_idx	product_idx	4	const	100	Using where

    • 这里 id 为 1 的是主查询，而 id 为 2 的是子查询，id 越小的先执行。

2. select_type：查询类型

• 作用：select_type 用来标识查询的类型。这个字段告诉我们查询中包含的操作类型，例如主查询、子查询、联合查询等。

  • 常见值：

    • SIMPLE：简单查询，没有子查询。
    • PRIMARY：主查询，复合查询中的主查询部分。
    • UNION：联合查询的第二个或后续查询部分。
    • DEPENDENT UNION：依赖于外部查询的联合查询。
    • SUBQUERY：子查询。
    • DEPENDENT SUBQUERY：依赖于外部查询的子查询。
    • DERIVED：派生表，即子查询作为临时表。
    • MATERIALIZED：物化子查询（MySQL 8.0 引入），表示子查询的结果被缓存了。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

    结果：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	orders	ref	customer_idx	customer_idx	4	const	10	Using where

    • select_type = SIMPLE，表示这是一个简单查询。

3. table：查询的表

• 作用：table 表示当前操作涉及的表名。如果查询涉及多个表（例如 JOIN 查询），则会列出每个表。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	4	NULL	500	Using where

    • table = employees，查询的表是 employees 表。

4. type：连接类型

• 作用：type 显示了 MySQL 执行查询时表之间的连接方式。这个字段是查询效率的重要标志，连接类型越低效，查询速度越慢。

  • 常见值：

    • ALL：全表扫描，效率最低。
    • index：索引扫描，但仍然扫描索引的每一行，效率高于全表扫描。
    • range：范围扫描，使用索引范围查找。
    • ref：非唯一索引扫描，基于某个字段的索引查找。
    • eq_ref：对于每一行，查询仅使用唯一索引进行查找，通常用于连接操作。
    • const：查询条件能在查询执行前确定，效率最高。
    • system：表只有一行数据，等于 const，非常高效。
    • NULL：表示没有表参与查询，通常出现在系统表查询中。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	4	NULL	500	Using where

    • type = range，表示使用了范围扫描，效率较高。

5. possible_keys：可能使用的索引

• 作用：possible_keys 列出 MySQL 查询中可能使用的所有索引。MySQL 会根据查询条件（WHERE）和表结构自动选择最合适的索引。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	4	NULL	500	Using where

    • possible_keys = dept_salary，表示查询可能使用 dept_salary 索引。

6. key：实际使用的索引

• 作用：key 表示 MySQL 实际使用的索引。MySQL 可能选择一个或多个索引来优化查询，key 显示了实际使用的索引名称。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	4	NULL	500	Using where

    • key = dept_salary，表示查询实际使用了 dept_salary 索引。

7. key_len：索引长度

• 作用：key_len 显示 MySQL 使用的索引的长度（单位为字节）。这个值帮助你了解查询操作使用了多少字节来扫描索引。通常情况下，key_len 反映了索引中使用的字段个数。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	8	NULL	500	Using where

    • key_len = 8 表示查询使用了一个长度为 8 字节的索引。

8. ref：连接条件

• 作用：ref 表示连接时的条件，或者表示使用某个常量或字段与表中的列进行匹配。这个字段通常用于多表连接查询，表示连接条件的值。

  • 示例：

    sql

    复制代码

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

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	ref	dept_id_idx	dept_id_idx	4	const	500	Using where
    2	SIMPLE	departments	eq_ref	PRIMARY	PRIMARY	4	e.department_id	1	NULL

9. rows：扫描的行数

• 作用：rows 估算 MySQL 查询时扫描的行数。这个值是一个估算值，基于表的大小、索引的选择和查询条件的复杂性。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	4	NULL	500	Using where

    • rows = 500，表示 MySQL 预计扫描 500 行数据。

10. Extra：附加信息

• 作用：Extra 列包含查询执行过程中附加的操作信息。它为优化查询提供了额外的信息，帮助我们判断是否有进一步优化的空间。

  • 常见值：

    • Using where：查询使用了 WHERE 条件过滤。
    • Using temporary：查询需要使用临时表（例如在 GROUP BY 或 ORDER BY 中）。
    • Using filesort：查询使用了文件排序，可能意味着性能问题。
    • Using index：查询仅使用索引，不需要扫描表。
    • Using join buffer：使用了连接缓存。

  • 示例：

    sql

    复制代码

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

    返回：

    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE	employees	range	dept_salary	dept_salary	4	NULL	500	Using where

    • Extra = Using where，表示查询中包含了 WHERE 条件。

---

通过上面详细的描述，你可以清楚地知道每个字段的作用及其对查询优化的影响。了解 EXPLAIN 输出的每个细节，能够帮助你优化 SQL 查询和数据库设计。