Mysql数据库之 InnoDB存储引擎

写在前面，对于mysql数据库存储引擎这块的知识比较杂乱无章，特别是对于存储引擎下面涉及到的理论知识，楼主在第一遍看完《mysql技术内幕 InnoDB存储引擎》后，脑海里只留下一点关于存储引擎的基本概念，就像是看完一场电影一样，可能是缺乏实践操作，也有可能是对于其中的理解不够透彻。所以决定再次阅读该书，并记录自己的笔记，以增强理解记忆。

InnoDB体系架构

后台线程

InnoDB存储引擎有多个内存块，可以理解这些内存块组成了一个大的内存池，负责一系列工作。

通过InnoDb存储引擎体系架构可以看出其是一个多线程的模型，后台具有多个不同的线程，负责不同的任务。这里面主要有Master Thread、IO Thread、Purge Thread等线程。具体功能分别如下：

Master Thread：是一个核心线程，主要作用是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。

IO Thread：主要处理IO请求的回调处理。

Purge Thread：回收不再使用的undolog。

内存

缓冲池

InnoDb存储引擎是基于磁盘存储的，其中的记录是按照页的方式进行管理的。在数据库系统中，CPU的处理速度比磁盘的要快的多，为了不因为磁盘的读写速度拖垮CPU，在CPU和磁盘之间存在一个内存区域称为缓冲区。（具体有关缓存的知识可以看计算机相关专业知识，这里不再赘述）。缓冲区的大小直接影响到数据库的整体性能，对于InnoDb存储引擎来说，缓冲池的配置通过参数innodb_buffer_pool_size来设置。另外要特别注意不要认为缓冲区中缓存的数据类型只有数据页和索引页，缓冲区缓存的数据类型除了索引页和数据页外，还有undo页、插入缓冲、自适应哈希索引等，如下图所示

缓区的管理

上一节介绍过缓存区是一块内存很大的区域，存放的页的种类也非常多，那么InnoDb存储引擎是如何管理这块大的内存的呢。答案是采用LRU算法来管理的。关于LRU算法，楼主想到了计算机操作系统里面的分页、调页的LRU（不懂得可以去翻阅资料了解下），这里的算法和计算机操作系统里面的是一模一样的，只是InnoDb存储引擎对LRU算法做了一点优化。我们知道传统的LRU算法是将新的页插入到LRU列表的头部，但是在数据库中并不是这样。InnoDb存储引擎在LRU列表中加入了一个midpoint，新加入的页不是直接放到LRU列表的头部，而是放在了midpoint位置。另midpoint位于LRU列表大小的5/8处。这里楼主当时读到这个地方有个疑问，既然传统的LRU算法已经可以很好的解决分页、调页问题，至少计算机操作系统里面是采用过的，说明在大多数场景下，朴素的LRU算法是实用的。但是InnoDB存储引擎为什么要将LRU改进呢？这是因为如果将读取到的页直接放到首页，某些SQL操作可能使其他的页刷出内存，常见的操作有数据库扫描操作等，而该操作可能只会仅仅在查询的时候需要，并不是活跃的数据，如果放在LRU首部，就想一个占着茅坑不拉屎的人一样，那些真正的热点数据不能够在首部或者直接被刷新出去，大大降低了数据库执行的效率，如图所示（图片参考https://www.cnblogs.com/BoNuo/articles/10203417.html大佬的）。

Checkpoint技术

一般所说的checkpoint是一个数据库事件，当checkpoint事件发生时DBWn会将脏块写入到磁盘中，同时数据文件和控制文件的文件头也会被更新以记录checkpoint信息。因此checkpoint技术的目的是解决以下几个问题：

1、 缩短数据库的恢复时间
2、 缓冲池不够用时，将脏页刷新到磁盘
3、 重做日志不可用时，刷新脏页

缩短数据库的恢复时间：数据库发生宕机时，数据库不需要重做所有的日志（前面说的Write Ahead Log），因为checkpoint之前的页都已经刷新回磁盘了，故数据库只需要对checkpoint之后的日志进行恢复，大大缩短了数据的恢复时间。

缓冲池不够用时，将脏页刷新到磁盘：当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若瓷业为脏页，那么就需要强制执行checkpoint，将脏页刷会磁盘。

