SQL 索引优化实战:为什么你的查询慢,以及怎么修

"这条 SQL 怎么这么慢?"—— 几乎每个后端工程师都问过这句话。答案十有八九和索引有关:要么没建索引,要么建了用不上,要么建多了拖慢写入。这篇文章不堆理论,而是沿着"索引为什么快 → 为什么用不上 → 怎么诊断 → 怎么建对"这条线走一遍,所有结论都配可复现的 SQL。

索引为什么快:B+ 树的直觉

没有索引时,数据库找一行数据只能做全表扫描:从第一行翻到最后一行,逐行比对。100 万行就是 100 万次比对。索引的作用,是把"线性查找"变成"树形查找"。

MySQL(InnoDB)的索引底层是 B+ 树。它的关键特性:

• 多叉、矮胖:一个节点能存很多键,所以即使几千万行数据,树高通常也只有 3~4 层。查一行最多 3~4 次磁盘 I/O。
• 数据全在叶子节点,且叶子节点之间用双向链表串起来 —— 这让范围查询(BETWEEN、>、ORDER BY)非常高效,顺着链表扫即可。
• 有序:键在树里按顺序排列,这是后面所有"用得上/用不上"规则的根源。

InnoDB 还有个重要概念:聚簇索引。主键索引的叶子节点直接存放整行数据;而普通索引(二级索引)的叶子节点只存主键值。这意味着:用二级索引查询,如果要的列它没覆盖,就得拿着主键值回表到聚簇索引再查一次。"回表"是性能分析里的高频词,记住它。

-- 建一张测试表
CREATE TABLE orders (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id   BIGINT NOT NULL,
  status    TINYINT NOT NULL,
  amount    DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  remark    VARCHAR(255)
);
-- 假设已灌入 200 万行数据

诊断工具:EXPLAIN 怎么看

优化索引的第一步永远是 EXPLAIN。它告诉你数据库打算怎么执行这条 SQL。重点看这几列:

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

• type:访问类型,性能从好到坏 —— system > const > eq_ref > ref > range > index > ALL。看到 ALL(全表扫描)基本就是要优化的信号。
• key:实际用到的索引。NULL 表示没用上任何索引。
• rows:预估要扫描的行数。越小越好。
• Extra:最有信息量的一列。Using index 是好事(覆盖索引);Using filesort、Using temporary 通常是坏事,意味着额外排序或临时表。

想看实际执行情况(不是预估),用 EXPLAIN ANALYZE(MySQL 8.0+):

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345 AND status = 1;
-- 输出里会有真实的 actual time、实际扫描行数、循环次数

最常见的"索引用不上"场景

建了索引不等于用得上。下面这些写法会让索引"失效",每一条都给出可对照的例子。

1. 对索引列做运算或用函数

-- 用不上 created_at 索引:列被函数包住了
SELECT * FROM orders WHERE DATE(created_at) = '2026-05-15';

-- 改写:让索引列保持"裸露",把运算挪到右边
SELECT * FROM orders
WHERE created_at >= '2026-05-15 00:00:00'
  AND created_at <  '2026-05-16 00:00:00';

原理:B+ 树是按 created_at 原始值排序的,DATE(created_at) 是个新值,树里没有这个顺序,只能全表算一遍。

2. 隐式类型转换

-- user_id 是 BIGINT,这里传了字符串,MySQL 会把整列转成字符串再比 —— 等于对列做了函数
SELECT * FROM orders WHERE user_id = '12345';   -- 可能用不上索引

-- 正确:类型对齐
SELECT * FROM orders WHERE user_id = 12345;

3. 前导模糊匹配

-- 用不上索引:% 在开头,等于"不知道从哪个前缀开始找"
SELECT * FROM orders WHERE remark LIKE '%超时%';

-- 用得上:% 在末尾,可以利用索引的有序性定位前缀
SELECT * FROM orders WHERE remark LIKE '退款%';

4. OR 连接的条件有一边没索引

-- 如果 remark 没有索引,整条退化成全表扫描
SELECT * FROM orders WHERE user_id = 12345 OR remark = 'x';

-- 改写成 UNION,让有索引的那部分走索引
SELECT * FROM orders WHERE user_id = 12345
UNION
SELECT * FROM orders WHERE remark = 'x';

联合索引与"最左前缀"原则

真实业务很少只按一个列查,这就需要联合索引。它的使用规则是整个索引体系里最容易踩错的地方。

-- 建一个三列联合索引
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);

这个索引可以理解成:先按 user_id 排序,user_id 相同的再按 status 排序,都相同的再按 created_at 排序。所以它的有序性是"从左到右逐级"的。由此得出最左前缀原则:

