10.2.1.17 GROUP BY Optimization — MySQL 8.4

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

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

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

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

Неполное сканирование индекса также может использоваться в отсутствие GROUP BY при некоторых условиях. См. Метод сканирования пропусков в диапазонах.

Неполное сканирование индекса

Наиболее эффективный способ обработки GROUP BY — это когда индекс используется для непосредственного извлечения столбцов группирования. С этим методом доступа MySQL использует свойство некоторых типов индексов, что ключи упорядочены (например, BTREE). Это свойство позволяет использовать группы поиска в индексе без необходимости учитывать все ключи в индексе, удовлетворяющие всем WHERE условиям. Этот метод доступа учитывает только часть ключей в индексе, поэтому он называется Неполным сканированием индекса. Когда нет условия WHERE, неполное сканирование индекса считывает столько ключей, сколько групп, что может быть намного меньше, чем всех ключей. Если условие WHERE содержит предикаты диапазона (см. обсуждение типа соединения range в Раздел 10.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;
```