MySQL¶

索引¶

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

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

最左匹配失效的情况：

• 使用 !=, or等
• like '%...
• 字符串不加引号
• join 字段类型不同，造成隐式转换

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

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

锁¶

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

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 乐观锁。

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 时需在应用层重试

方案对比¶

分库分表¶

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

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

拆分后引发的问题¶

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

生产常见故障¶

MySQL 的线上故障往往是连锁反应：一条慢 SQL 或一个长事务占住资源，后续请求不断堆积，最终连接耗尽，接口全部超时。因此定位时要先找到堆积的源头，而不是只看最后的报错。

排查工具¶

-- 查看所有连接正在执行的 SQL
SHOW FULL PROCESSLIST;

-- 查找运行超过 60 秒的事务
SELECT trx_id, trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 60 SECOND;

-- 查看谁在阻塞谁
SELECT * FROM sys.innodb_lock_waits;

-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS;

-- 终止指定连接，线程 ID 来自上面的查询
KILL <thread_id>;

慢查询¶

开启 slow_query_log，并调低 long_query_time（默认 10 秒），再用 pt-query-digest 汇总慢查询日志，找出总耗时最高的 SQL。EXPLAIN 重点关注 type（ALL 表示全表扫描）、rows（预估扫描行数）和 Extra（Using filesort、Using temporary）。

连接数耗尽¶

连接数达到 max_connections（默认 151）后，新连接会报 Too many connections。此时 MySQL 为管理员保留了一个额外连接，8.0.14 起也可以通过 admin_port 配置独立的管理端口。

连接池的连接最大存活时间要小于 wait_timeout，否则应用可能拿到已被 MySQL 关闭的连接，报 MySQL server has gone away。

长事务与大事务¶

长事务指运行时间长的事务，最常见的原因是在事务里调用 RPC、HTTP 等外部接口，或者框架关闭了自动提交后事务一直未结束。它的危害有：

• 持有的行锁直到提交才释放，阻塞其他写入
• undo log 无法清理，导致 undo 膨胀，其他查询遍历版本链时也会变慢
• 持有元数据锁，引发 DDL 阻塞

大事务指一次修改大量数据的事务，如一次 DELETE 上百万行。它的危害有：

• 回滚耗时可能比执行更长
• binlog 集中写入，引发主从延迟

修复方式：事务内不做外部调用，只包含必要的 SQL；批量操作按主键范围分批执行，每批单独提交；监控 information_schema.innodb_trx，对超过阈值的事务告警。

锁等待与死锁¶

InnoDB 的行锁是加在索引上的，事务持有的锁要到提交时才释放。等待超过 innodb_lock_wait_timeout（默认 50 秒）时，报 Lock wait timeout exceeded。

InnoDB 会自动检测死锁，并回滚代价最小的事务，被回滚的一方报 Deadlock found。因此业务代码要捕获这个错误并重试。

DDL 阻塞¶

所有查询和写入都持有元数据锁（MDL）读锁，事务提交后才释放，而 DDL 需要 MDL 写锁。典型的故障过程是：

1. 一个未提交的长事务持有 MDL 读锁
2. ALTER TABLE 等待 MDL 写锁
3. 后续所有查询都排在写锁之后，这张表完全不可读写
4. 请求堆积，连接耗尽

这个问题即使对小表也会发生，修复方式如下：

• 变更前检查 information_schema.innodb_trx，确认没有长事务
• 设置 SET lock_wait_timeout = 5，让 DDL 等待超时后放弃，默认值是一年
• 能用 ALGORITHM=INSTANT（如 8.0 加列）时优先使用
• 大表使用 ALGORITHM=INPLACE, LOCK=NONE，或 gh-ost、pt-online-schema-change，并在业务低峰执行

主从延迟¶

主从延迟的表现是写入后立刻读取却读不到新数据。SHOW REPLICA STATUS 的 Seconds_Behind_Source 可以查看延迟。

对于必须读到最新数据的场景，写入后的关键读要强制走主库。

容量问题¶

误操作恢复¶

误删数据时，先恢复全量备份到临时实例，再回放 binlog 到误操作之前的位置（PITR），确认无误后把数据导回线上。row 格式的 binlog 也可以用 binlog2sql、MyFlash 等工具生成反向 SQL。

预防措施：定期演练备份恢复；开启 sql_safe_updates，拒绝没有 WHERE 或 LIMIT 的更新；保留延迟从库，为发现问题留出时间。

FAQ¶

SQL 执行¶

索引¶

事务¶

MVCC¶

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

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

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

快照读与当前读：

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

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

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

日志¶

评论