Оптимизация баз данных MySQL/PostgreSQL для ускорения работы сайта

Оптимизация базы данных MySQL/PostgreSQL — это не магия, а системный процесс: анализ slow-query-логов, точечная настройка индексов и реструктуризация таблиц. В этой статье — реальные методы от архитекторов AVOdigital, которые помогут ускорить сайт в 2–5 раз без замены сервера.

Каждый запрос к базе данных — это цепочка операций ввода-вывода, блокировок и сортировок. Когда сайт тормозит, виноват не всегда хостинг. В 70% случаев узким местом становится неоптимизированный SQL. В этой статье разберём, как находить проблемные запросы, настраивать индексы, реструктурировать таблицы и применять кэширующие слои — всё на примерах реальных проектов.

1. Поиск медленных запросов: инструменты и протоколы

Прежде чем что-то оптимизировать, нужно найти «бутылочное горлышко». Для этого используем slow query log в MySQL или auto_explain в PostgreSQL. Включение лога в MySQL:

• Откройте my.cnf и добавьте: slow_query_log = 1, long_query_time = 2, slow_query_log_file = /var/log/mysql/slow.log.
• Перезапустите MySQL: sudo systemctl restart mysql.
• Анализируйте лог утилитой mysqldumpslow или pt-query-digest из Percona Toolkit.

В PostgreSQL настройка через postgresql.conf: shared_preload_libraries = 'auto_explain', auto_explain.log_min_duration = 1000 (в миллисекундах). После перезапуска сервер начнёт логировать планы выполнения всех запросов дольше 1 секунды.

Для типовых CMS, таких как WordPress, мы дополнительно включаем профилирование в wp-config.php: define('SAVEQUERIES', true); — это сохраняет все запросы в глобальную переменную, доступную для отладки. В связке с REST API и JWT-аутентификацией (например, при авторизации через OAuth) это помогает выявить запросы, которые генерируют плагины или кастомные эндпоинты. В нашей практике в AVOdigital был случай, когда один неоптимизированный CommerceML-запрос на синхронизацию каталога забивал весь пул соединений — мы выявили это именно через slow query log.

2. Настройка индексов: от простого к сложному

Индексы — это ускорители поиска. Но неправильные индексы только замедляют запись. Основные правила:

• B-Tree индексы — для полей с высокой селективностью (уникальные значения, например, email).
• Покрывающие индексы — включают все поля, участвующие в запросе, чтобы не обращаться к таблице.
• Составные индексы — для фильтрации по нескольким столбцам. Порядок столбцов важен: сначала идёт поле с наибольшей селективностью.

Пример: для запроса SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01' ORDER BY user_id создаём индекс: CREATE INDEX idx_status_created_user ON orders (status, created_at, user_id);. Если в WHERE-условии есть LIKE '%text%' — индекс не сработает, нужен полнотекстовый (FULLTEXT) или использование внешнего поискового движка (Elasticsearch, Sphinx).

В PostgreSQL дополнительно доступны GIN и GiST для JSONB и геоданных, а также BRIN для больших таблиц с естественной сортировкой по времени. Настройка индексов — итеративный процесс: всегда проверяйте план выполнения через EXPLAIN ANALYZE.

3. Оптимизация структуры таблиц и партиционирование

Нормализация до 3НФ — база, но при высоких нагрузках денормализация оправдана. Например, можно хранить счётчики (количество товаров в корзине) прямо в поле заказа, а не вычислять их каждый раз через JOIN. В проектах, где мы внедряли Redis для кэширования, количество запросов к БД снижалось на 60%.

Партиционирование — для таблиц с миллионами строк. В MySQL поддерживается RANGE, LIST, HASH, KEY. Пример: PARTITION BY RANGE (YEAR(created_at)). В PostgreSQL — декларативное наследование или PARTITION BY RANGE. Это позволяет быстро удалять «старые» партиции без блокировки всей таблицы.

Ниже — сравнительная таблица подходов для типовых сценариев:

4. Настройки сервера и кэширующие слои

Даже идеальные индексы не спасут, если сервер «съедает» всю память. Ключевые параметры для MySQL (InnoDB):

• innodb_buffer_pool_size — 70-80% от доступной RAM.
• innodb_log_file_size — 25% от буфера.
• max_connections — не более 200-300, иначе — пул соединений.

Для PostgreSQL:

• shared_buffers — 25% RAM.
• effective_cache_size — 50% RAM.
• work_mem — для сортировок и хэшей, старт с 4-8 MB.

На уровне веб-сервера (Nginx) настраиваем proxy_cache и fastcgi_cache, а также .htaccess для Apache (если используется) — включаем mod_deflate и mod_expires. Для статики — облачный CDN (Cloudflare, Vercel).

Архитектурные паттерны: Redis или RabbitMQ для очередей. Например, при синхронизации с 1С через CommerceML — ставим задачу в RabbitMQ, чтобы не блокировать ответ пользователю. В AVOdigital мы используем связку Redis + MySQL для агрегации данных в реальном времени: при каждом запросе кеш обновляется асинхронно, а ответы выдаются за 1-2 мс.

5. Пошаговая инструкция — чек-лист оптимизации

1. Включите slow query log и соберите выборку 24-часовой нагрузки.
2. Проанализируйте топ-10 запросов по времени выполнения.
3. Для каждого запроса выполните EXPLAIN и проверьте тип доступа (ALL, INDEX, RANGE, REF, EQ_REF, CONST).
4. Добавьте недостающие индексы — используйте покрывающие и составные.
5. Проверьте размеры таблиц — SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES.
6. Оптимизируйте таблицы — OPTIMIZE TABLE (MySQL) или VACUUM ANALYZE (PostgreSQL).
7. Настройте параметры сервера под доступную память и количество соединений.
8. Внедрите кэширование — Redis для часто запрашиваемых данных, Nginx для статики.
9. Протестируйте под нагрузкой — инструменты: JMeter, Locust, Siege.
10. Повторите цикл — оптимизация — это постоянный процесс.

6. Заключение: практика AVOdigital

Мы в AVOdigital неоднократно сталкивались с проектами, где после миграции на PostgreSQL и настройки партиционирования, а также внедрения Redis, время генерации страницы падало с 5 секунд до 0.3 секунды. Важно помнить: оптимизация БД — это не разовая акция, а регулярный аудит. Используйте slow query log, профилируйте запросы, следите за планами выполнения. И тогда сайт будет работать быстро даже при пиковых нагрузках.

Частые вопросы