MySQL¶

索引¶

索引类似于字典的目录，是空间换时间的思想。

聚簇索引（InnoDB 默认）：叶子节点直接存储索引值与完整行数据，找到索引即拿到数据。每张表只能有一个聚簇索引，通常就是主键索引。
非聚簇索引（二级索引）：叶子节点存储索引值与对应的主键值（而非行数据），如普通索引、唯一索引、前缀索引。查询时需先从二级索引找到主键，再回表查聚簇索引获取完整数据。若查询所需字段恰好都包含在索引中（覆盖索引），则可避免回表。

最左匹配失效的情况： - 使用 !=, or等 - like '%... - 字符串不加引号 - join 字段类型不同，造成隐式转换

常见用于搜索的数据结构：哈希表、平衡二叉树、红黑树、B 树、B+ 树，MySQL InnoDB 采用 B+ 树作为索引，是因为：

磁盘 IO 友好：B+ 树的非叶子节点只存储索引和指针（叶子节点存放索引与行数据），单个节点能容纳更多索引项，树的高度更低（通常 3~4 层即可覆盖千万级数据），每次查询所需的磁盘 IO 次数更少
天然支持范围查询：B+ 树的叶子节点通过双向链表连接，范围查询（BETWEEN、>、ORDER BY 等）只需定位到起始叶子节点后顺序遍历，效率远高于 B 树需要中序遍历整棵树
查询性能稳定：所有数据都存储在叶子节点，任何查询都要走从根到叶子的完整路径，时间复杂度稳定为 O(log n)；而 B 树的数据分布在所有节点，不同记录的查询深度不一致
相比哈希索引：哈希索引虽然等值查询 O(1)，但不支持范围查询和排序，也无法利用最左前缀匹配
相比跳表：跳表在内存中表现优异（如 Redis），但其指针结构不利于磁盘顺序读取，无法充分利用操作系统的预读机制
相比红黑树：每个节点只能有两个子节点，存储大量数据时树的高度会很高，这会导致多次的 I/O，红黑树适合存储少量数据的内存操作
相比 B 树：B 树的非叶子节点也存储数据，且叶子节点没有指针，范围查询效率低于 B+ 树

页分裂（Page Split）

InnoDB 以页（Page，默认 16KB）为最小 IO 单位，每个 B+ 树叶子节点对应一个数据页，页内记录按主键顺序排列。

当向一个已满的数据页中插入新记录时，InnoDB 会将该页的一部分数据搬移到新分配的页中，并调整父节点指针，这个过程就是页分裂。页分裂会导致：

额外的磁盘 IO（分配新页、写入、更新父节点）
页空间利用率下降（分裂后每个页约半满）
大量分裂时产生碎片，影响范围查询的顺序读性能

如何减少页分裂：使用自增主键（AUTO_INCREMENT）作为聚簇索引，保证数据总是追加到最后一个数据页，避免中间插入导致的分裂。这也是不推荐使用 UUID 作为主键的主要原因——UUID 的随机性会导致频繁的随机插入和页分裂。

锁¶

记录锁：锁定指定的数据
间隙锁：锁定指定数据的范围区间
临键锁（默认）：记录锁 + 间隙锁

MySQL 默认的隔离级别是 RR（可重复读），通过临键锁解决了 RR 下的幻读问题。

日志¶

undo log：记录事务的反向 SQL，支持事务回滚、MVCC 版本链，事务的原子性、一致性
redo log（WAL）：避免崩溃导致数据丢失，事务的持久性
bin log：主从同步，有 statement、mixed、row 三种模式

MVCC 支持了事务的隔离性

并发操作金额¶

在并发场景下对同一账户进行余额扣减、充值等操作时，核心问题是更新覆盖（Lost Update）：线程 A 和 B 分别读取到相同余额后各自计算并写回，导致其中一次扣款被另一次覆盖。以下介绍三种常见的解决方案。

1. 悲观锁：SELECT ... FOR UPDATE¶

通过 FOR UPDATE 对目标行加排他锁（X 锁），在事务提交前阻止其他事务对该行的并发修改。