重做日志不可用时，刷新脏页：理解重做日志不可用，保存着恢复数据的所有信息（且自身已满），这个时候重做日志不可以被覆盖。为了可以继续使用，需要强制执行checkpoint，将缓冲池中的脏页刷回到磁盘（至少刷新到重做日志的位置）。

插入缓冲Insert Buffer

插入缓冲Insert Buffer可能是InnoDB存储引擎关键特性中最令人激动与兴奋的功能。不过插入缓冲Insert Buffer不是缓冲池的一部分（虽然上面的图里面看着像），而是和数据页一样属于物理页的一个组成部分。说到插入缓冲这里我们就必须说一下数据库的索引，说到这个索引啊，我们就不得不提这个主键。笔者在写这篇博文之前总以为主键上建立的索引就是聚集索引，其实有这种感觉我觉得也对，因为对于InnoDB引擎来说确实是这样，但是myisam就不是这样。InnoDB里面如果一个表有主键定义的话，那么该主键就是聚集索引；若没有主键定义，InnoDB会将该表的第一个唯一非空的字段作为聚集索引；若既没有主键，又没有符合条件的非空字段，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，该隐藏主键是一个6字节的列，该列的值会随着数据的插入而自增。

至于什么叫聚集索引和非聚集索引，我想在这里再次强调一下：聚集索引就是指数据库表中的数据的物理顺序和键值的逻辑顺序一致，一个表值只能有一个聚集索引，因为一个表的物理顺序是一定的，所以对应的聚集索引就只能有一个。不满足这样条件的索引就是非聚集索引，聚集索引的数据检索速度比非聚集索引快的多。（关于数据库索引的底层实现即B-树、B树、B+树和B*树的知识，此处不再赘述）。

我们现在设计一个表的时候一般都是有主键，且主键都是自增长的，这时插入的索引是连续的，也就是聚集索引。聚集索引的好处是一般数据都是顺序存储的。如果你的sql读的是某一块连续的数据块，这样因为聚集索引的连续，你不用访问多个数据页，大大减少了IO次数，提升了查询速度。一般情况下，主键索引的插入也非常快，因为不需要离散的读取数据页，除非你自己设置了主键的值，比如说你第一次插入的主键是1，后面的sql，自己设置主键非要设置个1000，那么极有可能和第一次插入的主键索引不在一个数据页上，这时需要额外的一次IO。OK，没问题，以后我不自己设置主键的值，那么非聚集索引呢？就是普通的索引，连续性不能保证了怎么办？那么就到了今天要讲的insert buffer了。

对于非聚集索引来说，比如存在用户购买金额这样一个字段，索引是普通索引，每个用户的购买的金额不相同的概率比较大，这样导致可能出现购买记录在数据在数据里的排序可能是1000，3，499，35…，这种不连续的数据，mysql如果说，忽略这种情况，来一个我直接写入磁盘，那么mysql估计很累，一会插入这个数据页，一会插入那个数据页，我们都知道IO是很耗时的，所以出现了Insert Buffer。Insert Buffer是怎么做的呢？mysql对于非聚集索引的插入，先去判断我要插入的索引页是否已经在内存中了，如果不在，我暂时不着急先把索引页加载到内存中，而是把它放到了一个Insert Buffer对象中，临时先放在这，然后等待情况，等待很多和现在情况一样的非聚集索引，等Insert Buffer差不多了，再和要插入的非聚集索引页合并，比如说现在Insert Buffer中有1，99，2，100，合并之前可能要4次插入，合并之后1，2可能是一个页的，99，100可能是一个页的，这样就减少到了2次插入。效率就这样提升了。（记住数据都是插入数据页里面，同一个页里面数据应该是连续的）。

插入缓冲Insert Buffer的条件

1. 索引必须是辅助索引
   如果是聚集索引，那就没什么要合并的必要了
2. 不能是唯一索引
   因为插入缓冲的时候，mysql不会去判断数据的唯一性，如果要判断唯一性，就要离散读，失去了Insert Buffer的意义。

两次写

为了解决partial page write问题，InnoDB实现了double write buffer，简单来说，就是在写数据页之前，先把这个数据页写到一块独立的物理文件位（ibdata），然后再写到数据页。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是double write。

