17.8.11 Configuring the Merge Threshold for Index Pages — MySQL 9.2

17.8.11 Настройка порога слияния страниц индекса

Вы можете настроить значение MERGE_THRESHOLD для страниц индекса. Если процент “page-full” для страницы индекса опускается ниже значения MERGE_THRESHOLD при удалении строки или при сокращении строки операцией UPDATE, InnoDB предпринимает попытку слить страницу индекса с соседней страницей индекса. Значение по умолчанию MERGE_THRESHOLD равно 50, что соответствует ранее жёстко заданному значению. Минимальное значение MERGE_THRESHOLD составляет 1, а максимальное — 50.

Когда процент “page-full” для страницы индекса опускается ниже 50%, что является значением по умолчанию MERGE_THRESHOLD, InnoDB пытается слить страницу индекса с соседней страницей. Если обе страницы близки к 50%-ной заполненности, может произойти разделение страницы вскоре после слияния страниц. Если такое поведение слияния-разделения происходит часто, это может негативно сказаться на производительности. Чтобы избежать частых слияний-разделений, можно снизить значение MERGE_THRESHOLD, чтобы InnoDB пыталось слить страницы при меньшем проценте “page-full”. Слияние страниц при меньшем проценте заполнения оставляет больше места на страницах индекса и помогает уменьшить поведение слияния-разделения.

Значение MERGE_THRESHOLD для страниц индекса может быть определено для таблицы или для отдельных индексов. Значение MERGE_THRESHOLD, определённое для отдельного индекса, имеет приоритет над значением MERGE_THRESHOLD, определённым для таблицы. Если не определено, значение MERGE_THRESHOLD по умолчанию равно 50.

Установка MERGE_THRESHOLD для таблицы

Вы можете установить значение MERGE_THRESHOLD для таблицы, используя предложение table_option COMMENT оператора CREATE TABLE. Например:

CREATE TABLE t1 (
   id INT,
  KEY id_index (id)
) COMMENT='MERGE_THRESHOLD=45';

Вы также можете установить значение MERGE_THRESHOLD для существующей таблицы, используя предложение table_option COMMENT с оператором ALTER TABLE:

CREATE TABLE t1 (
   id INT,
  KEY id_index (id)
);

ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

Установка MERGE_THRESHOLD для отдельных индексов

Чтобы установить значение MERGE_THRESHOLD для отдельного индекса, вы можете использовать предложение index_option COMMENT с операторами CREATE TABLE, ALTER TABLE или CREATE INDEX, как показано в следующих примерах:

Установка MERGE_THRESHOLD для отдельного индекса с помощью CREATE TABLE:
```
CREATE TABLE t1 (
   id INT,
  KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
);
```

Установка MERGE_THRESHOLD для отдельного индекса с помощью ALTER TABLE:

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';

Установка MERGE_THRESHOLD для отдельного индекса с помощью CREATE INDEX:
```
CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
```

Примечание

Вы не можете изменить значение MERGE_THRESHOLD на уровне индекса для GEN_CLUST_INDEX, который является кластеризованным индексом, созданным InnoDB при создании таблицы InnoDB без первичного ключа или уникального индекса ключа. Вы можете изменить значение MERGE_THRESHOLD для GEN_CLUST_INDEX, установив MERGE_THRESHOLD для таблицы.

Запрос значения MERGE_THRESHOLD для индекса

Текущее значение MERGE_THRESHOLD для индекса можно получить, запросив таблицу INNODB_INDEXES. Например:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G
*************************** 1. row ***************************
       INDEX_ID: 91
           NAME: id_index
       TABLE_ID: 68
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 40

Вы можете использовать SHOW CREATE TABLE, чтобы просмотреть значение MERGE_THRESHOLD для таблицы, если оно явно определено с помощью предложения table_option COMMENT:

mysql> SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  KEY `id_index` (`id`) COMMENT 'MERGE_THRESHOLD=40'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Примечание

Значение MERGE_THRESHOLD, определённое на уровне индекса, имеет приоритет над значением MERGE_THRESHOLD, определённым для таблицы. Если не определено, MERGE_THRESHOLD по умолчанию равно 50% (MERGE_THRESHOLD=50, что соответствует ранее жёстко заданному значению.

Аналогично, вы можете использовать SHOW INDEX, чтобы просмотреть значение MERGE_THRESHOLD для индекса, если оно явно определено с помощью предложения index_option COMMENT:

mysql> SHOW INDEX FROM t2 \G
*************************** 1. row ***************************
        Table: t2
   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

Измерение эффекта настроек 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 |
+-----------------------------+----------------------------------------+

При снижении значения MERGE_THRESHOLD, целями являются:

Меньшее количество попыток слияния страниц и успешных слияний страниц
Аналогичное количество попыток слияния страниц и успешных слияний страниц

Слишком маленькое значение MERGE_THRESHOLD может привести к большим файлам данных из-за чрезмерного количества пустого места на страницах.

Сведения о применении счётчиков INNODB_METRICS см. в Разделе 17.15.6, «Таблица метрик InnoDB INFORMATION_SCHEMA».