二级索引更新必须先锁索引再锁主键，因InnoDB二级索引叶节点仅存主键值，UPDATE需两阶段加锁：先在二级索引定位并加锁，再回表聚簇索引加载整行修改；该顺序由存储引擎强制，非优化器选择。
二级索引更新为什么必须先锁索引再锁主键
因为 InnoDB 的二级索引叶子节点只存主键值，不存完整行数据。所以任何通过二级索引定位的 UPDATE 都得走两步：先在二级索引上找到对应记录（加锁），再拿着主键去聚簇索引里捞出整行、修改、回写（再次加锁）。这不是优化器选择，而是存储引擎强制的两阶段加锁顺序。
- 事务 A 执行
UPDATE t SET status = 1 WHERE user_id = 123，命中idx_user_id→ 先锁idx_user_id上的索引项，再根据查到的主键去锁聚簇索引中对应行 - 事务 B 同时执行
UPDATE t SET name = 'x' WHERE status = 0，命中idx_status→ 先锁整个status = 0区间（含间隙锁），再逐个回表锁主键行 - 两个事务锁资源交叉、顺序相反，死锁风险直接拉满
二次IO开销具体体现在哪两次磁盘访问
不是“两次SQL”，而是同一次 UPDATE 内部触发的两次物理页加载：第一次读二级索引页（定位主键），第二次读聚簇索引页（加载原行数据用于修改）。如果二级索引和主键数据不在同一内存页、甚至不在同一磁盘区域，就会产生两次独立的随机IO。
- 二级索引页通常缓存在
innodb_buffer_pool，但若该页刚被刷出，就得从磁盘加载（一次随机IO） - 查出主键后，再去加载聚簇索引中对应行所在的页——若该页也不在缓冲池，又是一次随机IO
- 修改完成后，还要把这两个页都标记为 dirty，后续刷盘时可能再次触发写IO
- 尤其当
WHERE条件匹配多行（比如status = 0有上万行），这个“查索引→回表→改行”的循环会放大IO倍数
为什么非唯一二级索引会让问题更严重
非唯一索引触发的是 Next-Key Lock（临键锁），它同时锁住匹配行 + 行前间隙。这意味着即使你只更新一行，InnoDB 可能锁住一个区间内的多个主键值，导致回表操作批量发生，MRR 也救不了——因为 MRR 只对 range 和 ref 访问生效，而等值更新非唯一索引时，优化器常判定为 index 或 ALL 类型，直接跳过 MRR 路径。
MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
-
EXPLAIN UPDATE t SET x = 1 WHERE status = 0的type很可能是index，Extra是空或Using where，根本不会出现Using MRR - 此时所有匹配的主键都是乱序加载的，Handler_read_rnd_next 会飙升，
Handler_read_rnd却几乎为 0（说明 MRR 没启用） - 用
SELECT * FROM t WHERE status = 0 FOR UPDATE+SHOW ENGINE INNODB STATUS能看到实际锁住的 heap no 和 gap 范围，往往比你想的宽得多
真正有效的缓解手段不是调参，而是设计收敛
MRR 缓冲区调大、optimizer_switch 开关全开，对二级索引更新类语句基本无效。核心矛盾在于：非唯一二级索引天生携带间隙锁 + 回表不可免 + 加锁顺序固定。能动的只有 schema 和查询逻辑。
- 把高频等值更新的列设为
UNIQUE（哪怕业务上不绝对唯一，也可加WHERE deleted = 0等条件构造逻辑唯一性），让临键锁退化为行锁 - 确认该字段是否真需要支持
WHERE col = ?更新——如果只有几个固定值（如status IN (0,1,2)），直接建覆盖索引INDEX idx_status_cover (status, id, other_needed_cols)，避免回表 - 批量更新时改用主键列表驱动：
UPDATE t SET status = 1 WHERE id IN (123,456,789)，走聚簇索引，绕过二级索引锁竞争 - 删除无实际查询用途的二级索引：用
sys.schema_table_statistics查updates高但rows_affected在慢查询日志里几乎不出现的索引，直接DROP INDEX
二次IO本身不可消除，但它的放大效应完全可控。关键不是让每次更新更快，而是让每次更新锁得更少、波及更窄、路径更可预测。
