MySQL八股文

锁

mysql的锁可分为全局锁、表级锁、行级锁三类：

  • 全局锁
  • 表级锁
    • 表锁
    • 元数据锁
    • 意向锁
    • 自增锁（AUTO-INC Lock）
  • 行级锁
    • 记录锁
    • 间隙锁
    • 临键锁（Next-Key Lock）
    • 插入意向锁

（ps：这些锁按照加锁方式来分，可分为共享锁和排他锁）

全局锁

对整个数据库实例加锁，使整个数据库只读，通常用于全库备份

表级锁

  • 表锁：锁住整张表，分为表读锁和表写锁，阻塞其他线程对该表的读写操作，早期MyISAM存储引擎默认使用，操作简单但并发性能低
  • 元数据锁：当我们对数据库表进行操作时，会自动给这张表加元数据锁。为了保护表结构等元数据，避免CRUD操作与DDL语句发生冲突（例如避免用户正在更新某条数据的时候表结构的字段发生了变化）
  • 意向锁：当一个事务对表中的行添加共享锁或排他锁时，InnoDB存储引擎会自动添加表级意向锁，之后当另一个事务尝试添加表级共享锁或排他锁时，无需遍历行检查是否存在行级锁，只需检查表上是否存在意向锁
  • 自增锁（AUTO-INC Lock）：在向含有自增字段的表中并发插入数据时会自动添加自增锁，用于保证在并发插入的条件下，自增值能连续且唯一的生成。可以通过配置innodb_autoinc_lock_mode（三种级别）来控制自增锁的行为：
    • 0 每次insert都加表级自增锁，串行化insert
    • 1 用轻量锁快速分配id，真正插入前就释放（但不能用于未知数量的批量插入 如insert...select...此时需要用 0）
    • 2 允许多个事务交错分配id，但可能造成自增值不连续

行级锁

  • 记录锁：也称为行锁，锁住单行记录，修改和删除时添加排他记录锁，读取时添加共享记录锁
  • 间隙锁：锁住记录之间的空隙，间隙锁之间是兼容的，可以锁住相同的空隙，目的是阻塞其他事务在锁住的间隙中插入记录
  • 临键锁（Next-Key Lock）：即为记录锁 + 间隙锁，锁住的是行与行之间的间隙，是**InnoDB默认的行锁类型**
  • 插入意向锁：执行insert操作时产生，表示事务想要在某个间隙插入记录的意图，不同插入意向锁如果插入的位置不同则不会冲突，可以同时持有。但如果插入的间隙已经被间隙锁/临键锁锁住了，则会阻塞。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的是一个点，是一种特殊的间隙锁

MVCC

简介

MVCC（multi-version concurrency control）即多版本并发控制，该机制的思想读取时是不加锁，而是通过保存数据的多个版本，让读操作可以读取数据的旧版本，从而解决读写的并发问题

  • 优点：提高读写并发性，降低竞争锁的开销
  • 缺点：需要额外的存储开销来存储旧版本的数据，需要后台线程清理不需要的旧版本数据

工作原理

核心机制有三条：

  1. 版本链

    InnoDB会在数据库中的每行数据后面添加两个隐藏列：

    • 事务id：用来记录最近一次修改该行的事务的id
    • pointer：指针指向undo log中该行数据的旧版本记录，不同的版本通过链表连接起来，形成版本链

    undo log是innoDB的回滚日志，它不仅用于事务的回滚，还用于存储数据的旧版本。当一个事务修改数据时，会将数据的旧版本写入undo log，并修改pointer指向该旧版本

  2. 读视图（read view）

    在可重复读隔离级别下，read view是在每个事务开始时生成的（读已提交隔离级别下，read view是在每次执行select时生成），用于记录事务的活跃状态

  3. 可见性判断

    当事务读取一行数据时，他会获取该数据的最新版本，之后检查该版本数据的事务id，根据read view中记录的事务活跃状态，判断在当前事务下，该版本的数据是否可见（通过比较id大小）。如果不可见，事务会沿着版本链向上回溯，查找更旧的版本，直到找到第一个可见的版本

