课程：B站大学
记录学习极客时间团队MySQL45讲，进阶数据分析和数据处理

MySQL实战：普通索引和唯一索引，应该怎么选择？

核心结论先行：在业务代码已经保证数据唯一性的前提下，优先选择普通索引。因为普通索引可以使用change buffer机制来大幅提升更新性能，而唯一索引无法享受这一优化。

一、问题背景

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，业务代码已经保证了不会写入两个重复的身份证号。需要按照身份证号查姓名：

SELECTnameFROMCUserWHEREid_card='xxxxxxxyyyyyyzzzzzz';

由于身份证号字段比较大，不建议当做主键，那么在id_card字段上建索引时，面临两个选择：

问题来了：从性能角度考虑，应该选择哪种？

二、查询过程：性能差异微乎其微

执行查询语句：

SELECTidFROMTWHEREk=5;

2.1 查找过程

InnoDB 中索引的查找过程：通过 B+ 树从根节点逐层搜索到叶子节点（数据页），然后在数据页内部通过二分法定位记录。

2.2 普通索引 vs 唯一索引

2.3 为什么差异可以忽略？

关键原因：InnoDB 按数据页（16KB）为单位读写数据。

当找到k=5的记录时，它所在的整个数据页已经加载到内存中了。普通索引多做的那一次"查找下一条记录"操作，只是在内存中做一次指针偏移，对现代 CPU 来说成本几乎为零。

即使极端情况下要跨数据页（该记录恰是数据页最后一条），由于一个整型数据页可存放近千个 key，跨页概率极低，平均性能差异仍可忽略。

结论：在查询性能上，普通索引和唯一索引几乎没有差别。

2.4 B+ 树索引结构示意

下图展示了 InnoDB 中 B+ 树索引的典型结构，查询时从根节点逐层定位到叶子节点（数据页）：

图示：B+ 树索引结构 —— 从根节点到叶子节点的查找路径，叶子节点以双向链表连接，每个节点对应一个 16KB 的数据页。

三、更新过程：真正拉开差距的地方

3.1 什么是 change buffer？

change buffer是 InnoDB 引入的一项更新优化机制：

当需要更新一个数据页时，如果数据页已在内存中，则直接更新；如果数据页不在内存中，InnoDB 会将这些更新操作缓存在 change buffer 中，避免立即从磁盘读取数据页。

其核心流程如下：

1. 更新操作写入 change buffer（内存中）
2. 后续查询需要访问该数据页时，将数据页读入内存
3. 将 change buffer 中缓存的操作应用到数据页，得到最新结果 —— 这个过程称为merge

change buffer 的特点：

• 可以持久化（内存中有拷贝，也会写入磁盘的系统表空间ibdata1）
• 后台线程定期执行 merge
• 数据库正常关闭时也会执行 merge
• 大小可通过参数innodb_change_buffer_max_size动态设置（如设为 50 表示最多占用 buffer pool 的 50%）

3.2 为什么唯一索引无法使用 change buffer？

唯一索引在更新时必须先判断唯一性约束：

-- 插入 (4, 400)，必须确认表中不存在 k=4 的记录INSERTINTOTVALUES(4,400);

要判断是否冲突，必须先把数据页从磁盘读入内存—— 既然数据页都已经在内存了，直接更新即可，根本不需要 change buffer。

结论：只有普通索引可以使用 change buffer，唯一索引不行。

3.3 两种场景的更新对比

假设执行INSERT INTO T VALUES (4, 400)：

场景一：目标数据页在内存中

差异：仅多一次唯一性判断，CPU 开销可忽略。

场景二：目标数据页不在内存中

差异巨大！随机磁盘IO是数据库中最昂贵的操作之一。change buffer 通过延迟读盘，将随机IO转化为内存操作，性能提升非常明显。

📌真实案例：有 DBA 将某业务表的普通索引改为唯一索引后，内存命中率从 99% 暴跌到 75%，更新语句全部堵塞。原因就是失去了 change buffer 的保护。

四、change buffer 的最佳使用场景

4.1 写多读少 → change buffer 效果最好

核心逻辑：merge 之前，change buffer 中累积的变更越多，收益越大。

适合的业务模型：

• 账单类系统：数据写入后很少立即查询
• 日志类系统：持续写入，批量读取分析
• 历史归档库：数据写入后主要做离线分析

4.2 写后立即读 → change buffer 反而有副作用

如果业务模式是"写入后马上查询"：

1. 更新先记录在 change buffer
2. 紧接着的查询立即触发 merge（从磁盘读入数据页）
3. 随机IO没有减少，反而多了 change buffer 的维护开销

