WangQuan's Blog

Appearance

MySQL ​

这篇文章按技术博客的方式整理 MySQL 的核心知识，重点放在 存储引擎、表设计、索引、事务、锁、MVCC、SQL 优化、架构扩展 这些真正影响系统设计和性能表现的主题上。

如果你想单独系统学习 MySQL 索引，可以直接阅读独立专题：

下文里带 🌟 的小节，表示这些主题在后端开发里更常碰到，也更值得单独展开理解。

如果把 MySQL 看成一个工程系统，而不只是一个“能执行 SQL 的数据库”，那它大致在解决四类问题：

  1. 以结构化方式存储和组织数据
  2. 在并发读写下维持一致性
  3. 在有限资源下尽量提升查询和更新效率
  4. 在数据量和请求量增长后继续扩展

1. MySQL 适合解决什么问题 ​

MySQL 是典型的关系型数据库，适合以下场景：

  1. 数据结构相对清晰，实体关系明确
  2. 需要事务保障，不能接受明显的数据不一致
  3. 查询条件、排序和聚合模式相对稳定
  4. 既要考虑读性能，也要考虑更新和维护成本

它并不是所有数据场景的通用答案。对于超高吞吐日志写入、极度灵活的文档结构、复杂图关系遍历，往往会有更贴合的系统。但在大多数业务系统里，MySQL 仍然是最稳妥、最成熟的基础设施之一。

2. 存储引擎 ​

MySQL 的存储引擎决定了数据是怎么存的、事务怎么做、锁怎么加、崩溃后怎么恢复。现在实际业务中最常见的是 InnoDB，MyISAM 更多是历史知识。

2.1 InnoDB ​

InnoDB 是当前默认且最主流的存储引擎，核心特点包括：

  1. 支持事务
  2. 支持行级锁
  3. 支持崩溃恢复
  4. 支持 MVCC
  5. 主键索引是聚簇索引

它适合绝大多数在线业务系统，因为这些系统几乎都会同时关心并发控制、一致性和恢复能力。

2.2 MyISAM ​

MyISAM 的特点主要是：

  1. 不支持事务
  2. 锁粒度偏粗
  3. 更偏早期读多写少场景

它的历史价值大于现实价值。只要系统对一致性和并发写入稍微有要求，通常都会优先选择 InnoDB。

2.3 为什么现在大多数场景优先 InnoDB ​

原因并不复杂：现代业务系统几乎默认需要事务、细粒度锁和崩溃恢复，而这三点刚好是 InnoDB 的核心能力。

从工程角度看，InnoDB 不是“某些特殊场景下更强”，而是“默认更适合作为业务数据库的底座”。

3. 表设计 ​

一张表设计得好不好，影响的不只是“看起来规不规范”，还会影响：

  1. 数据冗余程度
  2. 更新是否容易出错
  3. 查询是否容易命中索引
  4. 后续扩展和维护的复杂度

3.1 三范式 ​

三范式是关系型建模的基本规则。它的目标不是为了追求教条式整洁，而是为了让数据归属更清楚，避免更新异常。

第一范式 ​

第一范式要求字段保持原子性，一个字段只表达一个值。

例如：

  1. 不要把多个手机号塞进同一个字段
  2. 不要把多个标签硬编码成一个逗号分隔字符串

第一范式解决的是“字段不可拆、查询困难、维护混乱”的问题。

第二范式 ​

第二范式可以直接记成：消除部分依赖。

更完整地说，第二范式要求非主键字段必须完全依赖主键，而不是只依赖联合主键的一部分。

它主要处理联合主键场景下的部分依赖问题。例如选课表以 (student_id, course_id) 作为联合主键时，student_name 只依赖 student_id，course_name 只依赖 course_id，这就不符合第二范式。

第三范式 ​

第三范式可以直接记成：消除传递依赖。

更完整地说，第三范式要求非主键字段不要依赖其他非主键字段，也就是非主键字段要直接依赖主键，而不是通过另一个非主键字段间接依赖主键。

例如员工表里同时保存 dept_id 和 dept_name，而 dept_name 本质上依赖的是 dept_id，这时更自然的做法通常是拆成独立的部门表。

3.2 为什么真实系统不一定严格死守三范式 ​

三范式解决的是一致性和维护成本问题，但真实系统还要考虑读性能。

如果每次查询都要连很多张表，系统可能会出现：

  1. SQL 复杂
  2. 查询延迟变高
  3. 接口响应不稳定

因此工程上常见的做法是：

  1. 核心数据按范式建模
  2. 对读性能敏感的链路做适度反范式
  3. 对冗余字段建立明确的同步策略

例如：

  1. 订单表冗余下单时的商品名称和价格快照
  2. 报表系统维护汇总表
  3. 列表页维护少量展示型冗余字段

反范式不是随意复制数据，而是带约束的性能优化手段。

3.3 字段设计的基本原则 ​

除了范式，字段本身的设计也很重要。

通常会遵循这些原则：

  1. 类型尽量精确，不要无意义放大
  2. 能用整数就尽量不用字符串表示数值
  3. 能拆冷热字段就不要所有内容都堆在一张大宽表里
  4. 尽量避免把巨大文本字段放进高频查询表的核心行中
  5. 默认值、非空约束、唯一约束要尽量表达业务语义

例如“状态”字段用 tinyint 通常比用长字符串更经济；高频列表页不需要的大文本内容，往往更适合拆到扩展表中。

4. 主键设计与生成策略 ​

在 InnoDB 中，主键远不只是“唯一标识”。因为主键索引本身就是聚簇索引，表数据会按主键组织存储，所以主键会直接影响：

  1. 插入行为
  2. 页分裂概率
  3. 二级索引大小
  4. 查询回表成本

4.1 自增主键 ​

最常见的主键形式是：

sql
id bigint primary key auto_increment

它的优点非常直接：

  1. 简单
  2. 单机下天然唯一
  3. 趋势递增，顺序插入友好
  4. 主键较短，二级索引占用更可控

缺点也很明显：

  1. 多库多表时不天然全局唯一
  2. 容易暴露业务增长规律
  3. 在分布式场景下不适合作为统一 ID 方案

对于单库单表或单体系统，自增主键通常是最稳妥的默认选择。

4.2 UUID ​

UUID 的优点是：

  1. 去中心化
  2. 容易跨服务生成
  3. 全局唯一性强

但它直接作为 MySQL 主键时往往问题更多：

  1. 值较长
  2. 常见 UUID 无序或弱有序
  3. 对 B+ 树插入不友好
  4. 容易导致页分裂和索引膨胀

UUID 更适合作为业务 ID、对外暴露的唯一标识，或者在确有需要时用二进制压缩存储，而不是默认拿来做 InnoDB 的聚簇主键。

4.3 雪花算法 ID ​

雪花算法的目标是在分布式系统中生成全局唯一、趋势递增的 64 位整数 ID。它通常由以下几部分组成：

  1. 时间戳
  2. 机器标识
  3. 序列号

它兼顾了两个重要目标：

  1. 不依赖数据库生成
  2. 比 UUID 更适合索引结构

缺点主要在实现层面：

  1. 需要处理机器号分配
  2. 需要关注时钟回拨
  3. 需要额外维护发号逻辑

如果系统是典型分布式架构，雪花算法通常比 UUID 更适合做数据库主键。

4.4 号段模式 / 发号器 ​

号段模式的思路是由数据库或专门的发号服务统一分配一段一段的 ID，应用本地消费这一段 ID。

它的优点是：

  1. 统一可控
  2. 容易做趋势递增
  3. 适合大型系统统一管理 ID 规则

缺点是：

  1. 需要额外组件
  2. 发号服务可能成为瓶颈
  3. 需要特别关注可用性

这类方案常见于平台级系统或公司内部统一基础服务。

4.5 业务字段做主键 ​

有时看起来很自然的字段，例如手机号、订单号、学号、身份证号，并不适合作为聚簇主键。

原因通常有：

  1. 字段较长
  2. 字段可能变更
  3. 二级索引会随主键一起变大
  4. 某些字段有隐私风险

更常见的做法是：

  1. 使用独立代理主键作为聚簇主键
  2. 对业务唯一字段建立唯一索引

4.6 主键设计的实践原则 ​

