数据库核心术语知识库

Appearance

定义 ​

执行计划 (Execution Plan) 是数据库优化器为执行一条 SQL 语句而生成的详细操作步骤序列。它描述了数据库将如何访问数据(全表扫描还是索引扫描)、使用哪些索引、表的连接顺序、排序方法等关键信息。

通过 EXPLAIN 命令可以查看执行计划,这是 SQL 性能分析和优化的核心工具。

详细笔记 ​

核心原理 ​

优化器的工作流程 ​

SQL 语句
  ↓
解析器(Parser):语法分析,生成解析树
  ↓
预处理器:语义检查,权限验证
  ↓
优化器(Optimizer):
  - 生成多个候选执行计划
  - 估算每个计划的成本(Cost)
  - 选择成本最低的计划
  ↓
执行器(Executor):按执行计划执行
  ↓
返回结果

EXPLAIN 基本用法 ​

sql
-- 基本语法
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- MySQL 8.0:更详细的 JSON 格式
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- SQL Server
SET SHOWPLAN_ALL ON;
SELECT * FROM users WHERE name = '张三';

MySQL EXPLAIN 输出详解 ​

sql
EXPLAIN SELECT u.name, o.amount 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age > 25
ORDER BY o.create_time DESC
LIMIT 10;

典型输出:

+----+-------------+-------+------------+--------+---------------+--------------+---------+-----------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref     | rows | filtered | Extra              |
+----+-------------+-------+------------+--------+---------------+--------------+---------+-----------------+------+----------+----------------------------------------------+
|  1 | SIMPLE  | u   | NULL   | range  | PRIMARY,idx_age| idx_age   | 5   | NULL    | 5000 | 100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE  | o   | NULL   | ref  | idx_user_id | idx_user_id  | 5   | test.u.id   | 10 | 100.00 | Using where            |
+----+-------------+-------+------------+--------+---------------+--------------+---------+-----------------+------+----------+----------------------------------------------+

关键字段解读 ​

一、id ​

表示查询中执行 SELECT 子句或操作表的顺序:

  • id 相同:从上到下执行
  • id 不同:id 越大越先执行(子查询优先)
sql
-- 示例:子查询
EXPLAIN SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 输出:
-- id: 1 (外层查询)
-- id: 2 (子查询,先执行)

二、select_type ​

查询类型:

值说明示例
SIMPLE简单查询(无子查询、无 UNION)SELECT * FROM users
PRIMARY外层查询(包含子查询时)SELECT * FROM (SELECT ...) AS t
SUBQUERY子查询SELECT ... WHERE id = (SELECT ...)
DERIVED派生表(FROM 子句中的子查询)SELECT * FROM (SELECT ...) AS t
UNIONUNION 中的第二个或后面的查询SELECT ... UNION SELECT ...
UNION RESULTUNION 的结果UNION RESULT

三、table ​

当前行正在访问的表名:

  • 实际表名:users, orders
  • 别名:u, o
  • 派生表:<derived2>(表示 id=2 的派生表)
  • UNION 结果:<union1,2>

四、type(重要!) ​

访问类型,从最好到最差:

system > const > eq_ref > ref > range > index > ALL

1. system(最优)

  • 表只有一行记录(系统表)
  • 极少见

2. const

  • 通过主键或唯一索引等值查询
  • 最多返回一行
sql
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

3. eq_ref

  • 连接查询中,使用主键或唯一索引等值匹配
  • 对于前表的每一行,后表最多返回一行
sql
EXPLAIN SELECT * FROM users u 
INNER JOIN orders o ON u.id = o.user_id;
-- o 表的 type: eq_ref(如果 user_id 是唯一索引)

4. ref

  • 使用普通索引等值查询
  • 可能返回多行
sql
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type: ref(假设 name 是普通索引)

5. range

  • 索引范围扫描
  • 适用于:BETWEEN, >, <, IN, LIKE '张%'
sql
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- type: range

6. index

  • 全索引扫描
  • 比全表扫描快,因为索引通常比数据小
sql
EXPLAIN SELECT name FROM users;
-- type: index(扫描整个索引树)

7. ALL(最差)

  • 全表扫描
  • 需要优化
sql
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: ALL(没有索引)

五、possible_keys ​

可能使用的索引列表:

  • 优化器考虑使用的索引
  • 如果为 NULL,表示没有可用的索引

六、key(重要!) ​

实际使用的索引:

  • 如果为 NULL,表示未使用索引
  • 可能与 possible_keys 不同(优化器最终选择的索引)

七、key_len ​

使用的索引长度(字节数):

  • 用于判断复合索引使用了多少列
  • 计算规则:
    • INT:4 字节
    • BIGINT:8 字节
    • VARCHAR(n):n * 字符集字节数 + 2
    • DATETIME:8 字节
    • 允许 NULL:额外 +1 字节
sql
CREATE INDEX idx_name_age ON users(name VARCHAR(50), age INT);

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- key_len: 152 (50*3 + 2,UTF8MB3)

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;
-- key_len: 157 (152 + 4 + 1,NULL 标识)

八、ref ​