-- 1. 显式开启事务
BEGIN;

-- 2. 查询并加排他锁（锁会持有至 COMMIT / ROLLBACK）
SELECT balance FROM wallet WHERE user_id = 1 FOR UPDATE;

-- 3. 在应用层进行业务校验：
-- if (balance >= deductAmount) { ... } else { ROLLBACK; return; }

-- 4. 执行扣款
UPDATE wallet SET balance = balance - 100 WHERE user_id = 1;

-- 5. 插入交易流水
INSERT INTO wallet_log(user_id, amount, type) VALUES (1, -100, 'DEDUCT');

-- 6. 提交事务（释放排他锁）
COMMIT;

锁的兼容性：FOR UPDATE 不会阻塞普通的 SELECT（快照读），但会阻塞以下操作：

SELECT ... FOR UPDATE — 尝试加 X 锁
SELECT ... LOCK IN SHARE MODE — 尝试加 S 锁
UPDATE / DELETE — 隐式加 X 锁

注意事项：悲观锁的并发性能较差，且容易引发死锁（例如两个账户互相转账时加锁顺序不一致）。对于简单扣款场景，推荐优先使用 WHERE 条件原子更新或 CAS 乐观锁。

SKIP LOCKED

FOR UPDATE SKIP LOCKED 会自动跳过已被其他事务锁定的行，非常适合任务队列等"抢占式消费"场景：

SELECT * FROM task_queue
WHERE status = 'PENDING'
LIMIT 1
FOR UPDATE SKIP LOCKED;

2. WHERE 条件原子更新（推荐）¶

将余额校验下沉到 WHERE 条件中，利用 MySQL 在执行 UPDATE 时自动加行级排他锁的特性，保证原子性。

BEGIN;

-- 仅当余额充足时才执行扣减，MySQL 自动加行锁保证原子性
UPDATE wallet
SET balance = balance - 100
WHERE user_id = 1 AND balance >= 100;

-- 通过 affected_rows 判断结果：
--   affected_rows == 1 → 扣款成功，继续插入日志并 COMMIT
--   affected_rows == 0 → 余额不足，ROLLBACK 并返回错误

INSERT INTO wallet_log(user_id, amount, type) VALUES (1, -100, 'DEDUCT');

COMMIT;

优势：减少了一次 SELECT 的网络往返（RTT），锁仅在 UPDATE 执行瞬间持有，死锁风险极低。

局限：仅适用于逻辑简单的单表扣减。如果业务涉及跨表查询（风控、优惠券、会员折扣等复杂校验），仍需使用 FOR UPDATE 先读后写。

3. 乐观锁：CAS 版本号¶

不加锁，而是在更新时校验版本号（Compare-And-Swap），若版本不匹配则说明数据已被其他事务修改，需要重试。

-- 1. 普通 SELECT，不加锁
SELECT balance, version FROM wallet WHERE user_id = 1;

-- 2. 更新时校验 version，防止覆盖
UPDATE wallet
SET balance = balance - 100, version = version + 1
WHERE user_id = 1 AND version = <old_version> AND balance >= 100;

-- affected_rows == 0 时需在应用层重试

方案对比¶

方案	优点	缺点	适用场景
`SELECT ... FOR UPDATE`	强一致性；支持复杂业务逻辑（先读后写）	并发性能差；持锁时间长；易死锁	跨表校验、复杂业务流程
WHERE 条件原子更新	实现简单；锁持有时间极短；死锁风险低	无法在扣减前执行复杂校验逻辑	单表余额扣减等简单场景（推荐）
CAS 版本号	读操作不加锁；并发性能好	高竞争时重试频繁；需应用层实现重试机制	读多写少、冲突概率低的场景

分库分表¶

拆分有垂直和水平两种维度：

垂直拆分：按业务或字段维度拆分。垂直分库将不同业务的表拆到独立数据库（如订单库、用户库）；垂直分表将同一张表中不常用或大字段（如 TEXT、BLOB）拆到扩展表，减少单行 I/O。
水平拆分：按行维度将同一张表的数据分散到多个库/表中，常见策略如下：

