Как в mysql ускорить запрос с GROUP BY?

Question

[Avreliya777]

Сначала считаю сколько постов всего есть по тегам из таблицы tags_rel (id, id_tag, id_post) с проиндексированными колонками. ДАНО: юзер запрашивает посты с тегами, id которых равно 1, 2 или 3.
Берём только уникальные значения, конечно же, т.к. на каждый пост по несколько тегов, а значит могут повторяться посты, поэтому с GROUP BY:

SELECT COUNT(*)
FROM
(
	SELECT tags_rel.id
	FROM tags_rel
	LEFT JOIN posts ON tags_rel.id_post = posts.id
	WHERE id_tag IN (1,2,3) AND posts.status = 1
	GROUP BY tags_rel.id_post
) t1

Всё, посчитали, здесь всё отлично, запрос обработан за 0.01сек.
Идём выковыривать теперь сами посты:

SELECT *
FROM
(
	SELECT posts.*
	FROM posts
	LEFT JOIN tags_rel ON tags_rel.id_post = posts.id
	WHERE id_tag IN (1,2,3) AND posts.status = 1
	GROUP BY posts.id
) t1 LIMIT 0,50

И вот здесь всё хорошо ровно до тех пор, пока я не проставлю GROUP BY. Без группировки - сотые доли секунды. С группировкой - уже от 2 секунд и выше, в зависимости от количества найденного.

Как решать такие проблемы?

Думала уже может быстрее вообще все записи без пагинации вытащить, удалить как-то дубли из многомерного массива по колонке id и вытащить из него уже нужные для пагинации номера? Или память всю выжрет?
И сразу вдогонку вопрос может кто сталкиваться, если по FetchAll вытащить всё, как можно наиболее быстро сделать такой перебор многомерного массива на PHP? Можно ли так потягаться в производительности с SQL, или не стоит и пробовать?

Comments

[Akina]

Самый первый запрос, точнее его подзапрос, синтаксически ошибочен. И если и выполняется по причине отключенного ONLY_FULL_GROUP_BY, то даёт недетерминированный результат.

Остальное даже не читал, ибо нет смысла.

[Avreliya777]

Akina, Вы про тот который с COUNT? Так он вроде хорошо работает, на него у меня нет жалоб, и результат детерминированный, т.е. ровно тот даёт результат, какой нужно. Количество высчитывает правильно.
Здесь взяла пример: https://stackoverflow.com/a/19371614/6230467

[Akina]

Avreliya777,

на него у меня нет жалоб

SET SESSION sql_mode := 'ONLY_FULL_GROUP_BY';, а потом любуйтесь на свой запрос.

и результат детерминированный

Нет, это у Вас просто везучесть повышенная. В обычных условиях сервер грузит в кэш записи в том порядке, в каком они лежат в кластерном индексе, и извлекает оттуда без пересортировки. Поэтому результат абы детерминирован. Но если будет момент, когда голова таблицы в кэше отсутствует, и задница ещё не вымыта - получите хрень.

Здесь взяла пример

В примере как раз всё правильно. А вот применён он - неправильно. Более того, он к вашему случаю вообще неприменим.

Answer 1

[Rsa97]

SELECT *
  FROM `posts`
  WHERE `id` IN (
    SELECT `id_post`
      FROM `tags_rel`
      WHERE `id_tag` IN (1,2,3)
    ) AND `status` = 1
  LIMIT 0, 50

Comments

[Avreliya777]

Гениально!!))
Спасибо большое!
Добавила к вашему ответу group by (после внутреннего WHERE), и всё заработало очень быстро!
Я правильно поняла, это потому что группировка это сложный процесс и нужно было выполнять её в маленькой таблице tags_rel, а потом уже готовый результат пихать в posts.
Было у меня тоже как-то идея такая, но я подумала что нельзя в IN такие большие строки пихать:)) а нет, оказывается целую выборку можно в IN засовывать! :)))

