Оптимизация запросов 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

MySQL — проблема запросов с limit и offset к очень большим таблицам и ее решение

Представим, что у нас есть таблица пользователей (users) или заказов (orders) на десятки миллионов записей и больше. И у нас стоит задача их перебора в любом контексте.

Я представляю себе это в виде цикла, в котором порционно забираем разумное количество записей, что-то вроде:

select * from users order by id limit 1000 offset 0;

Такой запрос выполняется очень быстро, моментально.

Рано или поздно мы приходим к offset, например 40 млн:

select * from users order by id limit 1000 offset 40000000;

И тут запрос может выполняться несколько минут.

Происходит это из-за того, что mysql не может гарантировать, что все элементы будут на своих порядковых местах, ведь может так статься, что элемент 40 000 099 был ранее удален и алгоритмам приходится сканировать все 40 млн элементов для получения выборки.

Решением этой проблемы может стать видоизменение запроса из limit-offset на where-limit:

 select * from users where id > 40000000 order by id limit 1000;

Выполняется моментально!

И даже, если не все элементы гарантированно присутствуют в таблице, айдишник для where легко «вытащить» из предыдущего запроса в цепочке.