3、SQL优化实战：覆盖索引、索引下推、Join优化、子查询改写、分页优化（延迟关联）

这一章，我们聊聊SQL优化的几个硬核技巧。

说实话，很多系统慢，不是硬件不行，也不是架构烂，就是SQL写得糙。我见过太多开发同学，索引建了一堆，查询还是慢得像蜗牛。为什么？因为索引没用对。

今天我把几个最常用的优化手段掰开揉碎讲清楚。你跟着我走一遍，以后写SQL心里就有底了。

3.1 覆盖索引：让查询"只走索引，不回表"

先问一个问题：你知道MySQL执行一条查询，最耗时的操作是什么吗？

不是排序，不是分组，而是回表。

什么叫回表？就是通过二级索引找到主键ID后，还得拿着ID去聚簇索引里捞整行数据。这一步，是随机IO，慢得很。

那怎么避免？用覆盖索引。

核心思想：查询所需的所有字段，都包含在索引中。这样MySQL直接从索引树拿数据，根本不用回表。

举个例子。假设我们有张订单表：

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `user_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` tinyint NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `status`, `create_time`)
) ENGINE=InnoDB;

现在要查某个用户最近10笔已支付订单的金额：

-- 这个查询会回表
SELECT * FROM `order` 
WHERE user_id = 123 AND status = 1 
ORDER BY create_time DESC 
LIMIT 10;

-- 这个查询，覆盖索引，不回表
SELECT id, order_no, amount, create_time FROM `order` 
WHERE user_id = 123 AND status = 1 
ORDER BY create_time DESC 
LIMIT 10;

看出区别了吗？第二个查询的字段全部在 idx_user_status 索引里。MySQL直接遍历索引树就搞定了，不需要回表拿数据。

我的经验：我在项目中遇到过一张千万级的日志表，原来查询要3秒多。我加了个覆盖索引，把查询字段都塞进去，直接降到20毫秒。说白了，覆盖索引就是"用空间换时间"的典型。

3.2 索引下推：减少回表次数

覆盖索引是"不回表"，索引下推是"少回表"。两者思路不同，但目标一致——减少IO。

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6引入的优化。它的原理很简单：在索引遍历过程中，提前过滤掉不满足条件的记录，减少回表次数。

举个例子：

-- 联合索引 (name, age)
SELECT * FROM t_user WHERE name LIKE '张%' AND age = 25;

没有ICP时，MySQL会这样干：

  1. 用索引找到所有姓"张"的记录（比如1000条）
  2. 回表1000次，拿到完整数据
  3. 在Server层过滤 age=25

有ICP时：

  1. 用索引找到姓"张"的记录
  2. 在索引层直接判断 age=25，过滤掉不满足的（比如只剩50条）
  3. 只回表50次

你看，回表次数从1000次降到了50次。这就是索引下推的威力。

注意：ICP只适用于二级索引。主键索引本身就是聚簇索引，不需要回表。另外，ICP默认是开启的，你可以用 SET optimizer_switch='index_condition_pushdown=off'; 关闭它，但我不建议你这么做。

3.3 Join优化：小表驱动大表

Join是很多人的噩梦。一提到多表关联，就有人说"慢"。其实不是Join慢，是你没用好。

Join优化的核心原则就一句话：小表驱动大表。

什么意思？MySQL执行Join时，会选择一个驱动表，然后遍历驱动表的每一行，去被驱动表里匹配。驱动表的遍历次数，决定了Join的总成本。

所以，永远让数据量小的表做驱动表。

举个例子：

-- 假设：orders表100万行，users表1万行
-- 正确写法：users驱动orders
SELECT * FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1;

-- 错误写法：orders驱动users（慢！）
SELECT * FROM orders o 
INNER JOIN users u ON o.user_id = u.id 
WHERE u.status = 1;

为什么？第一个查询只遍历1万次users表，每次去orders表用索引匹配。第二个查询要遍历100万次orders表，差距巨大。

避坑指南：我曾经接手过一个报表系统，一个Join查询跑了30秒。我一看，驱动表选反了。把驱动表换成小表后，直接降到0.5秒。嗯，有时候问题就这么简单。

另外，Join的字段一定要建索引。被驱动表的关联字段如果没有索引，MySQL会做全表扫描，那才是真正的灾难。

3.4 子查询改写：把"嵌套"变成"连接"

子查询写起来很爽，逻辑清晰。但性能嘛……有时候一言难尽。

MySQL对子查询的优化并不完美。特别是IN子查询和关联子查询，经常会出现"每行执行一次子查询"的惨剧。

举个例子：

-- 慢查询：关联子查询
SELECT * FROM orders o 
WHERE o.amount > (
    SELECT AVG(amount) FROM orders 
    WHERE user_id = o.user_id
);

-- 改写为Join
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
) t ON o.user_id = t.user_id
WHERE o.amount > t.avg_amount;

第一个查询，每行orders都要执行一次子查询，计算该用户的平均金额。100万行，就要执行100万次子查询。

第二个查询，先一次性算好所有用户的平均金额，然后一次性Join。只执行一次子查询。

高下立判。

我的习惯：我写SQL时，只要看到子查询，就会下意识想"能不能改成Join"。不是所有子查询都能改，但大部分都可以。改完之后，性能提升往往立竿见影。

3.5 分页优化：延迟关联

分页查询，越往后翻越慢。你有没有遇到过？

原因很简单：LIMIT 1000000, 20 这种写法，MySQL会先查1000020行，然后扔掉前1000000行。前面的数据全白查了。

怎么优化？延迟关联。

核心思路：先用覆盖索引快速定位到需要的ID，再用ID回表拿完整数据。

-- 慢分页
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 20;

-- 延迟关联优化
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 1000000, 20
) tmp ON o.id = tmp.id
ORDER BY o.create_time DESC;

为什么快？内层子查询只查了ID和排序字段，这两个字段都在索引里。MySQL只需要遍历索引树，不需要回表。找到20个ID后，再一次性回表拿完整数据。

你想想看，原来要回表1000020次，现在只回表20次。这差距，不是一星半点。

注意：延迟关联适用于大偏移量的分页。如果偏移量很小（比如前几页），直接用普通分页就行，没必要折腾。另外，如果查询条件很复杂，延迟关联的效果会更明显。

3.6 本章小结

来，我们捋一捋今天讲的内容：

优化手段 核心思想 适用场景
覆盖索引 查询字段全在索引里，不回表 高频查询，字段固定
索引下推 在索引层提前过滤，少回表 联合索引，范围查询+等值查询
Join优化 小表驱动大表，关联字段建索引 多表关联查询
子查询改写 把嵌套子查询改成Join IN子查询、关联子查询
延迟关联 先查ID，再回表拿数据 大偏移量分页

这些技巧，说白了就是围绕一个核心：减少回表，减少IO。你只要记住这个原则，写SQL的时候多想想"能不能少回一次表"，性能自然就上去了。

嗯，今天就到这里。下一章我们聊聊更高级的优化手段——但那是后话了。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →