sql-optimization-patterns - implementation-playbook-育师

SQL 优化模式实施手册

本文件包含技能引用的详细模式、清单和代码示例。

SQL 优化模式

通过系统化优化、合理索引和查询计划分析，将缓慢的数据库查询转变为闪电般快速的操作。

不要使用本技能的时机

任务与 sql 优化模式无关
你需要此范围之外的领域或工具

说明

明确目标、约束和所需输入。
应用相关最佳实践并验证结果。
提供可操作的步骤和验证。
如果需要详细示例，请打开resources/implementation-playbook.md。

使用本技能的时机

调试运行缓慢的查询
设计高性能的数据库模式
优化应用响应时间
减少数据库负载和成本
提高不断增长数据集的扩展性
分析 EXPLAIN 查询计划
实现高效索引
解决 N+1 查询问题

核心概念

1. 查询执行计划（EXPLAIN）

理解 EXPLAIN 输出是优化的基础。

PostgreSQL EXPLAIN：

-- 基本 explainEXPLAINSELECT*FROMusersWHEREemail='user@example.com';-- 带实际执行统计信息EXPLAINANALYZESELECT*FROMusersWHEREemail='user@example.com';-- 带更多细节的详细输出EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECTu.*,o.order_totalFROMusers uJOINorders oONu.id=o.user_idWHEREu.created_at>NOW()-INTERVAL'30 days';

需要关注的关键指标：

Seq Scan：全表扫描（对于大表通常较慢）
Index Scan：使用索引（良好）
Index Only Scan：仅使用索引而不触碰表（最佳）
Nested Loop：连接方法（适用于小数据集）
Hash Join：连接方法（适用于较大的数据集）
Merge Join：连接方法（适用于已排序数据）
Cost：预估查询成本（越低越好）
Rows：预估返回的行数
Actual Time：实际执行时间

2. 索引策略

索引是最强大的优化工具。

索引类型：

B-Tree：默认，适用于等值和范围查询
Hash：仅用于等值（=）比较
GIN：全文搜索、数组查询、JSONB
GiST：几何数据、全文搜索
BRIN：用于具有相关性的超大表的块范围索引（Block Range INdex）

-- 标准 B-Tree 索引CREATEINDEXidx_users_emailONusers(email);-- 复合索引（顺序很重要！）CREATEINDEXidx_orders_user_statusONorders(user_id,status);-- 部分索引（只索引行子集）CREATEINDEXidx_active_usersONusers(email)WHEREstatus='active';-- 表达式索引CREATEINDEXidx_users_lower_emailONusers(LOWER(email));-- 覆盖索引（包含额外列）CREATEINDEXidx_users_email_coveringONusers(email)INCLUDE(name,created_at);-- 全文搜索索引CREATEINDEXidx_posts_searchONpostsUSINGGIN(to_tsvector('english',title||' '||body));-- JSONB 索引CREATEINDEXidx_metadataONeventsUSINGGIN(metadata);

3. 查询优化模式

避免 SELECT *：

-- 不好：获取不必要的列SELECT*FROMusersWHEREid=123;-- 好：只获取你需要的SELECTid,email,nameFROMusersWHEREid=123;

高效使用 WHERE 子句：

-- 不好：函数阻止索引使用SELECT*FROMusersWHERELOWER(email)='user@example.com';-- 好：创建函数索引或使用精确匹配CREATEINDEXidx_users_email_lowerONusers(LOWER(email));-- 然后：SELECT*FROMusersWHERELOWER(email)='user@example.com';-- 或存储规范化数据SELECT*FROMusersWHEREemail='user@example.com';

优化 JOIN：

-- 不好：笛卡尔积然后过滤SELECTu.name,o.totalFROMusers u,orders oWHEREu.id=o.user_idANDu.created_at>'2024-01-01';-- 好：先过滤再连接SELECTu.name,o.totalFROMusers uJOINorders oONu.id=o.user_idWHEREu.created_at>'2024-01-01';-- 更好：过滤两个表SELECTu.name,o.totalFROM(SELECT*FROMusersWHEREcreated_at>'2024-01-01')uJOINorders oONu.id=o.user_id;

优化模式

模式 1：消除 N+1 查询

问题：N+1 查询反模式

# 不好：执行 N+1 次查询users=db.query("SELECT * FROM users LIMIT 10")foruserinusers:orders=db.query("SELECT * FROM orders WHERE user_id = ?",user.id)# Process orders

解决方案：使用 JOIN 或批量加载

