Анализ журнала медленных запросов MySQL в хостинге: Советы по оптимизации

Журнал медленных запросов MySQL показывает мне на хостинге, какие запросы съедают время, как часто они встречаются и почему тормозят работу. Я покажу вам конкретные шаги, как активировать журнал, проанализировать его и перестроить запросы так, чтобы страницы загружались быстрее, а ресурсы сервера работали эффективнее.

Центральные пункты

• Активация и разумно установить пороговые значения
• Оценка с помощью pt-query-digest и mysqldumpslow
• Метрики интерпретировать: Query_time, Lock_time, Rows_examined
• Тюнинг с помощью индексов, EXPLAIN и переписывания
• Автоматизация и мониторинг в хостинге

Что делает журнал медленных запросов на хостинге?

Хостинг означает общие ресурсы, поэтому каждая миллисекунда на запрос имеет значение. Я использую журнал для поиска запросов, которые выполняются дольше установленного предела, и вижу такие ключевые показатели, как время запроса (Query_time), время блокировки (Lock_time), количество отправленных строк (Rows_sent) и количество просмотренных строк (Rows_examined) для каждого запроса. Эти цифры покажут мне, стоит ли за запросом недостающий индекс, неблагоприятное объединение или полное сканирование таблицы. Особенно на серверах с несколькими сайтами один плохой запрос может создать большую нагрузку на процессор и ввод-вывод. Затем я определяю приоритетность запросов с наибольшим общим временем, поскольку именно они оказывают наибольшее влияние на время загрузки и нагрузку на сервер.

Активация и разумные пороговые значения

НачалоЯ могу работать временно или постоянно через my.cnf, в зависимости от доступа на хостинге. Для быстрых тестов я временно включаю журнал и устанавливаю long_query_time в значение, соответствующее трафику и аппаратному обеспечению. Я часто перехожу к 0,1 секунды для интенсивно используемых сайтов, но слежу за размером журнала, чтобы ввод-вывод не увеличивался без необходимости. Если прямой доступ к файлам ограничен, я использую опции схемы производительности в оболочке MySQL для создания отчетов. После тонкой настройки я записываю окончательные параметры в конфигурационный файл и перезапускаю службу.

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

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Постоянно Я устанавливаю такие параметры, как log_throttle_queries_not_using_indexes и log_slow_admin_statements, чтобы журнал оставался полезным и не разрастался. Я документирую каждое значение, например, почему long_query_time составляет 0,5 или 0,1 секунды. Это позволяет мне уточнить его позже. В общих средах я часто обсуждаю активацию с провайдером или использую их панель. Я связываю каждую активацию с датой начала, чтобы иметь возможность сравнить эффекты в мониторинге и метриках.

[mysqld].
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1

Эффективная оценка медленного журнала

Необработанные данные являются шумными, поэтому я суммирую их с помощью pt-query-digest и сортирую по общему времени за определенный период времени. Так я распознаю закономерности, запросы с высокой переменной и семейства запросов, которые отличаются только параметрами. Я проверяю распределение, а не только среднее значение, потому что выбросы вызывают реальные проблемы у пользователей. Для быстрого обзора mysqldumpslow помогает мне увидеть десять самых медленных групп. Для более глубокого анализа я использую временные окна, фильтры базы данных и экспорт в текстовый анализ.

pt-query-digest /var/log/mysql/slow-query.log
pt-query-digest --since '24h' /var/log/mysql/slow-query.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Полезное также просматривать другие журналы, когда в дело вступают приложения или функции PHP. Для этого я использую существующие рабочие процессы журналов и объединяю результаты. Это руководство часто служит мне введением: Анализ журналов. Я синхронизирую временные метки, чтобы можно было сравнить пики трафика с пиками запросов. Это позволяет мне увидеть, используют ли базу данных одновременно пропуски кэша, задания cron или задания импорта.

Правильная интерпретация метрик

Время запроса показывает чистое время выполнения; в первую очередь я отдаю предпочтение запросам длительностью более одной секунды. Время_блокировки показывает время ожидания из-за блокировок, которые часто возникают из-за неоправданно длинных транзакций или больших пакетов. Отношение Rows_examined к Rows_sent говорит мне о том, просматривают ли запросы слишком много строк и не хватает ли индексов. Если в журнале много записей „Индексы не используются“, я устанавливаю дросселирование и внимательно изучаю затронутые таблицы. По-прежнему важно всегда бороться с причиной, а не с симптомом: Индекс на правильном столбце победит любое обновление оборудования.