如果没有特别强的业务限制，通常会优先考虑以下原则：

  1. 主键尽量短
  2. 主键尽量稳定，不要轻易变更
  3. 主键尽量趋势递增，减少随机插入
  4. 主键与业务语义适当解耦

5. 索引 ​

索引是 MySQL 性能优化的核心主题之一。它的本质是“用额外空间换时间”，通过维护有组织的数据结构，缩小查询时需要扫描的数据范围。

5.1 索引是什么 ​

如果没有索引，数据库往往需要全表扫描；如果索引设计合理，数据库就可以直接定位目标区间，甚至直接从索引中返回结果。

索引的价值不只是“查得更快”，它还影响：

  1. 排序成本
  2. 分组成本
  3. 联表效率
  4. 回表次数

5.2 🌟 为什么 InnoDB 常说 B+ 树索引 ​

InnoDB 的主流索引结构是 B+ 树。它特别适合数据库场景，主要原因有三点：

  1. 节点分叉多，树高低，磁盘 I/O 更少
  2. 叶子节点有序，范围查询效率高
  3. 能同时兼顾等值查询、排序和区间扫描

5.2.1 什么是 B+ 树 ​

B+ 树是一种多路平衡查找树。

可以抓住三个结构特征：

  1. 非叶子节点主要存 key 和指针，不直接保存完整数据
  2. 数据通常集中在叶子节点
  3. 叶子节点之间通常按顺序连接

这种结构让单个节点能容纳更多索引项，因此树的高度更低。对于数据库来说，真正昂贵的往往不是比较次数，而是磁盘 I/O 次数，所以“更矮的树”非常重要。

5.2.2 B 树和 B+ 树的区别 ​

B 树 的非叶子节点也可能存数据，而 B+ 树 的非叶子节点通常只做导航，数据集中在叶子节点。

这带来三个实际差异：

  1. B+ 树单个节点能放更多 key，树更矮
  2. B+ 树叶子节点天然有序，更适合范围查询
  3. B+ 树访问路径更统一，更适合数据库这种大量随机查询和区间扫描场景

5.2.3 为什么不用红黑树 ​

红黑树是二叉平衡树，每个节点只有两个方向。数据量一大，树高会明显增加，查询路径变长，对磁盘索引来说就意味着更多随机 I/O。

数据库索引更需要的是“低树高”，而不是“纯粹的平衡”。这正是 B+ 树比红黑树更适合磁盘场景的原因。

5.2.4 为什么不用哈希做通用索引 ​

哈希索引很擅长等值查询，但不擅长：

  1. 范围查询
  2. 排序
  3. 联合索引最左匹配
  4. 区间扫描

而这些刚好都是数据库的常见需求。B+ 树虽然在单点等值查找上不一定绝对最快，但整体能力更均衡，因此更适合作为通用索引结构。

5.3 🌟 聚簇索引和二级索引 ​

在 InnoDB 中，主键索引通常就是聚簇索引，表数据本身按主键组织存储。

这意味着：

  1. 聚簇索引的叶子节点存放完整行数据
  2. 二级索引的叶子节点通常存放主键值

因此通过二级索引查到主键后，再去聚簇索引取完整行数据的过程，就叫回表。

5.3.1 🌟 回表为什么会慢 ​

回表本质上是多做了一次查找：

  1. 先查二级索引
  2. 再查聚簇索引

如果结果集很大，或者查询非常频繁，回表的额外 I/O 和缓存压力就会被放大。

5.4 🌟 联合索引和最左前缀 ​

联合索引是多个列组成的一个索引，例如 (a, b, c)。

它的组织方式决定了一个重要规则：最左前缀原则。

简单说就是，索引的利用通常要从最左边开始连续匹配。如果跳过前面的列，后面的列往往很难被单独充分利用。

这也是为什么联合索引字段顺序如此重要。

5.5 🌟 覆盖索引 ​

如果查询需要的字段都已经包含在索引里，数据库就不需要回表，这种情况通常称为覆盖索引。

覆盖索引的价值在于：

  1. 减少一次聚簇索引查找
  2. 降低随机 I/O
  3. 在高频读取场景中更稳定

5.6 索引设计原则 ​

