Mysql 三大特性详解

Mysql Innodb后台线程

工作方式

首先Mysql进程模型是单进程多线程的。所以我们通过ps查找mysqld进程是只有一个。

体系架构

InnoDB存储引擎的架构如下图所以，是由多个内存块组成的内存池，同时又多个后台线程进行工作，文件是存储磁盘上的数据。

后台线程

上面看到一共有四种后台线程，每种线程都在不停地做自己的工作，他们的分工如下:

• Master Thread: 是最核心的线程，主要负责将缓冲池中的数据异步刷新的磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSERT BUFFER),UNDO页的回收等。下面几个线程其实是为了分担主线程的压力而在最新的版本中添加的。
• IO Thread: InnoDB使用大量的异步IO来处理请求。IO Thread的主要工作就是负责IO请求的回调(call back)处理。异步IO可以分为4个，分别是:write, read, insert buffer 和 log IO thread。
• Purge Thread: undo log是用来保证事务的，当一个事务正常提交后，这个undo log可能就不再使用了。purge thread就是用来清除这部分log已经分配的undo页的。
• Page Cleaner Thread: 主要是把脏页的刷新从主线程中拿到单独的线程，减轻主线程的压力，减少用户查询线程的阻塞，提高整体性能。

Mysql Innodb内存结构

具体来看缓冲池中缓存的数据页类型有:

• 索引页: 缓存数据表索引
• 数据页: 缓存数据页，占缓冲池的绝大部分
• undo页: undo页是保存事务，为回滚做准备的。
• 插入缓冲(Insert buffer): 上面提到的插入数据时要先插入到缓存池中。
• 自适应哈希索引(adaptive hash index): 除了B+ Tree索引外，在缓冲池还会维护一个哈希索引，以便在缓冲池中快速找到数据页。
• InnoDB存储的锁信息(lock info):
• 数据字典(data dictionary):
• 内存中除了缓冲池外外还有:
• 重做日志缓冲redo log: 为了避免数据丢失的问题，当前数据库系统普遍采用了write ahead log策略，既当事务提交时先写重做日志，再修改写页。当由于发生宕机而导致数据丢失时，可以通过重做日志进行恢复。InnoDB先将重做日志放到这个缓冲区，然后按照一定的频率更新到重做日志文件中。重做日志一般在下列情况下会刷新内容到文件:

1.Master Thread每一秒将重做日志缓冲刷新到重做日志文件

2.每个事务提交时会将重做日志缓冲刷新到重做日志文件

3.当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件

• 额外内存池: InnoDB存储引擎中，对内存的管理师通过一种称为内存堆的方式进行的，在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

缓冲池是一个很大的内存区域，InnoDB是如何对这些内存进行管理的呢。答案就使用LRU list。
LRU(Latest Recent Used, 最近最少使用)算法默认的是最近使用的放到表头，最早使用的放到表尾，依次排列。当有LRU填满时有新的进来就把最早的淘汰掉。InnoDB则是在这个基础上进行了修改:

最近使用的不放到表头，而是根据配置放到一定比例处，这个地方叫做midpoint, midpoint之前的成为new列表，之后的成为old列表。淘汰的同样是表尾的页。
为了保证new列表的不经常使用时能够淘汰，设置了一个超时时间:innodb_old_blocks_time，当数据在midpoint(我理解应该是在old列表中，不然这个点的页就一个，变化也比较频繁)的时间超过找个时间时就会被提升到表头，new列表的表尾页则被置换到old列表中。
这么做的原因主要是因为常见的索引或数据的扫描操作会连续读取大量的页，甚至是全表扫描。如果采用原来的LRU算法就会更新全部的缓冲池，其他查询需要的热点数据就会被冲走，导致更多的磁盘读取操作，降低数据库的性能。
LRU是用来管理已经读取的页，当数据库启动时LRU是空列表,既只有表头，没有内容。这时页都放在Free List中。当需要有数据读写时要进行需要获取分页，这时要从Free List中删除分页，然后添加到LRU list中。到一定时间Free List中的分页就会被分配完毕，这时候就正常使用上面的LRU策略。
LRU列表中的页被修改后，称该页为脏页(dirty page),既缓冲池中的数据和磁盘上的数据产生了不一致，这时脏页会被加入到一个Flush 列表中(注意，同时存在两个列表中)。然后根据刷新的机制定时的刷新到磁盘中。

三大特性之一插入缓冲

1.1聚簇索引与非聚簇索引的区别

