Полное руководство по оптимизации MySQL: конфигурация, индексы, запросы

MySQL остаётся одной из самых популярных реляционных баз данных в мире. Но стандартная конфигурация MySQL оптимизирована для систем с минимальными ресурсами — не для продакшн-серверов с гигабайтами RAM и миллионами строк в таблицах. В этом руководстве мы разберём все аспекты оптимизации MySQL: от настройки конфигурационного файла до построения эффективных индексов и оптимизации медленных запросов.

Понимание архитектуры MySQL

Перед оптимизацией важно понимать, как MySQL работает внутри.

Основные компоненты

MySQL состоит из нескольких слоёв:

Уровень подключений. Управляет клиентскими подключениями, аутентификацией и потоками. Каждое соединение получает собственный поток.

Уровень SQL. Парсит запросы, оптимизирует их и выполняет. Здесь работает кэш запросов (в версиях до 8.0) и оптимизатор.

Уровень хранения. Движки хранения (InnoDB, MyISAM) управляют чтением и записью данных на диск.

Уровень файловой системы. Физическое хранение данных на диске.

Движки хранения

InnoDB — основной движок с MySQL 5.5+:

• Поддержка транзакций ACID
• Блокировки на уровне строк
• Автоматическое восстановление после сбоев
• Поддержка внешних ключей
• Кластерные индексы

MyISAM — устаревший движок:

• Без транзакций
• Блокировки на уровне таблиц
• Быстрее для операций чтения
• Не рекомендуется для новых проектов

Все примеры оптимизации в этой статье ориентированы на InnoDB.

Конфигурация my.cnf: основные параметры

Файл my.cnf (или my.ini на Windows) содержит настройки сервера MySQL. Расположение файла зависит от дистрибутива:

Linux:

• /etc/mysql/my.cnf
• /etc/my.cnf
• /usr/local/mysql/etc/my.cnf
• ~/.my.cnf

Проверьте расположение:

mysql --help | grep "Default options"

Структура конфигурационного файла

Конфигурация разделена на секции:

[client]
# Настройки для клиентских программ

[mysql]
# Настройки для mysql CLI

[mysqld]
# Настройки для сервера MySQL
# Большинство оптимизаций здесь

[mysqldump]
# Настройки для mysqldump

Основные настройки размещаются в секции [mysqld].

Оптимизация памяти InnoDB

InnoDB — самый критичный компонент для оптимизации.

innodb_buffer_pool_size

Самый важный параметр MySQL. Определяет объём RAM для кэширования данных и индексов.

Рекомендация: 70-80% доступной RAM на выделенном сервере MySQL.

Для сервера с 8GB RAM:

[mysqld]
innodb_buffer_pool_size = 6G

Для сервера с 32GB RAM:

[mysqld]
innodb_buffer_pool_size = 24G

Расчёт для общего сервера (MySQL + приложение):

innodb_buffer_pool_size = Total RAM * 0.5

Проверьте текущее значение:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Важно: изменения требуют перезапуска MySQL:

sudo systemctl restart mysql

innodb_buffer_pool_instances

Разделяет buffer pool на несколько экземпляров для уменьшения конкуренции.

Рекомендация: 1 экземпляр на каждый 1GB buffer pool, но не более 64.

Для 24GB buffer pool:

[mysqld]
innodb_buffer_pool_instances = 24

Для 8GB buffer pool:

[mysqld]
innodb_buffer_pool_instances = 8

Минимальное требование: работает только если innodb_buffer_pool_size >= 1GB.

innodb_log_file_size

Размер каждого файла журнала транзакций (redo log).

Рекомендация: 25% от innodb_buffer_pool_size, но не более 4GB на файл.

Для 24GB buffer pool:

[mysqld]
innodb_log_file_size = 2G
innodb_log_files_in_group = 2

Для 8GB buffer pool:

[mysqld]
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

Изменение требует специальной процедуры:

Остановите MySQL:

sudo systemctl stop mysql

Удалите старые лог-файлы:

sudo rm /var/lib/mysql/ib_logfile*

Измените конфигурацию:

sudo nano /etc/mysql/my.cnf

Запустите MySQL:

sudo systemctl start mysql

MySQL создаст новые лог-файлы нужного размера.

innodb_flush_log_at_trx_commit

Контролирует запись журнала на диск после транзакции.

Значение 1 (по умолчанию):

• Полная ACID-совместимость
• Запись на диск после каждой транзакции
• Максимальная безопасность, минимальная скорость

Значение 2:

• Запись в OS кэш после каждой транзакции
• Запись на диск каждую секунду
• Риск потери 1 секунды транзакций при сбое ОС
• Значительно быстрее

Значение 0:

• Запись в буфер MySQL и на диск каждую секунду
• Риск потери 1 секунды транзакций при сбое MySQL
• Самый быстрый вариант

Для высокой производительности с приемлемым риском:

[mysqld]
innodb_flush_log_at_trx_commit = 2

Для максимальной надёжности (критичные данные):

[mysqld]
innodb_flush_log_at_trx_commit = 1

innodb_flush_method

Определяет метод записи данных на диск.

Значение O_DIRECT:

• Обходит файловый кэш ОС
• Избегает двойного кэширования (OS + InnoDB)
• Рекомендуется для Linux

[mysqld]
innodb_flush_method = O_DIRECT

Значение O_DSYNC:

• Используется на некоторых Unix-системах
• Синхронизирует метаданные файлов

innodb_io_capacity и innodb_io_capacity_max

Указывают MySQL скорость дисковой подсистемы.

Для HDD (7200 RPM):

[mysqld]
innodb_io_capacity = 200
innodb_io_capacity_max = 400

Для SSD:

[mysqld]
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

Для NVMe SSD:

[mysqld]
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000

Проверка IOPS диска:

sudo fio --name=random-read --ioengine=libaio --rw=randread --bs=4k --size=1G --numjobs=4 --runtime=60 --time_based --iodepth=32

Оптимизация подключений

max_connections

Максимальное количество одновременных подключений.

Расчёт:

max_connections = (Available RAM - MySQL buffers) / (per-thread buffers)

Типичные значения:

[mysqld]
max_connections = 200

Для высоконагруженных серверов:

[mysqld]
max_connections = 500

Важно: каждое подключение потребляет память для буферов потока.

Проверьте максимальное использованное количество:

SHOW STATUS LIKE 'Max_used_connections';

Если близко к max_connections, увеличьте параметр.

thread_cache_size

Кэширует потоки для переиспользования.

Рекомендация:

[mysqld]
thread_cache_size = 50

Для высоконагруженных систем:

[mysqld]
thread_cache_size = 100

Проверьте эффективность кэша:

SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';

Хороший показатель: Threads_created < 1% от Connections.

wait_timeout и interactive_timeout

Таймауты для неактивных подключений.

Закрывайте неактивные соединения через 5 минут:

[mysqld]
wait_timeout = 300
interactive_timeout = 300

Для приложений с длительными соединениями:

[mysqld]
wait_timeout = 600
interactive_timeout = 600

Оптимизация кэширования запросов

Внимание: Query Cache удалён в MySQL 8.0. Эти настройки актуальны для MySQL 5.7 и ниже.

query_cache_type и query_cache_size

Для MySQL 5.7:

[mysqld]
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

Отключение query cache (рекомендуется при высокой записи):

[mysqld]
query_cache_type = 0
query_cache_size = 0

Проверьте эффективность:

SHOW STATUS LIKE 'Qcache%';

Признаки неэффективности:

• Высокий Qcache_lowmem_prunes
• Низкий Qcache_hits относительно Com_select

Настройка буферов потоков

sort_buffer_size

Буфер для операций сортировки (ORDER BY, GROUP BY).

Консервативное значение:

[mysqld]
sort_buffer_size = 2M

Для больших сортировок:

[mysqld]
sort_buffer_size = 4M

Не устанавливайте слишком большое значение — выделяется для каждого соединения.

join_buffer_size

Буфер для соединений без индексов.

[mysqld]
join_buffer_size = 2M

Для сложных JOIN:

[mysqld]
join_buffer_size = 4M

read_buffer_size и read_rnd_buffer_size

Буферы для последовательного и случайного чтения.

[mysqld]
read_buffer_size = 1M
read_rnd_buffer_size = 2M

Оптимизация временных таблиц

tmp_table_size и max_heap_table_size

Максимальный размер временных таблиц в памяти.

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

Для больших GROUP BY и DISTINCT:

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

