claude-skills SQL Pro 实战从执行计划到索引设计的完整 SQL 查询优化指南【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills导读本文是 claude-skills 仓库中sql-pro技能67 个全栈开发者专用技能之一的核心优化手册系统讲解 SQL 查询性能调优的完整链路从 EXPLAIN 执行计划分析、索引设计与维护、查询重写模式到分区策略、物化视图与性能监控。读完本文你将掌握一套可落地的优化方法论能够独立诊断慢查询、设计高效的索引结构并用量化手段验证优化效果。文章中的全部 SQL 示例均可在 PostgreSQL、MySQL、SQL Server 等主流数据库上对照实践适用于日常开发中任何查询变慢的场景。一、技能定位sql-pro 在 claude-skills 中的角色在 claude-skills 仓库中sql-pro是 语言类技能 中的一员12 个语言技能之一见 SKILLS_GUIDE.md其 SKILL.md 定义了五个核心工作流步骤Schema Analysis审查数据库结构、索引、查询模式与性能瓶颈Design使用 CTE、窗口函数、合适的 JOIN 构造基于集合的操作Optimize分析执行计划、实现覆盖索引、消除大表顺序扫描Verify运行EXPLAIN ANALYZE确认大表上无顺序扫描若未达到sub-100ms 目标则迭代索引选择或查询重写Document输出查询解释、索引设计理由与性能指标。本技能采用按需加载参考文档的机制SKILL.md 中的主题表列出了五份 referencesquery-patterns.md、window-functions.md、optimization.md、database-design.md、dialect-differences.md。其中optimization.md正是本文的主体——执行计划、索引、统计信息与调优手段的权威参考。二、执行计划分析一切优化的起点优化第一原则动手优化前永远先运行 EXPLAIN。没有执行计划支撑的优化都是猜测。2.1 PostgreSQLEXPLAIN ANALYZE 的完整用法-- PostgreSQL EXPLAIN ANALYZE EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT c.customer_id, c.name, COUNT(o.order_id) as order_count, SUM(o.total) as lifetime_value FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id WHERE c.created_at 2024-01-01 GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) 5;该查询统计 2024 年以来每个客户的订单数与累计消费额并筛选出订单数超过 5 的客户。(ANALYZE, BUFFERS, VERBOSE)是实战中最常用的选项组合其中BUFFERS会额外输出缓存命中信息VERBOSE显示完整的输出列与排序信息。执行计划中需要重点阅读的指标如下指标含义判断标准Planning Time生成执行计划的时间过短可能意味着统计信息缺失Execution Time实际运行耗时对照技能中 sub-100ms 的性能目标Seq Scan顺序扫描整个表大表上出现即为危险信号Index Scan通过索引定位数据期望看到的高效访问路径Rows预估行数 vs 实际行数差距大 统计信息过期需 ANALYZEBuffersshared hit 缓存命中read 磁盘 I/Oread 偏高说明缺索引或缓存未命中Loops嵌套循环迭代次数次数过多且内层为大表时会放大成本2.2 MySQLJSON 格式执行计划-- MySQL EXPLAIN EXPLAIN FORMATJSON SELECT * FROM orders o INNER JOIN customers c ON o.customer_id c.customer_id WHERE o.order_date 2024-01-01 AND c.country US;FORMATJSON输出比传统表格更详细的结构化信息包含cost_info成本估算、used_columns用到的列以及attached_condition下推条件等关键字段便于程序化解析与对比优化前后成本。2.3 SQL Server统计 I/O 与耗时-- SQL Server execution plan SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT ...; -- Check actual vs estimated rows SELECT * FROM sys.dm_exec_query_stats;SQL Server 通过SET STATISTICS IO ON / TIME ON在消息窗口输出逻辑/物理读取次数与各阶段耗时sys.dm_exec_query_stats动态管理视图则能查询到历史执行的 CPU 时间、逻辑读写以及实际行数rows与预估行数estimated_rows的对比用于识别统计信息偏差。2.4 执行计划解读速查结合 SKILL.md 中的 EXPLAIN ANALYZE 解释指引看到以下信号时应立即采取行动大表上出现 Seq Scan→ 添加或修正索引actual rows 远超 estimated rows→ 执行ANALYZE table刷新统计信息Buffers 中 read 数量很高→ 缓存未命中通常意味着缺索引导致扫描范围过大。三、索引设计与优化覆盖、复合、部分、表达式与 GIN技能约束SKILL.md明确要求为高频查询创建覆盖索引这是最有效也最容易被忽略的优化手段。3.1 覆盖索引Covering Index让查询不访问表-- Covering index (all columns in index) CREATE INDEX idx_orders_covering ON orders ( customer_id, order_date ) INCLUDE (total, status); -- Query uses index-only scan (no table access needed) SELECT customer_id, order_date, total, status FROM orders WHERE customer_id 123 AND order_date 2024-01-01;INCLUDE子句将total、status作为负载列存入索引但不参与排序与匹配。当查询需要的所有列都已在索引中时PostgreSQL 会执行Index-Only Scan完全跳过堆表访问显著降低 I/O。SKILL.md 中的 before/after 示例也展示了配套思路CREATE INDEX idx_order_items_order_qty ON order_items (order_id) INCLUDE (quantity);。3.2 复合索引列顺序至关重要-- Composite index (order matters!) CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC); -- Good: WHERE customer_id X AND order_date Y -- Good: WHERE customer_id X -- Bad: WHERE order_date Y (doesnt use index)复合索引遵循最左前缀原则索引(customer_id, order_date)能服务按客户 日期范围的查询也能单独服务按客户的查询但无法单独服务按日期的查询。将等值条件的列放在前面、范围条件的列放在后面是排序与过滤效率最大化的通用准则。database-design.md 进一步说明该复合索引还能支持WHERE customer_id ? ORDER BY order_date DESC避免额外的排序操作。3.3 部分索引Partial/Filtered Index更小、更快-- Partial/Filtered index (smaller, faster) CREATE INDEX idx_active_orders ON orders (customer_id, order_date) WHERE status active; -- Only used when query includes the filter SELECT * FROM orders WHERE customer_id 123 AND status active AND order_date 2024-01-01;只索引status active的行索引体积大幅缩小、写入开销降低、扫描更快——但代价是查询必须携带同样的过滤条件才能命中该索引。此模式非常适合软删除归档数据等低频值占绝大多数的表database-design.md 中的idx_active_products与唯一部分索引idx_users_active_emailWHERE deleted_at IS NULL都是同类实践。3.4 表达式索引函数包住列会让普通索引失效-- Expression/Function-based index CREATE INDEX idx_users_lower_email ON users (LOWER(email)); -- Now this uses the index SELECT * FROM users WHERE LOWER(email) userexample.com;在列上应用函数如LOWER(email)会阻止普通 B-tree 索引生效。解决方式是把函数表达式本身建成索引使查询中的表达式与索引表达式严格一致。注意表达式索引要求查询里的函数写法与索引定义逐字匹配。3.5 GIN 索引数组与 JSONB 的专用武器PostgreSQL-- GIN index for arrays/JSONB (PostgreSQL) CREATE INDEX idx_products_tags ON products USING GIN (tags); SELECT * FROM products WHERE tags ARRAY[electronics, sale]; CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata jsonb_path_ops); SELECT * FROM orders WHERE metadata {priority: high};对于数组包含和 JSONB 文档查询普通 B-tree 无能为力必须使用 GINGeneralized Inverted Index。jsonb_path_ops操作符类比默认的jsonb_ops索引更小、查询更快代价是不支持某些仅顶层键的查询操作符适用于键名已知且值精确匹配的场景。四、索引维护找到缺失、未用与重复的索引索引不是建完就万事大吉需要定期用统计视图体检。4.1 找出缺失索引顺序扫描过于频繁的表-- PostgreSQL: Find missing indexes SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan as avg_seq_read FROM pg_stat_user_tables WHERE seq_scan 0 AND seq_tup_read / seq_scan 10000 ORDER BY seq_tup_read DESC;当某表顺序扫描次数多、且每次扫描读取的行数巨大avg_seq_read超过 10000说明查询在反复全表遍历而索引没有被有效利用这是该建索引却没建的最强信号。4.2 找出未使用索引白白占用写开销与空间-- Find unused indexes SELECT schemaname, tablename, indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes WHERE idx_scan 0 AND indexrelname NOT LIKE pg_toast% ORDER BY pg_relation_size(indexrelid) DESC;idx_scan 0表示索引从未被使用过。每个索引都会拖慢 INSERT/UPDATE/DELETE 并占用存储未使用索引应优先考虑删除对主键/唯一约束索引需谨慎评估后操作。4.3 找出重复索引-- Find duplicate indexes SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) as size, (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2, (array_agg(idx))[3] as idx3 FROM ( SELECT indexrelid::regclass as idx, (indrelid::text ||E\n|| indclass::text ||E\n|| indkey::text ||E\n|| COALESCE(indexprs::text,)||E\n|| COALESCE(indpred::text,)) as key FROM pg_index ) sub GROUP BY key HAVING COUNT(*) 1 ORDER BY SUM(pg_relation_size(idx)) DESC;该查询将每个索引的所属表indrelid、操作符类indclass、键列indkey、表达式indexprs与谓词indpred拼接成指纹并分组指纹相同的索引即为冗余可安全删除以节省空间并降低写入成本。4.4 重建索引与更新统计信息-- Reindex to reduce bloat REINDEX INDEX CONCURRENTLY idx_orders_customer_date; -- Update statistics ANALYZE orders; ANALYZE VERBOSE; -- Show progress长期高并发的 B-tree 索引会产生页空洞bloatREINDEX INDEX CONCURRENTLY在不阻塞读写的前提下重建索引普通REINDEX会锁表生产环境慎用。ANALYZE刷新表的统计信息让优化器拿到准确的行数估算——这正是第一节中预估行数 vs 实际行数差距大的正解。五、查询重写模式用集合思维替代低效写法技能约束SKILL.md中的 MUST DO 强调用集合操作替代逐行处理“用 EXISTS 替代 COUNT 做存在性检查”“尽可能早地应用过滤条件”。5.1 用 EXISTS 替代 SELECT DISTINCT-- Avoid SELECT DISTINCT when possible -- Bad: Forces sort/dedup SELECT DISTINCT customer_id FROM orders WHERE status active; -- Good: Use EXISTS SELECT customer_id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.status active );SELECT DISTINCT会触发排序或哈希去重而是否存在匹配行本质是布尔判断EXISTS在找到第一条匹配后即停止语义更清晰、性能通常更优。这也是 query-patterns.md 中EXISTS 用于关联子查询、IN 用于小规模静态列表的通用准则。5.2 用 NOT EXISTS 替代 NOT IN-- Avoid NOT IN with NULLs -- Bad: NULL handling issues and poor performance SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); -- Good: Use NOT EXISTS SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );NOT IN在子查询结果包含 NULL 时会退化为整表无结果的诡异行为三值逻辑陷阱且优化器难以为其生成高效计划。NOT EXISTS语义明确、无 NULL 陷阱性能也更稳定。等价的反连接写法还有LEFT JOIN ... WHERE o.customer_id IS NULL见 query-patterns.md。5.3 尽早下推过滤条件-- Push down filtering early -- Bad: Filter after JOIN SELECT c.*, o.* FROM customers c JOIN orders o ON c.customer_id o.customer_id WHERE c.country US AND o.order_date 2024-01-01; -- Good: Use WHERE in subquery/CTE to reduce JOIN size WITH us_customers AS ( SELECT customer_id, name FROM customers WHERE country US ), recent_orders AS ( SELECT customer_id, order_id, total FROM orders WHERE order_date 2024-01-01 ) SELECT c.*, o.* FROM us_customers c JOIN recent_orders o ON c.customer_id o.customer_id;先在子查询/CTE 内过滤掉不满足条件的行再参与 JOIN能让进入连接阶段的数据量显著缩小。虽然现代优化器通常会自动重排谓词但在统计信息不准或优化器决策不佳时手工下推仍是最可靠的兜底手段也契合技能Filter early, aggregate late的最佳实践。5.4 消除 SELECT 中的标量子查询N1 问题-- Avoid scalar subqueries in SELECT -- Bad: N1 problem SELECT p.product_id, p.name, (SELECT COUNT(*) FROM reviews WHERE product_id p.product_id) as review_count FROM products p; -- Good: Single JOIN with GROUP BY SELECT p.product_id, p.name, COUNT(r.review_id) as review_count FROM products p LEFT JOIN reviews r ON p.product_id r.product_id GROUP BY p.product_id, p.name;对结果集的每一行执行一次子查询就是经典的 N1 问题1000 个商品会执行 1000 次计数。改用一次LEFT JOIN GROUP BY后所有统计在单次扫描中完成。SKILL.md 中的 before/after 示例用同样的手法优化了订单明细聚合并配套覆盖索引进一步消除回表。六、分区策略大表治理的结构性手段最佳实践清单第 8 条大表超过 1000 万行考虑分区。分区是索引之外、针对超大表的最后防线。6.1 范围分区Range Partitioning-- Range partitioning by date (PostgreSQL) CREATE TABLE orders ( order_id SERIAL, customer_id INT, order_date DATE NOT NULL, total DECIMAL(10,2) ) PARTITION BY RANGE (order_date); 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); -- Partition pruning in action EXPLAIN SELECT * FROM orders WHERE order_date 2024-02-01 AND order_date 2024-03-01; -- Only scans orders_2024_q1 partition按日期范围分区的核心收益是Partition Pruning分区裁剪查询范围落在哪个分区优化器就只扫描哪个分区上例中仅扫描orders_2024_q1同时还能支持按季度整体 DROP/归档的运维便利。6.2 列表分区List Partitioning-- List partitioning by category CREATE TABLE products ( product_id SERIAL, category VARCHAR(50) NOT NULL, name VARCHAR(200) ) PARTITION BY LIST (category); CREATE TABLE products_electronics PARTITION OF products FOR VALUES IN (electronics, computers, phones); CREATE TABLE products_clothing PARTITION OF products FOR VALUES IN (clothing, shoes, accessories);适合枚举值有限且查询天然按类别隔离的业务例如按商品类目、按地区、按业务线拆分物理存储。6.3 哈希分区Hash Partitioning-- Hash partitioning for even distribution CREATE TABLE users ( user_id SERIAL, email VARCHAR(255) ) PARTITION BY HASH (user_id); CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);对分区键做哈希取模MODULUS 4, REMAINDER 0~3数据均匀散落到 4 个分区适合无法用范围/列表切分、但写入并发压力极大的场景如用户表。注意哈希分区无法做分区裁剪其价值在于分散写入热点与并行扫描。七、物化视图预计算换取查询速度7.1 创建与唯一索引-- Create materialized view for expensive aggregations CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT DATE_TRUNC(day, order_date) as day, COUNT(*) as order_count, SUM(total) as revenue, AVG(total) as avg_order_value, COUNT(DISTINCT customer_id) as unique_customers FROM orders GROUP BY DATE_TRUNC(day, order_date); CREATE UNIQUE INDEX idx_daily_sales_day ON daily_sales_summary (day);将按天聚合订单这一昂贵计算固化为一物化视图查询直接读结果。唯一索引是REFRESH CONCURRENTLY的前置条件务必先建。7.2 并发刷新与触发器自动刷新-- Refresh strategy REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; -- Auto-refresh with trigger (PostgreSQL) CREATE OR REPLACE FUNCTION refresh_daily_sales() RETURNS TRIGGER AS $$ BEGIN REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_refresh_daily_sales AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH STATEMENT EXECUTE FUNCTION refresh_daily_sales();REFRESH ... CONCURRENTLY在不锁读的前提下增量更新视图代价是更慢的刷新速度。若业务对实时性要求高可用FOR EACH STATEMENT触发器在orders每次写操作后自动刷新——但要意识到这会放大写路径开销写频繁时更推荐**定时任务如 cron / pg_cron**而非触发器。窗口函数相关的昂贵计算同样适合物化见 window-functions.md 中product_rankings物化视图的示例。八、查询提示最后的强力手段务必谨慎原则提示Hints是绕过优化器决策的强制手段能用索引/重写解决就不要用且每次升级数据库后需重新验证。8.1 PostgreSQL 与 SQL Server-- PostgreSQL: Force index usage (use sparingly) SET enable_seqscan OFF; SELECT /* IndexScan(orders idx_orders_customer) */ * FROM orders WHERE customer_id 123; SET enable_seqscan ON; -- SQL Server: Query hints SELECT * FROM orders WITH (INDEX(idx_orders_customer_date)) WHERE customer_id 123; -- Force specific join type SELECT * FROM customers c INNER MERGE JOIN orders o ON c.customer_id o.customer_id;PostgreSQL 关闭enable_seqscan只是提示优化器降低顺序扫描优先级会话级开关配合/* ... */注释形式的提示需启用 pg_hint_plan 扩展可进一步约束访问路径。SQL Server 用表提示WITH (INDEX(...))指定索引用INNER MERGE JOIN强制合并连接。这些手段适合统计信息异常、优化器误判的特殊时刻。8.2 MySQL 与并行查询-- MySQL: Index hints SELECT * FROM orders USE INDEX (idx_orders_customer_date) WHERE customer_id 123; SELECT * FROM orders FORCE INDEX (idx_orders_customer_date) WHERE customer_id 123; -- PostgreSQL: Parallel query tuning SET max_parallel_workers_per_gather 4; ALTER TABLE large_table SET (parallel_workers 4);USE INDEX仅是建议优化器可忽略FORCE INDEX是强制。并行查询调优方面max_parallel_workers_per_gather控制单查询可用的并行 Worker 数ALTER TABLE ... SET (parallel_workers 4)则按表指定并行度——对超大表的聚合扫描往往有立竿见影的效果但受 CPU 核数与内存上限约束。九、性能监控让慢查询无处遁形9.1 从 pg_stat_statements 定位慢查询-- PostgreSQL: Find slow queries SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time, rows / calls as avg_rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;pg_stat_statements是 PostgreSQL 的语句级统计视图需在shared_preload_libraries中启用扩展按平均执行时间排序即可快速锁定 TOP 20 慢查询配合calls调用次数与avg_rows判断是偶发超慢还是高频次慢。9.2 定位锁阻塞-- Find blocking queries SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted;该查询通过自关联pg_locks找出等待锁granted false的会话及其对应的持锁会话从而直接看到谁阻塞了谁、各自的 SQL 语句是什么——是排查数据库突然卡死类问题的标准脚本。9.3 表膨胀检测-- Table bloat detection SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||.||tablename)) as size, n_dead_tup, n_live_tup, ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup n_dead_tup, 0), 2) as dead_pct FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC;n_dead_tup死元组占比过高说明 VACUUM 跟不上写入频率表与索引正在膨胀。dead_pct明显偏高时应检查 autovacuum 配置或手动VACUUM (ANALYZE)必要时结合第四节中的REINDEX处理索引膨胀。十、最佳实践清单一份可复印进团队规范的行动指南先 EXPLAIN ANALYZE 再动手优化——用数据说话在外键与 WHERE/JOIN 列上建索引——连接与过滤的最基本保障高频查询使用覆盖索引——Index-Only Scan 消除回表定期 ANALYZE 保持统计信息新鲜——确保优化器估算准确避免 SELECT *只选所需列——减少传输量与回表概率技能 MUST NOT DO 也明确禁止在生产查询中使用SELECT *见 SKILL.md子查询用 EXISTS 而非 IN——尤其在大集合与关联场景先过滤、后聚合——让进入聚合的数据量最小超 1000 万行的大表考虑分区——分区裁剪 运维便利昂贵聚合用物化视图——预计算结果换查询速度监控慢查询日志与 pg_stat_statements——建立持续优化循环。此外技能的输出模板SKILL.md要求任何一次优化交付都应包含带行内注释的优化后 SQL、配套索引及设计理由、执行计划分析、优化前后性能指标、平台相关注意事项——这正是把上述技巧沉淀为可审查、可复现工作产物的标准格式。结语把优化变成可验证的科学SQL 查询优化没有玄学。本文给出的完整方法论——先用 EXPLAIN含 ANALYZE/BUFFERS建立基准再按索引设计覆盖/复合/部分/表达式/GIN与查询重写EXISTS、过滤下推、消除标量子查询两条主线实施改进用统计视图持续监控验证——与 claude-skills 中sql-pro技能的五个工作流步骤完全一致分析 Schema → 设计集合操作 → 优化执行计划 →EXPLAIN ANALYZE验证 sub-100ms 目标 → 输出带索引理由与性能指标的文档。将本文与仓库中的 query-patterns.mdCTE、递归 CTE、LATERAL JOIN、PIVOT、window-functions.md排名、滚动聚合、LAG/LEAD、百分位、database-design.md范式、索引策略以及 dialect-differences.md多方言差异配合使用你便拥有了一套覆盖建模—查询—优化—验证全流程的数据库调优工具箱。【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
claude-skills SQL Pro 实战：从执行计划到索引设计的完整 SQL 查询优化指南
claude-skills SQL Pro 实战从执行计划到索引设计的完整 SQL 查询优化指南【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills导读本文是 claude-skills 仓库中sql-pro技能67 个全栈开发者专用技能之一的核心优化手册系统讲解 SQL 查询性能调优的完整链路从 EXPLAIN 执行计划分析、索引设计与维护、查询重写模式到分区策略、物化视图与性能监控。读完本文你将掌握一套可落地的优化方法论能够独立诊断慢查询、设计高效的索引结构并用量化手段验证优化效果。文章中的全部 SQL 示例均可在 PostgreSQL、MySQL、SQL Server 等主流数据库上对照实践适用于日常开发中任何查询变慢的场景。一、技能定位sql-pro 在 claude-skills 中的角色在 claude-skills 仓库中sql-pro是 语言类技能 中的一员12 个语言技能之一见 SKILLS_GUIDE.md其 SKILL.md 定义了五个核心工作流步骤Schema Analysis审查数据库结构、索引、查询模式与性能瓶颈Design使用 CTE、窗口函数、合适的 JOIN 构造基于集合的操作Optimize分析执行计划、实现覆盖索引、消除大表顺序扫描Verify运行EXPLAIN ANALYZE确认大表上无顺序扫描若未达到sub-100ms 目标则迭代索引选择或查询重写Document输出查询解释、索引设计理由与性能指标。本技能采用按需加载参考文档的机制SKILL.md 中的主题表列出了五份 referencesquery-patterns.md、window-functions.md、optimization.md、database-design.md、dialect-differences.md。其中optimization.md正是本文的主体——执行计划、索引、统计信息与调优手段的权威参考。二、执行计划分析一切优化的起点优化第一原则动手优化前永远先运行 EXPLAIN。没有执行计划支撑的优化都是猜测。2.1 PostgreSQLEXPLAIN ANALYZE 的完整用法-- PostgreSQL EXPLAIN ANALYZE EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT c.customer_id, c.name, COUNT(o.order_id) as order_count, SUM(o.total) as lifetime_value FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id WHERE c.created_at 2024-01-01 GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) 5;该查询统计 2024 年以来每个客户的订单数与累计消费额并筛选出订单数超过 5 的客户。(ANALYZE, BUFFERS, VERBOSE)是实战中最常用的选项组合其中BUFFERS会额外输出缓存命中信息VERBOSE显示完整的输出列与排序信息。执行计划中需要重点阅读的指标如下指标含义判断标准Planning Time生成执行计划的时间过短可能意味着统计信息缺失Execution Time实际运行耗时对照技能中 sub-100ms 的性能目标Seq Scan顺序扫描整个表大表上出现即为危险信号Index Scan通过索引定位数据期望看到的高效访问路径Rows预估行数 vs 实际行数差距大 统计信息过期需 ANALYZEBuffersshared hit 缓存命中read 磁盘 I/Oread 偏高说明缺索引或缓存未命中Loops嵌套循环迭代次数次数过多且内层为大表时会放大成本2.2 MySQLJSON 格式执行计划-- MySQL EXPLAIN EXPLAIN FORMATJSON SELECT * FROM orders o INNER JOIN customers c ON o.customer_id c.customer_id WHERE o.order_date 2024-01-01 AND c.country US;FORMATJSON输出比传统表格更详细的结构化信息包含cost_info成本估算、used_columns用到的列以及attached_condition下推条件等关键字段便于程序化解析与对比优化前后成本。2.3 SQL Server统计 I/O 与耗时-- SQL Server execution plan SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT ...; -- Check actual vs estimated rows SELECT * FROM sys.dm_exec_query_stats;SQL Server 通过SET STATISTICS IO ON / TIME ON在消息窗口输出逻辑/物理读取次数与各阶段耗时sys.dm_exec_query_stats动态管理视图则能查询到历史执行的 CPU 时间、逻辑读写以及实际行数rows与预估行数estimated_rows的对比用于识别统计信息偏差。2.4 执行计划解读速查结合 SKILL.md 中的 EXPLAIN ANALYZE 解释指引看到以下信号时应立即采取行动大表上出现 Seq Scan→ 添加或修正索引actual rows 远超 estimated rows→ 执行ANALYZE table刷新统计信息Buffers 中 read 数量很高→ 缓存未命中通常意味着缺索引导致扫描范围过大。三、索引设计与优化覆盖、复合、部分、表达式与 GIN技能约束SKILL.md明确要求为高频查询创建覆盖索引这是最有效也最容易被忽略的优化手段。3.1 覆盖索引Covering Index让查询不访问表-- Covering index (all columns in index) CREATE INDEX idx_orders_covering ON orders ( customer_id, order_date ) INCLUDE (total, status); -- Query uses index-only scan (no table access needed) SELECT customer_id, order_date, total, status FROM orders WHERE customer_id 123 AND order_date 2024-01-01;INCLUDE子句将total、status作为负载列存入索引但不参与排序与匹配。当查询需要的所有列都已在索引中时PostgreSQL 会执行Index-Only Scan完全跳过堆表访问显著降低 I/O。SKILL.md 中的 before/after 示例也展示了配套思路CREATE INDEX idx_order_items_order_qty ON order_items (order_id) INCLUDE (quantity);。3.2 复合索引列顺序至关重要-- Composite index (order matters!) CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC); -- Good: WHERE customer_id X AND order_date Y -- Good: WHERE customer_id X -- Bad: WHERE order_date Y (doesnt use index)复合索引遵循最左前缀原则索引(customer_id, order_date)能服务按客户 日期范围的查询也能单独服务按客户的查询但无法单独服务按日期的查询。将等值条件的列放在前面、范围条件的列放在后面是排序与过滤效率最大化的通用准则。database-design.md 进一步说明该复合索引还能支持WHERE customer_id ? ORDER BY order_date DESC避免额外的排序操作。3.3 部分索引Partial/Filtered Index更小、更快-- Partial/Filtered index (smaller, faster) CREATE INDEX idx_active_orders ON orders (customer_id, order_date) WHERE status active; -- Only used when query includes the filter SELECT * FROM orders WHERE customer_id 123 AND status active AND order_date 2024-01-01;只索引status active的行索引体积大幅缩小、写入开销降低、扫描更快——但代价是查询必须携带同样的过滤条件才能命中该索引。此模式非常适合软删除归档数据等低频值占绝大多数的表database-design.md 中的idx_active_products与唯一部分索引idx_users_active_emailWHERE deleted_at IS NULL都是同类实践。3.4 表达式索引函数包住列会让普通索引失效-- Expression/Function-based index CREATE INDEX idx_users_lower_email ON users (LOWER(email)); -- Now this uses the index SELECT * FROM users WHERE LOWER(email) userexample.com;在列上应用函数如LOWER(email)会阻止普通 B-tree 索引生效。解决方式是把函数表达式本身建成索引使查询中的表达式与索引表达式严格一致。注意表达式索引要求查询里的函数写法与索引定义逐字匹配。3.5 GIN 索引数组与 JSONB 的专用武器PostgreSQL-- GIN index for arrays/JSONB (PostgreSQL) CREATE INDEX idx_products_tags ON products USING GIN (tags); SELECT * FROM products WHERE tags ARRAY[electronics, sale]; CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata jsonb_path_ops); SELECT * FROM orders WHERE metadata {priority: high};对于数组包含和 JSONB 文档查询普通 B-tree 无能为力必须使用 GINGeneralized Inverted Index。jsonb_path_ops操作符类比默认的jsonb_ops索引更小、查询更快代价是不支持某些仅顶层键的查询操作符适用于键名已知且值精确匹配的场景。四、索引维护找到缺失、未用与重复的索引索引不是建完就万事大吉需要定期用统计视图体检。4.1 找出缺失索引顺序扫描过于频繁的表-- PostgreSQL: Find missing indexes SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan as avg_seq_read FROM pg_stat_user_tables WHERE seq_scan 0 AND seq_tup_read / seq_scan 10000 ORDER BY seq_tup_read DESC;当某表顺序扫描次数多、且每次扫描读取的行数巨大avg_seq_read超过 10000说明查询在反复全表遍历而索引没有被有效利用这是该建索引却没建的最强信号。4.2 找出未使用索引白白占用写开销与空间-- Find unused indexes SELECT schemaname, tablename, indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes WHERE idx_scan 0 AND indexrelname NOT LIKE pg_toast% ORDER BY pg_relation_size(indexrelid) DESC;idx_scan 0表示索引从未被使用过。每个索引都会拖慢 INSERT/UPDATE/DELETE 并占用存储未使用索引应优先考虑删除对主键/唯一约束索引需谨慎评估后操作。4.3 找出重复索引-- Find duplicate indexes SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) as size, (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2, (array_agg(idx))[3] as idx3 FROM ( SELECT indexrelid::regclass as idx, (indrelid::text ||E\n|| indclass::text ||E\n|| indkey::text ||E\n|| COALESCE(indexprs::text,)||E\n|| COALESCE(indpred::text,)) as key FROM pg_index ) sub GROUP BY key HAVING COUNT(*) 1 ORDER BY SUM(pg_relation_size(idx)) DESC;该查询将每个索引的所属表indrelid、操作符类indclass、键列indkey、表达式indexprs与谓词indpred拼接成指纹并分组指纹相同的索引即为冗余可安全删除以节省空间并降低写入成本。4.4 重建索引与更新统计信息-- Reindex to reduce bloat REINDEX INDEX CONCURRENTLY idx_orders_customer_date; -- Update statistics ANALYZE orders; ANALYZE VERBOSE; -- Show progress长期高并发的 B-tree 索引会产生页空洞bloatREINDEX INDEX CONCURRENTLY在不阻塞读写的前提下重建索引普通REINDEX会锁表生产环境慎用。ANALYZE刷新表的统计信息让优化器拿到准确的行数估算——这正是第一节中预估行数 vs 实际行数差距大的正解。五、查询重写模式用集合思维替代低效写法技能约束SKILL.md中的 MUST DO 强调用集合操作替代逐行处理“用 EXISTS 替代 COUNT 做存在性检查”“尽可能早地应用过滤条件”。5.1 用 EXISTS 替代 SELECT DISTINCT-- Avoid SELECT DISTINCT when possible -- Bad: Forces sort/dedup SELECT DISTINCT customer_id FROM orders WHERE status active; -- Good: Use EXISTS SELECT customer_id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.status active );SELECT DISTINCT会触发排序或哈希去重而是否存在匹配行本质是布尔判断EXISTS在找到第一条匹配后即停止语义更清晰、性能通常更优。这也是 query-patterns.md 中EXISTS 用于关联子查询、IN 用于小规模静态列表的通用准则。5.2 用 NOT EXISTS 替代 NOT IN-- Avoid NOT IN with NULLs -- Bad: NULL handling issues and poor performance SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); -- Good: Use NOT EXISTS SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );NOT IN在子查询结果包含 NULL 时会退化为整表无结果的诡异行为三值逻辑陷阱且优化器难以为其生成高效计划。NOT EXISTS语义明确、无 NULL 陷阱性能也更稳定。等价的反连接写法还有LEFT JOIN ... WHERE o.customer_id IS NULL见 query-patterns.md。5.3 尽早下推过滤条件-- Push down filtering early -- Bad: Filter after JOIN SELECT c.*, o.* FROM customers c JOIN orders o ON c.customer_id o.customer_id WHERE c.country US AND o.order_date 2024-01-01; -- Good: Use WHERE in subquery/CTE to reduce JOIN size WITH us_customers AS ( SELECT customer_id, name FROM customers WHERE country US ), recent_orders AS ( SELECT customer_id, order_id, total FROM orders WHERE order_date 2024-01-01 ) SELECT c.*, o.* FROM us_customers c JOIN recent_orders o ON c.customer_id o.customer_id;先在子查询/CTE 内过滤掉不满足条件的行再参与 JOIN能让进入连接阶段的数据量显著缩小。虽然现代优化器通常会自动重排谓词但在统计信息不准或优化器决策不佳时手工下推仍是最可靠的兜底手段也契合技能Filter early, aggregate late的最佳实践。5.4 消除 SELECT 中的标量子查询N1 问题-- Avoid scalar subqueries in SELECT -- Bad: N1 problem SELECT p.product_id, p.name, (SELECT COUNT(*) FROM reviews WHERE product_id p.product_id) as review_count FROM products p; -- Good: Single JOIN with GROUP BY SELECT p.product_id, p.name, COUNT(r.review_id) as review_count FROM products p LEFT JOIN reviews r ON p.product_id r.product_id GROUP BY p.product_id, p.name;对结果集的每一行执行一次子查询就是经典的 N1 问题1000 个商品会执行 1000 次计数。改用一次LEFT JOIN GROUP BY后所有统计在单次扫描中完成。SKILL.md 中的 before/after 示例用同样的手法优化了订单明细聚合并配套覆盖索引进一步消除回表。六、分区策略大表治理的结构性手段最佳实践清单第 8 条大表超过 1000 万行考虑分区。分区是索引之外、针对超大表的最后防线。6.1 范围分区Range Partitioning-- Range partitioning by date (PostgreSQL) CREATE TABLE orders ( order_id SERIAL, customer_id INT, order_date DATE NOT NULL, total DECIMAL(10,2) ) PARTITION BY RANGE (order_date); 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); -- Partition pruning in action EXPLAIN SELECT * FROM orders WHERE order_date 2024-02-01 AND order_date 2024-03-01; -- Only scans orders_2024_q1 partition按日期范围分区的核心收益是Partition Pruning分区裁剪查询范围落在哪个分区优化器就只扫描哪个分区上例中仅扫描orders_2024_q1同时还能支持按季度整体 DROP/归档的运维便利。6.2 列表分区List Partitioning-- List partitioning by category CREATE TABLE products ( product_id SERIAL, category VARCHAR(50) NOT NULL, name VARCHAR(200) ) PARTITION BY LIST (category); CREATE TABLE products_electronics PARTITION OF products FOR VALUES IN (electronics, computers, phones); CREATE TABLE products_clothing PARTITION OF products FOR VALUES IN (clothing, shoes, accessories);适合枚举值有限且查询天然按类别隔离的业务例如按商品类目、按地区、按业务线拆分物理存储。6.3 哈希分区Hash Partitioning-- Hash partitioning for even distribution CREATE TABLE users ( user_id SERIAL, email VARCHAR(255) ) PARTITION BY HASH (user_id); CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);对分区键做哈希取模MODULUS 4, REMAINDER 0~3数据均匀散落到 4 个分区适合无法用范围/列表切分、但写入并发压力极大的场景如用户表。注意哈希分区无法做分区裁剪其价值在于分散写入热点与并行扫描。七、物化视图预计算换取查询速度7.1 创建与唯一索引-- Create materialized view for expensive aggregations CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT DATE_TRUNC(day, order_date) as day, COUNT(*) as order_count, SUM(total) as revenue, AVG(total) as avg_order_value, COUNT(DISTINCT customer_id) as unique_customers FROM orders GROUP BY DATE_TRUNC(day, order_date); CREATE UNIQUE INDEX idx_daily_sales_day ON daily_sales_summary (day);将按天聚合订单这一昂贵计算固化为一物化视图查询直接读结果。唯一索引是REFRESH CONCURRENTLY的前置条件务必先建。7.2 并发刷新与触发器自动刷新-- Refresh strategy REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; -- Auto-refresh with trigger (PostgreSQL) CREATE OR REPLACE FUNCTION refresh_daily_sales() RETURNS TRIGGER AS $$ BEGIN REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_refresh_daily_sales AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH STATEMENT EXECUTE FUNCTION refresh_daily_sales();REFRESH ... CONCURRENTLY在不锁读的前提下增量更新视图代价是更慢的刷新速度。若业务对实时性要求高可用FOR EACH STATEMENT触发器在orders每次写操作后自动刷新——但要意识到这会放大写路径开销写频繁时更推荐**定时任务如 cron / pg_cron**而非触发器。窗口函数相关的昂贵计算同样适合物化见 window-functions.md 中product_rankings物化视图的示例。八、查询提示最后的强力手段务必谨慎原则提示Hints是绕过优化器决策的强制手段能用索引/重写解决就不要用且每次升级数据库后需重新验证。8.1 PostgreSQL 与 SQL Server-- PostgreSQL: Force index usage (use sparingly) SET enable_seqscan OFF; SELECT /* IndexScan(orders idx_orders_customer) */ * FROM orders WHERE customer_id 123; SET enable_seqscan ON; -- SQL Server: Query hints SELECT * FROM orders WITH (INDEX(idx_orders_customer_date)) WHERE customer_id 123; -- Force specific join type SELECT * FROM customers c INNER MERGE JOIN orders o ON c.customer_id o.customer_id;PostgreSQL 关闭enable_seqscan只是提示优化器降低顺序扫描优先级会话级开关配合/* ... */注释形式的提示需启用 pg_hint_plan 扩展可进一步约束访问路径。SQL Server 用表提示WITH (INDEX(...))指定索引用INNER MERGE JOIN强制合并连接。这些手段适合统计信息异常、优化器误判的特殊时刻。8.2 MySQL 与并行查询-- MySQL: Index hints SELECT * FROM orders USE INDEX (idx_orders_customer_date) WHERE customer_id 123; SELECT * FROM orders FORCE INDEX (idx_orders_customer_date) WHERE customer_id 123; -- PostgreSQL: Parallel query tuning SET max_parallel_workers_per_gather 4; ALTER TABLE large_table SET (parallel_workers 4);USE INDEX仅是建议优化器可忽略FORCE INDEX是强制。并行查询调优方面max_parallel_workers_per_gather控制单查询可用的并行 Worker 数ALTER TABLE ... SET (parallel_workers 4)则按表指定并行度——对超大表的聚合扫描往往有立竿见影的效果但受 CPU 核数与内存上限约束。九、性能监控让慢查询无处遁形9.1 从 pg_stat_statements 定位慢查询-- PostgreSQL: Find slow queries SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time, rows / calls as avg_rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;pg_stat_statements是 PostgreSQL 的语句级统计视图需在shared_preload_libraries中启用扩展按平均执行时间排序即可快速锁定 TOP 20 慢查询配合calls调用次数与avg_rows判断是偶发超慢还是高频次慢。9.2 定位锁阻塞-- Find blocking queries SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted;该查询通过自关联pg_locks找出等待锁granted false的会话及其对应的持锁会话从而直接看到谁阻塞了谁、各自的 SQL 语句是什么——是排查数据库突然卡死类问题的标准脚本。9.3 表膨胀检测-- Table bloat detection SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||.||tablename)) as size, n_dead_tup, n_live_tup, ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup n_dead_tup, 0), 2) as dead_pct FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC;n_dead_tup死元组占比过高说明 VACUUM 跟不上写入频率表与索引正在膨胀。dead_pct明显偏高时应检查 autovacuum 配置或手动VACUUM (ANALYZE)必要时结合第四节中的REINDEX处理索引膨胀。十、最佳实践清单一份可复印进团队规范的行动指南先 EXPLAIN ANALYZE 再动手优化——用数据说话在外键与 WHERE/JOIN 列上建索引——连接与过滤的最基本保障高频查询使用覆盖索引——Index-Only Scan 消除回表定期 ANALYZE 保持统计信息新鲜——确保优化器估算准确避免 SELECT *只选所需列——减少传输量与回表概率技能 MUST NOT DO 也明确禁止在生产查询中使用SELECT *见 SKILL.md子查询用 EXISTS 而非 IN——尤其在大集合与关联场景先过滤、后聚合——让进入聚合的数据量最小超 1000 万行的大表考虑分区——分区裁剪 运维便利昂贵聚合用物化视图——预计算结果换查询速度监控慢查询日志与 pg_stat_statements——建立持续优化循环。此外技能的输出模板SKILL.md要求任何一次优化交付都应包含带行内注释的优化后 SQL、配套索引及设计理由、执行计划分析、优化前后性能指标、平台相关注意事项——这正是把上述技巧沉淀为可审查、可复现工作产物的标准格式。结语把优化变成可验证的科学SQL 查询优化没有玄学。本文给出的完整方法论——先用 EXPLAIN含 ANALYZE/BUFFERS建立基准再按索引设计覆盖/复合/部分/表达式/GIN与查询重写EXISTS、过滤下推、消除标量子查询两条主线实施改进用统计视图持续监控验证——与 claude-skills 中sql-pro技能的五个工作流步骤完全一致分析 Schema → 设计集合操作 → 优化执行计划 →EXPLAIN ANALYZE验证 sub-100ms 目标 → 输出带索引理由与性能指标的文档。将本文与仓库中的 query-patterns.mdCTE、递归 CTE、LATERAL JOIN、PIVOT、window-functions.md排名、滚动聚合、LAG/LEAD、百分位、database-design.md范式、索引策略以及 dialect-differences.md多方言差异配合使用你便拥有了一套覆盖建模—查询—优化—验证全流程的数据库调优工具箱。【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考