生活_傻大方

  1. 首页 > 潮·科技 > >

第14期：数据页合并


第14期：数据页合并文章插图
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 的实际用法与监测 。
【第14期：数据页合并】注意！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: 0Duplicates: 0Warnings: 02. 索引的 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: 0Duplicates: 0Warnings: 0或者 ，
mysql> create index idx_id on t1(id) comment 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.05 sec)Records: 0Duplicates: 0Warnings: 03. 查看 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=401 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)



上一篇：骁龙888|雷军晒骁龙888处理器开箱图 小米11将首发骁龙888

下一篇：一键同步！CODING 现已支持导入 GitHub 仓库