索引设计的核心不是“字段都加一遍”，而是让索引尽量贴合真实查询路径，同时控制写入成本和空间成本。

5.6.1 优先服务高频查询 ​

索引最应该优先服务以下字段：

  1. where 中的高频过滤字段
  2. join 关联字段
  3. order by 和 group by 高频字段

如果某个字段几乎只用于展示，很少参与查询，通常没必要单独建立索引。

5.6.2 优先考虑区分度高的字段 ​

区分度越高，字段越能缩小扫描范围，索引收益通常越大。

像用户 ID、订单号这类字段通常区分度高；性别、布尔开关、删除标记这类字段区分度通常低。低区分度字段并不是绝对不能建索引，但单独建索引的收益往往有限，要结合查询模式判断。

5.6.3 多条件查询优先考虑联合索引 ​

如果多个条件经常一起出现，与其分别建多个单列索引，不如优先考虑联合索引。

原因是：

  1. 联合索引更容易贴合查询模式
  2. 多个单列索引未必能组合出最优执行计划
  3. 联合索引还能兼顾排序和覆盖索引设计

5.6.4 联合索引字段顺序很重要 ​

字段顺序通常要综合考虑：

  1. 高频过滤条件
  2. 区分度
  3. 排序需求
  4. 分组需求
  5. 最左前缀原则

这不是固定公式，而是要结合实际 SQL 模式设计。

5.6.5 索引不是越多越好 ​

索引除了提升查询，也会带来成本：

  1. 插入更慢
  2. 更新更慢
  3. 删除更慢
  4. 占用更多空间
  5. 增加维护复杂度

因此索引的原则应当是“少而精”，而不是“多多益善”。

5.6.6 索引字段尽量短、尽量稳定 ​

索引字段越长，单页可容纳的索引项越少，缓存命中率和 I/O 表现往往越差。

稳定字段也更适合建索引，因为字段频繁变更意味着索引结构要频繁维护。

5.6.7 SQL 写法要让索引用得起来 ​

建了索引不等于一定会使用。常见导致索引利用变差的情况包括：

  1. 没按最左前缀使用联合索引
  2. 对索引列做函数或表达式计算
  3. 发生隐式类型转换
  4. 范围条件后续列利用受限
  5. 查询选择性太差

索引设计和 SQL 写法必须配套考虑。

6. 事务与隔离级别 ​

事务解决的问题是：一组操作要么全部成功，要么全部失败，并且在并发环境下仍然维持合理的一致性。

6.1 ACID ​

事务通常用 ACID 描述：

  1. 原子性：操作不可分割，要么全部成功，要么全部失败
  2. 一致性：事务前后数据满足约束和业务规则
  3. 隔离性：并发事务之间互不干扰到不可接受程度
  4. 持久性：事务提交后结果不会丢失

其中最容易被误解的是“一致性”。一致性不是“数据库自动帮你保证所有业务都对”，而是指事务执行前后，数据要仍然满足既定约束。

6.2 🌟 隔离级别 ​

并发事务之间不可能既完全隔离又毫无性能成本，因此数据库需要在一致性和性能之间做取舍。

常见隔离级别包括：

  1. 读未提交
  2. 读已提交
  3. 可重复读
  4. 串行化

InnoDB 默认通常是 可重复读。

如果把这 4 种隔离级别先压缩成一句话，可以这样理解：

  1. 隔离级别越低，并发越好，但读到不一致数据的概率越高
  2. 隔离级别越高，一致性越强，但并发代价通常也越大

6.2.1 四种隔离级别对比表 ​

隔离级别英文能否读到未提交数据是否可能不可重复读是否可能幻读特点
读未提交Read Uncommitted是是是隔离性最弱，并发高，但几乎很少作为业务默认选择
读已提交Read Committed否是是只能读到已提交数据，很多数据库把它作为默认级别
可重复读Repeatable Read否否理论上仍可能，InnoDB 里会结合 MVCC 和锁尽量控制InnoDB 默认级别，兼顾一致性和并发
串行化Serializable否否否隔离性最强，但并发性能最差

这张表最适合先建立全局认识，但真正理解时还要注意两个点：

  1. 表里的“是否可能”说的是理论并发异常
  2. InnoDB 在 RR 下不是只靠教科书定义工作，它还会结合 MVCC、间隙锁、临键锁等机制控制结果

