课程B站大学记录学习极客时间团队MySQL45讲进阶数据分析和数据处理MySQL普通索引和唯一索引MySQL实战普通索引和唯一索引应该怎么选择一、问题背景二、查询过程性能差异微乎其微2.1 查找过程2.2 普通索引 vs 唯一索引2.3 为什么差异可以忽略2.4 B 树索引结构示意三、更新过程真正拉开差距的地方3.1 什么是 change buffer3.2 为什么唯一索引无法使用 change buffer3.3 两种场景的更新对比场景一目标数据页在内存中场景二目标数据页不在内存中四、change buffer 的最佳使用场景4.1 写多读少 → change buffer 效果最好4.2 写后立即读 → change buffer 反而有副作用五、change buffer 与 redo log 的关系5.1 执行流程示例5.2 读请求的处理5.3 核心区别总结六、change buffer 掉电会丢失吗七、索引选择实践建议7.1 通用建议7.2 特别场景归档库7.3 查看 change buffer 命中率MySQL为什么有时候会选错索引一、问题背景二、实验环境搭建2.1 建表与数据准备2.2 正常情况下的索引选择三、复现选错索引的问题3.1 触发场景3.2 验证选错的后果四、优化器的逻辑4.1 优化器的目标4.2 扫描行数怎么判断核心定义基数Cardinality4.3 索引统计的采样机制4.4 为什么统计不准还选错4.5 解决方案一ANALYZE TABLE五、更复杂的选错场景5.1 多条件查询的索引选择六、索引选择异常的三种处理方法方法一FORCE INDEX强制指定索引方法二改写 SQL 语句引导优化器方法三增删索引7.1 选错索引的根本原因7.2 决策指南实践是检验真理的唯一标准MySQL实战普通索引和唯一索引应该怎么选择核心结论先行在业务代码已经保证数据唯一性的前提下优先选择普通索引。因为普通索引可以使用change buffer机制来大幅提升更新性能而唯一索引无法享受这一优化。一、问题背景假设你在维护一个市民系统每个人都有一个唯一的身份证号业务代码已经保证了不会写入两个重复的身份证号。需要按照身份证号查姓名SELECTnameFROMCUserWHEREid_cardxxxxxxxyyyyyyzzzzzz;由于身份证号字段比较大不建议当做主键那么在id_card字段上建索引时面临两个选择方案索引类型说明方案A唯一索引UNIQUE INDEX语义上保证唯一性方案B普通索引INDEX仅加速查询不做唯一约束问题来了从性能角度考虑应该选择哪种二、查询过程性能差异微乎其微执行查询语句SELECTidFROMTWHEREk5;2.1 查找过程InnoDB 中索引的查找过程通过 B 树从根节点逐层搜索到叶子节点数据页然后在数据页内部通过二分法定位记录。2.2 普通索引 vs 唯一索引索引类型查找行为额外开销普通索引找到第一条满足条件的记录后继续查找下一条直到碰到不满足条件的记录多一次指针寻找 计算唯一索引找到第一条满足条件的记录后立即停止检索无2.3 为什么差异可以忽略关键原因InnoDB 按数据页16KB为单位读写数据。当找到k5的记录时它所在的整个数据页已经加载到内存中了。普通索引多做的那一次查找下一条记录操作只是在内存中做一次指针偏移对现代 CPU 来说成本几乎为零。即使极端情况下要跨数据页该记录恰是数据页最后一条由于一个整型数据页可存放近千个 key跨页概率极低平均性能差异仍可忽略。结论在查询性能上普通索引和唯一索引几乎没有差别。2.4 B 树索引结构示意下图展示了 InnoDB 中 B 树索引的典型结构查询时从根节点逐层定位到叶子节点数据页图示B 树索引结构 —— 从根节点到叶子节点的查找路径叶子节点以双向链表连接每个节点对应一个 16KB 的数据页。三、更新过程真正拉开差距的地方3.1 什么是 change bufferchange buffer是 InnoDB 引入的一项更新优化机制当需要更新一个数据页时如果数据页已在内存中则直接更新如果数据页不在内存中InnoDB 会将这些更新操作缓存在 change buffer 中避免立即从磁盘读取数据页。其核心流程如下更新操作写入 change buffer内存中后续查询需要访问该数据页时将数据页读入内存将 change buffer 中缓存的操作应用到数据页得到最新结果 —— 这个过程称为mergechange buffer 的特点可以持久化内存中有拷贝也会写入磁盘的系统表空间ibdata1后台线程定期执行 merge数据库正常关闭时也会执行 merge大小可通过参数innodb_change_buffer_max_size动态设置如设为 50 表示最多占用 buffer pool 的 50%3.2 为什么唯一索引无法使用 change buffer唯一索引在更新时必须先判断唯一性约束-- 插入 (4, 400)必须确认表中不存在 k4 的记录INSERTINTOTVALUES(4,400);要判断是否冲突必须先把数据页从磁盘读入内存—— 既然数据页都已经在内存了直接更新即可根本不需要 change buffer。结论只有普通索引可以使用 change buffer唯一索引不行。3.3 两种场景的更新对比假设执行INSERT INTO T VALUES (4, 400)场景一目标数据页在内存中步骤唯一索引普通索引1找到插入位置3 和 5 之间找到插入位置3 和 5 之间2判断无冲突——3插入值结束插入值结束差异仅多一次唯一性判断CPU 开销可忽略。场景二目标数据页不在内存中步骤唯一索引普通索引1从磁盘读入数据页随机IO将更新记录写入 change buffer2判断无冲突语句执行结束3插入值结束——差异巨大随机磁盘IO是数据库中最昂贵的操作之一。change buffer 通过延迟读盘将随机IO转化为内存操作性能提升非常明显。真实案例有 DBA 将某业务表的普通索引改为唯一索引后内存命中率从 99% 暴跌到 75%更新语句全部堵塞。原因就是失去了 change buffer 的保护。四、change buffer 的最佳使用场景4.1 写多读少 → change buffer 效果最好核心逻辑merge 之前change buffer 中累积的变更越多收益越大。适合的业务模型账单类系统数据写入后很少立即查询日志类系统持续写入批量读取分析历史归档库数据写入后主要做离线分析4.2 写后立即读 → change buffer 反而有副作用如果业务模式是写入后马上查询更新先记录在 change buffer紧接着的查询立即触发 merge从磁盘读入数据页随机IO没有减少反而多了 change buffer 的维护开销这种场景下建议关闭 change buffer。五、change buffer 与 redo log 的关系很多同学容易混淆这两个机制它们虽然都服务于 WALWrite-Ahead Logging体系但优化的方向不同。5.1 执行流程示例INSERTINTOt(id,k)VALUES(id1,k1),(id2,k2);假设k1所在数据页在内存中k2所在数据页不在内存中步骤操作说明①更新内存中的 Page1直接修改 buffer pool②在 change buffer 记录往 Page2 插入一行Page2 不在内存③将①②两个动作写入 redo log顺序写磁盘一次完成事务完成总共写了两处内存 一次顺序磁盘写入。图示带 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 核心区别总结机制节省的IO类型作用阶段redo log随机写磁盘 → 顺序写事务提交时change buffer随机读磁盘 → 延迟到查询时数据更新时一句话总结redo log 解决写磁盘慢的问题change buffer 解决读磁盘慢的问题。六、change buffer 掉电会丢失吗这是原文留下的思考题也是面试高频考点。答案不会丢失。原因有二change buffer 的修改也会写 redo log—— 事务提交时change buffer 的变更同样被记录到 redo log 中并持久化到磁盘change buffer 本身可持久化—— 内存中的 change buffer 有拷贝存储在系统表空间ibdata1中掉电重启后的恢复流程已提交事务的 change buffer 操作 → 通过 redo log 恢复未提交事务的 change buffer 操作 → 事务本身未提交无需恢复七、索引选择实践建议7.1 通用建议条件建议业务代码已保证唯一性✅ 优先选择普通索引需要数据库层做唯一约束✅ 必须使用唯一索引写多读少日志/账单/归档✅ 普通索引 调大 change buffer写后立即读考虑关闭 change buffer使用机械硬盘✅ 特别关注普通索引 大 change buffer 收益极大7.2 特别场景归档库线上数据保留半年历史数据存入归档库。归档数据已经确保没有唯一键冲突此时将唯一索引改为普通索引可以显著提升归档写入速度。7.3 查看 change buffer 命中率可通过以下命令监控SHOWENGINEINNODBSTATUS;关注INSERT BUFFER AND ADAPTIVE HASH INDEX部分中的hit rate。一句话总结全文唯一索引用不上 change buffer在高并发写入场景下性能差距可能达到数量级。如果业务已经能保证数据唯一性请毫不犹豫地选择普通索引。MySQL为什么有时候会选错索引一、问题背景在 MySQL 中一张表可以支持多个索引但写 SQL 语句时并没有主动指定使用哪个索引——使用哪个索引完全由 MySQL 优化器决定。问题来了一条本来可以执行得很快的语句会不会因为 MySQL选错了索引导致执行速度变得很慢答案是会的。下面通过实验来复现这个问题。二、实验环境搭建2.1 建表与数据准备CREATETABLEt(idint(11)NOTNULL,aint(11)DEFAULTNULL,bint(11)DEFAULTNULL,PRIMARYKEY(id),KEYa(a),KEYb(b))ENGINEInnoDB;往表t中插入10 万行记录取值按整数递增(1,1,1), (2,2,2), ... (100000,100000,100000)。-- 使用存储过程批量插入delimiter;;createprocedureidata()begindeclareiint;seti1;while(i100000)doinsertintotvalues(i,i,i);setii1;endwhile;end;;delimiter;callidata();2.2 正常情况下的索引选择mysqlselect*fromtwhereabetween10000and20000;用explain查看执行计划key字段值为a优化器正确选择了索引a一切符合预期。三、复现选错索引的问题3.1 触发场景在两个 Session 中按以下顺序操作时间序session Asession B1start transaction with consistent snapshot;2delete from t; call idata();3explain select * from t where a between 10000 and 20000;4commit;关键点session A 开启了一致性读事务长事务session B 删除数据后重新插入 10 万行。3.2 验证选错的后果-- 将慢查询阈值设为0所有语句都记录到慢查询日志setlong_query_time0;select*fromtwhereabetween10000and20000;-- Q1未指定索引select*fromtforceindex(a)whereabetween10000and20000;-- Q2强制使用索引a慢查询日志结果对比查询扫描行数执行时间Q1无 force index100000 行全表扫描40 msQ2force index(a)10001 行索引扫描21 ms结论MySQL 没有使用索引a而是走了全表扫描执行时间几乎是后者的2 倍。四、优化器的逻辑4.1 优化器的目标选择索引是优化器的工作目标是找到最优执行方案用最小代价执行语句。影响代价的因素包括扫描行数主要因素扫描行数越少 → 磁盘 I/O 越少 → CPU 消耗越少是否使用临时表是否排序4.2 扫描行数怎么判断MySQL 在真正执行语句之前无法精确知道满足条件的记录有多少条只能根据统计信息来估算。核心定义基数Cardinality基数 索引上不同值的个数。基数越大索引的区分度越好。showindexfromt;可以看到三个索引的基数值并不准确即使每列值都一样基数统计值却不同。4.3 索引统计的采样机制为什么用采样因为全表逐行统计代价太高只能选择采样统计。InnoDB 采样统计流程默认选择N 个数据页统计这些页面上的不同值取平均值乘以索引的总页面数→ 得到基数估计值参数innodb_stats_persistent控制存储方式设置值存储位置默认 N采样页数默认 M触发重新统计的变更比例 1/MON持久化磁盘2010OFF仅内存内存816当变更的数据行数超过1/M时自动触发重新统计。问题因为是采样统计无论 N20 还是 N8基数都很容易不准。4.4 为什么统计不准还选错查看优化器预估的扫描行数查询预估扫描行数rows实际情况Q1全表扫描104620≈ 10 万行 ✓Q2索引 a37116实际仅 10001 行 ✗关键矛盾优化器预估索引a要扫描 37116 行而全表扫描要扫描 104620 行——看起来索引a更优。但实际执行时优化器却选择了全表扫描。真正的原因使用普通索引a时每次从索引上拿到一个值都要回表回到主键索引查出整行数据这个回表代价优化器也要算进去。而全表扫描是直接在主键索引上顺序扫描没有额外的回表代价。优化器综合评估后认为直接扫主键索引代价更小——但这个判断基于错误的行数估计导致最终选择并非最优。4.5 解决方案一ANALYZE TABLEanalyzetablet;执行后索引统计信息被重新准确计算rows预估恢复到正确值约 10000优化器重新选择了索引a。实践建议如果发现explain的rows预估与实际差距很大优先尝试ANALYZE TABLE。五、更复杂的选错场景5.1 多条件查询的索引选择mysqlselect*fromtwhere(abetween1and1000)and(bbetween50000and100000);先分析两个索引的结构两种方案对比方案扫描过程预估扫描行数使用索引a扫描索引 a 前 1000 个值 → 回表 → 过滤 b 条件1000 行使用索引b扫描索引 b 最后 50001 个值 → 回表 → 过滤 a 条件50001 行显然应该用索引a但explain结果却是keyb选了索引 brows50198扫描行数估计依然不准且又选错了索引。六、索引选择异常的三种处理方法方法一FORCE INDEX强制指定索引-- 错误选择2.23 秒select*fromtwhereabetween1and1000andbbetween50000and100000orderbyblimit1;-- 强制使用索引a0.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 决策指南场景推荐方案说明统计信息不准ANALYZE TABLE t;重新采样统计简单有效优化器误判FORCE INDEX(idx)见效快但有维护和兼容性问题可改写 SQL调整 WHERE / ORDER BY引导优化器无需改索引索引本身多余删除误用索引DBA 与业务沟通后执行实践是检验真理的唯一标准