Метрики	Что я вижу	Измерение
Время_запроса высокое	Длительное время работы одной версии	Проверьте EXPLAIN, перепишите запрос, добавьте индекс
Высокое время блокировки	Время ожидания замков	Сокращение количества транзакций, уменьшение размера пакета, подходящая изоляция
Строки_исследованные ≫ Строки_отправленные	Отсканировали слишком много, вернули слишком мало	Индексные столбцы фильтрации, создание саргетируемости
Индекс не используется	Полное сканирование таблицы	Создайте индекс, избегайте выражений в WHERE

Предельные значения Я корректирую его после первой недели, чтобы не потеряться в шуме. Я снижаю long_query_time поэтапно, пока не получу достаточное количество совпадений для систематических улучшений. Я документирую каждую корректировку с указанием даты и причины. Это позволяет сфокусировать оценку. Ценные совпадения избавляют меня от дублирования работы в дальнейшем.

Практика: пошаговая настройка запросов

ПОЯСНИТЬ это мой старт, прежде чем я изменю код. Я ищу „type: ALL“, „rows“ с большими номерами и „Using filesort“ или „Using temporary“. Функции над столбцами в WHERE или JOIN часто не позволяют использовать индексы. Вместо этого я формулирую условия, которые можно выполнить, а затем проверяю новый план. Каждый шаг должен выполнять сокращение строк заблаговременно и целенаправленно.

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Лучше:
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

JOINs Я оптимизирую, проверяя порядок соединения и сопоставляя индексы по ключам соединения. Я проверяю, покрывает ли составной индекс WHERE + ORDER BY, чтобы избежать файлового сортирования. Я устанавливаю LIMIT там, где необходим только предварительный просмотр. Я сохраняю кэширование результатов на уровне приложения для повторяющихся одинаковых запросов с низкой частотой изменений. Более подробное введение в индексы и блокировки вы можете найти здесь: Индексы и блокировка.

Индексные стратегии для CMS и магазинов

WordPress, Системы WooCommerce или магазины создают типичные шаблоны: много чтения, выборочная запись, часто с мета- или товарными таблицами. Я анализирую наиболее распространенные маршруты - главная страница, категория, поиск, оформление заказа - и размещаю индексы специально для столбцов фильтра, сортировки и объединения. Охватывающие индексы (например, (status, created_at, id)) экономят много обращений к таблице. Для поиска префиксов я использую подходящие индексные формы или полный текст вместо LIKE ‚%wort%‘. Я измеряю каждое изменение индекса до и после запуска с одинаковыми профилями нагрузки.

Рост Я использую кардинальность и гистограммы для проверки наборов данных, чтобы не индексировать редкие значения. Я поддерживаю низкое количество индексов, чтобы держать под контролем нагрузку на запись и требования к памяти. Консолидированные составные индексы заменяют несколько отдельных индексов. Я регулирую автовакуумные задачи в MySQL, регулярно анализируя их и перестраивая только при необходимости. Это позволяет сохранить надежность оптимизатора.

Настройки сервера, кэширование и память

InnoDB Я определяю размер буферного пула на основе активных записей данных и размеров индексов, а не по шаблонам. Я увеличиваю его до тех пор, пока размер рабочего набора не будет в основном находиться в памяти, а частота пропусков страниц не снизится. Я устанавливаю размеры tmp_table_size и max_heap_table_size так, чтобы меньше временных таблиц оказывалось на диске. Для обеспечения безопасности записи и задержки я балансирую innodb_flush_log_at_trx_commit соответствующим образом для приложения. На уровне приложения я кэширую частые результаты и использую HTTP-кэширование, чтобы база данных видела меньше запросов.

Оборудование и сетевые эффекты включаются в диагностику: Медленный ввод-вывод с хранилища или перегруженный процессор немедленно распознаются запросами. Поэтому я измеряю IO-wait параллельно с метриками базы данных. Если вам нужны дополнительные резервы, планируйте вертикальное или горизонтальное масштабирование с измеримой целью. В этом руководстве вы найдете компактный обзор узких мест, настройки и ресурсов: Оборудование и кэш. Таким образом я убеждаюсь, что не поворачиваю вслепую не ту ручку.

Параллельность и блокировка в хостинге