这种场景下，建议关闭 change buffer。

五、change buffer 与 redo log 的关系

很多同学容易混淆这两个机制，它们虽然都服务于 WAL（Write-Ahead Logging）体系，但优化的方向不同。

5.1 执行流程示例

INSERTINTOt(id,k)VALUES(id1,k1),(id2,k2);

假设k1所在数据页在内存中，k2所在数据页不在内存中：

事务完成！总共写了两处内存 + 一次顺序磁盘写入。

图示：带 change buffer 的更新状态图 —— Page1 在内存中直接更新，Page2 不在内存中写入 change buffer，两个动作一并记入 redo log。

5.2 读请求的处理

后续执行SELECT * FROM t WHERE k IN (k1, k2)：

• Page1：直接从内存返回（即使磁盘上还是旧数据，内存中的结果是正确的）
• Page2：从磁盘读入 → 应用 change buffer 中的操作 → 返回正确结果

图示：读请求处理流程 —— Page1 直接从内存返回，Page2 需从磁盘加载并 merge change buffer 后返回。

5.3 核心区别总结

一句话总结：redo log 解决"写磁盘慢"的问题，change buffer 解决"读磁盘慢"的问题。

六、change buffer 掉电会丢失吗？

这是原文留下的思考题，也是面试高频考点。

答案：不会丢失。

原因有二：

1. change buffer 的修改也会写 redo log—— 事务提交时，change buffer 的变更同样被记录到 redo log 中并持久化到磁盘
2. change buffer 本身可持久化—— 内存中的 change buffer 有拷贝存储在系统表空间ibdata1中

掉电重启后的恢复流程：

• 已提交事务的 change buffer 操作 → 通过 redo log 恢复
• 未提交事务的 change buffer 操作 → 事务本身未提交，无需恢复

七、索引选择实践建议

7.1 通用建议

7.2 特别场景：归档库

线上数据保留半年，历史数据存入归档库。归档数据已经确保没有唯一键冲突，此时将唯一索引改为普通索引，可以显著提升归档写入速度。

7.3 查看 change buffer 命中率

可通过以下命令监控：

SHOWENGINEINNODBSTATUS;

关注INSERT BUFFER AND ADAPTIVE HASH INDEX部分中的hit rate。

一句话总结全文：

唯一索引用不上 change buffer，在高并发写入场景下性能差距可能达到数量级。如果业务已经能保证数据唯一性，请毫不犹豫地选择普通索引。

MySQL为什么有时候会选错索引？

一、问题背景

在 MySQL 中，一张表可以支持多个索引，但写 SQL 语句时并没有主动指定使用哪个索引——使用哪个索引完全由 MySQL 优化器决定。

问题来了：一条本来可以执行得很快的语句，会不会因为 MySQL选错了索引，导致执行速度变得很慢？

答案是：会的。下面通过实验来复现这个问题。

二、实验环境搭建

2.1 建表与数据准备

