目录
1 测试数据准备
wget https://github.com/datacharmer/test_db/archive/refs/tags/v1.0.7.tar.gz
root@u24-mysql-50#tar -xf test_db-1.0.7.tar.gz
root@u24-mysql-50#cd test_db-1.0.7/
root@u24-mysql-50#mysql -uroot -p < employees.sql
2 基本语法
mysql> \h explain
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement
explain_type: {
FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
| TREE
}
explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
URL: https://dev.mysql.com/doc/refman/8.0/en/explain.html
3 常见用法
mysql> select title,avg(salary) as avg_salary
-> from titles t
-> join salaries s
-> on s.emp_no=t.emp_no
-> group by title
-> order by avg_salary desc;
+--------------------+------------+
| title | avg_salary |
+--------------------+------------+
| Senior Staff | 70470.8353 |
| Staff | 69309.1023 |
| Manager | 66924.2706 |
| Senior Engineer | 60543.2191 |
| Engineer | 59508.0397 |
| Assistant Engineer | 59304.9863 |
| Technique Leader | 59294.3742 |
+--------------------+------------+
7 rows in set (15.19 sec)
查看执行计划
mysql> explain select title,avg(salary) as avg_salary from titles t join salaries s on s.emp_no=t.emp_no group by title order by avg_salary desc\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 209
ref: NULL
rows: 441832
filtered: 100.00
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: employees.t.emp_no
rows: 9
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain analyze select title,avg(salary) as avg_salary from titles t join salaries s on s.emp_no=t.em
p_no group by title order by avg_salary desc\G;
*************************** 1. row ***************************
EXPLAIN: -> Sort: avg_salary DESC (actual time=14958..14958 rows=7 loops=1)
-> Table scan on <temporary> (actual time=14958..14958 rows=7 loops=1)
-> Aggregate using temporary table (actual time=14958..14958 rows=7 loops=1)
-> Nested loop inner join (cost=588075 rows=4.32e+6) (actual time=0.563..6590 rows=4.64e+6 loops=1)
-> Covering index scan on t using PRIMARY (cost=44582 rows=441832) (actual time=0.395..353 rows=443308 loops=1)
-> Index lookup on s using PRIMARY (emp_no=t.emp_no) (cost=0.252 rows=9.78) (actual time=0.00727..0.0119 rows=10.5 loops=443308)
1 row in set (14.97 sec)
ERROR:
No query specified
输出说明：
Sort: avg_salary DESC (actual time=14958..14958 rows=7 loops=1)
实际耗时：14,958 毫秒（与聚合时间重叠）。
返回行数：7 行。
说明：排序 7 行数据极快，耗时实际包含在聚合步骤中。
Table scan on <temporary> (actual time=14958..14958 rows=7 loops=1)
Aggregate using temporary table (actual time=14958..14958 rows=7 loops=1)
实际耗时：14,958 毫秒（约 15 秒）。
返回行数：7 行（不同职称数量）。
问题：处理 464 万行数据导致聚合极慢（临时表可能使用了磁盘）。
Nested loop inner join (cost=588075 rows=4.32e+6) (actual time=0.563..6590 rows=4.64e+6 loops=1)
实际耗时：0.563 毫秒（启动）→ 6590 毫秒（完成）。
处理行数：464 万行（实际连接结果）。
循环次数：1 次（全量连接）。
问题：连接耗时较长（6.5 秒），是主要性能瓶颈。
Covering index scan on t using PRIMARY (cost=44582 rows=441832) (actual time=0.395..353 rows=443308 loops=1)
实际耗时：0.395 毫秒（启动）→ 353 毫秒（完成）。
返回行数：443,308 行（所有职称记录）。
优化点：covering index 表示索引覆盖所有查询字段（无需回表），效率较高。
Index lookup on s using PRIMARY (emp_no=t.emp_no) (cost=0.252 rows=9.78) (actual time=0.00727..0.0119 rows=10.5 loops=443308)
单次耗时：0.00727 毫秒（启动）→ 0.0119 毫秒（完成）。
单次返回：10.5 行（每个员工平均薪资记录数）。
总循环次数：443,308 次（驱动表行数）。
总时间计算：443,308 × 0.0119 ≈ 5275 毫秒（5.3 秒），是连接操作的主要耗时来源。
4 如何读懂执行计划
id: 1 #序号
select_type: SIMPLE #select类型
table: t #表
partitions: NULL #匹配分区信息
type: index #访问类型
possible_keys: PRIMARY #可能索引
key: PRIMARY #实际使用索引
key_len: 209 #实际使用索引的长度
ref: NULL #索引比较
rows: 441832 #要检查的行数，估计值
filtered: 100.00 #过滤
Extra: Using index; Using temporary; Using filesort #额外信息
4.1 type
访问类型会直接影响到查询语句的性能，性能从好到差依次为：
system
#访问仅有1条记录的数据表或没有记录的空表(基本上不存在)
const
#唯一索引扫描
mysql> explain select * from departments where dept_no='d009' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: departments
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
eq_ref
#执行连接查询时，如果被驱动表时通过主键或非NULL唯一主键访问的，
mysql> explain select * from departments where dept_no in (select dept_no from dept_emp where emp_no=10001) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dept_emp
partitions: NULL
type: ref
possible_keys: PRIMARY,dept_no
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: departments
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: employees.dept_emp.dept_no
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
ref
从该表中读取匹配索引值的所有数据行，作用通eq_ref类似
fulltext
通过fulltext索引查找数据
ref_or_null
与ref类似，增加了NULL比较
index_merge
unique_subquery
index_subquery
range
使用索引查找范围值
mysql> explain select * from dept_manager where emp_no between 10002 and 10004 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dept_manager
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
index
索引扫描
mysql> explain select * from dept_manager order by emp_no \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dept_manager
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 20
ref: NULL
rows: 24
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
查看索引
mysql> show index from dept_manager \G;
*************************** 1. row ***************************
Table: dept_manager
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
Collation: A
Cardinality: 24
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: dept_manager
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: dept_no
Collation: A
Cardinality: 24
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: dept_manager
Non_unique: 1
Key_name: dept_no
Seq_in_index: 1
Column_name: dept_no
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.00 sec)
ALL
全表扫描，通常表示存在性能问题
mysql> explain select * from dept_manager \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dept_manager
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 24
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
4.2 Rows
数据行数
4.3 key_len
键的长度
4.4 filtered
4.5 Extra
Using where，Using index,Using temporary,Using filesort