pasted-image-1779107715491.webp

例如：

原来数据库中有一条记录id = 1, balance = 100，有三个事务：A(id = 100) B(id = 101) C(id = 102)

如果事务B先修改数据为balance = 200，但未提交，此时事务A开始查询该条数据，事务A在开始时会创建read view，根据id判断事务B和事务C都是活跃的，意味着事务A启动的时候事务B和事务C还没提交，他们产生的结果对事务A应该是不可见的，事务A读取数据，发现balance = 200, trx_id = 101，为不可见的数据，于是根据pointer指向的版本链找到旧版本，例如找到了balance = 100, trx_id = 90，id为90的事务在事务A开始前就已经提交了，因此对事务A可见，于是事务A读取到的结果为100

当事务提交/回滚之后，该事务创建的read view就失效了，mysql后台的purge线程会扫描undo log，清理那些不再被任何活跃事务的read view引用的旧版本数据

事务

事务的四大特性

ACID：

  • 原子性（Atomic）：事务视作一个不可分割的最小单位，事务中的操作要么全部完成，要么全部失败（中间失败需要触发回滚，回滚到事务开始前到状态） ｜ 解决：undo log 回滚
  • 一致性（Consistency）：事务操作前后，数据库的状态需保持一致性，例如把A的库存转移到B，事务执行之后，不能出现A的库存扣减，但B的库存未增减的中间状态。此外需要满足数据库的所有完整性约束，例如主键/唯一/非空等约束 ｜ 解决：a + i + d
  • 隔离性（Isolation）：多个事务可以并发执行，需要相互隔离，互不干扰 ｜ 解决：MVCC + 锁
  • 持久性（Durability）：事务一旦提交，数据库中的数据变化需要持久化保存，即使数据库系统故障重启也不能丢失 ｜ 解决：redo log，事务的提交会先写入持久化日志文件redo log，即使数据库崩溃重启，redo log中的记录也不会丢失，重启后的数据库可以通过读取redo log文件恢复已提交的日志

事务的隔离级别

事务的隔离级别有四种：

  • 读未提交
  • 读已提交
  • 可重复读
  • 串行化

  • 读未提交

    一个事务可以读取到另外一个事务未提交的数据，存在脏读问题

    脏读：例如事务B修改了某些数据，还未提交，事务A读取到了事务B的更改，之后事务B触发了回滚，那么事务A读到的就是没有用的“脏数据”

  • 读已提交

    一个事务只能读取到另外一个事务已经提交的数据，从而解决了脏读问题，但存在不可重复读问题

    不可重复读：例如事务A首次读取了某行数据，事务B更改了该行数据并提交了事务，之后事务A再次读取该行数据发现与首次读取的结果不同，同一个事务前后读取同一行数据但结果不同，称为“不可重复读”

  • 可重复读

    在一个事务中，多次读取同一行数据时结果始终保持一致，主要通过MVCC机制实现，首次读取时会创建read view，之后的读取采用快照读的方式，读取的结果都基于该read view，从而解决了不可重复读问题，但存在幻读问题

    幻读：例如事务A首次读取age >= 18的用户数据，读取到的行数为3行，事务B执行了insert/delete操作并提交，之后事务A再次读取age >= 18的用户数据，发现读取到的行数多了或少了几行，同一个事务在相同条件下前后读取到的记录行数不一样，称为“幻读”

    然而，MySQL的InnoDB存储引擎通过MVCC机制与临键锁（Next-Key Lock），在可重复读的隔离级别下，很大程度上解决了幻读问题，在工业上基本可以认为MySQL的可重复读隔离级别能够避免脏读、不可重复读、幻读问题

  • 串行化

    最高的隔离级别，所有并发的事务是完全隔离的，所有读写操作都通过加锁来实现，不会出现脏读、不可重复读、幻读问题。（事务是并发+锁，使得效果和串行执行的效果一样，并不是所有事务真的串行执行）

