mysql 的精要设计

---

theme: channing-cyan

写入流程

  update user set name = '李四' where id = 100

1. 先将 id = 100 这个磁盘数据页读取到 buffer pool 中

2. 然后插入一条 undolog 日志记录变更前的数据 name = '张三'

3. 随后修改 Buffer Pool 中的值，name = '李四'

4. 然后将 Buffer pool 变更写入到 redo log 中 Prepare 阶段

5. 然后写入 Binlog

6. 最后写入 redo log commit 标识

写入优化

可以发现在每次执行写入的时候都需要先将更新所在的数据加载到缓存中，导致写入行为变得比较慢，所以有 change buffer 的优化方案

1. 检查 id = 100 如果在内存中就更新 buffer pool 如果不在的话将更新数据插入 change buffer 中

2. 然后插入一条 undolog 日志记录变更前的数据 name = '张三'

3. 然后将 Buffer poll 或者 change buffer 记录的变更写入到 redo log，prepare 阶段

4. 然后写入 Binlog

5. 最后写入 redo log commit 标识

由于每次不需要读取磁盘数据所以存在大量写的情况下性能很高

change buffer 也是会写入磁盘中的进行持久化存储的

什么情况下 change buffer 无法使用

当插入或者修改的数据被标记为唯一索引的时候，此时必须从磁盘去检索目标数据是否满足唯一性校验，所以无法使用 change buffer

change buffer 对查询的影响

由于将变更的数据写入了 change buffer，磁盘中留存的是老数据，所以写一次查询请求过来的时候，首先需要从磁盘读取数据，然后再执行 merge change buffer 操作

而读取 change buffer 又会涉及到磁盘 IO 所以当修改少读取多的时候会导致性能大幅度下降

数据存储

在磁盘中

以 InnoDB 为例，数据索引分为聚簇索引、普通索引、唯一索引

其中聚簇索引将索引和数据存储在一起为整个完整的数据

非主键索引只是存储了索引和主键ID字段数据

索引的实现机制为 B+ 树，非叶子节点存储索引、叶子节点存储数据，在底层磁盘存放数据的时候索引也是按照数据块将索引数据存储在一起的通过存储下一个节点的指针进行定位，所以每当加载一个数据页的时候能够读取到大量的索引，再基于索引做二分搜索就能快速定位到数据所在的下一层索引区间或者数据区间

同时数据也是通过磁盘数据块进行存储并且通过指针进行连接，同时由于 B+ 树的特性，底层数据是按照索引从小到大的顺序挨个存放的

在内存中

数据在内存中存储在 Buffer Pool 中，就是还原了读取磁盘中的数据为一颗 B+ 树，只不过 Buffer Pool 是有固定大小的，当写满了之后就需要进行数据页淘汰

数据何时进入磁盘

1. Buffer Pool 刷脏页：后台定时刷，关机刷，满了刷。在刷脏页的时候同时也会推进 redo log checkpoint
2. redo log 写满了必须进行推进 redo log 中记录的变更到磁盘中：当 redo log 写满了之后需要停止所有写操作来进行推进 checkpoint

查询流程

SQL 解析器解析 SQL 检查语法是否正确

查询优化器为查询选择合适的索引

执行器调用存储引擎执行 SQL

存储引擎读取磁盘数据加载内存 buffer pool 中过滤后返回给执行器

数据检索

单表检索

检索数据分为以下几种检索方式

（1）根据主键索引检索

（2）根据普通索引检索

（3）根据唯一索引检索

索引的检索都是在 B+ 树中进行解锁，B+ 树这个数据结构特征决定了可以快速定位到要检索的数据，同时也只能进行左匹配搜索、默认是有序的，内存中可以存储大量的索引非叶子节点，叶子节点的数据都是由链表串联起来的

假设 mysql 表中有 2千万数据，主键为 int 4 字节，指针 6 字节，一个数据页默认为 16KB，那么一个数据页能够存储 (16 * 1024) / (4 + 6) = 1638 个索引

3层高的 B+ 树可以存放 1638 * 1638 = 268万个索引，假设每一行数据占据 1KB，1个 叶子节点能存储 16 行数据，那么 3 层高的索引一共能存储 268 万 * 16 = 4300万行数据

如果索引没有在磁盘中，理论上也只需要 3次随机磁盘 IO 就能定位到要获取的数据，同时读取了索引或者数据之后会在磁盘进行缓存，假设索引节点都已经存在内存中，那么只需要一次磁盘 IO 就能获取到随机（索引等值查询）

主键索引的特征是叶子节点存储了完整的数据，不能重复

非主键索引特征是只存储索引和主键 ID 值

对于普通索引，索引区分度决定了其检索效率如何，检索到对应的索引字段后还会检索后检查直到检查到了一条不满足条件的数据

多表检索

当进行多表关联查询的时候，需要基于驱动表的数据去检索被驱动表的数据，所以驱动表和被驱动表需要被扫描的次数以及其数据量决定了关联查询的效率

为了优化查询优化器会选择以数据量小的作为驱动表

假设执行如下 sql 根据身份证检索出其所有好友信息

  select a.* from a left join b on a.name = b.name

场景一：假设 a 表 name 为普通索引，b 表 name 没有设置索引

检索一次 a 表过滤出 name = zhangsan 的信息扫描出来了 100 条数据

将这 100 条数据全部放入 join_buffer 中(因为是 select a.*)

然后去全表扫描 friends，每取一条数据就跟 join_buffer 中的数据进行对比，满足条件的数据就存放到 net_buffer 中

join_buffer 默认为 256K 由 join_buffer_size 控制，当 join_buffer 存放不小的时候就开始处理当前的数据检索，处理完毕后清空 join_buffer 继续读取后续的数据

net_buffer 被写满或者 friends 扫描完毕之后，就会将数据发送给客户端

mysql 是边读边发的利用 net buffer 作为应用的缓冲区，如果不利用 net buffer 等技术将全部结果放入内存中可能会将内存打爆 net buffer 默认 16K 大小

这种无法利用被驱动表的索引的查询叫做 Block Nested-Loop Join

实际工作场景中几乎不可能用这样无法使用到索引的关联查询的，尤其是在驱动表中检索出来的数据量比较大的情况，因为驱动表一条数据就会进行一次被驱动表的全表扫描

场景二：假设 a 表 name 为普通索引，b 表 name 设置了索引

检索一次 user 表过滤出 name = zhangsan 的信息得到对应行信息，然后基于 name 索引去检索 friends 表，每扫描一条数据就放入 net_buffer

这种可以用上被驱动表的索引的查询叫做 Index Nested-Loop Join

由于在普通索引中查询到的数据只有索引数据和主键 ID 值，同时索引是有序的但是检索出来的主键 ID 可能是无序的，这里是 select * 所以需要进行回表返回完整数据的时候，可能就需要多次随机 IO 查询数据了

为了优化回表导致的随机 IO 采用 Multi-Range Read，将驱动表读取到的所有数据 id 值放入 read_rnd_buffer 中，然后进行排序，最后基于有序的数据去检索被驱动表的数据，因为是有序的所以 有可能更能高效利用 buffer_pool

read_rnd_buffer 由 read_rnd_buffer_size 控制优化器策略，判断消耗的时候，会更倾向于不使用 MRR，要默认全部开启的话需要设置 set optimizer_switch="mrr_cost_based=off"

跟 join_buffer 类似如果 read_rnd_buffer 写满了的话，就先处理当前的数据，然后清空继续读取后续数据

排序

由于 B+ 树默认就是按照数据从小到达组织的所以通过索引 order by asc/desc 返回的数据天然有序

如果 order by column 字段没有索引的话，首先需要初始化 sort_buffer 内存，放入查询的字段，然后继续索引检索到满足条件的数据放入 sort_buffer 中，然后在 sort_buffer 基于快速排序算法进行排序，最后返回结果给客户端

sort_buffer_size 控制 sort_buffer 大小，如果内存中存放不下就需要借助磁盘文件进行排序，将有序数据输出到多个文件中，最后采用归并排序算法将多个有序小文件合并进行输出

事务控制

事务的 ACID

原子性（Atomicity）：事务操作要么整个事务里面的操作全部成功要么全部失败

一致性（Consistency）：事务操作不伦成功或者失败完成性必须保持一致，比如成功后数据、索引全部维护正确

隔离性（Isolation）：事务之间是互相隔离的

持久性：事务提交的数据会持久化到磁盘中

事务的隔离级别

读未提交：事务可以读取到到另外一个事务没有提交的变更

读已提交：事务只能读取到已经提交事务的变更

可重复度：在整个事务过程中读取到的数据始终保持一致

串行化：事务串行化排队挨个处理

update 的工作原理

理解事务以及回滚需要先简单理解增删改的工作原理

mysql 会为表创建 2 个隐藏的字段

（1）trx_id：表示当前行是哪一个事务更新的

（2）roll_pointer：指向上一个版本的 undo log

如果是 insert 的话 undo log 指向的就是空数据、如果是 delete undo log 指向的就是原数据

所以可以理解 msyql 将我们每一次变更通过 undo log 指针串起来了，便于回滚

可重复读的实现原理

