【读过的书，留下的迹】MySQL技术内幕-InnoDB存储引擎
1 MySQL体系结构和存储引擎
（1）MySQL存储引擎
-
InnoDB存储引擎
- 支持事务
- 支持行锁
- 支持外键
- 支持非锁定读
-
MyISAM存储引擎
- 不支持事务
- 表锁
- 支持全文索引
2 InnoDB存储引擎
（1）InnoDB体系架构
- 后台线程
- Master Thread：负责数据异步刷新到磁盘，保证数据的一致性
- IO Thread：IO请求
- Purge Thread：挥手已经使用并分配的undo页
- 内存池
- 主要包括索引页，数据页，undo页，插入缓冲，自适应哈希索引，锁信息，字典信息
（2）InnoDB关键特性
- 插入缓冲：对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页，而是先判断插入的非聚集索引页是否在缓冲池中，若在则直接插入；若不在，则先放入到一个Insert Buffer对象中
- 两次写：重做日志中记录的是对页的物理操作，在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做
- 自适应哈希索引：InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引
- 异步IO：用户可以在发出一个IO请求后立即再发出另一个IO请求
- 刷新邻接页：当刷新一个脏页时，InnoDB存储引擎会检测该页所在的区（extent）的所有页，如果是脏页，那么一起刷新
3 文件
（1）日志文件
- 错误日志：对MySQL的启动、运行、关闭过程进行了记录，不仅记录所有错误信息，也记录一些警告或正确信息。
- 慢查询日志：定位可能存在问题的SQL语句
- 查询日志：记录所有对MySQL数据库请求的信息
- 二进制日志：记录对MySQL数据库执行更改的所有操作
（2）InnoDB存储引擎文件
- 表空间文件：InnoDB采用将存储的数据按表空间进行存放的设计
- 重做日志文件：它与二进制日志的区别
- 二进制日志记录所有与MySQL有关的日志；而InnoDB存储引擎重做日志只记录有关InnoDB的事务日志
- 二进制记录关于一个事务的具体操作内容；重做日志记录关于每个页的更改的物理情况
- 二进制日志仅在事务提交前进行提交；而在事务进行的过程中，却不断有重做日志被写入
4 表
4.1 InnoDB逻辑存储结构
从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称为表空间。表空间又由段、区、页组成
- 段：常见的段有数据段、索引段、回滚段
- 区：区由连续的页组成，在任何情况下每个区的大小都为1MB
- 对于小表，开始时先用32个碎片页来存放数据，用完后才会申请64个连续页（一个区），这样可以节约空间
- 页：默认每个大小为16KB
4.1 InnoDB行记录格式
（1）Compact行记录格式（老版本InnoDB）
- NULL列不占任何空间，只占NULL标志位
- 页内部通过一种链表的结构来串连各个行记录
- 不管是BLOB或VARCHAR，当数据页放不下时，部分存储在数据页，其余存储在溢出页（需保证一个数据页至少有两条记录）
（2）Compressed行记录格式（新版本InnoDB，InnoDB1.0.x开始支持）
- 对于BLOB中的数据采用完全的行溢出存储
（3）char的行结构存储
对于UTF-8下的CHAR(10)类型的列，其最小可以存储10字节的字符，最大可以存储30字节的字符。
因此对于多字节字符编码的CHAR数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型，与VARCHAR的实际存储基本没区别。
4.2 InnoDB数据页结构
- File Header：用来记录页的一些头信息
- Page Header：用来记录数据页的状态信息
- Infimum和Supremum Record：用来限定记录的边界
- User Record和Free Space
- Page Directory：B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找
- File Trailer：检测页是否已经完整地写入磁盘
4.3 分区表/font>
分区地过程是将一个表或索引分解为多个更小、更可管理地部分。MySQL数据库支持地分区类型是水平分区，并不支持垂直分区。
（1）分区类型
- RANGE：NULL值小于任何一个非NULL值，NULL值分在最左边地分区
- LIST：若需NULL分区，必须显式指出那个分区放NULL值
- HASH：HASH和KEY，任何函数都会将含有NULL值地记录返回0
- KEY
（2）分区性能
数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏；另一类是OLAP（在线分析处理），如数据仓库、数据集市。
对于OLAP的应用，分区可以很好提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。
对于OLTP的应用，分区应该非常小心。因为其大部分都是通过索引返回几条记录。对于一张大表，一般的B+树需要2～3次磁盘IO，能较快解决问题。分区反而可能会导致更多次磁盘IO。
5 索引与算法
5.1 B+树
所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
一般插入操作
一般删除操作
ps：为尽量减少分页，B+树提供了旋转操作，旋转发生在Leaf Page已经满，但其左右兄弟节点没有满的情况下
5.2 B+树索引
数据库中的B+树索引可以分为：
- 聚集索引：按每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页
- 辅助索引（非聚集索引）：叶子节点并不包含行记录的全部数据
（1）聚集索引
- 聚集索引的存储并不是物理上连续的，而是逻辑上连续的
- 对于主键的排序查找和范围查找速度非常快
（2）非聚集索引
- 过程：InnoDB存储引擎会遍历辅助索引并通过页级别的指针（书签页）获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录
其他数据库，如SQL Server，或引擎，如MyISAM，无聚集索引。它们通过维护一个堆表，即行数据的存储按照插入的顺序存放，这时索引的书签页是一个行标识符
5.3 Cardinality值
Cardinality值表示索引中不重复记录数量的预估值，是通过抽样的方法估算的（默认随机对8个叶子节点抽样）
什么时候索引有用：
- 在访问表中很少一部分行记录时B+树索引才有意义
- Cardinality/n_rows_in_table值接近1时
5.4 B+树索引的使用
（1）联合索引
- 数据页按联合索引的顺序存放，如（a, b, c)
- 左前缀查找有效：如
- where a = 1 and b < 1
- where a = 1 and b =3
- where a like ‘some%’
（2）覆盖索引
- 覆盖索引：即从辅助索引中就可以得到查询的记录，不需要查询聚集索引中的记录
5.5 哈希索引
InnoDB中是自适应哈希索引，数据库自身创建并使用，不能对其进行干预
5.6 全文索引
全文索引是将存储与数据库中的整本书或整篇文章中的任意内容信息查找出来的技术，采用倒排序索引实现，它在辅助表中存储了单词与单词自身在一个或多个文档所在位置之间的映射：
- inverted file index，表形式：｛单词，单词所在文档、记录的ID｝
full inverted index，表形式：｛单词，单词所在文档、记录的ID，具体位置｝
-
InnoDB引擎：
- 有stopword列表，表示该列表中的word不需要索引分词
- 限制
- 每张表只能有一个全文检索的索引
- 不支持每个单词界定符的语言，如中文，韩文
6 锁
6.1 InnoDB中的锁
（1）一致性非锁定读
是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。
数据的多版本是通过undo段来完成的。
在不同事务隔离级别下，读取的方式不同，READ COMMITTED和REPEATABLE READ下，使用的是非锁定一致性读。
（2）一致性锁定读
- select…for update：X锁，排他锁
- select…lock in share mode：S锁，共享锁
（3）自增长与锁
InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，插入记录会用互斥量去对内存中的计数器进行累加的操作
（4）外键和锁
在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB会自动对其加一个索引
对于外键值的插入或更新，首先需要select父表，并主动对父表加一个S锁
6.2 锁的算法
（1）行锁的3种算法
- Record Lock：单个行记录上的锁
- Gap Lock：锁定一个范围，但不包括记录本身
- Next-Key Lock：Gap Lock＋Record Lock，锁定一个范围，并且锁定记录本身
InnoDB对于行的查询都是采用Next-Key Lock，例如一个所有有1，2，则Next-Key Lock的区间为(-inif, 1), (1, 2), (2, +inif)
6.3 锁的问题
- InnoDB默认事务隔离级别是repeatable read，在该隔离级别下，采用Next-key Lock的方式加锁
- InnoDB的read commited，仅采用record lock
7 事务
7.1 分类
- 扁平事务：由begin，start transaction开始，由commit，rollback结束
- 带由保存点的扁平事务：允许在事务执行过程中回滚到同一事务中较早的一个状态
- 链事务
- 嵌套事务：子事务在父事务提交后才真正提交；父事务的回滚引起子事务的回滚
- 分布式事务
7.2 事务的实现
事务隔离性由锁实现，原子性和持久性由redo log实现，一致性由undo log实现
（1）redo log
每一次事务提交时，必须先将事务的所有日志写入到redo log，再commit
（2）redo log和binary log区别
- redo log是在innodb层次的，binary log是mysql层次
- binary log记录的是对应的sql语句，redo log记录对于每个页的修改
- binary log只在事务提交完成后进行一次写入，而redo log在事务进行中不断写入
（3）undo log
事务有时需要进行回滚，这时需要undo log，undo log是逻辑日志，它实际上做的是与先前相反的工作
7.3 事务的隔离级别
大部分其他数据库的默认为read commited，而InnoDB默认采用repeatable read。在默认隔离级别下，因为采用Next-key Lock，解决了幻读的问题，实际上已经满足serializable隔离级别
8 备份与恢复
（1）分类
- 热备份：数据库运行中直接备份，对正在运行的数据库操作没有任何影响
- 冷备份：备份操作是在数据库停止的情况下进行，一般只需复制相关数据库物理文件
- 温备份：在数据库运行中进行，但会对当前数据库的操作有所影响
（2）热备份
原理
- 记录备份开始时，InnoDB存储引擎重做日志文件检查点
- 复制共享表空间文件以及独立表空间文件
- 记录复制完表空间文件后，InnoDB存储引擎重做日志文件检查点
- 复制在备份时产生的重做日志