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

Дата публикации:Июнь 18, 2015

Медленные SQL-запросы могут негативно отразиться на производительности WordPress-сайта. Иногда медленные запросы – это результат плохо сформированного SQL-кода, который должен быть реализован несколько иным путем. И в некоторых ситуациях медленные запросы сначала являлись быстрыми – но с ростом возраста сайта запросы становились все медленнее и медленнее, и уже начинали отставать от расширяющейся базы данных.

Вне зависимости от того, каким образом ваш SQL-код стал медленным, давайте взглянем на некоторые пути поиска и устранения проблемных запросов в WordPress.

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

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

  1. Определяем, какие запросы на самом деле медленные.
  2. Ищем код, который генерирует и исполняет их.

Давайте рассмотрим два плагина и одно SaaS-решение, которые помогут нам найти медленные запросы.

Query Monitor

Query Monitor – плагин, выводящий различную информацию о текущей странице. В дополнение к многочисленным данным о внутренних механизмах WordPress, плагин выводит детальную статистику по следующим пунктам:

  • Сколько запросов произошло по этому вызову
  • Какие запросы на странице заняли больше всего времени
  • Какие функции выполнялись дольше всего в SQL-запросах
  • Какие запросы шли от плагинов, тем и ядра WordPress

query-monitor-table

Query Monitor выделяет медленные запросы с помощью страшного красного текста, который упрощает выявление проблемного SQL:

red-scary-sql

Debug Bar

Еще один великолепный инструмент для поиска медленного SQL– это старый добрый плагин Debug Bar. Debug Bar выводит информацию о внутренних механизмах WordPress, когда вы загружаете страницу с такими данными, как:

  1. Параметры WP_Query
  2. Информация вызова (включая соответствие правил перезаписи)
  3. SQL-запросы, генерируемые текущей страницей

Чтобы активировать третий пункт в Debug Bar (SQL-отслеживание), убедитесь в том, что SAVEQUERIES включена на вашем сайте – обычно это делается в файле wp-config.php:

if ( ! defined( 'SAVEQUERIES' ) ) {
  define( 'SAVEQUERIES', true );
}

Предостережение: SAVEQUERIES отражается на производительности вашего сайта, и, скорее всего, не должна использоваться на рабочем сайте. Используйте ее только на разрабатываемом сайте.

Поиск медленного SQL в Debug Bar не так прост. К примеру, плагин не имеет сортируемых таблиц и не подсвечивает медленные запросы. Debug Bar обеспечивает трассировку функций, которая позволяет выявить источник запроса.

debug-bar-function-list

Здесь приведен список загружаемых файлов и функций, которые приводят к выполнению запроса. В основном вам будет интересна самая последняя запись в списке; именно там выполняется самый медленный запрос и именно с него вам нужно будет начать свой поиск. Также удобным является наличие контекста для каждой отдельной функции.

NewRelic

NewRelic – это сервис, который измеряет и отслеживает произвольность вашего веб-приложения, включая WordPress. Сервис предлагает массу разной информации о производительности вашего сайта. Очень легко потеряться в данных, которые предлагает NewRelic, начиная с детального выполнения кода и заканчивая пошаговыми отчетами по SQL-запросам.

newrelic

Есть два крупных различия между NewRelic и плагинами, упомянутыми выше:

  1. NewRelic отображает более детальную информацию о производительности вашего PHP, вплоть до миллисекунд, потраченных на выполнение каждой функции.
  2. NewRelic отслеживает каждый запрос к вашему сайту в фоновом режиме, т.е. вы можете проанализировать информацию позже для поиска медленного SQL. Плагины выводят информацию только для текущей страницы.

Стоит отметить, что NewRelic имеет бесплатный план, который позволяет вывести общую информацию о производительности вашего сайта, однако для получения всех важных деталей вам понадобится перейти на платный тариф. Именно в этом плане вы сможете отслеживать отдельные запросы и находить медленный SQL.

Обнаружение медленных запросов с помощью EXPLAIN

До недавнего момента мы рассматривали инструменты для поиска медленных запросов. Давайте теперь рассмотрим, почему эти запросы становятся медленными.

В этом нам поможет MySQL ключ EXPLAIN. Он позволит понять, что произошло. Добавление EXPLAIN в начало запроса покажет, как MySQL выполняет запрос. В случае с более сложными запросами EXPLAIN поможет выявить медленные участки в ваших SQL – к примеру, медленные подзапросы или неэффективные процессы.

К примеру, если у вас есть запрос следующего вида:

SELECT slow_column FROM slow_table

Вы можете добавить к нему EXPLAIN, просто выполнив следующее:

EXPLAIN SELECT slow_column FROM slow_table

Вот как выглядит вывод EXPLAIN в phpMyAdmin:

mysql-explain

Я не совсем понимаю, как работают внутренние механизмы MySQL, но при этом запуск EXPLAIN для запросов дает мне понимание того, как MySQL выполняет мой SQL-запрос. Использует ли запрос индекс? Сканирует ли он всю таблицу? Даже для простых запросов EXPLAIN обеспечивает некоторую информацию, позволяющую понять, как все работает.