Проверьте использование дисковых временных таблиц:

SHOW STATUS LIKE 'Created_tmp%';

Если Created_tmp_disk_tables > 25% от Created_tmp_tables, увеличьте размеры.

Оптимизация бинарных логов

binlog_cache_size

Размер кэша для транзакционных бинарных логов.

[mysqld]
binlog_cache_size = 1M

Для больших транзакций:

[mysqld]
binlog_cache_size = 4M

expire_logs_days

Автоматическое удаление старых бинарных логов.

Хранить 7 дней:

[mysqld]
expire_logs_days = 7

Для MySQL 8.0+:

[mysqld]
binlog_expire_logs_seconds = 604800

Полная конфигурация для разных сценариев

Конфигурация для сервера с 8GB RAM

Оптимизирована для веб-приложений со средней нагрузкой:

[mysqld]
# Основные параметры
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# InnoDB настройки
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_file_per_table = 1
innodb_open_files = 400

# Подключения
max_connections = 200
thread_cache_size = 50
wait_timeout = 300
interactive_timeout = 300

# Буферы запросов
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M

# Временные таблицы
tmp_table_size = 64M
max_heap_table_size = 64M

# Логирование
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Бинарные логи
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 100M

# Безопасность
bind-address = 127.0.0.1

# Кодировка
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# MyISAM (если используется)
key_buffer_size = 256M

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

Конфигурация для сервера с 32GB RAM

Оптимизирована для высоконагруженных приложений:

[mysqld]
# Основные параметры
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# InnoDB настройки
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 24
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 50

# Подключения
max_connections = 500
thread_cache_size = 100
wait_timeout = 600
interactive_timeout = 600

# Буферы запросов
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# Временные таблицы
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M

# Таблицы
table_open_cache = 4000
table_definition_cache = 2000

# Логирование
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Бинарные логи
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 0
binlog_cache_size = 4M

# Производительность
query_cache_type = 0
query_cache_size = 0

# Безопасность
bind-address = 127.0.0.1

# Кодировка
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# MyISAM
key_buffer_size = 512M

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

Конфигурация для read-heavy нагрузки

Оптимизирована для приложений с преобладанием чтения:

[mysqld]
# Увеличенный buffer pool
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 20

# Больше read потоков
innodb_read_io_threads = 16
innodb_write_io_threads = 4

# Агрессивное кэширование
table_open_cache = 8000
table_definition_cache = 4000

# Оптимизация для чтения
innodb_flush_log_at_trx_commit = 2
innodb_flush_neighbors = 0

# Больше подключений
max_connections = 1000
thread_cache_size = 200

Конфигурация для write-heavy нагрузки

Оптимизирована для приложений с интенсивной записью:

[mysqld]
# Больше write потоков
innodb_write_io_threads = 16
innodb_read_io_threads = 4

# Большие логи транзакций
innodb_log_file_size = 4G
innodb_log_files_in_group = 2

# Оптимизация записи
innodb_flush_log_at_trx_commit = 2
innodb_flush_neighbors = 1
innodb_doublewrite = 1

# Высокий IO capacity
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000

# Отключить query cache
query_cache_type = 0
query_cache_size = 0

Применение конфигурации

Резервное копирование текущей конфигурации

Перед изменениями создайте бэкап:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

С датой:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup.$(date +%Y%m%d)

Проверка синтаксиса

Проверьте конфигурацию перед перезапуском:

mysqld --verbose --help | grep -A 1 'Default options'

Постепенное внедрение

Не применяйте все изменения одновременно. Изменяйте параметры группами:

День 1: Buffer pool и логи
День 2: Подключения и потоки
День 3: Буферы запросов
День 4: Временные таблицы

Мониторьте производительность после каждого изменения.

Перезапуск MySQL

Применить изменения:

sudo systemctl restart mysql

Проверьте статус:

sudo systemctl status mysql

Проверьте логи на ошибки:

sudo tail -f /var/log/mysql/error.log

Проверка применённых параметров

Подключитесь к MySQL:

mysql -u root -p

Проверьте конкретный параметр:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Проверьте все InnoDB параметры:

SHOW VARIABLES LIKE 'innodb%';

Экспортируйте все настройки:

SELECT * FROM performance_schema.global_variables ORDER BY VARIABLE_NAME \G