-- ✅ 用得上(完整命中)
WHERE user_id = 1 AND status = 1 AND created_at > '2026-01-01'
-- ✅ 用得上(命中前两列)
WHERE user_id = 1 AND status = 1
-- ✅ 用得上(只命中第一列)
WHERE user_id = 1
-- ❌ 用不上:跳过了最左的 user_id
WHERE status = 1
-- ⚠️ 部分用上:user_id 走索引,status 之后失效(中间断了)
WHERE user_id = 1 AND created_at > '2026-01-01'
-- ⚠️ 范围列之后的列用不上排序:user_id、status 命中,created_at 是范围,
--    再往后如果还有列就用不上了
WHERE user_id = 1 AND status > 0 AND created_at = '...'

关键结论两条:查询条件里跳过了联合索引最左边的列,索引就用不上;遇到范围查询(>、<、BETWEEN、LIKE 前缀),该列能用,但它右边的列在索引里就用不上了。所以建联合索引时,等值查询的列放左边,范围查询的列放右边。

覆盖索引:消灭回表

前面说过二级索引查询要"回表"。如果一条查询需要的所有列,索引本身都包含了,数据库就不用回表 —— 这叫覆盖索引,EXPLAIN 的 Extra 会显示 Using index。

-- 假设有索引 idx_user_status_time (user_id, status, created_at)

-- 需要回表:要 amount,但索引里没有,得拿主键回聚簇索引取
SELECT amount FROM orders WHERE user_id = 1 AND status = 1;

-- 不用回表:要的 created_at 索引里就有 —— 覆盖索引
SELECT created_at FROM orders WHERE user_id = 1 AND status = 1;

-- 优化技巧:如果 amount 经常和这组条件一起查,把它加进索引
CREATE INDEX idx_cover ON orders (user_id, status, amount);
-- 这样 SELECT amount ... WHERE user_id=? AND status=? 也能覆盖了

这也是为什么很多人建议少用 SELECT *:* 几乎不可能被覆盖,永远要回表,还多传了你用不到的列。

排序与分页的索引优化

用索引消除 filesort

-- 如果没有合适索引,ORDER BY 会触发 Using filesort(在内存或磁盘里另排一遍)
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;

-- 有了 (user_id, created_at) 索引,因为索引本身有序,
-- 直接顺着叶子链表反向扫即可,Extra 不再出现 filesort
CREATE INDEX idx_user_time ON orders (user_id, created_at);

深分页优化

-- 慢:LIMIT 1000000, 20 要先扫过前 100 万行再丢掉
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 快:用"游标"代替偏移量,记住上一页最后的 id
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 如果必须用页码,用"延迟关联":先在覆盖索引里定位 id,再回表取整行
SELECT o.* FROM orders o
JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

索引不是越多越好

索引加速读,但拖慢写:每次 INSERT / UPDATE / DELETE 都要同步维护所有相关索引的 B+ 树。索引还占磁盘。所以建索引要克制:

• 区分度低的列别单独建索引。比如 status 只有 3 个值,单独建索引,优化器大概率认为还不如全表扫,直接不用。
• 能用联合索引覆盖的,别建多个单列索引。(a, b, c) 一个索引,等价于同时拥有 (a)、(a,b)、(a,b,c) 三个前缀索引。
• 定期清理用不上的索引。可以查 sys.schema_unused_indexes 看哪些索引从没被用过。

-- 查某张表上所有索引
SHOW INDEX FROM orders;

-- 查从未被使用的索引(MySQL 8.0)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';

-- 查索引的区分度:接近 1 说明区分度高,适合建索引;接近 0 则不适合
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;

一个完整的优化案例

把上面的知识串起来。假设线上有这条慢查询:

SELECT id, amount, created_at FROM orders
WHERE user_id = 88 AND status = 1 AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

第一步,EXPLAIN 看现状:假设 type=ALL、key=NULL、rows=2000000、Extra=Using filesort —— 全表扫 + 额外排序,典型的没索引。

第二步,设计索引:等值条件 user_id、status 放左边,范围条件 created_at 放右边,而且 created_at 还兼顾了 ORDER BY:

CREATE INDEX idx_opt ON orders (user_id, status, created_at);

第三步,再 EXPLAIN:type 变成 range,key=idx_opt,rows 降到几十,Extra 里的 filesort 消失(因为索引在 user_id、status 固定后,created_at 本身就是有序的,反向扫即可)。

第四步,考虑覆盖:查询要 amount,当前索引不含它,还要回表。如果这条查询 QPS 很高,可以把 amount 加进去做成覆盖索引 (user_id, status, created_at, amount),彻底消除回表。是否值得,取决于回表成本和写入成本的权衡。

不只有 B+ 树:其他索引类型

前面讲的都是 B+ 树索引,它是 InnoDB 的默认和主力。但还有几种索引类型,知道它们的存在能帮你应对特定场景。

-- 哈希索引:等值查询 O(1),但完全不支持范围查询和排序
-- InnoDB 不能手动建哈希索引,但有"自适应哈希索引"会自动对热点页建立
-- Memory 引擎默认就是哈希索引
CREATE TABLE cache (k VARCHAR(64), v TEXT, INDEX USING HASH (k)) ENGINE=MEMORY;

