mysql支持innodb引擎

转载

mob64ca140ce312 2024-07-21 15:53:28

文章标签 mysql支持innodb引擎 MySQL InnoDB InsertBuffer DoubleWrite 文章分类 MySQL 数据库

MySQL InnoDB存储引擎包括以下关键特性：

①插入缓冲（Insert Buffer）
②两次写（Double Write）
③自适应哈希索引（Adaptive Hash Index）
④异步IO（Async IO）
⑤刷新邻接页（Flush Neighbor Page）

这些特性为InnoDB存储引擎带来了更好的性能和更高的可靠性。

插入缓冲

1.Insert Buffer

对于非聚簇索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚簇索引页是否在缓冲池中：若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中。数据库以为这个非聚簇索引已经插入到叶子节点，而实际上并没有，仅仅是存放在另一个位置。然后再以一定的频率和条件执行Insert Buffer和辅助索引叶子节点的merge（合并）操作，此时通常能将多个插入合并到一个操作中（处于同一个索引页），这就大大提高了对于非聚簇索引插入操作的性能。使用Insert Buffer需要同时满足以下两个条件：

①索引是辅助索引（secondary index）；
②索引不是唯一（unique）的。
2.Change Buffer

Insert Buffer的升级。从InnoDB1.0.x版本起，DML操作——INSERT、DELETE和UPDATE都进行缓冲，分别为：Insert Buffer、Delete Buffer和Purge Buffer。和Insert Buffer一样，Change Buffer的适用对象依然是非唯一的辅助索引。

对一条记录进行UPDATE操作可能分为两个过程：

①将记录标记为删除；
②真正将记录删除。

因此Delete Buffer对应UPDATE操作的第一个过程，即将记录标记为删除；Purge Buffer对应UPDATE操作的第二个过程，即将记录真正地删除。

innodb_change_buffer_max_size

mysql> SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'\G *************************** 1. row *************************** Variable_name: innodb_change_buffer_max_size Value: 25 1 row in set (0.00 sec)

可以通过SQL命令“ SHOW ENGINE INNODB STATUS ”查看Change Buffer的信息：

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 

……

-------------------------------------
Ibuf: size 1, free list len 46766, seg size 46768, 20234 merges
merged operations:
 insert 15454, delete mark 5454, delete 896
discarded operations:
 insert 0, delete mark 0, delete 0

……

可以看到merged operations和discarded operations，其下分别为Change Buffer中每个操作的次数：insert表示Insert Buffer；delete mark表示Delete Buffer；delete表示Purge Buffer。discard operations表示当Change Buffer发生merge时，表已经被删除，无需再将记录合并到辅助索引页中。

3.Insert Buffer的内部实现（B+Tree）

Insert Buffer的数据结构是一棵B+Tree。MySQL4.1之前的版本中每张表都有一棵Insert Buffer B+Tree。现在的版本中全局只有一棵Insert Buffer B+Tree，负责对所用表的辅助索引进行Insert Buffer。这棵B+Tree存放在共享表空间中，默认为ibdata1。

Insert Buffer B+Tree的非叶节点存放的是查询的search key（键值），一共占用9个字节，具体如下。

①space：4字节。表示待插入记录所在表的表空间id，InnoDB存储引擎中，每个表都有一个唯一的space id，通过查询space id可以得知是哪张表。
②marker：1字节。用来兼容老版本的Insert Buffer。
③offset：4字节。表示页偏移量。

当一个辅助索引要插入到页（space， offset）时，如果这个页不在缓冲池中，InnoDB存储引擎会首先根据以上规则构造一个search key，接下来查询Insert Buffer B+Tree，然后再将这条记录插入到Insert Buffer B+Tree的叶子节点中。对于插入到Insert Buffer B+Tree叶子节点的记录，并不是直接将待插入的记录插入，而是需要进行构造，额外写入space、marker、offset和metadata字段。space、marker和offset字段和非叶节点中的含义相同。metadata占用4字节：IBUF_REC_OFFSET_COUNT（2字节）、IBUF_REC_OFFSET_TYPE（1字节）和IBUF_REC_OFFSET_FLAGS（1字节）。IBUF_REC_OFFSET_COUNT用来排序每个记录进入Insert Buffer / Change Buffer的顺序，按照这个顺序回放（replay）可以得到记录的正确值。因此，较之原插入记录，Insert Buffer B+Tree叶子节点记录需要额外13个字节的开销。

启用Insert Buffer后，辅助索引页（space， page_no）中的记录可能被插入到Insert Buffer B+Tree中。为了保证每次Merge Insert Buffer页必须成功，需要有一个特殊的页来标记每个辅助索引页（space， page_no）的可用空间，这个页的类型为Insert Buffer Bitmap。每个Insert Buffer Bitmap页用来追踪16384个辅助索引页，即256个区（Extent）。每个Insert Buffer Bitmap页都在16384个页的第二个页中。每个辅助索引页在Insert Buffer Bitmap页中占4位（bit）：IBUF_BITMAP_FREE（2bit）表示该辅助索引页的可用空间大小；IBUF_BITMAP_BUFFERED（1bit）表示该辅助索引页中有记录被缓存在Insert Buffer B+Tree中；IBUF_BITMAP_IBUF（1bit）表示该页为Insert Buffer B+Tree的索引页。

