Даже самый быстрый сервер с мощным процессором и гигабайтами оперативной памяти может работать медленно, если база данных не оптимизирована. Запросы, которые выполняются секундами, убивают производительность приложения, создают очередь из пользователей и нагружают сервер до предела. Правильная оптимизация базы данных — это искусство, которое сочетает понимание структуры данных, навыки написания запросов и знание внутренних механизмов СУБД.
В этой статье мы подробно разберём:
- Почему запросы тормозят и как это диагностировать.
- Что такое индексы, как они работают и как их правильно создавать.
- Анализ запросов с помощью
EXPLAIN. - Кеширование на уровне базы данных и приложения.
- Партиционирование и шардирование.
- Настройку конфигурации СУБД для производительности.
- Медленный лог и его анализ.
- Мониторинг производительности.
- Практические примеры оптимизации для MySQL и PostgreSQL.
1. Почему запросы тормозят
Прежде чем оптимизировать, нужно понять, в чём причина медленной работы. Основные причины:
| Причина | Описание |
|---|---|
| Отсутствие индексов | Запрос сканирует всю таблицу (full scan) вместо быстрого поиска по индексу. |
| Неправильный индекс | Индекс создан, но не используется из-за неправильного порядка полей или типа запроса. |
| Большой объём данных | Таблицы выросли до миллионов строк, а оптимизация не проводилась. |
| Сложные JOIN-ы | Объединение нескольких больших таблиц без индексов на ключах соединения. |
| Неоптимальные запросы | SELECT *, использование LIKE '%text', отсутствие LIMIT, вложенные подзапросы. |
| Недостаток памяти | Кеш базы данных слишком мал, и данные постоянно читаются с диска. |
| Блокировки (locks) | Конкуренция за ресурсы, длительные транзакции. |
Задача оптимизации — найти и устранить эти узкие места.
2. Индексы: ускорители запросов
Индекс — это структура данных (чаще всего B-Tree или хеш-таблица), которая позволяет СУБД быстро находить строки по значениям одного или нескольких столбцов, не сканируя всю таблицу.
2.1 Как работают индексы
Представьте книгу без оглавления. Чтобы найти главу, вам нужно перелистать все страницы. Индекс — это оглавление, которое указывает, где находится нужная информация.
Типы индексов:
- B-Tree (сбалансированное дерево) — стандартный индекс для большинства СУБД (MySQL InnoDB, PostgreSQL). Подходит для точных сравнений, диапазонов (
>,<,BETWEEN) и сортировки. - Hash (хеш-таблица) — только для точных сравнений (
=), очень быстрый, но не поддерживает диапазоны. Используется в движке Memory. - Full-text — для полнотекстового поиска по тексту.
- GiST / GIN — для геоданных и полнотекстового поиска в PostgreSQL.
- Bitmap — для столбцов с небольшим количеством уникальных значений.
2.2 Когда создавать индексы
- На столбцах, которые часто используются в
WHERE,JOIN,ORDER BY,GROUP BY. - На столбцах с высокой селективностью (много уникальных значений).
- На внешних ключах (для ускорения JOIN-ов).
- Для покрывающих индексов (covering index) — когда индекс содержит все нужные поля, и СУБД не обращается к таблице.
2.3 Когда НЕ создавать индексы
- На маленьких таблицах (менее 1000 записей) — сканирование таблицы быстрее.
- На столбцах, которые часто обновляются — индекс замедляет вставку и обновление.
- На столбцах с низкой селективностью (например,
genderс значениями M/F) — индекс почти не ускоряет поиск. - Если индекс не используется (проверьте через
EXPLAIN).
2.4 Правила создания индексов
1. Учитывайте порядок столбцов в составном индексе.
Индексы работают слева направо. Если у вас индекс (a, b, c), то он эффективен для:
WHERE a = ...WHERE a = ... AND b = ...WHERE a = ... AND b = ... AND c = ...
Но он НЕ эффективен для:
WHERE b = ...WHERE c = ...WHERE a = ... AND c = ...(использует толькоa)
2. Используйте покрывающие индексы.
Если запрос выбирает только поля, которые есть в индексе, СУБД вообще не обращается к таблице.
-- Покрывающий индекс (id, username, email)
CREATE INDEX idx_username_email ON users(username, email);
-- Запрос использует только индекс
SELECT username, email FROM users WHERE username = 'john';ВАЖНО! В InnoDB любой вторичный индекс неявно содержит первичный ключ (обычно id), поэтому индекс (username, email) уже позволяет выполнить запрос без обращения к таблице, если нужны только username, email и id. Это и есть покрывающий индекс.
3. Индексы для LIKE.
LIKE 'text%'— использует индекс.LIKE '%text'— НЕ использует индекс.LIKE '%text%'— НЕ использует индекс.
4. Используйте индекс для ORDER BY.
Если поля в ORDER BY совпадают с индексом, сортировка выполняется без дополнительной операции.
CREATE INDEX idx_created_at ON orders(created_at);
SELECT * FROM orders ORDER BY created_at DESC;2.5 Примеры создания индексов
MySQL:
-- Простой индекс
CREATE INDEX idx_username ON users(username);
-- Составной индекс
CREATE INDEX idx_city_age ON users(city, age);
-- Уникальный индекс
CREATE UNIQUE INDEX idx_email ON users(email);
-- Полнотекстовый индекс
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- Индекс для внешнего ключа
CREATE INDEX idx_user_id ON orders(user_id);PostgreSQL:
-- Аналогично
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_city_age ON users(city, age);
CREATE UNIQUE INDEX idx_email ON users(email);
-- Частичный индекс (только для определённых строк)
CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';
-- Индекс на выражение
CREATE INDEX idx_email_lower ON users(LOWER(email));LOWER(email) мешает использованию индекса. Короткий пример, как это обойти:
- В PostgreSQL: индекс на выражение
CREATE INDEX idx_email_lower ON users(LOWER(email)); - В MySQL: такой индекс на выражение поддерживается, но важно помнить, что запрос должен использовать ту же функцию, иначе индекс не сработает.
3. Анализ запросов с помощью EXPLAIN
EXPLAIN — самый мощный инструмент для анализа производительности запросов. Он показывает план выполнения запроса: какие таблицы сканируются, используются ли индексы, сколько строк обрабатывается, какие операции выполняются.
3.1 Базовое использование EXPLAIN
EXPLAIN SELECT * FROM users WHERE username = 'john';MySQL:
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_username | idx | 50 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+Важные поля:
- type — тип доступа (от лучшего к худшему):
const>eq_ref>ref>range>index>ALL(ALL — полное сканирование таблицы, хуже всего). - possible_keys — какие индексы мог бы использовать запрос.
- key — какой индекс фактически использован.
- key_len — длина используемой части индекса (важно для составных индексов).
- rows — оценка количества строк, которое нужно обработать.
- Extra — дополнительная информация:
Using index(покрывающий индекс),Using where,Using filesort(внешняя сортировка),Using temporary(временная таблица).
3.2 Расширенный анализ: EXPLAIN ANALYZE
PostgreSQL:
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';Показывает реальное время выполнения и количество строк, обработанных на каждом этапе.
MySQL (с версии 8.0):
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';ВАЖНО! EXPLAIN ANALYZE в MySQL полезен для разовой отладки, но не стоит запускать его на высоконагруженных запросах в продакшене. Для постоянного профилирования используйте медленный лог и мониторинг.
3.3 Как читать EXPLAIN
Плохой запрос (без индекса):
type: ALL
rows: 100000
Extra: Using whereЭто означает полное сканирование 100 000 строк. Нужен индекс.
Хороший запрос:
type: ref
key: idx_username
rows: 1
Extra: Using indexИспользуется индекс, возвращается одна строка, дополнительного доступа к таблице нет.
Пример с составным индексом:
Запрос:
SELECT * FROM users WHERE city = 'Moscow' AND age > 25;Индекс (city, age):
key: idx_city_age
key_len: 50 + 4 = 54
rows: 10Индекс (age, city):
key: idx_age_city
key_len: 4 + 50 = 54
rows: 5000Первый индекс лучше, потому что city более селективен.
3.4 Частые проблемы, видимые в EXPLAIN
| Проблема | Признак | Решение |
|---|---|---|
| Полное сканирование таблицы | type: ALL | Создать индекс на поля в WHERE / JOIN. |
| Внешняя сортировка | Extra: Using filesort | Создать индекс для ORDER BY. |
| Временная таблица | Extra: Using temporary | Использовать индекс для GROUP BY. |
| Индекс не используется | possible_keys не NULL, а key — NULL | Проверьте, что тип данных совпадает, и нет функций (LOWER). |
| Только часть индекса | key_len меньше, чем ожидалось | Проверьте порядок полей в составном индексе. |
4. Кеширование на уровне базы данных и приложения
Кеширование — один из самых эффективных способов снизить нагрузку на базу данных.
4.1 Кеш запросов (Query Cache) — устарел, но важен исторически
В старых версиях MySQL (до 8.0) был встроенный кеш запросов. Он кешировал результат SELECT-запроса с точным совпадением текста. Отключён в MySQL 8.0 из-за проблем с масштабированием.
Что вместо него:
- Используйте внешние кеши (Redis, Memcached).
- Настройте кеш на уровне приложения.
4.2 Кеш на уровне приложения (Redis / Memcached)
Этот подход мы уже рассматривали в статье про высокую нагрузку. Коротко:
Пример с Redis:
<?php
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$cacheKey = 'user_profile_' . $userId;
$data = $redis->get($cacheKey);
if ($data === false) {
$data = $db->query("SELECT * FROM users WHERE id = $userId");
$redis->setex($cacheKey, 3600, serialize($data));
}
echo $data['name'];
?>Стратегии кеширования:
- Cache-Aside — приложение проверяет кеш, если нет — идёт в БД и сохраняет в кеш.
- Write-Through — при записи обновляется и БД, и кеш.
- Write-Behind — запись сначала в кеш, асинхронная запись в БД.
- Refresh-Ahead — кеш обновляется до истечения срока жизни (TTL).
ВАЖНО! При использовании Cache‑Aside обязательно реализуйте инвалидацию кеша при изменении данных: при UPDATE/DELETE удаляйте или обновляйте ключ в Redis. Иначе пользователи будут видеть устаревшие данные.
4.3 Кеширование результатов тяжелых запросов
Если какой-то запрос выполняется долго (агрегация, отчёты), можно сохранять его результат в отдельной таблице или в Redis и обновлять раз в минуту/час.
Пример:
-- Тяжелый запрос
SELECT DATE(created_at), COUNT(*) FROM orders GROUP BY DATE(created_at);
-- Сохранять результат в таблицу-кеш
CREATE TABLE daily_orders_cache (
day DATE PRIMARY KEY,
count INT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);И обновлять через cron или триггер.
Индекс на created_at помогает для сортировки и диапазонов, но для агрегации по DATE(created_at) эффективнее либо хранить дату в отдельном столбце (денормализация), либо использовать отдельную таблицу‑кеш и обновлять её по расписанию.
4.4 Кеширование объектов (ORM кеш)
Популярные ORM (Doctrine, Eloquent, Hibernate) имеют встроенные механизмы кеширования на уровне объектов. Они хранят результат запроса в кеше и не ходят в БД при повторных запросах.
4.5 Промежуточное кеширование через Proxy (ProxySQL)
ProxySQL — это прокси-сервер для MySQL, который умеет кешировать запросы на уровне протокола, переписывать запросы, распределять нагрузку и маршрутизировать трафик на реплики.
Использование ProxySQL — это отдельная тема, но его можно рассматривать как дополнительный уровень кеширования.
5. Партиционирование
Партиционирование — это разделение большой таблицы на более мелкие части (партиции) по определённому критерию (диапазон, список, хеш). Запросы, которые попадают в одну партицию, работают значительно быстрее.
5.1 Типы партиционирования
- По диапазону (RANGE) — по датам, ID.
- По списку (LIST) — по списку значений (например, по регионам).
- По хешу (HASH) — распределение по хешу для равномерной нагрузки.
- По ключу (KEY) — специфичный для MySQL.
5.2 Пример для MySQL
-- Создание партиционированной таблицы по дате
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL,
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 p_future VALUES LESS THAN MAXVALUE
);
-- Запрос, использующий партицию
SELECT * FROM orders WHERE order_date >= '2023-01-01';5.3 Пример для PostgreSQL (декларативное партиционирование)
-- Создание основной таблицы
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);
-- Создание партиций по месяцам
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');5.4 Когда использовать партиционирование
- Таблицы > 10 миллионов строк.
- Есть естественный критерий для разделения (дата, регион).
- Большинство запросов используют этот критерий.
- Нужно быстро удалять старые данные (DROP PARTITION).
5.5 Ограничения
- Не все индексы работают на партициях.
- Внешние ключи могут быть сложнее.
- Партиционирование — это не серебряная пуля. Если запросы не используют ключ партиционирования, они будут сканировать все партиции (что даже хуже).
ВАЖНО! В MySQL внешние ключи не поддерживаются для партиционированных таблиц. Если у вас есть внешние ключи, партиционирование может быть неприменимо. В PostgreSQL поддержка есть, но планируйте миграции и обслуживание заранее.
6. Шардирование
Шардирование — это горизонтальное разделение данных на разные серверы. Каждый шард содержит часть данных (например, пользователи с ID 1–10000 на сервере A, 10001–20000 на сервере B).
Когда нужно шардирование:
- Данных настолько много, что один сервер не справляется (сотни терабайт).
- Нагрузка на запись настолько высока, что один мастер не тянет.
Сложности:
- Перераспределение данных при добавлении новых шардов.
- JOIN-ы между шардами.
- Транзакции на разных серверах.
В большинстве проектов до шардирования не доходят, обходясь оптимизацией, индексами и репликацией.
7. Настройка конфигурации СУБД
7.1 Настройка MySQL
Основные параметры в /etc/mysql/mysql.conf.d/mysqld.cnf:
ini
# Буфер для индексов и данных InnoDB (обычно 50-70% от всей памяти сервера)
innodb_buffer_pool_size = 2G
# Размер лога транзакций (для производительности)
innodb_log_file_size = 512M
# Размер буфера для сортировки (per session)
sort_buffer_size = 2M
# Размер буфера для чтения (per session)
read_buffer_size = 1M
# Кеш для запросов (отключён в MySQL 8.0, используйте Redis)
# query_cache_size = 0
# Количество соединений
max_connections = 500
# Таймаут для долгих запросов (лог медленных запросов)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 27.2 Настройка PostgreSQL
В /etc/postgresql/15/main/postgresql.conf:
ini
# Размер буфера (аналог InnoDB pool)
shared_buffers = 2G
# Рабочая память для сортировки и хешей (per query)
work_mem = 16MB
# Память для кеша операционной системы (эффективный кеш)
effective_cache_size = 4G
# Максимальное количество соединений
max_connections = 300
# Лог медленных запросов
log_min_duration_statement = 2000 # 2 секунды8. Медленный лог (Slow Query Log)
Медленный лог записывает все запросы, которые выполняются дольше заданного времени. Это самый ценный инструмент для поиска проблем.
8.1 Включение медленного лога в MySQL
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2; -- 2 секунды
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';Анализ медленного лога с помощью mysqldumpslow:
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.logПокажет топ-10 медленных запросов по времени.
Включайте медленный лог только на время диагностики или на тестовом сервере. На продакшене используйте выборочную настройку (например, long_query_time = 2 и ротацию логов), чтобы не получить проблемы с местом на диске.
8.2 Включение медленного лога в PostgreSQL
В postgresql.conf:
log_min_duration_statement = 2000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h 'После изменения перезапустите PostgreSQL:
sudo systemctl restart postgresql8.3 Анализ логов в реальном времени
tail -f /var/log/mysql/mysql-slow.log
tail -f /var/log/postgresql/postgresql-15-main.log9. Мониторинг производительности БД
Мы уже настроили Prometheus + Grafana. Добавим мониторинг для базы данных.
9.1 Метрики, за которыми нужно следить
- Количество запросов в секунду (QPS)
- Время ответа (latency) — среднее, p95, p99
- Использование индексов (игнорируются ли индексы)
- Блокировки (locks) — количество ожидающих транзакций
- Размер базы данных и рост таблиц
- Количество активных соединений
- Нагрузка на InnoDB Buffer Pool (MySQL) / Shared Buffers (PostgreSQL)
- Медленные запросы в реальном времени
9.2 Инструменты для мониторинга
- Percona Monitoring and Management (PMM) — бесплатный инструмент для MySQL, PostgreSQL, MongoDB.
- pgAdmin — встроенный мониторинг для PostgreSQL.
- MySQL Workbench — мониторинг производительности.
- Grafana + Prometheus с экспортёрами для MySQL и PostgreSQL (мы установили их ранее).
10. Практические примеры
10.1 Пример: Оптимизация запроса с JOIN
Проблемный запрос:
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Moscow'
ORDER BY o.created_at DESC
LIMIT 20;Проблемы:
- Нет индекса на
users.city. - Нет индекса на
orders.user_id. ORDER BY o.created_atбез индекса.
Решение:
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);После:
usersиспользует индексidx_city.ordersиспользует индексidx_user_idдля JOIN.ORDER BYиспользуетidx_created_at.
10.2 Пример: Оптимизация поиска по тексту
Проблемный запрос:
SELECT * FROM articles WHERE content LIKE '%оптимизация%';Проблема: LIKE '%text%' не использует индекс.
Решение 1: Использовать полнотекстовый поиск.
MySQL:
ALTER TABLE articles ADD FULLTEXT(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('оптимизация');PostgreSQL:
CREATE INDEX idx_content ON articles USING GIN (to_tsvector('russian', content));
SELECT * FROM articles WHERE to_tsvector('russian', content) @@ to_tsquery('оптимизация');Решение 2: Использовать внешний поисковик (Elasticsearch, Sphinx).
10.3 Пример: Оптимизация GROUP BY
Проблемный запрос:
SELECT DATE(created_at), COUNT(*) FROM orders GROUP BY DATE(created_at);Проблема: GROUP BY без индекса приводит к временной таблице.
Решение: Создать индекс на created_at.
CREATE INDEX idx_created_at ON orders(created_at);Теперь GROUP BY использует индекс для сортировки.
Более продвинутое решение: Создать отдельную таблицу для статистики и обновлять через триггер или cron.
10.4 Пример: Оптимизация подзапросов
Плохой запрос:
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);Оптимизация через JOIN:
SELECT DISTINCT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;Или через EXISTS:
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);EXISTS часто быстрее, если подзапрос возвращает мало строк.
11. Частые ошибки и как их избежать
| Ошибка | Проявление | Решение |
|---|---|---|
| Индекс на поле, но он не используется | EXPLAIN показывает type: ALL | Проверьте функции (LOWER), преобразования типов, LIKE '%text'. |
| Слишком много индексов | Медленные вставки/обновления | Удалите индексы, которые не используются (проверьте через performance_schema). |
SELECT * в больших таблицах | Тратится много памяти и времени | Перечисляйте только нужные поля. |
| Нет лимита на выборку | Запрос возвращает миллионы строк | Всегда используйте LIMIT для выборок в интерфейсе. |
| Долгие транзакции | Блокировки, недоступность таблиц | Старайтесь делать транзакции короткими, фиксируйте их как можно раньше. |
| Игнорирование медленного лога | Проблемы не видны | Регулярно анализируйте медленный лог и оптимизируйте запросы. |
| Неправильный порядок полей в составном индексе | Индекс не используется полностью | Ставьте наиболее селективное поле первым. |
| Кеширование устаревших данных | Пользователи видят старую информацию | Используйте TTL (время жизни кеша) или инвалидацию при обновлении. |
Оптимизация — это не разовое действие, а непрерывный процесс. Регулярно анализируйте запросы, следите за метриками и адаптируйте структуру базы данных под растущие потребности вашего проекта.