Оптимизация запросов MySQL — ускоряем запросы

• Одна из распространённых причин медленных запросов — обработка лишних данных

Это может быть запрос лишних строк из большой таблицы, где не все они нужны. Можно использовать 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