-- 全文索引:专门用于文本搜索,解决 LIKE '%关键词%' 用不上索引的问题
CREATE FULLTEXT INDEX idx_ft ON orders (remark);
SELECT * FROM orders
WHERE MATCH(remark) AGAINST('超时 退款' IN NATURAL LANGUAGE MODE);

-- 前缀索引:对长字符串列只索引前 N 个字符,省空间
-- N 的选取:让前缀的区分度接近完整列的区分度
SELECT COUNT(DISTINCT LEFT(remark, 10)) / COUNT(*) FROM orders;
CREATE INDEX idx_prefix ON orders (remark(10));

选型直觉:绝大多数场景用 B+ 树;纯等值、且不需要排序的内存表可考虑哈希;中文/英文全文检索用全文索引(或者干脆上 Elasticsearch);超长字符串列空间紧张时用前缀索引,但前缀索引无法用于覆盖索引和排序,要权衡。

优化器的隐藏武器:索引下推与 MRR

MySQL 5.6 之后,优化器有两个自动生效的优化,理解它们能解释一些"为什么这条查询比预期快"的现象。

索引条件下推(ICP,Index Condition Pushdown):正常情况下,二级索引只能用来过滤"它索引了的列",其余条件要回表后再过滤。ICP 让一部分条件在索引层就先过滤掉,减少回表次数。

-- 索引 (user_id, status),查询:
SELECT * FROM orders WHERE user_id = 1 AND status > 2 AND amount > 100;
-- 无 ICP:用 user_id 定位,回表后再判断 status 和 amount
-- 有 ICP:user_id 和 status 都在索引里,直接在索引层过滤完,
--         只对真正满足 user_id+status 的行回表判断 amount
-- EXPLAIN 的 Extra 出现 "Using index condition" 就是 ICP 生效了

多范围读取(MRR,Multi-Range Read):二级索引查出来的主键值往往是乱序的,直接回表会造成随机 I/O。MRR 先把主键收集起来排序,再按顺序回表,把随机 I/O 变成接近顺序 I/O。Extra 里出现 Using MRR 即生效。这两个优化你不用手动开,但看 EXPLAIN 时认得它们,就能更准确地判断一条 SQL 的真实成本。

实战:给一张真实业务表设计索引

假设有张消息表,主要有三类查询。我们一步步推导索引方案。

CREATE TABLE messages (
  id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  receiver_id BIGINT NOT NULL,
  sender_id   BIGINT NOT NULL,
  is_read     TINYINT NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL,
  content     TEXT
);

-- 查询1:某人的未读消息,按时间倒序
SELECT id, sender_id, created_at FROM messages
WHERE receiver_id = ? AND is_read = 0 ORDER BY created_at DESC;

-- 查询2:某人收到的全部消息,分页
SELECT id, sender_id, created_at FROM messages
WHERE receiver_id = ? ORDER BY created_at DESC LIMIT ?, 20;

-- 查询3:两人之间的会话
SELECT * FROM messages
WHERE receiver_id = ? AND sender_id = ? ORDER BY created_at DESC;

推导过程:查询 1 需要 receiver_id(等值)+ is_read(等值)+ created_at(排序),建 (receiver_id, is_read, created_at);查询 2 需要 receiver_id + created_at,正好是上面索引的"最左前缀的变体"—— 但中间隔了 is_read,用不上,所以查询 2 需要单独的 (receiver_id, created_at);查询 3 需要 (receiver_id, sender_id, created_at)。

CREATE INDEX idx_unread  ON messages (receiver_id, is_read, created_at);
CREATE INDEX idx_inbox   ON messages (receiver_id, created_at);
CREATE INDEX idx_session ON messages (receiver_id, sender_id, created_at);

注意一个权衡:idx_unread 和 idx_inbox 看起来有重叠,能不能合并?不能直接合并,因为 is_read 夹在中间会让查询 2 用不上排序。但如果业务上"未读消息"和"全部消息"两个入口的访问量差距悬殊,你也可以只保留高频的那个,让低频查询忍受一次 filesort。索引设计永远是"读性能、写性能、存储空间"三者之间的权衡,没有标准答案,只有适合当前业务的答案。

写在最后

索引优化没有玄学,它就是一套可推导的规则,根都在"B+ 树是有序的"这一句话上。有序,所以等值和范围查询快;有序,所以最左前缀原则成立;有序,所以能消除 filesort。记住排查的固定动作:慢了先 EXPLAIN,看 type 和 Extra,对照"索引失效场景"逐条排除,再按"等值在左、范围在右、高频列考虑覆盖"的原则设计联合索引。把这套流程练成肌肉记忆,大部分慢查询你都能在十分钟内定位并修复。

—— 别看了 · 2026