agents 市场中的 sql-optimization-patterns 技能：EXPLAIN 分析、索引设计与 SQL 慢查询优化实战指南

agents 市场中的 sql-optimization-patterns 技能EXPLAIN 分析、索引设计与 SQL 慢查询优化实战指南【免费下载链接】agentsMulti-harness agentic plugin marketplace for Claude Code, Codex, Cursor, OpenCode, GitHub Copilot, and Google Antigravity项目地址: https://gitcode.com/GitHub_Trending/agents24/agents本篇技术指南围绕 agents 插件市场多 Harness Agentic 插件市场覆盖 Claude Code、Codex CLI、Cursor、OpenCode、GitHub Copilot 与 Google Antigravity中 sql-optimization-patterns 技能 展开系统讲解该技能内置的三大核心能力EXPLAIN 查询执行计划分析、索引类型选择与创建策略、慢查询优化模式含 N1 消除、游标分页、批处理等实战模式。读完后你将能够复制该技能中的完整 SQL 模板独立完成慢查询定位、索引设计与 PostgreSQL 性能调优。技能在 agents 市场中的定位与使用前提该技能位于 developer-essentials 插件版本 1.0.4的skills/sql-optimization-patterns/目录下与 Git 工作流、错误处理、代码审查、E2E 测试、认证实现等 11 个开发者必备技能同属一个插件技能目录清单见 docs/agent-skills.md 的 Developer Essentials 一节。从源码结构看该技能采用两级渐进式披露progressive disclosure组织SKILL.md 是导航层navigation tier包含场景清单、EXPLAIN 基础、索引策略、核心查询优化模式与监控 SQLAgent 激活技能时优先加载references/details.md 是详情层存放 5 大优化模式的完整对照示例Bad/Good/Better与物化视图、分区等进阶技术SKILL.md 中明确指示当导航层内容不足以解决问题时再读取该文件。这种分层设计的目的是控制上下文 token 占用常规优化任务只需导航层深度模式如 N1 重构、游标分页改造才展开详情层。适用场景技能 frontmatter 与正文给出的触发场景When to Use包括调试慢查询Debugging slow-running queries设计高性能数据库 schema优化应用响应时间降低数据库负载与成本为增长中的数据集提升可扩展性分析 EXPLAIN 查询计划实现高效索引解决 N1 查询问题。安装方式根据 README 的 Quick start该技能可通过两条路径安装# 方式一Claude Code 市场安装整个插件 /plugin marketplace add wshobson/agents /plugin install developer-essentials # 方式二只安装单个技能任意支持 Agent Skills 的 Harness无需 clone gh skill install wshobson/agents sql-optimization-patterns npx skills add wshobson/agents --skill sql-optimization-patterns注意技能本身只包含 Markdown 知识包SQL 模板与模式说明不包含可执行代码文中 SQL 以 PostgreSQL 为主要方言EXPLAIN、VACUUM、GIN/GiST/BRIN、物化视图并发刷新、分区表等均为 PostgreSQL 特性仅在Query Hints小节出现 MySQL 的USE INDEX示例。核心概念一用 EXPLAIN 读懂查询执行计划EXPLAIN 输出是优化的起点。技能给出三种粒度递增的分析方式-- Basic explain仅估算计划不执行 EXPLAIN SELECT * FROM users WHERE email userexample.com; -- With actual execution stats真实执行并采集统计 EXPLAIN ANALYZE SELECT * FROM users WHERE email userexample.com; -- Verbose output with more details含缓冲区与详细输出 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;三者区别在于EXPLAIN只给出基于统计信息的估算计划EXPLAIN ANALYZE会真正执行语句并附加实际行数与实际耗时(ANALYZE, BUFFERS, VERBOSE)进一步暴露共享/本地缓冲区的命中与读取次数用于判断 I/O 瓶颈。技能要求重点盯住以下指标Key Metrics to Watch指标含义与判断Seq Scan全表扫描大表上通常是性能问题信号Index Scan走了索引良好Index Only Scan只读索引不触碰表数据最佳Nested Loop连接方式小数据集上可以接受Hash Join连接方式大数据集上通常表现好Merge Join连接方式适合已排序数据Cost估算查询代价越低越好Rows估算返回行数Actual Time真实执行时间实际排查流程可以这样串起来先用EXPLAIN (ANALYZE, BUFFERS)捕获慢查询计划若看到大表 Seq Scan 且 Rows 估算与实际偏差巨大多半是统计信息过期对应后文的ANALYZE维护若估算行数合理却仍然慢则检查 Join 方式选择与缓冲区命中率。核心概念二索引类型选择与创建策略技能将索引称为最强大的优化工具并列出五种索引类型及其适用边界B-Tree默认类型适合等值与范围查询Hash仅适合等值比较GIN全文检索、数组查询、JSONBGiST几何数据、全文检索BRINBlock Range INdex面向体量巨大且键值与物理位置存在相关性的表。对应的七类索引创建示例完整继承自 SKILL.md-- Standard B-Tree index CREATE INDEX idx_users_email ON users(email); -- Composite index列顺序重要 CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- Partial index只索引行子集 CREATE INDEX idx_active_users ON users(email) WHERE status active; -- Expression index表达式/函数索引 CREATE INDEX idx_users_lower_email ON users(LOWER(email)); -- Covering indexINCLUDE 附加列支撑 Index Only Scan CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name, created_at); -- Full-text search index CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector(english, title || || body)); -- JSONB index CREATE INDEX idx_metadata ON events USING GIN(metadata);这些示例各自针对一类典型问题复合索引解决按用户查其特定状态订单的等值过滤组合部分索引把索引体积限制在活跃行上表达式索引让LOWER(email)这类函数过滤能命中索引呼应后文WHERE 中使用函数导致索引失效的坑INCLUDE覆盖索引使高频查询可以只扫描索引页完成GIN 索引则分别支撑全文检索与 JSONB 元数据查询。从示例组合可以推断该技能的索引设计优先级先用 B-Tree 覆盖高频等值/范围路径用部分索引裁剪写入放大用覆盖索引换取 Index Only Scan只有全文/JSONB/几何场景才引入 GIN/GiST超大且有序表才考虑 BRIN。核心概念三查询优化基本模式避免 SELECT *只取需要的列减少 I/O 与网络传输也让覆盖索引有机会生效-- Bad: Fetches unnecessary columns SELECT * FROM users WHERE id 123; -- Good: Fetch only what you need SELECT id, email, name FROM users WHERE id 123;WHERE 子句中高效使用函数对列施加函数会导致普通索引失效技能给出两种解法——建表达式索引或存储归一化数据-- Bad: Function prevents index usage SELECT * FROM users WHERE LOWER(email) userexample.com; -- Good: 建表达式索引后复用该写法 CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- Then: SELECT * FROM users WHERE LOWER(email) userexample.com; -- 或存储归一化数据后直接精确匹配 SELECT * FROM users WHERE email userexample.com;优化 JOIN技能区分了三个层次隐式连接逗号笛卡尔积后过滤、显式 JOIN 过滤、以及先过滤再连接-- Bad: Cartesian product then filter SELECT u.name, o.total FROM users u, orders o WHERE u.id o.user_id AND u.created_at 2024-01-01; -- Good: Filter before join SELECT u.name, o.total FROM users u JOIN orders o ON u.id o.user_id WHERE u.created_at 2024-01-01; -- Better: Filter both tables SELECT u.name, o.total FROM (SELECT * FROM users WHERE created_at 2024-01-01) u JOIN orders o ON u.id o.user_id;需要说明的是现代优化器通常会自行重排谓词与连接顺序先过滤再连接的收益主要体现在优化器统计信息不准或无法自动下推的场景如视图、部分方言技能将其作为可复制的书写习惯给出读者可在自己数据库版本上用 EXPLAIN 验证优化前后代价变化。进阶模式详解来自 references/details.md 的五大模式SKILL.md 导航层内容不足以覆盖的模式在该技能的详情层 references/details.md 中以问题—Bad—Good—Better对照形式展开。模式 1消除 N1 查询典型反模式是循环内逐行查询# 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 orders解法一是 JOIN解法二是批量加载batch loading-- Solution 1: 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); -- Solution 2: Batch query SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);# Good: Single query with JOIN or batch load 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) ) # 或批量加载 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)两种方案的取舍JOIN 方案一次往返但结果集存在用户×订单的扇出批量加载方案保持两张表的行粒度分离在内存中按user_id分组更适合对象关系映射ORM场景下的按需装配。模式 2优化分页大偏移量下OFFSET需要扫描并丢弃大量行-- Slow for large offsets SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100000; -- Very slow!游标分页Cursor-Based Pagination改为以上一页最后一条的排序键为起点-- 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);技能特别标注Requires index游标分页的性能收益依赖(created_at DESC, id DESC)这类与排序键完全对齐的复合索引使用(created_at, id) (...)行比较写法可避免同一created_at值下翻页错位代价是要求数据库支持行构造器比较PostgreSQL 支持。模式 3高效聚合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来自统计信息是估算值仅在展示大约多少行类场景可接受精确计数仍依赖带过滤条件的COUNT(*)配合单列索引有机会退化为 Index Only Scan。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);模式 4子查询优化相关子查询会对外表每一行重复执行技能给出 JOIN聚合 与 窗口函数两种改写并展示 CTE 提升可读性-- 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; -- Better: Use window functions 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;-- Using Common Table Expressions 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;两种改写各有定位JOIN聚合在大多数数据库上执行稳定窗口函数方案避免GROUP BY后丢失行粒度适合需要逐行携带聚合值的报表查询PostgreSQL 的DISTINCT ON用于每个u.id只保留一行。CTE 本身不必然带来性能收益其价值在于把30 天新用户与30 天订单计数两个过滤逻辑显式分离便于复用与审查。模式 5批量操作批量 INSERT 从逐条执行到多值单条语句再到 PostgreSQL 的COPY-- 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;批量 UPDATE 从循环单条更新到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;IN (...)列表过长的 SQL 在解析与规划上都有开销且各数据库对参数上限不同临时表方案把待更新集合落为关系数据UPDATE ... FROM按主键 JOIN适合数千行以上的批量变更。进阶技术物化视图、分区与查询提示物化视图预计算昂贵查询对高频访问的聚合结果物化视图把每次现算变成定期刷新-- 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;注意CONCURRENTLY刷新有前提物化视图上必须存在唯一索引PostgreSQL 的要求技能示例中建立的是total_spent DESC索引生产环境通常还需补一个唯一索引例如基于u.id。数据新鲜度与查询速度的权衡由刷新频率决定。分区拆分超大表按时间范围分区后带时间谓词的查询只会扫描命中分区分区裁剪-- 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分区同时改善了维护操作过期季度分区可以直接整体 detach 或归档而不是逐行 DELETE。查询提示与优化器开关-- 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 joinUSE INDEX是 MySQL 方言PostgreSQL 没有索引提示语法惯用做法是会话级关闭某类计划路径如enable_nestloop OFF或调整并行参数让优化器在受限空间中重新选择。这类开关只应在会话内、针对已复现的慢查询做 A/B 验证后使用不宜写进全局配置。最佳实践与定期维护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不长时间阻塞 DML为主。统计信息过期是索引明明存在却走 Seq Scan的常见根因ANALYZE刷新后优化器的 Cost/Rows 估算才会回到可信区间。常见陷阱清单技能汇总了七类高频陷阱可作为慢查询排查的自查清单过度索引Over-Indexing每个索引都会拖慢 INSERT/UPDATE/DELETE未使用索引Unused Indexes白占空间且持续拖慢写入索引缺失Missing Indexes慢查询、全表扫描隐式类型转换谓词两侧类型不一致会导致索引失效OR 条件跨列 OR 难以高效命中索引前导通配符的 LIKELIKE %abc无法使用 B-Tree 索引WHERE 中的函数除非存在对应表达式索引否则索引失效。监控定位慢查询、缺失索引与冗余索引技能的最后一节给出三条 PostgreSQL 诊断 SQL分别回答谁慢该建什么索引该删什么索引-- 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;使用前提与局限pg_stat_statements视图依赖同名统计扩展启用且需重启数据库或重载配置后才开始累积统计值在数据库重载/重启后会清零需按观察窗口解读缺失索引查询以seq_tup_read顺序扫描读取的总行数排序优先暴露每次全表扫都扫很多行的表未使用索引查询以索引实际占用空间pg_relation_size(indexrelid)倒序先删最贵的僵尸索引。注意idx_scan 0可能只是观察窗口内没有命中低频但关键如月度报表的索引需人工复核后再决定去留。小结从技能文件到落地流程该技能的完整使用路径可以归纳为安装技能/plugin install developer-essentials或gh skill install/npx skills add单技能方式→ 触发场景匹配后加载 SKILL.md 导航层 → 按EXPLAIN 定位 → 索引策略 → 查询改写三板斧处理常规问题 → 需要 N1、分页、聚合、子查询、批量操作等深度模式时展开 references/details.md → 用监控 SQL 闭环验证优化效果 → 以 ANALYZE/VACUUM/REINDEX 维持长期健康。所有 SQL 模板均可直接复制主要适用前提为 PostgreSQL 方言个别提示语法标注为 MySQL跨数据库迁移时需按目标方言核对语法支持。该技能与同插件的 monorepo-architect 智能体 等组件共同构成 developer-essentials 的开发者工具集整个插件市场的目录结构与多 Harness 分发机制可进一步参考 README 与 docs/agent-skills.md。【免费下载链接】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），仅供参考