6.2.2 读未提交 ​

Read Uncommitted 是隔离性最弱的级别。

它允许一个事务直接读到另一个事务还没提交的数据。

这意味着最典型的问题就是：

  1. 脏读
  2. 不可重复读
  3. 幻读

它的好处是阻塞少、并发高，但代价是读到的数据很可能还会被回滚，所以在真正业务系统里通常很少直接作为默认选择。

6.2.3 读已提交 ​

Read Committed 的核心变化是：一个事务只能读到其他事务已经提交的数据。

它解决了脏读问题，但仍然可能出现：

  1. 不可重复读
  2. 幻读

原因并不复杂：

  1. 你第一次读时，别的事务还没提交
  2. 你第二次读时，别的事务已经提交
  3. 同一事务里两次读到的结果就可能不同

很多数据库把 RC 作为默认隔离级别，因为它在一致性和并发之间做了一个比较常见的平衡。

6.2.4 可重复读 ​

Repeatable Read 的目标是：同一事务里，多次读取同一批数据时，结果尽量保持一致。

在这个级别下：

  1. 脏读不会出现
  2. 不可重复读通常不会出现
  3. 幻读问题在 InnoDB 中会结合 MVCC 和锁机制处理

这也是为什么很多人学 MySQL 时会觉得：

RR 不只是一个定义，它背后还连着 MVCC、间隙锁和临键锁。

如果只是普通 select，更多依赖的是 MVCC 的一致性视图；如果是当前读，例如 select ... for update，则还会进一步依赖锁机制。

6.2.5 串行化 ​

Serializable 可以理解成最严格的隔离级别。

它的目标接近于：让并发事务看起来像一个一个串行执行。

这样做的结果是：

  1. 脏读不会发生
  2. 不可重复读不会发生
  3. 幻读也不会发生

但代价同样明显：

  1. 锁冲突更多
  2. 吞吐更低
  3. 等待更明显

所以它更适合那些一致性要求非常强、并发量又相对可控的场景，而不是普通业务系统的默认首选。

6.2.6 怎么理解它们之间的区别 ​

如果不用硬背定义，也可以按“读到的数据有多新、又有多稳定”来理解：

  1. RU：数据最新，但可能最新得过头，连没提交的都能读到
  2. RC：只看已提交数据，但同一事务里前后两次读可能变
  3. RR：同一事务里的快照通常更稳定
  4. Serializable：最稳定，但并发代价也最高

6.2.7 一般怎么选 ​

真实系统里并不是“级别越高越好”，而是看业务需求。

通常可以这样理解：

  1. 如果主要追求并发，又能接受同一事务中前后读取不完全一致，RC 是比较常见的平衡点
  2. 如果希望同一事务里的读视图更稳定，RR 往往更合适
  3. 如果一致性要求极强，可以考虑 Serializable，但要接受更高的并发成本

对 MySQL / InnoDB 来说，默认的 RR 之所以常被采用，就是因为它在很多业务场景下能提供一个相对稳妥的平衡。

6.3 🌟 并发读写中的三个经典问题 ​

脏读 ​

一个事务读到了另一个事务尚未提交的数据。

不可重复读 ​

同一事务中，两次读取同一行数据，结果不同。

幻读 ​

同一事务中，两次按条件查询，发现结果集行数发生变化，好像“多出来几行”。

更具体一点说，幻读通常不是“同一行的值变了”，而是：原本满足条件的一批记录里，突然又冒出了新的记录，或者少了几条记录。

例如事务 A 先执行：

sql
select *
from orders
where amount > 100;

第一次查出来一共 10 行。

这时事务 B 插入了一条新订单：

sql
insert into orders (amount) values (200);
commit;

如果事务 A 再次执行同样的范围查询：

sql
select *
from orders
where amount > 100;

结果变成了 11 行。

站在事务 A 的视角看，就像结果集中“凭空多出来一行”，这就是幻读。

🌟 幻读和不可重复读的区别 ​

这两个概念很容易混。

可以这样区分：

  1. 不可重复读更强调“同一行数据前后值变了”
  2. 幻读更强调“满足条件的记录集合变了”