-- 解决方案 1：JOINSELECTu.id,u.name,o.idasorder_id,o.totalFROMusers uLEFTJOINorders oONu.id=o.user_idWHEREu.idIN(1,2,3,4,5);-- 解决方案 2：批量查询SELECT*FROMordersWHEREuser_idIN(1,2,3,4,5);

# 好：使用 JOIN 或批量加载的单次查询# 使用 JOINresults=db.query(""" SELECT u.id, u.name, o.id as order_id, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.id IN (1, 2, 3, 4, 5) """)# 或批量加载users=db.query("SELECT * FROM users LIMIT 10")user_ids=[u.idforuinusers]orders=db.query("SELECT * FROM orders WHERE user_id IN (?)",user_ids)# Group orders by user_idorders_by_user={}fororderinorders:orders_by_user.setdefault(order.user_id,[]).append(order)

模式 2：优化分页

不好的做法：大表上的 OFFSET

-- 对于大的偏移量很慢SELECT*FROMusersORDERBYcreated_atDESCLIMIT20OFFSET100000;-- 非常慢！

好的做法：基于游标的分页

-- 快得多：使用游标（最后看到的 ID）SELECT*FROMusersWHEREcreated_at<'2024-01-15 10:30:00'-- 最后游标ORDERBYcreated_atDESCLIMIT20;-- 带复合排序SELECT*FROMusersWHERE(created_at,id)<('2024-01-15 10:30:00',12345)ORDERBYcreated_atDESC,idDESCLIMIT20;-- 需要索引CREATEINDEXidx_users_cursorONusers(created_atDESC,idDESC);

模式 3：高效聚合

优化 COUNT 查询：

-- 不好：统计所有行SELECTCOUNT(*)FROMorders;-- 在大表上很慢-- 好：使用估计值进行近似计数SELECTreltuples::bigintASestimateFROMpg_classWHERErelname='orders';-- 好：先过滤再计数SELECTCOUNT(*)FROMordersWHEREcreated_at>NOW()-INTERVAL'7 days';-- 更好：使用仅索引扫描CREATEINDEXidx_orders_createdONorders(created_at);SELECTCOUNT(*)FROMordersWHEREcreated_at>NOW()-INTERVAL'7 days';

优化 GROUP BY：

-- 不好：先分组再过滤SELECTuser_id,COUNT(*)asorder_countFROMordersGROUPBYuser_idHAVINGCOUNT(*)>10;-- 更好：先过滤再分组（如果可能）SELECTuser_id,COUNT(*)asorder_countFROMordersWHEREstatus='completed'GROUPBYuser_idHAVINGCOUNT(*)>10;-- 最佳：使用覆盖索引CREATEINDEXidx_orders_user_statusONorders(user_id,status);

模式 4：子查询优化

转换相关子查询：

-- 不好：相关子查询（对每一行都执行）SELECTu.name,u.email,(SELECTCOUNT(*)FROMorders oWHEREo.user_id=u.id)asorder_countFROMusers u;-- 好：使用聚合的 JOINSELECTu.name,u.email,COUNT(o.id)asorder_countFROMusers uLEFTJOINorders oONo.user_id=u.idGROUPBYu.id,u.name,u.email;-- 更好：使用窗口函数SELECTDISTINCTON(u.id)u.name,u.email,COUNT(o.id)OVER(PARTITIONBYu.id)asorder_countFROMusers uLEFTJOINorders oONo.user_id=u.id;

使用 CTE 提高清晰度：

-- 使用公共表表达式WITHrecent_usersAS(SELECTid,name,emailFROMusersWHEREcreated_at>NOW()-INTERVAL'30 days'),user_order_countsAS(SELECTuser_id,COUNT(*)asorder_countFROMordersWHEREcreated_at>NOW()-INTERVAL'30 days'GROUPBYuser_id)SELECTru.name,ru.email,COALESCE(uoc.order_count,0)asordersFROMrecent_users ruLEFTJOINuser_order_counts uocONru.id=uoc.user_id;

模式 5：批量操作

批量 INSERT：

-- 不好：多次单独插入INSERTINTOusers(name,email)VALUES('Alice','alice@example.com');INSERTINTOusers(name,email)VALUES('Bob','bob@example.com');INSERTINTOusers(name,email)VALUES('Carol','carol@example.com');-- 好：批量插入INSERTINTOusers(name,email)VALUES('Alice','alice@example.com'),('Bob','bob@example.com'),('Carol','carol@example.com');-- 更好：使用 COPY 进行批量插入（PostgreSQL）COPY users(name,email)FROM'/tmp/users.csv'CSV HEADER;

