第一章 MySQL体系结构和存储引擎

数据库和数据库实例：

• 数据库是文件的集合，是依照某种数据模型组织起来存放于二进制存储器中的数据集合。
• 数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库的任何操作，包括数据库定义、查询、数据维护、数据库运行控制等都是在数据库实例下进行的。

MySQL组成

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• 缓冲组件
• 插件式存储引擎
• 物理文件

存储引擎

MySQL存储引擎包括：

• InnoDB
• MyISAM
• NDB
• Memory
• Archive
• Federated
• Maria等。

各个引擎之间的差异主要体现在：存储容量的限制、事务支持、锁粒度、MVCC支持、支持的索引、备份和复制等。

第二章 InnoDB存储引擎

InnoDB是MySQL最常用的存储引擎。体系结构主要概括为内存和线程两项。

image.png

后台线程

• Master Thread：缓冲区异步刷新到磁盘，保证数据一致性。包括脏页的刷新、合并插入缓冲区（INSERT BUFFER）、undo页的回收等
• IO Thread：负责AIO（Async IO）处理写IO请求的回调处理。包括四种：insert buffer，log，read，write
• Purge Thread：减轻Master Thread工作，负责回收已经使用并分配的undo页
• Page Cleaner Thread：减轻Master Thread的工作和对用户查询线程的阻塞，负责脏页的刷新操作

内存

image.png

内存包括三个部分：

• 缓冲池
• 重做日志缓冲
• 额外的内存池。

缓冲池

缓冲池中缓存的数据类型有：

• 索引页
• 数据页
• undo页
• 插入缓冲（insert buffer）
• 自适应哈希索引（adaptive hash index）
• InnoDB存储的锁信息（lock info）
• 数据字典信息（data dictionary）

其中索引页和数据页占很大一部分

缓冲池允许有多个实例，通过innodb_buffer_pool_instances配置

LRU List、Free List和Flush List

数据库中的缓冲池是通过LRU（Latest Recent Used，最近最少使用）算法来管理的。但做了修改（优化），新加入的数据放在列表的midpoint位置。
Free List保存的是当前可用的页列表
在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过checkpoint机制将脏页刷新回在磁盘。所以Flush列表中的页即为脏页列表。

需要注意的是：脏页既存在于LRU列表中，页存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响

重做日志缓冲（redo log buffer）

通常情况，默认8M的重做日志缓冲池足以满足绝大部分的应用，因为下列三种情况会将重做日志缓冲的内容刷新到外部磁盘的重做日志文件中：

• Master Thread每秒刷新一次
• 每个事务提交时会刷新
• 当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件

额外的内存池

存储一些缓冲池相关的信息，十分重要。

Checkpoint（检查点）

技术要解决的问题：

• 缩短数据库恢复时间
• 缓冲池不够用时，将脏页刷新到磁盘；
• 重做日志不可用时，刷新脏页。

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库秩序啊哟对Ceheckpoint后的重做日志进行恢复。这样记忆大大缩短了恢复的时间。

InnoDB存储引擎内部，有两种Checkpoint：

• Sharp Checkpoint：发生在数据库关闭时将所有的脏页都刷新回磁盘
• Fuzzy Checkpoint：只刷新一部分脏页，而不是刷新所有的脏页回磁盘

在InnoDB存储引擎中，通过LSN（Log Sequence Number）来标记版本。LSN时8个字节的数字（64位）。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。

Fuzzy Checkpoint可能发生在以下几种情况：

• Master Thread Checkpoint
• FUSH_LRU_LIST Checkpoint
• Async/Sync Flush Checkpoint
• Dirty Page too much Checkpoint

Master Thread工作方式

InnoDB的关键特性

插入缓冲

两次写

自适应哈希索引

异步IO

刷新邻接页等

第三章 文件

InnoDB存储引擎表中包含的文件种类：参数文件、日志文件、socket文件、pid文件、MySQL表结构文件、存储引擎文件。

InnoDB存储引擎文件

重做日志文件

采用多个文件循环写的目的：写满的日志可以进行归档另存为等操作，但一般都没有归档。

当数据库主机由于掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电之前的时刻，来保证数据的完整性。

第四章 表

索引组织表

当没有显示定义主键时：

• 选取表中第一个定义的 非空 唯一索引为主键
• 若没有上述索引，则自动创建一个6字节大小的指针。

但采用单列唯一非空索引作为主键时，可以使用_rowid搜索出来。多列索引则不能使用这个列名称。

InnoDB逻辑存储结构

image.png

InnoDB逻辑空间最高层为表空间。表空间向下可层层划分为：

• 段（Segment）
• 区（Extent）
• 页（Page）/块（block）

表空间

表空间可以看作是InnoDB存储引擎逻辑结构的最高层，所有数据都存放在表空间中。

innodb_file_per_table=1可以让每张表内的数据单独存放到一个表空间内。每张表空间内存放的只是：