比如：

  1. 你第一次查某个用户余额是 100
  2. 第二次查变成 200
  3. 这是不可重复读

而如果：

  1. 你第一次查 amount > 100 的订单有 10 条
  2. 第二次查变成 11 条
  3. 这是幻读

所以一个更好记的方式是：不可重复读关注单行内容变化，幻读关注结果集范围变化。

这三类问题本质上反映的是并发事务在不同隔离级别下看到的数据视图不同。

7. 锁与并发控制 ​

事务要落地，最终仍然离不开锁。

7.1 🌟 行锁 ​

行锁锁住的是具体记录，粒度细，并发能力更好，是 InnoDB 处理高并发更新的基础能力之一。

7.2 表锁 ​

表锁锁住整张表，粒度粗，实现简单，但并发能力差。它适合某些简单或特殊场景，但并不适合高并发业务表。

7.3 🌟 间隙锁 ​

间隙锁锁住的是索引记录之间的区间，而不是某一条具体记录。它的价值主要在于配合事务隔离级别控制并发插入，减少幻读问题。

7.4 🌟 临键锁 ​

临键锁可以理解为“记录锁 + 间隙锁”的组合，它在 InnoDB 处理范围更新和防止幻读时非常重要。

7.5 🌟 为什么会发生死锁 ​

死锁的本质是多个事务互相持有对方需要的资源，并且都在等待。

常见原因包括：

  1. 加锁顺序不一致
  2. 事务范围过大
  3. 索引设计不好，导致锁范围扩大
  4. 范围更新和范围查询交错

死锁不是数据库“坏掉了”，而是并发控制中的自然现象。优化的关键不是幻想完全消灭死锁，而是降低死锁概率并做好重试和回滚处理。

一个最常见的死锁例子 ​

假设有两个事务：

  1. 事务 A 先更新订单 id = 1，再更新订单 id = 2
  2. 事务 B 先更新订单 id = 2，再更新订单 id = 1

这时就可能出现：

  1. 事务 A 已经持有 id = 1 的锁，等待 id = 2
  2. 事务 B 已经持有 id = 2 的锁，等待 id = 1
  3. 两边都不释放，于是形成循环等待

这就是最典型的死锁场景。

怎么尽量避免死锁 ​

虽然不能保证绝对没有死锁，但通常可以通过下面这些方式显著降低概率：

  1. 固定加锁顺序
    比如多个事务都统一按主键从小到大更新，避免 A 先锁 1 再锁 2，而 B 先锁 2 再锁 1。

  2. 尽量让事务更短
    事务里不要混入无关逻辑，不要把网络调用、复杂计算、长时间等待放进事务中，减少锁持有时间。

  3. 让 SQL 更精准命中索引
    索引不合适时，锁范围可能被放大，原本只想锁少量记录，最后却锁住更大范围。

  4. 避免大范围更新和扫描
    范围查询、范围更新、批量修改更容易和别的事务形成锁冲突，能拆批时尽量拆批。

  5. 先查后改时保持一致访问路径
    特别是账户转账、库存扣减这类双边更新场景，要尽量统一访问顺序和 SQL 形态。

发生死锁后怎么处理 ​

真正的工程重点不只是预防，还包括“发生之后怎么办”。

常见处理思路是：

  1. 让数据库检测死锁并主动回滚其中一个事务
    InnoDB 检测到死锁后，通常会选择一个事务作为代价较小的“牺牲者”回滚，这样系统不会无限卡住。

  2. 应用层捕获死锁异常并做有限重试
    因为很多死锁是并发时序问题，重试一次到几次后往往就能成功。

  3. 记录死锁日志，回看 SQL 和锁顺序
    如果死锁反复出现，通常说明表结构、索引或事务设计需要调整，而不是只靠重试掩盖问题。

处理死锁时要注意什么 ​

  1. 不要无限重试
    否则容易把局部锁冲突放大成系统抖动。

  2. 重试前最好有短暂退避
    让并发峰值错开，而不是立刻再次撞上同一把锁。

  3. 关键链路要做到幂等
    否则事务回滚后重试，可能把业务结果做重。

一个更实用的理解方式 ​

