Все мы знаем простую формулу: быстрый сайт = счастливые пользователи, высокая оценка Google-статистики и увеличение количества переходов. Возможно, Вам и кажется, что Ваш сайт работает настолько быстро, насколько ему позволяет быть быстрым WordPress – Вы же видели те списки лучших практик настройки сервера, улучшения производительности кода и так далее – но всё же, разве это предел?
Используя динамические базы данных сервисов типа WordPress, в конечном итоге перед Вами предстанет проблема: запросы к базе замедляют работу сайта.
В рамках этого поста я постараюсь научить Вас определять проблемные места, понимать их причину и предложу некоторые быстрые решения и другие программные подходы для ускорения работы. В качестве примера мы будем использовать действующие примеры работы с базой, замеченные на одном из сайтов.
Идентификация
Первый шаг на пути повышения производительности - это обнаружение проблемных SQL-запросов. И на сцену выходит Query Monitor – по-настоящему незаменимый плагин для мониторинга работы запросов. Особенность его в том, что плагин показывает детальную информацию о каждом запросе, позволяя к тому же отфильтровать их, исходя из порождающего участка кода (включается подсветка дублированных запросов).
Если же по какой-то причине Вы не желаете устанавливать отладочный плагин, вы можете в качестве опции включить MySQL Slow Query Log, логирующий все запросы, требующее длительное время для выполнения. К тому же конфигурация и настройка места логирования относительно проста. Так как это серверное дополнение, падение производительности будет заметно в меньшей мере, чем если бы вместе с отладочным плагином непосредственно на сайте. Однако по мере отсутствия необходимости, MySQL Slow Query Log стоит отключать.
Понимание
Как только вы находите затратный по времени запрос, который было бы неплохо улучшить, встает следующий вопрос: почему он работает медленно? К примеру, во время разработки одного сайта был обнаружен запрос, работающий целых 8 секунд!
Для поддержки магазина плагинов мы использовали WooCommerce и кастомизированную версию WooCommerce Software Subscriptions плагина. Задача приведенного выше запроса была получить все подписки пользователя, оперируя его идентификационным номером. WooCommerce обладает своеобразной комплексной моделью данных, при использовании которой наблюдалось следующее: хотя запрос и хранится как пользовательские пост-типы, идентификатор пользователя хранится в пост-мета данных, а не в post_author, как должно было бы быть. Также было найдено несколько связок с пользовательскими таблицами, созданными плагином. Итак, как же нам уберечь себя от подобных ошибок?
MySQL – Ваш друг
MySQL обладает весьма полезным выражением DESCRIBE, которое используется для вывода информации о структуре таблицы (такой, как столбцы, типы данных, дефолты). К примеру, если Вы выполните команду DESCRIBE wp_postmeta, Вы увидите следующий результат:
Это, конечно, хорошо, но, возможно, Вы уже знали об этом. Но знали ли Вы, что DESCRIBE также может быть использован в качестве префикса для SELECT, INSERT, UPDATE, REPLACE и DELETE? Широкой общественности это так же известно, как синоним EXPLAIN, который дает нам детальную информацию, как именно та или иная команда будет выполнена.
Вот результат для нашего проблемного запроса:
На первый взгляд, понять это не слишком-то просто. Однако при более детальной расшифровке задача упрощается.
Наиболее важной колонкой считается type, так как она описывает связь между таблицами. Если вы замечаете слово ALL, это значит, что MySQL читает всю таблицу с диска, нагружая центральный процессор. Сие также известно под термином «полное сканирование таблицы» - позже мы вернемся к этому.
Колонка row также хороший индикатор того, что нужно, собственно говоря, MySQL сделать. Эта колонка наглядно демонстрирует, сколько рядов было прочитано, дабы найти результат.
Ключевое слово EXPLAIN также дает более полную информацию, полезную для дальнейшей оптимизации запросов. К примеру, таблица pm2 (wp_postmeta) говорит нам о том, что мы используем Using filesort, так как нам нужно отсортировать результаты по возрастанию (ORDER BY).
Визуальное изучение
Здесь стоит упомянуть о таком полезном инструменте, как MySQL Workbench. Так как для баз данных, работающих на версии MySQL 5.6 и выше, результаты EXPLAIN формируются в виде JSON, MySQL Workbench конвертирует JSON в визуальное представление.
Утилита автоматически обращает Ваше внимание на проблемные запросы. На картинке мы ясно можем видеть, что wp_woocommerce_software_licences вызывает негодование.
Решение
Упомянутая часть запроса проводит полное сканирование таблицы, чего Вам, собственно говоря, по мере возможности стоит избегать. К тому же тут используется неиндексированная колонка order_id в качестве ссылки между таблицами wp_woocommerce_software_licences и wp_posts. Подобная ситуация – общая причина большинства «медленных» запросов. Которая, однако, решается просто.
Индексы
Order-id - достаточно важная часть идентификационной информации. Потому, если мы строим запрос подобным образом, мы должны иметь при себе индекс колонки. В противном случае MySQL в буквальном смысле просканирует ВСЕ рядки таблицы, пока не найдет нужный. Давайте добавим индекс и проверим, что из этого получится:
Видео курсы по схожей тематике:
Невероятно, но нам удалось урезать порядка 5 секунд выполнения запроса!
Знайте свой запрос
Проверяйте запрос – команда за командой, подзапрос за подзапросом. Не делает ли он того, чего нам не нужно? Можем ли мы что-то оптимизировать?
В этом случае, ограничивая пост-типы shop_order, мы связываем таблицы лицензий с таблицами постов при помощи order_id. Повышая интеграцию данных, подобным образом мы убеждаемся, что используются только корректные порядки записей. Однако на самом деле это только внешняя часть запроса. Довольно опасно иметь строку software license в таблице, владеющей связанным с порядком WooCommerce order_id в пост-таблицах. Причина этому – связка с PHP-кодом плагина. Давайте удалим зависимость и посмотрим на результат:
Конечно, это не очень много, но теперь запрос работает до 3 секунд вместо 8!
Кэшируйте все, что только можно!
Если на Вашем сервере не установлен MySQL query caching, тогда пора установить его. Это позволит MySQL вести запись всех исполняемых команд – совместно с их результатом. Кэш же, в свою очередь, не «устаревает», так как MySQL обновляет кэш при изменении таблиц.
Query Monitor обнаруживает, что наш запрос запускается 4 раза на одной странице. И, хотя query caching – полезная штука, дублирующие запросы должны быть удалены. Статическое кэширование в PHP – это достаточно простое и эффективное решение проблемы. По сути, Вы просто перехватываете результат чтения из базы и заносите его в статическое свойство класса. Когда будет вызван определенный подзапрос, вы просто используете уже загруженную информацию из статического свойства:
Кэш имеет такое понятие, как время жизни запроса. Если же Вам необходимо иметь постоянную информацию из кэша, тогда, возможно, Вам стоит воспользоваться постоянным Object Cache`ом. Однако в этом случае Ваш код должен сам описывать работу с кэшем.
Отказывайтесь от шаблонного мышления!
Помимо этих, существует великое множество других техник по улучшению производительности запросов, которые потребуют операции посложнее, чем просто правка запроса или дописывание индекса. Одна из наиболее медленных операций нашего запроса – это необходимость связывать покупателя с таблицей товаров, причем для каждого покупателя. А что, если просто один раз связать все, что нам нужно, и не заморачиваться этим в дальнейшем, просто загружая из таблицы всю информацию, связанную с покупателем?
Мы можем создать таблицу, которая будет содержать в себе информацию о лицензии, вместе с идентификаторами пользователей и товаров для всех лицензий. При необходимости нам нужно будет только дать запрос для одного пользователя. Для этого Вам предстоит пересобрать таблицу при помощи MySQL triggers на базе INSERT/UPDATE/DELETE. Однако подобная операция заметно увеличит производительность системы в целом.
Подобным образом, в случае если количество связей замедляет выполнение запросов, возможно, Вам стоит разбить запрос в два или больше предложений и исполнять их раздельно при помощи PHP. После чего просто собрать и отфильтровать полученную информацию внутри кода. Что-то подобное реализовано в Laravel при помощи eager loading в Eloquent.
Бесплатные вебинары по схожей тематике:
WordPress иногда может замедлять запросы на wp_posts таблице, в случае если у Вас хранится много информации и разных пользовательских пост-типов. Если Вы считаете, что пост-типы замедляют запросы, возможно, Вам стоит задуматься об использовании кастомных таблиц.
Подведем итоги
При помощи подобных нехитрых приемов оптимизации запросов нам удалось снизить время работы запроса с 8 до примерно 2 секунд. К тому же было снижено количество вызовов запрос с 4 до 1.
Искренне надеемся, что сей гайд будет полезным для работы с запросами. Внешне оптимизация может казаться чем-то устрашающим, но как только Вы попробуете это сделать, Ваши маленькие победы развеют подобные суждения.
Автор перевода: Евгений Лукашук
Статьи по схожей тематике