doublewrite有2部分组成，一部分是位于内存中的doublewrite，另一部分是位于磁盘上共享表空间的doublewrite，大小均为2M。

1. 当一系列操作使得脏页需要刷新到磁盘上的时候，并不是直接刷新到磁盘上，而是先写到内存中的doublewrite buffer中。
2. 接着从doublewrite buffer中分2次写入磁盘共享区中（顺序写），每次写1M。
3. 待第二步完成后，再将doublewrite buffer中的脏页数据写入实际的各个表空间文件(离散写)；(脏页数据固化后，即进行标记对应doublewrite数据可覆盖)。

再看redo log 写入关系，可以用下图演示

两次写的作用

在数据库启动时（异常关闭的情况下），都会做数据库恢复（redo）操作。在恢复的过程中，数据库会检查页面是否合法（校验），如果发现一个页面的校验结果不一致，则此时就会用到两次写机制，用两次写空间中的数据来恢复异常页面的数据，这也正是为处理这样的错误而设计的。此时的处理机制就是，将两次写的两个簇都读出来，再将innodb_parallel_doublewrite_path文件的内容读出来，然后将所有这些页面写回到对应的页面中去，这样就可以保证这些页面是正确的，并且是在写入前已经更新过的（最新数据）。在写回对应页面中去之后，就可以在此基础上继续做数据库恢复了，且不会遇到这样的问题了，因为最后有可能产生写断裂的数据页面都恢复了。

上面所讲的都是数据页面有问题的情况下可以通过两次写页面来恢复，但是如果两次写页面本身发生写断裂怎么办呢？ 对于这个问题，大家不必担心。因为如果两次写有问题，则数据页面本身就不会做写操作（一定是先逻辑后物理嘛，逻辑挂了，就没有后面的物理了。），此时系统挂了，发生错误的是两次写页面，而数据页面在挂之前都是在Buffer里面，文件中依然是当前事务操作前的值，并没有变化，还是一致状态，这意味着两次写页面根本就不会被使用到。

异步IO（AIO）

InnoDB存储引擎采用的是异步IO处理数据的

刷新临接页

在将脏页刷出的时候会检查该页所在区的页是否为脏页。若为脏页则一同刷出。但是可能将不怎么脏的页刷出去了。

自适应哈希索引

众所周知，InnoDB使用的索引结构是B+树，但其实它还支持另一种索引：自适应哈希索引。哈希表是数组+链表的形式。通过哈希函数计算每个节点数据中键所对应的哈希桶位置，如果出现哈希冲突，就使用拉链法来解决。

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

特点：

1. 无序，没有树高 。
2. 降低对二级索引树的频繁访问资源索引树高<=4，访问索引：访问树、根节点、叶子节点。
3. 自适应。

缺陷：

1. hash自适应索引会占用innodb buffer pool；
2. 自适应hash索引只适合搜索等值的查询，如select * from table where index_col=‘xxx’，而对于其他查找类型，如范围查找，是不能使用的；
3. 极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

Mysql全文索引

表A：

CREATE TABLE `tableA` (
`id` int(11) NOT NULL auto_increment,
`content` varchar(256) default NULL,
PRIMARY KEY  (`id`),

如果我要在content中进行模糊查询，那么使用like的话，肯定要这样写:

select * from tableA from content like "%xxx%"

这里有个问题，即使我为content加了index索引，那么在下面这两种情况索引也是无效的

content like "%xxx" / like "%xxx%" 都不能使用索引

如果想索引作用只能使用content like “xxx%”
这时候就要使用全文索引来处理了

ALTER TABLE `tableA` ADD FULLTEXT `fidx_content` (`content`)

然后用以下方式查询

SELECT * FROM `tableA`WHERE MATCH (content)AGAINST ('xxx')

EXPLAIN SELECT * FROM `wf_master`WHERE MATCH (cpr_no)AGAINST ('801876')
id  select_type  table       type      possible_keys  key         key_len  ref  rows  Extra
1   SIMPLE       wf_master  fulltext  fidx_cpr_no     fidx_cpr_no  0       1          Using where

需要注意的是全文检索的对象是一个单词，被检索的词需要用非文本隔开的也就是说如果你在"abcd,efg,hijklmn"中检索"hi"，那么全文检索也没有用，如果你检索efg，那么可以使用全文检索。