8.2.1.15 GROUP BY Optimization — MySQL 5.7

8.2.1.15 Оптимизация GROUP BY

Наиболее общий способ удовлетворения условия GROUP BY заключается в сканировании всей таблицы и создании новой временной таблицы, где все строки каждой группы идут подряд, а затем использование этой временной таблицы для определения групп и применения агрегатных функций (если таковые имеются). В некоторых случаях MySQL может сделать намного лучше, чем это, и избежать создания временных таблиц, используя доступ к индексу.

Наиболее важными предпосылками для использования индексов для GROUP BY являются то, что все столбцы GROUP BY ссылаются на атрибуты из одного и того же индекса, и что индекс хранит свои ключи в порядке (как это справедливо, например, для индекса BTREE, но не для индекса HASH). Возможность замены использования временных таблиц доступом к индексу также зависит от того, какие части индекса используются в запросе, условий, указанных для этих частей, и выбранных агрегатных функций.

Существует два способа выполнения запроса GROUP BY с помощью доступа к индексу, как подробно описано в следующих разделах. Первый метод применяет операцию группирования вместе со всеми предикатами диапазона (если таковые имеются). Второй метод сначала выполняет сканирование диапазона, а затем группирует полученные кортежи.

В MySQL используется GROUP BY для сортировки, поэтому сервер также может применять оптимизации ORDER BY к группировке. Однако, полагаться на неявную или явную сортировку GROUP BY не рекомендуется. См. Раздел 8.2.1.14, «Оптимизация ORDER BY».

Сканирование индекса с произвольным доступом

Наиболее эффективный способ обработки запроса GROUP BY — когда индекс используется для прямого извлечения столбцов группирования. С помощью этого метода доступа MySQL использует свойство некоторых типов индексов, что ключи упорядочены (например, BTREE). Это свойство позволяет использовать группы поиска в индексе, не рассматривая все ключи в индексе, удовлетворяющие всем условиям WHERE. Этот метод доступа учитывает только часть ключей в индексе, поэтому он называется Сканирование индекса с произвольным доступом. При отсутствии условия WHERE, сканирование индекса с произвольным доступом считывает столько ключей, сколько групп, что может быть значительно меньше, чем количество всех ключей. Если условие WHERE содержит предикаты диапазона (см. обсуждение типа соединения range в Разделе 8.8.1, «Оптимизация запросов с помощью EXPLAIN»), сканирование индекса с произвольным доступом ищет первый ключ каждой группы, удовлетворяющей условиям диапазона, и снова считывает минимальное количество ключей. Это возможно при следующих условиях:

Запрос выполняется над одной таблицей.
Имена столбцов GROUP BY указывают только столбцы, образующие лексически наименьший префикс индекса, и никакие другие столбцы. (Если вместо GROUP BY запрос имеет условие DISTINCT, все уникальные атрибуты относятся к столбцам, образующим лексически наименьший префикс индекса). Например, если в таблице t1 есть индекс на (c1,c2,c3), сканирование индекса с произвольным доступом применимо, если запрос имеет GROUP BY c1, c2. Оно неприменимо, если запрос имеет GROUP BY c2, c3 (столбцы не являются лексически наименьшим префиксом) или GROUP BY c1, c2, c4 (c4 отсутствует в индексе).
Единственными агрегатными функциями, используемыми в списке выбора (если таковые имеются), являются MIN() и MAX(), и все они относятся к одному столбцу. Столбец должен находиться в индексе и должен непосредственно следовать за столбцами в GROUP BY.
Любые другие части индекса, кроме тех, которые из GROUP BY, упомянутые в запросе, должны быть константами (то есть они должны быть упомянуты в равенствах с константами), за исключением аргумента функций MIN() или MAX().
Для столбцов в индексе должны быть проиндексированы полные значения столбцов, а не только префикс. Например, при использовании c1 VARCHAR(20), INDEX (c1(10)), индекс использует только префикс значений c1 и не может быть использован для сканирования индекса с произвольным доступом.

Если сканирование индекса с произвольным доступом применимо к запросу, вывод EXPLAIN показывает Using index for group-by в столбце Extra.

Предположим, что существует индекс idx(c1,c2,c3) на таблице t1(c1,c2,c3,c4). Метод доступа сканирования индекса с произвольным доступом может быть использован для следующих запросов:

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

Следующие запросы не могут быть выполнены с помощью этого быстрого метода выбора по приведенным причинам:

Есть агрегатные функции, отличные от MIN() или MAX():
```
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
```
Столбцы в условии GROUP BY не образуют лексически наименьшего префикса индекса:
```
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
```
Запрос относится к части ключа, которая следует за GROUP BY частью, и для которой нет равенства с константой:
```
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
```
Если бы запрос включал в себя WHERE c3 = const, сканирование индекса с произвольным доступом могло быть использовано.

Метод доступа сканирования индекса с произвольным доступом может быть применен к другим формам ссылок на агрегатную функцию в списке выбора, помимо ссылок MIN() и MAX(), которые уже поддерживаются:

AVG(DISTINCT), SUM(DISTINCT) и COUNT(DISTINCT) поддерживаются. AVG(DISTINCT) и SUM(DISTINCT) принимают один аргумент. COUNT(DISTINCT) может иметь более одного столбцового аргумента.
В запросе не должно быть условия GROUP BY или DISTINCT.
Ограничения сканирования индекса с произвольным доступом, описанные ранее, по-прежнему применимы.

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

Сканирование индекса с точным доступом

Сканирование индекса с точным доступом может быть полным сканированием индекса или сканированием индекса диапазона в зависимости от условий запроса.

Когда условия для сканирования индекса с произвольным доступом не выполняются, все равно может быть возможно избежать создания временных таблиц для запросов GROUP BY. Если в условии WHERE есть условия диапазона, этот метод считывает только ключи, удовлетворяющие этим условиям. В противном случае он выполняет сканирование индекса. Поскольку этот метод считывает все ключи в каждом диапазоне, определенном условием WHERE, или сканирует весь индекс, если условия диапазона отсутствуют, он называется Сканирование индекса с точным доступом. При сканировании индекса с точным доступом операция группирования выполняется только после того, как все ключи, удовлетворяющие условиям диапазона, будут найдены.

Для работы этого метода достаточно, чтобы для всех столбцов в запросе, ссылающихся на части ключа, предшествующие или находящиеся между частями ключа GROUP BY, существовало условие равенства с константой. Константы из условий равенства заполняют любые «пробелы» в ключах поиска, чтобы было возможно сформировать полные префиксы индекса. Эти префиксы индекса затем могут быть использованы для поиска по индексу. Если результат GROUP BY требует сортировки, и возможно сформировать ключи поиска, которые являются префиксами индекса, MySQL также избегает дополнительных операций сортировки, поскольку поиск с префиксами в упорядоченном индексе уже извлекает все ключи в порядке.

Предположим, что существует индекс idx(c1,c2,c3) на таблице t1(c1,c2,c3,c4). Следующие запросы не работают с методом доступа сканирования индекса с произвольным доступом, описанным ранее, но все еще работают с методом доступа сканирования индекса с точным доступом.

Есть пробел в GROUP BY, но он покрывается условием c2 = 'a':
```
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
```
GROUP BY не начинается с первой части ключа, но есть условие, которое предоставляет константу для этой части:
```
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
```