批量 UPDATE：

-- 不好：循环中更新UPDATEusersSETstatus='active'WHEREid=1;UPDATEusersSETstatus='active'WHEREid=2;-- ... 对许多 ID 重复-- 好：使用 IN 子句的单次 UPDATEUPDATEusersSETstatus='active'WHEREidIN(1,2,3,4,5,...);-- 更好：对大批量使用临时表CREATETEMPTABLEtemp_user_updates(idINT,new_statusVARCHAR);INSERTINTOtemp_user_updatesVALUES(1,'active'),(2,'active'),...;UPDATEusers uSETstatus=t.new_statusFROMtemp_user_updates tWHEREu.id=t.id;

高级技术

物化视图

预计算昂贵的查询。

-- 创建物化视图CREATEMATERIALIZEDVIEWuser_order_summaryASSELECTu.id,u.name,COUNT(o.id)astotal_orders,SUM(o.total)astotal_spent,MAX(o.created_at)aslast_order_dateFROMusers uLEFTJOINorders oONu.id=o.user_idGROUPBYu.id,u.name;-- 为物化视图添加索引CREATEINDEXidx_user_summary_spentONuser_order_summary(total_spentDESC);-- 刷新物化视图REFRESH MATERIALIZEDVIEWuser_order_summary;-- 并发刷新（PostgreSQL）REFRESH MATERIALIZEDVIEWCONCURRENTLY user_order_summary;-- 查询物化视图（非常快）SELECT*FROMuser_order_summaryWHEREtotal_spent>1000ORDERBYtotal_spentDESC;

分区

拆分大表以获得更好的性能。

-- 按日期范围分区（PostgreSQL）CREATETABLEorders(idSERIAL,user_idINT,totalDECIMAL,created_atTIMESTAMP)PARTITIONBYRANGE(created_at);-- 创建分区CREATETABLEorders_2024_q1PARTITIONOFordersFORVALUESFROM('2024-01-01')TO('2024-04-01');CREATETABLEorders_2024_q2PARTITIONOFordersFORVALUESFROM('2024-04-01')TO('2024-07-01');-- 查询自动使用合适的分区SELECT*FROMordersWHEREcreated_atBETWEEN'2024-02-01'AND'2024-02-28';-- 只扫描 orders_2024_q1 分区

查询提示与优化

-- 强制使用索引（MySQL）SELECT*FROMusersUSEINDEX(idx_users_email)WHEREemail='user@example.com';-- 并行查询（PostgreSQL）SETmax_parallel_workers_per_gather=4;SELECT*FROMlarge_tableWHEREcondition;-- 连接提示（PostgreSQL）SETenable_nestloop=OFF;-- 强制使用 hash 或 merge 连接

最佳实践

选择性索引：索引过多会拖慢写入
监控查询性能：使用慢查询日志
保持统计信息更新：定期运行 ANALYZE
使用合适的数据类型：更小的类型 = 更好的性能
理性规范化：平衡规范化与性能
缓存频繁访问的数据：使用应用级缓存
连接池：复用数据库连接
定期维护：VACUUM、ANALYZE、重建索引

-- 更新统计信息ANALYZEusers;ANALYZEVERBOSE orders;-- 清理（PostgreSQL）VACUUMANALYZEusers;VACUUMFULLusers;-- 回收空间（锁定表）-- 重建索引REINDEXINDEXidx_users_email;REINDEXTABLEusers;

常见陷阱

过度索引：每个索引都会拖慢 INSERT/UPDATE/DELETE
未使用的索引：浪费空间并拖慢写入
缺少索引：查询缓慢、全表扫描
隐式类型转换：阻止索引使用
OR 条件：无法高效使用索引
带前导通配符的 LIKE：LIKE '%abc'无法使用索引
WHERE 中的函数：除非存在函数索引，否则阻止索引使用

监控查询

-- 查找慢查询（PostgreSQL）SELECTquery,calls,total_time,mean_timeFROMpg_stat_statementsORDERBYmean_timeDESCLIMIT10;-- 查找缺失索引（PostgreSQL）SELECTschemaname,tablename,seq_scan,seq_tup_read,idx_scan,seq_tup_read/seq_scanASavg_seq_tup_readFROMpg_stat_user_tablesWHEREseq_scan>0ORDERBYseq_tup_readDESCLIMIT10;-- 查找未使用的索引（PostgreSQL）SELECTschemaname,tablename,indexname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHEREidx_scan=0ORDERBYpg_relation_size(indexrelid)DESC;