Claude Skills 数据库查询优化实战：基于 EXPLAIN 的执行计划分析与查询重写完整指南

Claude Skills 数据库查询优化实战：基于 EXPLAIN 的执行计划分析与查询重写完整指南

【免费下载链接】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 中database-optimizerSkill 的 查询优化参考文档 为核心骨架，系统讲解覆盖 PostgreSQL 与 MySQL 两大数据库的查询性能优化方法。读者将掌握执行计划（EXPLAIN）的解读技巧、六类查询重写模式、CTE 与窗口函数优化策略、Keyset 分页方案，以及可落地的性能验证流程，帮助把慢查询分析从"凭直觉"升级为"有数据、有方法、可验证"的工程实践。

从数据库优化器 Skill 说起

在 claude-skills 仓库中，database-optimizer被定位为"资深数据库优化专家"，专注于 PostgreSQL 与 MySQL 系统的性能调优。从 Skill 元数据 可以看到，它的触发场景明确指向：慢查询调查、执行计划分析、索引设计、查询重写、配置调优、分区策略与锁竞争解决。当项目中出现数据库迁移任务时，工作流会将该类任务分派给 database-optimizer 代理处理（见 execute-ticket.md），并在 创建实施计划 时把数据库变更与它绑定。

该 Skill 提供的核心工作流是：分析性能 → 定位瓶颈 → 设计方案 → 增量实施 → 验证结果，其中每一步都要求以EXPLAIN (ANALYZE, BUFFERS)采集的基线数据为决策依据。而本文要深入展开的，正是这一工作流中"分析慢查询与执行计划"环节的核心参考文档 —— query-optimization.md。它与同 Skill 下的 index-strategies.md（索引策略）、postgresql-tuning.md（PostgreSQL 调参）、mysql-tuning.md（MySQL 调参）、monitoring-analysis.md（监控分析）共同构成完整的性能优化参考体系。

一、执行计划分析：优化前必须完成的第一步

任何查询优化的前提都是拿到可靠的执行计划。参考文档强调：在改动任何代码之前，先用 EXPLAIN 采集基线。下面分别给出 PostgreSQL 与 MySQL 的实操方法。

1.1 PostgreSQL EXPLAIN ANALYZE

PostgreSQL 的EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING)组合选项可以提供实际执行统计信息：

-- 获取实际执行统计 EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING) SELECT u.id, u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > NOW() - INTERVAL '30 days' GROUP BY u.id, u.name HAVING COUNT(o.id) > 5;

需要重点检查的五个指标（参考文档明确列出）：

1. Actual time vs Planning time：规划时间与实际执行时间的差异，反映统计信息与真实数据的偏差程度；
2. Rows estimate vs Actual rows（基数估算）：优化器估算行数与实际行数的偏差，偏差过大说明统计信息陈旧或缺失；
3. Buffers（shared hits vs reads）：命中共享缓冲池的次数与磁盘读取次数，直接决定缓存命中率；
4. Sequential Scans vs Index Scans：全表顺序扫描与索引扫描的比例，是发现缺索引的最直观信号；
5. Join methods（Nested Loop、Hash Join、Merge Join）：连接方法选择是否合理。

与 SKILL.md 中"阅读 EXPLAIN 输出的关键模式表"配合使用，可以快速把症状映射到对策：

1.2 MySQL EXPLAIN

MySQL 提供三种由浅入深的执行计划查看方式：

-- 基础执行计划 EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'; -- JSON 格式，便于程序化解析与深度分析 EXPLAIN FORMAT=JSON SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.created_at > '2024-01-01'; -- 分析实际执行（MySQL 8.0+） EXPLAIN ANALYZE SELECT * FROM products WHERE category_id = 5 ORDER BY price DESC LIMIT 10;

EXPLAIN FORMAT=JSON输出的结构更适合在监控工具或脚本中提取关键字段（如rows_examined_per_scan、cost_info）；EXPLAIN ANALYZE则直接给出真实执行耗时与循环次数，相当于 MySQL 版的 ANALYZE 模式。

二、查询重写模式：六种立竿见影的改写手法

参考文档提供了四组完整的"改写前/改写后"对比示例，全部可直接用于实战。

2.1 消除子查询：相关子查询替换为 JOIN + 窗口函数

-- 改写前（慢：每一行都执行一次子查询） SELECT * FROM orders o WHERE total > ( SELECT AVG(total) FROM orders WHERE user_id = o.user_id ); -- 改写后（快：一次连接 + 一次聚合） WITH user_averages AS ( SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id ) SELECT o.* FROM orders o INNER JOIN user_averages ua ON o.user_id = ua.user_id WHERE o.total > ua.avg_total;

