📚 课程目录第4章数据库查询优化

4. 数据库查询优化：索引、SQL重写

数据库查询优化，说白了就是让接口跑得更快。金融系统里一条慢查询，可能直接让交易超时、资金划转失败。我个人习惯，接手任何接口前先看它的 SQL 和表结构，而不是急着加缓存。今天聊的，就是我从上百个慢查询案例里筛出来的核心打法。

4.1 索引优化 —— 别让数据库全表扫描

索引不是越多越好，金融表数据量动辄上亿，索引建错了比不建还坏事。我遇到过一张历史流水表，建了 8 个单列索引，插入时撑死每秒几十条。

4.1.1 复合索引的最左前缀原则

复合索引是最常用的优化手段。比如查询条件经常是 WHERE trade_date BETWEEN ? AND ? AND cust_id = ?，那复合索引应该把等值条件的列放在前面。

  正确姿势：

  CREATE INDEX idx_trade_cust ON t_trade(trade_date, cust_id);

  如果反着建 idx_cust_trade(cust_id, trade_date)，B+ 树依然能走索引，但等值过滤后还要回表扫描，效率差 5~8 倍。

4.1.2 覆盖索引与回表

金融查询经常只取少数字段，比如交易流水号、金额、状态。如果索引包含了所有需要的列，就不需要回表，IO 少一次。

类型	说明	适用场景
普通索引	只存索引列和主键	小表或查询列很多
覆盖索引	索引包含查询所有列	高频小查询，如交易状态按流水号查

避坑： 不要为了覆盖索引把 varchar(64) 加进去，索引长度膨胀后，B+ 树层数增加，得不偿失。

4.1.3 索引下推（ICP）

MySQL 5.6 后支持索引下推。查询条件中有复合索引的非前缀列时，存储引擎直接过滤掉不满足的行，减少回表次数。比如 WHERE name LIKE '张%' AND age > 20，索引 idx_name_age(name, age) 会在索引层判断 age。

曾经踩过的坑： 我在一个对账接口里用了 WHERE create_time > ? AND status IN (1,2,3)，索引 idx_create_status(create_time, status)，但 status 是 varchar 类型，查询用了 int，隐式转换让索引完全失效。后来改成字符串比较，性能从 8 秒降到 120 毫秒。

4.2 SQL 重写 —— 换个写法，性能翻倍

索引再好，SQL 写法不对也白搭。你想想看，同样的逻辑，不同写法的执行计划可能天差地别。

4.2.1 避免在索引列上使用函数

WHERE DATE(trade_time) = '2025-01-01' 会让索引失效。改成 WHERE trade_time >= '2025-01-01 00:00:00' AND trade_time < '2025-01-02 00:00:00'，就能走索引范围扫描。

  对比效果：

  ❌ 函数写法：DATE(trade_time) → 全表扫描

  ✅ 范围写法：trade_time >= ... → 索引范围扫描，千万级数据快 30 倍以上

4.2.2 隐式类型转换

字段类型是 varchar，传入 int 参数，MySQL 会把全表字段转成 int，索引失效。我见过一个账户查询，WHERE account_no = 12345678，account_no 是 varchar(20)，结果走了全表。加上引号变成字符串，瞬间走索引。

4.2.3 子查询优化为 JOIN

金融业务里经常有“在某个表中存在记录才返回”的查询。比如查当天有交易的客户，用 SELECT * FROM cust WHERE cust_id IN (SELECT cust_id FROM trade WHERE trade_date = ?)，可能会触发临时表。改成 SELECT DISTINCT c.* FROM cust c JOIN trade t ON c.cust_id = t.cust_id WHERE t.trade_date = ?，让优化器直接走 index join。

注意： 不是所有子查询都要改。如果子查询结果集很小（比如几十条），半连接优化也能做得很好。建议每个场景都 EXPLAIN 一下，别盲目信经验。

4.2.4 分页优化

传统分页 LIMIT 100000, 20 会扫描前 10 万行再丢弃，越往后越慢。金融系统里分页查询订单经常遇到。改用“上一页最后一条的主键”来锚定：WHERE id > 100000 ORDER BY id LIMIT 20，走主键索引直接定位，翻页速度恒定。

分页方式	SQL 示例	执行计划	百万级性能
传统分页	`LIMIT 100000, 20`	全表扫描 + filesort	> 500ms
游标分页	`WHERE id > ? LIMIT 20`	主键索引范围扫描	< 5ms

4.2.5 避免 SELECT * 和临时表

SELECT * 在金融表里能拖出 50 个字段，索引覆盖失效。只选需要的列。另外 GROUP BY 如果没有索引支撑，会生成临时表，我建议尽量通过索引来排序，或者把 GROUP BY 改为程序里分组。

重要提醒： 以上所有优化，修改前和修改后一定做 EXPLAIN 对比，看 type、rows、Extra 字段。我曾自信满满改了一个 JOIN，结果没注意 Extra 出现了 "Using temporary"，性能反而更差。要相信执行计划，别猜。