17370845950

索引在MySQL事务中是实时参与并被锁住的，所有增删改操作同步更新索引并写入redo/undo log；索引存在不等于被命中，隐式转换、函数、OR、LIKE '%xxx'等会导致全表扫描和大范围加锁；复合索引需遵循最左前缀原则，避免高频更新列置左；大事务批量更新应分批提交以减少锁竞争与索引碎片。

事务修改数据时，索引不是“旁观者”，而是被实时锁住的参与者

MySQL（InnoDB）中，事务对数据的增删改**必然同步更新所有相关索引**，哪怕事务尚未提交。这不是延迟操作，而是立即发生的物理变更：插入要往B+树里加节点，更新索引列要删旧项+插新项，删除则标记索引条目为待清除。这些变更写入redo log保证持久，同时记录在undo log支撑回滚——也就是说，索引结构在事务生命周期内已被占用、锁定、甚至分裂。

常见错误现象：UPDATE user SET status = 1 WHERE id = 123 执行慢、阻塞其他事务，但表很小、CPU不忙——大概率是id没建主键或索引，导致全表扫描+行锁升级为表级锁；或者status列上有索引却被高频更新，引发大量索引页重组织和锁竞争。

• 没有索引的WHERE条件 → InnoDB无法精确定位，只能扫全表并逐行加锁（record lock + gap lock组合成next-key lock），锁范围爆炸式扩大
• 唯一索引上的INSERT或UPDATE在REPEATABLE READ下会触发间隙锁，防止幻读，但也容易让并发插入卡在同一个间隙上
• 长事务持续持有索引页上的锁，其他事务想更新同一索引范围时，直接等待，而不是跳过——锁不是“查完才加”，是“一读就锁”

为什么“加了索引却还是锁表”？关键在查询是否真的走索引

索引存在 ≠ 查询命中索引。事务中一条SELECT ... FOR UPDATE或UPDATE若因隐式类型转换、函数包裹、OR条件、LIKE '%xxx'等导致索引失效，InnoDB就会退化为全表扫描，并对扫描路径上的每一页都加意向锁（IX），再对实际匹配行加行锁——此时锁范围远超预期，极易引发死锁或大面积阻塞。

实操建议：

• 用EXPLAIN FORMAT=TRADITIONAL确认事务内每条DML的type是否为const/ref/range，避免ALL或index
• 检查key列是否显示真实使用的索引名；若为NULL，说明没走索引
• 对ORDER BY或GROUP BY字段建索引时，注意filesort是否消失——它不直接影响锁，但延长执行时间，变相延长锁持有时间

复合索引设计不当，会让事务锁得更久、更宽

事务中频繁执行UPDATE orders SET paid_at = NOW() WHERE user_id = ? AND status = 'unpaid'，如果只在user_id上建单列索引，InnoDB仍需回表过滤status，加锁范围是所有该user_id的行；而建(user_id, status)复合索引后，索引本身就包含status值，能直接在二级索引页完成判断，不仅减少回表I/O，还让锁精准落在匹配的几行上。

容易踩的坑：

• 把高频更新列（如updated_at）放在复合索引最左位 → 每次更新都要调整整个索引树结构，写放大严重
• 忽略最左前缀原则：建了(a,b,c)却只查WHERE c = ?，索引完全失效
• 为覆盖查询建冗余索引（如已有(a,b)，又单独建b索引）→ 写入时多维护一份索引，事务开销翻倍

大事务批量更新索引列，等于主动制造索引碎片和锁风暴

一个事务里执行UPDATE products SET price = price * 1.1 WHERE category_id IN (1,2,3)，若category_id有索引但匹配数万行，InnoDB会在同一事务内反复分裂B+树页、写大量undo log、膨胀buffer pool中的脏页——其他事务访问同一索引范围时，轻则等待，重则触发Lock wait timeout exceeded错误。

正确做法：

• 拆成小批次，例如WHERE id BETWEEN 10000 AND 19999，每次提交，释放锁与资源
• 确保WHERE条件走的是**高区分度索引**（如主键），避免扫描大量无关索引页
• 非高峰时段执行，或临时调低隔离级别至READ COMMITTED（间隙锁关闭，但需业务接受幻读风险）

最常被忽略的一点：事务提交后，索引页的物理碎片不会自动整理。长期运行的系统里，SELECT COUNT(*)变慢、UPDATE响应抖动，往往不是SQL问题，而是索引页分裂+空洞堆积所致——定期用OPTIMIZE TABLE或ALTER TABLE ... FORCE重建表（注意锁表影响）仍是必要手段。