Индексы: основы

Индексы — самый мощный инструмент оптимизации MySQL. Правильные индексы могут ускорить запросы в сотни раз.

Как работают индексы

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

Без индекса: MySQL сканирует всю таблицу (full table scan) С индексом: MySQL использует индекс для прямого доступа к нужным строкам

Аналогия: предметный указатель в книге. Вместо перелистывания всех страниц вы смотрите в указатель и открываете нужную страницу.

Типы индексов в MySQL

PRIMARY KEY:

• Уникальный идентификатор строки
• Автоматически создаётся индекс
• Не может быть NULL
• Одна таблица = один PRIMARY KEY

UNIQUE:

• Гарантирует уникальность значений
• Автоматически создаётся индекс
• Может быть NULL
• Можно иметь несколько UNIQUE индексов

INDEX (обычный индекс):

• Не требует уникальности
• Ускоряет поиск по столбцу
• Самый распространённый тип

FULLTEXT:

• Для полнотекстового поиска
• Работает со столбцами TEXT, VARCHAR
• Специальная логика поиска

SPATIAL:

• Для геопространственных данных
• Типы GEOMETRY, POINT, POLYGON

Структуры индексов

B-Tree (по умолчанию):

• Сбалансированное дерево
• Подходит для большинства случаев
• Поддерживает =, >, <, >=, <=, BETWEEN, LIKE 'prefix%'

Hash:

• Хеш-таблица
• Только для точного совпадения (=)
• Не поддерживает диапазоны
• Только для MEMORY таблиц

Full-text:

• Инвертированный индекс
• Для текстового поиска
• Поддерживает MATCH AGAINST

Создание индексов

Создание простого индекса

Индекс на одном столбце:

CREATE INDEX idx_email ON users(email);

С явным указанием метода:

CREATE INDEX idx_email ON users(email) USING BTREE;

Создание составного индекса

Индекс на нескольких столбцах:

CREATE INDEX idx_user_date ON orders(user_id, order_date);

Порядок столбцов критичен:

• Первый столбец: наиболее селективный или часто используемый в WHERE
• Последующие: порядок использования в запросах

Создание уникального индекса

Гарантирует уникальность значений:

CREATE UNIQUE INDEX idx_username ON users(username);

Составной уникальный индекс:

CREATE UNIQUE INDEX idx_email_domain ON users(email, domain);

Добавление индекса к существующей таблице

Через ALTER TABLE:

ALTER TABLE products ADD INDEX idx_category (category_id);

С несколькими индексами:

ALTER TABLE products
  ADD INDEX idx_category (category_id),
  ADD INDEX idx_price (price),
  ADD INDEX idx_status (status);

Создание индекса с префиксом

Для длинных строковых полей индексируйте префикс:

CREATE INDEX idx_description ON products(description(100));

Индексируются только первые 100 символов, экономя место.

Создание полнотекстового индекса

Для полнотекстового поиска:

CREATE FULLTEXT INDEX idx_content ON articles(title, content);

Использование:

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('mysql optimization' IN NATURAL LANGUAGE MODE);

Создание индекса при создании таблицы

Определите индексы в CREATE TABLE:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY idx_username (username),
    UNIQUE KEY idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

Стратегии индексирования

Правило "самый левый префикс"

Составной индекс (a, b, c) может использоваться для:

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?

Но НЕ для:

• WHERE b = ?
• WHERE c = ?
• WHERE b = ? AND c = ?

Пример:

CREATE INDEX idx_compound ON orders(user_id, status, order_date);

Этот индекс эффективен для:

SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' AND order_date > '2024-01-01';

Неэффективен для:

SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE order_date > '2024-01-01';

Covering index (покрывающий индекс)

Индекс, содержащий все столбцы запроса — MySQL не обращается к таблице.

Запрос:

SELECT user_id, order_date, total FROM orders WHERE user_id = 123;

Создайте покрывающий индекс:

CREATE INDEX idx_covering ON orders(user_id, order_date, total);

MySQL получит все данные из индекса без обращения к таблице.

Индексирование для сортировки

Индексы используются не только для WHERE, но и для ORDER BY:

CREATE INDEX idx_date_status ON orders(order_date, status);

Эффективная сортировка:

SELECT * FROM orders ORDER BY order_date, status;

Индексирование для JOIN

Индексируйте столбцы, используемые в JOIN:

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON order_items(product_id);

Запрос:

SELECT o.*, oi.*, p.*
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 123;

Нужны индексы:

• orders(user_id)
• order_items(order_id)
• order_items(product_id)

Partial indexes (частичные индексы)

MySQL не поддерживает WHERE в индексах напрямую, но можно имитировать через виртуальные столбцы:

ALTER TABLE users ADD COLUMN is_active_bool BOOLEAN AS (status = 'active') STORED;
CREATE INDEX idx_active ON users(is_active_bool);

Управление индексами

Просмотр индексов таблицы

Показать все индексы:

SHOW INDEX FROM table_name;

Подробная информация:

SHOW INDEX FROM table_name \G

Через information_schema:

SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    CARDINALITY,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Размер индексов

Проверьте размер всех индексов таблицы:

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(SUM(stat_value) * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_database'
  AND table_name = 'your_table'
GROUP BY TABLE_NAME, INDEX_NAME;

Размер индексов по всей базе:

SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

Удаление индекса

Через DROP INDEX:

DROP INDEX idx_email ON users;

Через ALTER TABLE:

ALTER TABLE users DROP INDEX idx_email;

Удалить несколько индексов:

ALTER TABLE products
  DROP INDEX idx_category,
  DROP INDEX idx_price,
  DROP INDEX idx_old_status;

Переименование индекса

MySQL 5.7+:

ALTER TABLE users RENAME INDEX old_name TO new_name;

Для старых версий — удалите и создайте заново.

Перестроение индексов

Перестроить все индексы таблицы:

ALTER TABLE table_name ENGINE=InnoDB;

Или через OPTIMIZE TABLE:

OPTIMIZE TABLE table_name;

Важно: OPTIMIZE блокирует таблицу. Используйте в окна обслуживания.

Анализ использования индексов

Проверка неиспользуемых индексов

MySQL 5.7+ имеет sys.schema_unused_indexes:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';

Или через performance_schema:

SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'your_database'
ORDER BY object_schema, object_name;

Мониторинг использования индексов

Включите сбор статистики:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/io/table/%';

Проверьте использование:

SELECT 
    object_name,
    index_name,
    count_star AS uses,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_star DESC;

Анализ дублирующихся индексов

Найдите избыточные индексы с помощью sys:

SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_database';

Пример дубликатов:

• INDEX (user_id)
• INDEX (user_id, order_date)

Первый индекс избыточен — второй покрывает его функциональность.

Оптимизация запросов с EXPLAIN

EXPLAIN показывает план выполнения запроса.

Базовое использование EXPLAIN

Анализ SELECT запроса:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Расширенная информация:

EXPLAIN EXTENDED SELECT * FROM users WHERE email = 'user@example.com';
SHOW WARNINGS;

EXPLAIN FORMAT=JSON

Детальный вывод в JSON:

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;

Интерпретация EXPLAIN

Ключевые столбцы:

type — тип доступа (от лучшего к худшему):

• system — таблица с одной строкой
• const — одна строка по PRIMARY KEY или UNIQUE
• eq_ref — одна строка для каждой комбинации из предыдущих таблиц
• ref — несколько строк с одинаковым значением индекса
• range — диапазон значений индекса
• index — сканирование индекса
• ALL — полное сканирование таблицы (плохо!)

possible_keys — индексы, которые MySQL может использовать

key — индекс, который MySQL действительно использует

key_len — длина использованной части индекса

rows — примерное количество просматриваемых строк

Extra — дополнительная информация:

• Using index — покрывающий индекс (отлично!)
• Using where — фильтрация на уровне MySQL
• Using temporary — используется временная таблица (плохо)
• Using filesort — сортировка файлов (может быть медленно)
• Using join buffer — JOIN без индексов (плохо)

Примеры анализа

Плохой запрос:

EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;

Проблема: функция на столбце не использует индекс.

Исправление:

CREATE INDEX idx_order_date ON orders(order_date);

EXPLAIN SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

Неэффективный JOIN:

EXPLAIN SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Проблема: нет индекса на users.country.

Исправление:

CREATE INDEX idx_country ON users(country);

Оптимизация медленных запросов

Включение slow query log

Включите логирование медленных запросов:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

В конфигурации:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Анализ slow query log

Используйте mysqldumpslow:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Параметры:

• -s t — сортировка по времени
• -t 10 — топ 10 запросов

Альтернативные сортировки:

• -s c — по количеству вызовов
• -s l — по времени блокировки
• -s r — по количеству прочитанных строк

Детальный анализ:

mysqldumpslow -s t -t 20 -g 'SELECT' /var/log/mysql/slow.log

Использование pt-query-digest

Установите Percona Toolkit:

wget percona.com/get/pt-query-digest
chmod +x pt-query-digest

Анализ лога:

pt-query-digest /var/log/mysql/slow.log > slow-digest.txt

С сортировкой по времени выполнения:

pt-query-digest --order-by Query_time:sum /var/log/mysql/slow.log

Профилирование запросов

Включите профилирование:

SET profiling = 1;

Выполните запросы:

SELECT * FROM large_table WHERE column = 'value';

Просмотрите профили:

SHOW PROFILES;

Детали конкретного запроса:

SHOW PROFILE FOR QUERY 1;

Детализация по процессору и блокировкам:

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

Оптимизация конкретных типов запросов

Оптимизация SELECT

Проблема: SELECT *

SELECT * FROM users WHERE id = 123;

Решение: выбирайте только нужные столбцы

SELECT id, username, email FROM users WHERE id = 123;

Проблема: функции в WHERE

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

Решение: переписать условие

SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Оптимизация JOIN

Проблема: множественные JOIN без индексов

SELECT o.*, u.username, p.name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

Решение: добавить индексы на JOIN столбцы

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON orders(product_id);

Используйте STRAIGHT_JOIN для принудительного порядка:

SELECT STRAIGHT_JOIN o.*, u.username
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US';

Оптимизация GROUP BY

Проблема: GROUP BY без индекса

SELECT category, COUNT(*) FROM products GROUP BY category;

Решение: индекс на GROUP BY столбце

CREATE INDEX idx_category ON products(category);

Оптимизация с покрывающим индексом:

CREATE INDEX idx_category_price ON products(category, price);

SELECT category, AVG(price) FROM products GROUP BY category;

Оптимизация ORDER BY

Проблема: сортировка без индекса

SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

Решение: индекс на ORDER BY столбце

CREATE INDEX idx_order_date ON orders(order_date DESC);

Композитный индекс для WHERE + ORDER BY:

CREATE INDEX idx_user_date ON orders(user_id, order_date DESC);

SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY order_date DESC 
LIMIT 10;

Оптимизация COUNT

Проблема: COUNT(*) на большой таблице

SELECT COUNT(*) FROM orders WHERE status = 'pending';

Решение 1: покрывающий индекс

CREATE INDEX idx_status ON orders(status);

Решение 2: денормализация (счётчик в отдельной таблице)

CREATE TABLE order_stats (
    status VARCHAR(20) PRIMARY KEY,
    count INT DEFAULT 0
);

UPDATE order_stats SET count = count + 1 WHERE status = 'pending';

Решение 3: приблизительный COUNT

SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_NAME = 'orders';

Оптимизация DISTINCT

Проблема: DISTINCT на большой таблице

SELECT DISTINCT category FROM products;

Решение: GROUP BY вместо DISTINCT

SELECT category FROM products GROUP BY category;

С индексом:

CREATE INDEX idx_category ON products(category);

Оптимизация подзапросов

Проблема: коррелированный подзапрос

SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 10;

Решение: переписать через JOIN

SELECT u.* FROM users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
    HAVING order_count > 10
) o ON u.id = o.user_id;

