数据库 MySQL 索引与事务｜java知识点

MySQL 整体架构

MySQL 的 SQL 执行自上而下分为以下几层：

1. 连接器：负责权限认证、登录校验和连接管理。
2. 分析器：对 SQL 进行词法分析和语法分析，检查是否存在语法错误。
3. 优化器：对 SQL 语句进行优化，选择它认为最优的执行计划，如选择使用哪个索引、多表连接的顺序等。
4. 执行器：调用存储引擎接口执行 SQL，返回结果数据。
5. 存储引擎：负责数据的存储与检索，采用插件式架构。默认引擎为 InnoDB，其他可选引擎包括 MyISAM、Memory 等。

索引

B+Tree 索引

InnoDB 默认使用 B+Tree 作为索引结构。B+Tree 的核心特点：

• 所有数据存储在叶子节点，非叶子节点只存储键值和索引信息。
• 叶子节点之间用双向链表连接，支持高效的范围查询和顺序扫描。
• 树更矮：非叶子节点不存数据，每个节点能容纳更多键值，使得树的高度更低，减少磁盘 IO 次数，查询更快。

相比之下，B-Tree 的每个节点都存储数据，导致树更高，查询性能较差。

聚簇索引与二级索引

聚簇索引（Clustered Index）：数据和索引放在一起。InnoDB 中主键索引就是聚簇索引，叶子节点直接存储整行数据。一张表只能有一个聚簇索引。

二级索引（Secondary Index）：非主键索引。叶子节点只存储索引键值和主键 ID。如果查询需要索引字段之外的数据，必须通过主键再查聚簇索引获取完整行，这个过程叫做回表。

回表与覆盖索引

回表：当通过二级索引查询，且需要获取索引字段以外的列时，需要拿着主键 ID 去聚簇索引中查找完整行数据。回表会增加额外的查询开销。

覆盖索引（Covering Index）：当查询所需的所有字段（包括条件字段和返回字段）都已被某个索引覆盖时，只需要扫描索引树就能返回结果，无需回表。例如，对 (user_id, name) 建立联合索引后，SELECT user_id, name FROM user WHERE user_id = 1 就可以通过覆盖索引直接返回。

索引失效的常见场景

• 在索引列上使用函数或表达式计算，如 WHERE YEAR(create_time) = 2024
• 使用 LIKE '%xxx' 前缀通配符，如 WHERE name LIKE '%张'
• 使用 OR 连接条件，且其中一个条件没有索引
• 对索引列进行隐式类型转换
• 使用 !=、NOT IN、NOT EXISTS 等否定条件（视情况）
• 范围查询后的列无法使用索引（联合索引的最左前缀原则）

EXPLAIN 执行计划

使用 EXPLAIN 分析 SQL 性能时，重点关注以下字段：

• type：访问类型，从好到差依次为 const > eq_ref > ref > range > index > ALL。ref 表示用到了索引，range 是范围查询用到索引，index 是遍历索引树，ALL 是全表扫描，必须优化。
• key：实际使用的索引。
• rows：预估扫描的行数。
• Extra：额外信息。Using index 表示使用了覆盖索引，Using where 表示在存储引擎层过滤后还需在服务层过滤。

事务

什么是事务

事务是逻辑上的一组操作，要么全部执行，要么全部不执行。

事务并发问题

• 脏读（Dirty Read）：一个事务读取到了另一个事务尚未提交的数据。如果对方回滚，读到的数据就是无效的。
• 不可重复读（Non-Repeatable Read）：同一事务内多次读取同一数据，由于其他事务的修改操作导致结果不一致。
• 幻读（Phantom Read）：同一事务内多次查询，由于其他事务的插入或删除操作导致结果集行数不一致。

ACID 特性

• 原子性（Atomicity）：事务要么全部成功，要么全部失败回滚。由 Undo Log 实现。
• 一致性（Consistency）：数据库从一个一致状态转移到另一个一致状态。由原子性、隔离性和持久性共同保证。
• 隔离性（Isolation）：并发事务之间互不干扰。通过锁和 MVCC 实现。
• 持久性（Durability）：已提交的事务修改永久保存。由 Redo Log 和双写缓冲实现。

隔离级别

InnoDB 默认使用可重复读隔离级别。选择可重复读的原因：在大多数业务场景下，可重复读已经能满足需求，且性能优于串行化。同时 InnoDB 通过 MVCC 和间隙锁（Gap Lock）在可重复读级别下基本解决了幻读问题。

MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）通过以下机制实现：