策略	示例	优点	缺点
按范围分	`user_id` 1–100W 一张表	扩容简单；冷数据易归档（如按时间分的订单表）	写热点集中在最新分片
按哈希取模	`user_id % N`	数据分布均匀，无热点	扩容需迁移数据；通常配合一致性哈希或预分片缓解
按地理位置	华北库、华南库	就近访问，降低延迟	适用于有明确地域属性的业务（如企业 SaaS 按租户区域分片）

拆分后引发的问题¶

SQL 路由：应用无法直接感知数据落在哪个分片，需要引入 ShardingSphere、MyCat 等中间件或 SDK 做透明路由。
分布式主键：自增 ID 在多库下会冲突，常用方案有美团 Leaf、雪花算法（Snowflake）等全局唯一 ID 生成器。
跨分片 JOIN：
- 字段冗余：将常用的关联字段直接冗余存到当前表，用空间换时间，避免跨库关联。
- 应用层组装：先查出 ID 列表，再在业务代码中并发查询各分片并在内存中拼接结果。
分布式事务：优先在业务设计上将同一事务内的数据划入同一分片；若无法避免跨库事务，可采用本地消息表 + MQ 最终一致性、Seata（AT / TCC 模式）或 Saga 模式。
多维度查询（非分片键查询）：
- 场景：订单表按 user_id 分片，商家需要按 merchant_id 查询、或仅凭 order_id 查询，怎么办？
- 基因法（Gene）：生成 order_id 时，将 user_id 的低几位哈希值嵌入 order_id 的后缀，这样仅凭 order_id 即可推导出所在分片。
- 双写与异构索引：通过 Canal / CDC 监听 Binlog，异步同步到 Elasticsearch，复杂的多条件检索、聚合及后台查询全部走 ES，实现读写分离的 CQRS 架构。

FAQ¶

MySQL¶

Q：一条 SQL 执行的过程是怎样的？ MySQL 可以分为 server 层和存储引擎层，server 层的执行顺序：

连接器：客户端通过 TCP 三次握手与数据库建立连接，并校验权限
分析器：对 SQL 语句进行词法分析和语法分析，识别关键字、表名、列名等，并检查语法是否合法
优化器：对 SQL 语句进行优化，根据成本计算出最优的执行计划，选择适合的索引
执行器：执行 SQL 语句，与存储引擎交互，返回结果

MySQL 8 之前还有查询缓存，但表发生修改时就要使相关缓存失效，维护成本高且命中率低，因此之后将其删除。

索引¶

讲一下 MySQL 的索引

MySQL的索引就像书籍的目录，当我们查看某个章节的时候，通过目录可以快速找到对应内容，这里的目录就是索引，索引是空间换时间的思想。MySQL的全表扫描就像一页一页翻书，索引则像通过目录查询，效率会高很多。

让你来设计 MySQL 的索引，你会怎么做？

常见的用于搜索的数据结构有哈希表、红黑树、B 树、B+ 树等：

哈希表：等值查询 \(O(1)\)，但不支持范围查询和排序，也无法利用最左前缀匹配；且数据量增大时哈希冲突加剧导致性能退化，扩容时需要 rehash 迁移数据，代价较高。
红黑树：作为二叉树，每个节点最多两个子节点，数据量大时树的高度很高，导致磁盘 I/O 次数过多；且每次插入或删除都可能触发旋转以维持平衡，在磁盘场景下开销较大。更适合内存中的少量数据操作（如 Java 的 TreeMap）。
B 树：多叉树，树高较低，但非叶子节点同时存储索引和数据，导致单个节点能容纳的索引项有限，树仍然偏高；且叶子节点之间没有指针，范围查询需要中序遍历整棵树。
B+ 树：非叶子节点只存储索引和指针，单节点可容纳更多索引项，树更加矮胖，磁盘 I/O 更少；所有数据都存储在叶子节点，查询性能稳定；叶子节点间通过双向链表连接，天然支持范围查询和顺序遍历。

综合来看，B+ 树在磁盘 I/O、范围查询、查询稳定性上都优于其他结构，因此 MySQL InnoDB 选择 B+ 树作为索引。

