mysql5.7索引优化_MySQL 5.7 : 索引创建优化（Bulk Load）-阿里云开发者社区

先来看一组测试数据

使用sysbench，生成一张大约10,000,000行记录的表，数据全部在buffer pool中

创建索引(k,pad)

5.7.5

alter table sbtest1 add key(k,pad);

Query OK, 0 rows affected (44.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

5.7.4

root@sbb 04:25:11>alter table sbtest1 add key(k,pad);

Query OK, 0 rows affected(1 min 2.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

几轮测试的结果都差不多，5.7.5的索引创建速度总是优于5.7.4(同时也优于5.6).

OK,老规矩，我们来看主要对索引创建做了什么样的优化，在5.7.5的changelog entry如下：

InnoDB: Instead of inserting one index record at a time, InnoDB now performs a bulk load when creating or rebuilding indexes. This method of index creation is also known as a “sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes. A new global configuration option, innodb_fill_factor, defines the percentage of space on each page that is filled with data during a sorted index build, with the remaining space reserved for future index growth. For more information, see Bulk Load for CREATE INDEX.

在之前的版本中，创建索引时总是一条记录一条记录的插入索引，当记录量比较大时，这种插入方式非常低效，因此引入了BULK LOAD的方式。采用自底向上的构建方式。大体思路为，总是按照从左向右，从底往上的方式来构建btree记录。

因此修改的点应该在获取了已经归并排序完成的索引记录之后，准备向新构建的BTREE中插入记录。老的实现方式有明显的缺点：1.每次插入BTREE都需要重定位cursor；2.自顶向下的构建索引可能导致大量的索引分裂。

0.background

新增源文件：btr/btr0bulk.cc

定义了两个类来辅助索引

PageBulk：用于处理页内操作

BtrBulk：用于处理btree操作，针对每层BTREE都有一个PageBulk对象

1.总体入口

我们以上述测试用的建索引语句为例

入口函数：row_merge_build_indexes

在完成对二级索引记录的排序文件后，进入插入阶段：

Step 1. 初始化BtrBulk

BtrBulk btr_bulk(sort_idx, trx->id);

btr_bulk.init(); 初始化m_page_bulks，该vector存储的是PageBulk对象

Step 2.随后调用row_merge_insert_index_tuples进入元组插入阶段。

遍历元组，对于每条记录，进行转换后，调用 btr_bulk->insert(dtuple)->insert(tuple, 0)插入

Step 3.完成后调用btr_bulk.finish(error)完成插入操作。

显然我们的重点集中在btr_bulk->insert的逻辑上。

2.自底向上的索引记录插入

入口函数： BtrBulk::insert

画了个流程图来便于理解这个过程。

Step 1. 当前btree level没有对应的PageBulk，则创建，初始化，并加入到m_page_bulks中。

PageBulk::init() 会开启一个mtr (不记录redo log,MTR_LOG_NO_REDO)，分配新的page(如果需要的话)

另外还需要计算page上保留的空闲空间数，用于索引完成后的DML操作，由新参数innodb_fill_factor控制。

m_reserved_space =

UNIV_PAGE_SIZE * (100 – innobase_fill_factor) / 100;

Step 2. 当前插入的tuple是非叶子节点的最左节点的最小记录，设置tuple标记REC_INFO_MIN_REC_FLAG

Step 3.判断是否需要外部存储，如果需要，转换记录格式(dtuple_convert_big_rec)

page_bulk->needExt(tuple, rec_size)。

Step 4. 检查当前page的空间是否足够插入记录(PageBulk::isSpaceAvailable)

如果PageBulk::isSpaceAvailable返回false，表示page空间不足，需要

#创建一个兄弟节点，及其对应的PageBulk，并进行初始化，分配新page

#提交当前PageBulk：err = pageCommit(page_bulk, sibling_page_bulk, true)

…将当前page指向下一个page；

…压缩表需要特殊处理，先进行压缩(PageBulk::compress())，如果压缩失败，则进行page分裂(BtrBulk::pageSplit)， 在pageSplit里会先分配一个新的page用于容纳分裂后的数据，简单的说..

page_bulk1 —-> 空间不足

page_bulk1      (page_bulk2)

page_bulk1 —->page_bulk2

压缩page_bulk1—>failed—->split(page_bulk1, page_bulk3) —>(page_bulk1—>page_bulk3—>page_bulk2)

也就是说，这里可能产生BtrBulk::pageCommit的递归调用。

…构建父亲节点记录，并插入到父节点。

dtuple_t*      node_ptr = page_bulk->getNodePtr();

dberr_t        err = insert(node_ptr, page_bulk->getLevel()+1);

这里会递归调用BtrBulk::insert函数来完成自下而上的BTREE节点构建

#如果是叶子节点，还需要唤醒page cleaner线程，做必要的log_free_check()

Step 5.在完成上述必要的检查后，将tuple转会为rec，并插入到page中(PageBulk::insert)

page_bulk->insert(rec, offsets)

Step 6.处理外部存储记录(PageBulk::storeExt)

参考：

worklog

对应补丁：

官方文档：