MySQL在可重复读隔离级别下如何解决幻读问题？

对于普通的select语句，即快照读，其他事务插入/删除的数据，根据MVCC机制，是对当前事务不可见的，因此读取到的是版本链中的旧版本数据，从而避免了幻读问题

对于select...for update，即当前读，必须读取最新数据，因此InnoDB使用临键锁（Next-Key Lock）来锁住行以及行之间的间隙，阻塞其他事务的插入/删除操作，从而避免了幻读问题

综上，MySQL的InnoDB在可重复读的隔离级别下，通过MVCC与临键锁结合的机制很大程度解决了幻读问题。

但是在某些违和情况下仍然存在幻读问题：

例如：事务A首次使用select快照读，第二次使用select...for update当前读，中间可能会有其他事务插入/删除了数据，导致前后读取的记录数不同，发生幻读问题。对于这种情况，可以选择在开启事务之后马上执行select...for update，使用当前读InnoDB会对记录添加临键锁，避免其他事务插入新的记录，临键锁会一直持有直到当前事务结束释放

索引

索引分类

按数据结构分类：

  1. B+树索引：底层使用B+树存储，所有数据都存储在叶子节点，叶子节点之间通过双向链表连接，便于排序和范围查询，非叶子节点仅存储索引键值，不存储实际数据，这使得非叶子节点可以存储更多的索引键值，从而降低树的高度，提高查询性能
  2. Hash索引：基于hash表实现，通过hash函数计算索引列的hash值，hash值映射到存储数据记录的指针，类似拉链法。等值查询理论上可达到O(1)，但不支持范围查询和排序。InnoDB使用自适应hash自动加速存储热点数据，用户无法手动创建hash索引
  3. 全文索引：针对文本内容搜索设置的索引，用于char/varchar/text中的关键词搜索，InnoDB支持，底层通常用倒排索引来匹配文本（即 关键词 -> 哪些文档包含它）

按物理存储分类：

除了主键索引之外的索引，称为二级索引，在InnoDB存储引擎中，主键索引是聚簇索引，二级索引是非聚簇索引

  1. 聚簇索引：索引的叶子节点直接保存完整的行数据，查询效率高，但插入和更新可能会造成数据行物理地址的移动，开销较大（不完全，需要分析会不会造成物理地址移动）。每个表只能拥有一个聚簇索引，在InnoDB存储引擎中，默认是主键索引；如果没有定义主键，则会选择第一个**unique not null索引**；如果也没有unique not null索引，InnoDB会隐式创建一个聚簇索引，此时手动创建的所有其他索引都是非聚簇索引
  2. 非聚簇索引：索引的叶子节点仅保存索引列和指针，通过指针指向数据实际存储的地址。在InnoDB中，这个指针通常是主键值。查找过程为先通过非聚簇索引找到对应的主键，再通过主键查找数据行。因此查询效率较低，但插入和更新通常更快（不完全，需要分析会不会造成物理地址移动）

按逻辑特性分类：

  1. 主键索引：唯一且非空的标识符，一个表只能有一个
  2. 普通索引
  3. 唯一索引：注意唯一索引列的值允许为空
  4. 联合索引：联合索引是在表的多个列上创建的索引，查询时遵循最左前缀匹配
  5. 空间索引：用于对地理空间数据进行索引，加速地址位置相关的查询操作

回表与覆盖索引

回表

例如name是二级索引（非聚簇索引）

1
select * from user where name = 'Tom';

第一步：先走二级索引 name -> id，获取到id = 1

第二步：根据主键id去查询完整数据

这个过程查了两次表，就叫做回表，回表会影响查询效率，可以用覆盖索引优化

覆盖索引

例如age是二级索引（非聚簇索引）

1
select id, name, age from user where age > 18;

查询时会发生回表，使用覆盖索引优化：

新建联合索引(age, name)，新建索引时会新建一颗B+树，这个树的叶子节点存放了索引值和id，即