B+ 树怎么存储数据的？聚簇索引、非聚簇索引、覆盖索引、联合索引

MySQL的索引分为聚簇索引和非聚簇索引，每张表只能有一个聚簇索引，主键索引就是聚簇索引，普通索引、唯一索引、前缀索引则是非聚簇索引。它们的区别主要在于索引与数据是否一起存储，聚簇索引的叶子节点存放索引与数据行，而非聚簇索引则在叶子节点存放索引与主键，所以非聚簇索引在查询时需要通过主键ID再查一次聚簇索引，也就是回表。这也是为什么非聚簇索引也叫二级索引。而SQL优化的一个重要方向就是减少回表，因此使用覆盖索引，即查询所需的字段就在索引中。联合索引中的多个字段可以大大增加覆盖索引的命中率，减少回表次数。

联合索引的最左前缀匹配原则

假设现在的联合索引是(a,b,c)，则命中情况如下：

where a = 1 → 命中
where a = 1 and b = 2 → 命中
where b = 2 and a = 1 → 命中，MySQL 优化器会自动重排索引顺序
where a = 1 and b = 2 and c = 3 → 命中
where a = 1 and c = 3 → 部分命中，a 走索引，c 不走索引，因为 c 的有序建立在 b 的基础上，c 前面的 b 有缺失，所以 c 无法走索引
where b = 2 → 不命中
where b = 2 and c = 3 → 不命中
where a != 1 → 通常不命中，优化器根据数据分布决定是否走索引，多数情况下选择全表扫描
where a = 1 or a = 2 等价于 where a in (1,2) → 命中
where a = 1 or b = 2 → 不命中
where a like '%... → 不命中
where a like '...% → 命中
where a like '%...% → 不命中
where substring(a, 1, 1) = 'a' → 不命中，因为对索引列使用函数后，破坏了 B+ 树的有序性，无法使用二分查找，自然无法走索引

什么情况下应该加索引？

索引不是越多越好，索引占用的空间会随着数据量的增长而增大，同时写入数据时也要更新索引，拖慢写入性能。应该针对经常查询、排序、区分度高的列添加索引。尽量多使用联合索引而非单个索引，同时减少回表，对于较长的字符串可以使用前缀索引。

为什么要使用自增 ID 作为主键？以及什么是页分裂？

InnoDB中每个叶子节点都对应一个数据页，页内的记录按照主键顺序排列。如果向一个已满的数据页插入数据时，InnoDB会将该页的一部分数据重新分配到新的数据页中，这个过程叫做页分裂。页分裂导致了额外的磁盘I/O。要避免页分裂，可以使用自增ID作为主键，让数据总是追加到最后一个数据页，避免中间插入导致分裂。

事务¶

什么是事务？

以A向B转账100元为例，A扣减100，B增加100，这种执行多个SQL的操作需要保证原子性，成功则提交，失败则回滚，因此需要事务来实现。

数据库并发产生的问题：

脏读（Dirty Read）：一个事务读取到了另一个并发事务未提交的数据。
不可重复读（Non-repeatable Read）：一个事务内两次读取同一行数据，由于另一个并发事务在此期间执行了 UPDATE 并提交，导致两次读取的值不一致。
幻读（Phantom Read）：一个事务内两次执行同条件的范围查询，由于另一个并发事务在此期间执行了 INSERT 或 DELETE 并提交，导致两次相同的查询返回的行数不一致。

事务的隔离分为 4 个级别，从宽松到严格如下表格：

隔离级别	脏读	不可重复读	幻读	改进
读未提交	✓	✓	✓
读已提交	✗	✓	✓	解决脏读
可重复读	✗	✗	✗ (快照读靠 MVCC，当前读靠临键锁)	解决不可重复读
串行化	✗	✗	✗	解决幻读

MVCC¶

MVCC（多版本并发控制）通过 undo log 版本链 保存数据的历史版本，通过 ReadView 判断哪些版本对当前事务可见，从而在不加锁的情况下实现事务隔离，解决脏读和不可重复读问题。同时由于 MVCC 的不同事务看到的数据量不同，因此 InnoDB 无法维护数据表的精确行数。

