В чём принципиальная разница двух запросов?

Question

[Игорь]

SELECT * FROM (
    SELECT MAX(c5_.id) AS sclr_32 FROM contacts_history c5_ 
    WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id
) tt 
JOIN contacts_history ch ON tt.sclr_32 = ch.id;

SELECT * FROM contacts_history WHERE id IN (
    SELECT MAX(c5_.id) AS sclr_32 FROM contacts_history c5_ WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id
);

Какой из запросов будет иметь большую деградацию по мере роста количества данных.

Comments

[FanatPHP]

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

[Игорь]

У нас сейчас наблюдается просадка по производительности, наш тим Лид говорит, что этот запрос

SELECT * FROM (SELECT MAX(c5_.id) AS sclr_32 FROM contacts_history c5_ WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id) tt JOIN contacts_history ch ON tt.sclr_32 = ch.id;

Будет выполняться медленнее, но иметь наименьшую деградацию по мере роста.

[Игорь]

А этот запрос выполняется быстрее, но каждым днём всё хуже и хуже.

SELECT * FROM contacts_history WHERE id IN (SELECT MAX(c5_.id) AS sclr_32 FROM contacts_history c5_ WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id);

[FanatPHP]

Ну по-хорошему тут надо Akina надо звать.
Для меня из опыта запрос сподзапросом in чреват адовыми тормозами, а с джойном более предсказуем.
Но я не вижу, где тут будут тормоза, если передать в in сразу готовый набор значений. Ну если он не будет совсем гигантским, на сотни тыщ значений

[Максим Y]

во втором движок может попытаться переписать на джойн сам, и в процессе накосячить

Оптимизатор мог бы пытаться переписать IN на JOIN, если бы в запросе не было агрегатной функции.

Если версия сервера позволяет - можно обойтись одним сканом таблицы:

SELECT *
  FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c5_.contact_id ORDER BY c5_.id DESC) AS rn,
               c5_.* -- или перечислить здесь все нужные колонки
          FROM contacts_history c5_
         WHERE (c5_.organization_id = '1')
        ) tt
 WHERE rn = 1;

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

А здесь полностью соглашусь.

[Akina]

В чём принципиальная разница двух запросов?

В том, что они реализуют различную логику и возвращают разные данные. Даже структура различается. На этом фоне всё остальное несущественно.

Какой из запросов будет иметь большую деградацию по мере роста количества данных.

Второй - WHERE IN (subquery) обычно наиболее медленный из альтернативных вариантов, и чем больше массив данных, тем заметнее разница.. а самый быстрый обычно WHERE EXISTS.

[FanatPHP]

Игорь отредактируй вопрос блин уже, а то читать невозможно

[galaxy]

Akina, А где там разная логика? или я чего-то не вижу?

[Akina]

galaxy,

А где там разная логика? или я чего-то не вижу?

Вероятно, не видишь.

Вот максимально упрощённая модель. Количество полей различно. Количество записей различно. Может там быть одинаковая логика - ну хотя бы теоретически, а?

[galaxy]

Akina, у него она таблица. С поправкой на лишнее поле, запросы логически идентичны

[Akina]

galaxy,

С поправкой на лишнее поле, запросы логически идентичны

Запросы возвращают различное количество записей. Соответственно любые попытки объявить их логически идентичными - заведомо ошибочны.

Или Вы как-то ну очень иначе понимаете идентичность.

[galaxy]

Akina, https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b932d3...

Не понимаю. Сделайте мне тут разное количество записей

[Akina]

:facepalm: У Вас частный случай - id является уникальным полем.

[galaxy]

Akina, ок. Почему-то то, что это может быть не так, мне в голову даже и не пришло.

[d-stream]

А если посмотреть вдумчиво в explain то может вообще третий вариант народиться... Или эти два схлопнутся в какой-нибудь 12-15 версии mysql

[Игорь]

Да, ещё у нас пагинация, чёрт возьми.
Я так думаю это вообще треш

SELECT * FROM (SELECT MAX(c5_.id) AS sclr_32 FROM contacts_history c5_ WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id) tt JOIN contacts_history ch ON tt.sclr_32 = ch.id LIMIT 10000,100;

[Игорь]

Объёмы растут в сутки по 11 000 тыс, в будущем может по 50 000.

Прикол в том что нам нужно выбирать только последние исторические записи контакта, поэтому у нас такая конструкция.

В двух словах, у нас по сути контакт лист и у каждого контакта есть своя история.
Анализируя историю нам нужно получить все последние звонки каждого контакта.

Ну это я так, к слову, что бы понимали контекст.

[Дмитрий Свиридов]

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

[ComodoHacker]

Akina, Скорее, id = PK это общий случай, а у вас частный :)
И в этом общем случае запросы эквивалентны.

[ComodoHacker]