1
(age, name, id)

再次查询

1
select id, name, age from user where age > 18;

可以直接在叶子节点拿到所需的完整数据，不需要再根据id回表查询，这就是覆盖索引

联合索引最左前缀匹配

例如存在(a, b, c)联合索引，联合索引底层会按照a -> b -> c的顺序排序，整体是全局有序，但b,c只在索引内部相对有序，因此必须从联合索引最左端的字段开始匹配才能高效利用索引

1
2
select * from user where a = 1 and b = 2 and c = 3	// 利用索引(a, b, c)
select * from user where a = 1 and c = 3 and b = 2	// 利用索引(a, b, c)

这两种方式都可以有效利用索引，MySQL的优化器会自动优化顺序

1
2
select * from user where a = 1 and b = 2	// 利用索引 (a, b)
select * from user where a = 1	// 利用索引(a)

这两种方式也可以有效利用索引，满足最左前缀匹配

1
select * from user where b = 2	// 无法有效利用索引

无法有效利用索引，跳过了a，b只相对于a局部有序，但全局是无序的

1
select * from user where a = 1 and c = 3	// 利用索引(a)

相当于只用到了索引a，跳过了b，c相对于a不是有序的

1
select * from user where a = 1 and b > 2 and c = 3	// 利用索引(a, b)

范围查询会破坏之后的索引效果，能利用到索引(a, b)，a, b内部是相对有序的，找到满足条件的b之后，会顺着叶子节点往后扫描，因此破坏了c的稳定顺序，无法高效利用索引c

在MySQL中，例如如果存在索引name，类型为char/varchar，那么该索引在B+树中的节点会按照类似字符串的字典排序（但默认不区分大小写），因此对like的匹配也可以走索引，同样满足最左前缀匹配

1
select name from user where name like 'ab%'

这种写法可以高效利用name索引

1
select name from user where name like '%ab%'

类似的，这种写法则无法高效利用name索引

什么时候需要创建索引

  1. 用于where条件查询的字段，例如select * from user where status = 1这里的status
  2. 用于join联表查询的逻辑外键
  3. 用于order by / group by的字段，如果查询结果需要排序和分组，利用索引的有序性可以加速操作，甚至某些情况下可以避免额外的排序和分组操作
  4. 数据量较大的表和经常被访问的表/列
  5. 创建联合索引，使用覆盖索引避免回表
  6. 慢查询日志中的慢sql

另外：unique的列，数据库会自动为这些列创建唯一索引。因为唯一约束依赖于索引的快速查找，否则每次插入都要全表扫描判断唯一性

创建联合索引时要注意索引的顺序，遵循最左前缀匹配，通常将高频查询的字段和等值查询字段放在前面，范围查询放后面

什么时候不需要创建索引

  1. where/order by/group by用不到的字段
  2. 频繁更新的字段，维护索引的B+树开销太大，影响性能
  3. 表数据比较少，加索引反而可能会有回表的开销，不如全表扫描的效率
  4. 区分度很低的大量重复字段，比如gender只有男/女两种值，MySQL的优化器可能直接忽略索引直接全表扫描

索引失效的场景

  1. 不满足最左前缀匹配（联合索引省略前面列 / 范围查询）以及模糊前缀（like %ab）
  2. 在查询条件中对索引列做了计算、函数，例如：where price * 0.8 > 10，where YEAR(create_time) = 2025
  3. 隐式类型转换，例如：phone varchar(11)，where phone = 12345678，与数字比较时发生varchar -> int的隐式类型转换
  4. 使用!= / not，例如：where age != 18，如果age不为18的行占比较多，优化器会选择全表扫描效率更高
  5. 使用or连接不同条件，例如where age = 18 or status = 1，如果or两边有一边字段没加索引通常会索引失效全表扫描；如果两边字段都有索引优化器会衡量开销，如果扫描的数据量太大则索引也会失效；如果or两边是相同的字段例如where name = 'Tom' or name = 'Lily'则索引通常可以生效

B+树

