MySQL技术：InnoDB存储引擎关键特性之插入缓冲

一、聚集索引和非聚集索引的插入

1、聚集索引的插入

• 在InnoDB存储引擎中，主键是行的唯一标识符，通常应用程序中行记录插入的顺序就是按照主键递增的顺序进行插入的
• 聚集索引有这样的特点：行物理地址与主键的逻辑顺序是相同的
• 这就意味着插入聚集索引的时候不需要进行磁盘的随机读取，直接顺序插入即可。
• 由于索引页中的索引也是顺序存放的，所以只要在页中顺序插入，不需要读取其他页

2、非聚集索引的插入

• 由于非聚集索引树中的叶子节点的插入不再是顺序的，所以每次插入都需要离散的访问非聚集索引页
• 这种随机读取的存在就导致了插入操作的性能下降，这个特性其实是因为B+树的特性决定的
• 但是需要注意的是，在某些特殊情况下辅助索引的插入依然是顺序的，或者说是比较顺序的，比如用户购买表中的时间字段

3、Insert Buffer原理概述

• InnoDB存储引擎开创性地设计的Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入，如果不在，那就先放到一个Insert Buffer对象中
• 这就好似欺骗，数据库的非聚集索引已经插入到叶子结点中了，但是实际上并没有 ，只是存起来了，然后一一定的频率和情况进行Insert Buffer和辅助索引页的合并操作
• 这时候通常就可以将多个插入合并到一个操作中（因为在一个索引页中），这就大大的提高了对非聚集索引的插入性能

4、 Insert Buffer使用条件

• 索引是辅助索引
• 索引不是唯一的

索引是辅助索引：Insert Buffer本身就是针对于非聚合索引的插入出现的，所以必须是非聚合索引的插入才可以使用

索引不是唯一的：因为在插入缓冲的时候，数据库并不去查找索引页来判断插入的记录的唯一性，如果去查找肯定会有离散读取的情况发生，这就使其失去了意义。因为判断是否唯一需要扫描索引页，而扫描索引页可能涉及到随机读写磁盘，这里引入插入缓冲，就是为了避免多次随机读写磁盘引入的。

二、不访问索引的其中一种情况

　　索引成本比全表扫描的成本高，就算建了索引也不会走索引。

1、全表扫描的成本

　　多块读，扫描冷数据，造成大量的物理读。

　　通过预读机制，将可能要访问的数据读入内存，减少io访问磁盘次数。 

2、索引的成本

　　1、访问索引(内存命中的概率很高)

　　2、主键的树(内存命中的概率很高)

　　3、集群因子(计算索引访问成本)：index clustering factor是oracle一个参数。

3、索引的有序度和主键的有序度的一个比较

　　访问数据时，如果访问的数据是主键挨着的，那么在表中可能位于磁盘的一个块上（这样索引的效率最高，成本最低）；如果不挨着，就会来回跳，不在一个块中。
　　所以在建立主键时，尽量保持和最常用的索引的增长趋势是一致的，这样索引的成本最低，效率最高。表中有两个列，name列和id列，如果最经常访问的是name列，但是id列是主键有顺序的，通过name列访问是无序的，效果非常差。
　　解决办法：可以在建立主键时，让namelie在前面，自增长列在后面，建立联合主键这样就不会导致主键冲突，就算到时候取80%的数据时，也可以实现用索引取数据。

4、全表扫描优于索引情况

　　查询结果的记录大于表中记录一定比例(对于大多数数据库来说，这个比例是10%)的时候，全表扫描要比使用索引快。

　　这个主要是由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。

　　即先对结果数量估算，如果小于这个比例用索引，大于的话即直接全表扫描。

三、为什么需要insert buffer，针对二级索引

1、索引数据页的更新

　　表的索引存于该表的ibd文件中，数据也存于此文件。表数据更新的同时也会更新对应的表的索引数据，所以：对表进行insert时，很可能会产生大量的物理读(物理读索引数据页)

2、索引对insert的影响

　　1、表insert，对应表上的所有索引都需要insert；

　　2、假设这些索引不常使用，容易产生物理读；

　　3、索引的顺序和表的顺序完全不一致；

　　原则：一个表上的索引最好不超过6个

3、change buffer

　　A special data structure that records changes to pages in secondary indexes. These values could result from SQL INSERT, UPDATE, or DELETE statements (DML). The set of features involving the change buffer is known collectively as change buffering, consisting of insert buffering, delete buffering, and purge buffering.

　　将对索引的更新记录存入insert buffer中，而不是直接调入索引页进行更新；择机进行merge insert buffer的操作，将insert buffer中的记录合并(merge)到真正的辅助索引中。

　　解决了insert表数据产生过多物理读的问题。

4、merge insert buffer的操作可能发生在什么情况下

　　在merge insert buffer之前，insert buffer数据是存在内存中，为了防止数据库意外宕机导致数据丢失，系统会周期性将insert buffer数据写入共享表空间中。

　　1、辅助索引页被读取到buffer pool中

　　　　例如这在执行正常的select查询操作，索引页被调入内存，该索引页对应在insert buffer中的索引更改记录就会发生merge操作。

　　2、insert buffer bitmap页追踪到该辅助索引页已无可用空间时

　　　　存于ibd文件中(表数据文件)

　　　　记录每一个索引页在insert buffer中对应的行数

　　3、master thread工作

　　　　在master thread线程中每秒或每10秒会进行一次merge insert buffer的操作，不同之处在于每次进行merge操作的页的数量不同。

mysql> show engine innodb status\G
……
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

　　1、insert buffer空间占有量：2*16K

　　　　对表进行批量IDU的时候，可能会导致change buffer迅速增加。

　　2、merges合并的次数：一次合并对应一次物理读

　　3、insert 0, delete mark 0, delete 0

　　4、discarded是数据还没有合并，索引被删除，相应的数据也要被删除。

假设：

　　Merges：10

　　Insert：1000

　　Delete Mark：3000

　　Delete：3000

　　(1000+3000+3000)/10=700：表示merge一次解决了对索引的多少次更改，此处700次索引更改一次merge

5、如何看insert buffer的效果

　　1、insert buffer所占空间，占比太高就影响缓冲性能

　　2、每次merge处理的数据量

　　　　1、merges如果很高，说明insert buffer调小了，也说明索引建多了；

　　　　2、对表进行批量IDU的时候，可能会导致insert buffer迅速增加。

6、关注change buffer在innodb buffer pool中的占比

mysql> show variables like '%change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.01 sec)

　　1、innodb_change_buffer_max_size：表示change buffer在buffer pool中的最大占比，默认25%，最大50%

　　2、innodb_change_buffering：表示索引列merge对象，all表示对IDU索引列都起作用，都进行merge，如果只想对insert索引列进行merge，就把all改为inserts。

调整依据：

　　1、如果系统中有严重的insert、update并且还有活跃的delete时，就增大max_size；

　　2、针对不更改数据的纯报表系统，可以减小该参数值。

---

原文链接：https://www.cnblogs.com/geaozhang/p/7235953.html