Время блокировки растет, когда длинные транзакции затрагивают много строк или когда задания по очистке выполняются в прайм-тайм. Я сокращаю время операций записи, разбиваю большие обновления на более мелкие партии и тем самым уменьшаю время удержания блокировок. Подходящие уровни изоляции уменьшают конфликты, не ставя под угрозу согласованность данных. Я избавляюсь от горячих точек с помощью вторичных индексов и подходящих условий WHERE, чтобы затронуть меньшее количество строк. Я планирую фоновые задания на временные интервалы с низким трафиком, чтобы действия пользователей были приоритетными.

тупиковые ситуации Я анализирую их, используя повторяющиеся шаблоны: одинаковые таблицы, меняющаяся последовательность, одинаковые строки. Я стандартизирую последовательность доступа в коде и хранимых процедурах. Логика повторных попыток с джиттером решает временные коллизии. По возможности я изолирую самые дорогие операции в очередях заданий. Это заметно снижает дисперсию и повышает воспринимаемую производительность.

Автоматизированные сигналы тревоги и рабочие процессы

Рутина Бьет по экшену: я анализирую журнал ежедневно или еженедельно, в зависимости от трафика и частоты релизов. Небольшой скрипт подсчитывает новые хиты за последние несколько минут и отправляет мне письмо, если пороговое значение увеличивается. Я также регулярно генерирую отчеты по pt-запросам и всегда слежу за топ-10. Я более внимательно слежу за днями выхода релизов. Это позволяет мне выявлять регрессии до того, как их заметят пользователи.

#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
ПОРОГ=100
RECENT_COUNT=$(awk -v cutoff="$(date -d '5 минут назад' '+%Y-%m-%dT%H:%M')" '/^# Time:/ { if ($3 >= cutoff) count++ } END { print count+0 }' "$LOG_FILE")
if [ "$RECENT_COUNT" -gt "$THRESHOLD" ]; then
    echo "ALERT: $RECENT_COUNT slow queries" | mail -s "MySQL Alert" [email protected]
fi

Прозрачность Я устанавливаю четкие обязанности: Кто реагирует на пики, кто корректирует индексы, кто тестирует релизы. Я обобщаю результаты в коротких журналах изменений. Таким образом, каждый член команды понимает, почему было сделано то или иное изменение и какой эффект оно дало. Структурированный процесс экономит время и предотвращает ложные тревоги.

Изображения ошибок и быстрые исправления

Полный текст Сканирование таблицы вызывает непропорционально высокую нагрузку. Сначала я проверяю, нет ли подходящего индекса в столбце фильтра или не блокирует ли выражение этот индекс. Я устраняю высокое время блокировки (lock_time), сокращая транзакции и уравнивая конкурирующие операции. Я избавляюсь от переполненных журналов с помощью log_throttle_queries_not_using_indexes и реалистичного long_query_time. Я немедленно оцениваю каждое исправление по сравнению с исходными показателями, чтобы успехи оставались заметными.

Хранение-Я выявляю узкие места, увеличивая время ожидания ввода-вывода и задержки на дисках во время пиков запросов. Затем я сокращаю количество ненужных операций записи, например, реже обновляя неизменяемые поля. Когда таблицы растут, я планирую стратегии архивирования или разбиения, чтобы горячие данные оставались в памяти. Для административных запросов в пиковые моменты я включаю log_slow_admin_statements, чтобы выявить тихие факторы затрат. Небольшие, целенаправленные исправления окупаются быстрее, чем крупные реорганизации.

Специальные возможности в управляемых и облачных средах

управляемый хостинг или облачные сервисы часто ограничивают доступ к файлам. В таких случаях я устанавливаю log_output в TABLE и оцениваю медленный журнал непосредственно из базы данных. В MySQL 8.0 я также использую SET PERSIST для постоянной установки параметров без прямого доступа к my.cnf. В группах параметров облака (например, для управляемых сервисов) я ввожу те же переменные и планирую окно обслуживания для перезапуска.

-- Если разрешено: постоянные настройки без перезапуска
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;
SET PERSIST log_output = 'TABLE'; -- Альтернатива FILE для ограниченного доступа к файлам

-- Оценка с log_output=TABLE
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 50;

Подсказка: При интенсивном трафике log_output=FILE может быть более производительным, поскольку ведение журнала в таблице создает дополнительные накладные расходы. Однако в ограниченных средах TABLE часто является единственным выходом. Тогда я устанавливаю более жесткие ограничения (например, min_examined_row_limit), чтобы сохранить контролируемый объем.