数据结构

从平衡二叉树讲起，当数据量过大时，平衡二叉树就会变得很高，而每深入一层访问一次节点，就会进行一次磁盘IO操作，因此当数据量很大时，访问平衡二叉树会很慢

B树在平衡二叉树的基础上，允许每个节点可以存放多个数据，拥有多个子节点，对于一个n阶的B树，意味着每个节点可以存放n - 1个数据，且最多有n个子节点，这减少了树的高度，当存储大量数据时可以有效减少磁盘IO的次数。但是由于每个节点都存储了数据，查询时可能会读取无用的数据加载到内存，并且由于每个节点都存储了数据，那每个节点能够存储的索引键值数目就会减少，

B+树在B树的基础上，把所有数据都放在叶子节点存储，非叶子节点只存储索引键值，因此每个节点扇出更多子节点，树的高度更矮，通常只有3～4层，此外叶子节点之间通过双向链表连接，便于范围查询

与B树的比较

  1. B树每个节点都存储数据，B+树只在叶子节点存储数据，非叶子节点能存储更多索引键值，扇出子节点更多，高度更矮
  2. B树不擅长范围查询（中序遍历），而B+树叶子节点通过双向链表连接，范围查询效率更高
  3. B树由于每个节点都存储数据，磁盘IO更多，B+树磁盘IO更少

B+树适合做索引的原因和注意事项

原因

  1. 高效的磁盘IO：B+树高扇出的特性，使树的高度更低，访问数据时经历的层数更少，磁盘IO的次数更少。同时B+树的每个节点通常被设计为和磁盘页的大小一致，一次磁盘读取就可以加载一个完整的节点，减少了磁盘寻道次数
  2. 范围查询高效：叶子节点通过链表连接，便于顺序遍历，无需回溯到父节点，范围查询高效
  3. 查询效率稳定：叶子节点都在同一层，高度统一，从根节点到目标节点的路径长度都是一样的

注意事项

B+数的阶数为n是指最多能存储n - 1个索引键值，最多有n个子节点，相差为1的原因如下：

1
2
3
4
5
// 父节点
第k层：100 | 200
// 子节点
第k + 1层：0~100 | 100~200 | 200~
// 阶数为3的例子
  • 因此对B+树的插入和删除操作可能会导致节点的分裂与合并，带来额外开销
  • 另外要注意查询时的最左前缀匹配，防止B+树的索引无法发挥作用
  • 对于数据量较小的表，维护索引的开销可能大于B+树带来的性能提升

一条查询SQL的执行过程

  1. 连接器：客户端与数据库服务器建立连接，经过TCP三次握手和用户身份验证，连接成功后服务器会分配一个独立的线程来处理后续的请求
  2. 查询缓存（MySQL 8.0之前）：检查是否命中缓存，如果查询请求相同且缓存结果有效可直接返回。8.0版本之后移除，因为表数据有更新缓存则会失效，缓存命中率低，且维护成本高
  3. 解析器：词法分析，识别出sql语句的关键词；语法分析，根据词法分析的结果，构建抽象语法树
  4. 预处理器：检查表和字段是否存在，用户是否有权限执行等，还会进行一些语义检查和替换，比如把*扩展为所有字段
  5. 优化器：选择最优的执行计划，例如：索引选择、join联表顺序等
  6. 执行器：调用存储引擎的接口，按执行计划读取数据并处理（排序、聚合等）
  7. 存储引擎：从磁盘或buffer pool（内存）中读取数据

数据库连接池

数据库连接池是应用程序提前创建并维护的一组数据库连接，业务线程需要访问数据库时，从连接池中获取连接，用完后再归还给连接池，而不是每次请求都重新创建和关闭连接

为什么需要数据库连接池？

  1. 创建连接成本高

    建立数据库连接需要经过TCP握手、MySQL认证、权限校验、初始化会话等步骤，如果每次SQL都新建连接，开销很大

  2. 复用连接，提高性能

    连接池里的连接可以反复使用，减少频繁创建和销毁连接的开销

  3. 控制并发，保护数据库

    如果没有连接池，高并发下应用可能创建大量连接，把MySQL打满。连接池可以限制最大连接数，避免数据库连接数被无限放大

