MySQL更新索引列时索引树节点的更新机制问询

以下内容基于MySQL默认存储引擎InnoDB的B+树索引结构展开：

• 当更新操作同时满足以下两个条件时，会直接原地修改索引节点内容：
  • 更新后的索引键值排序位置未发生变化，无需调整B+树的有序结构
  • 对应索引页的剩余存储空间足够容纳更新后的数据
    比如对varchar(16)类型的索引列nickname，将原值"zhangsan123"修改为"zhangsan"，键值排序位置未变且修改后占用空间更小，就会直接原地更新。
• 当索引键值更新后排序位置发生变化，或原地修改会超出当前索引页的存储上限时，InnoDB会执行先删除旧索引节点、再插入新索引节点的操作，不会做原地更新。
  比如对int类型的二级索引列age，将原值18更新为40，两个值对应B+树的叶子节点大概率不在同一个数据页，排序位置完全偏移，此时就会先删除旧的索引条目，再插入新的索引条目。

额外补充：如果更新的是聚簇索引（主键索引）的键值，由于所有二级索引的叶子节点都存储了主键值，此时所有关联的二级索引都需要同步执行删除旧条目+插入新条目的操作，性能开销极高。这也是行业通用规范要求主键选择不可变字段或自增ID的核心原因之一。
对于非唯一二级索引的更新，InnoDB还会通过change buffer（变更缓冲）优化写入性能：如果要更新的索引页不在内存缓冲池中，会先把更新操作记录在变更缓冲，待后续该页被加载到内存时再合并执行，减少随机IO消耗。

内容的提问来源于stack exchange，提问作者Akhil Vasudevan