MySQL索引与事务：从B+Tree原理到死锁排查实战

这期内容确实值得先收藏再慢慢看。它不是零散的 SQL 片段而是把 MySQL 索引和事务从原理到实战、从执行计划到死锁排查整理成了一条完整链路。无论是准备面试、日常开发写 SQL还是接手老项目做性能优化这些知识点都会反复用到。下面我们用可复现的示例把这块内容一次性讲透。1. 为什么索引与事务是 MySQL 学习的核心很多开发者在刚接触 MySQL 时第一步是学会建表、写增删改查第二步就接触到索引和事务。这两个概念看似独立实际关系非常紧密索引决定了查询能不能走对路事务决定了数据在并发环境下稳不稳定。只有把两者结合起来理解才能解释很多线上问题。先看两个典型场景一条 SQL 在测试库执行 50ms到了生产环境变成 3s排查后发现 where 条件里的字段没有走索引。两个接口同时更新同一条订单记录其中一个事务提交后另一个事务读到了中间的脏数据。这些问题如果只靠“网上搜一条命令临时解决”下次换一个环境、换一个表又会踩坑。更可靠的做法是理解背后的数据结构、执行计划、隔离级别和锁机制再回到实践中去验证。本文会围绕 MySQL 中最常用的 InnoDB 存储引擎展开因为它在 5.5 以后的版本里是默认引擎支持事务、行级锁和崩溃恢复。示例以 MySQL 5.7 / 8.0 的常见行为为准如果你的版本不同个别系统表名称或默认配置会略有差异但核心思路是通用的。2. 环境准备与测试表结构动手之前先准备一个干净的 MySQL 环境。你可以用本地安装的 MySQL也可以用 Docker 快速启动一个实例。本文示例不依赖额外的第三方中间件只要能用客户端连接 MySQL 即可。2.1 环境版本说明数据库MySQL 5.7 或 MySQL 8.0存储引擎InnoDB客户端工具命令行 mysql、Navicat 或 DataGrip 均可操作系统Windows / Linux / macOS 均可如果本地没有 MySQL可以用 Docker 启动例如docker run -d \ --name mysql-demo \ -e MYSQL_ROOT_PASSWORD123456 \ -p 3306:3306 \ mysql:8.0注意这只是演示环境生产环境不要使用简单密码并要单独创建业务账号。2.2 创建测试库与测试表我们模拟一个电商订单场景创建用户表、订单表和订单明细表。CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARACTER SET utf8mb4; USE demo_db; -- 用户表 CREATE TABLE t_user ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(64) NOT NULL COMMENT 用户名, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; -- 订单主表 CREATE TABLE t_order ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL COMMENT 用户ID, order_no VARCHAR(64) NOT NULL COMMENT 订单号, amount DECIMAL(12,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; -- 订单明细表 CREATE TABLE t_order_item ( id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL COMMENT 订单ID, goods_name VARCHAR(128) NOT NULL COMMENT 商品名称, price DECIMAL(12,2) NOT NULL COMMENT 商品单价, quantity INT NOT NULL COMMENT 数量, PRIMARY KEY (id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这里有几个设计细节用 BIGINT 作为主键避免海量数据下 INT 溢出。用户名字段加唯一索引因为业务上用户名不允许重复。订单表加订单号唯一索引因为订单号是业务上的唯一标识。user_id 和 created_at 分别建索引用于后续联表查询和时间范围查询。2.3 写入测试数据为了方便观察索引效果和事务行为我们插入一些示例数据。线上环境数据量可能是百万级这里先造出一定规模的数据。-- 插入 1000 个用户仅示例 INSERT INTO t_user (username, phone) VALUES (alice, 13800000001), (bob, 13800000002), (carol, 13800000003); -- 插入订单 INSERT INTO t_order (user_id, order_no, amount, status) VALUES (1, NO20250101001, 199.00, 1), (2, NO20250101002, 299.00, 0), (3, NO20250101003, 99.90, 1), (1, NO20250101004, 59.00, 2);如果希望更贴近真实场景可以写存储过程批量造数但在学习阶段先把数据量控制在可观察范围即可。重点是通过EXPLAIN看到不同 SQL 的执行计划差异。3. 索引的核心原理与分类索引的作用可以用一句话概括它把无序的数据变成有规律的结构让查找不再全表扫描。实现这个目标最常见的数据结构是 BTree。与二叉树相比BTree 的每个节点能存放更多键值树的高度更低对磁盘 IO 更友好与哈希索引相比BTree 支持范围查询和排序。3.1 聚簇索引与非聚簇索引InnoDB 的表数据本身就是按照主键构建的 BTree 组织的这叫聚簇索引。聚簇索引的叶子节点存储的是整行数据。也就是说只要通过主键定位一次索引查找就能拿到完整记录。非聚簇索引又叫二级索引它的叶子节点存储的是索引列的值和主键值。比如在user_id字段上建立索引后通过user_id查询时会先到二级索引里找到主键 id再回到聚簇索引里拿整行数据这个过程叫回表。3.2 覆盖索引如果查询所需的字段已经在二级索引的叶子节点中那么就不需要回表这种索引叫覆盖索引。覆盖索引可以明显减少 IO 次数是优化查询的重要手段。举个例子SELECT user_id, amount FROM t_order WHERE user_id 1;如果只有idx_user_id这一个索引执行时在二级索引中可以直接拿到 user_id 和主键 id但 amount 字段不在索引里需要回表。我们可以创建一个联合索引来覆盖查询字段ALTER TABLE t_order ADD INDEX idx_user_id_amount (user_id, amount);这样user_id和amount都在索引中查询就不需要回表了。联合索引的字段顺序很重要这引出了最左前缀原则。3.3 联合索引与最左前缀原则对于联合索引(a, b, c)查询条件只有遵守最左前缀才能充分利用索引条件包含a可以使用索引。条件包含a和b可以使用索引。条件包含a、b和c可以完全使用索引。条件只包含b或只包含c通常无法使用该联合索引。这是因为 BTree 中联合索引先按第一个字段排序再按第二个字段排序。跳过了第一个字段就无法利用索引的排序结构。4. 索引实战从创建到执行计划分析4.1 创建索引的常用语法在已有表上添加索引-- 普通索引 ALTER TABLE t_order ADD INDEX idx_status (status); -- 唯一索引 ALTER TABLE t_user ADD UNIQUE INDEX uk_phone (phone); -- 联合索引 ALTER TABLE t_order ADD INDEX idx_user_status (user_id, status); -- 删除索引 ALTER TABLE t_order DROP INDEX idx_status;创建索引本身并不难难点在于判断哪些字段需要索引、哪些字段不适合索引。后面第 8 节会给出系统性的设计原则。4.2 使用 EXPLAIN 分析执行计划EXPLAIN是 MySQL 提供的 SQL 执行计划分析工具。在任意 SELECT 语句前加EXPLAIN就能看到优化器选择的执行路径。EXPLAIN SELECT * FROM t_order WHERE user_id 1;执行后重点看这几列列名含义type访问类型从好到差依次是 system const eq_ref ref range index ALLkey实际使用的索引名称rows预估扫描的行数Extra额外信息比如 Using index 表示覆盖索引Using where 表示过滤条件在存储引擎层之后使用possible_keys可能使用的索引如果 type 是 ALL说明全表扫描。在生产环境大表上全表扫描通常是性能瓶颈。把 type 从 ALL 优化到 ref 或 range是索引优化最常见的目标。4.3 一个完整的索引失效案例下面这条 SQL 看起来可以走idx_created_at索引SELECT * FROM t_order WHERE DATE(created_at) 2025-01-01;但实际执行时因为对索引列使用了DATE()函数索引会失效退化成全表扫描。正确的写法是SELECT * FROM t_order WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00;这样可以充分利用索引也方便命中年月日范围查询。4.4 常见的索引失效场景对索引列使用函数或计算例如WHERE amount 1 200。隐式类型转换例如手机号字段是 varchar查询条件写成WHERE phone 13800000001。联合索引不满足最左前缀。like 查询以通配符开头例如WHERE goods_name LIKE %手机%。使用 or 连接条件时如果一侧字段没有索引可能导致整个条件无法走索引。5. 事务的四大特性与隔离级别事务是一组不可分割的操作。把多个 SQL 放入一个事务执行时要么全部成功要么全部回滚。事务有四个核心特性原子性、一致性、隔离性、持久性简写为 ACID。原子性事务中的操作要么全部成功要么全部失败。一致性事务执行前后数据满足完整性约束。隔离性多个事务并发执行时彼此之间要互相隔离。持久性事务提交后数据修改要永久保存。隔离性不是绝对的隔离强度越高并发性能越低。SQL 标准定义了四个隔离级别隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不会可能可能REPEATABLE READ不会不会可能InnoDB 通过间隙锁基本避免SERIALIZABLE不会不会不会MySQL InnoDB 的默认隔离级别是 REPEATABLE READ。在这个级别下InnoDB 通过 MVCC 和间隙锁极大降低了幻读发生的概率。6. 事务实战手动控制事务与隔离级别验证6.1 手动控制事务MySQL 默认开启自动提交也就是每条 SQL 都会立即提交。手动控制事务时需要显式开启。START TRANSACTION; UPDATE t_order SET status 1 WHERE id 1; -- 如果发现数据有问题回滚 ROLLBACK; -- 如果确认无误提交 COMMIT;在应用代码中不要把多条业务操作放在没有事务管理的 SQL 执行中。比如 Java 中使用 Spring 的Transactional注解或者在 Python 中使用connection.commit()和connection.rollback()控制事务边界。6.2 验证脏读脏读是指一个事务读到了另一个事务未提交的数据。下面用两个会话演示。会话 ASTART TRANSACTION; UPDATE t_order SET amount 999.00 WHERE id 1;会话 BSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT amount FROM t_order WHERE id 1;此时会话 B 会读到 999.00但这个值是会话 A 尚未提交的修改。如果会话 A 回滚会话 B 就读到了脏数据。把会话 B 的隔离级别改成 READ COMMITTEDSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT amount FROM t_order WHERE id 1;此时读到的仍然是事务开始前的旧值说明 READ COMMITTED 可以避免脏读。6.3 验证不可重复读不可重复读指的是同一个事务内多次查询同一行结果不一致。在 READ COMMITTED 级别下可能发生。会话 ASET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT amount FROM t_order WHERE id 1;会话 BUPDATE t_order SET amount 88.00 WHERE id 1; COMMIT;此时会话 A 再次执行同样的 SELECTSELECT amount FROM t_order WHERE id 1;会发现结果从原来的值变成了 88.00。同一个事务内两次查询结果不同就是不可重复读。在 REPEATABLE READ 级别下InnoDB 的快照读会保证事务内多次读取的结果一致。6.4 锁与死锁的基本原理InnoDB 支持行级锁但行级锁并不只是锁住一行数据在范围条件下还可能锁住间隙避免其他事务在间隙中插入数据这就是间隙锁。间隙锁与行锁结合就是 next-key lock。死锁的典型场景是两个事务各持有一把锁又同时等待对方持有的锁。比如事务 ASTART TRANSACTION; UPDATE t_order SET status 1 WHERE id 1; -- 等待事务 B 释放 id2 的行锁 UPDATE t_order SET status 1 WHERE id 2; COMMIT;事务 BSTART TRANSACTION; UPDATE t_order SET status 1 WHERE id 2; -- 等待事务 A 释放 id1 的行锁 UPDATE t_order SET status 1 WHERE id 1; COMMIT;两个事务互相等待InnoDB 会检测到死锁并回滚其中一个事务。应用层收到死锁异常后需要捕获并重试。7. 常见问题与排查思路7.1 慢 SQL 问题怎么看慢 SQL 是数据库性能问题的高发区。一般排查路径是开启慢查询日志定位执行时间超过阈值的 SQL。对慢 SQL 执行 EXPLAIN观察 type、key、rows 和 Extra。确认是否存在索引失效、全表扫描、回表过多等问题。分析数据量级和写放大判断是否需要对大表做归档或分表。7.2 索引失效问题怎么查先列出常见的怀疑点问题现象常见原因排查思路明明有索引却不走where 条件对索引列用了函数改写为范围比较查询很慢联合索引字段顺序不合理检查最左前缀类型转换导致失效varchar 字段与数字比较统一使用字符串传参大量回表查询字段不在索引中尝试覆盖索引analyze 后统计信息过期数据量变化大执行 ANALYZE TABLE 更新统计信息7.3 死锁问题怎么查死锁出现后可以通过SHOW ENGINE INNODB STATUS查看最近一次死锁信息重点看 LATEST DETECTED DEADLOCK 部分。里面会显示两个事务各自持有和等待的锁。排查步骤根据死锁日志确认涉及的表和 SQL。分析两个事务的加锁顺序。在业务层统一加锁顺序。缩小事务范围减少锁持有时间。必要时使用 SELECT ... FOR UPDATE 显式控制锁顺序或者引入分布式锁。7.4 事务不回滚的坑使用 Spring 框架时Transactional默认只在 RuntimeException 下回滚受检异常不会回滚。这也是事务不生效、数据出现半截写入的常见原因。建议在事务方法内部自行捕获异常并判断是否需要标记setRollbackOnly()或者将异常抛出让 Spring 处理。8. 索引与事务的最佳实践8.1 索引设计原则并不是索引越多越好。每个索引都会占用磁盘空间并增加写入代价。插入、更新时MySQL 需要同步维护索引结构索引过多会拖慢写入性能。实际项目中建议遵循这些原则为 where 条件、order by、group by 中频繁出现的字段建立索引。优先使用联合索引减少单列索引数量。区分度低的字段例如性别、状态单独建索引可能没有效果。大文本字段不建议直接建索引可以使用前缀索引。不要为每个字段都建立索引更不要提前给未来可能用到的字段建上索引。8.2 事务控制原则事务在保证数据一致性的同时也会增加锁和日志的开销。设计事务时要尽量减少事务范围避免在事务中执行耗时的外部调用比如 HTTP 请求、文件上传、短信发送。不要让一个大事务包裹几十条 SQL优先拆分成多个小事务。操作生产数据时务必遵循以下原则先备份或确认有 binlog 可以恢复。在测试环境执行相同 SQL 验证影响行数。使用 WHERE 条件时先 SELECT 预览命中数据。大批量更新或删除要分批执行减少锁范围。对权限敏感的操作使用只读账号避免误删。8.3 日志与监控数据库层面的监控指标包括慢查询数量、锁等待时间、事务平均时长、回滚次数、临时表使用量。如果锁等待持续升高说明存在长事务或锁冲突。开发阶段就应当在日志中打印 SQL 参数和事务边界方便问题回溯。9. 总结与下一步MySQL 索引和事务是几乎所有后端开发者都会遇到的内容。写 SQL 时先问自己三个问题这条查询能不能用上索引这个更新操作会不会持锁过久这个事务边界是否包含了不必要的操作能把这三个问题回答清楚就能解决大部分数据库性能和数据一致性问题。实战方面建议你按下面的顺序动手练习用EXPLAIN分析不同 SQL 的执行计划观察 type 和 rows 的变化。建一个包含联合索引的表测试不同查询条件是否命中索引。开两个数据库会话手动验证 READ COMMITTED 和 REPEATABLE READ 的区别。模拟一次死锁通过SHOW ENGINE INNODB STATUS分析加锁顺序。把线上一个真实慢 SQL 作为案例尝试用覆盖索引或改写 SQL 优化它。如果后续想继续深入可以学习 MVCC 的底层实现、InnoDB 锁的分类、binlog 与 redo log 的联动机制以及分库分表下的全局事务方案。这些内容都和本文的基础知识一脉相承。把今天的基础打牢后面深入时会轻松很多。