итоговый запрос получился:
SELECT *
FROM `posts`
WHERE `id` IN
(
SELECT `id_post`
FROM `tags_rel`
WHERE `id_tag` IN (1,2,3)
GROUP BY id_post
) AND `status` = 1
LIMIT 0, 50

[Rsa97]

Avreliya777, GROUP BY тут вообще не нужен.

[Avreliya777]

Rsa97, ааааа, я поняла! если в IN значение повторяется, то оно не берётся, правильно?? :)))
Сейчас проверила действительно только уникальные id выводятся.

[Avreliya777]

Rsa97 подскажите что у меня не правильно в первом запросе, который с COUNT? Там Akina что-то сказать мне пытается, но мы, очевидно, на разных языках говорим (не вижу что именно неправильно). Там тоже лучше переделать всё как я понимаю по такому же типа, как вот этот ваш запрос?

[Rsa97]

Avreliya777, Всё просто. Представьте таблицу

id_rel | id_post | title
     1 |       1 | Новость 1
     2 |       1 | Новость 1
     3 |       1 | Новость 1

SELECT *
  FROM `table`
  GROUP BY `id_post`

Вы делаете группировку по id_post. Какое именно из значений id_rel должно быть в результате и почему именно оно?
Именно поэтому стандарт SQL запрещает при группировке использование в результате колонок, не входящих в список GROUP BY или не обёрнутых в агрегатную функцию.
MySQL с отключенным ONLY_FULL_GROUP_BY не ругается на это нарушение, но невозможно определить, какое именно значение будет в результате.
С включённым ONLY_FULL_GROUP_BY MySQL будет ругаться на такой запрос, за исключением случаев, когда неагрегированная колонка содержит одни и те же значения для всех строк в группе. В примере выше id_rel покажет ошибку, title не покажет ошибки.

[Avreliya777]

Rsa97 Вот теперь я всё поняла, спасибо большое! :)
Буду знать хоть теперь что такое ONLY_FULL_GROUP_BY и нюансы работы с group by.

Если есть время проверьте пожалуйста моё понимание:

я правильно понимаю, что для count при выключенном ONLY_FULL_GROUP_BY вообще без разницы что там указано в select в моём примере, я указала GROUP BY tags_rel.id_post, значит в остальных полях будет непонятное значение, в том числе и извлекаемое мною id (SELECT tags_rel.id)? Но нам же в данном случае без разницы какие будут данные? Нам же количество строк нужно найденных посчитать, а не сгруппированные правильно их данные. Я думаю потому count и выполняется быстро, что он не выполняет полноценно группировку (не наслаивает, не пытается агрегатные функции выполнить и т.д.), а просто находит разные id_post (то, что указано в GROUP BY), и считает их кол-во. Так что по сути первый запрос у меня правильный ведь?

А вот когда уже не COUNT, а второй наш запрос, именно на вытаскивание данных, то тут согласна, нужно аккуратнее с group by. Но в моём случае видимо даже и этот вариант, который у меня изначально был, был допустим, т.к. я группировала по автоинкрементной уникальной колонке ID, то все остальные колонки таблицы posts при объединении просто перекрывали друг друга, но оно и без разницы, ведь если id одинаковый, то и данные одинаковые в колонках, каша была бы только в присоединённых (JOIN tags_rel) таблицах, но я их и не дёргала в select, в select только posts.*

[Akina]

Rsa97,

С включённым ONLY_FULL_GROUP_BY MySQL будет ругаться на такой запрос, за исключением случаев, когда неагрегированная колонка содержит одни и те же значения для всех строк в группе.

Нет. Значения не проверяются на идентичность, ругаться будет в любом случае.
Не будет ругаться, если в выходном наборе присутствует поле таблицы, а в выражении группировки - первичный ключ этой таблицы. Или во всяком случае не должно - ибо это гарантирует идентичность всех значений в группе.
Кстати, забавно с композитными ключами - то ругается, то нет (подозреваю, что это могло бы зависеть от того, используется ли данный индекс в плане, но с другой стороны ошибка-то детектируется ещё до построения плана... хотя не исключено, что это ловится на стадии построения первичного графа связей). С просто уникальными емнип ругается всегда, хотя в последних версиях не смотрел.