连接池大致流程：

1
2
3
4
5
6
7
8
9
应用启动
    ↓
初始化一批数据库连接
    ↓
业务线程从连接池获取连接
    ↓
执行SQL
    ↓
归还连接到连接池

常见参数：

  • 最大连接数：连接池最多能创建多少连接
  • 最大空闲连接数：最多保留多少个空闲连接
  • 最大空闲时间：连接空闲太久会被回收
  • 最大生命周期：连接使用时间太久会被重建，避免长期连接异常

需要注意：

  • 连接池不是越大越好，连接太多会增加MySQL线程调度和内存开销
  • 连接池太小会导致业务线程等待连接，接口响应变慢

深分页

深分页是指分页查询的页码非常靠后，例如：

1
select * from user order by id limit 1000000, 20;

这条SQL不是直接跳到第1000000行开始读，而是需要先扫描前面1000000行，再丢弃掉，只返回后面的20行

1
2
3
4
5
扫描1000020行
    ↓
丢弃前1000000行
    ↓
返回20行

因此页码越深，扫描和丢弃的数据越多，查询越慢

深分页慢的原因：

  • limit offset, size中的offset越大，需要跳过的数据越多
  • 如果查询字段不能被索引覆盖，可能会产生大量回表
  • 如果还伴随order by，可能会有排序开销

常见优化方式：

  1. 基于上一页最后一条记录做范围查询

    如果按照主键递增分页，可以记录上一页最后一个id，下一页从这个id之后开始查

    1
    2
    3
    4
    select * from user
    where id > 1000000
    order by id
    limit 20;

    这种方式可以利用主键索引范围扫描，避免跳过大量数据

    缺点是只能适合“下一页”这种连续翻页场景，不适合直接跳到第1000页

  2. 延迟关联 / 覆盖索引

    先用覆盖索引查出目标页的主键id，再根据id回表查询完整数据

    1
    2
    3
    select id from user order by id limit 1000000, 20 // return ids

    select * from user u where u.id in ids

    内层查询只走索引，减少回表的数据量

  3. 限制最大翻页深度

    很多业务没有必要支持无限深分页，可以限制用户最多翻到多少页，或者提供搜索条件缩小范围

慢查询

慢查询是指执行时间超过阈值的SQL。MySQL可以通过慢查询日志记录这些SQL，便于后续分析和优化

常见配置：

1
2
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;	// 1s

表示开启慢查询日志，并记录执行时间超过1秒的SQL

慢查询常见原因：

  1. 没有走索引

    例如查询条件没有索引，或者索引失效，导致全表扫描

  2. 返回数据量太大

    查询没有限制范围，一次返回大量数据

  3. 深分页

    limit offset, size中的offset过大，需要扫描并丢弃大量数据

  4. 排序、分组开销大

    order by / group by没有利用索引

  5. 锁等待

    SQL本身可能不复杂，但被其他事务持有的锁阻塞了

  6. 表结构或SQL写法不合理

    例如字段类型不匹配导致隐式类型转换、对索引列使用函数、select *导致大量回表等

慢查询排查思路：

  1. 使用explain查看执行计划

    重点看：

    • type：访问类型，是否为ALL全表扫描
    • key：实际使用了哪个索引
    • rows：预估扫描行数
    • Extra：是否出现Using filesort、Using temporary

  2. 判断索引是否合理

    • 查询条件是否命中索引
    • 是否满足最左前缀匹配
    • 是否存在索引失效
    • 是否可以用覆盖索引减少回表

  3. 看扫描行数和返回行数

    如果扫描很多行但只返回少量数据，通常说明索引设计不合理

  4. 看是否有锁等待

    如果SQL执行时间很长，但扫描行数不多，可能是被锁阻塞

    可以通过 SHOW PROCESSLIST 查看 Waiting for lock 状态，

    通过 SHOW ENGINE INNODB STATUS 查看 LOCK WAIT 信息

