index_merge сортировка_пересечение

До MariaDB 5.3, метод доступа index_merge поддерживал операции union, sort-union, и intersection. Начиная с MariaDB 5.3, также поддерживается операция sort-intersection. Это позволяет использовать index_merge в большем количестве случаев.

Эта функция по умолчанию отключена. Чтобы включить её, активируйте переключатель оптимизатора index_merge_sort_intersection следующим образом:

SET optimizer_switch='index_merge_sort_intersection=on'

Ограничения index_merge/пересечение

До MariaDB 5.3, метод доступа index_merge имел одну стратегию пересечения, называемую intersection. Эта стратегия может использоваться только тогда, когда объединённые сканирования индексов производят потоки строк в порядке возрастания rowid. На практике это означает, что intersection может быть создан только из условий равенства (=).

Например, следующая запрос будет использовать intersection:

MySQL [ontime]> EXPLAIN SELECT AVG(arrdelay) FROM ontime WHERE depdel15=1 AND OriginState ='CA';
+--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+-------------------------------------------------+
|id|select_type|table |type       |possible_keys       |key                 |key_len|ref |rows |Extra                                            |
+--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+-------------------------------------------------+
| 1|SIMPLE     |ontime|index_merge|OriginState,DepDel15|OriginState,DepDel15|3,5    |NULL|76952|Using intersect(OriginState,DepDel15);Using where|
+--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+-------------------------------------------------+

но если вы замените OriginState ='CA' на OriginState IN ('CA', 'GB') (что соответствует тому же количеству записей), то intersection больше не будет использоваться:

MySQL [ontime]> explain select avg(arrdelay) from ontime where depdel15=1 and OriginState IN ('CA', 'GB');
+--+-----------+------+----+--------------------+--------+-------+-----+-----+-----------+
|id|select_type|table |type|possible_keys       |key     |key_len|ref  |rows |Extra      |
+--+-----------+------+----+--------------------+--------+-------+-----+-----+-----------+
| 1|SIMPLE     |ontime|ref |OriginState,DepDel15|DepDel15|5      |const|36926|Using where|
+--+-----------+------+----+--------------------+--------+-------+-----+-----+-----------+

В наших экспериментах последний запрос также выполнялся в 5 раз медленнее (от 2,2 до 10,8 секунд).

Как index_merge/сортировка_пересечение улучшает ситуацию

В MariaDB 5.3, при включённом index_merge_sort_intersection, планы пересечения index_merge могут быть построены из условий, не являющихся равенством:

MySQL [ontime]> explain select avg(arrdelay) from ontime where depdel15=1 and OriginState IN ('CA', 'GB');
+--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+
|id|select_type|table |type       |possible_keys       |key                 |key_len|ref |rows |Extra                                                   |
+--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+
| 1|SIMPLE     |ontime|index_merge|OriginState,DepDel15|DepDel15,OriginState|5,3    |NULL|60754|Using sort_intersect(DepDel15,OriginState); Using where |
+--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+

В наших тестах этот запрос выполнялся за 3,2 секунды, что не так хорошо, как в случае с двумя равенствами, но всё же намного лучше, чем 10,8 секунд, которые мы получали без sort_intersect.

Стратегия sort_intersect имеет более высокую нагрузку, чем intersect, но способна обрабатывать более широкий набор WHERE условий.

Когда использовать

index_merge/sort_intersection лучше всего работает на таблицах с большим количеством записей и где пересечения достаточно велики (но всё ещё достаточно малы, чтобы полное сканирование таблицы было избыточным).

Преимущества ожидаются больше для операций, ограниченных операциями ввода-вывода.

См. также

• Что такое MariaDB 5.3