- Одна из распространённых причин медленных запросов — обработка лишних данных
Это может быть запрос лишних строк из большой таблицы, где не все они нужны. Можно использовать LIMIT или ограничивать выборку WHERE
SELECT * FROM huge_table LIMIT 10; -- или SELECT * FROM huge_table WHERE id BETWEEN 0 AND 1000;
Это может быть запрос всех столбцов в таблице с большим их количеством или в нескольких таблицах, соединенных JOIN. Лучше, запрашивать только необходимые столбцы
SELECT id, name, email FROM users LIMIT 1000;
Это может быть повторный запрос за теми же данными. В этом случае, если не получается избежать, стоит кешировать результаты и использовать данные из кеша
- Другая причина медленных запрсов — анализ лишних данных
Необходимо минимизировать этот параметр. Например, запрос по индексируемому столбцу анализирует очень ограниченный набор данных, а по этому же столбцу без индекса — может анализировать все строки таблицы. Простейшее решение будет добавление или оптимизация индексов (ссылка на статью)
Каким образом и сколько данных анализируется, можно посмотреть используя команду EXPLAIN и строка rows в результатах
- Эффективным способом ускорения сложного запроса может быть разбиение его на более простые, которые выполняются быстрее первоначального
-- Было SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag_name = ‘mysql’; -- Стало SELECT * FROM tag WHERE tag_name = ‘mysql’; SELECT * FROM tag_post WHERE tag_id = 1234; SELECT * FROM post WHERE id IN (123, 456,789);
- Или разбиение очень длительного запроса на более короткие порции
-- Было DELETE FROM messages WHER created < DATE_SUB(NOW(), INTERVAL 3 MONTH); -- Стало DELETE FROM messages WHER created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 1000; -- несколько раз
- Оптимизатор MySQL плохо оптимизирует корректированные подзапрос вида
SELECT * FROM films WHERE id IN (
SELECT film_id FROM film_actors WHERE actor_id = 100
);
В этом случае, лучше разделить и связать их на бекенде
SELECT film_id FROM film_actors WHERE actor_id = 100; -- И затем SELECT * FROM films WHERE id IN (1,5,9,13,77);
- Не стоит злоупотреблять сортировкой ORDER BY, так как, без индекса на сортируемом столбце, она может значительно увеличить время выполнения запроса. Можно использовать LIMIT для ускорения сортировки, если она необходима
- Запросы с UNION часто идет с ограничением LIMIT, и для ускорения, следует добавить LIMIT в оба под-запроса
-- Было (SELECT id, name FROM film_actors order by name) UNION ALL (SELECT id, name FROM theatre_actors order by name) LIMIT 20; -- Стало (SELECT id, name FROM film_actors order by name LIMIT 20) UNION ALL (SELECT id, name FROM theate_actors order by name LIMIT 20) LIMIT 20;
- Запросы с LIMIT и OFFSET на больших таблицах плохо работают с большими OFFSET, а это часто встречается в пагинации
Например для:
SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 40000;
Сервер сгенерирует 40100 строк и отбросит первые 40000
Стоит заменить на:
SELECT * FROM users WHERE id > 40000 ORDER BY id LIMIT 100;
- Ну и на последок, на бекенде часто используется ORM для генерации запросов. И нужно понимать, что для сложных запросов ORM формирует неоптимальный медленный запрос. В таком случае, следует переписать ORM запрос на чистый SQL
