SQL 查询优化模式实战指南基于 agents 仓库 sql-optimization-patterns 技能的 5 大优化模式与高级技巧【免费下载链接】agentsMulti-harness agentic plugin marketplace for Claude Code, Codex, Cursor, OpenCode, GitHub Copilot, and Google Antigravity项目地址: https://gitcode.com/GitHub_Trending/agents24/agents本篇文章以 agents 仓库中developer-essentials插件下的sql-optimization-patterns技能为核心系统讲解从 N1 查询消除、游标分页、高效聚合、子查询改写到批量操作的五大优化模式并深入物化视图、分区表与查询提示等高级手段。读完本文你将掌握一套发现问题 → 定位执行计划 → 改写 SQL → 验证效果的完整优化方法论可直接用于调试慢查询、设计高性能表结构以及降低数据库负载。技能定位为什么 SQL 优化需要一套可复用的模式库在 agents 仓库中sql-optimization-patterns是 developer-essentials 插件下的 11 项技能之一按照 Agent Skills 规范采用渐进式披露组织SKILL.md 只保留触发条件、核心概念与最佳实践更细的 5 大模式与完整可运行示例则下沉到 references/details.md即本文的主体内容。技能描述明确其适用场景调试慢查询、设计数据库 Schema、优化应用响应时间、降低数据库负载与成本、分析 EXPLAIN 执行计划、解决 N1 查询问题。这套模式库的价值在于SQL 优化虽然高度依赖具体数据库与数据分布但绝大多数性能问题的形状是高度重复的——循环查询、深分页、全表聚合、相关子查询、逐行 DML。把这些问题归纳成可复用的模式配合坏写法 → 好写法 → 更优写法的递进式示例既能指导 Agent 在编码阶段规避反模式也能在排查阶段快速定位根因。前置知识读懂执行计划与索引优化的地基在深入具体模式之前先掌握两个贯穿全文的基础工具EXPLAIN 执行计划与索引策略。它们来自 SKILL.md 的核心概念部分也是后续每个模式中为什么这样改更快的底层依据。用 EXPLAIN 量化慢在哪优化不能靠直觉必须以执行计划为准。PostgreSQL 提供了三个递进层级的分析命令-- 基础查看查询计划 EXPLAIN SELECT * FROM users WHERE email userexample.com; -- 实际执行并返回真实耗时 EXPLAIN ANALYZE SELECT * FROM users WHERE email userexample.com; -- 完整输出附加缓冲区命中与详细节点信息 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT u.*, o.order_total FROM users u JOIN orders o ON u.id o.user_id WHERE u.created_at NOW() - INTERVAL 30 days;阅读执行计划时重点关注以下指标指标含义优化含义Seq Scan全表扫描大表上通常较慢应尽量消除Index Scan使用索引访问良好信号Index Only Scan仅扫索引不碰表最理想靠覆盖索引实现Nested Loop嵌套循环连接小数据集尚可大数据集要警惕Hash Join哈希连接大数据集上的优选连接方式Merge Join归并连接数据已排序时的高效选择Cost优化器估算成本越低越好Rows估算返回行数与实际行数偏差大说明统计信息过期Actual Time真实执行耗时与估算值对比可发现统计失真索引类型与常见建法索引是性价比最高的优化武器但必须按访问模式选择类型B-Tree默认类型适合等值与范围查询Hash仅用于等值比较GIN全文检索、数组查询、JSONBGiST几何数据、全文检索BRINBlock Range Index适用于数据相关性极强的大表SKILL.md 给出了覆盖常见场景的建索引模板-- 标准 B-Tree 索引 CREATE INDEX idx_users_email ON users(email); -- 复合索引列顺序至关重要 CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- 部分索引只索引满足条件的行子集 CREATE INDEX idx_active_users ON users(email) WHERE status active; -- 表达式索引解决 WHERE 中套函数导致索引失效 CREATE INDEX idx_users_lower_email ON users(LOWER(email)); -- 覆盖索引额外 INCLUDE 列实现 Index Only Scan CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name, created_at); -- 全文检索索引 CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector(english, title || || body)); -- JSONB 索引 CREATE INDEX idx_metadata ON events USING GIN(metadata);理解这两块地基后下面进入 details.md 的五大核心优化模式。模式一消除 N1 查询问题循环查询是最常见的性能杀手N1 反模式指先查一次主表拿到 N 行再在循环体内为每行各执行一次关联查询总计产生 N1 次数据库往返。网络延迟被放大 N 倍数据库连接与解析开销也随之剧增# Bad: Executes N1 queries users db.query(SELECT * FROM users LIMIT 10) for user in users: orders db.query(SELECT * FROM orders WHERE user_id ?, user.id) # Process orders10 个用户就会产生 1 次主查询 10 次子查询共 11 次往返如果用户量增长到 1000就是 1001 次。这是应用层最容易写出、也最值得优先消除的模式。解法一JOIN 一次取回将查用户 循环查订单合并为一条 JOIN 语句让数据库在单次查询内完成关联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);注意此处使用LEFT JOIN保证没有订单的用户也不会丢失。解法二批量加载Batch Loading当 JOIN 会造成结果集膨胀一对多关系中每行被复制多次时可以退而求其次先查用户再一次性用IN查出全部订单最后在内存中按user_id分组# Good: Single query with JOIN or batch load # Using JOIN results 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) ) # Or batch load users db.query(SELECT * FROM users LIMIT 10) user_ids [u.id for u in users] orders db.query( SELECT * FROM orders WHERE user_id IN (?), user_ids ) # Group orders by user_id orders_by_user {} for order in orders: orders_by_user.setdefault(order.user_id, []).append(order)批量加载把 N1 次查询压缩为恒定的 2 次无论用户数如何增长数据库往返次数都不再随行数线性膨胀。ORM 世界中的selectinload、prefetch_related等机制底层正是这个思路。模式二优化分页问题OFFSET 深分页的性能陷阱LIMIT 20 OFFSET 100000看起来简单但数据库必须扫描并丢弃前 100000 行才能返回第 100001100020 行偏移越大扫描越多-- Slow for large offsets SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100000; -- Very slow!解法基于游标Cursor的分页游标分页用上一页的最后一条记录作为过滤条件把随机深偏移变成确定性的范围扫描无论翻到第几页扫描量都恒定-- Much faster: Use cursor (last seen ID) SELECT * FROM users WHERE created_at 2024-01-15 10:30:00 -- Last cursor ORDER BY created_at DESC LIMIT 20; -- With composite sorting SELECT * FROM users WHERE (created_at, id) (2024-01-15 10:30:00, 12345) ORDER BY created_at DESC, id DESC LIMIT 20; -- Requires index CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);两点实战提醒复合排序必须用行值比较当排序键存在重复值如同一时刻有多条created_at时单纯比较时间戳会漏行或重行。(created_at, id)行值比较借助主键保证唯一性和确定性。配套索引必须与排序方向一致CREATE INDEX ... ON users(created_at DESC, id DESC)让索引顺序与ORDER BY完全吻合数据库才能直接倒序扫描索引避免额外的排序步骤。游标分页的代价是不能跳页用户无法直接点击第 50 页但它换来的是 O(1) 的稳定延迟适合无限滚动与数据流场景。模式三高效聚合优化 COUNT区分精确与近似COUNT(*)在大表上需要扫描全部可见行代价高昂。可以根据业务对精度的要求选择不同策略-- Bad: Counts all rows SELECT COUNT(*) FROM orders; -- Slow on large tables -- Good: Use estimates for approximate counts SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname orders; -- Good: Filter before counting SELECT COUNT(*) FROM orders WHERE created_at NOW() - INTERVAL 7 days; -- Better: Use index-only scan CREATE INDEX idx_orders_created ON orders(created_at); SELECT COUNT(*) FROM orders WHERE created_at NOW() - INTERVAL 7 days;三条递进策略适用不同场景需要精确的全表行数且表极大时直接读pg_class.reltuples获取优化器统计信息里的估算值毫秒级返回适合展示类需求如共 N 条结果的概览带过滤条件的计数应先缩小范围如只统计最近 7 天扫描行数大幅下降为过滤列建立索引后PostgreSQL 可以用覆盖索引完成统计Index Only Scan连表数据都不用触碰——这是Better一行的原因。优化 GROUP BY先过滤、后分组、再覆盖分组统计的性能关键在尽早缩小参与分组的数据量并让索引覆盖过滤条件-- Bad: Group by then filter SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) 10; -- Better: Filter first, then group (if possible) SELECT user_id, COUNT(*) as order_count FROM orders WHERE status completed GROUP BY user_id HAVING COUNT(*) 10; -- Best: Use covering index CREATE INDEX idx_orders_user_status ON orders(user_id, status);把WHERE status completed放到分组之前让绝大多数无关行在分组前就被剔除随后为(user_id, status)建立覆盖索引过滤、分组所需的数据全部位于索引页内进一步将聚合推向 Index Only Scan。模式四子查询优化消除相关子查询从每行执行一次到整体一次相关子查询会引用外层表的列导致数据库对外层每一行都重新执行一次内层查询——本质上是 SQL 层面的 N1。改写为 JOIN 聚合可以一次性完成-- Bad: Correlated subquery (runs for each row) SELECT u.name, u.email, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) as order_count FROM users u; -- Good: JOIN with aggregation SELECT u.name, u.email, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.id, u.name, u.email;改写后数据库可以自由选择哈希或归并连接完成整个关联而不是逐行触发子查询。更进一步窗口函数当需要在一行中同时看到明细与聚合结果时COUNT(...) OVER (PARTITION BY ...)窗口函数可以避免 GROUP BY 对结果集的压缩副作用SELECT DISTINCT ON (u.id) u.name, u.email, COUNT(o.id) OVER (PARTITION BY u.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id u.id;不过要提醒窗口函数版在语义上更精巧但DISTINCT ON (u.id)对 PostgreSQL 专有且窗口聚合仍需先产生 JOIN 中间结果实践中最稳妥的默认选择仍是JOIN GROUP BY。用 CTE 提升可读性与复用性公共表表达式WITH 子句把复杂查询拆解为有名字的中间步骤逻辑一目了然也便于独立调试每一段WITH recent_users AS ( SELECT id, name, email FROM users WHERE created_at NOW() - INTERVAL 30 days ), user_order_counts AS ( SELECT user_id, COUNT(*) as order_count FROM orders WHERE created_at NOW() - INTERVAL 30 days GROUP BY user_id ) SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders FROM recent_users ru LEFT JOIN user_order_counts uoc ON ru.id uoc.user_id;注意示例中用COALESCE(uoc.order_count, 0)把无订单用户的 NULL 归一为 0这是 LEFT JOIN 聚合查询的常见收尾处理。CTE 在 PostgreSQL 12 中默认内联inline多数情况下不会额外物化安全性有保障。模式五批量操作批量 INSERT一次多值 vs 逐行插入每条 INSERT 语句都伴随解析、权限检查、事务日志写入等固定开销逐行执行时这些开销被放大 N 倍-- Bad: Multiple individual inserts INSERT INTO users (name, email) VALUES (Alice, aliceexample.com); INSERT INTO users (name, email) VALUES (Bob, bobexample.com); INSERT INTO users (name, email) VALUES (Carol, carolexample.com); -- Good: Batch insert INSERT INTO users (name, email) VALUES (Alice, aliceexample.com), (Bob, bobexample.com), (Carol, carolexample.com); -- Better: Use COPY for bulk inserts (PostgreSQL) COPY users (name, email) FROM /tmp/users.csv CSV HEADER;量级再往上走如初始化、迁移、数据导入应切换到 PostgreSQL 的COPY命令它绕过标准 INSERT 的逐行处理路径直接以流式方式装载数据是海量导入的事实标准。批量 INSERT 也有实用边界——单条语句不宜无上限堆值一般以数千行为一批分段提交兼顾内存与锁粒度。批量 UPDATE单条 IN 与临时表逐行 UPDATE 的循环写法与逐行 INSERT 同样低效。首选把多行合并进一条IN语句若需要为不同行更新不同值则引入临时表 JOIN 更新-- Bad: Update in loop UPDATE users SET status active WHERE id 1; UPDATE users SET status active WHERE id 2; -- ... repeat for many IDs -- Good: Single UPDATE with IN clause UPDATE users SET status active WHERE id IN (1, 2, 3, 4, 5, ...); -- Better: Use temporary table for large batches CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR); INSERT INTO temp_user_updates VALUES (1, active), (2, active), ...; UPDATE users u SET status t.new_status FROM temp_user_updates t WHERE u.id t.id;当目标行数极大或每行的新值互不相同时先把待更新数据批量装入临时表同样可以走 COPY再用UPDATE ... FROM一次完成关联更新。临时表在会话结束时自动清理无需手动维护。高级技巧物化视图、分区表与查询提示物化视图用存储空间换查询时间对频繁读取、低频变更的重型聚合物化视图把昂贵计算提前固化到磁盘-- Create materialized view CREATE MATERIALIZED VIEW user_order_summary AS SELECT u.id, u.name, COUNT(o.id) as total_orders, SUM(o.total) as total_spent, MAX(o.created_at) as last_order_date FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name; -- Add index to materialized view CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC); -- Refresh materialized view REFRESH MATERIALIZED VIEW user_order_summary; -- Concurrent refresh (PostgreSQL) REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary; -- Query materialized view (very fast) SELECT * FROM user_order_summary WHERE total_spent 1000 ORDER BY total_spent DESC;三个关键点物化视图是物理存储因此可以像普通表一样建索引示例中为total_spent建立了降序索引以加速排序查询刷新策略需要权衡普通REFRESH MATERIALIZED VIEW会锁表并整体重建REFRESH ... CONCURRENTLY支持在线刷新、不阻塞查询但要求物化视图有唯一索引且刷新期间需要额外空间数据新鲜度由业务决定——报表、看板等可容忍分钟级延迟的场景收益最大。分区表把扫描一张大表变成只扫需要的分片分区将逻辑上的一张表拆成多张物理分片查询条件命中分区键时优化器只扫描对应分片-- Range partitioning by date (PostgreSQL) CREATE TABLE orders ( id SERIAL, user_id INT, total DECIMAL, created_at TIMESTAMP ) PARTITION BY RANGE (created_at); -- Create partitions CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM (2024-01-01) TO (2024-04-01); CREATE TABLE orders_2024_q2 PARTITION OF orders FOR VALUES FROM (2024-04-01) TO (2024-07-01); -- Queries automatically use appropriate partition SELECT * FROM orders WHERE created_at BETWEEN 2024-02-01 AND 2024-02-28; -- Only scans orders_2024_q1 partition上例中查询只落在orders_2024_q1一个分片。分区带来的附加收益是运维层面的历史分区可以直接DETACH归档或快速删除避免对大表执行昂贵的 DELETE。需要强调的是分区裁剪的前提是WHERE 条件必须包含分区键此处为created_at否则优化器仍会扫描全部分区。查询提示谨慎使用的人工干预优化器通常足够聪明但当统计信息失真或特定场景下优化器选错计划时可以用查询提示强制干预-- Force index usage (MySQL) SELECT * FROM users USE INDEX (idx_users_email) WHERE email userexample.com; -- Parallel query (PostgreSQL) SET max_parallel_workers_per_gather 4; SELECT * FROM large_table WHERE condition; -- Join hints (PostgreSQL) SET enable_nestloop OFF; -- Force hash or merge join需要清醒认识查询提示的两面性USE INDEX、SET enable_nestloop OFF这类手段属于绕过优化器的兜底方案数据分布变化后可能适得其反。正确顺序永远是先更新统计信息ANALYZE、检查索引缺失最后才考虑提示。最佳实践与常见陷阱八条维护准则SKILL.md 总结了数据库长期健康运行的八条准则索引要有选择性索引过多会拖慢写入持续监控查询性能启用慢查询日志保持统计信息新鲜定期执行 ANALYZE使用合适的数据类型更小的类型意味着更好的性能审慎地规范化在范式与性能之间取平衡缓存高频访问数据应用层缓存兜底使用连接池复用数据库连接定期维护VACUUM、ANALYZE、重建索引对应的日常维护 SQL-- Update statistics ANALYZE users; ANALYZE VERBOSE orders; -- Vacuum (PostgreSQL) VACUUM ANALYZE users; VACUUM FULL users; -- Reclaim space (locks table) -- Reindex REINDEX INDEX idx_users_email; REINDEX TABLE users;注意VACUUM FULL会锁定整表应安排在维护窗口执行常规VACUUM与ANALYZE则可在线进行。七个必须避开的陷阱陷阱后果应对过度建索引每条 INSERT/UPDATE/DELETE 都要同步维护索引只为高频查询路径建索引无用索引浪费磁盘并拖慢写入用pg_stat_user_indexes定期清理索引缺失慢查询、全表扫描结合执行计划补索引隐式类型转换列上套函数/类型转换导致索引失效显式转换并保持类型一致OR 条件多数情况下无法高效利用索引改写为 UNION ALL 或 INLIKE 前导通配符LIKE %abc无法走索引改用前缀匹配或全文检索WHERE 中套函数索引失效除非有表达式索引建函数索引或存储规范化数据其中WHERE 中套函数是最隐蔽的坑WHERE LOWER(email) ...会让users(email)索引完全失效解决办法是创建LOWER(email)表达式索引或干脆在应用层把 email 规范化为小写存储。监控查询让优化有据可依PostgreSQL 的统计视图让哪条查询慢、哪个索引没用上可量化、可排序避免靠猜-- Find slow queries (PostgreSQL) SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; -- Find missing indexes (PostgreSQL) SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg_seq_tup_read FROM pg_stat_user_tables WHERE seq_scan 0 ORDER BY seq_tup_read DESC LIMIT 10; -- Find unused indexes (PostgreSQL) SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY pg_relation_size(indexrelid) DESC;这三条查询分别对应定位慢语句发现该建索引的表和清理没用的索引与上面八条维护准则和七个陷阱形成闭环先用量化数据发现问题再用五大模式改写语句最后用统计视图验证效果并清理冗余索引。总结一套可落地的优化流程把 details.md 与 SKILL.md 的内容串起来就得到一套闭环优化流程量化定位用pg_stat_statements、慢查询日志找出最慢的语句读取执行计划EXPLAIN (ANALYZE, BUFFERS, VERBOSE)确认 Seq Scan、连接方式与实际耗时套用模式N1 用 JOIN/批量加载深分页用游标聚合先过滤再分组子查询改写为 JOIN/CTEDML 全部批量数据侧兜底为过滤列建覆盖/部分/表达式索引重度聚合上物化视图海量时序数据上分区表验证与维护复查执行计划确认 Index Scan/Index Only Scan定期 ANALYZE 保持统计新鲜用统计视图清理无用索引。这套方法论不绑定特定数据库示例以 PostgreSQL 为主兼有 MySQL 语法核心的减少往返、缩小扫描、用索引换扫描三原则在任何关系型数据库上都成立。当你下一次遇到接口响应变慢、数据库 CPU 飙高或报表查询超时不妨先按本文的模式清单逐条排查——大多数性能问题都能在这一套框架内得到系统性解决。【免费下载链接】agentsMulti-harness agentic plugin marketplace for Claude Code, Codex, Cursor, OpenCode, GitHub Copilot, and Google Antigravity项目地址: https://gitcode.com/GitHub_Trending/agents24/agents创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 SQL 查询优化模式实战指南：基于 agents 仓库 sql-optimization-patterns 技能的 5 大优化模式与高级技巧
SQL 查询优化模式实战指南基于 agents 仓库 sql-optimization-patterns 技能的 5 大优化模式与高级技巧【免费下载链接】agentsMulti-harness agentic plugin marketplace for Claude Code, Codex, Cursor, OpenCode, GitHub Copilot, and Google Antigravity项目地址: https://gitcode.com/GitHub_Trending/agents24/agents本篇文章以 agents 仓库中developer-essentials插件下的sql-optimization-patterns技能为核心系统讲解从 N1 查询消除、游标分页、高效聚合、子查询改写到批量操作的五大优化模式并深入物化视图、分区表与查询提示等高级手段。读完本文你将掌握一套发现问题 → 定位执行计划 → 改写 SQL → 验证效果的完整优化方法论可直接用于调试慢查询、设计高性能表结构以及降低数据库负载。技能定位为什么 SQL 优化需要一套可复用的模式库在 agents 仓库中sql-optimization-patterns是 developer-essentials 插件下的 11 项技能之一按照 Agent Skills 规范采用渐进式披露组织SKILL.md 只保留触发条件、核心概念与最佳实践更细的 5 大模式与完整可运行示例则下沉到 references/details.md即本文的主体内容。技能描述明确其适用场景调试慢查询、设计数据库 Schema、优化应用响应时间、降低数据库负载与成本、分析 EXPLAIN 执行计划、解决 N1 查询问题。这套模式库的价值在于SQL 优化虽然高度依赖具体数据库与数据分布但绝大多数性能问题的形状是高度重复的——循环查询、深分页、全表聚合、相关子查询、逐行 DML。把这些问题归纳成可复用的模式配合坏写法 → 好写法 → 更优写法的递进式示例既能指导 Agent 在编码阶段规避反模式也能在排查阶段快速定位根因。前置知识读懂执行计划与索引优化的地基在深入具体模式之前先掌握两个贯穿全文的基础工具EXPLAIN 执行计划与索引策略。它们来自 SKILL.md 的核心概念部分也是后续每个模式中为什么这样改更快的底层依据。用 EXPLAIN 量化慢在哪优化不能靠直觉必须以执行计划为准。PostgreSQL 提供了三个递进层级的分析命令-- 基础查看查询计划 EXPLAIN SELECT * FROM users WHERE email userexample.com; -- 实际执行并返回真实耗时 EXPLAIN ANALYZE SELECT * FROM users WHERE email userexample.com; -- 完整输出附加缓冲区命中与详细节点信息 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT u.*, o.order_total FROM users u JOIN orders o ON u.id o.user_id WHERE u.created_at NOW() - INTERVAL 30 days;阅读执行计划时重点关注以下指标指标含义优化含义Seq Scan全表扫描大表上通常较慢应尽量消除Index Scan使用索引访问良好信号Index Only Scan仅扫索引不碰表最理想靠覆盖索引实现Nested Loop嵌套循环连接小数据集尚可大数据集要警惕Hash Join哈希连接大数据集上的优选连接方式Merge Join归并连接数据已排序时的高效选择Cost优化器估算成本越低越好Rows估算返回行数与实际行数偏差大说明统计信息过期Actual Time真实执行耗时与估算值对比可发现统计失真索引类型与常见建法索引是性价比最高的优化武器但必须按访问模式选择类型B-Tree默认类型适合等值与范围查询Hash仅用于等值比较GIN全文检索、数组查询、JSONBGiST几何数据、全文检索BRINBlock Range Index适用于数据相关性极强的大表SKILL.md 给出了覆盖常见场景的建索引模板-- 标准 B-Tree 索引 CREATE INDEX idx_users_email ON users(email); -- 复合索引列顺序至关重要 CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- 部分索引只索引满足条件的行子集 CREATE INDEX idx_active_users ON users(email) WHERE status active; -- 表达式索引解决 WHERE 中套函数导致索引失效 CREATE INDEX idx_users_lower_email ON users(LOWER(email)); -- 覆盖索引额外 INCLUDE 列实现 Index Only Scan CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name, created_at); -- 全文检索索引 CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector(english, title || || body)); -- JSONB 索引 CREATE INDEX idx_metadata ON events USING GIN(metadata);理解这两块地基后下面进入 details.md 的五大核心优化模式。模式一消除 N1 查询问题循环查询是最常见的性能杀手N1 反模式指先查一次主表拿到 N 行再在循环体内为每行各执行一次关联查询总计产生 N1 次数据库往返。网络延迟被放大 N 倍数据库连接与解析开销也随之剧增# Bad: Executes N1 queries users db.query(SELECT * FROM users LIMIT 10) for user in users: orders db.query(SELECT * FROM orders WHERE user_id ?, user.id) # Process orders10 个用户就会产生 1 次主查询 10 次子查询共 11 次往返如果用户量增长到 1000就是 1001 次。这是应用层最容易写出、也最值得优先消除的模式。解法一JOIN 一次取回将查用户 循环查订单合并为一条 JOIN 语句让数据库在单次查询内完成关联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);注意此处使用LEFT JOIN保证没有订单的用户也不会丢失。解法二批量加载Batch Loading当 JOIN 会造成结果集膨胀一对多关系中每行被复制多次时可以退而求其次先查用户再一次性用IN查出全部订单最后在内存中按user_id分组# Good: Single query with JOIN or batch load # Using JOIN results 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) ) # Or batch load users db.query(SELECT * FROM users LIMIT 10) user_ids [u.id for u in users] orders db.query( SELECT * FROM orders WHERE user_id IN (?), user_ids ) # Group orders by user_id orders_by_user {} for order in orders: orders_by_user.setdefault(order.user_id, []).append(order)批量加载把 N1 次查询压缩为恒定的 2 次无论用户数如何增长数据库往返次数都不再随行数线性膨胀。ORM 世界中的selectinload、prefetch_related等机制底层正是这个思路。模式二优化分页问题OFFSET 深分页的性能陷阱LIMIT 20 OFFSET 100000看起来简单但数据库必须扫描并丢弃前 100000 行才能返回第 100001100020 行偏移越大扫描越多-- Slow for large offsets SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100000; -- Very slow!解法基于游标Cursor的分页游标分页用上一页的最后一条记录作为过滤条件把随机深偏移变成确定性的范围扫描无论翻到第几页扫描量都恒定-- Much faster: Use cursor (last seen ID) SELECT * FROM users WHERE created_at 2024-01-15 10:30:00 -- Last cursor ORDER BY created_at DESC LIMIT 20; -- With composite sorting SELECT * FROM users WHERE (created_at, id) (2024-01-15 10:30:00, 12345) ORDER BY created_at DESC, id DESC LIMIT 20; -- Requires index CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);两点实战提醒复合排序必须用行值比较当排序键存在重复值如同一时刻有多条created_at时单纯比较时间戳会漏行或重行。(created_at, id)行值比较借助主键保证唯一性和确定性。配套索引必须与排序方向一致CREATE INDEX ... ON users(created_at DESC, id DESC)让索引顺序与ORDER BY完全吻合数据库才能直接倒序扫描索引避免额外的排序步骤。游标分页的代价是不能跳页用户无法直接点击第 50 页但它换来的是 O(1) 的稳定延迟适合无限滚动与数据流场景。模式三高效聚合优化 COUNT区分精确与近似COUNT(*)在大表上需要扫描全部可见行代价高昂。可以根据业务对精度的要求选择不同策略-- Bad: Counts all rows SELECT COUNT(*) FROM orders; -- Slow on large tables -- Good: Use estimates for approximate counts SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname orders; -- Good: Filter before counting SELECT COUNT(*) FROM orders WHERE created_at NOW() - INTERVAL 7 days; -- Better: Use index-only scan CREATE INDEX idx_orders_created ON orders(created_at); SELECT COUNT(*) FROM orders WHERE created_at NOW() - INTERVAL 7 days;三条递进策略适用不同场景需要精确的全表行数且表极大时直接读pg_class.reltuples获取优化器统计信息里的估算值毫秒级返回适合展示类需求如共 N 条结果的概览带过滤条件的计数应先缩小范围如只统计最近 7 天扫描行数大幅下降为过滤列建立索引后PostgreSQL 可以用覆盖索引完成统计Index Only Scan连表数据都不用触碰——这是Better一行的原因。优化 GROUP BY先过滤、后分组、再覆盖分组统计的性能关键在尽早缩小参与分组的数据量并让索引覆盖过滤条件-- Bad: Group by then filter SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) 10; -- Better: Filter first, then group (if possible) SELECT user_id, COUNT(*) as order_count FROM orders WHERE status completed GROUP BY user_id HAVING COUNT(*) 10; -- Best: Use covering index CREATE INDEX idx_orders_user_status ON orders(user_id, status);把WHERE status completed放到分组之前让绝大多数无关行在分组前就被剔除随后为(user_id, status)建立覆盖索引过滤、分组所需的数据全部位于索引页内进一步将聚合推向 Index Only Scan。模式四子查询优化消除相关子查询从每行执行一次到整体一次相关子查询会引用外层表的列导致数据库对外层每一行都重新执行一次内层查询——本质上是 SQL 层面的 N1。改写为 JOIN 聚合可以一次性完成-- Bad: Correlated subquery (runs for each row) SELECT u.name, u.email, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) as order_count FROM users u; -- Good: JOIN with aggregation SELECT u.name, u.email, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.id, u.name, u.email;改写后数据库可以自由选择哈希或归并连接完成整个关联而不是逐行触发子查询。更进一步窗口函数当需要在一行中同时看到明细与聚合结果时COUNT(...) OVER (PARTITION BY ...)窗口函数可以避免 GROUP BY 对结果集的压缩副作用SELECT DISTINCT ON (u.id) u.name, u.email, COUNT(o.id) OVER (PARTITION BY u.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id u.id;不过要提醒窗口函数版在语义上更精巧但DISTINCT ON (u.id)对 PostgreSQL 专有且窗口聚合仍需先产生 JOIN 中间结果实践中最稳妥的默认选择仍是JOIN GROUP BY。用 CTE 提升可读性与复用性公共表表达式WITH 子句把复杂查询拆解为有名字的中间步骤逻辑一目了然也便于独立调试每一段WITH recent_users AS ( SELECT id, name, email FROM users WHERE created_at NOW() - INTERVAL 30 days ), user_order_counts AS ( SELECT user_id, COUNT(*) as order_count FROM orders WHERE created_at NOW() - INTERVAL 30 days GROUP BY user_id ) SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders FROM recent_users ru LEFT JOIN user_order_counts uoc ON ru.id uoc.user_id;注意示例中用COALESCE(uoc.order_count, 0)把无订单用户的 NULL 归一为 0这是 LEFT JOIN 聚合查询的常见收尾处理。CTE 在 PostgreSQL 12 中默认内联inline多数情况下不会额外物化安全性有保障。模式五批量操作批量 INSERT一次多值 vs 逐行插入每条 INSERT 语句都伴随解析、权限检查、事务日志写入等固定开销逐行执行时这些开销被放大 N 倍-- Bad: Multiple individual inserts INSERT INTO users (name, email) VALUES (Alice, aliceexample.com); INSERT INTO users (name, email) VALUES (Bob, bobexample.com); INSERT INTO users (name, email) VALUES (Carol, carolexample.com); -- Good: Batch insert INSERT INTO users (name, email) VALUES (Alice, aliceexample.com), (Bob, bobexample.com), (Carol, carolexample.com); -- Better: Use COPY for bulk inserts (PostgreSQL) COPY users (name, email) FROM /tmp/users.csv CSV HEADER;量级再往上走如初始化、迁移、数据导入应切换到 PostgreSQL 的COPY命令它绕过标准 INSERT 的逐行处理路径直接以流式方式装载数据是海量导入的事实标准。批量 INSERT 也有实用边界——单条语句不宜无上限堆值一般以数千行为一批分段提交兼顾内存与锁粒度。批量 UPDATE单条 IN 与临时表逐行 UPDATE 的循环写法与逐行 INSERT 同样低效。首选把多行合并进一条IN语句若需要为不同行更新不同值则引入临时表 JOIN 更新-- Bad: Update in loop UPDATE users SET status active WHERE id 1; UPDATE users SET status active WHERE id 2; -- ... repeat for many IDs -- Good: Single UPDATE with IN clause UPDATE users SET status active WHERE id IN (1, 2, 3, 4, 5, ...); -- Better: Use temporary table for large batches CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR); INSERT INTO temp_user_updates VALUES (1, active), (2, active), ...; UPDATE users u SET status t.new_status FROM temp_user_updates t WHERE u.id t.id;当目标行数极大或每行的新值互不相同时先把待更新数据批量装入临时表同样可以走 COPY再用UPDATE ... FROM一次完成关联更新。临时表在会话结束时自动清理无需手动维护。高级技巧物化视图、分区表与查询提示物化视图用存储空间换查询时间对频繁读取、低频变更的重型聚合物化视图把昂贵计算提前固化到磁盘-- Create materialized view CREATE MATERIALIZED VIEW user_order_summary AS SELECT u.id, u.name, COUNT(o.id) as total_orders, SUM(o.total) as total_spent, MAX(o.created_at) as last_order_date FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name; -- Add index to materialized view CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC); -- Refresh materialized view REFRESH MATERIALIZED VIEW user_order_summary; -- Concurrent refresh (PostgreSQL) REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary; -- Query materialized view (very fast) SELECT * FROM user_order_summary WHERE total_spent 1000 ORDER BY total_spent DESC;三个关键点物化视图是物理存储因此可以像普通表一样建索引示例中为total_spent建立了降序索引以加速排序查询刷新策略需要权衡普通REFRESH MATERIALIZED VIEW会锁表并整体重建REFRESH ... CONCURRENTLY支持在线刷新、不阻塞查询但要求物化视图有唯一索引且刷新期间需要额外空间数据新鲜度由业务决定——报表、看板等可容忍分钟级延迟的场景收益最大。分区表把扫描一张大表变成只扫需要的分片分区将逻辑上的一张表拆成多张物理分片查询条件命中分区键时优化器只扫描对应分片-- Range partitioning by date (PostgreSQL) CREATE TABLE orders ( id SERIAL, user_id INT, total DECIMAL, created_at TIMESTAMP ) PARTITION BY RANGE (created_at); -- Create partitions CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM (2024-01-01) TO (2024-04-01); CREATE TABLE orders_2024_q2 PARTITION OF orders FOR VALUES FROM (2024-04-01) TO (2024-07-01); -- Queries automatically use appropriate partition SELECT * FROM orders WHERE created_at BETWEEN 2024-02-01 AND 2024-02-28; -- Only scans orders_2024_q1 partition上例中查询只落在orders_2024_q1一个分片。分区带来的附加收益是运维层面的历史分区可以直接DETACH归档或快速删除避免对大表执行昂贵的 DELETE。需要强调的是分区裁剪的前提是WHERE 条件必须包含分区键此处为created_at否则优化器仍会扫描全部分区。查询提示谨慎使用的人工干预优化器通常足够聪明但当统计信息失真或特定场景下优化器选错计划时可以用查询提示强制干预-- Force index usage (MySQL) SELECT * FROM users USE INDEX (idx_users_email) WHERE email userexample.com; -- Parallel query (PostgreSQL) SET max_parallel_workers_per_gather 4; SELECT * FROM large_table WHERE condition; -- Join hints (PostgreSQL) SET enable_nestloop OFF; -- Force hash or merge join需要清醒认识查询提示的两面性USE INDEX、SET enable_nestloop OFF这类手段属于绕过优化器的兜底方案数据分布变化后可能适得其反。正确顺序永远是先更新统计信息ANALYZE、检查索引缺失最后才考虑提示。最佳实践与常见陷阱八条维护准则SKILL.md 总结了数据库长期健康运行的八条准则索引要有选择性索引过多会拖慢写入持续监控查询性能启用慢查询日志保持统计信息新鲜定期执行 ANALYZE使用合适的数据类型更小的类型意味着更好的性能审慎地规范化在范式与性能之间取平衡缓存高频访问数据应用层缓存兜底使用连接池复用数据库连接定期维护VACUUM、ANALYZE、重建索引对应的日常维护 SQL-- Update statistics ANALYZE users; ANALYZE VERBOSE orders; -- Vacuum (PostgreSQL) VACUUM ANALYZE users; VACUUM FULL users; -- Reclaim space (locks table) -- Reindex REINDEX INDEX idx_users_email; REINDEX TABLE users;注意VACUUM FULL会锁定整表应安排在维护窗口执行常规VACUUM与ANALYZE则可在线进行。七个必须避开的陷阱陷阱后果应对过度建索引每条 INSERT/UPDATE/DELETE 都要同步维护索引只为高频查询路径建索引无用索引浪费磁盘并拖慢写入用pg_stat_user_indexes定期清理索引缺失慢查询、全表扫描结合执行计划补索引隐式类型转换列上套函数/类型转换导致索引失效显式转换并保持类型一致OR 条件多数情况下无法高效利用索引改写为 UNION ALL 或 INLIKE 前导通配符LIKE %abc无法走索引改用前缀匹配或全文检索WHERE 中套函数索引失效除非有表达式索引建函数索引或存储规范化数据其中WHERE 中套函数是最隐蔽的坑WHERE LOWER(email) ...会让users(email)索引完全失效解决办法是创建LOWER(email)表达式索引或干脆在应用层把 email 规范化为小写存储。监控查询让优化有据可依PostgreSQL 的统计视图让哪条查询慢、哪个索引没用上可量化、可排序避免靠猜-- Find slow queries (PostgreSQL) SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; -- Find missing indexes (PostgreSQL) SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg_seq_tup_read FROM pg_stat_user_tables WHERE seq_scan 0 ORDER BY seq_tup_read DESC LIMIT 10; -- Find unused indexes (PostgreSQL) SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY pg_relation_size(indexrelid) DESC;这三条查询分别对应定位慢语句发现该建索引的表和清理没用的索引与上面八条维护准则和七个陷阱形成闭环先用量化数据发现问题再用五大模式改写语句最后用统计视图验证效果并清理冗余索引。总结一套可落地的优化流程把 details.md 与 SKILL.md 的内容串起来就得到一套闭环优化流程量化定位用pg_stat_statements、慢查询日志找出最慢的语句读取执行计划EXPLAIN (ANALYZE, BUFFERS, VERBOSE)确认 Seq Scan、连接方式与实际耗时套用模式N1 用 JOIN/批量加载深分页用游标聚合先过滤再分组子查询改写为 JOIN/CTEDML 全部批量数据侧兜底为过滤列建覆盖/部分/表达式索引重度聚合上物化视图海量时序数据上分区表验证与维护复查执行计划确认 Index Scan/Index Only Scan定期 ANALYZE 保持统计新鲜用统计视图清理无用索引。这套方法论不绑定特定数据库示例以 PostgreSQL 为主兼有 MySQL 语法核心的减少往返、缩小扫描、用索引换扫描三原则在任何关系型数据库上都成立。当你下一次遇到接口响应变慢、数据库 CPU 飙高或报表查询超时不妨先按本文的模式清单逐条排查——大多数性能问题都能在这一套框架内得到系统性解决。【免费下载链接】agentsMulti-harness agentic plugin marketplace for Claude Code, Codex, Cursor, OpenCode, GitHub Copilot, and Google Antigravity项目地址: https://gitcode.com/GitHub_Trending/agents24/agents创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考