聚集索引的叶子节点存储的是数据，而且是按照物理顺序存储的;非聚集索引叶子节点是地址(也就是聚集索引键地址)，是按照逻辑顺序存储的(以上言论是从网上了解到的，但是本书P194特别指出，聚集索引也不是按照物理地址连续的，而是逻辑上连续的)。

1.2高并发后的insert会发生什么？

1.  
   mysql> create table t ( id int auto_increment,
2.  
   name varchar(30),primary key (id),key(name));
3.  
   Query OK, 0 rows affected (0.21 sec)

我们知道，主键是行唯一的标识符，在应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取，速度会很快，但是我们看到上一个表还有一个叫做name的索引字段，这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键id的执行顺序存放，但是对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地访问非聚集索引页，插入性能在这里变低了。然而这并不是这个name字段上索引的错误，是因为B+树的特性决定了非聚集索引插入的离散性。

1.3插入缓冲的实现

在innodb的1.0x版本开始，引入了change buffer，可以把它看成insert buffer的升级版，innodb可以对DML操作-INSERT/DELETE/UPDATE都进行缓冲。

1.将一个辅助索引插入到页(space,offset) 
2.检查这个页是否在缓冲池中 
在:直接插入 
不在:继续 

3.缓存进入insert buffer
4.构造一个search key 
5.查询insert buffer树 
6.生成逻辑记录并插入树中

1.3.1insert buffer内部实现原理

在mysql4.1版本之后，insert buffer是通过一全局唯一的一个B+树进行管理所有表的辅助索引。而这颗树存放在共享表空间中，格式为ibdata1，所以如果试图通过独立表空间idb文件恢复表中数据的时候，往往会导致CHECK TABLE失败，这是因为表的辅助索引中的数据可能还在INSERT BUFFER中，也就是共享表空间中，所以通过ibd文件进行恢复后，还需要进行REPAIR TABLE 操作来重建表上所有的辅助索引。

insert buffer的b+树的非叶子节点存放的是查询的search key（键值），其构造如图：

• space为表空间id
• marker用来兼容老版本
• offset表示页所在偏移量

叶子节点会比非叶子节点多俩数据，一个是metadata，一个是secondary index record。

 

• metadata 记录的每一列的类型,长度
• secondary index record 记录的具体值

为了保证每个辅助索引页Merge Insert Buffer的B+树必须成功，还需要有一个特殊的页用来标记每个辅助索引页（space，page_no）的可用空间。这个页的类型为Insert Buffer Bitmap。它会标记16385个辅助索引页，每个辅助索引页会在其中占用4bit的位置来记录信息，具体信息如下：

 

 

Merge Insert Buffer的操作可能发生在以下几种情况下：

• 辅助索引页被读取到缓冲池时；
• Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时；
• Master Thread。

第一种情况为当辅助索引页被读取到缓冲池中时，例如这在执行正常的SELECT查询操作，这时需要检查Insert Buffer Bitmap页，然后确认该辅助索引页是否有记录存放于Insert Buffer B+树中。若有，则将Insert Buffer B+树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。
Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，并至少有1/32页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于1/32页，则会强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况。
还有一种情况，之前在分析Master Thread时曾讲到，在Master Thread线程中每秒或每10秒会进行一次Merge Insert Buffer的操作，不同之处在于每次进行merge操作的页的数量不同。

 

1.4缓冲的限制条件

插入缓冲的启用需要满足一下两个条件：
1）索引是辅助索引（secondary index）
2）索引不适合唯一的

原因是因为插入缓冲本身就是为了解决二级索引离散插入的问题，所以建立一个缓冲区将部分离散的索引数据合并，使用一次大的IO操作统一刷到磁盘，如果索引是唯一的，那这么做将失去意义，而且每次还需要去询问数据页是否已经存在，还会增加额外的IO操作。

 

1.5插入缓冲性能影响

任何一项技术在带来好处的同时，必然也带来坏处。插入缓冲主要带来如下两个坏处：
1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。
2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。

 

1.6观察

1.  
   show engine innodb status\g;
2. -------------------------------------
3. INSERT BUFFER AND ADAPTIVE HASH INDEX
4. -------------------------------------
5.  
   Ibuf: size 1, free list len 0, seg size 2, 2 merges
6.  
   merged operations:
7.  
   insert 0, delete mark 0, delete 0
8.  
   discarded operations:
9.  
   insert 0, delete mark 0, delete 0
10.  
    Hash table size 34679, node heap has 0 buffer(s)
11.  
    Hash table size 34679, node heap has 0 buffer(s)
12.  
    Hash table size 34679, node heap has 1 buffer(s)