Игорь, Думаю, вам стоит задуматься об оптимизации схемы данных под ваши задачи. Например, материализовать эти самые "последние записи" в отдельной таблице.

[Максим Y]

Игорь, попробуйте ещё мой вариант из 5-го комментария. Если версия сервера 8+, или когда там row_number() добавили.

Если выбирать из ваших двух, то тимлид скорее прав.

[Akina]

ComodoHacker, когда мы говорим об эквивалентности запросов, мы говорим об этом без каких-либо ограничений на структуру и данные. Эквивалентность - это равенство реализованной в запросе логики, а структура и данные находятся за по делами запроса.

Что, собственно, и наблюдается - достаточно полю id потерять уникальность, и эквивалентноси конец, да? Так не обманывайте себя, её не было.

[ComodoHacker]

Akina, При всем уважении к вашему опыту, не соглашусь. Об эквивалентности нужно говорить именно в контексте конкретной схемы данных, если мы решаем конкретную задачу, а не теоретизируем. То, что ТС не привел свою схему сразу, вынуждая нас гадать, это его ошибка.

[Игорь]

ComodoHacker,

Думаю, вам стоит задуматься об оптимизации схемы данных под ваши задачи. Например, материализовать эти самые "последние записи" в отдельной таблице.

Возможно, нужно собрать все за и против, хотя идея хорошая, сейчас в базе 100 тыс контактов.
Записи будут 1:1.

Я даже тупо пробовал в сам контакт писать PK последней записи, но бизнес логика подразумевает передачу контакта в другой проект с обнулением истории/статусов/чего-либо.

Но это мы уже отклоняемся от темы.

[Akina]

ComodoHacker,

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

Если так подходить, то просто смотрим на изначальные запросы, обнаруживаем, что структура выходных данных различна, и ставим штамп "Неэквивалентны". Чистая практика, никакого трёпа. Кстати, именно с этого я и начал.

А вот всё остальное - это и есть теоретизирование.

Возможно, Вы как-то иначе понимаете эквивалентность запросов. Для меня эквивалентны - это "дают одинаковые выходные наборы при любых одинаковых входных данных". WHERE IN и WHERE EXISTS - эквивалентны. LEFT JOIN с условием по правой таблице в ON и такой же с условием во WHERE и дополнительным OR IS NULL - эквивалентны. А показанные автором - нет.

[Игорь]

Максим Y,

Если версия сервера позволяет - можно обойтись одним сканом таблицы:

Версия позволяет.

Проверил, запрос выполняется за 5 сек.
Долго.

Explain

Answer 1

[SagePtr]

Вместо того, чтобы гадать, можно посмотреть explain обоих запросов.
В крайнем случае, сгенерировать тестовые данные и забить в таблицы, измерить после этого с отключённым кэшем запросов.

Answer 2

[ComodoHacker]

Принципиальной разницы нет.

Comments

[Игорь]

Сейчас в системе используется запрос № 1
На данный момент чувствуем просадку при большой активности запросов, что вполне логично.

[ComodoHacker]

Игорь, Прощу прощения, я пропустил последнюю фразу вашего вопроса. Я имел в виду, что в семантике запросов нет разницы.

Что касается производительности, тут нужно тестировать, анализировать планы, статистику и т.д., как обычно.

[LoliDeveloper]

ComodoHacker, а матан не поможет? Чисто эмпирически, серьёзно?

[ComodoHacker]

LoliDeveloper, Не знаю, мне как-то не помогает.
Если вам помогает, рад за вас. :)

[LoliDeveloper]

ComodoHacker, не, я субд не юзаю, просто интересно стало

[ComodoHacker]

LoliDeveloper, Реляционные СУБД это больше алгебра множеств. :)

Answer 3

[grebenyukov]

Будет разным количество столбцов, т.к. в первом случае select * производится из (подзапрос tt join таблица contacts_history), а во втором случае - только из таблицы contacts_history. То есть, в первом варианте будет плюс поле sclr_32.
По производительности мне эмпирически больше нравится первый вариант, но как выше правильно написал SagePtr, надо смотреть план, причем с реалистичным количеством строк и соотношением количества строк к количеству уникальных значений поля contacts_history.contact_id.
А если рассматривать применение подобных запросов на практике, то возможно, он еще будет обернут в select * from (...) limit xx offset yyy; Тогда и анализ надо будет проводить относительно полного текста. И тут мне кажется. первый вариант быстрее выдаст первые xx строк.

Comments

[Игорь]

Первый запрос выполняется быстрее на порядок, наверное важно сколько он ресурсов забирает на эту операцию.

У нас сервер повис, когда все начали смотреть свои отчёты, наш Тим Лид утверждает что запрос № 1 долго не проживёт, с ростом данных он будет деградировать по экспоненте.