10.2.1.3 Index Merge Optimization — MySQL 8.4

10.2.1.3 Оптимизация объединения индексов

Метод доступа Объединение индексов извлекает строки с несколькими сканированиями range и объединяет их результаты в один. Данный метод доступа объединяет сканирования индексов только из одной таблицы, а не из нескольких. Объединение может производить объединение, пересечение или объединение пересечений извлеченных данных.

Примеры запросов, для которых может быть использован метод Объединение индексов:

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

Примечание

У алгоритма оптимизации Объединение индексов есть следующие известные ограничения:

Если ваш запрос содержит сложное WHERE условие с глубоким вложением операторов AND/OR, и MySQL не выбирает оптимальный план, попробуйте распределить условия, используя следующие тождественные преобразования:
```
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
```
Метод Объединение индексов неприменим к полным текстам индексов.

В выводе EXPLAIN метод Объединение индексов отображается как index_merge в столбце type. В этом случае столбец key содержит список используемых индексов, а столбец key_len содержит список самых длинных ключевых частей этих индексов.

Метод доступа Объединение индексов имеет несколько алгоритмов, которые отображаются в поле Extra вывода EXPLAIN:

Using intersect(...)
Using union(...)
Using sort_union(...)

В следующих разделах эти алгоритмы описаны более подробно. Оптимизатор выбирает между различными возможными алгоритмами Объединения индексов и другими методами доступа на основе оценок стоимости различных доступных вариантов.

Алгоритм доступа Объединение индексов (пересечение)

Этот алгоритм доступа применим, когда условие WHERE преобразуется в несколько диапазонных условий по разным ключам, объединённых оператором AND, и каждое условие является одним из следующих:

Выражение с N-частями такого вида, где индекс имеет ровно N частей (то есть все части индекса покрыты):
```
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
```
Любое диапазонное условие по первичному ключу таблицы InnoDB.

Примеры:

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Алгоритм пересечения Объединения индексов выполняет одновременные сканирования по всем используемым индексам и производит пересечение последовательностей строк, получаемых из объединённых сканирований индексов.

Если все столбцы, используемые в запросе, покрываются используемыми индексами, полные строки таблицы не извлекаются (EXPLAIN вывод содержит Using index в поле Extra в этом случае). Вот пример такого запроса:

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

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

Если одно из объединённых условий является условием по первичному ключу таблицы InnoDB, оно не используется для извлечения строк, но используется для фильтрации извлечённых строк с использованием других условий.

Алгоритм доступа Объединение индексов (объединение)

Критерии для этого алгоритма аналогичны критериям алгоритма Объединения индексов (пересечение). Алгоритм применим, когда условие WHERE преобразуется в несколько диапазонных условий по разным ключам, объединённых оператором OR, и каждое условие является одним из следующих:

Выражение с N-частями такого вида, где индекс имеет ровно N частей (то есть все части индекса покрыты):
```
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
```
Любое диапазонное условие по первичному ключу таблицы InnoDB.
Условие, для которого применим алгоритм Объединения индексов (пересечение).

Примеры:

SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

Алгоритм доступа Объединение индексов (сортировка-объединение)

Этот алгоритм доступа применим, когда условие WHERE преобразуется в несколько диапазонных условий, объединённых оператором OR, но алгоритм Объединения индексов (объединение) не применим.

Примеры:

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

Различие между алгоритмами сортировка-объединение и объединение заключается в том, что алгоритм сортировка-объединение должен сначала извлечь идентификаторы строк для всех строк и отсортировать их, прежде чем вернуть какие-либо строки.

Влияние на оптимизацию Объединение индексов

Использование Объединения индексов зависит от значений флагов index_merge, index_merge_intersection, index_merge_union и index_merge_sort_union системной переменной optimizer_switch. См. Раздел 10.9.2, «Переключаемые оптимизации». По умолчанию все эти флаги on. Чтобы включить только определённые алгоритмы, установите index_merge в off и включите только те из других, которые должны быть разрешены.

Помимо использования системной переменной optimizer_switch для управления использованием алгоритмов Объединения индексов на уровне сессии, MySQL поддерживает подсказки оптимизатора для влияния на оптимизатор на уровне каждого оператора. См. Раздел 10.9.3, «Подсказки оптимизатора».