可以把死锁问题拆成两层：

  1. 数据库层：负责检测死锁并回滚一个事务，避免永久等待
  2. 应用层：负责捕获异常、决定是否重试，并从业务上保证重试安全

所以真正成熟的处理方式通常不是：我把 SQL 写出来就结束了。

而是：数据库负责打破死锁，应用负责兜住失败并安全重试。

8. 🌟 MVCC ​

MVCC 是 Multi-Version Concurrency Control，即多版本并发控制。

它的核心目标是：在很多读场景下，不通过简单粗暴的锁把所有读写都互相阻塞住，而是让读操作读取一个一致性快照。

如果你想把 Read View、版本链、RC 和 RR 的差异系统串起来看，可以直接阅读独立专题：

8.1 MVCC 在解决什么问题 ​

如果没有多版本机制，读操作和写操作会更频繁地互相阻塞，这会直接影响吞吐和响应时间。

MVCC 的价值在于：

  1. 降低读写冲突
  2. 提升并发能力
  3. 在一定隔离级别下维持一致性视图

8.2 🌟 MVCC 和锁的关系 ​

MVCC 并不是“不要锁了”，而是“在合适场景下尽量少用阻塞式读写冲突控制”。

通常可以这样理解：

  1. 快照读更多依赖 MVCC
  2. 当前读仍然可能依赖锁

两者是协同关系，而不是替代关系。

9. SQL 优化 ​

SQL 优化不是单一动作，而是一套定位和改进过程。

如果一条 SQL 很慢，原因可能来自：

  1. 表结构
  2. 索引设计
  3. SQL 写法
  4. 数据量级
  5. 执行计划
  6. 更上层的系统架构

9.1 常见慢 SQL 原因 ​

最常见的原因包括：

  1. 没有索引
  2. 索引没有命中
  3. 返回行数太多
  4. 排序和分组开销大
  5. 联表不合理
  6. 深分页
  7. 回表太多
  8. 字段过宽

9.2 SQL 优化的一般思路 ​

一个比较稳妥的过程通常是：

  1. 先通过慢查询日志、监控或 APM 找到慢 SQL
  2. 用 EXPLAIN 看执行计划
  3. 看是否命中索引、扫描行数是否合理
  4. 判断瓶颈是索引问题、写法问题还是数据规模问题
  5. 再决定是改 SQL、改索引、改表结构，还是做架构级优化

优化的关键不是“立刻改”，而是先定位真正的瓶颈。

9.3 索引层优化 ​

索引层优化通常会关注：

  1. 是否给高频过滤字段建立了合适索引
  2. 是否利用了联合索引
  3. 是否尽量走覆盖索引
  4. 是否发生大量回表
  5. 是否因为函数、表达式或类型转换导致索引失效

9.4 SQL 写法优化 ​

SQL 本身的写法会直接影响执行计划。

常见优化点包括：

  1. 避免 select *
  2. 控制返回字段和结果集大小
  3. 避免不必要的排序和分组
  4. 尽量减少复杂嵌套子查询
  5. 把不利于索引的函数调用改写为范围查询

例如：

sql
where date(create_time) = '2026-08-05'

通常不如：

sql
where create_time >= '2026-08-05 00:00:00'
  and create_time < '2026-08-06 00:00:00'

更容易利用索引。

9.5 join 优化 ​

联表查询的关键关注点通常是：

  1. 关联字段是否有索引
  2. 驱动表是否尽量小
  3. 返回字段是否可控
  4. 是否存在不必要的大表关联

对于复杂链路，工程上有时会选择：

  1. 拆分查询
  2. 冗余字段
  3. 维护汇总表或宽表

9.6 分页优化 ​

深分页是常见的慢 SQL 来源。

例如：

sql
select * from user order by id limit 100000, 20;

这种语句的问题在于，数据库通常仍然要扫描并跳过前面的大量记录。

常见优化方式包括：

  1. 使用主键或有序索引进行游标分页
  2. 使用“上次最大 ID”继续翻页
  3. 避免无限深的随机页码跳转
  4. 必要时用延迟关联减少回表成本

9.7 表结构层优化 ​

优化不只发生在 SQL 层，有时慢的根因在于表本身太重。

