mysql創建數據庫表，brighthouse mysql_MySQL 數據庫中的數據頁合并-愛可生

原標題：MySQL 數據庫中的數據頁合并-愛可生

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=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)

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_enableQuery 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_THRESHOLDQuery 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_disableQuery OK, 0 rows affected (0.00 sec)

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

mysql> set global innodb_monitor_enableQuery 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 500Query 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 索引頁合并臨界值的概念以及如何在實際環境中評估這個值對索引頁合并以及拆分的影響，有問題或者相關建議歡迎指正回復。返回搜狐，查看更多

責任編輯：