关于innodb:第14期数据页合并

34次阅读

共计 5779 个字符，预计需要花费 15 分钟才能阅读完成。

MySQL InnoDB 表数据页或者二级索引页（简称数据页或者索引页）的合并与决裂对 InnoDB 表整体性能影响很大；数据页的这类操作越多，对 InnoDB 表数据写入的影响越大。
MySQL 提供了一个 数据页合并临界值（MERGE_THRESHOLD），在某些场景下，能够人为染指，缩小数据页的合并与决裂。在 InnoDB 表里，每个数据页默认 16K 大小，默认 MERGE_THRESHOLD 值为 50，取值范畴从 1 到 50，默认值即是最大值。也就是当页面记录数占比小于 50% 时，MySQL 会把这页和相邻的页面进行合并，保证数据页的紧凑，防止太多节约。

触发临界值场景

场景一：

页 A 里原本数据占用 100%，有一部分记录被删掉后，数据占用小于 50%，刚好触发了临界值。

场景二：

页 B 里寄存的记录被更新为更短的模式，比方记录值由 rpad（’ 我爱你们所有人 ’ , 10000, ‘ 增加冗余字符 ’）变为 ‘ 我只爱你 ’，这时候记录对数据页占用也小于 50%，刚好触发了临界值。

简述数据页的合并

页 A 在删除一些记录后，此页里残余记录对页 A 的占用小于 MERGE_THRESHOLD 设定的值，此时刚好页 A 相邻的一个页 C，数据占用也不到 50%，这时候 MySQL 会把页 C 的记录并入页 A，之后页 C 的空间就被开释，不蕴含任何数据，页 C 就可用于当前新记录的写入，防止空间的节约。

简述数据页的决裂

  • 页 D 和页 E，两个页面记录占用都在 49%。那么页合并后，页 D 记录占用 98%，只剩下 2%。
  • 页 F 和页 H，两个页面记录占用也都是 49%，那么合并后，页 F 记录占用 98%，也只剩下 2%。

此时有新的插入申请过去，这条记录的主键刚好在页 D 和页 F 之间，可是页 D 和页 F 都只剩下 2% 的空间，不够插入这条记录。那怎么办？此时只能拆分页 D。建设一个新的页 I，完了把页 D 原来的记录和新插入的记录做一个排序，再依照新的程序把页 D 填满，剩下的数据放到页 I。所以页决裂会波及到老页数据的迁徙到新建页的建设，如果页的决裂频繁，那开销很大。下来看看 MERGE_THRESHOLD 的理论用法与监测。
留神！MERGE_THRESHOLD 不能小写，必须大写！小写就会被 MySQL 当作简略的正文。

1. 表的 MERGE_THRESHOLD

对整张表设置 MERGE_THRESHOLD，须要把这个值放入表的 comment 中。INNODB 并不是第一个在表正文里定义对表数据管制选项的，MySQL 之前的列式引擎 brighthouse 早这么干过，把对表的一些利于优化的 HINT 放入到 comment 里。具体的语法为：

mysql> create table sample1(id int primary key,r1 int, r2 varchar(1000)) comment 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.08 sec)

或者是针对之前的表更改 MERGE_THRESHOLD 值

mysql> alter table t1 comment 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2. 索引的 MERGE_THRESHOLD

也能够针对单个索引列设置 MERGE_THRESHOLD 值，单个列的 MERGE_THRESHOLD 优先级比表高，也就是会笼罩掉表的设置。

mysql> create table t1(id int, key idx_id(id) comment 'MERGE_THRESHOLD=40');
Query OK, 0 rows affected (0.08 sec)

或者先删除索引，再建设新的。

mysql> alter table t1 drop key idx_id, add key idx_id(id) comment 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

或者，

mysql> create index idx_id on t1(id) comment 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 查看 MERGE_THRESHOLD

能够通过查看表定义信息，

mysql> show create table sample1\G
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MERGE_THRESHOLD=40'
1 row in set (0.00 sec)

能够通过独自查看索引定义信息，

mysql> show index from t1\G
...
     Comment:
Index_comment: MERGE_THRESHOLD=40
1 row in set (0.00 sec)

或者能够通过数据字典表 information_schema.innodb_indexes 来获取表里所有对象的 MERGE_THRESHOLD 值设定。