Ротация, хранение и защита данных

Вращение предотвращает заполнение диска журналами. Я провожу ротацию ежедневно или по размеру, сжимаю старые файлы и поддерживаю четкую политику хранения (например, 14 дней). После ротации я запускаю промывку журнала, чтобы MySQL аккуратно записывал данные в новый файл. Это обеспечивает стабильность анализа и работы.

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
  ежедневно
  ротация 14
  размер 100M
  сжать
  missingok
  notifempty
  создать 640 mysql adm
  postrotate
    test -x /usr/bin/mysqladmin || exit 0
    /usr/bin/mysqladmin flush-logs
  endscript
}

Защита данных является обязательным: медленные журналы могут содержать значения параметров. Я строго ограничиваю доступ (права на файлы, группы) и проверяю, не записываются ли в журнал конфиденциальные данные. При необходимости я работаю с привязкой параметров в приложении, чтобы в журнале не появлялся персонализированный простой текст. Для совместной работы я предпочитаю делиться агрегированными отчетами, а не необработанными журналами.

Используйте схему производительности и схему системы

Схема работы предоставляет метрики даже без активированного медленного журнала. Я активирую соответствующие потребители для утверждений, а затем анализирую системные представления. Преимущество: я могу видеть топ-дайджесты и распределение задержек почти в реальном времени, сгруппированные по схожим запросам.

-- Активируйте потребителя для истории выписок (насколько это возможно во время выполнения)
UPDATE performance_schema.setup_consumers
УСТАНОВИТЕ ENABLED = 'YES'
WHERE NAME IN ('events_statements_history', 'events_statements_history_long');

-- Быстрый обзор групп дорогостоящих запросов
SELECT имя_схемы, дайджест_текста, count_star,
       ROUND(sum_timer_wait/1e12, 3) AS total_s,
       ROUND(avg_timer_wait/1e9, 3) AS avg_ms,
       ROUND(max_timer_wait/1e9, 3) AS pmax_ms
FROM sys.statement_analysis
ORDER BY sum_timer_wait DESC
LIMIT 10;

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

EXPLAIN ANALYZE и трассировка оптимизатора

EXPLAIN ANALYZE (по состоянию на MySQL 8.0.18) дополняет оценки измеренным временем. Я сравниваю оценки строк с фактическими значениями и выявляю ошибки оптимизатора. В случае противоречивых планов я анализирую трассировку оптимизатора, чтобы понять, почему тот или иной индекс не был выбран.

-- План с измеренными значениями
ОБЪЯСНИТЬ ПРОАНАЛИЗИРОВАТЬ
SELECT o.id, o.created_at
FROM заказы o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

-- Отслеживание решений оптимизатора
SET optimizer_trace="enabled=on";
SELECT ...; -- анализируемый запрос
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimiser_trace="enabled=off";

РезультатЕсли оценки сильно завышены, я обновляю статистику (ANALYZE TABLE), добавляю гистограммы или меняю форму индексов/запросов, чтобы селективность начала действовать раньше.

Перепишите шаблоны, которые почти всегда работают

ИЛИ СОЕДИНЯТЬ ВСЕХМножественные условия OR на разных столбцах часто не позволяют использовать индекс. Я разделяю их на два выборочных запроса и объединяю результаты, если можно исключить дубликаты.

-- До:
SELECT * FROM t WHERE a = ? OR b = ?

-- Лучше:
(SELECT * FROM t WHERE a = ?)
UNION ALL
(SELECT * FROM t WHERE b = ? AND a  ?);

ПагинацияС увеличением OFFSET/LIMIT становится дорого. Я переключаюсь на пагинацию по ключам и использую подходящий ключ сортировки (в идеале - индексированный и монотонный).

-- Дорого:
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

-- Лучше (набор ключей):
SELECT id, title
FROM posts
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 50;

Композитные индексы: Порядок подсчета. Я сортирую столбцы в индексе в соответствии с селективностью и шаблоном запроса (сначала фильтр WHERE, затем сортировка столбцов). Цель - создать покрывающий индекс, позволяющий избежать файлового сортировки и поиска в таблице.

Функциональные и генерируемые индексы в MySQL 8

Выражения в WHERE/JOIN часто блокируют индексы. В MySQL 8.0 я специально индексирую выражения или работаю со сгенерированными столбцами для создания sargability. Это особенно полезно для CAST для числовых метазначений или полей JSON.