Проблема: IN с большим подзапросом

SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

Решение: EXISTS или JOIN

SELECT p.* FROM products p
WHERE EXISTS (
    SELECT 1 FROM categories c 
    WHERE c.id = p.category_id AND c.active = 1
);

Или:

SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1;

Партиционирование таблиц

Партиционирование разделяет большие таблицы на управляемые части.

Типы партиционирования

RANGE — по диапазону значений

CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

LIST — по списку значений

CREATE TABLE users (
    id INT,
    country VARCHAR(2),
    username VARCHAR(50)
)
PARTITION BY LIST COLUMNS(country) (
    PARTITION pNorthAmerica VALUES IN ('US', 'CA', 'MX'),
    PARTITION pEurope VALUES IN ('UK', 'DE', 'FR'),
    PARTITION pAsia VALUES IN ('JP', 'CN', 'IN')
);

HASH — по хешу значения

CREATE TABLE logs (
    id INT,
    message TEXT,
    created_at DATETIME
)
PARTITION BY HASH(id)
PARTITIONS 10;

KEY — как HASH, но MySQL выбирает функцию

CREATE TABLE sessions (
    session_id VARCHAR(128),
    user_id INT,
    data TEXT
)
PARTITION BY KEY(session_id)
PARTITIONS 8;

