数据库进阶：软件开发进阶技能详解（二）

第二部分：查询分析与执行计划 —— 让数据库告诉你慢在哪

写完了 SQL，数据库到底是怎么执行的？这可不是靠猜的。读懂执行计划，才是SQL调优的硬核基本功。

2.1 获取执行计划
不同的数据库，获取方式略有不同，但思路一致：
在MySQL里，直接在前面加个EXPLAIN就行，比如 EXPLAIN SELECT ...。如果你用的是 MySQL 8.0.18 及以上，还可以试试 EXPLAIN ANALYZE SELECT ...，它会真的跑一遍，把实际耗时和行数都甩给你看。
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 索引吧，它得先扫一遍整月的数据，再过滤用户ID。都不是最优解。
优化的思路就是建一个联合索引 (user_id, order_date)：
ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);

改造后的执行计划会变成这样：
type: ref
key: idx_user_date
rows: 估算该用户1月的订单数，很小
Extra: Using index
当然，如果只查 amount 且 amount 不在索引中，还是会回表。但就这个查询场景来说，性能已经天差地别了。如果还觉得慢，可以进一步考虑物化视图或者做个汇总表，把聚合结果先算好。

2.4 连接查询的优化
多表JOIN是SQL里最容易出妖蛾子的地方。优化器得决定谁先来、谁后到（选驱动表），还要决定用什么算法（Nested Loop、Hash Join 或 Sort-Merge Join）。

2.4.1 驱动表的选择
在EXPLAIN的输出中，第一行是驱动表，第二行是被驱动表。原则很简单：用小结果集驱动大结果集。
举个例子：用户表1000行，订单表100万行。查一下“2024年之后注册的用户以及他们的订单”：
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.registration_date > '2024-01-01';
如果满足条件的用户只有10个，那最理想的执行计划应该是：先找到这10个用户，再对这10个用户分别去订单表通过索引 (user_id) 查订单。这时候被驱动表被访问的次数只有10次，而不是100万次。绝对不要反过来！

2.4.2 尽量避免 SELECT *
这个已经是老生常谈了。只选你需要的列，好处是：减少网络传输、更可能使用覆盖索引、减少回表次数。对于SQL调优来说，这算是零成本的操作。

2.4.3 子查询优化
有些子查询优化器会自动转成JOIN（semi-join），但也有写得太烂的子查询导致逐行执行。
比如这个反例：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
MySQL 5.6之后，优化器已经能对这类IN子查询做semi-join优化了，性能尚可。但如果想更可控，改成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替代，更稳定。