3、索引优化策略：B+树索引原理、联合索引设计、索引下推与覆盖索引

好，咱们今天聊聊索引。说实话，我在面试候选人的时候，只要问三个关于索引的问题，基本就能摸清他的底子有多深。哪三个？B+树到底长什么样、联合索引怎么设计最合理、还有索引下推和覆盖索引的区别。今天咱们就把这三个硬骨头啃下来。

3.1 B+树索引原理：别再说它只是一棵树

很多人一提到B+树，就说「哦，就是一棵平衡树」。嗯，这话没错，但太笼统了。我习惯把B+树想象成一个多层级的目录系统。

最上层是「根节点」，中间是「内部节点」，最下面是「叶子节点」。关键区别在哪？只有叶子节点才存数据，内部节点只存「路标」——也就是索引键值和指向下一层的指针。

举个例子，假设你在找一本书的第256页。你不会从第1页开始翻，对吧？你会先看目录，找到「第3章」从200页开始，然后直接翻到200页附近。B+树干的就是这个事。

核心要点：B+树的所有叶子节点通过双向链表连接，形成一个有序的序列。这意味着范围查询（比如 WHERE id BETWEEN 100 AND 200）可以顺着链表一路扫过去，效率极高。

我在项目中遇到过一个问题：某张表数据量到了5000万行，一个简单的范围查询跑了8秒。当时我一看执行计划，发现它走了全表扫描。为什么？因为索引的叶子节点没有按查询条件排序。后来重建了索引，查询时间降到了30毫秒。嗯，这就是B+树的威力。

3.2 联合索引设计：最左前缀原则不是闹着玩的

联合索引，说白了就是把多个字段拼在一起建一个索引。但怎么拼？顺序怎么定？这里头门道不少。

我给大家一个口诀：「等值在前，排序在后，覆盖优先」。什么意思？

  • 等值条件的字段放在最前面。比如 WHERE a = 1 AND b > 2，a是等值，b是范围，那索引应该建在 (a, b) 上。
  • 排序字段尽量利用索引的有序性。比如 ORDER BY c，如果c已经在索引里，就能避免文件排序。
  • 覆盖索引能少回表就少回表。后面会细说。

来看个具体的例子。假设我们有张订单表：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    create_time DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_user_status_time (user_id, status, create_time)
);

这个索引 (user_id, status, create_time) 能支持哪些查询？

查询语句 是否走索引 原因
WHERE user_id = 1 ✅ 走索引 匹配最左前缀 user_id
WHERE user_id = 1 AND status = 2 ✅ 走索引 匹配 user_id + status
WHERE user_id = 1 AND status = 2 AND create_time > '2024-01-01' ✅ 走索引 完全匹配三个字段
WHERE status = 2 ❌ 不走索引 跳过了 user_id，违反最左前缀
WHERE user_id = 1 AND amount > 100 ✅ 部分走索引 user_id 走索引，amount 不在索引中，需要回表

我曾经踩过的坑：有一次我把选择性最高的字段放在最前面，结果查询反而变慢了。为什么？因为那个字段虽然是高选择性的，但查询条件里很少单独用它。你想想看，索引的「最左前缀」决定了只有从第一个字段开始连续匹配才能生效。如果第一个字段很少被用到，那这个索引基本就废了。

3.3 索引下推：MySQL 5.6 后的隐藏福利

索引下推（Index Condition Pushdown，ICP）这个概念，说实话，很多人用了好几年MySQL都不知道它的存在。但它确实是个好东西。

没有ICP的时候，MySQL是怎么工作的？举个例子：

SELECT * FROM orders 
WHERE user_id = 1 AND status LIKE '%已完成%';

假设索引是 (user_id, status)。没有ICP时，MySQL会这样干：

  1. 通过索引找到所有 user_id = 1 的记录（假设有1000条）。
  2. 对这1000条记录逐一回表，到磁盘上读取完整行数据。
  3. 在Server层对 status 做 LIKE 匹配，过滤掉不符合的。

有ICP时呢？

  1. 同样通过索引找到 user_id = 1 的记录。
  2. 在存储引擎层，直接对索引中的 status 字段做 LIKE 匹配，过滤掉不符合的。
  3. 只对剩下的记录回表。

说白了，ICP就是把过滤操作「下推」到了存储引擎层，减少了回表的次数。我个人习惯在创建联合索引时，把范围查询的字段尽量往后放，这样ICP能发挥更大作用。

小技巧：想知道你的查询有没有用到ICP？看执行计划里的 Extra 列，如果显示 "Using index condition"，就说明ICP生效了。

3.4 覆盖索引：少一次回表，快一个数量级

覆盖索引这个概念，说白了就是「索引里啥都有，不用回表」。你想想看，如果查询需要的所有字段都在索引的叶子节点里，那MySQL就没必要再去磁盘上读完整行数据了。

举个例子：

-- 假设有索引 (user_id, status, create_time)
SELECT user_id, status, create_time 
FROM orders 
WHERE user_id = 1;

这个查询需要的三个字段，全都在索引里。MySQL直接从索引的叶子节点就能拿到数据，不需要回表。这就是覆盖索引。

再看一个反面例子：

SELECT user_id, status, amount 
FROM orders 
WHERE user_id = 1;

amount 字段不在索引里，MySQL只能回表去拿。这时候 Extra 列会显示 "Using index condition" 或者干脆没有覆盖索引的提示。

我的经验：对于高频查询，我通常会设计「宽索引」，把查询中常用的字段都包含进来。比如一个订单列表页，每次只查 id、status、create_time 三个字段，那我就建一个只包含这三个字段的联合索引。这样查询几乎全在内存里完成，速度飞快。

但要注意，覆盖索引不是万能的。索引字段越多，占用的空间越大，写入时的开销也越大。我见过有人把一张表的20个字段全塞进一个索引里，结果写入性能直接腰斩。嗯，这里要把握好度。

3.5 实战建议：索引设计的三个原则

最后，我总结一下索引设计的三个原则，都是我在实际项目中摔打出来的：

  1. 小表不用索引。一张表就几百行数据，全表扫描比走索引还快。因为索引有额外的IO开销。
  2. 区分度高的字段优先。比如性别字段，只有男女两种值，区分度太低，建索引意义不大。但用户ID、手机号这种，几乎每条记录都不同，建索引效果显著。
  3. 不要过度索引。一张表的索引数量建议控制在5个以内。索引不是越多越好，每多一个索引，写入时就要多维护一棵B+树。

我曾经接手过一个项目，一张表上有12个索引，结果每次INSERT都要花好几秒。后来删掉了7个冗余索引，写入性能提升了10倍。你想想看，索引是拿来用的，不是拿来供着的。

好了，这一章的内容就到这里。下一章咱们聊聊慢查询日志的分析和优化，那可是实打实的实战技能。

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