更新字段会更新索引吗？InnoDB索引机制深度解析

直接给结论但凡更新字段的操作都会在索引上留下痕迹。真正的问题是这个字段到底是索引里的哪个字段以及更新发生在什么层级。在MySQL里索引不是独立于表的影子而是和表数据绑定在一起的物理结构。你更新一个非索引字段索引树无感更新一个索引字段索引树一定跟着动。这跟很多人想的MySQL会自动判断值变了才更新索引完全不是一回事MySQL没那么聪明它对索引的维护是绝对的、无条件的。这篇文章写给三类人一是刚入行、被面试题和网上各种索引失效总结绕晕的新手想弄清楚索引到底长什么样、更新字段时MySQL内部发生了什么二是已经写了几年代码但只停留在建了索引查询就快这个层面想深入了解一下索引和数据行之间怎么绑定的开发三是准备做数据库调优或排查慢SQL的运维和架构师想搞清楚为什么有时候更新一列数据会拖慢整个表。内容基于InnoDB存储引擎因为现在绝大多数线上MySQL实例用的都是它讲清楚InnoDB就等于讲清楚你手上的生产环境。1. 先搞明白索引在哪索引和数据行是长在一起的1.1 InnoDB的索引即数据数据即索引很多人对索引的理解是图书目录说索引就是单独建一张表存着关键字和页码的对应关系查的时候先去目录里翻再按页码去正文找。这个类比在学习阶段没问题但到了理解更新行为的层面它反而会误导你。InnoDB里主键索引的叶子节点直接存的就是整行数据。也就是说一张表在磁盘上的物理形态就是一颗以主键为key的B树树的每个叶子节点里躺着完整的一行记录。你创建一张没有主键的表InnoDB也会偷偷给你生成一个隐藏主键row_id照样建树。所以索引存储在什么地方这个问题答案不是有一个专门的索引文件而是你的表数据文件本身就是主键索引。这就解释了为什么主键索引也叫聚簇索引数据行按照主键的顺序物理聚集在叶子节点上页与页之间通过双向链表连接同一个页内部的记录通过单向链表连接。你要按主键查走这棵树你要全表扫也是在一层层遍历这棵树。1.2 二级索引的叶子节点不存数据存主键那非主键的索引呢比如你给name字段建了一个普通索引这棵B树的叶子节点存的就不是整行了而是(name, 主键值)这样的键值对。你在查询里用name条件命中索引后MySQL拿着叶子节点上的主键值再回聚簇索引树上做一次主键查找才能把整行数据取出来这个过程叫回表。这套设计是MySQL索引行为里最重要的一张底牌二级索引的一切维护动作最终都要落到主键上。你先记住这句话后面讲更新字段为什么会影响多个索引的时候就全靠它了。1.3 索引树上的有序性是怎么来的B树有别于hash索引的一点是它内部严格有序。InnoDB里的索引树层级之间按页分隔页内部按索引键值排序存储。插入新数据时如果页满了就要发生页分裂删除数据时如果页太空就可能页合并。这套机制的代价是写路径上必须维护有序性。更新一个索引字段的时候本质上你要先做一次删除再插入旧值在树上的那个位置作废了主键没变但索引键变了新的键值必须按大小重新放到树里应该待的位置。这中间涉及到定位旧节点、标记删除、找到新位置、可能触发页分裂等一系列动作。所以别小看一条update语句它背后远不止改一行数据那么简单。2. 更新字段到底动没动索引分四种场景说清楚2.1 场景一更新非索引字段比如表里有主键id、姓名name有索引、年龄age无索引你执行UPDATE user SET age 30 WHERE id 100;这条语句的路径是这样的MySQL通过主键索引直接定位到id100的聚簇索引叶子节点然后在这个叶子节点的记录上把age字段的内存值改掉同时标记这条记录为脏数据。注意这里只操作了一棵聚簇索引树而且树的形状和顺序完全没有变化。因为age不是索引键age的改动不影响任何键值的有序性所以叶子节点在页里的物理位置也不会挪动。结论很明确更新非索引字段不会重建索引也不会更新任何二级索引。但需要补充一个细节就算age不在任何索引里这条update仍然会写redo log和undo log事务提交时还要保证binlog和redo log一致。这是事务和崩溃恢复层面的开销跟索引无关但很多人把这块开销误算到了更新字段导致索引更新头上。2.2 场景二更新二级索引字段这是问题的主体。比如name字段上有索引执行UPDATE user SET name zhangsan_new WHERE id 100;MySQL要做两步先在聚簇索引树上定位并更新name字段聚簇索引里也有name这个值因为整行都在这里然后把name的旧值在二级索引树上的那一条记录标记删除最后插入一条(zhangsan_new, 100)到二级索引树中合适的位置。这里有个容易忽略的点这一步不一定会立即物理清除旧记录。InnoDB的索引页有复用机制被标记删除的记录其空间会被后续插入复用。所以在update的瞬间二级索引树上可能同时存在新值和旧值的影子只是旧值对查询不可见。垃圾回收purge线程会择机把这些已删除的版本彻底清理干净。另外如果name上新值和旧值一样比如本来就叫zhangsan你update一条namezhangsanMySQL不会做任何优化跳过索引更新。它会老老实实把旧条目标记删除再插入一个新条目。很多新手以为值没变MySQL应该不更新吧实测结果会让你失望InnoDB不比较新旧值是否相等它只看你有没有执行更新操作执行了就按更新路径走。所以平时写代码尽量避免那种UPDATE t SET name name的无脑更新它会白白产生索引维护开销。2.3 场景三更新主键字段更新主键是所有场景里代价最大的。比如UPDATE user SET id 200 WHERE id 100;因为聚簇索引的叶子节点是按主键排序的主键变了等于这行数据在整棵树上要挪位置而不仅仅是改一个字段。InnoDB的处理方式是先在原位置删除这条记录然后以新主键重新插入到正确的位置。如果新主键值落在另一个页那涉及到的不仅仅是目标页还有沿途的页分裂、页合并的概率性操作。更坑的是所有二级索引的叶子节点都存着主键值。你更新主键就等于告诉MySQL请把我所有二级索引的叶子节点上的主键值都改一遍。假设你表上建了5个二级索引这一条update就会产生5次索引树修改。所以生产环境里我基本不推荐大家设计可更新的业务主键。如果非要改选在夜间低峰期操作并且提前评估索引数量带来的放大效应。2.4 场景四更新唯一索引字段唯一索引和普通索引在更新路径上没有本质区别都是删旧插新。但多了一层唯一性检查新值插入二级索引树时需要额外判断树里是否已有相同键值。这个检查不是先查一遍全表而是利用B树的有序性在插入位置旁边探测相邻节点就能确认有没有冲突。代价比全表扫小得多但仍比普通索引的更新多一步校验逻辑。值得一提的是为什么唯一索引的校验不做成加一把全局锁查完再放行因为在高并发下这会导致插入和更新全部串行化。InnoDB采用的方式是在插入时对索引记录加锁插入意向锁、记录锁的组合如果检测到重复键就报错并回滚。这套机制本身也和索引更新行为紧密相关你在并发场景下更新唯一索引字段可能遇到锁等待甚至死锁原因就在这里。3. 更新操作在InnoDB内部到底怎么执行的3.1 从一条update看binlog、redo log、undo log的配合索引更新的全过程不是简单地改一棵树。为了保证崩溃恢复和事务回滚InnoDB有一套完整的日志配合机制。以场景二的UPDATE user SET name zhangsan_new WHERE id 100为例执行时大致经历这些环节第一开启事务后先在undo log里写入一条旧值镜像也就是记录id100这一行原来的name是zhangsan。万一事务回滚MySQL靠这条undo log把name改回去同时把二级索引树上插入的新条目标记删除、把旧条目的删除标记取消恢复原状。第二在聚簇索引的缓冲池页里更新name字段值这个页变成脏页同时生成对应的redo log记录某个page的某个offset处name被写成了新值。redo log是物理日志它关心的是页上字节的变化不关心业务含义。第三对二级索引树执行删旧插新这种结构性变更也会记录到redo log里。需要注意B树插入叶子节点导致页分裂时redo log记录的是一个MINI-INSERT事务InnoDB对插入操作有特殊的日志类型支持。第四事务提交阶段binlog写入。binlog是逻辑日志记录的是SQL语句本身或行级别的前后镜像。MySQL通过两阶段提交保证binlog和redo log的一致性这也是主从复制环境下索引行为一致的前提。3.2 change buffer二级索引更新的加速器如果你更新的是二级索引字段而这条记录的二级索引页此刻不在内存里InnoDB并不会立刻把索引页从磁盘读入缓冲池再修改。它会把这次更新缓存到一个叫change buffer的结构里等这个索引页将来被读取到缓冲池时再把缓存的操作合并应用到页上。这个机制非常有意思它能显著减少随机磁盘I/O因为你不必为了改一个索引键值而立刻把整个索引页读进来。但代价是如果change buffer里的操作堆积较多一旦该索引页被读到合并merge过程可能让这条查询变慢。所以在更新频繁、二级索引多的表上偶尔出现某个查询突然变慢很可能就是触发了一次大的change buffer合并。从DBA角度看change buffer占了缓冲池的配额默认最多25%可通过innodb_change_buffer_max_size调整。如果你发现更新性能瓶颈在change buffer上而不是IO上再考虑调优否则随意修改参数可能适得其反。3.3 缓冲池和脏页刷新对更新性能的影响索引更新后修改先落在缓冲池里的页上这些页被称为脏页。脏页最终要由后台线程刷到磁盘而不是每次update都立即落盘。这样做的好处是能把磁盘写入合并成批量坏处是系统如果突然宕机必须依赖redo log来重放未刷盘的更新。重放redo log本质就是把索引页上该做的插入、删除操作再做一遍。因为redo log是顺序写的性能和可靠性都比随机刷盘好得多。你要理解一个点MySQL对索引的更新最终一定会在磁盘上完成但不是同步完成。所以update是不是立刻更新了索引文件这个问题准确的回答是立刻更新了缓冲池里的索引树但磁盘上索引树的更新是延迟异步的。如果你去翻SHOW ENGINE INNODB STATUS看到的Modified db pages数量就是当前缓冲池里已经被更新过但还没刷回磁盘的页数。这个数字长期偏高不用慌只要redo log生成速度能和刷盘速度匹配就行。真正需要警惕的是History list length持续上涨那是undo log没被purge线程及时清理的信号。3.4 一条更新语句对自适应哈希索引的影响InnoDB除了B树索引还有一个自适应哈希索引Adaptive Hash Index简称AHI它是MySQL根据热点数据自动维护的不由用户创建。当你频繁通过某个二级索引等值查询时InnoDB可能为该值在索引页上构建hash索引加速访问。但很多人不知道更新索引字段时AHI也要同步失效。因为AHI的key是索引键值你更新了索引键对应的hash条目里的指针就指向了旧索引记录必须删掉或更新。有意思的是InnoDB是通过在B树索引页上维护一个记录版本号来实现AHI失效的每次页面结构变化版本号就1AHI检查到版本号不一致就自动废弃相关条目。所以从内部视角看一次索引字段更新牵动的结构包括聚簇索引页、二级索引B树、AHI、change buffer、undo log、redo log、binlog。说一行update很轻量的人只是没站在数据库引擎的角度看问题。4. 为什么有时候更新字段索引没生效4.1 被各种索引失效规则淹没的真问题网上关于索引失效的总结非常多对索引列使用函数、隐式类型转换、左模糊、or连接、范围查询右侧失效等等。但回到标题更新字段会不会更新索引很多人有一个混淆更新字段和查询时索引失效是两个层面的问题。更新字段时索引一定会被维护这个不受查询优化器影响。而索引失效说的是查询阶段优化器因为某些原因不选择这个索引或者选择了但用不上。比如你对索引字段做WHERE name LIKE %zhang%B树的有序性帮不了你优化器就不走该索引或者走了也扫全量这是查询计划层面的表现跟索引本身有没有被更新没关系。所以在排查慢SQL时要分清楚我这条SQL慢是因为更新时索引维护太多还是因为查询时索引没被正确使用两者的优化方向完全不同。4.2 一张被频繁更新的索引字段会不会变成碎片这种问题在实际运维里很常见。比如一个订单表的status字段有索引业务里订单状态经常从待支付变成已支付一次状态流转就产生一次二级索引的删旧插新。当where条件里带上status时优化器评估索引区分度发现status分布极不均匀绝大部分订单已经是已支付那它的可选择性就很差走索引回表还不如直接全扫。这是统计信息层面的问题不是索引没更新。不过也别忽略真正的物理碎片索引页中大量被标记删除的记录空间没释放会导致索引页稀疏占用空间变大。解决办法是定期用OPTIMIZE TABLE重建表或通过ALTER TABLE ... ENGINEInnoDB做在线重建。但这句话说起来容易生产环境一个几亿行的表重建耗时和磁盘开销都要提前规划好。4.3 在线DDL和更新索引有什么关系MySQL 5.6以后ALTER TABLE ADD INDEX这类操作支持了在线DDL不会阻塞表的读写但不代表完全没有成本。你新增一个索引InnoDB要扫描全表把每个行的索引键值和主键值写入新的B树这个过程会占用IO和CPU且期间产生的新的数据变更需要记录到一种临时日志里最后应用到新索引上。这就是为什么有人会说我下午加了索引晚上线上业务变慢了。加索引虽然用的是online算法但在大表上仍然可能因为IO资源竞争导致读写放大。所以不要觉得更新字段会自动更新索引那我随手建一堆索引也没事。每个索引本身既是查询加速器也是更新开销放大器。4.4 计算一次实际开销50个索引的极端反例做个简单的乘法题。假如一张表上有10个二级索引你执行一条更新主键的语句MySQL需要操作的主体是1棵聚簇索引树删除再插入10棵二级索引树更新主键值每棵删旧插新各一次。如果这11棵树涉及到的页都不在内存里理论上最多要读11个页再写11个页。当然实际有change buffer优化不会全都同步IO但CPU和锁开销是省不掉的。如果同时有大量并发更新请求在相邻主键范围上还可能触发锁竞争和死锁检测开销。所以建索引的原则之一就是克制一个表的二级索引数控制在5个以内覆盖掉最核心的查询路径就够了。索引能提速的查询越多代价是每次写操作背的责任越大。这不是让你不建索引而是建的时候心里要有这本账。5. 更新字段时怎么判断到底走了哪些索引5.1 用EXPLAIN看的是查询不是更新一个常见的坑是有人用EXPLAIN SELECT的命中情况来推断update的索引维护路径。不对。EXPLAIN UPDATE可以提示更新时的扫描方式比如走主键还是走二级索引定位行但它不会告诉你本次更新会改动哪些索引树。判断哪些索引会被更新维护最直观的方法是看操作字段的索引配置用SHOW INDEX FROM user列出全部索引及索引列然后对照update语句里的SET部分。凡是出现在某个索引定义里的SET字段这个索引就躲不掉更新。主键字段出现在SET里则所有索引都躲不掉。5.2 怎么确认更新对索引的物理影响如果想知道一次更新是否真的在物理层面触发了二级索引页的变更从performance_schema里能找到路径。MySQL 8.0提供了performance_schema. table_io_waits_summary_by_table等表可以查看表的IO等待情况配合SHOW ENGINE INNODB STATUS观察PENDING WRITES的变化能间接确认写操作量级。更细腻的方法是开启innodb_buffer_pool_stats和全量performance_schema在低峰期用一个小事务更新单行看innodb_locks里出现了哪些索引锁记录。在可重复读隔离级别下UPDATE会锁定命中的记录及间隙你观察锁定对象时能看到聚簇索引记录锁和二级索引记录锁同时出现这也就是更新二级索引字段时的锁定特征。5.3 用binlog和主从复制反推更新影响有经验的DBA会从binlog的ROWS_EVENT里推测更新规模。在row格式下每行更新对应一个UPDATE_ROWS_EVENT里面含before image和after image两个镜像记录的是主键或唯一键定位列加所有业务字段。如果一条update通过二级索引定位并更新了同一索引字段binlog里记录的定位依据可能是主键而业务字段里能看到新旧值。主从复制时从库回放binlog会重新执行一遍在从库上的索引更新动作。所以如果你做了一个大规模update把主库的二级索引页改了从库上也会产生等量的索引维护。这就意味着一个索引数量很多的表在从库上做同样操作时从库的IO压力一样被放大。你对主库做优化时不要忽略从库的承接能力。6. 高频拷问主键索引和唯一索引的更新差别6.1 主键索引更新为什么默认不被鼓励主键是聚簇索引的根更新主键会引起整行数据在B树上的位置迁移同时所有二级索引里的主键值都要重写。这个操作的成本和风险成正比。生产上常见的主键类型有自增id和uuid。用uuid做主键插入时本来就是随机分布页分裂概率已经比自增id高再更新uuid的值那更是雪上加霜。我个人在建表时的原则是主键必须无业务含义、不可变、单调递增。自增id是默认选择分布式场景用雪花算法生成的bigint也没问题。凡是主键字段业务上可能需要改的表最好一开始就换方案不要等技术债累积成大表之后再来改主键那时候一条更新能把整个从库延迟拉高到分钟级。6.2 唯一索引的唯一在更新时是怎么保证的刚才提到唯一索引更新时要做唯一性检查。具体实现上InnoDB在插入新条目时先在B树上找到应该插入的位置然后检查相邻记录是否已经有相同键值。这个过程如果发现重复就返回Duplicate entry错误整个update语句报错回滚。这里有一个隐性成本容易被忽略即便是合法的唯一索引更新也会在相邻位置加锁防止并发事务同时插入重复值。这个插入意向锁记录锁的组合属于间隙锁范畴可能导致幻读防护相关的锁范围扩大。在高并发更新唯一索引字段的场景下容易出现锁等待超时。如果你在生产环境撞见过Lock wait timeout exceeded除了检查长事务也该看看是不是有大量并发在更新同一个唯一索引字段。6.3 二级索引做覆盖索引能不能减少更新开销覆盖索引指的是一个二级索引包含了查询需要的所有字段查询就不用回表。比如你有一个(name, age)的联合索引查询SELECT age FROM user WHERE name zhang时就能完全走这个索引不需要回表。但覆盖索引对于更新语句的开销没有任何帮助。你更新age时如果age是联合索引的第二列那么这条索引仍然要维护。更新时InnoDB不光要改聚簇索引里的age值还要对联合索引执行删旧插新。所以千万不要以为我用了覆盖索引更新时就能少动一棵树索引键只要被SET中的字段覆盖到就一样要动。7. 更新字段与索引维护常见问题与排障实录7.1 某次update突然变慢可能是因为页分裂有一次我在一个日志表上做状态批量更新平时单条update耗时不到1毫秒那天突然某个用户反馈响应超过2秒。排查后发现该表的status字段索引页刚好分裂而那次update恰好落在分裂边界附近插入时把页分裂的代价直接摊到了这次操作上。页分裂的代价在于要申请新页把原页一半的记录复制过去还要调整双向链表指针和父节点的路由信息。这个过程涉及多个页的写操作和锁操作比普通插入高一个量级。如果你的表写入量很大索引键随机性强比如UUID列上建索引页分裂就会非常频繁表现为无奈的随机卡顿。解决方案通常是把随机索引键改成顺序值或者调整索引页填充率相关参数。7.2 大批量更新索引字段时如何控制主从延迟比如你要把某个状态字段从0批量更新到1语句可能是UPDATE order SET status 1 WHERE status 0;如果这个表有5000万行status索引区分度不高这条update会扫描极大范围的数据行。每行都会引发一次聚簇索引更新和一次status二级索引的删旧插新。主库执行完了从库还要把同样的变更在binlog回放里再执行一遍。主库IO已经吃紧从库通常更扛不住。我踩过这个坑之后的处理办法是改成分批更新每批限制主键范围或者加上LIMIT配合循环执行批与批之间sleep几秒。另外提前把该表在从库上的SQL线程压力评估一下必要时延迟从库可以先暂停同步等批量更新结束再追。不要小看一个状态字段的批量更新它同样是索引更新放大效应的典型案例。7.3 常见问题速查表更新字段和索引的十个关键结论问题答案更新非索引字段会不会更新索引不会索引树形状不变更新二级索引字段会怎么样该索引树执行删旧插新标记删除旧条目并插入新条目字段值没变更新还走索引维护吗走InnoDB不判断新旧值是否相同更新主键会有什么后果聚簇索引树整行移动所有二级索引的主键值都要改更新唯一索引字段多做了什么多一次唯一性检查并伴随锁操作change buffer对更新的作用延缓二级索引页的物理更新减少随机IO为什么更新后有碎片索引页中大量被标记删除的记录空间未被及时清理加了覆盖索引能减少更新开销吗不能被SET覆盖的索引键照样需要维护更新走了二级索引定位行索引就一定会被改吗不一定取决于SET里是否有索引字段大量更新导致从库延迟分批更新降低单批事务量合理安排maintenance窗口7.4 工具与命令参考排查索引更新问题时我常用的命令有这些-- 查看表上的全部索引定义 SHOW INDEX FROM user; -- 查看当前缓冲区脏页数量 SHOW ENGINE INNODB STATUS\G -- 查看锁等待情况 SELECT * FROM sys.innodb_lock_waits; -- 查看表的统计信息 ANALYZE TABLE user;其中SHOW ENGINE INNODB STATUS的输出量很大重点看TRANSACTIONS段落里的锁信息以及INSERT BUFFER AND ADAPTIVE HASH INDEX段落的change buffer大小。还有一点8.0里SHOW ENGINE INNODB STATUS里有些信息被剥离到了information_schema.INNODB_METRICS如果工具不展示可以去那张表里找计数器。8. 基于执行计划的实践验证8.1 用一张示例表模拟几种update场景先建一张测试表包含主键、两个二级索引和一个无索引字段CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, score INT, KEY idx_name (name), KEY idx_age (age) ) ENGINEInnoDB;插入测试数据后分别执行三类updateUPDATE user SET score score 1 WHERE id 1; UPDATE user SET name new_name WHERE id 1; UPDATE user SET id 1000 WHERE id 1;第一类只动了score聚簇索引页的记录内容有更新但索引树排序不变第二类触发idx_name的删旧插新第三类主键变更idx_name和idx_age两个二级索引都要重写主键值。你可以打开general log或者看performance_schema.events_statements_history_long里每类语句的锁等待时间、扫描行数来做对比。8.2 从扫描行数看更新计划的差异在MySQL 8.0里执行EXPLAIN UPDATE user SET name x WHERE id 1通常看到type是const或eq_ref扫描行数为1。但如果WHERE name old_name并且name列上有索引explain可能会走idx_name进行索引范围扫描然后再更新聚簇索引记录。这里想提醒一个容易被误解的点explain显示的扫描行数和索引维护的行数不是一回事。哪怕update只命中1行只要SET里包含二级索引字段该索引树就要维护只是这个维护动作发生在索引内部不一定体现在查询计划里。自测时可以用SHOW STATUS LIKE Handler%的前后差值来感受一下索引操作量级。8.3 更新后为什么偶尔出现查询走错了索引频发更新索引字段后表的统计信息可能滞后。比如某字段之前区分度很高优化器一直用它某次大批量update把这个字段的大部分值改成了同一个值区分度急剧下降。如果没来得及重新收集统计信息优化器可能仍然依据过时的估算走这条索引大量回表反而变慢。对策是批量更新后执行ANALYZE TABLE让优化器拿到新的分布情况。这也能解释另一个现象为什么运维同学跑完大批量update之后线上查询反而变慢了。不一定是索引没更新而是统计信息失真导致执行计划劣化。索引本身还在忠实维护但还是那句话维护归维护优化器用不用是另一回事。9. 写代码时需要避开的几个坑9.1 别写出无意义更新很多人会在做最后修改时间字段更新时顺手把所有字段都带上UPDATE user SET name name, age age, update_time NOW() WHERE id 1;这在功能上没错但会让MySQL付出不必要的代价。name和age如果都有索引这条update即使值没变也会触发两棵二级索引的删旧插新。你只需要更新update_time就把它单独放SET里就好不要偷懒。9.2 注意联合索引里任何一个字段更新的传播效应联合索引(a, b)你更新b字段同样需要维护整棵索引树因为索引键值变了。哪怕a没变(a, old_b)和(a, new_b)在树里的位置也大概率不同。这也是很多开发容易忽略的以为我更新的不是联合索引的第一列可能索引不一定会变。错了联合索引的键是整体的(a,b)作为一个排序键任何一列变动都改变排序位置。真要测试可以在表上建一个(a, b)联合索引连续更新b用SHOW INDEX前后的statistics或者监控工具观察写入量你会发现它的维护频率和单列索引没有区别。9.3 事务里大批量更新索引字段还容易死锁事务A更新id1的记录的name字段事务B同时更新id2的记录的name字段如果两条记录的主键和二级索引分布不在同一个方向可能因为加锁顺序不一致产生死锁。InnoDB处理死锁的方式是回滚其中一个事务但回滚本身也要执行undo日志里的索引恢复操作代价更高。规避手段是把批量更新按主键排序后分段执行保证所有并发事务以相同的顺序加锁。尤其是批量更新索引字段的场景这个习惯能明显降低死锁概率。10. 实际操作中的个人体会做了这些年MySQL相关的工作我最深的感受是很多人学索引只看查询那一面忽略了写那一面。查询时索引是加速器写入时索引是负债。这话听着像废话但在真正的生产故障里因为更新触发索引维护导致慢SQL、锁等待、从库延迟的案例一点都不比查询没用上索引少。如果你平时用云数据库比如RDS或各种托管实例理解这些机制能帮你更准确地判断故障时间点。如果自己维护物理库或容器化部署的MySQL那更要理解缓冲池、change buffer和purge线程的工作原理。还有一点想重点提醒测试环境做索引更新实验和生产环境差异极大。测试库数据量小索引页全部落在缓冲池里更新性能看不出任何问题。生产环境数据量大很多索引页在磁盘上、在change buffer里没有合并同一条update的路径完全不同。所以不要用测试环境的速度去预估生产的性能。如果你对这块感兴趣建议自己动手做一轮对照实验建一张500万行的表分别测试更新非索引字段、更新二级索引字段、更新主键字段的耗时差异你会对更新字段会不会更新索引这个问题有非常具体的体感。实测数据往往比任何理论解释都更有说服力。