40 стратегий оптимизации производительности MySQL
Общая оптимизация запросов
Начнем с общей оптимизации запросов. Многие из этих рекомендаций могут быть знакомы, но используете ли вы их на практике. Надеюсь, после прочтения этой статьи вы сможете выработать хорошие привычки.
Подписывайтесь на телеграм-канал usr_bin, где я публикую много полезного по Linux, в том числе ссылки на статьи в этом блоге.
Выберите подходящие типы данных и наборы символов
Использование соответствующих типов данных может сократить объем хранилища и повысить скорость запросов. Когда объем данных достигает определенного размера, разница становится весьма заметной.
Пример: Для логических значений используйте TINYINT(1)вместо CHAR(1). Например, если есть поле, представляющее статус:
CREATE TABLE users (
is_active TINYINT(1)
);Для таблиц, в которых хранится только английский текст, используйте latin1вместо utf8mb4.
CREATE TABLE messages (
content VARCHAR(255) CHARACTER SET latin1
);Избегайте использованияSELECT *
Выбирайте только необходимые столбцы, чтобы сократить объем передачи данных.
Пример: вместо использования SELECT *укажите имена столбцов явно.
SELECT id, name, email FROM users;
Используйте JOIN и избегайте подзапросов
Минимизируйте количество операций JOIN, чтобы уменьшить размер набора данных.
Пример: оптимизируйте условия объединения и убедитесь, что столбцы объединения индексированы.
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';
По возможности используйте JOIN или EXISTS вместо подзапросов.
Пример: Избегайте использования подзапросов и вместо этого используйте JOIN.
SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;
Используйте UNION вместо OR и оптимизируйте использование ORDER BY и GROUP BY
Убедитесь, что столбцы, используемые в ORDER BY и GROUP BY, имеют индексы.
Пример: добавьте индексы к столбцам, используемым для сортировки и группировки.
CREATE INDEX idx_order_date ON orders (order_date); SELECT * FROM orders ORDER BY order_date;
Если позволяет бизнес-логика, используйте UNION вместо OR.
Пример: замените запрос с условиями OR, используя UNION с двумя отдельными запросами.
SELECT id, name FROM users WHERE status = 'active' UNION SELECT id, name FROM users WHERE status = 'pending';
Избегайте использования запросов LIKE, начинающихся с%
Избегайте использования запросов LIKE, начинающихся с %, поскольку они не могут использовать индексы.
Пример: используйте полнотекстовый поиск вместо LIKE '%keyword%'. Другими словами, поместите %в конец строки.
SELECT * FROM products WHERE description LIKE 'keyword%';
Вы могли заметить, что многие поисковые запросы в разных системах возвращают результаты только тогда, когда вы вводите начало слова; если вы вводите слово из середины, результаты не возвращаются. Это основано на том же принципе.
Используйте пакетные вставки для оптимизации операций INSERT
Используйте пакетные вставки для оптимизации операций INSERT и снижения накладных расходов на отдельные операции вставки.
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');При выполнении пакетных вставок отключите проверки уникальности и обновления индекса и включите их снова после завершения вставки.
SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; -- Операции пакетной вставки SET unique_checks=1; SET foreign_key_checks=1; COMMIT;
Используйте кэширование запросов
Используйте кэширование запросов, чтобы сократить накладные расходы на повторяющиеся запросы.
SET GLOBAL query_cache_size = 1048576; SET GLOBAL query_cache_type = ON;
Избегайте использования HAVING вместо WHERE
По возможности используйте для фильтрации WHERE вместо HAVING.
Пример: избегайте фильтрации с помощью HAVING.
SELECT user_id, COUNT(*) FROM orders WHERE order_date > '2020-01-01' GROUP BY user_id HAVING COUNT(*) > 1;
Настройка параметров конфигурации
В этом разделе поговорим о настройке MySQL. Хотя это больше относится к эксплуатации, знакомство с конфигурацией MySQL тоже важная область знаний для команды разработчиков.
Параметр innodb_buffer_pool_size
innodb_buffer_pool_sizeодин из важных параметров конфигурации InnoDB, используемый для указания размера пула буферов InnoDB. Пул буферов используется для кэширования страниц данных, индексных страниц и другой информации InnoDB. Правильная настройка этого параметра может существенно повлиять на производительность базы данных.
Увеличение размера пула буферов InnoDB повышает частоту попаданий в кэш.
SET GLOBAL innodb_buffer_pool_size = 2G;
Важно отметить, что это значение не будет лучшим, когда оно больше. innodb_buffer_pool_size следует установить как можно большим, при этом гарантируя, что для операционной системы и других приложений останется достаточно памяти. Обычно, рекомендуется устанавливать его на уровне 60–80 % от оперативной памяти на выделенном сервере базы данных. Отслеживая производительность базы данных и использование памяти, вы можете дополнительно настроить этот параметр для оптимизации производительности базы данных.
query_cache_size используется для указания размера кэша запросов. Кэш запросов может хранить результаты запросов SELECT, избегая повторного выполнения одних и тех же запросов и тем самым повышая производительность.
Однако, в MySQL 8.0 и более поздних версиях кэш запросов был полностью удален. Если вы используете MySQL 8.0 или выше, вы можете игнорировать этот параметр.
Предварительно увеличение размера кэша потоков снижает накладные расходы на создание потоков в будущем.
SET GLOBAL thread_cache_size = 100;
Увеличьте размер кэша таблиц, чтобы сократить накладные расходы на открытие новых таблиц.
SET GLOBAL table_open_cache = 4000;
Параметры tmp_table_size и max_heap_table_size
Увеличьте максимальный размер временных таблиц и таблиц кучи, чтобы сократить объем дискового ввода-вывода.
SET GLOBAL tmp_table_size = 64M; SET GLOBAL max_heap_table_size = 64M;
Параметр innodb_flush_log_at_trx_commit
Настройте стратегию очистки журналов в соответствии с потребностями, чтобы сбалансировать производительность и безопасность данных.
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
Увеличьте размер файлов журнала, чтобы сократить накладные расходы, связанные с переключением файлов журнала.
SET GLOBAL innodb_log_file_size = 256M;
Параметр innodb_log_buffer_size
Увеличьте размер буфера лога, чтобы повысить производительность записи данных.
SET GLOBAL innodb_log_buffer_size = 16M;
Отрегулируйте объем ввода-вывода InnoDB на основе производительности дискового ввода-вывода.
SET GLOBAL innodb_io_capacity = 2000;
Увеличьте максимальное количество подключений для обеспечения большего количества одновременных подключений.
SET GLOBAL max_connections = 500;
Увеличьте размер буфера сортировки, чтобы повысить производительность операций сортировки.
SET GLOBAL sort_buffer_size = 4M;
Увеличьте размер буфера чтения, чтобы повысить производительность последовательного сканирования.
SET GLOBAL read_buffer_size = 2M;
Правильное использование индексов
Этот раздел является наиболее важным, т.к. неправильное использование индексов может не только сделать их неэффективными, но и стать причиной общего снижения производительности работы БД.
Создавайте индексы для часто используемых запросов и объединяйте столбцы
Все просто: всякий раз, когда вы замечаете медленные запросы, первым делом проверьте, созданы ли индексы для столбцов в WHERE.
Следуйте правилу самого левого префикса
Это правило применяется к составным индексам, т.е. следует следовать правилу самого левого префикса.
Пример: индекс по (a, b, c) можно использовать для запросов по (a), (a, b) и (a, b, c).
CREATE INDEX idx_abc ON table_name (a, b, c); SELECT * FROM table_name WHERE a = 1 AND b = 2;
Избегайте выполнения вычислений в индексированных столбцах.
Пример: вместо использования WHERE YEAR(date) = 2020 используйте запрос диапазона, например, WHERE date BETWEEN '2025-01-01' AND '2025-12-31'.
SELECT * FROM orders WHERE date BETWEEN '2024-06-01' AND '2024-06-30';
Избегайте дублирования индексов
Проверьте и удалите дубликаты индексов, чтобы сократить расходы на обслуживание.
Будьте осторожны с индексами в часто обновляемых столбцах.
Для часто обновляемых столбцов индексы могут увеличить накладные расходы на операции записи, поэтому к их использованию следует подходить с осторожностью.
CREATE INDEX idx_update_col ON table_name (update_col); -- Если столбец update_col часто обновляется, используйте с осторожностью.
Избегайте использования слишком большого количества столбцов в составных индексах.
Слишком большое количество столбцов в составном индексе может увеличить накладные расходы на обслуживание и привести к чрезмерно большим файлам индекса. Рекомендуется разбить их на меньшее количество составных индексов и отдельных индексов.
CREATE INDEX idx_columns ON table_name (col1, col2, col3, col4, col5); -- слишком много столбцов
Используйте индексы покрытия
Если все запрашиваемые столбцы включены в индекс, можно избежать доступа к фактической таблице (это называется «возврат к таблице»), тем самым повышая производительность.
CREATE INDEX idx_covering ON orders (order_id, order_date, customer_id); -- Запрос включает только столбцы в индексе. SELECT order_id, order_date, customer_id FROM orders WHERE customer_id = 123;
Другие подводные камни, которых следует избегать
Избегайте использования SELECT DISTINCT
Избегайте использования SELECT DISTINCT без необходимости, так как это может привести к дополнительным операциям сортировки и увеличению стоимости запроса.
-- Если можно гарантировать, что в результирующем наборе не будет повторяющихся значений, избегайте использования DISTINCT. SELECT DISTINCT name FROM users WHERE status = 'active';
Используйте LIMIT 1 для оптимизации запросов
Использование LIMIT 1 в запросах, требующих только одного результата
SELECT * FROM users WHERE email = 'user@example.com' LIMIT 1;
Избегайте использования функций в предложении WHERE
Избегайте использования функций в WHERE, так как это может привести к неэффективности индекса.
-- Избегайте SELECT * FROM users WHERE YEAR(created_at) = 2023; -- Замените на SELECT * FROM users WHERE created_at BETWEEN '2024-06-01' AND '2024-06-01';
Разумно используйте UNION ALL
По возможности используйте UNION ALL вместо UNION, поскольку UNION удаляет дубликаты и влечет за собой дополнительные накладные расходы.
SELECT name FROM employees WHERE department = 'Sales' UNION ALL SELECT name FROM contractors WHERE department = 'Sales';
Избегайте использования IS NULL или IS NOT NULL в индексированных столбцах.
Старайтесь избегать использования IS NULL или IS NOT NULL в индексированных столбцах, поскольку некоторые системы хранения не используют индексы для таких запросов.
-- Избегайте SELECT * FROM users WHERE email IS NULL; -- Рассмотрите возможность использования значений по умолчанию вместо NULL SELECT * FROM users WHERE email = '';
Избегайте использования отрицательных условий
Избегайте использования отрицательных условий, таких как NOT IN, != или <>, поскольку эти условия могут неэффективно использовать индексы.
-- Избегайте
SELECT * FROM orders WHERE status != 'completed';
-- Измените на использование положительных условий
SELECT * FROM orders WHERE status IN ('pending', 'processing');Используйте нумерацию страниц с умом
При разбиении больших наборов данных на страницы избегайте использования большого OFFSET. Вместо этого используйте более эффективные методы, такие как запросы диапазона на основе уникальных ключей.
-- Избегайте SELECT * FROM orders ORDER BY order_id LIMIT 1000000, 10; -- Измените на использование диапазонных запросов SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 999999, 1) LIMIT 10;
Используйте блокировку правильно
Если блокировка необходима, выбирайте тип блокировки (блокировка строки, блокировка таблицы) с умом, чтобы избежать проблем с производительностью и взаимоблокировок.
-- Блокировка на уровне строк SELECT * FROM orders WHERE order_id = 1 FOR UPDATE; -- Блокировка на уровне таблиц LOCK TABLES orders WRITE;
Холодное и горячее резервное копирование данных
Общий принцип заключается в том, что доступ к 50 миллионам записей значительно медленнее, чем доступ к 5 миллионам записей, поэтому лучше разделить данные.
Примечание: это не та же концепция, что сегментирование или разбиение баз данных; речь идет об очистке холодных данных и сохранении самых последних горячих данных.
Подробнее про EXPLAIN
Напоследок, поговорим и об этом операторе. Когда оператор запроса обрабатывается оптимизатором запросов MySQL, он подвергается различным оптимизациям на основе стоимости и правил для создания плана выполнения. План выполнения показывает, как будет выполняться запрос, например, порядок многотабличных соединений и методы доступа, используемые для каждой таблицы во время выполнения запроса. Разработчики MySQL любезно предоставили оператор EXPLAIN, чтобы помочь просмотреть конкретный план выполнения для запроса.
В первую очередь, план выполнения используется, чтобы проверить, используются ли индексы. Например, если вы добавили индекс, но он не дает желаемого эффекта, вы можете проверить план выполнения, добавив к своему SQL префикс EXPLAIN.
Написание оператора запроса
Сначала напишите оператор запроса, который вы хотите оптимизировать. Например:
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
Использование EXPLAIN
Добавьте ключевое слово EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
Выполните оператор EXPLAIN.
Запустите оператор EXPLAIN с вашим запросом, чтобы просмотреть выходные результаты. MySQL вернет таблицу, содержащую план выполнения запроса (как показано ниже).
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | d | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | e | ref | department_id | department_id | 4 | const | 10 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
Таблица вывода из оператора EXPLAIN содержит несколько столбцов, каждый из которых предоставляет различную информацию о плане запроса. Общие столбцы включают:
1. id: идентификатор запроса, представляющий порядок выполнения запроса.
2. select_type: тип запроса, например:
- SIMPLE: простой запрос (без использования подзапросов или объединений).
- PRIMARY: первичный запрос (самый внешний запрос).
- UNION: часть запроса UNION.
- SUBQUERY: подзапрос.
3. table: таблица, участвующая в запросе.
4. type: тип соединения, указывающий, как MySQL находит строки. Распространенные типы, упорядоченные от наиболее к наименее эффективным, включают:
- system: таблица содержит только одну строку (обычно встречается в системных таблицах).
- const: таблица имеет не более одной соответствующей строки (индексированной по первичному ключу или уникальному индексу).
- eq_ref: для каждой строки из предыдущей таблицы существует не более одной соответствующей строки в текущей таблице.
- ref: для каждой строки из предыдущей таблицы может быть несколько соответствующих строк в текущей таблице.
- range: строки, найденные с помощью сканирования диапазона индекса.
- index: полное сканирование индекса.
- ALL: полное сканирование таблицы.
5. possible_keys: индексы, которые могут использоваться запросом.
6. key: фактический индекс, используемый запросом.
7. key_len: длина используемого индексного ключа.
8. ref: столбцы или константы, используемые для сравнения с индексом.
9. rows: предполагаемое количество строк, которое MySQL ожидает прочитать.
10. filtered: процент строк, оставшихся после применения фильтров условий таблицы.
11. Extra: Дополнительная информация, например:
- Using index: используется индекс.
- Using where: для фильтрации используется WHERE.
- Using filesort: операция сортировки выполняется с использованием внешнего файла (не через индекс).
- Using temporary: используется временная таблица для хранения промежуточных результатов.
Оптимизация пути запроса
На основе выходных данных EXPLAIN выполните следующие шаги для оптимизации пути запроса:
Обеспечьте использование индекса
- Если столбец type показывает «ALL» или «index», это означает, что выполняется полное сканирование таблицы. Это можно оптимизировать, создав соответствующие индексы.
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
Избегайте использования функций или вычислений в индексированных столбцах, так как это может помешать эффективному использованию индекса. Перепишите условия запроса, чтобы воспользоваться преимуществами индекса.
-- Избегайте SELECT * FROM orders WHERE YEAR(order_date) = 2025; -- Замените на SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
Если запрос включает только столбцы, являющиеся частью индекса, он может избежать доступа к данным таблицы (это называется «поиск в таблице» или «поиск в строке») и, таким образом, повысить производительность.
CREATE INDEX idx_covering ON orders (customer_id, order_date, order_id); -- Запросы, включающие только индексированные столбцы SELECT customer_id, order_date, order_id FROM orders WHERE customer_id = 123 ;
Разбиение сложных запросов на несколько более простых может повысить производительность. Например:
-- Сложные запросы SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = 'John Doe'; -- Разбиваем на два простых запроса SELECT id FROM customers WHERE name = 'John Doe'; -- Предположим, что результат запроса равен 123 SELECT * FROM orders WHERE customer_id = 123;
Реальный пример
Предположим, что есть таблицы employees и departments:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
hire_date DATE,
INDEX (department_id),
INDEX (hire_date)
);
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);Запросим всех сотрудников, которые присоединились к определенному отделу после определенной даты:
EXPLAIN SELECT e.id, e.first_name, e.last_name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Sales' AND e.hire_date > '2025-01-01';
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | d | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | e | ref | department_id | department_id | 4 | const | 10 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
Анализ выходных данных EXPLAIN
Из вывода EXPLAIN мы видим, что:
- Таблица departments (d) использует индекс PRIMARY с типом const , что указывает на то, что это постоянный поиск, что весьма эффективно.
- Таблица employees (e) использует индекс department_id с типом ref , указывающим на то, что это справочный поиск.
Дальнейшие рекомендации по оптимизации
- Создайте индекс по
departments.name: если этот запрос выполняется часто и затрагивает столбецdepartments.name, рассмотрите возможность создания индекса по этому столбцу, чтобы повысить производительность запроса. - Убедитесь, что существует индекс
hire_date: убедитесь, что для столбцаemployees.hire_dateсуществует индекс, чтобы ускорить запросы на основе дат.
CREATE INDEX idx_department_name ON departments (name);
EXPLAIN SELECT e.id, e.first_name, e.last_name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Sales' AND e.hire_date > '2025-01-01';
После выполнения оптимизированного запроса с вновь созданными индексами можно ожидать, что выходные данные будут отражать более эффективный план выполнения, что приведет к сокращению времени выполнения запроса.
На этом все! Спасибо за внимание! Если статья была интересна, подпишитесь на телеграм-канал usr_bin, где будет еще больше полезной информации.