13.  
    Hash table size 34679, node heap has 0 buffer(s)
14.  
    Hash table size 34679, node heap has 1 buffer(s)
15.  
    Hash table size 34679, node heap has 0 buffer(s)
16.  
    Hash table size 34679, node heap has 0 buffer(s)
17.  
    Hash table size 34679, node heap has 0 buffer(s)
18.  
    0.00 hash searches/s, 0.00 non-hash searches/s

从上面可以看到其中有一部分叫做INSERT BUFFER AND ADAPTIVE HASH INDEX，其中的seg size指的就是当前insert buffer的大小，具体计算方式为seg_size*16KB =32KB，free list len表示空闲列表的长度，size表示已经合并记录页的数量。

三大特性之二double write

2.1、基本概念

2.1.1页断裂（partial write）

所谓页断裂是数据库宕机时(OS重启，或主机掉电重启)，数据库页面只有部分写入磁盘，导致页面出现不一致的情况。那么为什么会不一样呢？因为数据库，OS和磁盘读写的基本单位是块，也可以称之为(page size)block size。我们知道数据库的块一般为8K，16K；而OS的块则一般为4K；IO块则更小，linux内核要求IO block size<=OS block size。磁盘IO除了IO block size，还有一个概念是扇区(IO sector)，扇区是磁盘物理操作的基本单位，而IO 块是磁盘操作的逻辑单位，一个IO块对应一个或多个扇区，扇区大小一般为512个字节。所以各个块大小的关系可以梳理如下：

DB block > OS block >= IO block > 磁盘 sector，而且他们之间保持了整数倍的关系。所以说当数据库突然宕机，就会造成部分DB block的数据实际上并未写入到磁盘的sector中，出现了页断裂的情况，进而导致数据不一致的现象。

2.1.2数据库日志的三种格式

数据库系统实现日志主要有三种格式，逻辑日志(logical logging)，物理日志(physical logging)，物理逻辑日志(physiological logging)，逻辑日志，记录一个个逻辑操作，不涉及物理存储位置信息，比如mysql的binlog；物理日志，则是记录一个个具体物理位置的操作，比如在2号表空间，1号文件，48页的233这个offset地方写入了8个字节的数据，通过(group_id,file_id,page_no,offset)4元组，就能唯一确定数据存储在磁盘的物理位置；物理逻辑日志是物理日志和逻辑日志的混合，如果一个数据库操作(DDL，DML，DCL)产生的日志跨越了多个页面，那么会产生多个物理页面的日志，但对于每个物理页面日志，里面记录则是逻辑信息。这里我举一个简单的INSERT操作来说明几种日志形式。
比如innodb表T(c1,c2, key key_c1(c1)),插入记录row1(1,’abc’)
逻辑日志：

<insert OP, T, 1,’abc’>

 

逻辑物理日志：因为表T含有索引key_c1, 一次插入操作至少涉及两次B树操作，二次B树必然涉及至少两个物理页面,因此至少有两条日志

1.  
   <insert OP, page_no_1, log_body>
2.  
   <insert OP, page_no_2, log_body>

物理理日志：由于一次INSERT操作，物理上来说要修改页头信息(如,页内的记录数要加1)，要修改相邻记录里的链表指针，要修改Slot属性等，因此对应逻辑物理日志的每一条日志，都会有N条物理日志产生。

1.  
   < group_id,file_id,page_no,offset1, value1>
2.  
   < group_id,file_id,page_no,offset2, value2>
3.  
   ……
4.  
   < group_id,file_id,page_no,offsetN, valueN>

因此对于上述一个INSERT操作，会产生一条逻辑日志，二条逻辑物理日志，2*N条物理日志。从上面简单的分析可以看出，逻辑日志的日志量最小，而物理日志的日志量最大；物理日志是纯物理的；而逻辑物理日志则页间物理，页内逻辑，所谓physical-to-a-page, logical-within-a-page。

2.2 数据一致性

2.2.1页断裂和数据一致性

       前面我们分析了异常重启导致页断裂的原因，而页断裂就意味着数据库页面不完整，那么数据库页面不完整就意味着数据库不一致。我们知道，数据库异常重启时，自身有异常恢复机制，主流数据库基本原理类似：第一阶段重做redo日志，恢复数据页和undo页到异常crash时的状态；第二阶段，根据undo页的内容，回滚没有提交事务的修改。通过两个阶段保证了数据库的一致性。对于mysql而言，在第一阶段，若出现页断裂问题，则无法通过重做redo日志恢复，进而导致恢复中断，数据库不一致。这里大家可能会有疑问，数据库的redo不是记录了所有的变更，并且是物理的吗？理论上来说，无论页面是否断裂，从上一个检查点对应的redo位置开始，一直重做redo，页面自然能恢复到正常状态。对吗？