1. 每行记录有两个隐藏列：最后修改的事务 ID 和回滚指针（指向 Undo Log 中的旧版本）。
2. 事务启动时生成一个 ReadView（读视图），记录当前活跃事务的 ID 列表。
3. 查询时根据 ReadView 的可见性规则，只读取版本号符合规则的记录。

在可重复读级别下，ReadView 在事务第一次读取时创建，整个事务期间复用，从而保证同一事务内多次读取结果一致。

日志

Redo Log（重做日志）

InnoDB 独有，用于崩溃恢复。记录的是物理变化（某个数据页的某个偏移量做了什么修改）。当事务提交时，修改先写入 Redo Log，再由后台线程异步刷新到磁盘。即使数据库崩溃，也可以通过 Redo Log 恢复已提交的修改。

Undo Log（回滚日志）

记录数据的逻辑变化（如 INSERT 对应一条 DELETE，UPDATE 对应一条反向 UPDATE）。当事务需要回滚时，通过 Undo Log 将数据恢复到修改前的状态，保证事务的原子性。

Binlog（二进制日志）

MySQL Server 层日志，记录所有更新操作的逻辑变化。事务提交时写入缓存，事务完成后刷新到日志文件。主要用于主从复制和数据备份恢复。Binlog 是追加写入，文件不断增长，需配合 Binlog 过期清理策略使用。

两阶段提交

为了保证 Redo Log 和 Binlog 的一致性，InnoDB 使用两阶段提交协议：

1. 准备阶段：Redo Log 写入磁盘，标记为 prepare 状态。
2. 提交阶段：Binlog 写入磁盘后，将 Redo Log 标记为 commit 状态。

如果在准备阶段后、提交阶段前崩溃，恢复时会检查 Binlog 是否完整：完整则提交，不完整则回滚。

锁

行锁类型

• 记录锁（Record Lock）：锁住索引上的一条具体记录，防止其他事务修改或删除该行。
• 间隙锁（Gap Lock）：锁住索引记录之间的间隙（左开右开区间），防止其他事务在间隙中插入新记录，从而避免幻读。
• Next-Key Lock：记录锁 + 间隙锁的组合（左开右闭区间），锁住一条记录及其前面的间隙。

在可重复读隔离级别下：范围查询默认使用 Next-Key Lock 防幻读；等值查询命中记录时退化为记录锁，未命中时退化为间隙锁。

慢查询优化

开启慢查询日志的步骤：

1. 设置 slow_query_log = ON 开启慢日志。
2. 设置 long_query_time 控制阈值（单位秒），超过该时间的 SQL 会被记录。
3. 设置 slow_query_log_file 指定日志文件路径。

SQL 优化的基本原则：

• 避免 SELECT *，只查询需要的字段。
• 优化分页查询（深分页使用游标方式）。
• 减少关联查询，优先在业务层做数据组装。
• 少用外键，在应用层保证数据完整性。
• 合理建立索引，频繁更新的字段不适合加索引。

读写分离与主从复制

主从复制原理

1. 主库执行事务后将变更写入 Binlog。
2. 从库的 IO 线程从主库拉取 Binlog，写入本地的 Relay Log。
3. 从库的 SQL 线程读取 Relay Log 并回放，实现数据同步。

读写分离

写操作走主库，读操作走从库。通过代理层（如 MyCat、ShardingSphere）或应用层路由实现。

主从延迟

主从延迟的常见原因：从库性能差、从库读压力大、大事务执行时间长、从库数量过多、网络延迟。

分库分表

分库分表策略

• 垂直分库：按业务模块拆分，如用户库、订单库、商品库。
• 水平分库：按规则（如 user_id % N）将同一张表的数据拆分到多个库。
• 垂直分表：将宽表按列拆分，常用字段放一张表，不常用字段放另一张表。
• 水平分表：大表按行（如 ID 取模）拆分成多张结构相同的小表。

分库分表的使用时机

• 单表数据量超过千万行
• 单库磁盘空间或连接数不足
• 写压力或并发过高
• 备份和恢复时间过长

分库分表的代价

• 本地事务失效，需要分布式事务方案
• 自增 ID 失效，需要分布式 ID 方案（如雪花算法）
• 聚合查询（JOIN、GROUP BY、ORDER BY）变得复杂困难
• 跨节点分页和排序需要额外处理

分库分表路由算法

• 哈希取模：hash(key) % N，数据分布均匀，但扩容时需要重新分布数据。
• 范围路由：按时间或 ID 范围分片，扩容方便但可能热点不均。
• 一致性哈希：哈希环，数据落在顺时针方向第一个节点。增减节点时只影响相邻节点的数据。
• 基因法：将分库分表索引嵌入分布式 ID 中，通过 ID 的低位直接定位目标分片。