14.8.12 Configuring the Merge Threshold for Index Pages — MySQL 5.7

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

Вы можете настроить значение MERGE_THRESHOLD для страниц индекса. Если процент заполнения “страницы-полностью” для страницы индекса опускается ниже значения 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 для таблицы

Вы можете установить значение 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_SYS_INDEXES. Например:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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=latin1

Примечание

Значение 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 см. в Разделе 14.16.6, «Таблица метрик InnoDB INFORMATION_SCHEMA».