MySQL 查询性能分析之 Explain

最新推荐文章于 2025-05-23 11:05:26 发布

原创

最新推荐文章于 2025-05-23 11:05:26 发布 · 332 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

EXPLAIN 关键字可以用于获取 SQL 语句执行计划的相关信息，在 MySQL 8.0 中，EXPLAIN 支持大多数 SQL 语句，如 SELECT 、DELETE 、INSERT 、REPLACE、和 UPDATE 。示例如下：

mysql> EXPLAIN SELECT * FROM employees;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299379 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+

注：本篇文章的测试数据来源于 MySQL 官方提供的 Employees Sample Database，其数据库结构如下：

以下分别介绍 EXPLAIN 输出结果中各个字段的含义：

1. id

id 为行标识符，同时也表示语句执行的优先级，值越大则优先级越高。特殊情况下，如果某行语句引用了其他多行结果集的并集，则该值可以为 NULL。示例如下：

# 该FROM字句只是用于演示，并没有任何实际意义
mysql> EXPLAIN SELECT COUNT(1) FROM (SELECT emp_no FROM salaries) AS t;
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL | 2757635 | NULL        |
|  2 | DERIVED     | salaries   | index | NULL          | PRIMARY | 7       | NULL | 2757635 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+

# 查询工资大于500000或部门编号等于d007的所有雇员的编号
mysql> EXPLAIN SELECT emp_no FROM salaries WHERE salary>500000 UNION ALL SELECT emp_no FROM dept_emp WHERE dept_no = "d007";
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
| id | select_type  | table      | type | possible_keys | key     | key_len | ref   | rows    | Extra                    |
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
|  1 | PRIMARY      | salaries   | ALL  | NULL          | NULL    | NULL    | NULL  | 2837161 | Using where              |
|  2 | UNION        | dept_emp   | ref  | dept_no       | dept_no | 4       | const |   91566