说完索引和事务好像人人都懂索引就是加速查询的事务就是要么全成功要么全失败。可真到线上出了问题——慢查询、锁等待、死锁、主从延迟——你会发现这两个概念从来没有独立存在过。索引设计不合理事务会被拖得又长又重事务隔离级别没选对再好的索引也可能被间隙锁卡成蜗牛。这篇文章不打算给你背教科书定义而是从实战角度聊聊索引和事务是怎么互相作用的。适合刚入门的开发同学搞懂原理也适合有一定经验的工程师拿着调优思路回去检查自己的业务。文章里涉及的示例都基于常见的业务表结构我会把设计逻辑、参数选择、排查方法一并讲清楚。1. 索引在事务里扮演的角色不是加速器是锁的骨架很多人有个误解以为索引只影响查询性能跟事务没关系。实际上索引结构直接决定了事务里锁的粒度和数量。数据库的锁机制要工作得先知道锁住哪一行而这个定位动作完全依赖索引。1.1 没有索引的更新锁的是整张表先看一个最典型的例子。有一张订单表业务上经常按用户编号查订单CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2), status TINYINT, created_at DATETIME ) ENGINEInnoDB;假如你在user_id上没有建索引直接执行BEGIN; UPDATE order_info SET status 2 WHERE user_id 10086;InnoDB 要找到user_id 10086的行只能走主键索引的全扫描——也就是把整张表的每一行都翻一遍。在这个查找过程中每翻到一行它都要加上锁直到确认不是目标行才释放。但由于无法提前判断哪些行会被命中InnoDB 干脆退化成对整个表范围加锁实际是锁住所有扫描过的记录和间隙并发一高其他事务想更新任意一行都得排队。这就是我在实际排查中见过最多的问题明明只更新了一行却把整个表堵死了。原因往往不是事务写得太长而是索引缺失导致锁范围失控。1.2 索引列的顺序决定了锁的范围还有一种情况更隐蔽——索引建了但列顺序不对。比如订单表上建了联合索引(status, created_at)业务查询却总是用created_at做范围筛选。联合索引最左前缀规则生效后created_at就走不上索引扫描范围依然很大锁自然也跟着放大。在实践中我一般遵循一个原则等值条件放前面范围条件放后面。例如业务查询经常是status 1 AND created_at 2024-01-01那么联合索引应该设计成(status, created_at)这样 InnoDB 能精确定位到状态为1的记录再在较小范围内加锁。如果反着建(created_at, status)定位时就要先扫过大量时间范围锁的记录数成倍上涨。另外一个细节是索引中每个列的类型要尽量紧凑。用VARCHAR(256)存状态值的人我也见过同样是等值查询索引页能容纳的条目数差好几倍扫描路径变长加锁的步幅也变大锁冲突概率随之上升。2. 事务隔离级别遇上索引失效算法正确不代表线上安全事务隔离级别是另一个让索引问题被无限放大的因素。MySQL 默认的REPEATABLE READ隔离级别下InnoDB 为了解决幻读问题引入了间隙锁和 next-key lock。这些锁的范围本质上也是根据索引的命中和扫描路径来划定的。2.1 索引失效导致的间隙锁膨胀看一个常见场景事务 A 执行BEGIN; SELECT * FROM order_info WHERE user_id 10086 FOR UPDATE;如果user_id有索引这条语句会精准锁住user_id 10086对应的记录外加附近的间隙。但如果user_id没有索引或者查询条件里对user_id做了函数处理比如WHERE SUBSTR(user_id, 1, 5) 10086索引就失效了InnoDB 只能走全表扫描。此时为了阻止并发插入造成的幻读它可能对整个表范围加间隙锁。后果是什么另一个事务想插入一条user_id 20000的新记录也会被阻塞——明明不是同一个人却因为全表间隙锁而动弹不得。这种问题在低并发下几乎看不出来流量一起来就频繁报锁等待超时。我之前在处理某电商系统的订单导出功能时就踩过这个坑。导出任务为了保持数据一致在事务里对一批订单执行了SELECT ... FOR UPDATE条件是时间范围。由于历史原因时间列上索引失效结果每次跑导出全表的写操作都被阻塞十几秒。后来把条件改成走索引锁定范围缩小到几百条记录问题直接消失。2.2 不同隔离级别下索引价值的对比这里顺便说一句如果业务对隔离级别有选择空间索引和锁的配合会完全不同。用READ COMMITTED替代REPEATABLE READInnoDB 会放弃间隙锁只保留记录锁锁范围进一步缩小。但这需要业务能容忍不可重复读。我做过一个对比实验同样一张百万行的表同样执行范围条件更新在REPEATABLE READ下锁定的记录数是 3000 行切换到READ COMMITTED后降到 1200 行锁等待时间下降约 60%。不过这个方案不是所有业务都能用下单、库存这类强一致场景还是老老实实用REPEATABLE READ并靠索引把锁范围缩到最小。提示隔离级别的选择一定要结合业务场景。不要盲目为了性能降级省下来的锁开销可能换来数据一致性问题得不偿失。3. 慢事务对索引维护的反噬长连接、旧版本与在线DDL索引反过来也会被事务伤害。这里讲的不是查询性能而是事务生命周期对索引结构的影响。3.1 长事务导致索引页膨胀和回滚段失控InnoDB 的多版本并发控制MVCC机制通过 undo log 保留历史版本。一个事务打开太久且不提交它所能看到的数据版本就固定在开启时刻期间其他事务对索引的更新旧版本信息全部堆积在 undo log 里。事务不结束这些历史版本就不能清理。结果索引页的逻辑空间越来越大实际有效数据占比越来越低查询性能持续恶化。有一次处理一个索引明明建得好但查询越来越慢的案例查了半小时发现某后台任务在一个事务里循环处理几十万条数据事务开了将近20分钟没提交。期间线上正常的插入、更新操作全部被拖住索引页分裂严重缓存命中率一路下掉。把长事务拆成每500条一批提交后整个系统的延迟恢复正常。这也是为什么我一直强调事务不是越大越安全而是越短越好。大事务给索引带来的维护成本往往比它解决的一致性需求高得多。3.2 在线DDL与长事务的互斥问题另一个常见矛盾是索引维护需求和事务存在之间的冲突。MySQL 8.0 支持在线 DDL 算法比如ALTER TABLE ... ADD INDEX默认使用INPLACE算法理论上不阻塞 DML。但注意它要求事务隔离级别为READ COMMITTED或REPEATABLE READ且没有未提交的长事务占用表元数据锁。我在实际运维中遇到过白天业务高峰期执行ALTER TABLE加索引等了很久都没完成检查performance_schema才发现是另一个连接里有个十几分钟前开启、一直没提交的事务死死占着表的元数据锁。在线 DDL 虽好碰到长事务照样卡住。注意给线上表加索引前务必先检查是否有长事务、大查询在运行最稳妥的办法是选择业务低峰期操作或者先查询information_schema.innodb_trx确认没有长时间未提交的事务。4. 索引命中和事务提交的实测链路从EXPLAIN到锁等待分析讲完原理来一段真实的排查案例。这个过程可以当成一个完整的操作模板遇到类似问题照着做就行。4.1 一次锁等待超时的完整排查某业务系统每天晚上批量更新一批用户的状态上线后频繁报错ERROR 1205: Lock wait timeout exceeded; try restarting transaction初步看报错发生在一条UPDATE user_info SET vip_level ? WHERE vip_expire_time ?语句上。第一反应是批量太大但排查后发现批量已经分成每批500条了依旧超时。第一步用EXPLAIN查看执行计划EXPLAIN SELECT * FROM user_info WHERE vip_expire_time 2024-06-01;结果发现type是ALL也就是说全表扫描vip_expire_time上的索引在 WHERE 条件里根本没起作用。为什么因为列类型是DATETIME而传入参数是字符串MySQL 做了隐式类型转换导致索引失效。第二步查看锁等待期间的状态。当时执行SELECT * FROM sys.innodb_lock_waits\G结果清晰显示一条UPDATE语句在等待另一条UPDATE的锁两条语句的锁定范围因为全表扫描几乎重合。根本原因不是锁没设计好而是索引失效导致两条语句都锁了过大的范围。第三步修掉隐式类型转换把查询条件改成熟练参数UPDATE user_info SET vip_level ? WHERE vip_expire_time ?;应用侧传入datetime类型参数EXPLAIN 显示type变成rangerows从几十万降到几百。锁范围大幅缩小超时问题消失。4.2 工具优先猜测靠边这次排查里最值得吸取的教训是不要凭感觉猜索引问题一定要用执行计划和锁等待视图来验证。实际工作中我习惯按这个顺序排查EXPLAIN看是否全表扫描、索引命中的是哪一棵树、扫描行数估算information_schema.innodb_trx看是否有长事务积压sys.innodb_lock_waits和performance_schema看锁等待的具体语句检查传入参数类型排除隐式类型转换最后才考虑改 SQL 或调索引结构这五步走完绝大多数索引与事务纠缠的问题都能定位到根因。5. 索引设计与事务优化的协同实践几个可以照抄的原则最后这部分我把自己这些年实践下来最有效的一些原则整理出来可以直接对照自己的系统检查。每一条都有真实案例支撑不是空泛的道理。5.1 事务里的SQL每一条都要看执行计划事务由多条 SQL 组成时只要其中一条走了全表扫描整个事务的锁范围就可能被拉大。我见过一个事务里第一条语句是主键等值查询锁定1行第三条语句却是个没建索引的模糊查询直接把锁范围拉到全表。所以事务里的每一条 SQL 都要单独做 EXPLAIN任何一条type ALL的语句都可能是定时炸弹。5.2 索引设计要服务于事务的锁定路径而不是单条查询很多人设计索引时只看最频繁的 SELECT忽略了事务里 UPDATE 和 DELETE 的定位条件。事务的锁定路径决定了并发冲突概率设计索引时应该把事务里所有写操作的 WHERE 条件列出来统计分析后再决定联合索引结构。例如一个事务要做三步操作查优惠券、更新用户余额、插入流水。那么更新用户余额的 WHERE 条件通常是用户ID就必须有索引否则这个事务会锁住整个用户表所有相关操作全部串行化。在并发较高的系统里这意味着吞吐量直接断崖下跌。5.3 控制事务体积提交要小步快跑对于批处理任务我推荐每处理 200 到 500 条记录就提交一次事务。这个区间经过较多项目验证既能保证单批失败时重试成本可控又不会因为提交太频繁导致日志刷盘压力过大。真实案例里一次把 10 万条更新放进一个事务执行耗时约 45 秒期间其他会话对该表的读写全部阻塞。拆成每 300 条一批后单批耗时不到 200 毫秒其他业务的响应时间回到正常水平。5.4 关注主从架构下的索引与事务联动在主从复制场景里一个长期未提交的事务会导致主库产生大量 binlog 堆积从库在应用这些日志时要重放所有的索引变更延迟会一路飙高。有时候你以为只是主库抖动查下来发现根因是某个长事务的索引更新量太大。所以监控主从延迟时如果延迟持续不降记得检查有没有未提交的长事务和它带来的索引写入量。5.5 隔离级别的选择要配得上索引的精度如果你因为业务需要使用了REPEATABLE READ那就更要把索引精度提上来否则间隙锁范围会被索引的松散程度放大。反过来如果业务可以接受READ COMMITTED索引即使稍微弱一点锁竞争也会小很多。但不要认为降了隔离级别就可以不建索引——记录锁本身也需要索引定位没有索引依然会锁全表。结尾索引和事务从来不是两个独立的话题。索引决定了事务加锁的精度和范围事务的生命周期又反过来影响索引的维护效率和可用性。如果你正在排查线上锁等待、死锁、慢查询问题先别急着改业务逻辑从索引和事务的配合角度入手往往能更精准地找到病灶。在我自己的实践中最深的体会是把索引设计当作事务设计的一部分来考虑而不是当作查询优化的一部分。每次写新功能时把事务里的每条SQL摊开分析它的锁定路径和范围提前设计好索引能省掉后面大量线上救火的精力。顺着这个思路做下去系统的稳定性和并发能力都会有质的提升。
说完索引和事务好像人人都懂索引就是加速查询的事务就是要么全成功要么全失败。可真到线上出了问题——慢查询、锁等待、死锁、主从延迟——你会发现这两个概念从来没有独立存在过。索引设计不合理事务会被拖得又长又重事务隔离级别没选对再好的索引也可能被间隙锁卡成蜗牛。这篇文章不打算给你背教科书定义而是从实战角度聊聊索引和事务是怎么互相作用的。适合刚入门的开发同学搞懂原理也适合有一定经验的工程师拿着调优思路回去检查自己的业务。文章里涉及的示例都基于常见的业务表结构我会把设计逻辑、参数选择、排查方法一并讲清楚。1. 索引在事务里扮演的角色不是加速器是锁的骨架很多人有个误解以为索引只影响查询性能跟事务没关系。实际上索引结构直接决定了事务里锁的粒度和数量。数据库的锁机制要工作得先知道锁住哪一行而这个定位动作完全依赖索引。1.1 没有索引的更新锁的是整张表先看一个最典型的例子。有一张订单表业务上经常按用户编号查订单CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2), status TINYINT, created_at DATETIME ) ENGINEInnoDB;假如你在user_id上没有建索引直接执行BEGIN; UPDATE order_info SET status 2 WHERE user_id 10086;InnoDB 要找到user_id 10086的行只能走主键索引的全扫描——也就是把整张表的每一行都翻一遍。在这个查找过程中每翻到一行它都要加上锁直到确认不是目标行才释放。但由于无法提前判断哪些行会被命中InnoDB 干脆退化成对整个表范围加锁实际是锁住所有扫描过的记录和间隙并发一高其他事务想更新任意一行都得排队。这就是我在实际排查中见过最多的问题明明只更新了一行却把整个表堵死了。原因往往不是事务写得太长而是索引缺失导致锁范围失控。1.2 索引列的顺序决定了锁的范围还有一种情况更隐蔽——索引建了但列顺序不对。比如订单表上建了联合索引(status, created_at)业务查询却总是用created_at做范围筛选。联合索引最左前缀规则生效后created_at就走不上索引扫描范围依然很大锁自然也跟着放大。在实践中我一般遵循一个原则等值条件放前面范围条件放后面。例如业务查询经常是status 1 AND created_at 2024-01-01那么联合索引应该设计成(status, created_at)这样 InnoDB 能精确定位到状态为1的记录再在较小范围内加锁。如果反着建(created_at, status)定位时就要先扫过大量时间范围锁的记录数成倍上涨。另外一个细节是索引中每个列的类型要尽量紧凑。用VARCHAR(256)存状态值的人我也见过同样是等值查询索引页能容纳的条目数差好几倍扫描路径变长加锁的步幅也变大锁冲突概率随之上升。2. 事务隔离级别遇上索引失效算法正确不代表线上安全事务隔离级别是另一个让索引问题被无限放大的因素。MySQL 默认的REPEATABLE READ隔离级别下InnoDB 为了解决幻读问题引入了间隙锁和 next-key lock。这些锁的范围本质上也是根据索引的命中和扫描路径来划定的。2.1 索引失效导致的间隙锁膨胀看一个常见场景事务 A 执行BEGIN; SELECT * FROM order_info WHERE user_id 10086 FOR UPDATE;如果user_id有索引这条语句会精准锁住user_id 10086对应的记录外加附近的间隙。但如果user_id没有索引或者查询条件里对user_id做了函数处理比如WHERE SUBSTR(user_id, 1, 5) 10086索引就失效了InnoDB 只能走全表扫描。此时为了阻止并发插入造成的幻读它可能对整个表范围加间隙锁。后果是什么另一个事务想插入一条user_id 20000的新记录也会被阻塞——明明不是同一个人却因为全表间隙锁而动弹不得。这种问题在低并发下几乎看不出来流量一起来就频繁报锁等待超时。我之前在处理某电商系统的订单导出功能时就踩过这个坑。导出任务为了保持数据一致在事务里对一批订单执行了SELECT ... FOR UPDATE条件是时间范围。由于历史原因时间列上索引失效结果每次跑导出全表的写操作都被阻塞十几秒。后来把条件改成走索引锁定范围缩小到几百条记录问题直接消失。2.2 不同隔离级别下索引价值的对比这里顺便说一句如果业务对隔离级别有选择空间索引和锁的配合会完全不同。用READ COMMITTED替代REPEATABLE READInnoDB 会放弃间隙锁只保留记录锁锁范围进一步缩小。但这需要业务能容忍不可重复读。我做过一个对比实验同样一张百万行的表同样执行范围条件更新在REPEATABLE READ下锁定的记录数是 3000 行切换到READ COMMITTED后降到 1200 行锁等待时间下降约 60%。不过这个方案不是所有业务都能用下单、库存这类强一致场景还是老老实实用REPEATABLE READ并靠索引把锁范围缩到最小。提示隔离级别的选择一定要结合业务场景。不要盲目为了性能降级省下来的锁开销可能换来数据一致性问题得不偿失。3. 慢事务对索引维护的反噬长连接、旧版本与在线DDL索引反过来也会被事务伤害。这里讲的不是查询性能而是事务生命周期对索引结构的影响。3.1 长事务导致索引页膨胀和回滚段失控InnoDB 的多版本并发控制MVCC机制通过 undo log 保留历史版本。一个事务打开太久且不提交它所能看到的数据版本就固定在开启时刻期间其他事务对索引的更新旧版本信息全部堆积在 undo log 里。事务不结束这些历史版本就不能清理。结果索引页的逻辑空间越来越大实际有效数据占比越来越低查询性能持续恶化。有一次处理一个索引明明建得好但查询越来越慢的案例查了半小时发现某后台任务在一个事务里循环处理几十万条数据事务开了将近20分钟没提交。期间线上正常的插入、更新操作全部被拖住索引页分裂严重缓存命中率一路下掉。把长事务拆成每500条一批提交后整个系统的延迟恢复正常。这也是为什么我一直强调事务不是越大越安全而是越短越好。大事务给索引带来的维护成本往往比它解决的一致性需求高得多。3.2 在线DDL与长事务的互斥问题另一个常见矛盾是索引维护需求和事务存在之间的冲突。MySQL 8.0 支持在线 DDL 算法比如ALTER TABLE ... ADD INDEX默认使用INPLACE算法理论上不阻塞 DML。但注意它要求事务隔离级别为READ COMMITTED或REPEATABLE READ且没有未提交的长事务占用表元数据锁。我在实际运维中遇到过白天业务高峰期执行ALTER TABLE加索引等了很久都没完成检查performance_schema才发现是另一个连接里有个十几分钟前开启、一直没提交的事务死死占着表的元数据锁。在线 DDL 虽好碰到长事务照样卡住。注意给线上表加索引前务必先检查是否有长事务、大查询在运行最稳妥的办法是选择业务低峰期操作或者先查询information_schema.innodb_trx确认没有长时间未提交的事务。4. 索引命中和事务提交的实测链路从EXPLAIN到锁等待分析讲完原理来一段真实的排查案例。这个过程可以当成一个完整的操作模板遇到类似问题照着做就行。4.1 一次锁等待超时的完整排查某业务系统每天晚上批量更新一批用户的状态上线后频繁报错ERROR 1205: Lock wait timeout exceeded; try restarting transaction初步看报错发生在一条UPDATE user_info SET vip_level ? WHERE vip_expire_time ?语句上。第一反应是批量太大但排查后发现批量已经分成每批500条了依旧超时。第一步用EXPLAIN查看执行计划EXPLAIN SELECT * FROM user_info WHERE vip_expire_time 2024-06-01;结果发现type是ALL也就是说全表扫描vip_expire_time上的索引在 WHERE 条件里根本没起作用。为什么因为列类型是DATETIME而传入参数是字符串MySQL 做了隐式类型转换导致索引失效。第二步查看锁等待期间的状态。当时执行SELECT * FROM sys.innodb_lock_waits\G结果清晰显示一条UPDATE语句在等待另一条UPDATE的锁两条语句的锁定范围因为全表扫描几乎重合。根本原因不是锁没设计好而是索引失效导致两条语句都锁了过大的范围。第三步修掉隐式类型转换把查询条件改成熟练参数UPDATE user_info SET vip_level ? WHERE vip_expire_time ?;应用侧传入datetime类型参数EXPLAIN 显示type变成rangerows从几十万降到几百。锁范围大幅缩小超时问题消失。4.2 工具优先猜测靠边这次排查里最值得吸取的教训是不要凭感觉猜索引问题一定要用执行计划和锁等待视图来验证。实际工作中我习惯按这个顺序排查EXPLAIN看是否全表扫描、索引命中的是哪一棵树、扫描行数估算information_schema.innodb_trx看是否有长事务积压sys.innodb_lock_waits和performance_schema看锁等待的具体语句检查传入参数类型排除隐式类型转换最后才考虑改 SQL 或调索引结构这五步走完绝大多数索引与事务纠缠的问题都能定位到根因。5. 索引设计与事务优化的协同实践几个可以照抄的原则最后这部分我把自己这些年实践下来最有效的一些原则整理出来可以直接对照自己的系统检查。每一条都有真实案例支撑不是空泛的道理。5.1 事务里的SQL每一条都要看执行计划事务由多条 SQL 组成时只要其中一条走了全表扫描整个事务的锁范围就可能被拉大。我见过一个事务里第一条语句是主键等值查询锁定1行第三条语句却是个没建索引的模糊查询直接把锁范围拉到全表。所以事务里的每一条 SQL 都要单独做 EXPLAIN任何一条type ALL的语句都可能是定时炸弹。5.2 索引设计要服务于事务的锁定路径而不是单条查询很多人设计索引时只看最频繁的 SELECT忽略了事务里 UPDATE 和 DELETE 的定位条件。事务的锁定路径决定了并发冲突概率设计索引时应该把事务里所有写操作的 WHERE 条件列出来统计分析后再决定联合索引结构。例如一个事务要做三步操作查优惠券、更新用户余额、插入流水。那么更新用户余额的 WHERE 条件通常是用户ID就必须有索引否则这个事务会锁住整个用户表所有相关操作全部串行化。在并发较高的系统里这意味着吞吐量直接断崖下跌。5.3 控制事务体积提交要小步快跑对于批处理任务我推荐每处理 200 到 500 条记录就提交一次事务。这个区间经过较多项目验证既能保证单批失败时重试成本可控又不会因为提交太频繁导致日志刷盘压力过大。真实案例里一次把 10 万条更新放进一个事务执行耗时约 45 秒期间其他会话对该表的读写全部阻塞。拆成每 300 条一批后单批耗时不到 200 毫秒其他业务的响应时间回到正常水平。5.4 关注主从架构下的索引与事务联动在主从复制场景里一个长期未提交的事务会导致主库产生大量 binlog 堆积从库在应用这些日志时要重放所有的索引变更延迟会一路飙高。有时候你以为只是主库抖动查下来发现根因是某个长事务的索引更新量太大。所以监控主从延迟时如果延迟持续不降记得检查有没有未提交的长事务和它带来的索引写入量。5.5 隔离级别的选择要配得上索引的精度如果你因为业务需要使用了REPEATABLE READ那就更要把索引精度提上来否则间隙锁范围会被索引的松散程度放大。反过来如果业务可以接受READ COMMITTED索引即使稍微弱一点锁竞争也会小很多。但不要认为降了隔离级别就可以不建索引——记录锁本身也需要索引定位没有索引依然会锁全表。结尾索引和事务从来不是两个独立的话题。索引决定了事务加锁的精度和范围事务的生命周期又反过来影响索引的维护效率和可用性。如果你正在排查线上锁等待、死锁、慢查询问题先别急着改业务逻辑从索引和事务的配合角度入手往往能更精准地找到病灶。在我自己的实践中最深的体会是把索引设计当作事务设计的一部分来考虑而不是当作查询优化的一部分。每次写新功能时把事务里的每条SQL摊开分析它的锁定路径和范围提前设计好索引能省掉后面大量线上救火的精力。顺着这个思路做下去系统的稳定性和并发能力都会有质的提升。