-- Пример: числовая сортировка по текстовому полю
ALTER TABLE product ADD COLUMN price_num DECIMAL(10,2)
  GENERATED ALWAYS AS (CAST(price AS DECIMAL(10,2))) СОХРАНЕНО;
CREATE INDEX idx_product_price_num ON product(price_num);

-- Запрос без CAST и с индексом
SELECT * FROM product
WHERE price_num BETWEEN 10 AND 50
ORDER BY price_num;

ПрактикаЯ проверяю, действительно ли работает новый индекс (EXPLAIN), и измеряю эффект в журнале медленных операций. Сгенерированные столбцы также помогают эффективно фильтровать префиксы или нормализованные варианты (LOWER(email)).

Более целенаправленный подход к шаблонам CMS/магазина

Мета-таблицы (например, wp_postmeta) выигрывают от комбинированных индексов на (post_id, meta_key) или (meta_key, meta_value). Для частых фильтров по meta_value_numeric я использую сгенерированные столбцы, как описано выше, вместо CAST в каждом запросе. Я ускоряю страницы поиска, отбрасывая лишнее (легкая денормализация) и делая доступ к чтению удобным для индекса.

-- Типично для WordPress: быстрый доступ к метаданным поста
CREATE INDEX idx_postmeta_postid_metakey ON wp_postmeta(post_id, meta_key);
CREATE INDEX idx_postmeta_metakey_metavalue ON wp_postmeta(meta_key, meta_value(100));

Касса-Я оптимизирую пути для минимального времени блокировки: короткие транзакции, только необходимые строки и индексы точно для используемых условий WHERE. Для отчетов я планирую асинхронную агрегацию (промежуточные таблицы), чтобы не замедлять потоки пользователей.

Пределы медленного журнала и дополнительные метрики

Множество небольших, быстрых запросов не заметны в медленном журнале, но увеличивают нагрузку. Поэтому я также отслеживаю пропускную способность (запросы/сек), 95-й/99-й процентили и долю запросов без индекса. В инструментах Performance Schema или APM я распознаю N+1 паттерны, которые затем решаю с помощью джойнов, процессов пакетной загрузки или кэширования.

Выборка полезен, когда журналы становятся слишком большими. Я немного увеличиваю long_query_time или устанавливаю min_examined_row_limit, чтобы включать только релевантные запросы. Важно: всегда отмечайте изменения, чтобы временные ряды оставались сопоставимыми.

Метод работы: От выводов к устойчивому совершенствованию

Базовый уровень Сначала я сохраняю отчет "до" (временное окно, трафик, конфигурация). Затем я оптимизирую одно семейство запросов за другим и сравниваю идентичные временные окна. Каждое исправление документируется в репозитории (Что? Почему? Измеренное значение до/после?). Таким образом, успехи можно отследить и предотвратить регрессию.

# Грубая процедура (пример)
1) pt-query-digest --since '7d' slow-query.log > baseline.txt
2) Выберите 3 лучших дайджеста запросов (по общему времени)
3) EXPLAIN/EXPLAIN ANALYZE, выработка предложений по индексу и перезаписи
4) Сгенерировать тестовые данные, смоделировать профиль нагрузки
5) Развертывание с мониторингом (более жесткие ограничения в течение 48 часов)
6) Сравнительный отчет: pt-query-digest --since '48h' > after.txt
7) Документирование результатов, планирование следующей части

Стабильность плана Я слежу за работой оптимизатора с течением времени: если планы меняются (новые версии, измененная статистика), я проверяю гистограммы, ANALYZE TABLE и ландшафт индексов. Я устанавливаю хинты выборочно и документированно, чтобы не нагружать оптимизатор надолго.

Резюме в виде четких шагов

Начало Это означает: активировать журнал, установить разумные предельные значения, собрать данные за первую неделю. Затем я подвожу итоги с помощью pt-query-digest, расставляю приоритеты по общему времени и дисперсии и выбираю лучшие драйверы. Я оптимизирую запросы с помощью EXPLAIN, sargable conditions и подходящих индексов и контролирую блокировку с помощью более коротких транзакций. На стороне сервера я настраиваю буферы, временные таблицы и стратегии промывки соответствующим образом. Наконец, я автоматизирую аварийные сигналы и регулярно повторяю цикл - это позволяет поддерживать быстродействие базы данных даже при росте трафика и объемов данных.