相关子查询的致命问题在于逐行执行：外层表有多少行，内层子查询就执行多少次，O(N×M) 的复杂度在大表上会迅速失控。改写为"先聚合后连接"后，聚合只需扫描一次，再通过哈希连接完成匹配。

2.2 优化 JOIN 顺序：先过滤、再连接

-- 改写前（先产生笛卡尔积再过滤） SELECT p.name, c.name, s.stock FROM products p, categories c, stock s WHERE p.category_id = c.id AND p.id = s.product_id AND c.active = true; -- 改写后（先把过滤条件收窄，再逐层连接） SELECT p.name, c.name, s.stock FROM categories c INNER JOIN products p ON p.category_id = c.id INNER JOIN stock s ON s.product_id = p.id WHERE c.active = true;

虽然现代优化器（PostgreSQL、MySQL 8.0+）多数时候会自主重排连接顺序，但把选择性最高的过滤条件放在最内层、让驱动表先缩小结果集，仍然是降低中间结果集膨胀的有效防御性写法，也让 EXPLAIN 输出更易读。

2.3 用 EXISTS 代替 IN：短路优先命中

-- 改写前（慢：物化整个子查询结果集） SELECT * FROM users WHERE id IN ( SELECT DISTINCT user_id FROM orders WHERE total > 1000 ); -- 改写后（快：找到第一条匹配即返回） SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000 );

IN需要先物化并去重整个子查询结果集；EXISTS则是一种半连接（semi-join）语义，一旦找到匹配行即可短路，且天然避免了对DISTINCT的额外排序开销。这一手法同样适用于 DISTINCT 优化：通过EXISTS表达"存在匹配记录"的语义后，可以完全去掉对全结果集的排序去重。

2.4 优化 DISTINCT：用存在性语义替代全量去重

-- 改写前（对整个结果集排序去重） SELECT DISTINCT u.email FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed'; -- 改写后（利用索引与半连接实现唯一性） SELECT u.email FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed' );

如果业务上只需要"哪些用户下过完成状态的订单"，就不需要把每个用户的每笔订单都拉出来排序去重。改写后的查询可以走users.email上的索引（必要时配合覆盖索引实现 Index Only Scan），从根源上消除 Sort 节点。

三、CTE 优化：物化与内联的取舍

PostgreSQL 12+ 支持在 CTE 上显式指定MATERIALIZED/NOT MATERIALIZED，这是控制优化器行为的重要工具：

-- PostgreSQL：强制物化以便多次复用 WITH expensive_calculation AS MATERIALIZED ( SELECT user_id, SUM(total) as lifetime_value, COUNT(*) as order_count FROM orders WHERE created_at > NOW() - INTERVAL '1 year' GROUP BY user_id ) SELECT * FROM expensive_calculation WHERE lifetime_value > 10000 OR order_count > 50; -- 强制内联：单次使用的 CTE 直接展开到主查询 WITH recent_users AS NOT MATERIALIZED ( SELECT id FROM users WHERE created_at > NOW() - INTERVAL '7 days' ) SELECT * FROM recent_users;

选择依据可以总结为两条经验法则：

• 物化（MATERIALIZED）适用场景：CTE 在主查询中被多次引用，或聚合计算代价高昂、物化一次可避免重复计算。上例中expensive_calculation同时被两个条件引用，物化能确保聚合只执行一次；
• 内联（NOT MATERIALIZED）适用场景：CTE 只被引用一次。此时内联能让优化器把它当作普通子查询，保留下推条件、利用主查询已有索引的机会，避免物化造成的"优化器黑盒"。

需要留意的是，在 PostgreSQL 12 之前 CTE 一律强制物化，常导致规划器无法下推过滤条件——升级到新版本后主动检查旧的 CTE 写法，往往能获得意外收益。

四、窗口函数优化：单次扫描替代多次子查询

-- 改写前（多次相关子查询，逐行执行） SELECT o.id, o.total, (SELECT MAX(total) FROM orders WHERE user_id = o.user_id) as max_total, (SELECT AVG(total) FROM orders WHERE user_id = o.user_id) as avg_total FROM orders o; -- 改写后（一次窗口函数扫描完成全部聚合） SELECT id, total, MAX(total) OVER (PARTITION BY user_id) as max_total, AVG(total) OVER (PARTITION BY user_id) as avg_total FROM orders;

窗口函数与GROUP BY的关键区别在于：聚合结果会附加到每一行上而不是压缩行数。当需要"每行附带该用户的聚合统计值"这类需求时，一个带PARTITION BY的窗口扫描，其成本远低于两个逐行触发的相关子查询。

五、聚合策略：部分聚合（两阶段聚合）

对高基数分组的大数据集，可以先把数据按较小粒度预聚合，再对预聚合结果做二次聚合：