Добавление партиций

Для RANGE партиций:

ALTER TABLE orders ADD PARTITION (
    PARTITION p2025 VALUES LESS THAN (2026)
);

Удаление партиций

Удалить старые данные через удаление партиции:

ALTER TABLE orders DROP PARTITION p2022;

Значительно быстрее, чем DELETE.

Просмотр партиций

Информация о партициях:

SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'orders';

Мониторинг производительности

Performance Schema

Включите Performance Schema:

[mysqld]
performance_schema = ON

Базовая статистика:

SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Sys Schema

Самые медленные запросы:

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;

Таблицы без первичных ключей:

SELECT * FROM sys.schema_tables_with_full_table_scans
WHERE object_schema = 'your_database';

Неоптимальные индексы:

SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'your_database'
ORDER BY rows_selected DESC;

Мониторинг InnoDB

Статус InnoDB:

SHOW ENGINE INNODB STATUS\G

Размер buffer pool:

SELECT 
    (PagesData*PageSize)/POWER(1024,3) AS data_gb,
    (PagesFree*PageSize)/POWER(1024,3) AS free_gb,
    (PagesData*PageSize)/POWER(1024,3) + (PagesFree*PageSize)/POWER(1024,3) AS total_gb
FROM (
    SELECT 
        variable_value AS PagesData
    FROM performance_schema.global_status
    WHERE variable_name='Innodb_buffer_pool_pages_data'
) AS A,
(
    SELECT 
        variable_value AS PagesFree
    FROM performance_schema.global_status
    WHERE variable_name='Innodb_buffer_pool_pages_free'
) AS B,
(
    SELECT 
        variable_value AS PageSize
    FROM performance_schema.global_status
    WHERE variable_name='Innodb_page_size'
) AS C;

Ключевые метрики

Проверьте эффективность buffer pool:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Хорошее соотношение: 99%+ попаданий в кэш.

Количество ожидающих транзакций:

SELECT COUNT(*) FROM information_schema.INNODB_TRX;

Текущие блокировки:

SELECT * FROM performance_schema.data_locks;

Инструменты оптимизации

mysqltuner

Установка:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

Запуск анализа:

./mysqltuner.pl

С дополнительными проверками:

./mysqltuner.pl --checkversion --updateversion

pt-query-digest

Анализ медленных запросов:

pt-query-digest /var/log/mysql/slow.log

Вывод в файл:

pt-query-digest /var/log/mysql/slow.log --output slowlog --report > report.txt

mytop

Установка:

sudo apt install mytop

Запуск:

mytop -u root -p

Интерактивный мониторинг запросов в реальном времени.

innotop

Установка:

sudo apt install innotop

Запуск:

innotop -u root -p

Мониторинг InnoDB в реальном времени.

Заключение

Оптимизация MySQL — это итеративный процесс. Основные принципы:

Начните с конфигурации. Правильные параметры my.cnf дают 50-70% улучшения без изменения кода.

Индексируйте стратегически. Не создавайте индексы на всё подряд. Анализируйте реальные запросы и создавайте индексы целенаправленно.

Используйте EXPLAIN. Всегда проверяйте планы выполнения медленных запросов.

Мониторьте постоянно. Используйте slow query log, Performance Schema и sys schema для отслеживания проблем.

Тестируйте изменения. Изменяйте параметры постепенно и измеряйте результаты.

Документируйте всё. Записывайте, что изменили и почему. Это поможет при откате или масштабировании.

Правильная оптимизация MySQL может улучшить производительность в 10-100 раз. Инвестируйте время в понимание вашей рабочей нагрузки и применяйте эти техники методично.