Оптимизация запросов 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 — производительные индексы

Индексы (или ключи) — это специальные структуры данных, которые использует подсистемы хранения для ускорения нахождения строк.

  • Индексы бесполезны, и, даже, вредны, если данных в таблицы мало (тысячи или меньше строк)
  • Если данных много (десятки тысяч строк и больше) и есть запрос WHERE по одному столбцу (или же сортировка ORDER BY по этому столбцу), который выполняется часто, лучше «повесить» на него простой индекс
  • Индекс хорошо работают по полному значению, диапазону значений
  • Составной индекс включает набор индексов (например, по трем столбцам A + B + C)
  • В это случае, индекс хорошо работает по полному набору проиндексированных столбцов («A + B + C»), по префиксам (левой части индекса: «A» или «A + B») и НЕ работает по пост-фиксам (правой части индекса: «C» или «B + C»). В случае необходимости поиска по «B» или «B + C», можно создать отдельный индекс — «B + C». Опять таки, индекс «B + C» будет неэффективен при поиске по C.
  • Альтернативой при существующем индексе «A + B + C» и необходимости поиска по пост-фиксу индекса, может быть включение в поиск столбца A искусственным образом:
SELECT * FROM TABLE WHERE B=2 AND C=3;
-- преобразуем в:
SELECT * FROM TABLE WHERE A="2022-10-14" AND B=2 AND C=3;
  • Так же, необходимо рассмотреть возможность смены порядка следования столбцов в запросе WHERE. Если, имеем индекс «A + B + C», при этом наш запрос выглядит так:
-- было:
SELECT * FROM TABLE WHERE A = 1 AND C = 2 AND B = 3;
-- стало:
SELECT * FROM TABLE WHERE A = 1 AND B = 3 AND C = 2;
  • Нужно понимать, что, если в запросе есть поиск по одному столбцу и сортировка по другому, хорошо подойдет составно индекс по этим столбцам и плохо будет работать отдельный индекс по одному из столбцов
  • В случае индексов на столбец строкового типа, индекс хорошо работает по полному совпадению и при поиске по префиксу (WHERE column LIKE «abc%»). При это у индекса есть ограничения, и по длинной строке поиск будет работать плохо
  • Чем меньше памяти потребляет тип столбца, тем быстрее работает и индекс. Поэтому поиск по индексированному целочисленному столбцу гораздо быстрее, чем по строковому
  • В случае длинных строк, и необходимости поиска по ним, можно ввести индексируемый столбец с хешем по длинной строке.

Хороший пример данных, это столбец с адресом веб-страниц url и поддержка столбца  c хешем этой строки — url_hash. При сохранение данных, записываем в столбец url оригинальный адрес веб-страницы, а в url_hash — хеш строки этого адреса (например кодировкой CRC32 или другой кодировкой в цифровое значение). А при поиске ведем поиск только по индексированному url_hash.

-- долгий запрос:
SELECT * FROM TABLE WHERE url = "https://long-adress-url.com";
-- заменяем на:
SELECT * FROM TABLE WHERE url_hash = CRC32("https://long-adress-url.com");
  • Другой частый случай плохо-индексируемых данных — это IP-адреса. Для них, так же есть специальный функции на бекенде, которые превращают строку IP-адреса в числовой тип для отдельного индексируемого столбца.

Конкретные функции зависят от языка программирования. Например, для PHP подойдет функция ip2long для шифровки и long2ip для расшифровки

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 легко «вытащить» из предыдущего запроса в цепочке.