配置索引页的合并阈值
您可以为索引页配置MERGE_THRESHOLD值。如果一个索引页的“page-full”百分比低于MERGE_THRESHOLD值,当一个行被删除或当一个行被UPDATE 操作缩短时,InnoDB会尝试合并这个索引页和相邻的索引页。默认的MERGE_THRESHOLD值是50,这是以前的硬编码值。MERGE_THRESHOLD最小值为1,最大值为50。
当索引页的页满百分比低于50%(默认的MERGE_THRESHOLD设置)时,InnoDB会尝试将索引页与相邻页合并。如果两个页面都接近50%的满,那么在页面合并后不久就会发生页面分割。如果频繁发生这种合并-分割行为,则会对性能产生不利影响。为了避免频繁的合并分割,你可以降低MERGE_THRESHOLD值,这样InnoDB尝试页面合并的百分比就会降低。以较低的页满百分比合并页面会在索引页中留下更多的空间,并有助于减少合并-分割行为。
可以为一个表或单个索引定义索引页的MERGE_THRESHOLD。为单个索引定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESHOLD值。如果未定义,MERGE_THRESHOLD值默认为50。
设置表的MERGE_THRESHOLD
可以使用CREATE TABLE语句的table_option COMMENT子句为一个表设置MERGE_THRESHOLD值。例如:
CREATE TABLE t1 ( id INT, KEY id_index (id) ) COMMENT='MERGE_THRESHOLD=45';
还可以使用ALTER TABLE的table_option COMMENT子句为现有表设置MERGE_THRESHOLD值
CREATE TABLE t1 ( id INT, KEY id_index (id) ); ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';
为单个索引设置MERGE_THRESHOLD
要为单个索引设置MERGE_THRESHOLD值,可以使用带有CREATE TABLE、ALTER TABLE或CREATE INDEX的index_option COMMENT子句,如下面的示例所示:
.使用CREATE TABLE为单个索引设置MERGE_THRESHOLD:
mysql> CREATE TABLE t1 ( -> id INT, -> KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40' -> ); Query OK, 0 rows affected (0.11 sec)
.使用ALTER TABLE为单个索引设置MERGE_THRESHOLD:
mysql> CREATE TABLE t1 ( -> id INT, -> KEY id_index (id) -> ); ALTER TABLE t1 DROP KEY id_index; ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.17 sec) mysql> ALTER TABLE t1 DROP KEY id_index; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
使用CREATE INDEX为单个索引设置MERGE_THRESHOLD:
mysql> CREATE TABLE t1 (id INT); Query OK, 0 rows affected (0.14 sec) mysql> CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40'; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0
不能在索引级别修改GEN_CLUST_INDEX的MERGE_THRESHOLD值,GEN_CLUST_INDEX是InnoDB在创建InnoDB表时,在没有主键或唯一键索引的情况下创建的聚集索引。只能通过设置表的MERGE_THRESHOLD来修改GEN_CLUST_INDEX的MERGE_THRESHOLD值
查询索引的MERGE_THRESHOLD值
当前索引的MERGE_THRESHOLD值可以通过查询INNODB_SYS_INDEXES表获得。例如:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE NAME='id_index' \G *************************** 1. row *************************** INDEX_ID: 265 NAME: id_index TABLE_ID: 267 TYPE: 0 N_FIELDS: 1 PAGE_NO: 4 SPACE: 269 MERGE_THRESHOLD: 40 1 row in set (0.00 sec)
如果使用table_option COMMENT子句显式定义,可以使用SHOW CREATE TABLE查看表的MERGE_THRESHOLD值
mysql> SHOW CREATE TABLE t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, KEY `id_index` (`id`) COMMENT 'MERGE_THRESHOLD=40' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
在索引级别定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESHOLD值。如果未定义,MERGE_THRESHOLD默认为50% (MERGE_THRESHOLD=50,这是以前的硬编码值。
同样,如果使用index_option COMMENT子句显式地定义,也可以使用SHOW INDEX查看索引的MERGE_THRESHOLD值:
mysql> show index from t1 \G *************************** 1. row *************************** Table: t1 Non_unique: 1 Key_name: id_index Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: MERGE_THRESHOLD=40 1 row in set (0.00 sec)
测量MERGE_THRESHOLD设置的效果:
INNODB_METRICS表提供了两个计数器,可以用来衡量MERGE_THRESHOLD设置对索引页合并的影响。
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)
当降低MERGE_THRESHOLD值时,目标是:
.页面合并尝试和成功合并的次数更少
.尝试合并和成功合并页面的次数相同
太小的MERGE_THRESHOLD设置由于大量的空页面空间导致大量的数据文件。