-- 大基数分组场景：先按天聚合，再按用户聚合 WITH daily_stats AS ( SELECT DATE(created_at) as day, user_id, COUNT(*) as daily_orders, SUM(total) as daily_total FROM orders WHERE created_at > NOW() - INTERVAL '90 days' GROUP BY DATE(created_at), user_id ) SELECT user_id, SUM(daily_orders) as total_orders, AVG(daily_total) as avg_daily_total FROM daily_stats GROUP BY user_id;

这里的关键在于：SUM和AVG是可分解聚合（distributive/algebraic aggregate），即"按天求和再求和"等于"直接求和"，因此两阶段聚合结果完全等价，但每一阶段处理的数据量都显著缩小。配合每日批量任务，还能把高频统计提前物化为汇总表，让在线查询只读预聚合结果。

六、分页优化：Keyset（游标）分页替代深偏移

传统的LIMIT x OFFSET y在大偏移量下性能会急剧恶化——数据库必须扫描并丢弃前面所有行：

-- 改写前（大偏移量下缓慢） SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10000; -- 改写后（Keyset 分页：基于游标定位） SELECT * FROM products WHERE created_at < '2024-01-01 12:00:00' OR (created_at = '2024-01-01 12:00:00' AND id < 12345) ORDER BY created_at DESC, id DESC LIMIT 20; -- 为 Keyset 分页创建配套索引 CREATE INDEX idx_products_pagination ON products (created_at DESC, id DESC);

Keyset 分页的原理是"记录最后一条的位置，用它作为下一页的起点"，因此无论翻到第几页，都只扫描 20 行，时间复杂度恒定。实践要点：

• 游标必须包含排序键（上例为created_at），并在created_at相同时用id兜底保证次序唯一稳定；
• WHERE中的"小于游标 或 等于游标且主键小于"组合，配合(created_at DESC, id DESC)索引即可实现索引有序扫描；
• 代价是不支持"跳页"，适合 feed 流、订单列表等顺序浏览场景；如需随机跳页仍可保留 OFFSET 方案但应限制最大偏移量。

七、查询模式红旗清单

参考文档给出了七类常见反模式的速查表，可作为日常代码审查的检查清单：

其中WHERE DATE(column) = ...与LIKE '%term%'是索引失效的两大高发场景：前者因为函数作用于索引列破坏了 B 树的排序性质，改写为范围查询后即可走索引；后者可通过 表达式索引（PostgreSQLLOWER(email)或 MySQL 生成列）等手段间接优化，但前缀不确定的模糊匹配本质上适合 GIN 全文索引或 trigram。

八、性能验证：优化必须用数据说话

8.1 PostgreSQL 验证体系

-- 对比查询性能（优化前后各执行一次并记录结果） EXPLAIN (ANALYZE, BUFFERS) -- your query here -- 检查整体缓冲缓存命中率 SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;

SKILL.md 进一步给出了验证的完整闭环：优化前保存计划与耗时，优化后对比Execution Time是否有意义的下降，再通过pg_stat_user_indexes确认新建索引确实被使用：

-- 确认索引确实被使用 SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'orders';

8.2 MySQL 验证体系

-- 检查处理器统计信息（Handler 计数） SHOW STATUS LIKE 'Handler%'; FLUSH STATUS; -- run your query SHOW STATUS LIKE 'Handler%';

通过FLUSH STATUS清空计数、执行查询后再查看，Handler_read_first、Handler_read_rnd_next、Handler_read_key等计数器能直观反映这次查询到底是索引查找为主还是全表扫描为主。

8.3 验证的工程纪律

参考文档与 SKILL.md 约束清单 共同强调了以下纪律，缺一不可：

• 先基线后优化：任何改动前必须采集EXPLAIN (ANALYZE, BUFFERS)输出；
• 单变量原则：一次只改一处，否则无法归因性能变化；
• 关注写放大：新索引会拖慢 INSERT/UPDATE/DELETE，需评估写入性能是否退化；
• 维护统计信息：大批量数据变更后执行ANALYZE，避免规划器基于陈旧统计做错误决策；
• 非生产环境先行：所有变更先在非生产环境验证，一旦写性能恶化或复制延迟上升立即回滚。

结语

查询优化是一项"先测量、后动手、再验证"的循环工程。本文基于 claude-skills 仓库中 query-optimization.md 提供的完整方法论，覆盖了从 EXPLAIN 执行计划解读、六类查询重写手法，到 CTE 物化控制、窗口函数、部分聚合、Keyset 分页与红旗模式识别的全链路。当你在实践中遇到慢查询时，可以继续翻阅该 Skill 的同系列参考文档：索引策略 解决"缺索引"问题、PostgreSQL 调优 与 MySQL 调优 解决"配置瓶颈"问题、监控分析 提供持续观测手段。始终记住参考文档的核心警告：在拿到基线数据之前，不要做任何优化。

【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills