MySql（InnoDB原理）
主要基于MySql5.6+版本，在5.6+版本中，InnoDB存储引擎增加了许多新功能，例如过个清理线程，全文索引，在线索引添加，独立回滚段，非递归死锁检测，新的刷新算法，新的元数据表等。
1. 体系结构
1.1 组成部分
- 连接池组件
- 管理服务和工具的组件
- SQL查询结构组件
- 查询解析器组件
- 优化器组件
- 缓存组件
- 基于插件形式的存储引擎组件
- 物理文件系统
1.2 主要存储引擎
2. InnoDB存储引擎
InnoDB是事务安全的存储引擎，完全支持ACID和SQL标准的四种事务隔离级别，同时使用next-key locking策略来解决幻读现象问题，支持MVCC来支持高并发性。
2.1 InnoDB体系结构
- 内存池
- 维护所有进程和线程需要访问的内部数据结构
- 缓存磁盘上的数据，同时对磁盘文件的修改缓存
- 重做日志缓冲
- 额外内存池
- 后台线程
- 刷新内存池中的数据
- 保证缓冲池中的数据是最近的热点数据
- 将已修改的数据文件刷新到磁盘文件
- 保证在发生异常的时候InnoDB能恢复到正常运行的状态
2.2 内存池
2.2.1 缓冲池
-
InnoDB是基于磁盘存储的，缓冲池是通过内存的速度来弥补CPU和磁盘的速度差距
-
缓冲池中缓存的数据页包括：
- 索引页
- 数据页
- undo页
- 插入缓冲
- 自适应哈希索引
- InnoDB的锁信息
- 数据字典信息
-
缓冲池可以有多个实例，每个页可根据hash平均分配到不同的缓冲池中，可减少内部的资源竞争，增加数据库的并发处理能力
读取时：
更新时：
2.2.2 LRU List, Free List和Flush List
缓冲池一块很大的内存空间，需要通过LRU算法来管理这段内存：最频繁使用的页在LRU列表前端，最少使用的页在LRU列表后端。
-
LRU列表：在InnoDB中，缓冲池的页默认大小为16KB，在使用LRU思想的时候，InnoDB对算法做了一定的改进：
- 在LRU列表中增加Midpoint位置
- 新读取到的页，虽然是最新访问的页，但是并不是直接放入到LRU列表的首部，而是放入在Midpoint的位置
- 一些SQL语句(索引或者数据扫描的操作)，需要访问表中的很多或者全部的页，但是这些页可能只是本次查询需要，并不是热点数据，如果放入首部，会造成需要的热点数据被从LRU列表删除，导致下次读取页时需要访问磁盘
-
Free列表：在数据库刚启动的时候，LRU List中没有任何的页，页都存放在Free List中；当需要从缓冲池中分页，现在LRU List中查看有没有可用的空闲页，如果有，则将该页从Free List中删除，放到LRU List中，否则按照算法淘汰末尾的页
-
Flush列表：在LRU List中的页被修改后，则会将该页也存放入Flush List中，此时LRU List中的脏页用来保证缓冲池中页的可用性，Flush List中的脏页用来通过CheckPoint机制将脏页刷新回磁盘文件，二者互相不影响
2.2.3 重做日志缓冲
InnoDB引擎在处理事务的时候，先会记录重做日志，此时是将重做日志放入到了重做日志缓冲区中，后台再以一定的频率将缓冲区的内容刷新到重做日志文件；该缓冲区不需要设置很大，默认为8MB，用户只需要保证每秒产生的事务数量在该缓冲区能存下即可。
重做日志在以下情况会将缓冲区的内容刷新到磁盘文件：
- Master Thread每隔一秒将缓冲刷新到日志文件
- 每次事务提交会将重做日志刷新到重做日志文件
- 当缓冲池的剩余空间小于一半时，会将重做日志刷新到日志文件
2.2.4 额外内存池
对一些数据结构本身的需要内存分配时，需要从额外内存中申请，当该区的内存不够时，会从缓冲池中进行申请
2.3 后台线程
2.3.1 Master Thread
- 具有最高的线程优先级
- 内部包含多个loop循环（主循环，后台循环，刷新循环，暂停循环）
- Master thread会根据db的运行状态在各个循环时间进行切换
2.3.1.1 Loop主循环
大多数的操作都在这个循环中，主要包括两大部分的操作，每秒钟的操作和每十秒的操作，loop循环通过thread sleep实现，所以在负载很大的情况下会有延迟
伪代码：
void master_thread(){
loop:
for(int i = 0; i < 10; i++){
per one second do thing
}
sleep 1s
per ten second do thing
goto loop;
}
-
每秒的操作，包括：
- 日志缓冲刷新到磁盘，即使这个事务还没有提交，每秒必定执行
- 合并插入缓冲，可能会不执行；InnoDB会判断前一秒内发生IO的次数是否小于5次，如果小于则会执行合并
- 至多刷新100个缓冲池中的脏页刷新到磁盘，可能会不执行；InnoDB会判断缓冲池中的脏页比例是否超过配置的比例，如果超过，则执行刷新同步
- 当前如果没有用户活动，切换到后台循环，可能会不执行
-
每10秒的操作，包括：
- 刷新00个脏页到磁盘，可能会不执行；InnoDB会判断前10秒中发生的磁盘IO操作是否小于200次，如果小于，则执行刷新同步
- 合并至多5个插入缓冲，每10秒钟必定执行
- 将日志缓冲刷新到磁盘，必定执行
- 删除无用的Undo页，总是执行，对于已经执行的Update和Delete操作的数据，如果没有查询操作需要读取之前版本的Undo信息时，在这一步执行full purge操作时，尝试着删除最多20个Undo页
- 刷新100个或者10个脏页到磁盘，InnoDB判断缓冲池中脏页的比例是否高于70%，若高于则刷新100个脏页，若低于则刷新10个脏页
2.3.1.2 后台循环
当没有用户活动时会切换到这个循环
2.3.1.3 刷新循环
2.3.1.4 暂停循环
2.3.2 IO Thread
- 使用AIO来处理IO写请求
- IO Thread负载外部IO请求的回调
- 主要包含4个read thread, 4个write thread, 1个log thread, 1个insert buffer thread
2.3.3 Purge Thread
- 事务被提交后，undo日志所在的页可能不再需要，需要purge thread来回收undo页
- purge thread显示独立出来，能减少master thread的工作
- purge thread能配置多个，更好的利用CPU和磁盘性能
2.3.4 Page Clean Thread
- InnoDB 1.2x+新引入
- 将Master thread中的脏页刷新工作独立出来
- 降低对用户查询线程的阻塞
2.4 InnoDB主要特性
2.4.1 插入缓冲(Insert Buffer)
-
InnoDB对于自增的主键值的插入是顺序的，不需要磁盘的随机读取，速度很快
-
对于非聚集的辅助索引来说，对于索引叶子节点的插入不是顺序的，需要离散随机访问索引页，会导致插入的性能降低
-
Insert Buffer需要的条件：
- 索引是辅助索引
- 索引不是唯一的
-
对于非聚集的辅助索引的每次插入和更新，并不是每次都直接插入到索引页中，而是先判断索引页是否在缓冲池中，如果在，则直接插入，如果不在，则先插入到Insert Buffer中，然后再以一定的频率将Insert Buffer和索引页进行合并操作
2.4.2 两次写(double write)
-
InnoDB中page size的大小为16KB，而将数据写入磁盘时，一次写入的大小是按照操作系统的页单位(4KB)来写入的，每次写入需要写4个块，计算机在极端的情况下是不保证写入的原子性的，可能只写入了4KB，8KB等部分页
-
Double Write写入的工作机制：
-
恢复时的步骤：
- 如果在写入double write buffer时失败了 ，数据不会被写入磁盘，在需要恢复的时候，则重新从磁盘载入原始的数据文件，再根据Redo日志来计算出正确的数据，最后重新写入到double write buffer
- 如果在写入double write buffer时成功，但是写磁盘时失败了，则在恢复时不需要再根据Redo日志来计算，只需要将double write buffer中的数据重新再写入一遍
- Innodb在进行恢复的时候，会直接计算页面的checksum,如果校验不通过的话，则就会从磁盘重新载入文件，加上事务的Redo日志来计算出正确的数据
2.4.3 自适应哈希索引
InnoDB引擎会监控对表的各个索引页的查询，如果发现建立哈希索引能提升效率，则会自动建立哈希索引，整个过程是根据对索引页的访问频率和方式对热点数据建立自适应哈希索引
2.4.4 异步IO
2.4.5 刷新邻接页
3. InnoDB表结构
3.1 索引组织表
InnoDB引擎中，表的数据都是根据主键的顺序来组织存放的。
3.2 InnoDB的逻辑存储结构
- 表空间是由各个段组成的（数据段，索引段， 回滚段。。。）
- 数据段即为B+树的叶子节点，索引段为B+树的非叶子节点
- 区是由连续页组成的，每个区的大小固定未1M，即一个区中共有64个连续的页
- 页是InnoDB磁盘管理的最小单位，默认为16KB
- 每个页存放的行记录条最多为(16KB/2 - 200)行
- 行记录支持多种格式(Compact, Redundant。。。)
3.3 InnoDB数据页结构
- File Header：页在表空间中的偏移值，页的类型(数据页还是索引节点页等)，当前页的上一页和下一页的指针，所属表空间的值，等等
- Page Header（记录数据页的状态信息）：在Page Directory中Slot数值，堆中第一个记录的指针，该页中数据记录的数量，索引ID(当前页属于哪个索引)
- Infimum和Supremum Record：将数据行记录限制在两者之间，这两个值在创建的时候被建立
- User Record：实际存储记录的内容
- Free Space：链表结构，一条数据记录被删除后，该记录的空间会被加入到该链表空间中
- Page Directory：存放记录的相对位置，在页内通过二分法找到记录行（如下图）
- File Trailer：检测页是否完整的写入磁盘
4. 索引与算法
4.1 B+树数据结构
定义：（M > 2）
- 任意非叶子节点最多有M个孩子
- 根节点的孩子数量为：2<= n <= M
- 除了根节点以外的非叶子节点的孩子总数为：M/2 <= n <= M
- 非叶子节点的子树的指针和关键字的数量相等
- 非叶子节点的关键字，K[i] < k[i+1]
- 所有的叶子节点都在同一层
- 为所有的叶子节点增加一个双向链指针
- 所有的关键字都在叶子节点中出现
4.2 聚集索引和非聚集索引
聚簇索引：叶节点就是指向的页就是全部的行记录数据
非聚簇索引：叶节点指向的页不包含全部的行记录数据，除了包含键值以外，还包含一个指向聚集索引键的书签
区别如下(借用图片):
4.3 InnoDB查询引擎优化器不使用索引的情况
SQL语句：select * from order where order_id > 1000 and order_id < 2000
索引：order_id(非聚集辅助索引)
在执行查询时，不会使用索引，而会进行表扫描。因为我们需要返回的是整行的数据，但是oerder_id索引不能覆盖我们所需要的全部数据，还需要根据索引的叶子节点的书签去查找聚集索引从而找到整行数据，虽然order_id索引中的数据是顺序存放的，但是再一次查找书签的过程是离散的，离散读取磁盘比较耗时；
所以，在需要的数据量占整表数据的很小一部分的时候，查询优化器会选择order_id辅助索引，否则会选取聚集索引进行表扫描
5. InnoDB存储引擎中的锁
5.1 锁的类型
共享锁(S)：事务读取行数据
排他锁(X)：事务更新或者删除行数据
意向锁：分为意向共享锁(IS)，意向排他锁(IX)；若将上锁的对象看成如下的一棵树，那么对最下层的行对象上锁， 也就是对最细 粒度的对象进行上锁， 那么首先需要对上层粗粒度的对象上锁。
举例来说， 在对记录 r1 加X锁之前， 如果已经有事务对表1进行了S表锁， 那么表1上已存在S锁， 之后事务需要对记录 r1 在表1上加上IX， 由于锁不兼容， 所以该事务需要等待表锁操作的完成。
5.2 一致性非锁定读(MVCC解决读写并行的幻读)
InnoDB引擎通过行记录的多版本并发控制的方式来读取当时执行时间数据库中的行记录数据
非锁定读： 因为不需要等待访问的行上X锁的 释放。 快照数据是指该行的之前版本的数据，该实现是通过undo 段 来完成。 而undo用来在事务中回滚数据， 因此快照数据本身是没有额外的开销。 此外， 读取快照数据是不需要上锁 的， 因为没有事务需要对历史的数据进行修改操作
5.2 一致性锁定读
select …for update 加X锁
select …lock in share mode 加S锁
5.3 锁算法
Record Lock：单行记录上的锁
Gap Lock：间隙锁，锁定一个范围，单不包括记录本身
Next-Key Lock(解决写写并行的幻读)：锁定一个范围，并且包括记录本身
InnoDB引擎在默认的REPEATEABLE READ隔离级别下使用Next-Key Lock来解决幻读问题，其原理过程如下：
| a(唯一索引) | b(辅助索引) |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 5 | 50 |
如果索引为 10，20，50，那么：
Record Lock：select * from tab where b = 10 for update; //对b=20单行进行加锁
Gap Lock锁范围：（- ∞，10）（10，20）（20，50）（50，+∞）
Next-Key Lock锁范围：（- ∞，10）[10，20）[20，50）[50，+∞）
- 当select * from test where b = 50 for update执行时，在事务没有提交的情况下，INSERT INTO
testVALUES (6, 60)语句是不能执行的，只能等待，因为列b有[50，+∞）范围的Next-Key Lock - 当select * from test where a = 5 for update执行时，在事务没有提交的情况下，INSERT INTO
testVALUES (6, 60)语句是可以执行的，因为对于唯一键值的锁定， Next-Key Lock 降级为Record Lock仅存在于查询所有的唯一索引列
扫一扫
182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