Вы можете запустить EXPLAIN либо из командной строки MySQL, либо через ваш предпочтительный MySQL-инструмент.

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

Теперь, когда мы знаем, что наш запрос является медленным, и EXPLAIN показал нам, почему запрос является медленным, самое время посмотреть, как мы можем исправить эти проблемы.

Вариант 1. Изменение запроса.

На сайте CSS-Tricks у нас есть запрос, который выполняется очень медленно – этот запрос является частью мета-панели Custom Fields. Вот сам SQL:

SELECT meta_key
FROM wp_postmeta
GROUP BY meta_key
HAVING meta_key NOT LIKE '\\_%'
ORDER BY meta_key
LIMIT 100

Данный фрагмент кода содержит SQL, который позволяет получить список meta_keys из таблицы wp_postmeta. Интересующие нас meta_keys не должны начинаться с подчеркивания «_». Оператор GROUP BY означает, что каждый результат является уникальным.

Выполнив этот запрос 5 раз, мы получим следующие данные:

1.7146 сек

1.7912 сек

1.8077 сек

1.7708 сек

1.8456 сек

Могли бы мы написать другой запрос, чтобы получить тот же самый результат? Нам нужно выбрать уникальные meta_keys. Синоним к слову «unique» (уникальный) – distinct, и, как оказалось, в SQL есть такой оператор!

Используя оператор DISTINCT, мы можем сделать следующее:

SELECT DISTINCT meta_key 
FROM wp_postmeta 
WHERE meta_key NOT LIKE '\\_%' 
ORDER BY meta_key

Выполнение нашего измененного запроса несколько раз дает следующие результаты:

0.3764 сек

0.2607 сек

0.2661 сек

0.2751 сек

0.2986 сек

Данное сравнение демонстрирует существенное улучшение!

Вариант 2. Добавление индекса.

Когда вы выполняете запрос SQL к стандартной таблице MySQL, MySQL сканирует всю таблицу, чтобы определить, какие строки соответствуют данному запросу. Если ваша таблица очень большая, то в таком случае сканирование займет довольно много времени.

В этом случае неплохо помогают индексы MySQL. Индексы берут данные из таблицы и располагают их так, чтобы эти данные было проще всего найти. Структурируя данные таким образом, индексы помогают избавиться от длительного сканирования, которое делает MySQL для каждого запроса.

Индексы могут быть добавлены к отдельным столбцам или к группе столбцов. Синтаксис выглядит следующим образом:

CREATE INDEX wp_postmeta_csstricks ON wp_postmeta (meta_key)

С индексом для meta_key исходный SQL-запрос будет выполняться следующим образом:

0.0042 сек

0.0024 сек

0.0031 сек

0.0026 сек

0.0020 сек

Улучшение налицо!

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

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

Мы знаем, что у нас имеются медленные запросы. Вместо того чтобы переписывать их, мы можем просто сохранить результаты запроса. Таким образом, мы ограничим частоту выполнения запроса, а также получим «быстрый ответ».

Чтобы кэшировать запрос, нам понадобится WordPress Transients API. Transients используются для хранения результатов сложных операций, таких как:

  • Запросы к внешним сайтам (к примеру, получение последних записей Facebook)
  • Медленные блоки обработки (к примеру, поиск длинных строк с помощью регулярных выражений)
  • Медленные запросы к базе данных

Хранение результатов запроса в transients будет выглядеть следующим образом:

if ( false === ( $results = get_transient( 'transient_key_name' ) ) ) {
  $results = ...; // Do the slow query to get the results here
  // 60 * 60 is the expiration in seconds - in this case, 3600 seconds (1 hour)
  set_transient( 'transient_key_name', $results, 60 * 60 ); 
}

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

Если у вас есть запрос, результаты которого меняются не так часто, использование transients – прекрасный способ обойти частое обращение к базе данных.

Выбор подхода

Мы рассмотрели три варианта, и кроме них есть еще примерно 17 способов решения проблемы с медленными запросами. Какой подход лучше всего выбрать?

Когда я работаю с чужим кодом, я предпочитаю ориентироваться на принцип программистов: «Выбирай самое простое решение, которое может работать».

Первый вариант (переписывание запроса) позволил получить превосходные результаты, однако как быть, если переписанный запрос не всегда выдает те же самые результаты? Мы можем неосознанно нарушить код путем замены запросов.

Второй вариант (добавление индекса) не всегда возможен, что зависит от таблицы и столбцов, используемых запросом. В случае с базовыми таблицами WordPress вам нужно будет подумать о возможных побочных эффектах индексации:

  • Поддерживает ли обновление ядра дополнительные индексы?
  • Не замедлит ли добавление индекса другие запросы, такие как INSERT и UPDATE?

Третий вариант (кэширование результатов через transients) оказывает минимальное воздействие – мы не меняем исходный запрос и нам не нужно изменять структуру базы данных.

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

Источник: css-tricks.com

Поделиться

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Получать новые комментарии по электронной почте. Вы можете подписаться без комментирования.