Комментарии 17
Вот итоговое решение
С точки зрения сервера, будь у него разум, такой запрос - изощрённое, тонкое издевательство. Вот зачем вы используете LEFT JOIN, если финальный WHERE превращает их все в INNER? Чтобы сервер попыхтел лишнего? Это раз.
Два. Условия some_field != 1 и some_field = 1 не являются обратными. Вернее, являются в случае, если some_field в структуре таблицы объявлено как NOT NULL. А структуры вы как-то привести не озаботились.
Три. Вообще-то WHERE NOT IN - самая медленная из возможных реализаций операции вычитания наборов записей. WHERE NOT EXISTS или LEFT JOIN WHERE IS NULL будут гарантированно не медленнее, а скорее всего быстрее. А если версия позволяет, то доступен ещё и EXCEPT.
И последнее. Поле с именем id в подавляющем большинстве случаев первичный индекс, как правило автоинкрементный. И, судя по использованию PRIMARY в планах, так оно и есть. А если так, то связывание вообще не нужно. Достаточно посчитать отдельно записи в таблице 1, отдельно в подзапросе, а потом тупо вычесть. Результат будет тем же, а геморрою серверу ну куда как меньше.
С точки зрения сервера, будь у него разум, такой запрос - изощрённое, тонкое издевательство. Вот зачем вы используете LEFT JOIN, если финальный WHERE превращает их все в INNER? Чтобы сервер попыхтел лишнего?
Да, вы правы. внешнее объединение тут лишнее, сейчас попробовал на сервере и отыгралось еще 0,1 секунды. Спасибо.
Вообще-то WHERE NOT IN - самая медленная из возможных реализаций операции вычитания наборов записей. WHERE NOT EXISTS или LEFT JOIN WHERE IS NULL будут гарантированно не медленнее, а скорее всего быстрее. А если версия позволяет, то доступен ещё и EXCEPT.
Не понял какой рабочий вариант предлагаете.
Поле с именем id в подавляющем большинстве случаев первичный индекс, как правило автоинкрементный. И, судя по использованию PRIMARY в планах, так оно и есть. А если так, то связывание вообще не нужно. Достаточно посчитать отдельно записи в таблице 1, отдельно в подзапросе, а потом тупо вычесть. Результат будет тем же, а геморрою серверу ну куда как меньше.
Вроде написал об этом:
Поэтому было бы идеально решить проблему двумя простыми запросами:
/* Находим общее число записей в table1 */
SELECT COUNT(t1.id)
FROMtable1t1/*
Находим число записей в table1, для которых выполняется условие
в связанной таблице t4.some_field = 1
*/
SELECT COUNT(t1.id)
FROMtable1t1
LEFT JOINtable2t2 ON t2.id=t1.table2_id
LEFT JOINtable3t3 ON t3.id=t2.table3_id
LEFT JOINtable4t4 ON t4.id=t3.table4_id
WHERE t4.some_field = 1и вычесть из общего числа записей число записей по условию прямо в коде.
Хоть комент и полезный но К чему эта токсичность, не понятно
У меня был похожий кейс, но с 2 таблицами, EXPLAIN'ом выяснилось что MySQL 5.7 строит запрос с eq_ref, а MySQL 5.6 берет ref и работает дольше на порядок. Такую разницу давали новые значения optimizer_switch в 5.7, которых не было в 5.6 (вроде favor_range_scan и condition_fanout_filter).
Быстрое решение (до обновления версии) внезапно нашел ChatGPT, который предложил заменить INNER_JOIN на STRAIGHT_JOIN (про этот оператор до этого момента ничего не знал) - и это помогло, EXPLAIN переключился на eq_ref индекс и начал отрабатывать на порядок быстрее.
Другой тип джойна насколько я понял заставляет отключить оптимизатор в части изменения порядка подключения таблиц при объединении и строго использовать тот порядок, что был записан в запросе.
Это не другой тип джойна. Это хинт оптимизатору. Сродни USE/FORCE/IGNORE INDEX.
Аналогично, в своём коде обнаружил, что он выполняет left join в неправильном порядке и поэтому сильно тормозит. Замена на straight_join решила проблему кардинально - с 22 секунд до 0,14 секунд! Но у меня 5 миллионов записей.
Аналогично, в своём коде обнаружил, что он выполняет left join в неправильном порядке и поэтому сильно тормозит. Замена на straight_join решила проблему кардинально .
Видите ли... если вы используете LEFT JOIN, то сканирование таблиц выполняется строго в том порядке, в каком они присутствуют в тексте запроса. Безусловно и без вариантов, и это явно описано в документации. STRAIGHT_JOIN делает абсолютно то же самое для INNER/CROSS JOIN, без этого хинта сервер волен в выборе порядка сканирования (на основании имеющейся статистики).
Обратите внимание - STRAIGHT_JOIN применим только и исключительно к INNER/CROSS JOIN и неприменим к OUTER JOIN и NATURAL JOIN, приводя к ошибке синтаксиса.
Если вы заменили LEFT JOIN на JOIN STRAIGHT_JOIN - то вы вообще-то изменили логику своего запроса. И именно с этим и связано ускорение. Но вы отнюдь не изменили порядок сканирования таблиц.
Так что интерпретация вами фактов крайне сомнительна, я бы даже сказал, что ошибочна. На самом деле надо было просто посмотреть в плане, какой индекс использовался, и при неправильном решении со стороны оптимизатора использовать соответствующий Index Hint (USE/FORCE/IGNORE). А может, было бы достаточно просто пересчитать статистику.
Пробовал менять на INNER JOIN - результат запроса в этом случае получался далеко не тот, который хотелось бы (сильно меньше записей выбирается в этом случае, что логично). А смену порядка выполнения подзапросов я увидел в EXPLAIN, из чего и последовали мои сомнительные выводы.
Во всяком случае вы больше эксперт и вам виднее.
Спасибо. Интересная статья. А какой тип индекса на поле по которому фильтр идёт ? Если там сбалансированное дерево (в постгресе это дефолт), то не совсем понял, как как изменение != на = помогло. Ведь в сбалансированном дереве нам достаточно просто найти 1 и взять множество значений либо слева, либо справа. Далее сделать мерж найденного множества значений с тройным джоином. Это должно всё быстро отрабатывать
Там обычный B-Tree индекс. Думаю дело просто в подавляющей разнице количества записей с 0 и 1. В обоих случаях используется отбор по индексу, просто число записей с 1 очень мало, поэтому и получилось такое ускорение. И даже действительно медленный вариант обора NOT IN не испортил общий результат оптимизации.
Ещё один показатель того, что SQL используется не по назначению.
То что несколько маленьких запросов выполняется быстрее, чем один быстрый правда. Сам заметил это давно.
Так же, как и тот факт (к теме не относится), что в mysql довольно кривые транзакции и одно время их не рекомендовали использовать сами создатели.
Статья интересная, автору спасибо.
Неудобно читать, потому что в разных запросах разные названия таблиц (t1/table1). Также в самом первом EXPLAIN-е два WHERE - это опечатка?
Что касается запроса:
Как заметили выше, левые джоины там не нужны.
Структура БД слегка подозрительная. table1 ссылается на table2, table2 на table3, table3 на table4 по первичному ключу. Кажется, что в такой ситуации в каждой последующей таблице должно быть не больше записей, чем в предыдущей, но из explain-ов видно, что в table1 проверяется 475k записей, а в table4 их 22M. Откуда лишние записи? Не стоит ли разделить какую-то из таблиц на несколько или просто почистить?
Необязательно писать в селекте именно ту таблицу, из которой нужно выбрать данные. Например, если, скажем, в какой-нибудь из таблиц в цепочке мало записей, можно попробовать джойнить остальные таблицы к ней.
Также в самом первом EXPLAIN-е два WHERE - это опечатка?
Да, опечатка. Исправил, спасибо.
Структура БД слегка подозрительная. table1 ссылается на table2, table2 на table3, table3 на table4 по первичному ключу. Кажется, что в такой ситуации в каждой последующей таблице должно быть не больше записей, чем в предыдущей, но из explain-ов видно, что в table1 проверяется 475k записей, а в table4 их 22M. Откуда лишние записи? Не стоит ли разделить какую-то из таблиц на несколько или просто почистить?
На table4 ссылается много разных таблиц, поэтому в ней больше записей, чем в table1.
Необязательно писать в селекте именно ту таблицу, из которой нужно выбрать данные. Например, если, скажем, в какой-нибудь из таблиц в цепочке мало записей, можно попробовать джойнить остальные таблицы к ней.
Не очень понял, что предлагается, учитывая описанные в статье ограничения: оптимизированный запрос формируется программно и может являться частью другого запроса.
Ускорение запроса MySQL с помощью обратного условия