InnoDB索引物理空间使用率统计

「引言」

为帮助用户解决何时optimize table的烦恼，CDB开发了InnoDB索引物理空间使用率统计功能。

「第一部分 背景」

InnoDB是一种数据按照行存储的引擎。当记录删除时，InnoDB只进行删除标记，后续再异步回收相应空间。InnoDB页面通常为16K大小，示意图如下：

当标记删除记录较多（>40%），页面物理空间使用率较低时，数据文件将因此膨胀，IO分散，数据访问效率降低。用户为了重新获得较好的数据访问体验和节省存储空间，只能进行耗时的表重建操作（optimize table）。遗憾的是当前没有一种在线统计InnoDB表各索引物理空间使用率、记录delete mark比例方法。用户难以判断表重建时机，只能通过查询slow log来定位需要重建的表，整个过程非常繁琐。

为帮助用户解决何时optimize table的烦恼，CDB开发了InnoDB索引物理空间使用率功能。鉴于索引物理空间使用率本质上是指导用户重建表行为的提示数据。我们设计此功能时的首要目标是不能影响用户对数据的正常使用，计算速度快，锁占用时间少，尽可能让统计数据更加准确。

「第二部分 页面使用空间统计方法」

InnoDB中一棵Btree对应一个索引。因此索引空间统计等价于Btree空间统计。下图左侧是一棵Btree树的结果，分为多层，每层的节点通过双向链表相连。下图右侧是一个InnoDB普通页面的结构。我们将其简化为三部分。页头的已分配部分（包括页头信息+索引记录），页中的暂未使用部分，和页尾FIL_TRAILER部分（包括page directory和FIL_END部分），由于被delete mark的记录不能算作真正使用的空间，因此一个InnoDB页面的实际使用空间计算公式为：USED = ALLOCED – DELETED + FIL_TRAILER。

「第三部分 方案讨论」

索引级别和表级别的物理空间使用率统计方法有两种：增量统计方案和全量统计方案。

我们首先讨论增量统计方案：在内存中维护每个表、索引的物理空间使用率整体值。当有相应页面刷盘时，根据页面的改动情况更新索引级别和表级别的物理空间使用率的值。采用增量方案的优点在于能够实时给出物理空间使用率的具体值，但其缺点主要有以下两个：

1）增量方案实际上是将物理空间使用率的统计开销均摊到了数据库正常运行过程中。当遇到写入非常频繁的场景时，物理空间的增量统计会对用户的正常数据库使用造成一定影响。

2） 数据库重启后，每个表、索引的物理空间使用率初值需要重新获取。这里有两种可能的方案。

1. 采用全量的方法计算一遍。这种方案的弊端很明显，如果用户数据量很大，短时间内对所有用户表进行物理空间使用率全量统计将消耗大量IO资源。
2. 找一个地方记录各表、各索引的物理空间使用率整体值。这种方案将带来兼容性问题。部分用户处于多方面原因，有可能将自己基于开源MySQL的实例作为CDB的备机。在这样的场景下，用户的实例是没法与CDB做主从同步的。

鉴于增量统计方案的缺点多于优点。我们最终采用了全量统计方案：通过从左到右累逐层累加Btree各层次的页面数据，获取索引级别、表级别的使用率，记录delete mark比例。

「第四部分 页面扫描优化」

4.1. 页面扫描方案思考

Btree各层左右页面只是逻辑相邻，并非物理相邻，逐层扫描Btree是随机IO。如下图所示，从左到右扫描叶子节点层，那么我们则需要按PAGE4 → PAGE1 → PAGEN-1 → PAGE3 ->...这种随机混乱的顺序访问页面。

是否能够将上述随机IO转化为顺序IO呢？InnoDB索引在数据文件中的分布示意图如下图所示。顺序IO的关键点是在InnoDB的数据文件中识别出目标Btree对应的页面集合。在获取到页面集合后，只需要对页面编号进行排序，然后顺序访问即可。

如何获取Btree对应所有的页面集合呢？下面我们来了解一下InnoDB的页面管理方法：InnoDB物理结构分为表空间、段、区、页几个层级。其中，区是64个连续页面。段是InnoDB的页面管理单位，页面的申请和释放全部记录在其中，InnoDB每棵Btree对应两个段，分别用于存放叶子节点和非叶子节点。段由32个碎片页+3个区链表组成，如下图所示。InnoDB的物理结构这里不再展开，感兴趣的同学可以参考本公众号的另一篇文章：浅析InnoDB文件结构。

有了上述的背景知识，我们知道，通过Btree root页面可以获取Btree段信息，通过段获取Btree页面集合，排序后顺序访问即可实现Btree页面顺序IO扫描。

4.2. 额外收益

Btree从上往下查找时需要持有index lock，从左到右扫描时为防止页面左右指针改变，须持有当前节点及相邻节点的页面读锁。采用上述顺序IO优化方案时，可以一次性获取所有Btree页面编号，顺序IO的过程中只需要持有当前页面的读锁即可，无需同时再持有相邻页面的读锁，更不需要持有index lock，从而锁占用时间比Btree扫描方案更少。

4.3. 优化效果

选取48核的测试机，用sysbench准备了96G大小的表，在64G Buffer pool下进行了扫描空间优化测试。优化结果如下图所示，物理空间使用率统计耗时由原先的220s下降到了93秒，可见顺序IO有效地提高了IO效率。

「第五部分 最终效果」

当前索引物理空间使用率统计功能已于最新的版本上线，当前支持的表类型包括InnoDB普通表、InnoDB压缩表和InnoDB分区表。其中压缩表统计的是表解压后的使用率数据，分区表则是将各个分区的进行加权累加，再向用户做综合展示。

「总结」

有了索引物理空间使用率统计功能，DBA们再也不用为何时optimize table掉头发了！

让我们一起期待CDB更多更有价值的功能吧~

腾讯数据库技术团队对内支持QQ空间、微信红包、腾讯广告、腾讯音乐、腾讯新闻等公司自研业务，对外在腾讯云上依托于CBS+CFS的底座，支持TencentDB相关产品，如CynosDB、CDB、CTSDB、MongoDB、CES等。腾讯数据库技术团队专注于持续优化数据库内核和架构能力，提升数据库性能和稳定性，为腾讯自研业务和腾讯云客户提供“省心、放心”的数据库服务。此公众号旨在和广大数据库技术爱好者一起推广和分享数据库领域专业知识，希望对大家有所帮助。