与索引比较的列或常量:

  • const:与常量比较
  • database.table.column:与其他列比较
  • NULL:范围查询或函数

九、rows(重要!) ​

估算需要扫描的行数:

  • 基于统计信息的估算值
  • 越小越好
  • 与实际行数可能有差异

十、filtered ​

存储引擎返回的数据在经过过滤后,剩下记录的比例:

  • 百分比,最大值 100%
  • 越高越好
sql
EXPLAIN SELECT * FROM users WHERE age > 25;
-- rows: 10000
-- filtered: 50.00
-- 意味着:扫描 10000 行,过滤后剩下 5000 行

十一、Extra(重要!) ​

额外信息,常见值:

优化相关:

  • Using index:覆盖索引,无需回表 ✅
  • Using index condition:索引下推(ICP) ✅
  • Using where:服务器层进行 WHERE 过滤
  • Using temporary:使用临时表 ⚠️
  • Using filesort:使用文件排序(非索引排序) ⚠️
  • Using join buffer:使用连接缓冲 ⚠️

性能警告:

  • Using filesort:需要优化 ORDER BY
  • Using temporary:需要优化 GROUP BY 或 DISTINCT
  • Range checked for each record:索引选择不佳

EXPLAIN FORMAT=JSON ​

MySQL 5.6+ 支持 JSON 格式,提供更详细的信息:

sql
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

输出示例:

json
{
  "query_block": {
  "select_id": 1,
  "cost_info": {
  "query_cost": "1234.56"
  },
  "table": {
  "table_name": "users",
  "access_type": "ref",
  "possible_keys": ["idx_name"],
  "key": "idx_name",
  "used_key_parts": ["name"],
  "key_length": "152",
  "ref": ["const"],
  "rows_examined_per_scan": 10,
  "rows_produced_per_join": 10,
  "filtered": "100.00",
  "cost_info": {
    "read_cost": "1230.00",
    "eval_cost": "4.56",
    "prefix_cost": "1234.56"
  },
  "used_index_conditions": [
    "name = '张三'"
  ]
  }
  }
}

优势:

  • 包含成本估算(query_cost)
  • 显示实际使用的索引列(used_key_parts)
  • 显示每步的成本(cost_info)

实际优化案例 ​

案例一:添加索引优化全表扫描 ​

sql
-- 问题查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 输出:
-- type: ALL
-- key: NULL
-- rows: 1000000

-- 优化:添加索引
CREATE INDEX idx_email ON users(email);

-- 优化后
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 输出:
-- type: ref
-- key: idx_email
-- rows: 1

案例二:优化 ORDER BY ​

sql
-- 问题查询
EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY create_time DESC;

-- 输出:
-- type: ref
-- key: idx_user_id
-- Extra: Using filesort  ⚠️

-- 优化:创建复合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time);

-- 优化后
EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY create_time DESC;

-- 输出:
-- type: ref
-- key: idx_user_time
-- Extra: (空,利用索引排序) ✅

案例三:优化 JOIN ​

sql
-- 问题查询
EXPLAIN SELECT u.name, o.amount 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- 输出:
-- users: type: ALL, rows: 100000
-- orders: type: ALL, rows: 1000000

-- 优化:添加索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_amount ON orders(amount);

-- 优化后
-- users: type: const, rows: 1
-- orders: type: ref, rows: 10

不同数据库的执行计划 ​

PostgreSQL ​

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

输出:

Seq Scan on users  (cost=0.00..1234.56 rows=10 width=100) (actual time=5.123..10.456 rows=8 loops=1)
  Filter: (name = '张三'::text)
  Rows Removed by Filter: 999992
Planning Time: 0.123 ms
Execution Time: 10.567 ms

特点:

  • ANALYZE:实际执行并显示真实时间
  • cost:估算成本(启动成本..总成本)
  • actual time:实际执行时间
  • Planning Time:优化器耗时
  • Execution Time:执行耗时

SQL Server ​

sql
SET SHOWPLAN_ALL ON;
SELECT * FROM users WHERE name = '张三';

或通过 SSMS 图形化查看执行计划。

监控与诊断 ​

Performance Schema(MySQL 8.0+) ​

sql
-- 查看最近执行的语句的性能
SELECT 
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT,
  AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

慢查询日志 ​

sql
-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的记录

-- 查看慢查询
mysqldumpslow -s t /var/lib/mysql/slow.log

最佳实践 ​

  1. 定期审查慢查询:通过慢查询日志找出性能问题

  2. 关注 type 字段:确保关键查询至少达到 range 级别

  3. 检查 Extra 字段:避免 Using filesort 和 Using temporary

  4. 验证 rows 估算:与实际返回行数对比,差距过大可能需要更新统计信息

  5. 使用 EXPLAIN FORMAT=JSON:获取更详细的成本信息

  6. 结合实际情况:执行计划只是参考,最终要以实际性能测试为准

关联术语 ​

  • [[索引选择性]]
  • [[回表]]
  • [[最左前缀原则]]
  • [[统计信息]]
  • [[索引下推]]

参考资料 ​

Released under MIT License.

Copyright © 2026 Database Term KB