默认开启事务后第一次执行 SQL 语句的时候创建一个 readView 一致性快照，后续所有的查询都是基于快照进行查询，这样就能保证同样的条件在事务前后读取到的数据总是一样的

那么这个 readView 的实现原理就是可重复读取的关键，创建 readView 的数据结构如下

（1）m_ids: s事务开启这一刻系统活跃事务 id 列表

（2）min_trx_id：m_ids 中最小值

（3）max_trx_id：m_ids 中最大值 + 1

（4）creator_trx_id：当前事务 ID

基于 readView 查询的工作原理，首先检查当前行是否是自己修改的，然后在基于 undo log 指针检查变更记录

（1）trx_id 等于 creator_trx_id 是自己更新的可见

（2）trx_id 在 m_ids 中，并且不是自己更新的不可见

（3）trx_id 小于 min_trx_id，表明这个版本的数据是当前事务开启之前提交的可见

（4）大于 max_trx_id，表明是当前事务开启之后开启的不可见

undo log 过多的问题：如果每次都改全部都要维护 undo log 指针是不太现实的，所以肯定需要在合理的时机进行删除，这个时机就是当前系统中所有 readView 中的 min_trx_id 之前的 undo log 指针和数据可以进行清空了

读已提交的工作原理

读已提交的工作原理也是根据 readView 来实现的，只不过是创建的时机不同，可重复读在第一次执行 SQL 的时候创建全局只使用一个视图，而读已提交在每次执行 SQL 的时候都会创建一个 readView

当前读

如果在读已提交的隔离级别中，想要读取到最新的版本的数据可以基于当前读特性来读取最新版本的数据

这个数据会对 id = 1 的数据加写锁，同时会读取最新版本的数据，如果此时有其它事务正在修改这条数据（也会写写锁）那么就会阻塞等待对方事务提交，然后读取到最新的值

  select * from table where id = 1 for update

还可以对数据加共享锁（可重复加锁，但是读写锁互斥）也是基于当前读的特性

  select * from table where id = 1 lock in share mode

幻读

mysql 事务在可重复度这个隔离级别下存在幻读的问题

锁

数据准备

  CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

mysql 中的锁按照种类划分为 读锁、 写锁

按照锁的粒度来进行划分 库全局锁、 表锁、 行锁、 间隙锁、 元数据锁（MDL锁）

读锁可以重复加锁，读写锁互斥、写写锁互斥

粒度划分的每个锁都可以跟读锁、写锁进行组合，如行写锁（对主键 id 执行 for update 或者 update table），如元数据读锁（执行 crud 都会加元数据读锁用于和 ddl 互斥）

全局锁：Flush tables with read lock，加了读锁会阻塞所有的增删改、数据库 ddl 的执行（因为它们需要加写锁，读写互斥）

表锁：lock table read/write 对表加读锁或者写锁

（1）read：会阻塞 table 的写语句，不会阻塞 table 的 select（表读锁之后 select 还可以加读锁，因为读锁可以重入）

（2）write：会阻塞 table 的读写语句（读写锁互斥嘛，因为写语句会加行写锁，但是表写锁已经加了）

行锁：

（1）行写锁：执行 update table id = 10

（2）行写锁：select * from table id = 10 for update

（3）行读锁：select * from table id = 10 lock in share model

间隙锁

1. 如果精准根据主键 ID 进行 update

那么只需要加行锁如 update t set d = d + 1 where id = 5 只会加 id = 5 那一行的锁

2. 如果主键 ID 没有定位到值

那么需要加间隙锁 update t set d = d + 1 where id = 7 会在 (5,10) 这个区间加锁，因为 id = 7 没有定位到数据，为了保证可重复度特性必须加间隙锁防止在这个间隙添加到 id = 7 的数据导致后续再次执行 update 时候语义混乱

3. 如果根据普通索引进行 update update t set d = d + 1 where c = 5 这种情况下会加 (0, 10) 的间隙锁 + 行锁，因为普通索引不具备主键索引或者唯一索引的唯一性，是可能存在重复的，为了防止当前数据的前置区间或者后置区间再次出现 c = 5 的数据，所以需要将 (0, 5]，（5， 10) 这 2 个区间进行加锁

next-key lock 是间隙锁 + 行锁的称呼，总之 加锁间隙锁是为了保证可重复度的语义，只会在可重复隔离级别下存在，所以说读已提交的性能更高，在可能破坏可重复度的语义场景中都会加间隙锁

读写分离

可以通过做读写分离来提高系统的吞吐量、同时还可以在主库宕机后提升 slave 为主库来保证系统的高可用

master/slave 架构下如何保证数据一致性是很多分布式系统都面临的一个问题，大部分都是基于集群中过半节点写入 os cache 成功就进行返回，我们来看下 Mysql 中的实现机制

主从数据同步机制如下

在 mysql5.6 之前就是采用这种模式进行通信，即主库支持并发，从库同步处理 binlog 的时候基于单线程进行处理，当主库 TPS 过高的时候，从库会存在较高的延迟

在 mysql5.7.22 支持的并行复制策略，主要的思想是

（1）通过 WRITESET 记录对于事务涉及更新到的每一行数据，如果 2 个事务没有涉及到同一行的数据变更（即两个事务的 WRITESET 没有交集）表明可以并行执行（WRITESET 会在提交事务的时候写入到 binlog）

（2）同时处于 prepare 的事务可以并行执行，同时处于 prepare 和 commit 的事务可以并行执行

（3）WRITESET_SESSION 对于 WRITESET 的约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序

就算支持多线程同步了，然后也是没有保证数据一致性的，没有保证数据一致性，那么在主库宕机切换从库的时候就可能导致数据丢失

所以有了 半同步机制（semi-sync replication）：数据写入到 binlog 之后再从库同步完成之后会响应 ack，主库只要收到了一条 ack 指令就给客户端响应成功

其实绝大部分机器保证数据一致性的方案都是过半写入从库，但是大都是逻辑简答的内存读写或者磁盘顺序读写，操作非常快，所以延时很低，对于客户端的 TPS 依然很高，但是对于 MySQL 这种系统同步执行事务大都是几百 MS，这样会严重影响主库的 TPS 吞吐量，所以对于 MySQL 来说大都还是采用异步复制方案，如果真的宕机了需要运维通过脚本将主库未同步的 binlog 在某一个从库执行完毕之后，再手动提升从库为主库即可

读写分离在客户端的实现方案可以是基于客户端模式，返回给一个 MySQL 主库的 proxy 节点，proxy 结点维护与 master 的连接，当 master 宕机后切换到另外一个数据库，保证客户端可以实现平滑过渡，对于 slave 库也可以同样返回 proxy 节点（proxy 节点只做数据转发）

读写分离在客户端的实现方案还可以选用中间件代理层模式，比如 mycat 用户只需要配置与 mycat 的连接，底层数据的宕机和切换均由 mycat 感知和切换（mycat 要负责解析 SQL 重量级的 Proxy 层）

分库分表

单库面临的最严重的问题就是资源优先包括存储空间、带宽、CPU 等等，在海量数据和大量 TPS 的情况下需要分库分表来解决问题

分库分表之前必须要做好的一件事情就是预估后期可能会存在的数据量尽量在分库分表的时候一次性设计好后面很多年都能用的策略，因为存储层分库分表扩容是一个非常麻烦的过程

比如我们用户表 2 亿用户从一开始就设计了 64 个库每个库 8 张表的设计，每张表存储 40万数据左右，每个数据库存储了 320 万用户，并且按照单表 500 万来计算可以满足存储 20多亿的用户，同时设计这么多库的原因希望支撑规划场景下的数万或者数十万的 TPS，以及海量的 QPS

分库分表中有以下几种设计

（1）将相关联的数据通关相关联的属性值关联路由存储到同一个数据库中

比如用户、订单、支付在单号设计上都混淆上 user 的后 4 位数，这样在路由的时候统一都根据这 4 位 user_id 关键值 % 数据库就路由到同一个数据库中，这样就可以借鉴本地事务来避免出现分布式事务的问题

同时用户在查看自己的订单时候根据 user_id 关键字路由后，可以直接进行简单的分页查询

（2）通过单数据源MySQL/ES/缓存等维护数据与服务器之间的存储关系

比如用户和订单分别根据各自的单号进行路由存储到不同的数据库中，然后在 ES 中做一个宽表存储买家、卖家、订单、商品、创建时间等等信息

此时买家卖家查看订单的时候，只需要根据自身编号过滤然后通过时间排序即可

但是在处理事务问题时候需要注意的是

• 分布式事务问题：由于写入分散到不同的数据源中可以基于 seata、mq 最终一致性、可靠消息服务来保证，同时配合手动补偿

• 数据实时性问题：由于 ES 同步总是存在延迟的，所以需要一般是需要基于 MySQL 中实时的数据进行处理，这个时候可以考虑单独针对这样的场景将数据写入到一个公共的数据源中再返回给客户端成功（需要评估单点写入压力，为极少数功能进行配置），或者在处理的时候尽量多冗余数据，在业务处理的时候直接就拥有的数据的路由键

（3）复杂业务

复杂业务可以基于 ES 存储宽表索引数据

客户端的框架可以选用 sharding 灵活的实现按照不同的或者自定义策略路由库表、配置单库路由、复杂检索等功能