UPDATE 更新主键索引、辅助索引、联合索引时，数据到底怎么变？

面试指北 · 去哪儿

题意

这道题问的是：在 MySQL InnoDB 中执行一条 UPDATE 语句时，主键索引（聚簇索引）、辅助索引（二级索引）、联合索引内部的数据到底会怎么变化。

关键不在于 SQL 语法，而在于理解 InnoDB 的索引结构：

• 主键索引是聚簇索引，叶子节点存完整行数据；
• 辅助索引的叶子节点存“索引列 + 主键值”；
• 联合索引本质上也是辅助索引，只不过索引键由多列组成。

所以，更新不同字段时，触发的索引维护动作不同；更新主键的代价通常最大。

结论

先给结论：

1. 更新主键：聚簇索引中旧主键记录要删除/标记删除，再按新主键插入整行记录；同时所有辅助索引里保存的旧主键值都要同步改成新主键值，表现为“删除旧索引条目 + 插入新索引条目”。
2. 更新辅助索引字段：如果更新的是索引列，辅助索引要删除旧条目、插入新条目；如果更新的不是索引列，辅助索引不动，只改聚簇索引中的行数据。
3. 更新联合索引字段：只要更新了联合索引中的任意一列，整个联合索引条目的键值就变了，需要删除旧条目、插入新条目；不是只改其中某一列。
4. 成本排序：更新主键 > 更新带索引的字段 > 更新非索引字段。InnoDB 里删除往往是“标记删除”，配合 undo/MVCC，后续由 purge 清理，并不是立刻物理删除。

要点展开

1. 先理解 InnoDB 的索引长什么样

InnoDB 的 B+ 树索引是按索引键有序组织的，索引条目不是随便原地改一个字段就行；键值变了，通常意味着在 B+ 树中的位置也要变。

• 聚簇索引：按主键组织，叶子节点存完整行数据。找到主键，就找到了整行。
• 辅助索引/二级索引：叶子节点存索引列的值和主键值。通过辅助索引查到主键后，再回聚簇索引取完整行，这就是“回表”。
• 联合索引：叶子节点存多个索引列组成的键值，以及主键值。例如联合索引 (name, age)，叶子条目大致是 (name, age, 主键)。

这也解释了一个容易忽略的点：辅助索引条目里带着主键值。所以一旦主键变了，所有辅助索引里的主键值都要跟着变。

2. 更新主键索引时发生了什么

示例：

UPDATE users SET id = 10 WHERE id = 1;

这里假设 id 是主键，并且主键值确实从 1 变成了 10。InnoDB 的逻辑动作可以理解为：

1. 通过旧主键 1 定位到聚簇索引中的旧记录；
2. 将旧记录标记删除，并写入 undo 信息，供 MVCC 和回滚使用；
3. 对每一个辅助索引，把包含旧主键值的旧条目删除/标记删除；
4. 按新主键 10 插入一条新的聚簇索引记录，整行数据重新落到新位置；
5. 对每一个辅助索引，插入新的索引条目，并在条目中保存新主键值 10。

注意：即使某个辅助索引的索引列完全没变，只要主键变了，它也要维护。例如 name 上有辅助索引，旧条目是 ('Alice', 1)，更新主键后就要变成 ('Alice', 10)。索引列 name 没变，但条目里保存的主键值变了，所以仍然要删旧插新。

成本上，主键更新可能导致聚簇索引按新主键插入到不同位置，触发页分裂；所有辅助索引也要同步维护。因此主键更新通常代价较高，业务上应尽量使用稳定主键，避免 UPDATE 主键。

3. 更新辅助索引字段时发生了什么

示例：

UPDATE users SET name = 'Bob' WHERE id = 1;

分两种情况：

情况一：name 不是索引字段。

那么只需要修改聚簇索引叶子节点中的行数据，辅助索引不受影响。因为辅助索引里根本没有 name 这个键值，索引结构不需要变化。

情况二：name 是辅助索引字段。

辅助索引中原来有类似 ('Alice', 1) 的条目，更新后需要：

• 删除/标记删除旧条目 ('Alice', 1)；
• 插入新条目 ('Bob', 1)；
• 聚簇索引中的行数据也同步改成 name = 'Bob'。

这里的主键仍然是 1，所以辅助索引条目中的主键值不变，变的是索引列 name。

4. 更新联合索引字段时发生了什么

示例：

UPDATE users SET name = 'Bob', age = 30 WHERE id = 1;

假设存在联合索引 (name, age)。

旧联合索引条目可以理解为 ('Alice', 25, 1)，新条目是 ('Bob', 30, 1)。InnoDB 需要删除旧条目、插入新条目。

更关键的是：即使只更新联合索引中的一列，整个联合索引条目也要删旧插新。 比如只把 name 从 'Alice' 改成 'Bob'，联合索引条目也会从 ('Alice', 25, 1) 变成 ('Bob', 25, 1)。因为联合索引的键值整体变了，B+ 树中的位置也可能变化。

如果更新的是联合索引之外的字段，比如 email，并且 email 不在 (name, age) 中，那么联合索引不需要维护，只改聚簇索引行数据即可。

所以不要把“更新联合索引的部分字段”理解成“只改联合索引里的某一列”。从索引维护角度看，仍然是旧联合索引条目删除、新联合索引条目插入。

5. 整体行为对比

更新场景	聚簇索引	辅助索引/联合索引	成本
更新主键字段	旧主键记录删除/标记删除，按新主键插入整行	所有辅助索引都要删除旧条目，插入带新主键的新条目	最高
更新非索引字段	聚簇索引叶子中的行数据被修改	辅助索引、联合索引不动	较低
更新单列辅助索引字段	行数据被修改	该辅助索引删除旧条目，插入新条目	中等
更新联合索引中的部分字段	行数据被修改	该联合索引删除旧条目，插入新条目	中等偏高
更新联合索引中的全部字段	行数据被修改	仍然是一条旧条目删除、一条新条目插入	中等偏高，不要误解为多次维护

易错点与对比

1. “更新主键只影响主键索引”是错的。
   辅助索引叶子节点里保存了主键值，主键一变，所有辅助索引都要同步维护。

2. “更新辅助索引字段就是原地改索引值”不准确。
   B+ 树按键有序组织，键值变化通常要删除旧条目、插入新条目，可能触发页分裂。

3. “联合索引只更新一列，就只改那一列”是错的。
   联合索引是一个整体键值，任意一列变化，整个索引条目都要删旧插新。

4. “更新非索引字段完全没有成本”不对。
   它虽然不维护辅助索引，但仍然要修改聚簇索引中的行数据，涉及 undo、redo、锁等开销，只是比更新索引字段小。

5. “删除就是立刻物理删除”不准确。
   InnoDB 中常见的是 delete-mark，旧版本通过 undo 支持 MVCC，后续由 purge 线程清理。面试里说“删除旧条目、插入新条目”是逻辑层面的概括。

6. 主键更新和非主键更新要对比看。
   非主键字段更新，最多影响包含该字段的索引；主键更新则可能影响所有辅助索引，所以设计表时更强调主键稳定。

小结

InnoDB 中 UPDATE 的索引维护，核心看“更新的字段是否属于某个索引键”。更新非索引字段，只改聚簇索引行数据；更新辅助索引字段，删旧索引条目、插新索引条目；更新联合索引中任意一列，整个联合索引条目删旧插新；更新主键最特殊，因为辅助索引里存着主键值，所以所有辅助索引都要同步维护。实际设计中应尽量使用稳定主键，避免频繁更新带索引的字段，尤其是频繁变化的列不宜轻易放进联合索引。