4.Merge Insert Buffer

以下情况发生时InnoDB存储引擎执行Merge Insert Buffer操作。

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

两次写

Insert Buffer带给InnoDB存储引擎的是性能上的提升，doublewrite带给InnoDB存储引擎的是数据页的可靠性。当发生宕机时，可能InnoDB存储引擎正在执行某个数据页的写操作，而这个页只写了一部分（比如16KB页只写了前4KB）就发生了系统崩溃，这种情况称为部分写失效（partial page write）。InnoDB存储引擎使用doublewrite技术前，出现过因为部分写失效而导致数据丢失的情况。当写入失效发生时，用户首先需要一个数据页的副本，通过页的副本来还原该页，这就是doublewrite。还原之后，再应用（apply）重做日志进行重做，重做日志记录的是对页的物理操作（如偏移量800写入记录'aaaa'）。

doublewrite由两部分组成：一部分是内存中的doublewrite buffer，大小为2MB；另一部分是物理磁盘上共享表空间中连续的128个页，即2个区（extent），大小同样为2MB。在对缓冲池的脏页进行刷新时，并不是直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数同步磁盘。以下是一个doublewrite的运行情况。

mysql> SHOW GLOBAL STATUS LIKE 'innodb_dblwr%'\G
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
        Value: 6325194
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
        Value: 100399
2 rows in set (0.00 sec)

Innodb_dblwr_pages_written：Innodb_dblwr_writes的比值远小于64:1，说明应用的写入压力并不是很高。如果操作系统在将页写入磁盘的过程中发生了崩溃，恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

innodb_doublewrite

自适应哈希索引

哈希（hash）是非常快的查找方法，一般情况下查找时间的复杂度为Ο(1)，仅需要一次查找就能定位数据；而B+Tree的查找次数取决于树的高度，生产环境一般需要3 ~ 4次查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称为自适应哈希索引（Adaptive Hash Index， AHI）。AHI是通过缓冲池的B+Tree页构造而来，建立的速度很快，而且不是对整张表构建哈希索引。

InnoDB存储引擎会自动根据访问的频率和模式主动地为某些热点索引页建立哈希索引。AHI的建立需要满足以下要求：

①对页的连续访问模式必须是一样的（对于组合索引(a, b)，“WHERE a=xxx”和“WHERE a=xxx AND b=xxx”两种访问模式都有效，但不能交替进行）；
②以该模式连续访问了100次；
③页通过该模式访问了n次（n = 页中记录 * 1 / 16）。

可以通过SQL命令“ SHOW ENGINE INNODB STATUS ”查看AHI的使用情况。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
……
Hash table size 34673, node heap has 348 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
11.50 hash searches/s, 1586.14 non-hash searches/s
……

查询输出了AHI表的数量、大小、使用情况和每秒使用AHI查找统计。哈希索引只能用来搜索等值查询，如“SELECT * FROM table WHERE index_col = xxx”。对于其他类型的查询，如范围查找，是不能使用哈希索引的，因此同样有non-hash searches/s数据。通过hash searches：non-hash searches可以大致了解使用哈希索引后的效率。

innodb_adaptive_hash_index ”为“0”和参数“ --skip-innodb_adaptive_hash_index ”禁用。

异步IO

每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作，即为同步IO（Synchronous IO， Sync IO）。如果用户发出一条索引扫描的查询，该查询可能需要扫描多个索引页，也就是需要进行多次IO操作。在Sync IO下，每扫描一个页并等待其完成后，再进行下一次扫描。

与Sync IO对应的是异步IO（Asynchronous IO， AIO），用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成。AIO的另一个优势是进行IO Merge操作，也就是将多个IO合并为1个IO，提高IOPS的性能。例如用户需要访问页的（space， page_no）为（8， 6）、（8， 7）、（8， 8），每个页大小为16KB：Sync IO需要进行3次IO；AIO会判断这三个页是连续的，因此AIO底层会发出一个IO请求，从（8， 6）开始读取48KB的页。

Linux操作系统下可以通过iostat命令输出中的rrqm/s（read requests merged per second that were queued to the device）和wrqm/s（write requests merged per second that were queued to the device）查看IO Merge情况。

mysql> system iostat -x
Linux 3.10.0-514.26.2.el7.x86_64 (bilery.zoo) 	2017年11月07日 	_x86_64_	(2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.21    0.03    0.24    0.02    0.00   99.50

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               14.02     54.81    1.54    0.44   120.87     3.00   124.75     0.00    1.14    1.15    1.09   0.41   0.08

为了提高磁盘操作的性能，当前数据库系统都采用AIO，InnoDB存储引擎亦是如此。InnoDB1.1.x之前，AIO是通过InnoDB存储引擎中的代码来模拟实现的。从InnoDB1.1.x开始（InnoDB Plugin不支持）提供了内核级别的AIO支持，称为Native AIO。Native AIO需要操作系统提供支持：Windows和Linux都提供Native AIO支持；Mac OSX则未提供。

innodb_use_native_aio