查询优化与 EXPLAIN
你将构建什么
一组针对大型订单/客户模式的优化查询,附带前后 EXPLAIN 计划,展示索引和查询重写的影响。
学习目标
- 阅读和解释 EXPLAIN 输出
- 识别顺序扫描和高成本节点
- 为查询模式创建有效索引
- 重写查询以提升性能
前置要求
- JOIN 和 WHERE 子句
- 基本索引知识
- 聚合查询
步骤
- 1
建立大型数据集
创建 orders 和 customers 表,填充示例数据以演示优化技术。
代码CREATE TABLE customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total NUMERIC(10,2) NOT NULL, status TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); -- Insert sample data (run multiple times for scale) INSERT INTO customers (name, city) SELECT 'Customer ' || i, 'City ' || (i % 10) FROM generate_series(1, 10000) i; INSERT INTO orders (customer_id, total, status) SELECT (i % 10000) + 1, (i % 1000)::numeric, CASE WHEN i % 3 = 0 THEN 'shipped' ELSE 'pending' END FROM generate_series(1, 100000) i;预期结果
插入 10000 名客户和 100000 条订单。
- 2
用 EXPLAIN 分析慢查询
对一个按状态过滤订单并连接客户的查询运行 EXPLAIN ANALYZE。注意 Seq Scan(全表扫描)和高成本数值。
代码EXPLAIN ANALYZE SELECT c.name, o.total, o.status FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'shipped' ORDER BY o.created_at DESC LIMIT 100;预期结果
计划显示 orders 上的 Seq Scan(无索引)和排序步骤 —— 在大表上都很慢。
- 3
添加索引并重新分析
在过滤和排序列上创建索引,然后重新运行 EXPLAIN ANALYZE 查看改善。
提示
CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_created ON orders(created_at DESC);
- 4
优化聚合查询
查找每个城市的总收入。先写朴素版本,再通过确保连接和分组列有索引来优化。
提示
EXPLAIN ANALYZE SELECT c.city, SUM(o.total) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.city; —— 在 orders(customer_id) 上添加索引。
- 5
重写相关子查询
相关子查询可能很慢。将「有超过 500 订单的客户」从相关子查询重写为 JOIN 以获得更好性能。
代码-- Slow: correlated subquery EXPLAIN ANALYZE SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500); -- Fast: JOIN with DISTINCT EXPLAIN ANALYZE SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.total > 500;预期结果
JOIN 版本通常产生更好的计划,用哈希连接代替嵌套循环。
自己动手试试
-- Analyze this query's performance
EXPLAIN ANALYZE
SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped';CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_customer ON orders(customer_id);
SELECT c.name, o.total FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'shipped';你学到了什么
你学会了阅读 EXPLAIN 计划、识别瓶颈(Seq Scan、排序、嵌套循环),并通过有针对性的索引和查询重写进行优化。这是数据库性能调优的核心技能。
相关资源
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。