1. 把事务和索引拆开看它们到底在解决什么问题先讲个我在实际项目中遇到的场景。去年帮朋友排查一个电商后台的订单接口用户下单后页面一直转圈数据库CPU直接飙到100%。查了半天发现是两个程序员写代码时对同一张订单表做了不同操作一个在事务里先更新订单状态再扣库存另一个反过来先扣库存再更新状态两边互相等对方释放锁直接死锁。业务量一大整个下单接口就卡死了。这类问题的解决思路就是事务和索引这两样东西。很多刚接触MySQL的朋友总把它们当成两个独立的知识点来背背完就忘遇到问题依然抓瞎。实际上事务和索引是一对配合紧密的机制事务管的是“多条SQL要么全成功、要么全失败”的原子性以及并发环境下彼此隔离不乱套索引管的是“数据能不能被快速找到”它决定SQL执行时是一行行扫全表还是直接定位目标也决定事务加锁时锁住的是一行还是一个表。用个生活化的类比。事务就好比汽车的安全带解决的是“万一出事人别飞出去”的问题索引就是发动机里的涡轮增压解决的是“车能不能跑得快”的问题。安全带不让你开得更快但能保命涡轮不保命但能让你超车时不憋屈。数据库里如果只有事务没有索引并发安全是保证了但每条SQL都全表扫描遇到几百万行的表一条更新语句能把数据库拖到罢工只有索引没有事务单条SQL是快了可一旦中途出错数据说丢就丢业务逻辑根本没法叠上去。这篇文章不打算按教科书那样从存储引擎结构讲起而是用“安全带”和“加速器”这两个视角把事务的隔离级别、锁机制、MVCC和索引的数据结构、建索引原则、执行计划分析串起来全程用大家熟悉的“订单系统”“用户表”“商品库存”这类业务场景举例每一步对应到真实SQL命令和排查方法。适合正在学MySQL的学生、刚转行做后端开发的初级工程师以及写了好几年业务代码但一直没系统捋过数据库底层机制的开发朋友。需要说明的是我讲的都是基于MySQL 8.x版本InnoDB存储引擎。这是目前最主流的组合也是你生产环境大概率在用的组合。MyISAM那种老古董不支持事务不在讨论范围内。2. 事务这条“安全带”原理、隔离级别和实战写法2.1 事务的ACDI四个核心性质逐个说透先说一个最容易被忽略的事实MySQL里每条单独的SQL语句默认就是一个事务自动提交autocommit默认是开启的。你执行一条UPDATEMySQL自动帮你在语句前后套上了BEGIN和COMMIT。所以日常开发里感觉不到事务的存在只有当你手动写BEGIN…COMMIT多语句操作时才真正进入事务管理的范畴。事务的核心是ACID四个特性教科书通常按“原子性、一致性、隔离性、持久性”来排但实际排查问题时我更倾向于按“一致性是目标其他三个是手段”来理解。原子性Atomicity最直观就是一组SQL要么全部成功要么全部回滚。比如转账扣款成功入账失败整个操作必须撤销。MySQL用undo log实现这个能力执行事务时记录操作前的数据快照回滚时按快照恢复原始状态。这个机制在内部叫“回滚日志”和我后面讲的MVCC强相关。一致性Consistency说的是事务执行前后数据必须始终满足业务规则和约束比如订单金额不能为负、库存不能超卖。这是业务层的概念数据库只能提供约束工具真正的一致性逻辑要开发者在事务里写对。比如扣库存时必须加条件“库存 0才update”如果忘了这个条件两个并发事务就可能同时读到库存为1各自扣成0超卖问题由此而来。数据库层面无法替你判断这种业务一致性。隔离性Isolation解决并发问题。多个事务同时读写同一行数据时可能会出现脏读读到别人未提交的数据、不可重复读同一条记录两次读值不同、幻读同样的查询条件两次查出不同条数。隔离性允许事务互相“假装看不见对方”程度越严格数据越安全但并发性能越低。持久性Durability最简单事务提交后数据不能丢。MySQL用redo log实现提交事务时先写重做日志到磁盘再异步刷新数据页。崩溃恢复时会重放redo log保证已经提交的事务不丢失。2.2 四个隔离级别到底怎么选从读未提交到串行化MySQL的InnoDB支持四个隔离级别从松到严分别是读未提交、读已提交、可重复读、串行化。默认是第三个“可重复读”这个选择本身就有讲究。读未提交READ UNCOMMITTED基本没人用因为允许读取其他事务未提交的数据脏读风险太高。说实话我在生产环境从没见人用过。读已提交READ COMMITTED是Oracle和SQL Server的默认级别解决脏读但存在不可重复读同一条记录事务A先读是100事务B修改成200并提交事务A再读就是200。对大多数业务来说这个级别够用且由于锁范围小并发性能通常比可重复读更好。MySQL 8.x也支持阿里云等一些云数据库甚至推荐生产环境用这个级别来降低死锁概率。可重复读REPEATABLE READ是MySQL的默认级别它通过MVCC保证事务内多次读取同一行结果一致。同时InnoDB在可重复读级别下还解决了一个标准SQL里没解决的问题——幻读用的是间隙锁MVCC的组合后面我会细说。串行化SERIALIZABLE最安全事务完全排队执行等于把并发变成了串行但性能损失极大。除非是资金结算那种低并发高安全场景否则慎用。怎么选没有绝对标准我的经验是内部管理系统、报表查询这类场景保持默认的可重复读即可高并发的交易类系统可以考虑读已提交但前提是团队对并发冲突有充分的测试覆盖。改隔离级别的方式是-- 查看当前会话级别 SELECT transaction_isolation; -- 设置会话级别仅对当前连接生效 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置全局级别新连接生效 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;2.3 手把手写一个标准事务五个关键步骤实际开发中建议用显式事务而不是依赖自动提交。写事务的标准姿势分五步开启事务、执行业务SQL、检查结果、提交或回滚、处理异常。以经典的“订单扣库存”为例完整逻辑是-- 步骤1开启事务 START TRANSACTION; -- 步骤2执行核心操作先锁定库存行 UPDATE inventory SET stock stock - 1 WHERE sku_id A001 AND stock 0; -- 步骤3检查上一步影响行数。如果影响行数为0说明库存不足直接回滚 -- 这一步在应用层判断if (rowsAffected 0) { rollback(); } -- 步骤4插入订单记录 INSERT INTO orders (order_no, sku_id, quantity, amount) VALUES (20250201001, A001, 1, 99.00); -- 步骤5提交 COMMIT;这段代码最关键的细节是AND stock 0。很多学生和初级开发容易漏掉这个条件只写UPDATE inventory SET stock stock - 1 WHERE sku_id A001。没有这个条件时两个并发请求同时读到库存为0各自执行更新库存变成-1超卖问题发生。有了条件行锁生效时第二个事务会等待第一个事务提交然后重新评估条件发现stock已经是0影响行数为0从而回滚。在代码层面Java里用Spring的Transactional注解时也容易犯一个错误事务里调用self.invoke()之类的方法导致注解失效。因为Spring的事务代理默认只对通过代理对象调用的方法生效类内部直接调用绕过代理事务就不会启动。2.4 事务日志满、长事务和死锁三个新手必踩的坑热搜词里有条特别真实的报错“数据库的事务日志已满”。这个报错在MySQL 8.x里常见于一个场景你执行了一个特别大的事务比如一次性往几百个表里插入几百万行数据或者循环几万次执行UPDATE但没及时提交。InnoDB收到“事务日志已满”通常不是指磁盘空间真的满了而是指事务占用的undo log和redo log超过了限制或者磁盘空间不足导致无法扩展日志文件。解决思路分三层第一层确认磁盘空间df -h看一眼磁盘是否满了第二层查是否有未提交的超长事务SELECT * FROM information_schema.innodb_trx\G有就把对应进程KILL掉第三层如果业务确实需要大批量操作拆分成小事务分批提交比如每次commit 1000条。死锁问题是事务并发的经典难题。我先说结论死锁不是靠配置解决的而是靠排查和预防。MySQL检测到死锁会自动回滚其中一个事务你会在应用日志里看到“Deadlock found when trying to get lock”。排查思路是开启死锁日志并分析-- 查看最近的死锁信息 SHOW ENGINE INNODB STATUS\G重点关注日志里“LATEST DETECTED DEADLOCK”部分它会打印两个事务各自的SQL和持有的锁。绝大多数死锁的根因是事务以不同顺序访问资源。比如事务A先更新order表再更新inventory表事务B先更新inventory表再更新order表两者在交集处互相等待。解决方式是制定一个全局统一的访问顺序所有事务都按“先order后inventory”的顺序操作死锁基本消失。还有一个体验相关的问题长事务。一个事务开着迟迟不提交会导致两件事一是它持有的锁一直不释放其他事务被卡住接口越来越慢二是MVCC需要保留该事务开始前的老版本数据undo log膨胀表空间剧增查询也可能变慢。排查方式是在information_schema.innodb_trx里查长事务的trx_started字段再配合performance_schema.events_statements_current看它正在执行的SQL。生产经验是合理设置innodb_lock_wait_timeout默认50秒可调到30秒甚至更低避免一个事务把全链路拖死。3. 索引这条“加速器”B树原理与建索引的底层逻辑3.1 索引为什么快从B树说起索引的本质是一种数据结构把“值”映射到“数据行的物理位置”。MySQL InnoDB的索引底层是B树理解B树的三个特点就足够用数据只存在叶子节点、非叶子节点只存键值用于路由、叶子节点用双向链表串起来。这三个特性决定了查询路径比如你要查“用户名为zhangsan”的记录如果没有索引InnoDB只能按主键顺序把整张表的每一行都读一遍这叫全表扫描复杂度O(N)。有了索引从根节点开始每次二分查找一层层往下复杂度变成O(logN)。一张百万行记录的表全表扫描可能要读十万个数据页走索引可能只需要读三四个数据页这就是索引最核心的加速原理。还有个细节你可能没注意InnoDB的主键索引就是数据本身也叫聚簇索引。表数据按主键顺序物理存储叶子节点直接保存整行记录。其他索引叫二级索引叶子节点保存的是“索引列的值对应的主键值”。所以当你用二级索引查数据时会先查到主键值再通过主键回到聚簇索引里取整行数据这叫回表。回表有额外开销减少回表次数是我后面要讲的覆盖索引的核心动机。3.2 聚簇索引、二级索引和联合索引怎么建才对建索引前先搞清楚索引的类型和适用场景。我见过太多人不管三七二十一给每个字段都建一个索引结果索引比数据还大写入性能差到离谱。建索引的核心原则就一句话索引设计必须跟着SQL查询模式走不是跟着表结构走。单列索引最基础适合查询条件只有一个字段的场景。比如用户表经常按手机号查用户那就在手机号列建普通二级索引ALTER TABLE users ADD INDEX idx_phone (phone);联合索引也叫复合索引是两个或多个字段组合成一个索引它遵循“最左前缀原则”查询条件必须从联合索引的最左列开始才能用上该索引。举例来说如果建了(a, b)联合索引那么WHERE a 1和WHERE a 1 AND b 2都能命中索引但只看WHERE b 2是无法命中索引的。我在实际面试中经常考这个点还真是很多人答不清楚。所以联合索引的建法有讲究第一把等值查询的字段放最左边第二把区分度高的字段放前面第三考虑范围查询字段放最后。比如订单表最常用的查询是“按用户ID查订单再按时间排序”那就该建(user_id, order_time)联合索引既能加速查用户订单又能让排序直接走索引避免额外的filesort操作。3.3 一张表到底建几个索引合适数量控制的建议关于索引数量不少入门者的理解是“越多越好”。真实情况是每个索引在插入、更新、删除时都要同步维护B树节点可能分裂、合并写性能会打折索引还占磁盘空间缓存池也放不下那么多索引页。我的经验守则是单表索引数量一般控制在5个以内单列索引能合并成联合索引的尽量用联合索引替代经常不用的索引要定期清理。判断索引是否被使用可以通过MySQL的索引统计信息来看-- 查看表索引 SHOW INDEX FROM orders; -- 查看各索引的使用频率 SELECT * FROM sys.schema_unused_indexes;第二步那个视图会直接列出从未被使用的索引非常实用。前阵子帮一个项目做优化发现一张表有8个索引其中3个从上线以来从未命中过直接删掉后写入性能提升了约18%。3.4 索引失效的六种典型场景照单自查建了索引不代表SQL一定会走索引。我总结过新手最常踩的六种索引失效场景建议背下来第一对索引列做函数计算或隐式类型转换。比如WHERE DATE(create_time) 2025-02-01因为对列做了函数操作索引直接失效。正确做法是WHERE create_time 2025-02-01 AND create_time 2025-02-02。另外如果索引列是字符串类型查询条件传数字MySQL会做隐式类型转换同样导致索引失效。第二前导模糊查询。比如WHERE name LIKE %张%由于左模糊不确定起点B树无法按顺序查找。如果业务必须支持考虑全文索引或者ES这类搜索中间件。第三联合查询不是左前缀。上面说的最左前缀原则一旦查询条件没有从联合索引的第一列开始索引无法命中。第四查询条件里对索引列做表达式运算比如WHERE age 1 30。这个和第一条类似索引优化器无法反向推导干脆放弃索引。把表达式改写为WHERE age 29就行。第五OR连接非索引列。比如WHERE id 1 OR status closed如果status没有索引优化器可能选择全表扫描因为要同时满足两边的取数逻辑。一个折中方案是把OR改写为UNION让两段分别使用各自的索引。第六NOT IN、!等否定条件。这类条件通常导致优化器认为扫描大部分行不划算从而放弃索引。如果业务必须用通常会把否定条件拆出来结合数据和范围再评估。排查索引是否生效的利器是EXPLAIN我下面专门展开。3.5 用EXPLAIN看懂执行计划三个必看字段遇到慢SQL时第一件事永远是丢一个EXPLAIN上去。我给团队定的规矩是上线前所有涉及查询的SQL必须过EXPLAIN重点看三个字段。type字段是访问类型的口诀从好到差依次是consteq_refrefrangeindexALL。ALL就是全表扫描慢SQL的头号元凶index是扫描全部索引树比全表扫描好一点但也不理想range是范围扫描比如IN、BETWEEN、LIKE右模糊后就是range可以接受ref是普通等值匹配eq_ref是联表查询时用了主键或唯一索引表现很好const是通过主键或唯一索引直接定位到一行最优。key字段显示实际用到的索引名possible_keys显示可能用到的索引两者对照能看出优化器有没有选错索引。如果possible_keys不为空但key为空说明索引没被选中多半是上一节讲的失效场景之一。rows是预测扫描的行数它不是真实行数是优化器估算的。这个数字越大说明成本越高结合Extra字段里的Using filesort、Using temporary可以快速判断排序是否为额外开销。举个实际的例子一条慢查询的EXPLAIN结果type ALL possible_keys idx_user_id key NULL rows 1200000 Extra Using wheretypeALL加上keyNULL说明虽然有用户ID索引但这条SQL没有走120万行全表扫了。回头检查SQL发现是WHERE user_id ? AND create_date BETWEEN ? AND ?但联合索引建的是(create_date, user_id)最左前缀不满足索引失效。改成(user_id, create_date)联合索引后type变rangerows降到几千响应时间从2.3秒降到30毫秒。4. 事务和索引怎么配合锁、MVCC和性能之间的博弈4.1 为什么事务的隔离级别不等同于加锁强度很多人觉得事务的隔离级别越严加的锁就一定越多其实是个常见误区。可重复读和读已提交这两个级别下普通查询SELECT走的是MVCC快照读根本不加锁。真正的锁发生在UPDATE、DELETE、INSERT这类写操作以及你手动加FOR UPDATE和LOCK IN SHARE MODE的SELECT上。MVCC的全称是多版本并发控制核心机制是每行记录在更新时产生新版本旧版本通过undo log保留。事务执行普通SELECT时根据事务开始时的视图read view读取“当时已提交”的某个版本而不是读取最新数据。这就是可重复读的本质同一个事务内看到的快照是固定的第二次读到的还是第一次读时的旧版本所以别人提交的新数据被“看不见”。因为读的是快照不需要加锁所以并发读的性能几乎不损失这就是为什么MySQL能在可重复读级别下还保持不错的并发性能。但加了对“幻读”的解释就需要引入锁了。可重复读下事务执行SELECT * FROM orders WHERE status pending FOR UPDATE时InnoDB除了锁住满足条件的所有记录还会在记录之间的间隙加一个间隙锁gap lock禁止其他事务在范围内插入新记录。间隙锁和它前面那条记录上的行锁合起来叫next-key lock。所以幻读在可重复读级别下其实是靠“行锁间隙锁”共同封堵住的这也是和Oracle的区别Oracle在可重复读下仍可能幻读MySQL直接用锁把这个口子堵死了。4.2 索引直接影响锁的粒度建错索引锁全表这一点我觉得值得单独拿出来强调因为它把事务和索引串起来了InnoDB的锁是建立在索引记录上的索引的粒度决定了锁的粒度。走主键索引更新一行通常只锁那一行走了辅助索引更新若干行会锁命中行以及必要的间隙如果查询条件无法命中任何索引InnoDB只能全表扫描找目标行那么它在扫描过程中会把扫过的每一行都加锁最终表现为锁定了整个表。想象一下线上有个千万级订单表一个UPDATE语句的WHERE条件没走索引执行时就要锁住扫过的所有记录期间所有其他事务对这个表的写操作哪怕是一行也得排队。更麻烦的是很多ORM自动生成的SQL并不会实际命中你以为的索引所以上线前用EXPLAIN确认每条写SQL的访问路径是必修课。4.3 如何利用索引优化事务性能三点实用经验第一点是让写事务的WHERE条件尽可能命中唯一或精确索引减少锁的行数。比如批量更新时UPDATE orders SET status paid WHERE order_no IN (...)只要order_no有唯一索引锁的就只有那几行没有索引就直接升级成全表锁。第二点是事务里尽量把读取操作放前面写操作放后面。因为锁通常在执行写操作时才获取先读后写可以让锁持有时间尽可能短。事务持有锁的时间越短其他事务等待的时间越短整个库的并发能力就越好。第三点是长事务配合索引查询也可能导致undo log膨胀。前面说过长事务会保持老版本数据索引数据页里可能有多个版本的记录查询要跳更多的版本链整体变慢。给长事务设置超时提醒很有必要我一般用如下脚本定期检查活动事务数量和时间超过30秒的事务自动告警SELECT trx_id, trx_started, trx_query, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 30;4.4 案例复盘索引缺失导致的分布式事务僵局聊一个朋友公司发生的真实故障很能说明问题。他们的订货系统改造成微服务后订单服务和库存服务分开部署采用分布式事务方案。某个凌晨大促下单接口突然大面积超时监控显示数据库活跃连接数打满。排查过程先看的APM链路发现是库存服务的数据库update等待锁超时。再查innodb_trx发现一个已运行20多分钟的订单事务持有库存表某行的锁不放。链路日志显示它卡在下游的库存回写接口上而回写接口的SQL长这样UPDATE inventory SET stock stock - 1 WHERE product_id ? AND warehouse_id ?当时这张表300万行但(product_id, warehouse_id)上没有联合索引只有product_id单列索引。MySQL查出大量目标行锁了超大范围加上大促并发高多个事务互相等待锁等待雪崩。修复方案其实不复杂给(product_id, warehouse_id)建联合索引锁的范围从几百行缩到一行同时把分布式事务中不必要的数据库事务范围缩小只把真正的写操作包在事务里之前因为建房失误把远程调用如发MQ、调用外部API也放进数据库事务一把梭拖大了锁持有时间。恢复后接口超时率从18%降到0.3%。这个案例说明一个问题分布式事务的一致性不由MySQL单独决定但MySQL事务提供的锁和隔离能力是地基索引不好地基上再花哨的分布式方案也白搭。5. 新手必看从建表到优化的完整实操清单5.1 建表时就要考虑索引和事务的配合很多朋友建表时随手写完字段就上线等慢查询出现才回头补索引这个习惯很不好。我建议建表时遵循几条硬约束必须有主键且主键尽量用自增整数或雪花生成的趋势递增整数不要用UUID这种无序字符串因为无序主键会导致B树频繁页分裂写入性能下降明显。字段类型尽量精炼。能TINYINT不INT能VARCHAR(32)不VARCHAR(255)越短的字段建索引后索引树越矮查询越快。日期字段必须用DATETIME或TIMESTAMP别用VARCHAR存日期否则范围查询没法利用索引。如果明确某列将来会被频繁作为查询条件比如手机号、用户ID、订单号、状态和时间组合建表阶段就加上合适索引。等到上线后数据多了再补索引虽然MySQL 8支持在线DDL但大表上执行ALTER TABLE仍然会触发大量内部操作生产环境做一次不轻松。5.2 上线前必做的三条索引与事务联查我给团队的SQL走查清单固定有这么几条用EXPLAIN确认每一条可能高频执行的查询都能命中索引且type至少是range对联合索引做脑内最左前缀验证写SQL时从联合索引最左侧列开始带条件对UPDATE和DELETE语句确认WHERE条件走的是唯一索引或组合索引防止锁范围扩大。还有一个常被忽略的点索引列不要加不必要的函数和隐式转换。这个在SQL走查中用EXPLAIN看key字段有没有从possible_keys里消失就能快速识别。5.3 慢查询日志定位从全局到单条如果不想上线前逐条核查可以在测试环境或预发环境开启慢查询日志把执行时间超过阈值的SQL全部捕获-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;配合mysqldumpslow或者直接用performance_schema里的events_statements_summary_by_digest视图做聚合找出查询次数多、平均耗时长的SQL一条条过EXPLAIN和索引优化。这个流程比凭感觉猜慢SQL高效得多。5.4 常见问题速查表直接对着排查我把自己踩过和帮别人排查过的高频问题整理成了一张速查表贴在团队Wiki里也分享在这里症状可能原因排查/解决方式SQL没问题但越来越慢索引失效或数据量大跑EXPLAIN看type和key优化SQL让索引命中更新某行时其他查询卡住WHERE条件没走索引确认索引命中避免锁扫描范围扩大报“事务日志已满”长事务或大事务占满undo/redo空间查innodb_trx杀掉长事务拆批提交偶发死锁多事务访问资源顺序不一致看SHOW ENGINE INNODB STATUS统一访问顺序同一条SQL有时快有时慢缓存失效、统计信息不准确定期ANALYZE TABLE优化SQL消除回表联合索引没生效查询条件不满足最左前缀调整索引列顺序或改写查询条件另外有个很容易忽视的坑EXPLAIN显示用了索引但实际慢在排序或回表上。Extra字段看到Using filesort时如果业务经常按某个字段排序可以考虑把它加进联合索引的末尾让排序直接在索引树上完成省掉额外的排序阶段。如果看到Using index condition索引条件下推说明MySQL 5.6以后的索引下推优化在生效这是好事不用处理。6. 写在最后根据我实际排障积累的几个锦囊这篇文章不是让你把事务和索引的原理背下来的更重要的是一套面对问题时的判断顺序。我自己在多年排障中形成了一个固定套路遇到数据库相关故障先看是不是长事务或死锁再查是不是索引失效最后再看SQL本身写法有没有问题。这个顺序能定位到90%以上的日常问题。动手之前先确认版本MySQL 5.7和8.x在事务行为、索引下推、优化器细节上都有差别。网上很多文章不标注版本就直接给结论抄作业容易翻车。索引不是万能的区分度低的列真不建索引可能比建了还快因为优化器扫描索引后还要回表成本反而更高。比如性别字段只有“男”“女”两个值等值查询可能命中大量数据不如全表扫。判断一个列是否适合建索引用这个SQL算一下区分度SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;区分度在0.1以下我通常不会单独建索引而是考虑和其他高频条件组合成联合索引。最后一个小技巧修改表结构或者批量刷数据的脚本尽量写成可重入的幂等操作。比如扣减库存前先查锁更新订单前判断状态机是否合法。幂等配合事务才能真正意义上保证数据不出错。我见过太多生产问题不是事务没开而是业务代码不具备幂等性重试一次就重复扣款。这套“事务兜底、索引提速”的组合拳掌握好了很多数据库相关的疑难杂症在你眼里就会清晰很多。希望能对正在学MySQL的朋友们有一点帮助。