常见方向包括：

  1. 缩短字段长度
  2. 拆分大字段
  3. 避免过宽表
  4. 把冷热数据分离
  5. 调整冗余策略

9.8 架构层优化 ​

当数据量和并发量继续增长，只改单条 SQL 往往已经不够。

这时通常会考虑：

  1. 缓存
  2. 读写分离
  3. 分库分表
  4. 历史数据归档
  5. 预计算和汇总表

SQL 优化解决的是单条访问路径问题，架构优化解决的是整体系统压力问题。

10. 🌟 EXPLAIN ​

EXPLAIN 是分析 SQL 执行计划的核心工具。它不是真正执行 SQL，而是告诉我们“优化器准备怎么执行这条语句”。

10.1 🌟 EXPLAIN 在看什么 ​

通常最关心的不是所有字段，而是以下几个：

  1. type
  2. key
  3. possible_keys
  4. rows
  5. Extra

10.2 type ​

type 表示访问类型，可以理解为“这条 SQL 的访问方式是否足够高效”。

常见值从差到优大致可以这样理解：

  1. ALL：全表扫描
  2. index：全索引扫描
  3. range：范围扫描
  4. ref：普通索引查找
  5. eq_ref：唯一索引精确匹配
  6. const：常量级别访问

不是所有 ALL 都一定错误，但它通常意味着值得警惕。

10.3 key 和 possible_keys ​

possible_keys 表示优化器认为“理论上可以考虑”的索引。

key 表示最终实际选择的索引。

两者一起看，可以知道：

  1. 建的索引是否真的可用
  2. 优化器最终为什么没有选某个索引

10.4 rows ​

rows 表示预计扫描的行数。

这个值不一定绝对精确，但它非常有参考价值。优化时除了看“有没有用索引”，更要看“扫描行数有没有降下来”。

10.5 Extra ​

Extra 主要用于观察额外开销。

常见值包括：

  1. Using index
  2. Using where
  3. Using filesort
  4. Using temporary

其中尤其值得关注的是：

  1. Using filesort：排序无法直接借助索引
  2. Using temporary：可能用了临时表

这两个经常意味着额外代价。

10.6 用 EXPLAIN 分析 SQL 的一个简单顺序 ​

实际排查时，通常可以按这个顺序看：

  1. 看 type，有没有全表扫描
  2. 再看 key，有没有用到预期索引
  3. 再看 rows，扫描量是否过大
  4. 最后看 Extra，有没有额外排序、临时表、回表等问题

11. 读写分离、主从复制与分库分表 ​

当单机能力逐渐接近上限时，数据库设计会从“单库优化”进入“架构扩展”阶段。

这一层更适合先建立演进顺序，而不是在总览页里把每种方案都再展开一遍。

可以直接这样理解：

  1. 主从复制负责把变更同步到副本
  2. 读写分离负责把读流量从主库分散出去
  3. 分库分表负责进一步拆解单点容量和写入瓶颈

这三类方案的典型代价也要同时记住：

  1. 主从复制和读写分离会引入复制延迟、一致性治理和故障切换问题
  2. 分库分表会把路由、跨分片查询、事务和扩容迁移复杂度一起带进系统

如果要继续深入，建议直接进入对应专题：

12. 一份更实用的 MySQL 优化清单 ​

如果把前面的内容压成一份实践清单，可以从这些问题开始检查：

  1. 表结构是否清晰，是否存在明显的冗余或大宽表问题
  2. 主键是否足够短、稳定，并且对聚簇索引友好
  3. 高频查询是否有匹配的索引
  4. 联合索引顺序是否贴合查询模式
  5. 是否存在大量回表、深分页、额外排序或临时表
  6. SQL 是否有函数、表达式或类型转换导致索引失效
  7. 事务范围是否过大，锁粒度是否合理
  8. 是否需要缓存、读写分离、归档或分库分表来做更上层扩展

MySQL 的优化并不是某个单点技巧，而是存储结构、查询模式、并发控制和系统架构共同作用的结果。把这些关系看清楚，很多问题就会从“记忆题”变成“能推演的工程问题”。

基于 VitePress 构建的个人技术笔记。

Copyright © 2026 WangQuan's Blog