• 数据
• 索引
• 插入缓冲Bitmap页

但是：
其他类的信息还是存放在原来的共享表空间内，例如：

• 回滚（undo）信息
• 插入缓冲索引页
• 系统事务信息
• 二次写缓冲（Double write buffer）

段

表空间由段组成，常见的段有：

• 数据段（B+树的叶子结点）
• 索引段（B+树的非索引节点）
• 回滚段

区

由连续页组成，每个区大小为1MB，默认InnoDB存储引擎页大小为16KB，即一个区中共有64个连续的页。此外页的大小可以设置，所以一个区中页的各种会变化，但区的大小总是1MB。

页

页是InnoDB磁盘管理的最小单位。可以通过innodb_page_size设置页的大小：4K, 8K, 16K
在InnoDB存储引擎中，常见的页类型有：

• 数据页（B-tree Node）
• undo页（undo Log Page）
• 系统页（System Page）
• 事务数据页（Transaction system Page）
• 插入缓冲位图页（Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List）
• 未压缩的二进制大对象页（Uncompressed BLOB Page）
• 压缩的二进制大对象页（Compressed BLOB Page）

行

InnoDB存储引擎是面向行的（row-oriented）

InnoDB行记录格式

• Compact
• Redundant格式为了兼容旧版

Compact行记录格式

image.png

NULL标志位使用位来表示后续列中哪些是NULL，默认是1字节，但列增多的话会继续以1字节位单位扩充，高位补零。所以NULL的列越多，专门用来记录NULL需要的字节越多。不建议将字段设置可以为NULL

行溢出数据

一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中，但当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。

第五章 索引与算法

如果索引太多，应用程序的性能可能会收到影响，而索引太少，对查询性能又会产生影响。

InnoDB存储引擎索引概述

InnoDB存储引擎支持：

• B+树索引
• 全文索引
• 哈希索引

B+树

B+树是为磁盘或者其他直接存取辅助设备设计的一种平衡查找树。

维持平衡的策略

• 拆分页：当页满的时候
• 旋转：当前页满，但左右兄弟节点没有满。减少了页的拆分操作，同时树的高度依然不变

Cardinality值

Cardinality表示索引中不重复记录数量的预估值。在实际应用中，Cardinality/n_rows_in_table应尽可能接近1。

InnoDB存储引擎的Cardinality统计

Cardinality的统计是放在存储引擎层进行的。
仅在INSERT和UPDATE两个操作中可能发生更新：

• 表中的1/16的数据已经发生过变化
• stat_modified_counter > 2 000 000 000
  更新的计算是通过采样计算

B+树索引的使用

不同应用中B+树索引的使用

根据需求设计索引

联合索引

如果没有排序需求，查询优化器优先使用单个值的索引，因为同样大小的页上可以存放更多的记录。如果有两个条件，第二个是排序的则会使用联合索引。

覆盖索引

从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
好处是：

• 覆盖索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
• 执行select count(*) from buy_log; 操作时，使用辅助索引数据量比较小，可以减少IO操作

对于索引(a,b)当查询b的范围统计时，有可能使用。虽然b在索引的第二个字段。

优化器选择不使用索引的情况

对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由于当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。

如果使用固态硬盘，可以使用force index来强制使用某个索引

索引提示

Select a,b from t USER INDEX(a) where a = 1 and b = 2; 建议使用索引a，但优化器可能不听。
Select a,b from t FORCE INDEX(a) where a = 1 and b = 2; 强制使用索引a。

Multi_Range Read 优化

先查到辅助索引和对应的RowID缓存到内存中，按照RowID排序，再查询聚集索引时就变成了尽量的顺序访问了。

Index Condition Pushdown（ICP）优化

在使用索引获取数据的同时使用where条件过滤数据。将过滤操作尽量放在存储引擎层。

哈希算法

InnoDB存储引擎中的哈希算法

对于缓冲池哈希表来说，在缓冲池中的Page页都有一个chain指针，指向相同哈希函数值的页。使用除法散列计算槽的位置，而槽的个数先泽略大于2倍缓冲池页数量的质数。

选质数是为了散列更均匀，算法导论上有讲。

自适应哈希索引

全文检索

概述

从InnoDB 1.2.X版本开始，InnoDB存储引擎开始支持全文索引，其支持MyISAM存储引擎的全部功能，并且还好支持其他一些特性。

InnoDB全文索引

Full Inverted Index，表现形式为{单词, (单词所在文档的ID， 在具体文档中的位置)}
InnoDB支持的全文索引采用Full Inverted Index

全文检索

相关性计算：

• word是否在文档中出现
• word在文档中出现的次数
• word在索引列中的数量
• 多少个文档包含该word

Elasticsearch相关度计算（评分）：

• 词频
• 逆向文档频率
• 文档长度归一值

ElasticSearch相关性打分机制