Группировка в MySQL
Дата: 23.01.2014
Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru
На форуме SQLinfo часто встречаются вопросы связанные с "неправильной" работой группировки в MySQL. В статье объясняется особенность работы группировки в MySQL и показано как правильно составлять запросы в таких случаях.
Суть проблемы
По стандарту SQL в запросе с группировкой в части перечисления полей (то, что идет после SELECT) можно указывать ТОЛЬКО те поля, по которым идет группировка, или которые используются с групповыми функциями (sum, max, group_concat, ...)
Для примера рассмотрим таблицу сообщений, имеющую поля (`user_id`, `post`, `time`), в которой хранится id пользователя, текст сообщения и время добавления сообщения. Следующий запрос не соответствует стандарту SQL, так как к полю `post` не применяется группировка, и в большинстве СУБД вернет ошибку:
SELECT `user_id`, `post`, max(`time`) FROM `test_table` GROUP BY `user_id`;
MySQL допускает выполнение запросов, не удовлетворяющих данному правилу. При этом для полей без групповых функций и не указанных в части GROUP BY выбирается произвольная строка из группы. Это расширение стандарта SQL введено для улучшения производительности, чтобы избежать лишней группировки и сортировки. Если заранее известно, что нужное нам поле в пределах группы имеет одинаковое значение (например, такая ситуация может быть в запросах с JOIN), то вместо дополнительной группировки по этому полю, как того требует стандарт SQL, мы предлагаем серверу взять произвольное значение.
Обратите внимание: В режиме ONLY_FULL_GROUP_BY поведение сервера MySQL соответствует стандарту SQL.
set @@sql_mode='only_full_group_by';
SELECT `user_id`, `post`, max(`time`) FROM `test_table` GROUP BY `user_id`;
ERROR 1055 (42000): 'test.test_table.post' isn't in GROUP BY
Если данная особенность применяется к случаю, когда выбираемое поле имеет различное значение в пределах группы, то результат становится неопределенным. Проиллюстрируем ситуацию на простом примере. Пусть мы хотим выбрать последнее сообщение каждого пользователя и таблица имеет вид:
create table test_table (user_id int, post varchar(255), `time` datetime);
insert into test_table values (1, 'post1', '2012-10-14 11:21:22'),(1, 'post2', '2012-11-12 18:53:26'),(1, 'post3', '2012-11-15 16:51:08');
SELECT * FROM test_table;
+---------+--------+---------------------+
| user_id | post | time |
+---------+--------+---------------------+
| 1 | post 1 | 2012-10-14 11:21:22 |
| 1 | post 2 | 2012-11-12 18:53:26 |
| 1 | post 3 | 2012-11-15 16:51:08 |
+---------+--------+---------------------+
SELECT `user_id`, `post`, max(`time`) FROM `test_table` GROUP BY `user_id`;
+---------+--------+---------------------+
| user_id | post | max(`time`) |
+---------+--------+---------------------+
| 1 | post 1 | 2012-11-15 16:51:08 |
+---------+--------+---------------------+
Как видим, значение поля post взято не из той строки, которая соответствует max(`time`). А если результат правильный, то это не более чем случайность.
Замечание: Попытки повлиять на выбираемые строки с помощью сортировки эффекта не дадут, так как сортировка выполняется после группировки. Например:
SELECT * FROM `таблица` GROUP BY user_id ORDER BY `time` DESC;
В этом случае для каждого user_id будет выбрана произвольная строка с данным user_id, после чего эти строки будут отсортированы по времени.
Обратите внимание: Если вдаваться в детали, то стоит сказать, что выбирается первая прочитанная строка из группы. Т.е. если вы несколько раз выполнили запрос и получили один и тот же устраивающий вас результат, то это не гарантия, что и в дальнейшем будет так. Например, с ростом таблицы для выполнения запроса может быть выбран другой индекс, и результат будет другим. Кроме того, это поведение не документировано и в других версиях может отличатся. Попытки упростить запрос, основываясь на том, что выбирается первая строка из группы, могут привести к некорректной работе приложения.
Что делать?
Существует несколько способов решения данной задачи:
Первый способ. Сначала найти комбинации (`user_id`,`time`), а по ним уже выбрать недостающее поле `post`. Для простоты предположим, что у пользователя не может быть двух одновременных сообщений, т.е. комбинация (`user_id`,`time`) однозначно идентифицирует строку. Тогда нужный нам запрос будет выглядеть так:
SELECT t.`user_id`, t.`post`, t.`time`
FROM `test_table` t JOIN
(SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`) as t1
ON t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`;
+---------+--------+---------------------+
| user_id | post | time |
+---------+--------+---------------------+
| 1 | post 3 | 2012-11-15 16:51:08 |
+---------+--------+---------------------+
При возможности осуществить группировку и JOIN по индексам такой способ является оптимальным с точки зрения производительности.
Второй способ. Пусть поле `time` имеет тип данных DATETIME, т.е. принимает значения вида '2012-02-15 01:47:19'. Тогда мы знаем, что значение этого поля всегда будет иметь ширину в 19 символов. В этом случае мы можем искать максимум не от поля `time`, а от строки, склеивающей значения полей `time` и `post`, а потом результат разбить назад на составляющие.
SELECT `user_id`, max(`time`), SUBSTR(MAX(CONCAT(`time`, `post`)), 20) as `post`
FROM `test_table` GROUP BY `user_id`;
+---------+---------------------+--------+
| user_id | max(`time`) | post |
+---------+---------------------+--------+
| 1 | 2012-11-15 16:51:08 | post 3 |
+---------+---------------------+--------+
Замечание: Требование одинакового значения ширины поля `time` у всех строк не является обязательным. Достаточно знать, что оно не может быть более чем какое-то значение. Предположим, что поле `time` определено как tinyint unsigned, т.е. максимальное значение в этом поле трехзначное. Дополняя значения этого поля слева, например, нулями до ширины в 3 знака, мы получим стоки одинаковой длины в 3 символа ('002', '016', '241'), результат сравнения которых будет аналогичен результату сравнения исходных чисел.
SELECT `user_id`, max(`time`),
SUBSTR(MAX(CONCAT(LPAD(`time`,3,'0'), `post`)), 4) as `post`
FROM `test_table` GROUP BY `user_id`;
Обратите внимание: вычисление групповых функций идет от строки. Например, если поле `time` может содержать отрицательные числа, то max от строки, полученной из числа, может дать неправильный результат.
Такой метод может быть необходим в случае, если группирующая функция берется не от значения поля, а от результата каких-то вычислений над полем. Тогда первый метод через JOIN не применим, так как после выполнения группировки у нас будет значение `user_id` и max( от каких-то вычислений с полем `time` ), а не значение самого поля `time`. Т.е. обратится вновь к таблице мы сможем только по полю `user_id`, а это не позволит нам идентифицировать нужную строку.
Пример подобной задачи разобран на форуме
Третий способ. Используя зависимый подзапрос, для каждой строки таблицы проверять существует ли строка с таким же user_id и большим временем. Если не существует, значит это и есть нужная нам строка.
SELECT * FROM test_table t where not exists
(SELECT * FROM test_table where user_id = t.user_id and `time` > t.`time` );
С точки зрения производительности это плохой метод, так как требуется полный скан таблицы и для каждой строки выполняется подзапрос.
Четвертый способ. Представляет собой предыдущий запрос, переписанный через JOIN.
SELECT t1.* FROM test_table t1 LEFT JOIN test_table t2
ON t1.user_id = t2.user_id AND t1.`time` < t2.`time`
WHERE t2.user_id IS NULL;
В данном примере каждой строке таблицы сопоставляются строки с тем же значением user_id и большим временем. Для строки с максимальным значением времени соответствия не будет, что и определяется условием "t2.user_id IS NULL". С точки зрения производительности этот метод лишь немного быстрее предыдущего, так как JOIN выполняется чуть быстрее, чем зависимый подзапрос (каждое выполнение подзапроса требует определенных накладных расходов, которые можно увидеть путем профилирования)).
Замечание: Если действие запроса вызывает вопросы, рекомендую выполнить его в упрощенном виде и посмотреть на результат.
SELECT * FROM test_table t1 LEFT JOIN test_table t2
ON t1.user_id = t2.user_id AND t1.`time` < t2.`time`;
Пятый способ. Сканируя таблицу, отсортированную в порядке "user_id, `time` desc" с помощью пользовательских переменных оставляем по первому значению из каждой группы с одинаковым user_id.
set @i:=0, @u:=0;
select user_id, post, `time` from (select *, if (@u=user_id, @i:=@i+1,(@i:=0) or (@u:=user_id) or 1), @i i from test_table order by user_id, `time` desc) t where i<1;
Замечание: звездочка в подзапросе использована для удобства восприятия, так как все нужные нам поля user_id, post, `time` совпадают с полным списком полей в тестовой таблице. Однако указывать * в рабочих запросах - плохая практика.
Применительно к данной задаче это плохое решение, так как записывает во временную таблицу всю исходную плюс дополнительные поля, что создает дополнительную нагрузку на диски.
Обратите внимание: для некоторых задач пользовательские переменные могут значительно улучшить производительность, см статью Оптимизация запросов MySQL с использованием пользовательских переменных .
Заключение
- При использовании расширения стандарта SQL (возможность выбора не группируемых столбцов) учтите, что поведение сервера зависит от используемого режима. Тщательно проверяйте работу ваших запросов в новых версиях. Например, использование этой возможности в подзапросах в MariaDB 5.3 и выше может привести к неожиданным результатам, подробнее см
https://mariadb.atlassian.net/browse/MDEV-4595
- Ненадлежащее использование этой возможности на основании того, что выбирается "первый" элемент из группы, может привести к некорректной работе приложения
- Производительность того или иного способа из описанных в статье нужно проверять отдельно в каждом конкретном случае
P.S. Если после прочтения статьи ваш вопрос с группировкой в MySQL остался нерешенным, то задавайте его на форуме SQLinfo.
Дата публикации: 23.01.2014
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
