查询生命周期与计划器
提升 PostgreSQL 查询性能
Amy McCarty
Instructor
基本查询生命周期
| 系统 | 前端步骤 | 后端进程 | |
|---|---|---|---|
| 1 | 解析器 | 将查询发送到数据库 | 检查语法。根据系统规则将 SQL 转为更易处理的语法。 |
| 2 | 计划器与优化器 | 评估并优化查询任务 | 使用数据库统计创建查询计划。计算成本并选择最佳方案。 |
| 3 | 执行器 | 返回查询结果 | 按查询计划执行。 |
查询计划器与优化器
响应 SQL 结构变化
- 生成计划树
- 节点对应各步骤
- 用 EXPLAIN 可视化
- 估计每棵树的成本
- 来自 pg_tables 的统计
- 基于时间的优化
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
来自 pg_tables 的统计
SELECT * FROM pg_class
WHERE relname = 'mytable'
-- sample of output columns
| relname | relhasindex |
SELECT * FROM pg_stats
WHERE tablename = 'mytable'
-- sample of output columns
null_frac | avg_width | n_distinct |
- 列索引
- 空值计数
- 列宽
- 不同值数
EXPLAIN
- 查询计划的窗口
- 步骤与成本为估计值
- 不执行查询
- 对 cheeses 表的顺序扫描
- 成本与大小估计
EXPLAIN
SELECT * FROM cheeses
Seq Scan on cheeses
(cost=0.00..10.50 rows=5725 width=296)
EXPLAIN：扫描
- 查询计划步骤
- 返回行
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- Seq Scan：扫描表中所有行
EXPLAIN：成本（cost）
- 无量纲
- 比较输出相同的结构
- 不应比较输出不同的查询
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- 0.00..：启动时间
..10.50：总时间
总时间 = 启动 + 运行时间
EXPLAIN：大小（size）
- 大小估计
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- rows：为执行需检查的行数
- width：行的字节宽度
带 WHERE 子句的 EXPLAIN
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep')
Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
-> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
- 自下而上
- 步骤 1：过滤
- 步骤 2：顺序扫描
- WHERE 子句
- 减少需扫描的行，提升总成本
带索引的 EXPLAIN
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column
Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
- 步骤 1：位图索引扫描
- Index Cond 解释扫描条件
- 索引
- 启动成本从 0 增加
- 总成本从 379 降低
让我们来练习！
提升 PostgreSQL 查询性能