慢查询优化一般不是只看SQL执行时间，而是看它为什么慢。常见方向是看执行计划、看索引、看扫描行数、看排序分组、看锁等待

MySQL存储基础概念

这部分主要是为了理解后面的Buffer Pool、脏页、redo log、随机IO / 顺序IO

什么是页？

MySQL InnoDB存储引擎管理数据时，不是每次只读写一行数据，而是以**页（Page）**为单位读写磁盘

InnoDB默认一页大小是16KB

1
2
3
4
5
6
7
磁盘
  ↓
按页读取
  ↓
Buffer Pool
  ↓
在页中查找具体行记录

也就是说，即使只查询一行数据，InnoDB也可能会把这一行所在的整个页加载到内存中

为什么要以页为单位？

  • 磁盘IO一次读一小块数据效率不高，按页读取可以减少IO次数
  • 数据库通常有局部性，一次读取一页，后续可能还能用到同一页里的其他数据
  • B+树的节点通常就是页，一个页里可以存很多索引键值或行记录

页和B+树的关系

InnoDB的索引底层是B+树，B+树的一个节点通常对应一个页

1
2
B+树非叶子节点页：存索引键值和子页指针
B+树叶子节点页：存真实行数据（聚簇索引）或二级索引值 + 主键

查询时，InnoDB会从根页开始查找，一层一层定位到叶子页，然后在叶子页里找到目标记录

1
2
3
4
5
6
7
根页
  ↓
中间页
  ↓
叶子页
  ↓
具体行记录

页分裂和页合并

InnoDB中一个页的大小是固定的。如果向某个页里插入数据时，这个页已经放不下了，就需要发生页分裂

页分裂

页分裂是指把一个满页拆成两个页，一部分数据留在原页，一部分数据移动到新页，然后更新B+树的父节点指针

1
2
3
4
5
6
7
原页满了
    ↓
申请新页
    ↓
把部分记录移动到新页
    ↓
更新页之间的链表和父节点索引

页分裂的问题：

  • 需要移动数据
  • 需要更新页指针和父节点索引
  • 可能导致更多磁盘IO
  • 频繁页分裂会影响插入性能

为什么主键建议自增？

如果主键自增，新数据通常插入到B+树最右侧的页，比较顺序，页分裂较少

如果主键是随机值，比如UUID，新数据可能插入到B+树中间的任意页，更容易造成页分裂和数据移动

页合并

删除数据后，某些页里的数据可能变得很少。为了避免空间浪费，InnoDB可能会把相邻的页合并，这就是页合并

1
2
3
4
5
两个相邻页的数据都很少
    ↓
合并到一个页中
    ↓
释放多余页

页合并也有维护成本，因为需要移动记录并更新页之间的关系

Buffer Pool

Buffer Pool是InnoDB在内存中维护的一块缓存区域，用来缓存磁盘中的数据页和索引页

为什么需要Buffer Pool？

因为磁盘IO很慢，而内存访问很快。MySQL不希望每次查询都直接读磁盘，所以会把常用的数据页缓存到Buffer Pool里

查询数据时：

1
2
3
4
5
先查Buffer Pool
    ↓
如果页在内存中，直接读取
    ↓
如果页不在内存中，从磁盘加载到Buffer Pool

更新数据时：

1
2
3
4
5
6
7
先把数据页加载到Buffer Pool
    ↓
在内存中修改这个页
    ↓
记录redo log
    ↓
后续再把数据页刷回磁盘

所以InnoDB不是每次更新都立刻把数据页写回磁盘，而是先改内存中的页，再通过redo log保证崩溃后可以恢复

什么是脏页？

Buffer Pool中的页如果被修改过，但还没有写回磁盘，这个页就叫脏页

1
2
磁盘中的页：age = 18
Buffer Pool中的页：age = 20

