前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >InnoDB数据页什么时候合并

InnoDB数据页什么时候合并

作者头像
老叶茶馆
发布2020-07-16 14:36:13
4600
发布2020-07-16 14:36:13
举报
文章被收录于专栏:MySQL修行 | 老叶茶馆

1. 为什么要合并数据页

2. 什么时候合并数据页

2.1 准备测试环境

2.2 找到两个相邻页

2.3 试探性逐步删除数据,接近阈值

2.4 再次只删除一条记录,验证是否合并

3. 其他补充说明

3.1 除了表级可以设置外,单个索引也可以设置合并阈值

3.2 页合并状态监控3.3 如何适当调整阈值设置延伸阅读

1. 为什么要合并数据页

我们知道,当从InnoDB表删除数据时,相应的数据是先打上删除标签(deleted mark),而后再由purge线程执行清理工作。

清理工作结束后,如果两个相邻的数据页存储填充率低于一定程度,就会尝试合并页,以降低碎片率,提高存储效率。

或者经过多次长度变小的UPDATE操作后(将varchar列长度更新变短),数据页填充率低于一定程度也会尝试合并。

合并完毕之后,空出来的页就会被标记为空闲页,等待再分配。

这个工作是InnoDB后台线程自动完成的,无需人为干预、控制。

2. 什么时候合并数据页

MySQL官方手册 The InnoDB Storage Engine / InnoDB Configuration / Configuring the Merge Threshold for Index Pages 中其实已经详细说明了什么时候会进行合并。

通过调整参数 MERGE_THRESHOLD 的值,当InnoDB数据页填充率低于该阈值时,就会尝试进行合并页操作。

该参数默认值是 50,最小值是 1,在5.6版本之后允许自行指定设置,在5.6之前的版本中则是被硬编码的,无法修改。

代码语言:javascript
复制
When the “page-full” percentage for an index page falls below 50%, which is the default MERGE_THRESHOLD setting, InnoDB attempts to merge the index page with a neighboring page. If both pages are close to 50% full, a page split can occur soon after the pages are merged.

简言之,就是当发现两个相邻页的填充率都低于50%时,就会尝试进行合并。

2.1 准备测试环境

我们拿一个实际案例进行测试,观察InnoDB的页合并是怎么做的。

测试表DDL

代码语言:javascript
复制
CREATE TABLE `t_sk` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL,
  `c3` int(10) unsigned NOT NULL,
  `c4` int(10) unsigned NOT NULL,
  `c5` datetime NOT NULL,
  `c6` char(20) NOT NULL,
  `c7` varchar(30) NOT NULL,
  `c8` varchar(30) NOT NULL,
  `c9` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`c1`)
) ENGINE=InnoDB COMMENT='MERGE_THRESHOLD=30';

特地设置了 MERGE_THRESHOLD=30,看看page的填充率在高于30%时会不会发生合并。

mysql_random_data_load 灌入一些测试数据。

本文使用的MySQL版本是Percona-Server-5.7.22,下载源码后自编译的。

代码语言:javascript
复制
Server version:        5.7.22-22-log Source distribution
2.2 找到两个相邻页

随机找到其中的两个相邻的页,pageno分别是7和8。

innodb_ruby 工具扫描这两个page中的数据:

代码语言:javascript
复制
[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-records > recs-no7.txt

[root@yejr.run]# cat recs-no7.txt
Record 128: (id=252)...
...
Record 15143: (id=351)...

非常巧,这个page中共有100条记录,其ID值从 252 ~ 351。

同样地,扫描8号page,也正好有100条记录,其ID值从 352 ~ 451。

2.3 试探性逐步删除数据,接近阈值

先利用 innodb_ruby 工具查看page-dump的结果:

代码语言:javascript
复制
[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-dump > dump-no7.txt
...
sizes:
  header           120
  trailer            8
  directory         52
  free            1054
  used           15330
  record         15150
  per record     151.00
...

从上面信息可以看到,每条记录平均长度是151字节,共100条记录。

如果想让填充率低于30%,那么需要删除的数据量大概是:

代码语言:javascript
复制
ceil((15150 - 16384 * 0.3) / 151) = 68

关于上述公式简要说明下:

  • 当前的数据所占字节数是:15150
  • 只剩30%填充率的字节数是:16384 * 0.3
  • 每条记录平均长度字节数是:151 这样应该能看懂吧。 也就是两个page分别都需要删除68条记录才会触发合并操作。

好了,针对上述两个ID值区间,先各自分别删除67条数据,只差一条数据就达到临界点,看看后续会不会发生合并。

代码语言:javascript
复制
[root@yejr.run]> delete from t_sk where id>=252 and id <= 318;
Query OK, 67 rows affected (0.01 sec)

[root@yejr.run]> delete from t_sk where id>=352 and id <= 418;
Query OK, 67 rows affected (0.00 sec)

先用 innblock 工具快速扫描:

代码语言:javascript
复制
[root@yejr.run]# innblock test/t_sk.ibd 7 16 | grep n_rows; innblock test/t_sk.ibd 8 16 | grep n_rows
slot_nums:9         heaps_rows:102        n_rows:33
slot_nums:9         heaps_rows:102        n_rows:33

确认两个page还没合并,各自都只剩33条记录。 再看两个page的填充率情况:

代码语言:javascript
复制
#扫描pageno=7
[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-dump
...
sizes:
  header           120
  trailer            8
  directory         18
  free           11198
  used            5186
  record          5040
  per record     152.00
...

#扫描pageno=8
[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 8 page-dump
...
sizes:
  header           120
  trailer            8
  directory         18
  free           11200
  used            5184
  record          5038
  per record     152.00
...

此时两个page的填充率分别是:

代码语言:javascript
复制
5040/16384 = 0.3076171875
5038/16384 = 0.3074951171

都超过了 MERGE_THRESHOLD=30 阈值,所以还没有进行合并。

2.4 再次只删除一条记录,验证是否合并

接下来,我们分别对两个page再各自删除一条记录,使得填充率低于临界点:

代码语言:javascript
复制
[root@yejr.run]> delete from t_sk where id in (319, 419);
Query OK, 2 rows affected (0.01 sec)

还是用 innblock 工具扫描,不过这次先扫描第7号page,看看是不是把8号page合并过来了。

代码语言:javascript
复制
[root@yejr.run]# innblock test/t_sk.ibd 7 16 | grep n_rows; innblock test/t_sk.ibd 8 16 | grep n_rows
slot_nums:16        heaps_rows:67         n_rows:64
slot_nums:9         heaps_rows:102        n_rows:32
...

果真如此,发现第7号page当前有64条记录,这是两个page合并之后的结果。

而第8号page因为已经被合并了,被标记为空闲page,此时已经从索引树里被摘掉了:

代码语言:javascript
复制
[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -I PRIMARY -l 0 index-level-summary
page    index   level   data    free    records min_key
4       54      0       7536    8692    50      id=1
5       54      0       15118   1086    100     id=52
6       54      0       15103   1101    100     id=152
7       54      0       9768    6456    64      id=320
9       54      0       15159   1045    100     id=452
10      54      0       15056   1150    99      id=552

很明显,第8号page已经不在列表中了。只不过page被合并后,里面的物理记录还存着,并没有立即被抹掉,等以后被重用时直接覆盖就好了。

至此,page合并试验结束。

3. 其他补充说明

3.1 除了表级可以设置外,单个索引也可以设置合并阈值

对InnoDB来说,其实整个表都是索引页,无非是聚集索引页还是辅助索引页而已。

因此,页合并阈值既可以用于聚集索引页,也可以用于辅助索引页。 只需要在创建索引时指定即可:

代码语言:javascript
复制
[root@yejr.run]> ALTER TABLE t_sk ADD INDEX k1(c1) COMMENT 'MERGE_THRESHOLD=20';

当然了,这个只能在创建索引时一次性指定,不能中途修改。

然而,表级别的合并阈值则可以在运行时修改:

代码语言:javascript
复制
[root@yejr.run]> ALTER TABLE t_sk COMMENT 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

还可以通过查看元数据确认各个索引的合并阈值设置:

代码语言:javascript
复制
# 直接根据 TABLE_ID 条件查询聚集索引和辅助索引
# 如果辅助索引创建时没设置阈值,则其阈值设置直接从表级设置中继承
# 提醒:8.0中 INNODB_SYS_INDEXES 表名变成了 INNODB_INDEXES
[root@yejr.run]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE select * from information_schema.innodb_sys_Indexes where TABLE_ID = 66\G
*************************** 1. row ***************************
       INDEX_ID: 54
           NAME: PRIMARY  --聚集索引
       TABLE_ID: 66
           TYPE: 3
       N_FIELDS: 1
        PAGE_NO: 3
          SPACE: 31
MERGE_THRESHOLD: 30
*************************** 2. row ***************************
       INDEX_ID: 65
           NAME: k1  --聚集索引
       TABLE_ID: 66
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 8
          SPACE: 31
MERGE_THRESHOLD: 20  --自行设定阈值为20%

# 或者执行SHOW语法
[root@yejr.run]> SHOW INDEX FROM t_sk\G
*************************** 1. row ***************************
        Table: t_sk
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 494750
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: t_sk
   Non_unique: 1
     Key_name: k1
 Seq_in_index: 1
  Column_name: c1
    Collation: A
  Cardinality: 451839
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: MERGE_THRESHOLD=20
3.2 页合并状态监控

页合并的统计情况,可以通过查询 INNODB_METRICS 表获取到。

代码语言:javascript
复制
# 先启用该metric
[root@yejr.run]> set global innodb_monitor_enable="module_index";
Query OK, 0 rows affected (0.00 sec)

# 一顿删除操作猛如虎出发页合并之后查询
[root@yejr.run]> SELECT NAME,COUNT,STATUS,COMMENT from INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'index_page%merge%';
+-----------------------------+-------+---------+----------------------------------------+
| NAME                        | COUNT | STATUS  | COMMENT                                |
+-----------------------------+-------+---------+----------------------------------------+
| index_page_merge_attempts   |    13 | enabled | Number of index page merge attempts    |
| index_page_merge_successful |     1 | enabled | Number of successful index page merges |
+-----------------------------+-------+---------+----------------------------------------+

通过监控这个metric,如果发现页合并非常频繁的话,可以考虑把 MERGE_THRESHOLD 阈值调低。但是设置太低也有风险,因为合并频率降低了,结果会导致更高的数据页碎片率。

3.3 如何适当调整阈值设置

有个不便的地方是,阈值 MERGE_THRESHOLD 无法全局设定(innodb_merge_threshold_set_all_debug参数只能用于debug版本,正常版本不可设置),而且通过 INNODB_METRICS 也无法监控到具体是哪些表上的合并操作最多。因此当发现有很高合并频率时,可能需要扫描所有表,找到那些碎片率较高的表,其产生合并的"嫌疑"应该也较高。

页分裂、合并是个平衡的艺术,如果表DDL设计的好,每条记录的长度基本上一致的话,并且没有频繁的变长更新或删除,那页合并的次数应该不会太高才对。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-07-12,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 老叶茶馆 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 2. 什么时候合并数据页
    • 2.1 准备测试环境
      • 2.2 找到两个相邻页
        • 2.3 试探性逐步删除数据,接近阈值
          • 2.4 再次只删除一条记录,验证是否合并
          • 3. 其他补充说明
            • 3.1 除了表级可以设置外,单个索引也可以设置合并阈值
              • 3.2 页合并状态监控
                • 3.3 如何适当调整阈值设置
                相关产品与服务
                云数据库 SQL Server
                腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
                领券
                问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档