CREATETABLE`t`(idint(11)NOTNULL,aint(11)DEFAULTNULL,bint(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`a`(`a`),KEY`b`(`b`))ENGINE=InnoDB;

往表t中插入10 万行记录，取值按整数递增：(1,1,1), (2,2,2), ... (100000,100000,100000)。

-- 使用存储过程批量插入delimiter;;createprocedureidata()begindeclareiint;seti=1;while(i<=100000)doinsertintotvalues(i,i,i);seti=i+1;endwhile;end;;delimiter;callidata();

2.2 正常情况下的索引选择

mysql>select*fromtwhereabetween10000and20000;

用explain查看执行计划：

key字段值为'a'，优化器正确选择了索引a，一切符合预期。

三、复现"选错索引"的问题

3.1 触发场景

在两个 Session 中按以下顺序操作：

关键点：session A 开启了一致性读事务（长事务），session B 删除数据后重新插入 10 万行。

3.2 验证选错的后果

-- 将慢查询阈值设为0，所有语句都记录到慢查询日志setlong_query_time=0;select*fromtwhereabetween10000and20000;-- Q1：未指定索引select*fromtforceindex(a)whereabetween10000and20000;-- Q2：强制使用索引a

慢查询日志结果对比：

结论：MySQL 没有使用索引a，而是走了全表扫描，执行时间几乎是后者的2 倍。

四、优化器的逻辑

4.1 优化器的目标

选择索引是优化器的工作，目标是找到最优执行方案，用最小代价执行语句。

影响代价的因素包括：

• 扫描行数（主要因素）：扫描行数越少 → 磁盘 I/O 越少 → CPU 消耗越少
• 是否使用临时表
• 是否排序

4.2 扫描行数怎么判断？

MySQL 在真正执行语句之前，无法精确知道满足条件的记录有多少条，只能根据统计信息来估算。

核心定义：基数（Cardinality）

基数= 索引上不同值的个数。基数越大，索引的区分度越好。

showindexfromt;

可以看到，三个索引的基数值并不准确（即使每列值都一样，基数统计值却不同）。

4.3 索引统计的采样机制

为什么用采样？因为全表逐行统计代价太高，只能选择"采样统计"。

InnoDB 采样统计流程：

1. 默认选择N 个数据页
2. 统计这些页面上的不同值，取平均值
3. 乘以索引的总页面数→ 得到基数估计值

参数innodb_stats_persistent控制存储方式：

当变更的数据行数超过1/M时，自动触发重新统计。

问题：因为是采样统计，无论 N=20 还是 N=8，基数都很容易不准。

4.4 为什么统计不准还选错？

查看优化器预估的扫描行数：

关键矛盾：优化器预估索引a要扫描 37116 行，而全表扫描要扫描 104620 行——看起来索引a更优。但实际执行时，优化器却选择了全表扫描。

真正的原因：

使用普通索引a时，每次从索引上拿到一个值，都要回表（回到主键索引查出整行数据），这个回表代价优化器也要算进去。

而全表扫描是直接在主键索引上顺序扫描，没有额外的回表代价。

优化器综合评估后，认为直接扫主键索引"代价更小"——但这个判断基于错误的行数估计，导致最终选择并非最优。

4.5 解决方案一：ANALYZE TABLE

analyzetablet;

执行后，索引统计信息被重新准确计算：

rows预估恢复到正确值（约 10000），优化器重新选择了索引a。

实践建议：如果发现explain的rows预估与实际差距很大，优先尝试ANALYZE TABLE。

五、更复杂的选错场景

5.1 多条件查询的索引选择

mysql>select*fromtwhere(abetween1and1000)and(bbetween50000and100000);

先分析两个索引的结构：

两种方案对比：

显然应该用索引a，但explain结果却是：

• key=b（选了索引 b）
• rows=50198

扫描行数估计依然不准，且又选错了索引。

六、索引选择异常的三种处理方法

方法一：FORCE INDEX（强制指定索引）

-- 错误选择（2.23 秒）select*fromtwhereabetween1and1000andbbetween50000and100000orderbyblimit1;-- 强制使用索引a（0.05 秒）select*fromtforceindex(a)whereabetween1and1000andbbetween50000and100000orderbyblimit1;

效果对比：2.23 秒 → 0.05 秒，快了 40 多倍！

FORCE INDEX 的原理：

MySQL 先根据词法解析得出候选索引列表，如果FORCE INDEX指定的索引在候选列表中，就直接选择它，不再评估其他索引的代价。

缺点：

• 写法不优雅，索引改名后 SQL 也要改
• 迁移到其他数据库可能不兼容
• 变更的及时性差——往往线上出问题后才去加，修改后还需测试发布

方法二：改写 SQL 语句，引导优化器

思路：通过改变 SQL 语义，让优化器倾向于选择我们期望的索引。

-- 原语句：优化器选了 b（因为 order by b 可以利用索引 b 的有序性，避免排序）select*fromtwhere(abetween1and1000)and(bbetween50000and100000)orderbyblimit1;-- 改写：order by b, a → 两个索引都需要排序 → 扫描行数成为主要决策因素select*fromtwhere(abetween1and1000)and(bbetween50000and100000)orderbyb,alimit1;

效果：

原理：原来优化器选b是因为可以避免排序（索引 b 本身有序）。改成order by b, a后，两个索引都需要排序，扫描行数就成了主要考量，优化器自然选了只需扫描 1000 行的索引a。

另一种改法：用子查询 +LIMIT诱导优化器

select*from(select*fromtwhere(abetween1and1000)and(bbetween50000and100000)-- 加 limit 让优化器意识到用 b 代价很高limit100)astmporderbyblimit1;

⚠️ 以上改写方法不具备通用性，只是在特定场景下诱导优化器，实际使用时需谨慎验证语义一致性。

方法三：增删索引

• 新建更合适的索引，提供给优化器更好的选择
• 删除误用的索引——看似极端，但实际生产中确实遇到过：DBA 与业务沟通后发现，优化器错误选择的索引本身就没有必要存在，删掉后优化器自然选到了正确的索引

7.1 选错索引的根本原因

采样统计不准确 → 基数估计偏差 → 扫描行数预估错误 → 优化器代价计算失误 → 选错索引

7.2 决策指南

实践是检验真理的唯一标准