8.2.1.3 Index Merge Optimization — MySQL 5.7

8.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(...)

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

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

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

Этот алгоритм доступа применим, когда условие 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;

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