2.2.2redo格式与数据一致性

       回到“发生页断裂后，是否会影响数据库一致性”的问题，发生页断裂后，对于利用纯物理日志实现redo的数据库不受影响，因为每一条redo日志完全不依赖物理页的状态，并且是幂等的(执行一次与N次，结果是一样的)，而逻辑物理日志则不行，比如修改页头信息，页内记录数加1，slot信息修改等都依赖于页面处于一个一致状态，否则就无法正确重做redo。而mysql正是采用这种日志类型，另外要说明一点，redo日志的页大小一般设计为512个字节，因此redo日志页本身不会发生页断裂。所以发生页面断裂时，异常恢复就会出现问题，需要借助于double write技术来辅助处理。

2.3 doubleWrite的实现

在InnoDB将BP中的Dirty Page刷（flush）到磁盘上时，首先会将（memcpy函数）Page刷到InnoDB tablespace的一个区域中，我们称该区域为Double write Buffer（大小为2MB，每次写入1MB，128个页，每个页16k,其中120个页为后台线程的批量刷Dirty Page，还有8个也是为了前台起的sigle Page Flash线程，用户可以主动请求，并且能迅速的提供空余的空间）。在向Double write Buffer写入成功后，第二步、再将数据分别刷到一个共享空间和真正应该存在的位置。具体的流程如下图所示：

2.4doubleWrite的保护机制

下面来看下在不同的写入阶段，操作系统crash后，double write带来的保护机制：

 

阶段一：copy过程中，操作系统crash，重启之后，脏页未刷到磁盘，但更早的数据并没有发生损坏，重新写入即可阶段二：write到共享表空间过程中，操作系统crash，重启之后，脏页未刷到磁盘，但更早的数据并没有发生损坏，重新写入即可阶段三：write到独立表空间过程中，操作系统crash，重启之后，发现：（1）数据文件内的页损坏：头尾checksum值不匹配（即出现了partial page write的问题）。从共享表空间中的doublewrite segment内恢复该页的一个副本到数据文件，再应用redo log；（2）若页自身的checksum匹配，但与doublewrite segment中对应页的checksum不匹配，则统一可以通过apply redo log来恢复。）阶段X：recover过程中，操作系统crash，重启之后，innodb面对的情况同阶段三一样（数据文件损坏，但共享表空间内有副本），再次应用redo log即可。

2.5doubleWrite对性能的影响

系统需要将数据写两份，一般认为，Double Write是会降低系统性能的。peter猜测可能会有5-10%的性能损失，但是因为实现了数据的一致，是值得的。Mark Callaghan认为这应该是存储层面应该解决的问题，放在数据库层面无疑是牺牲了很多性能的。事实上，Double Write对性能影响并没有你想象（写两遍性能应该降低了50%吧？）的那么大。在BP中一次性往往会有很多的Dirty Page同时被flush，Double Write则把这些写操作，由随机写转化为了顺序写。而在Double Write的第二个阶段，因为Double Write Buffer中积累了很多Dirty Page，所以向真正的数据文件中写数据的时候，可能有很多写操作可以合并，这样有可能会降低Fsync的调用次数。基于上面的原因，Double Write并没有想象的那么糟。最后发现打开和关闭Double Write对效率的影响并不大。

三大特性之三自适应哈希索引

 

哈希索引只有Memory, NDB两种引擎支持，Memory引擎默认支持哈希索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。但是，Memory引擎表只对能够适合机器的内存切实有限的数据集。要使InnoDB或MyISAM支持哈希索引，可以通过伪哈希索引来实现，但是innodb还实现了一种叫做自适应哈希索引来达到目的。

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

3.1、状态监控

1.  
   mysql> show engine innodb status\G
2.  
   ……
3.  
   Hash table size 34673, node heap has 0 buffer(s)
4.  
   0.00 hash searches/s, 0.00 non-hash searches/s

1、34673：字节为单位，占用内存空间总量

　　2、通过hash searches、non-hash searches计算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引

3.2、限制

　　1、只能用于等值比较，例如=， <=>，in
　　2、无法用于排序
　　3、有冲突可能
　　4、MySQL自动管理，人为无法干预。

3、自适应哈希索引的控制

　　由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了adaptive hash index功能，但是通过上面的状态监控，可以计算其收益以及付出，控制该功能开启与否。