此时内存中的页和磁盘中的页不一致，这个内存页就是脏页

注意：脏页不是错误数据，它只是表示这个页在内存中被改过，但还没同步刷回磁盘

脏页什么时候刷盘？

  • Buffer Pool空间不足，需要淘汰页时
  • 后台线程定期刷脏页
  • redo log快写满时，需要推进checkpoint
  • MySQL正常关闭时

需要注意，事务提交时通常不要求立刻把脏页刷到磁盘。只要redo log安全落盘，即使MySQL宕机，重启后也能根据redo log恢复数据

随机IO和顺序IO

随机IO

随机IO是指磁盘读写的位置不连续，每次读写都可能跳到不同位置

例如更新很多分散的数据页：

1
2
3
4
写第10页
写第900页
写第25页
写第3000页

对于机械硬盘来说，随机IO需要频繁寻道，性能很差；对于SSD来说随机IO好很多，但仍然比顺序写复杂

顺序IO

顺序IO是指按照连续的位置读写数据

例如写日志文件：

1
2
3
追加写第1段
追加写第2段
追加写第3段

顺序IO性能通常更好

为什么redo log性能高？

如果每次更新都直接把数据页写回磁盘，由于数据页分布在磁盘不同位置，可能是随机IO

redo log是追加写日志，属于顺序IO，性能更好

1
2
直接刷数据页：随机IO，慢
先写redo log：顺序IO，快

所以InnoDB采用的思路是：

1
2
3
4
5
更新时先改Buffer Pool中的页
    ↓
写redo log保证崩溃可恢复
    ↓
后台慢慢把脏页刷回磁盘

MySQL中的日志文件类型

undo log

undo log是回滚日志，主要用于事务回滚和MVCC

事务执行更新时，MySQL会记录旧数据版本到undo log中。如果事务需要回滚，就可以根据undo log恢复到修改前的状态

undo log的作用：

  • 事务回滚：保证事务的原子性
  • MVCC：提供历史版本数据，支持一致性读

redo log

redo log是InnoDB的重做日志，主要用于保证事务的持久性

事务的提交会先写入持久化日志文件redo log，即使数据库崩溃重启，redo log中的记录也不会丢失，重启后的数据库可以通过读取redo log文件恢复已提交的日志

binlog

binlog是MySQL Server层的归档日志，记录数据库的逻辑变更，所有存储引擎都可以使用

binlog常用于：

  • 主从复制
  • 数据恢复
1
update user set age = 20 where id = 1;

主从复制时，主库把binlog同步给从库，从库重放binlog，达到数据同步的效果

两阶段提交

一条更新SQL既要写redo log，也要写binlog。为了保证两份日志的一致性，MySQL使用两阶段提交

大致流程：

1
2
3
4
5
redo log prepare
    ↓
写 binlog
    ↓
redo log commit

为什么这样能保证一致性？

  1. 如果在redo log prepare之前崩溃

    此时redo log和binlog都没写成功，事务还没真正提交，恢复时直接回滚即可

  2. 如果在redo log prepare之后、写binlog之前崩溃

    此时redo log是prepare状态，但binlog不存在。MySQL恢复时发现没有对应的binlog，说明事务没有完整提交，因此回滚该事务

  3. 如果写完binlog之后、redo log commit之前崩溃

    此时redo log是prepare状态，但binlog已经存在。MySQL恢复时会认为这个事务已经可以提交，于是把redo log补成commit状态，提交该事务

关键点是：恢复时会根据redo log的prepare状态和binlog是否存在，判断事务应该回滚还是提交

1
2
redo log prepare + binlog不存在 -> 回滚
redo log prepare + binlog存在   -> 提交

两阶段提交保证了：事务提交时，redo log和binlog要么都成功，要么崩溃恢复时可以根据状态判断事务是否应该提交

简单总结：

  • undo log：用于回滚和MVCC
  • redo log：用于崩溃恢复，保证持久性
  • binlog：用于主从复制和数据恢复

说些什么吧！

gitalk