360 слов | 2 минуты

Оптимизация базы данных сайта

База данных — часто узкое место производительности сайта. Медленные запросы, разросшиеся таблицы и отсутствие индексов могут замедлить даже хорошо написанный код. Разберём как найти проблемы и устранить их.

Как понять что проблема в базе данных

Признаки что база данных тормозит:

  • Страницы с фильтрами, поиском или большим каталогом грузятся заметно медленнее остальных
  • Высокая нагрузка на CPU сервера при относительно небольшом трафике
  • В логах PHP много времени тратится на ожидание ответа базы данных
  • При анализе в GTmetrix видно что сервер долго формирует ответ (TTFB > 500 мс)

Инструменты диагностики

EXPLAIN — анализ запроса

Перед любым запросом добавьте EXPLAIN — это покажет как MySQL выполняет запрос:

EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC;

Обратите внимание на колонку type:

  • const, ref, range — хорошо, используется индекс
  • ALL — плохо, полное сканирование таблицы (table scan)

И на колонку rows — чем меньше строк просматривается, тем лучше.

Лог медленных запросов

Включите логирование запросов которые выполняются дольше 1 секунды:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Или в my.cnf:

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

MySQLTuner

Скрипт который анализирует настройки MySQL и даёт рекомендации:

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

Очистка мусора

WordPress

Таблица wp_options с параметром autoload = yes загружается при каждом запросе. Со временем она разрастается до нескольких мегабайт.

Найти тяжёлые автозагружаемые опции:

SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

Очистить таблицу от мусора плагинов:

-- Удалить транзиенты (временные данные плагинов)
DELETE FROM wp_options WHERE option_name LIKE '%_transient_%';

-- Очистить таблицу ревизий постов
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Удалить удалённые записи из корзины
DELETE FROM wp_posts WHERE post_status = 'trash';

1С-Битрикс

Таблицы которые быстро разрастаются:

-- Очистить журнал событий (может весить гигабайты)
TRUNCATE TABLE b_event_log;

-- Очистить статистику (если не используете)
TRUNCATE TABLE b_stat_adv_back;
TRUNCATE TABLE b_stat_session;

-- Очистить хиты (старше 90 дней)
DELETE FROM b_stat_guest WHERE DATE_REGISTER < DATE_SUB(NOW(), INTERVAL 90 DAY);

Перед очисткой сделайте резервную копию базы данных.

Общая оптимизация таблиц

После массового удаления данных таблицы занимают место «впустую». Оптимизация освобождает его:

-- Оптимизировать конкретную таблицу
OPTIMIZE TABLE wp_options;

-- Оптимизировать все таблицы базы данных (выполняется долго)
-- Используйте mysqlcheck:

mysqlcheck -u root -p --optimize --all-databases

Добавление индексов

Индексы — это как оглавление в книге. Без индекса MySQL читает всю таблицу чтобы найти нужные строки. С индексом — прыгает сразу к нужному месту.

Когда добавлять индекс

  • Поле часто используется в условии WHERE
  • Поле используется для сортировки ORDER BY
  • Поле используется для объединения таблиц JOIN
  • Запрос с EXPLAIN показывает type = ALL на большой таблице

Как добавить индекс

-- Простой индекс
ALTER TABLE wp_posts ADD INDEX idx_post_status (post_status);

-- Составной индекс (для запросов с несколькими условиями)
ALTER TABLE wp_posts ADD INDEX idx_status_date (post_status, post_date);

-- Уникальный индекс
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

Пример: ускорение запроса к каталогу

До оптимизации — запрос просматривает 50 000 строк:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND active = 1;
-- type: ALL, rows: 50000

После добавления индекса:

ALTER TABLE products ADD INDEX idx_cat_active (category_id, active);
-- type: ref, rows: 120

Настройка MySQL/MariaDB

Настройки в /etc/mysql/my.cnf которые влияют на производительность:

[mysqld]
# Размер буфера InnoDB — главный параметр
# Установить в 70-80% от доступной RAM если MySQL единственный сервис
innodb_buffer_pool_size = 1G

# Размер лога InnoDB
innodb_log_file_size = 256M

# Кэш запросов (для MySQL < 8.0)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# Максимальное количество соединений
max_connections = 150

# Кэш таблиц
table_open_cache = 400

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

systemctl restart mysql

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

Если таблица содержит миллионы строк и запросы к ней медленные — рассмотрите партиционирование. Например, разбить таблицу заказов по годам:

ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Теперь запросы за конкретный год сканируют только один раздел.

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

Для часто повторяющихся тяжёлых запросов используйте кэш:

Redis — кэш в оперативной памяти, намного быстрее базы данных:

$cacheKey = 'catalog:category:' . $categoryId;
$result = $redis->get($cacheKey);

if (!$result) {
    $result = $db->query('SELECT ...');
    $redis->setex($cacheKey, 3600, serialize($result)); // кэш на 1 час
}

Memcached — аналогичное решение, хорошо интегрируется с Битрикс и WordPress.

Мониторинг производительности базы данных

Полезные запросы для регулярной диагностики:

-- Топ самых больших таблиц
SELECT table_name, 
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 10;

-- Таблицы без первичного ключа
SELECT table_name FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name NOT IN (
    SELECT table_name FROM information_schema.table_constraints
    WHERE constraint_type = 'PRIMARY KEY'
);

Если нужна помощь с оптимизацией базы данных вашего сайта — обратитесь к нам. Проведём анализ и ускорим работу сайта.

Предыдущая
Как ускорить загрузку сайта — причины и способы оптимизации
Следующая
Google PageSpeed Insights — как улучшить оценку сайта