Как оптимизировать MySQL & quot; Порядок по лимиту 1 & quot; в запросах, которые объединяют несколько таблиц?
Итак, у меня есть такой запрос:
SELECT tablea.name, tablea.views from tablea inner
join tableb on (tablea.id = tableb.id and tablea.balance > 0)
order by tablea.views asc limit 1
Однако проблема в том, что когда я запускаю его, он работает довольно медленно (4+ секунды).
Интересно, что при удалении предложения order by при сохранении лимита 1 он выполняется за 0,005 секунды (приблизительно).
Еще интереснее: когда я не присоединяюсь к tableb, т.е.
SELECT tablea.name, tablea.views from tablea
where tablea.balance > 0
order by tablea.views asc limit 1
Запрос выполняется обычно за 0,005 секунды.
Заметки:
- Представления столбцов в таблице проиндексированы
- tablea и tableb имеют отношение 1 к 1 с точки зрения id и примерно имеют одинаковое количество строк.
Почему между первым запросом, первым запросом, когда убрана команда «упорядочить по», и вторым запросом существует такая резкая разница в производительности?
Будет ли в любом случае сделать сортировку намного быстрее при соединении двух таблиц?
Решение
Одно из возможных объяснений того, что здесь происходит, заключается в том, что MySQL выбирает порядок до это делает фактическое соединение. Как вы видели в исходном запросе при удалении ORDER BY предложение само по себе не является проблемой производительности. Один из способов обойти это — заключить исходный запрос в подзапрос, а затем упорядочить его:
SELECT *
FROM
(
SELECT tablea.name,
tablea.views
FROM tablea
INNER JOIN tableb
ON tablea.id = tableb.id AND
tablea.balance > 0
) t
ORDER BY t.views ASC
LIMIT 1
Если это работает, то это, вероятно, подтверждает то, что я предположил. В этом случае подзапрос заставляет MySQL упорядочивать только те записи, которые являются результатом фактического подзапроса. В любом случае, вы должны привыкнуть бегать EXPLAIN на такие запросы. Я предполагаю, что индекс не используется / эффективен при объединении в исходном запросе.
Другие решения
Given INDEX(x)
ORDER BY x LIMIT 1
Будет удобно использовать указатель и забрать первый товар
Given INDEX(x)
WHERE ...
ORDER BY x LIMIT 1
Может также использовать индекс, и надежда что какой-то ранний ряд удовлетворен WHERE, Если нет, то, возможно, придется сканировать всю таблицу, чтобы найти одну строку!
Given INDEX(a, x)
WHERE a = 12
ORDER BY x LIMIT 1
Нет проблем — поищите в индексе a = 12; выбрать первый пункт
Given INDEX(a, x)
WHERE a > 12
ORDER BY x LIMIT 1
Теперь индекс не так хорош. Потребуется собрать все строки с> 12, отсортировать по x, затем поставить одну строку.
В общем, если WHERE, а также ORDER BY может быть полностью удовлетворен, то LIMIT n можно оптимизировать. (Это предполагает, что нет GROUP BY, или GROUP BY а также ORDER BY являются идентичный.)
Это с одним столом. Когда ты JOIN две (или более) таблицы, это становится грязнее. С двумя таблицами оптимизатор выбирает одну таблицу, находит там все, что может, а затем выполняет соединение с вложенным циклом к другой таблице.
Обычно (не всегда), WHERE предложение (на одном столе) говорит оптимизатору «забери меня». Если это та же таблица, что и ORDER BY, тогда вышеупомянутое обсуждение может вступить в силу.
Без WHERE Как правило, оптимизатор начинается с таблицы меньшего размера. (Примечание: размер таблицы зависит от строки оценки и не всегда может быть правильным.)
Ваш первый запрос может быть ускорен с помощью WHERE EXISTS ( ... tableb ... ) вместо JOIN tableb..., Оптимизатор посчитает это чем-то стоящим для оптимизации.
И т. Д. И т. Д.
Обратите внимание, что ваши «0,005 секунды» были «удачей».
Если вы хотите копать глубже, предоставьте SHOW CREATE TABLE (чтобы мы могли видеть индекс (ы) и т. д.), EXPLAIN SELECT (чтобы мы могли обсудить, что решил Оптимизатор) и, если возможно, EXPLAIN FORMAT=JSON SELECT ... Больше подробностей. Также см моя индексирующая кулинарная книга .