query-review

SQL Review and Query Optimization (Query Review)

References: Markus Winand, "SQL Performance Explained"; Bruce Momjian, "Mastering PostgreSQL"; High Performance MySQL (O'Reilly); PostgreSQL optimizer documentation; MySQL 8.0 optimizer documentation.

Applicable Scenarios

Review SQL or ORM queries submitted by the backend
Analyze slow queries, timeouts, CPU/IO anomalies
Identify N+1, full table scans, inefficient joins, inefficient pagination
Optimize aggregation, sorting, filtering, fuzzy search
Provide evidence for new indexes or schema adjustments

Core Principles

1. 先定位真实瓶颈，再改 SQL 或索引
   不看执行计划就加索引 = 瞎猜

EXPLAIN ANALYZE is the truth The optimizer's estimates may be wrong; only actual execution counts
Look at call frequency × per-call cost 100 times/day × 10 seconds vs 100000 times/day × 100 milliseconds The latter's total cost is 30 times that of the former
Distinguish query rewriting vs index changes If you can change the SQL without touching indexes: prioritize that If you can only add an index: evaluate the write cost
Pay attention to actual rows vs estimated rows Difference of 10x+ → statistics may be stale → ANALYZE
ORM is a black box; you need to see the real SQL Enable SQL logging / use sqlcommenter


## 审查输入

```text
SQL 或 ORM 查询
表结构和索引
参数样例（实际生产值）
数据量级
调用路径和频率
执行计划（EXPLAIN ANALYZE）
慢日志样本
性能目标（P99）

EXPLAIN Interpretation Quick Reference

PostgreSQL EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

关键节点（从下往上读）：

Seq Scan on orders               <- 全表扫描（小表 OK，大表 = 问题）
  cost=0.00..1234.56
  rows=100  (估算)
  actual rows=95 (实际)         <- 估算与实际差距大 = 统计信息过期
  loops=1
  Buffers: shared hit=50

Index Scan using idx_user_id     <- 用上索引（好）
  Index Cond: (user_id = 1)      <- 过滤条件用了索引
  Filter: (status = 'paid')      <- 注意：过滤但未用索引（status 不在索引）
  Rows Removed by Filter: 50    <- 索引取了 50 条又过滤掉

Bitmap Heap Scan                 <- 多索引或低选择性
  Recheck Cond: ...

Sort                             <- 内存排序（大数据 = 问题）
  Sort Method: external merge   <- 用磁盘排序（更慢）
  Sort Key: created_at desc

Hash Join                        <- 大表 join
  Hash Cond: (a.id = b.id)
  Buffers: shared hit=10000     <- 看 IO 评估

Nested Loop                      <- 循环 join（小表 OK）

MySQL EXPLAIN

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

关键字段：

type=ALL          → 全表扫描（最差）
type=index        → 索引扫描（不是用索引快速定位）
type=range        → 范围扫描
type=ref          → 用索引定位多行
type=eq_ref       → 用唯一索引定位单行
type=const        → 主键 / 唯一索引等值（最好）

key=...           → 用了哪个索引
rows=100          → 估算扫描行数
filtered=10%      → 索引取出后过滤剩余比例

Extra=Using temporary    → 用临时表
Extra=Using filesort     → 文件排序
Extra=Using index        → 覆盖索引（好）
Extra=Using where        → 索引取出后还要过滤

Common Problem Diagnosis Library

1. Full Table Scan

Symptoms：Seq Scan / type=ALL, actual rows close to the total number of rows in the table

Causes：

Missing index
Index wrapped by a function:WHERE LOWER(email) = ?
Implicit type conversion:WHERE varchar_col = 123
Optimizer chose wrong (stale statistics)

Solution：

-- 表达式索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 类型对齐
WHERE varchar_col = '123'  -- 加引号

-- 更新统计信息（PG）
ANALYZE orders;

-- MySQL
ANALYZE TABLE orders;

2. N+1 Query

Symptoms: 1 main query + N subqueries (ORM lazy loading)

Example (wrong)：

# Django
orders = Order.objects.all()
for order in orders:
    print(order.user.name)  # 每次都查一次 users
# 1 + N 次查询

Solution：

# Django: prefetch_related / select_related
orders = Order.objects.select_related('user').all()

# SQLAlchemy: joinedload
orders = session.query(Order).options(joinedload(Order.user)).all()

# Prisma: include
const orders = await prisma.order.findMany({ include: { user: true } });

Direct SQL写法：

SELECT o.*, u.name AS user_name
FROM orders o
JOIN users u ON o.user_id = u.id;

3. OFFSET Deep Pagination

Symptoms：OFFSET 100000 LIMIT 20 gets slower and slower

Cause: OFFSET still has to scan the first 100000 rows in order to skip them

Solution: use cursor pagination

-- 错
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- 对（用上次最后一条的 cursor）
SELECT * FROM orders
WHERE (created_at, id) < ('2026-05-18 10:00', 9999)
ORDER BY created_at DESC, id DESC
LIMIT 20;

4. ORDER BY File Sort

Symptoms：Sort Method: external merge / Using filesort

Cause: the sort field has no index or is not at the end of the index

Solution: create a sort-friendly index

-- 错
CREATE INDEX ON orders(user_id);
-- ORDER BY created_at DESC → 文件排序

-- 对
CREATE INDEX ON orders(user_id, created_at DESC);

5. LIKE '%xxx%' Full Table Scan

Symptoms: fuzzy matching + large table

Solution：

PostgreSQL：GIN trigram

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%abc%';  -- 用上索引

MySQL：FULLTEXT

ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('abc' IN NATURAL LANGUAGE MODE);

6. COUNT(*) Is Slow

Symptoms: large table SELECT COUNT(*) takes several seconds

Cause: the MVCC model requires scanning the table

Solution：

Estimate (suitable for user experience)

-- PostgreSQL：从统计表读
SELECT reltuples::BIGINT AS approximate_count
FROM pg_class WHERE relname = 'orders';

Materialize (suitable for exactness)

-- 维护单独计数表
CREATE TABLE order_counters (key varchar PRIMARY KEY, count bigint);
-- 触发器或异步更新

7. JSON Field Queries Are Slow

Symptoms：metadata->>'status' = 'paid' full table scan

Solution：

-- PostgreSQL：表达式索引
CREATE INDEX idx_orders_status_meta ON orders((metadata->>'status'));

-- 或 GIN 索引
CREATE INDEX idx_orders_meta_gin ON orders USING gin(metadata);
SELECT * FROM orders WHERE metadata @> '{"status": "paid"}';

8. Large Transaction Lock Wait

Symptoms: a single SQL statement is not slow, but the wait time is long

Diagnosis：

-- PG：找锁等待
SELECT * FROM pg_locks WHERE NOT granted;
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- MySQL
SELECT * FROM performance_schema.events_waits_current
WHERE event_name LIKE 'wait/lock%';

Solution: narrow the transaction scope, avoid long transactions

Standard Process

1. 明确业务场景和性能目标
   - SLA：P99 < ? ms
   - 频率：QPS = ?
   ↓
2. 收集 SQL、schema、索引、数据量、执行计划
   - 真实参数（不是 SELECT 1=1）
   - EXPLAIN ANALYZE 输出
   - 调用频率（监控数据）
   ↓
3. 判断瓶颈
   - 扫描：Seq Scan / type=ALL
   - 排序：filesort / external merge
   - join：错误算法 / 大表笛卡尔
   - 锁：等待锁 / 长事务
   - N+1：1 + N 模式
   - 网络往返：批量改单查
   ↓
4. 给出查询重写或索引建议
   - 改 SQL（不影响 schema）：优先
   - 加索引：评估写入成本
   - 改 schema：极少（除非根本设计错）
   ↓
5. 验证
   - 重跑 EXPLAIN ANALYZE 对比
   - 监控生产指标改善
   - 评估写入退化

Output Format

Problem	Evidence (EXPLAIN)	Frequency	Single execution time	Recommendation	Verification method	Risk
Full table scan orders	actual rows=1M	1000 QPS	800ms	Add index (user_id, status, created_at)	EXPLAIN and check for Index Scan	Writes slower by ~10%

ORM Pitfalls Quick Reference

Django

# 反模式：lazy load
orders = Order.objects.all()
for o in orders:
    print(o.user.name)  # N+1

# 解决
orders = Order.objects.select_related('user')

# 反模式：count() 慢
Order.objects.filter(...).count()

# 解决（估算）
Order.objects.filter(...).explain()  # 看真实查询

SQLAlchemy

# 反模式：lazy load
for order in session.query(Order):
    print(order.user.name)

# 解决
session.query(Order).options(joinedload(Order.user))

Prisma

// 反模式：findMany 不 include
const orders = await prisma.order.findMany();
// 然后循环查 user → N+1

// 解决
const orders = await prisma.order.findMany({ include: { user: true } });

Supporting Templates

templates/sql-review-template.md — SQL review report (scenario / current SQL / execution plan / issue / recommendation / verification / risk)

Quality Self-Check

□ 有真实 SQL 或 ORM 查询
□ 说明数据量和调用频率
□ 基于 EXPLAIN ANALYZE，不是猜测
□ 区分查询重写 vs 索引变更
□ 评估分页、排序、join、聚合成本
□ 看 actual rows vs estimated rows
□ 检查 N+1 模式
□ OFFSET 深分页改 cursor
□ 文件排序加排序友好索引
□ JSON 字段查询用表达式索引或 GIN
□ 模糊匹配用 GIN trigram / FULLTEXT
□ 给出可验证指标
□ 评估写入退化

Common Pitfalls

Adding an index without EXPLAIN——adding the wrong index or a redundant one
Optimizing only single execution time——ignoring the total cost of high-frequency calls
OFFSET deep pagination——with offset 100000 it still has to scan the first 100000
ORM lazy loading N+1——1 + N queries
Mixing aggregate queries and online business on the same link——large queries slow down the primary database
Stale statistics——the optimizer estimates incorrectly
Implicit type conversion——index becomes ineffective
Wrapping indexed fields in functions——index becomes ineffective
OR with multiple conditions——may degrade to a full table scan
NOT IN with a large subquery——poor performance, switch to NOT EXISTS
COUNT(*) on a large table——MVCC requires a scan
No LIMIT——query until it kills the system
SELECT *——wasted transfer + memory
DISTINCT on large data——memory explosion

Collaboration with Other Skills

上游：
  schema-design → 表结构和约束
  index-access-pattern → 索引设计
  api-designer → 查询模式（pagination / filter）

下游：
  index-access-pattern → 根据证据设计索引
  schema-design → 必要时调整字段或表结构
  migration-rollout → 涉及结构或索引变更时设计上线方案
  backend-engineer → 改 ORM 写法

Related References

references/query-optimization-guide.md — optimizer internals, statistics, join algorithms (NL/Hash/Merge), complete ORM pitfall library, slow log analysis

About this skill

Applicable Scenarios

Core Principles

EXPLAIN Interpretation Quick Reference

PostgreSQL EXPLAIN ANALYZE

MySQL EXPLAIN

Common Problem Diagnosis Library

1. Full Table Scan

2. N+1 Query

3. OFFSET Deep Pagination

4. ORDER BY File Sort

5. LIKE '%xxx%' Full Table Scan

6. COUNT(*) Is Slow

7. JSON Field Queries Are Slow

8. Large Transaction Lock Wait

Standard Process

Output Format

ORM Pitfalls Quick Reference

Django

SQLAlchemy

Prisma

Supporting Templates

Quality Self-Check

Common Pitfalls

Collaboration with Other Skills

Related References