MVCC 只用在读已提交和可重复读两个隔离级别，两者的区别在于 ReadView 的创建时机：

读已提交：每次执行 SELECT 时都创建新的 ReadView，因此能读到其他事务已提交的最新数据
可重复读：仅在事务中第一次执行 SELECT 时创建 ReadView，后续复用同一个，因此整个事务内读取的数据快照一致

快照读与当前读：

快照读：普通 SELECT，通过 MVCC 读取历史版本，不加锁，并发性能高
当前读：SELECT ... FOR UPDATE、UPDATE、DELETE 等，直接读取数据的最新版本并加锁，阻塞其他事务对同一记录的修改

可重复读级别如何防止幻读：

快照读靠 MVCC，始终读取事务开始时的快照，自然看不到其他事务新插入的行
当前读靠临键锁（Next-Key Lock）锁住查询范围，阻止其他事务在该范围内插入新数据

日志¶

redo log、undo log、binlog 的作用是什么？

undo log（InnoDB 引擎层）：记录数据修改前的值，事务失败时用于回滚，同时也是 MVCC 版本链的基础。
redo log（InnoDB 引擎层）：实现了 WAL（Write-Ahead Logging），修改数据时先将变更记录顺序写入 redo log，再修改 buffer pool 中的数据页。如果 MySQL 崩溃，可通过 redo log 恢复尚未刷盘的脏页数据。同时将随机 I/O 转为顺序 I/O，提升写入性能。
binlog（Server 层）：记录所有数据变更，用于主从复制和数据恢复（point-in-time recovery）。有三种模式：
- statement：记录原始 SQL，日志体积小，但 NOW()、RAND() 等非确定性函数可能导致主从不一致
- row：记录每一行的具体变更，确定性强，但批量操作时日志体积大
- mixed：默认使用 statement，遇到非确定性操作时自动切换为 row，兼顾两者优点

在一个事务中，redo log、undo log、binlog 是如何协作的？

准备回滚（Undo log）：在对记录进行具体修改前，Undo log 会先记录修改前的数据镜像。这样一旦事务中途失败或执行 ROLLBACK，可以快速恢复到修改前的状态；同时 Undo log 也为 MVCC 提供了历史版本链。
准备重做（Redo log - Prepare）：数据在内存（Buffer Pool）中修改后，会生成对应的 Redo log，此时 Redo log 处于 Prepare（准备）状态，并刷新到磁盘（WAL 预写日志机制），确保哪怕此时宕机，数据改动也有据可查。
记录变更（Binlog）：在 Redo log 处于 Prepare 状态后，服务层会把本次事务的最终修改逻辑写入 Binlog 并刷盘，用于后续的主从同步或数据恢复。
提交事务（Redo log - Commit）：Binlog 写入成功后，Redo log 会将状态修改为 Commit（提交）状态，此时整个事务才算真正成功完成。

在事务中，为了确保 redo log 与 binlog 的一致性，采用两阶段提交（2PC）。

数据页如何工作？

数据页是 InnoDB 的最小 I/O 管理单位，默认 16KB。B+ 树的每个节点就是一个数据页，叶子节点的数据页中存储了实际的数据行。

读取：查询数据时，以数据页为单位加载到 buffer pool。由于空间局部性原理，同一页内的相邻数据会一起被加载，后续访问时直接命中内存，无需再次磁盘 I/O。

修改：根据 WAL 原则，先将变更写入 redo log，再修改 buffer pool 中的数据页。此时内存中的数据页与磁盘不一致，称为脏页。InnoDB 会在合适的时机（如空闲时、内存不足时）将脏页异步刷回磁盘。

MySQL¶

索引¶

锁¶

日志¶

并发操作金额¶

1. 悲观锁：SELECT ... FOR UPDATE¶

2. WHERE 条件原子更新（推荐）¶

3. 乐观锁：CAS 版本号¶

方案对比¶

分库分表¶

拆分后引发的问题¶

FAQ¶

MySQL¶

索引¶

事务¶

MVCC¶

日志¶

评论