第二部分：查询分析与执行计划 —— 让数据库告诉你慢在哪
写了 SQL 之后，数据库到底是如何执行的？读懂执行计划（Execution Plan）是进阶数据库技能的核心。
2.1 获取执行计划
MySQL：EXPLAIN SELECT ... 或者 EXPLAIN ANALYZE SELECT ...（MySQL 8.0.18+ 会实际执行并返回耗时和行数）。
PostgreSQL：EXPLAIN (ANALYZE, BUFFERS) SELECT ...。
2.2 解读执行计划的关键指标
以 MySQL 的 EXPLAIN 输出为例，最重要的列：
2.2.1 type 详解
const：主键或唯一索引等值查询，最多返回一行，常量级别的快。
eq_ref：连接查询中，被驱动表使用主键或唯一索引，每次只匹配一行。
ref：非唯一索引等值匹配，可能返回多行。
range：索引范围扫描（BETWEEN, >, <, IN 等）。
index：全索引扫描（遍历整个索引树），比全表扫描好，但依然慢。
ALL：全表扫描，最差。
2.2.2 Extra 详解
Using index：覆盖索引，好。
Using index condition：索引下推（Index Condition Pushdown），MySQL 5.6+ 特性，存储引擎层过滤索引记录，减少回表。
Using where：在 Server 层过滤行，通常与索引一起使用。
Using filesort：需要额外的排序操作，如果无法避免，尽量让排序字段利用索引（ORDER BY 遵循最左前缀）。
Using temporary：使用临时表，通常出现在 GROUP BY 或 DISTINCT 没有使用索引时，应尽量优化。
2.3 实战：优化一条慢 SQL
场景：订单表 orders 有百万级数据，需要查询某个用户在最近 30 天的订单总额。
原始 SQL：
SELECT SUM(amount)
FROM orders
WHERE user_id = 12345
AND order_date >= '2025-01-01'
AND order_date <= '2025-01-31';
执行计划分析：
假设当前只有 (user_id) 和 (order_date) 两个单列索引。MySQL 只能选择其中一个索引。如果选择 user_id 索引，会找到该用户的所有订单（可能很多），然后逐行过滤 order_date；如果选择 order_date 索引，则扫描一月的数据，再过滤 user_id。都不是最优。
优化方案：创建联合索引 (user_id, order_date)。
jALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);
执行计划：
type: ref
key: idx_user_date
rows: 估算该用户的1月订单数（很小）
Extra: Using index (如果只查 amount 且 amount 不在索引中，会回表；但 SUM 需要 amount 值，无法覆盖)
如果经常需要按用户和时间范围做聚合，可以进一步考虑物化视图或汇总表。
2.4 连接查询的优化
多表连接（JOIN）是 SQL 中最复杂且容易出性能问题的地方。优化器需要决定哪张表作为驱动表（小表），使用什么连接算法（Nested Loop、Hash Join、Sort-Merge Join）。
2.4.1 驱动表的选择
在 EXPLAIN 中，第一行表示驱动表，第二行表示被驱动表。基本原则：用小结果集驱动大结果集。
示例：用户表 1000 行，订单表 100 万行，查询用户及其订单。
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.registration_date > '2024-01-01';
如果满足条件的用户只有 10 个，那么优化的执行计划应该是：先找到这 10 个用户，然后对每个用户去订单表通过索引 (user_id) 查找订单（被驱动表访问次数 = 10，而不是 100 万）。
2.4.2 尽量避免 SELECT *
只选择需要的列可以：减少网络传输，更有可能使用覆盖索引，减少回表。
2.4.3 子查询优化
某些子查询会被优化器转为连接（semi-join），但也有写得很差的子查询导致逐行执行。
反例：
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
MySQL 5.6 以后会做 semi-join 优化，性能尚可。但有时将 IN 改为 EXISTS 或者显式 JOIN 更可控。
推荐写法：
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;
2.5 分页查询优化 —— 避免深分页
OFFSET 很大时，数据库需要扫描并丢弃前 N 行，非常低效。
反例：
SELECT * FROM products ORDER BY id LIMIT 1000000, 10;
这会扫描 1,000,010 行。
优化方案：使用游标/键集分页（Seek Method）。
-- 记住上一页最后一条记录的 id
SELECT * FROM products WHERE id > 1000000 ORDER BY id LIMIT 10;
需要保证 id 是递增且无空洞，或者使用多列排序时记住所有排序列的值。
更通用的方案：使用延迟关联（Deferred Join）。先只查询主键，再通过主键回表获取全部数据。
SELECT * FROM products
WHERE id IN (
SELECT id FROM products ORDER BY id LIMIT 1000000, 10
);
但注意 MySQL 对子查询 IN 的性能，可能需要改为 JOIN。
来源：
https://oplhc.cn/
