MySQL достаточно быстрый сервер баз данных. Во многих ситуациях он превосходит PostgreSQL и другие решения по производительности. Однако, любой даже самый быстрый сервер можно загрузить никому ненужной работой из-за неправильно написанного SQL запроса или неподходящей структуры таблиц. Рассмотрим актуальные на 2018 год способы оптимизации SQL запросов для MySQL сервера. В данной статье будут рассмотрены довольно очевидные вещи, которые можно затюнить без применения анализатора запросов Explain.
Работа с базами данных требует осмысленного подхода, разработчику следует учитывать многие аспекты взаимодействия с базами данных, иначе есть риск загрузить даже самый производительный сервер. MySQL хорош тем, что он из коробки достаточно быстр на чтение данных из таблиц помещающихся в оперативную память. Однако, неумелое составление запросов сильно понижает производительность запросов.
Используйте в качестве движка таблиц InnoDB
Уже много лет именно этот движок в MySQL и MariaDB является табличным движком по-умолчанию. Однако, с ним нужно правильно работать, т.к. он имеет свои особенности. Одной из таких особенностей является Clustered index. Кластерный индекс представляет из себя внутренний формат организации файла с таблицей данных. Благодаря такому устройству таблица InnoDB является большим B-TREE индексом отсортированным по значению Clustered index.
В качестве значения Clustered index может выступать любой уникальный идентификатор записи: PRIMARY KEY таблицы, а если он отсутствует, то первый UNIQUE KEY. Если уникальные индексты отсутствуют в таблице, тогда InnoDB то использует внутренний 6-тибайтный код.
Какие особенности имеет Clustered index в InnoDB?
Вставка записей в произвольное место clustered index будет медленнее, чем вставка в конец таблицы, так как нужно перестраивать B-TREE индекс и сам файл с данными. Если вы используете AUTO_INCREMENT, не стоит беспокоиться об этом. Однако, при использовании фрагментированного первичного ключа могут возникнуть проблемы с производительностью вставки.
Обновление значения clustered index у записи в таблице приводит к физическому перемещению информации на диске или к её фрагментации. Все вторичные индексы ссылаются на clustered index, благодаря чему ключ вида KEY key (a, b, c) будет иметь структуру KEY key (a, b, c, clustered_index). Именно этот факт позволит проводить некоторые оптимизации запросов.
Обработка данных с учётом Clustered index
Напомню, что InnoDB располагает свои данные на диске в порядке значения Clustered index. Поэтому обработка большого количества строк в одном запросе будет в десятки раз быстрее, если обрабатывать строки в том порядке, в котором они хранятся на диске. Это касается всех операций, в том числе: SELECT, INSERT и UPDATE. Иначе говоря, если в запросах используются конструкции вроде WHERE pk IN ($ids) и им подобные, убедитесь, чтобы значения были заранее отсортированы!
Правильное использование OFFSET и LIMIT
SELECT id FROM table LIMIT 1000000, 10;
Казалось бы достаточно быстрый запрос, на первый взгляд. Однако, как и в случае с ORDER BY RAND(), запросы с OFFSET начнут тормозить при больших размерах таблицы, даже если требуется выбрать всего 10 записей.
Дело в том, что прежде чем, выбрать требуемое количество записей указанных в LIMIT, MySQL выберет все записи, указанные в OFFSET. Чем больше указан OFFSET, тем медленней будет запрос. В идеале лучше вообще не использовать OFFSET. Просто LIMIT можно использовать на любых объёмах данных.
Обойти эту проблему опять таки можно с помощью индекса, и лучше с помощью первичного. Чтобы выбрать следующую пачку или страницу с записями используйте подобные запросы:
SELECT id FROM table WHERE id > $x LIMIT 10;
Где $x, максимальное значение id из прошлой страницы с данными. Однако, такое решение подойдёт, если данные нужно отобразить в порядке их первичного ключа.
Либо так:
SELECT * FROM table WHERE id > $x * $y - 1 LIMIT $x;
Здесь $x это лимит результатов на страницу, а $y — номер текущей страницы.
Если же всё-таки нужна сложная выборка и сложная сортировка с постраничным выбором данных, то запрещайте не привилегированным пользователям генерировать запросы с большим значением OFFSET. Иначе открывается потенциальная возможность задудосить ваше приложение.
Использование OFFSET и LIMIT с Cluster Index
Если ты крутой мачо-мэн и всё-таки используешь InnoDB, то предложу такой вариант оптимизации запроса SELECT LIMIT OFFSET с помощью вторичного индекса. Делается это в два запроса. Первый:
SELECT id FROM table WHERE status = 1 ORDER BY added_at LIMIT 20 OFFSET 100000;
При этом в таблице имеется первичный ключ id и вторичный ключ status + added_at, в который на уровне движка добавляется ещё и + id. Благодаря вторичному индексу и попаданию в него всех полей из запроса MySQL получит данные из вторичного индекса без чтения данных с диска, так как индекс находится в памяти на момент выполнения запроса, такой эффект называется covering index.
Вторым запросом достаточно сделать:
SELECT * FROM table WHERE message_id id ($ids);
Где $ids список id, полученных в первом запросе.
Таким образом можно делать достаточно глубокие OFFSET в десятки тысяч значений, а время выполнения запроса будет находиться в пределах нескольких секунд, тогда как простой запрос с LIMIT OFFSET может занимать до 30 секунд! Главное преимущество подхода с двумя запросами в том, что он одинаково быстро работает как при холодном старте, так и при прогретых кэшах файловой системы и MySQL-сервера.
Index merge для запросов с OR условиями
В MySQL есть алгоритм использования нескольких индексов (index merge), однако работает он далеко не во всех случаях и только без ORDER BY в запросе. Об этом механизме достаточно мало информации, но суть такова: если в условии WHERE разные столбцы сравниваются с помощью OR, то лучше иметь отдельные индексы для этих полей. Во всех остальных случаях нужно использовать составные индексы.
SELECT COUNT(*) FROM table WHERE id = 1 OR value = 1;
Как думаете, какой индекс для такого запроса будет самым эффективным?
Составной индекс KEY (id, value) в данном случае будет использован только частично. Для Более оптимального выполнения запроса с OR условиями лучше создать индексы так: KEY (id), KEY value (value).
Не используйте COUNT(*) для больших таблиц
Что из себя представляет операция COUNT(*)? Получение всех строк соответствующих условию и их подсчёт. Фактически то же самое, что и с LIMIT OFFSET. Если количество строк небольшое, то COUNT(*) отработает быстро. Однако, если запросу придётся посчитать миллионы строк со сложным фильтром, то такой запрос может занять минуты и даже часы.
По сути, не так много задач, где пользователь реально хочет знать точное количество строк по заданному критерию. Исключение составляют аналитические задачи, но для таких целей больше подходят специализированные аналитические СУБД, например, Clickhouse или Vertica.
Если же всё-таки пользователь хочет знать некоторое приблизительное количество строк найденных для его запроса, лучше воспользоваться обходным путём:
EXPLAIN SELECT * FROM table FORCE INDEX(value_idx) WHERE value > 50;
В результате будет что-то подобное:
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+ | 1 | SIMPLE | table | range | value_idx| value_idx| 4 | NULL | 814222 | Using where | +----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+
Колонка rows содержит оценочное число строк, которые нужно просмотреть для запроса, в данном случае это число 814 222. Оно может отличаться от реального количества. Если в запросе используются подзапросы или JOIN, то EXPLAIN вернёт несколько строк, по строке на каждое объединение таблиц. Для вычисления примерного количества записей нужно перемножить значения rows всех возвращённых строк.
Если же всё-таки хотите COUNT() и ничего другого, то убедитесь, что в качестве поля для подсчёта используется звёздочка, либо поле входящее в индекс по которому был выполнен запрос. В противном случае MySQL будет делать дополнительные чтения с диска, вместо использования покрывающего индекса. Также помните, что при указании имени столбца будет возвращено не общее количество строк, только тех, значение которых IS NOT NULL.
Оптимизация GROUP BY запросов
В алгоритме выполнения запросов с GROUP BY есть один маленький нюанс. MySQL-сервер всегда будет сортировать результат запроса содержащего GROUP BY. При большом количестве строк, эта сортировка может занять дополнительное время из-за сортировки строк по полю группировки во временном файле на диске. Однако, если такая сортировка не нужна, от неё можно отказаться дописав в запрос ORDER BY NULL.
Операции над полями в условиях
Весьма редко встречающаяся ситуация, но о которой полезно знать:
SELECT id FROM table WHERE count * 2 = $x;
Что будет происходить в данном запросе? Значение поля count всех строк будет умножено на 2. Т.е. на каждую строку будет выполнятся дополнительная математическая операция. Этого можно избежать так:
SELECT id FROM table WHERE count = $x / 2;
В данном случае значение $x / 2 вычисляется один раз и используется для всех проверяемых строк таблицы. Однако, оптимизация сработает только если значение $x константно. Если вместо него будет использоваться поле из таблицы, то оно будет рассчитываться для каждой строки.
Операции над проиндексированными полями
Принцип действия похож на предыдущий пример.
SELECT id FROM table WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(added_at) <= 10;
В данном случае если у поля added_at имеется индекс, то он будет проигнорирован, т.к. в условии над этим полем происходит модификация и нужно сканировать всю таблицу. Решается эта проблема инвертированием вычисляемого значения :
SELECT id FROM table WHERE added_at>= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
Таким способом будет получен выигрыш производительности за счёт использования индекса. а так же из-за вычисления значения DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) один раз, т.к. в нём нет меняющихся значений.
Альтернативные варианты для ORDER BY RAND()
SELECT id FROM table ORDER BY RAND() LIMIT 1;
На тему выбора случайных записей из базы данных можно писать отдельные статьи, т.к. способов решения этой задачи великое множество, но каждый из них имеет свои достоинства и недостатки. Так в чём проблема ORDER BY RAND()? Это крайне медленный запрос, т.к. сначала сортирует случайным образом все подходящие строки, после чего выбирает из них необходимое количество. Чем больше размер таблицы, тем большее количество времени займёт случайная сортировка записей.
Оставлять ORDER BY RAND() в коде стоит только в крайне редких случаях, если вы на 100% уверены в том, что размер таблицы никогда не вырастет более нескольких тысяч строк. Если есть время, то лучше сразу избавиться от этого слабого места в производительности, которое обязательно напомнит о себе при увеличении размера таблицы.
Все самые быстрые способы получения случайного значения из базы данных так или иначе должны использовать какое-либо проиндексированное поле. Например, можно добавить в таблицу дополнительное поле rand с типом INT(11) UNSIGNED NOT NULL. И при добавлении каждой записи генерировать для неё случайное значение в заранее обозначенном диапазоне.
При выборе диапазона стоит учитывать сколько случайных записей максимально нужно будет получать за раз и насколько часто должны повторяться случайные записи. Например, для таблицы из 1 000 записей можно поставить rand(0, 9). Тогда для каждого случайного числа будет в среднем по 100 записей.
SELECT id FROM table WHERE rand = 3 LIMIT 10;
Если же нужно выбирать только одно случайное значение, при условии что в таблице есть первичный числовой ключ не имеющий пропусков, то можно воспользоваться таким запросом:
SELECT * FROM table WHERE id = $rnd;
Где $rnd будет значением запроса:
SELECT MAX(id) FROM table;
Но этот вариант не подойдёт для тех, кто использует встроенный механизм AUTO_INCREMENT в MySQL, т.к. он не гарантирует, что все значения будут идти подряд без пропусков.
Пользовательские сессионные переменные
Некоторые сложные запросы можно сильно ускорить благодаря использованию пользовательских переменных, а некоторые задачи вообще невозможно решить в рамках реляционной модели данных. На эту тему у меня есть отдельная статья.
Множественная вставка данных
Достаточно часто бывают ситуации, когда в одну таблицу нужно вставить более одной строки. Вместо того, чтобы делать по INSERT запросу на каждую строку можно объединить данные в одном запросе:
INSERT INTO table (key, value) VALUES (key1, value1), (key2, value2), ..., (keyN, valueN);
Главная экономия заключается в том, что все данные передаются в одном запросе по сети. Этот особенно заметно, если MySQL-сервер находится в другом датацентре и пинг до него более десятка или сотни миллисекунд.
Ещё одно ускорение происходит из-за того, что ставка происходит как одна атомарная транзакция в результате чего индексы перестаиваются для всего набора строк, а не после добавления каждой записи. Однако, есть и обратная сторона. Скорость вставки может начать постепенно падать при превышении некоторого числа записей в одном запросе. Проще говоря:
- Скорость вставки 1 записи может быть 0.01 сек
- Скорость вставки 10 записи может быть 0.05 сек
- Скорость вставки 100 записи может быть 0.20 сек
- Скорость вставки 1 000 записи может быть 0.05 сек
- Скорость вставки 2 000 записи может быть 0.2 сек
Т.е. при вставке до 1 000 записей одним запросом скорость вставки одной записи из пачки может расти, а при вставке более 1 000 записей за раз скорость вставки одной записи может начать падать. 1 000 здесь весьма условное число, для каждой базы данных и таблицы это число нужно подбирать индивидуально.
Вставка данных внутри транзакции
….
Множественное обновление строк таблицы
Здесь рассмотрим 2 случая. Если нужно обновить несколько строк одним и тем же значением, то стоит использовать такой запрос:
UPDATE table SET value='new' WHERE id IN (1, 2, 3);
Если же нужно для нескольких элементов задать разные значения, то:
UPDATE table SET value= CASE WHEN id = 1 THEN 'value1' WHEN id = 2 THEN 'value2' WHEN id = 2 THEN 'value3' END WHERE id IN (1, 2, 3);
Опять же ускорение такого запроса происходит благодаря передачи всех данных в одном запросе и выполнения обновления в одной атомарной операции.
Обновление данных внутри транзакции
….
INSERT, UPDATE и DELETE малыми порциями
Все операции, изменяющие содержимое файлов базы данных крайне медленные. Запись данных на диск гораздо медленнее чтения. Поэтому не стоит делать операций по вставке, модификации или удалению данных, затрагивающих большое количество строк в таблице.
UPDATE table SET value='new' WHERE id < 100000;
Подобный запрос очень плох с точки зрения производительности. Он как минимум за одну операцию попытается изменить значение 100 000 строк. Такой запрос может занять в зависимости от нагрузки и производительности сервера в районе минуты времени. В добавок всё это время запросы, выполняющиеся другими пользователями начнут выполняться значительно медленнее.
Оптимальным решением будет добавление LIMIT в запрос:
UPDATE table SET value='new' WHERE id < 100000 LIMIT 5000;
Значение LIMIT нужно подбирать экспериментальным путём так, чтобы запрос выполнялся не более нескольких секунд. Повторив такой запрос нужное количество раз — все необходимые данные будут удалены.
Точно также нужно поступать и с операцией DELETE:
DELETE FROM WHERE id < 100000 LIMIT 5000;
Таким образом, выполнение нескольких маленьких запросов не оказывает значительной нагрузки на сервер и других пользователей, но и в некоторых случаях может занять меньшее количество времени, чем один большой запрос затрагивающий больше количество данных.
Использование большого количества JOIN
Здесь тоже есть два варианта:
- все JOIN таблицы связаны 1-к-1
- присутствуют связи 1-ко-многим
SELECT * FROM table t1 JOIN table2 t2 ON t2.id = t1.id JOIN table3 t3 t3.id = t2.value JOIN table4 t4 t4.id = t3.value JOIN table5 t5 t5.id = t4.value ;
В первом варианте, когда все связи таблиц объединяют 1 и только 1 строку всё не так уж плохо по производительности. При условии, что объединение таблиц происходит по проиндексированным полям. В примере выше запрос отработает оптимально при наличии индексов на полях: t2.id, t3.id, t4.id и t5.id.
Также старайтесь делать объединение таблиц только по столбцам с абсолютно идентичными типами данных, в противном случае будут дополнительные потери на преобразовании данных к одному типу. (но это не точно)
Второй вариант, когда одна и более таблица будет связана с другой более чем одной записью. В данном случае количество строг в результате запроса будет расти в геометрической прогрессии. В таком случае имеет смысл разбить запрос на несколько более маленьких и простых запросов.
Какой профит принесёт разбиение джоинов на отдельные маленькие запросы? Во-первых, это увеличит читаемость кода.
SELECT * FROM table t1 WHERE id = 1; SELECT * FROM table2 WHERE id = $id; SELECT * FROM table3 WHERE id = $id;
Согласитесь, в таком виде запросы более понятны. Во-вторых, такие маленькие запросы проще кэшировать и переиспользовать кэш. В таком виде каждое значение из каждой таблицы можно закэшировать отдельно. При кэшировании с джойнами одни и те же значения будут повторяться в кэше многих запросов, что будет транжирить оперативную память.
Попробуйте оба варианта и выберите оптимальный.
Использование ON DUPLICATE KEY UPDATE
$row = query('SELECT * FROM table WHERE id = 1;');
if($row) {
query('UPDATE table SET column = column + 1 WHERE id = 1;')
} else {
query('INSERT INTO table SET column = 1, id = 1;');
}
Такую конструкцию можно встретить, когда разработчик хочет вставить новую запись в таблицу, а если она уже существует, то изменить значение этой записи. Однако, гораздо эффективнее можно это сделать с помощью одного запроса:
INSERT INTO table SET column = 1, id = 1 ON DUPLICATE KEY UPDATE column = column + 1;
ON DUPLICATE KEY UPDATE сработает в том случае, если в таблице имеется либо первичный, либо уникальный ключ.
Использование REPLACE вместо INSERT + UPDATE
Оператор REPLACE работает аналогично INSERT, но если в таблице присутствует запись с таким же значением индекса UNIQUE или PRIMARY KEY, что и новая, то старая запись будет удалена, а новая добавлена.
REPLACE INTO table SET id = 1, status = 1, value = 7;
Преимущество запросов REPLACE и ON DUPLICATE KEY UPDATE в том, что проверка на существование и операция с данными выполняются одним запросом, что немного сокращает накладные расходы по сетевому взаимодействию. Использовать REPLACE менее предпочтительно, так как удаление записи и добавление новой записи будет сильно медленнее, чем обновление записи. Это также приведёт к генерации нового значение AUTO_INCRENENT для данной строки. Также использовать REPLACE стоит в том случае, если нужно осознанно стриггерить событие ON DELETE CASCADE.
Выборка всех полей SELECT * FROM table
Довольно частая рекомендация. Суть её заключается в том, что в каждом SELECT нужно запрашивать только те поля, которые реально будут использоваться в приложении. Зачем это нужно? Перечисляя только нужные поля сервер отдаёт меньше данных, отбрасывая незапрошенные поля из ответа. Это даёт меньшую нагрузку на сеть, и ускоряет время доставки ответа клиенту.
Даже если в запросе нужно получить все поля, гораздо безопаснее явно их перечислить. Ведь в любой момент структура таблицы может измениться, могут появиться новые поля, в результате чего скорость работы приложения начнёт деградировать. Однако это не значит что нужно бросать все дела и переписывать все SELECT * запросы. Реальную пользу от такой оптимизации можно получить только при большом количестве запросов, либо при очень больших ответах измеряемых мегабайтами.
В противном случае есть риск только потратить время без получения выигрыша в производительности, подвергая при этом код изменениям и вероятности сломать некоторые запросы. У перечисления всех полей в запросе есть одно преимущество — возможность узнать какие поля есть в таблице без заглядывания в неё.
Однако, лучше не допускать ситуаций, когда на каждый набор полей будет создаваться по новому запрос. Поддерживать десяток запросов различающимся только набором полей крайне плохая идея. Для ситуаций, когда нужно вытаскивать из базы значения для одной сущности — использование SELECT * FROM table WHERE id = x вполне допустимо.
Если в проекте есть механизм кэширования, например, APCU, Memcache или Redis, то чрезмерное количество вариантов запросов к одной таблице неоправданно раздует объём оперативной памяти требуемой под кэш. Возможно для части запросов вашего приложения кэширование вообще не нужно, т.к. MySQL весьма шустро выдаёт результаты InnoDB таблиц влезающих в оперативную память. В данном случае дополнительная прослойка в виде кэша будет только потреблять память, не давая выигрыша в производительности.