mysql> select a.name as tablename,b.name as index_name, b.MERGE_THRESHOLD from innodb_tables as a,innodb_indexes as b where a.table_id = b.table_id and a.name like 'ytt%';
+-------------+-----------------+-----------------+
| tablename   | index_name      | MERGE_THRESHOLD |
+-------------+-----------------+-----------------+
| ytt/sample1 | PRIMARY         |              40 |
| ytt/t1      | GEN_CLUST_INDEX |              50 |
| ytt/t1      | idx_id          |              40 |
+-------------+-----------------+-----------------+
3 rows in set (0.00 sec)

4. MERGE_THRESHOLD 设置成果评估

innodb_metrics 表提供了两个计数器来跟踪页合并（Innodb_metrics 表十分有用，前期独自开篇具体来介绍。）这两个计数器默认是屏蔽的，须要显式开启，

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
   -> WHERE NAME like '%index_page_merge%';
+-----------------------------+----------------------------------------+
| NAME                        | COMMENT                                |
+-----------------------------+----------------------------------------+
| index_page_merge_attempts   | Number of index page merge attempts    |
| index_page_merge_successful | Number of successful index page merges |
+-----------------------------+----------------------------------------+
2 rows in set (0.00 sec)

开启这两个计数器，

mysql> set global innodb_monitor_enable='index_page_merge_attempts';
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_monitor_enable
Query OK, 0 rows affected (0.00 sec)

建设两张表，MERGE_THRESHOLD 别离为默认值和 20，导入同样的 10000 条记录，看看页面合并的比照。

mysql> create table t1_max(id int primary key，r1 int, key  idx_r1 (r1));
Query OK, 0 rows affected (0.08 sec)

mysql> create table t1_min(id int, primary key (id) comment 'MERGE_THRESHOLD
Query OK, 0 rows affected (0.08 sec)

比照前，先清空计数器；禁止后；重置计数器。

mysql> set global innodb_monitor_disable='index_page_merge_attempts';
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_monitor_disable
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_monitor_reset_all
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_monitor_enable
Query OK, 0 rows affected (0.00 sec)

先往表 t1_max 里随机插入 1000 条记录。

replace into t1_max select ceil(rand()*1000),ceil(rand()*100)；...

mysql> select count(*) from t1_max
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.03 sec)

再删掉 500 条记录

mysql> delete from t1_max limit 500;
Query OK, 500 rows affected (0.05 sec)

查看计数器后果，尝试合并 707 次，合并胜利 20 次。

mysql> SELECT name,count,max_count,avg_count  FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';
+-----------------------------+-------+-----------+--------------------+
| name                        | count | max_count | avg_count          |
+-----------------------------+-------+-----------+--------------------+
|
| index_page_merge_successful |    20 |        20 |  0.072992700729927 |
+-----------------------------+-------+-----------+--------------------+
2 rows in set (0.00 sec)

再清空计数器，对表 t1_min 执行同样的操作，

mysql> select count(*) from t1_min;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.02 sec)

mysql> delete from t1_min limit 500
Query OK, 500 rows affected (0.02 sec)

再次查看计数器，尝试合并 30 次，理论胜利次数仅仅为 2。比默认的合并次数少了 20 多倍。所以能够看到，在肯定的数据模型下，手动管制合并临界值对数据页的合并频率调节十分无效。

mysql> SELECT name,count,max_count,avg_count  FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';
+-----------------------------+-------+-----------+---------------------+
| name                        | count | max_count | avg_count           |
+-----------------------------+-------+-----------+---------------------+
|
| index_page_merge_successful |     2 |         2 | 0.03333333333333333 |
+-----------------------------+-------+-----------+---------------------+
2 rows in set (0.00 sec)

当然，设置成最小值 1，基本上不会合并了，后果应该如下。

mysql> SELECT name,count,max_count,avg_count  FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';
+-----------------------------+-------+-----------+-----------+
| name                        | count | max_count | avg_count |
+-----------------------------+-------+-----------+-----------+
|
| index_page_merge_successful |     0 |      NULL |         0 |
+-----------------------------+-------+-----------+-----------+
2 rows in set (0.00 sec)

总结

这篇我介绍了 MySQL 索引页合并临界值的概念以及如何在理论环境中评估这个值对索引页合并以及拆分的影响，有问题或者相干倡议欢送斧正回复。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
innodb
发表至： innodb
2020-10-14
 0
关于innodb:技术分享-主从表的列不一致复制是否会受影响
关于innodb:故障分析-MySQL-临时表空间数据过多导致磁盘空间不足的问题排查
关于innodb:MySQL技术内幕InnoDB存储引擎笔记
关于innodb:mysql数据库innodb的索引特点