PS. LIMIT без сортировки - как-то некошерно...

Avreliya777,

я правильно понимаю, что для count при выключенном ONLY_FULL_GROUP_BY вообще без разницы что там в скобках у count?

Неверно. COUNT(*) считает общее количество записей безотносительно к значениям, а COUNT(column) считает количество NOT NULL значений в указанном поле, которое может быть или равно общему, или меньше. Кстати, именно поэтому COUNT(*) в принципе не может вернуть значение меньше единицы, тогда как COUNT(column) может вернуть и ноль.

[Avreliya777]

Akina,

Неверно. COUNT(*) считает общее количество записей безотносительно к значениям, а COUNT(column) считает количество NOT NULL pyfxtybq

Ого, спасибо, надо бы будет запомнить этот важный нюанс. Так, значит когда считаю кол-во строк, значит нужно всё-таки указывать звёздочку.

[N]

Rsa97,
Akina,

Для общего развития вопрос...вот допустим есть запрос:

SELECT COUNT(`id`)
  FROM `data`
  WHERE `id` IN (
    SELECT `id_data`
      / * JOIN несколько таблиц и т.п.*/
      WHERE /* куча условий */
    )
/* LIMIT 1000 */

Вопрос по производительности:
Как лучше модифицировать запрос, чтобы подсчитать кол-во уникальных id из data с ограничением по лимиту например в 1000.
Допустим под условия в подзапросе попадут 5000 записей, но нужно вывести максимум 1000 даже, если больше...
В каком месте LIMIT указать? Точнее стоит ли его вообще указывать в конце запроса, если он всё равно будет считать исходя из подзапроса внутри?

[Rsa97]

В приведённом примере внешний запрос не нужен.

SELECT MIN(COUNT(DISTINCT `id_data`), 1000)
  / * JOIN несколько таблиц и т.п.*/
      WHERE /* куча условий */

А использование COUNT(*) с LIMIT без группировки смысла не имеет, поскольку в результате всегда будет одна строка.

[N]

Rsa97, А что за второй параметр у MIN ? Что-то не нашёл в MySQL...

[N]

Rsa97, Но всё равно вопрос в том...что "внутри" он переберёт все записи или только первую попавшуюся 1000 под условия WHERE ?

[Rsa97]

N, Да, тут надо не MIN, а LEAST.
Сколько записей будет рассмотрено - зависит от запроса и индексов. Надо смотреть EXPLAIN по конкретным данным и конкретному запросу.

[N]

Rsa97, Спасибо!
Ладно...попробую погуглить на эту тему...
Просто если просто выбирать записи без подсчёта, то по EXPLAIN результаты разные в зависимости от условий в WHERE (они динамические), но на все нужные поля есть индекс...но бывает так, что даже с учётом индекса бывает большой "скан" таблиц...
Поэтому вопрос больше был в стиле "выжать" максимум из самого MySQL исходя из этой структуры, как я описал...

Насчёт вопроса автора - всё верно...у него более статичные данные по условиям выборки...

Answer 2

[Владислав Щеколдин]

Собственно ответ уже был дан выше и отличный по качеству. Хотел лишь уточнить одну "глобальную" мысль. Довольно часто единственным реальным способом ускорить запрос с group by явлется поиск варианта запроса где удастся обойти group by. Потому что группировка по своей сути подразумевает что: вначале мы создаем временное хранилище, туда отгружаем подходящие строки, а после этого по ним пробегаемся ещё процессом группировки (сравнения каждого с каждым). Большие по размеру группировки довольно часто и вовсе вызывают требование создавать для неё временный файл. Ну и сам способ вычисления группировки - небыстрая штука.