Оптимизация базы данных в WordPress: добавляем индексы к своим (кастомным) таблицам

Индексирование базы данных – практика, про которую часто забывают, но которая очень полезна при управлении кастомными таблицами.

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

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

Выполнение правильного индексирования ваших собственных таблиц демонстрирует хорошие результаты. В недавнем тесте с 1 млн записей, с запросами около 100 000 записей за раз, время выполнения запроса сократилось примерно на 150% при реализации индексов.

Почему вам могут потребоваться индексы в БД WordPress

Многие разработчики WordPress не очень хорошо знакомы с индексацией таблиц БД. Возможно, дело в том, что стандартные таблицы WP уже правильно проиндексированы по умолчанию, а потому сложные запросы уже оптимизированы (автоматические черновики, удаленные записи, неодобренные комментарии и т.д.).

В официальном руководстве разработчиков плагинов не упоминаются кастомные таблицы; рекомендуется использовать произвольные типы записей. Однако мы знаем, что произвольные типы записей имеют свои ограничения. В некоторых случаях удобнее создать свою таблицу для хранения данных плагина. В исходном кодексе WordPress все еще есть руководство по созданию таблиц с помощью плагинов. Юмор в том, что если вы уже создавали таблицы в MySQL со столбцом primary key (его обычно называют ID), MySQL формировал индекс для этого столбца (по умолчанию PRIMARY).

В руководстве нет информации о добавлении индексов таблиц к полям, для которых могут выполняться запросы WHERE и JOIN.

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

С другой стороны, если таблица содержит много данных, и при этом запросов на запись (write) не так много (к примеру, запросов INSERT и UPDATE), индексы могут существенно повысить производительность запросов SELECT, поскольку полного сканирования таблицы не произойдет. Хорошим примером является сайт WooCommerce с множеством заказов, накопленных за годы работы.

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

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

Чтобы как-то проиллюстрировать это, я создал несколько кастомных таблиц с помощью плагина на моем локальном WordPress-сайте. Таблицы содержат записи для интернет-магазина книг и имеют следующую структуру.

CREATE TABLE `wp_hfm_categories` (
    `id` mediumint NOT NULL,
    `slug` tinytext NOT NULL,
    `description` text NOT NULL,
    `title` text  NOT NULL
);

CREATE TABLE wp_hfm_books (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    category_id mediumint(9) NOT NULL,
    author_id mediumint(9) NOT NULL,
    title tinytext NOT NULL,
    description text NOT NULL,
    url varchar(100) DEFAULT '' NOT NULL,
    PRIMARY KEY  (id)
) ;

В таблицу wp_hfm_categories я внес тысячу записей (рубрик), а в таблицу wp_hfm_books – миллион книг. Это означает, что в каждой рубрике содержится примерно 100 000 книг.

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

<?php
$time_start = microtime( true );

$connection = mysqli_connect( "localhost", "root", "password", "wordpress" );

$query = "SELECT * FROM wp_hfm_books WHERE category_id = 1";

$time_end   = microtime( true );
$setup_time = round( $time_end - $time_start, 5 );

$time_start = microtime( true );

$result = mysqli_query( $connection, $query );

$time_end   = microtime( true );
$query_time = round( $time_end - $time_start, 5 );

if ( ! $result ) {
    echo "Query failed!" . '</br>';
    die();
}

$time_start = microtime( true );

$book_list = '';
while ( $book = $result->fetch_object() ) {
    $book_list .= '<p>' . $book->title . '</p>';
}

$time_end       = microtime( true );
$execution_time = round( $time_end - $time_start, 5 );

echo "Setup in {$setup_time} seconds!" . '</br>';
echo "Query in {$query_time} seconds!" . '</br>';
echo "Books gathered in {$execution_time} seconds!" . '</br>';

echo $book_list;

Финальные результаты выполнения:

Setup in 0.00031 seconds!
Query in 0.53784 seconds!
Books gathered in 0.00497 seconds!

Как видно из результатов, взаимодействие с БД составляет почти все время загрузки страницы и превышает 0.5 с на отметке в 1 млн записей.

С чем я столкнулся в реальности

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

Фид – важный элемент любого успешного подкаста. Это XML-данные, передаваемые на такие платформы, как Apple Podcasts, Spotify, Amazon Music. Каждая платформа запрашивает RSS-фид через некоторые промежутки времени, проверяя наличие новых эпизодов и обновляя подкаст-шоу.

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

По мере расширения бизнеса мы столкнулись с проблемой: RSS-каналы для крупных шоу не обновлялись должным образом. Сначала мы решили применить кэширование Redis для фидов. Какое-то время оно нормально работало, но вскоре проблема вернулась, когда у нас появилось больше шоу и эпизодов. Во время мозгового штурма один из разработчиков заметил, что таблицы, запрашиваемые для формирования фида, неправильно индексировались. Мы потратили около получаса на то, чтобы подтвердить корректные индексы и добавить их. Сразу же время запроса снизилось до десятой части исходного времени, и проблема исчезла.

Давайте рассмотрим наш пример с книгами в реальной среде. Я создал шаблон страницы в дочерней теме WP, который выводит некоторые данные для каждой книги.

<?php
/**
 * Template Name: Books Template
 *
 * @package WordPress
 * @subpackage Twenty_TwentyTwo_Child
 * @since 1.0.0
 */
$execution_time = 0;
$time_start = microtime(true);

get_header(); ?>

    <header class="page-header alignwide">
        <h1 class="page-title">Books</h1>
    </header><!-- .page-header -->


<?php
$books = hfm_books_get_books();
foreach ( $books as $book ) {
    ?>

    <article id="post-<?= $book['id'] ?>" <?php post_class(); ?>>

        <header>
            <h1><a href="<?= $book['url'] ?>"><?= $book['title'] ?></a></h1>
        </header><!-- .entry-header -->

        <div>
            <?php echo $book['description']; ?>
        </div><!-- .entry-content -->

    </article>

    <?php
}

get_footer();

Функция, запрашивающая книги, использует тот же запрос, что и наш предыдущий тест:

function wp_books_get_books() {
    global $wpdb;
    $query = "SELECT * FROM wp_hfm_books where category_id = 1;";
    return $wpdb->get_results($query, ARRAY_A);
}

Время создания контента страницы в моей локальной среде составило 2,79 с, общее время запроса – 0,52 с (все остальные запросы выполняются для отображения страницы), объем потребляемой памяти – 20 Мб.

Теперь вы, вероятно, подумали: «Хорошо, но я могу ускорить все это, добавив пагинацию и ограничив кол-во выводимых результатов на странице». Верно. Но, допустим, вы укажете 10 записей на странице. Для одного пользователя этот запрос, возможно, ускорится. Но как быть, если тот же запрос будут совершать 1000 пользователей одновременно? А 10000? Как насчет 1000 пользователей, каждый из которых запрашивает книги для каждой из 1000 рубрик? Фактически это 1 млн записей!

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

Как отследить медленные запросы к БД в WordPress

Есть два способа понять, являются ли запросы к БД корнем проблемы. Вы можете установить плагин Query Monitor. Он имеет массу разных инструментов, но главная его особенность – просмотр всех запросов к БД, из которых состоит запрос страницы.

Если по каким-то причинам вы не можете установить этот плагин или вы работаете не с WP-сайтом, вы можете включить журнал медленных запросов MySQL (Slow Query Log). В журнал будут вноситься все запросы, которые занимают больше времени, чем значение переменной long_query_time (по умолчанию 10 с). Для этого достаточно включить журнал с помощью переменной slow_query_log:

SET GLOBAL slow_query_log = 'ON';

Вы можете также настроить другое значение long_query_time:

SET GLOBAL long_query_time = 1; ## log any queries slower than 1 second

Этот файл журнала можно просмотреть вручную или с помощью команды mysqldumpslow.

Если вы выяснили, что проблема заключается в запросах к БД, вы можете попробовать некоторые из распространенных предложений по очистке базы данных:

• Удалите ненужные данные из таблиц WP. Часто это данные, оставленные удаленными плагинами WP – их настройки, параметры и метаданные, хранящиеся в wp_options, wp_posts, wp_postmeta и wp_commentmeta, а также в кастомных таблицах. Среди ненужных данных могут быть и неиспользуемые таксономии. Базовые таксономии WordPress – это метки и рубрики.
• Удалите данные, которые вам больше не требуются: спам-комментарии, ревизии постов, неиспользуемые метки и т.д.
• Установите и активируйте плагины для оптимизации БД, такие как WP-Optimize или Advanced Database Cleaner. Оба предлагают опции по поддержке мультисайтов.
• Используйте команду OPTIMIZE TABLE в MySQL.
• Измените движки таблиц.
• Отключите пингбэки и трэкбэки.

Если же у вас имеются кастомные таблицы без индексов, то единственный способ их ускорения – это добавление индексов.

Диагностика проблем с индексацией базы данных WP

Вернемся к нашим данным books, которые я показывал ранее. Представим, что мы выяснили, что запросы книг замедляют работу сайта.

Чтобы начать диагностику проблемы, нам понадобится «сырой» SQL-запрос:

SELECT * FROM wp_hfm_books where category_id = 1;

Вход в терминал MySQL и выполнение этого запроса занимает на моей машине 0.48 с. Чтобы понять, что занимает так много времени, мы можем использовать оператор EXPLAIN в MySQL. Выполним запрос еще раз, но теперь добавим explain в начало:

EXPLAIN SELECT * FROM wp_hfm_books where category_id = 1;

Выполнение этого запроса даст много информации, но важными полями для нас являются possible_keys и key . Здесь мы видим, какие индексы (ключи) доступны для этого запроса и какой индекс фактически используется. Важно отметить, что поле rows  сообщает нам, сколько строк было просканировано во время выполнения данного запроса. Для нашего запроса я получил следующие результаты:

+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | wp_hfm_books | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 976402 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

В нашем случае индексы не использовались, а потому сканирование охватило 976 402 строки. Неудивительно, что запрос занял так много времени.

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

Теперь, когда стало понятно, что проблема связана с индексированием, давайте добавим индекс к столбцу category_id  для таблицы wp_hfm_books . Есть несколько способов сделать это.

Первый способ: добавить индекс непосредственно к таблице. Добиться этого можно путем выполнения запроса ALTER TABLE в терминале MySQL или с помощью инструментов управления БД MySQL, таких как TablePlus. Также это можно осуществить в phpMyAdmin. Запрос ALTER TABLE для таблицы с книгами будет выглядеть следующим образом:

ALTER TABLE wp_hfm_books ADD INDEX category_id_index (category_id);

Здесь мы добавляем индекс category_id_index к полю category_id.

Если вы работаете с кастомными таблицами в WordPress, мы рекомендуем добавлять индексы к вашей функции установки, которая создает таблицу. Нужно добавить INDEX после PRIMARY KEY в запросе:

CREATE TABLE wp_hfm_books (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    category_id mediumint(9) NOT NULL,
    author_id mediumint(9) NOT NULL,
    title tinytext NOT NULL,
    description text NOT NULL,
    url varchar(100) DEFAULT '' NOT NULL,
    PRIMARY KEY  (id),
    INDEX category_id_index (category_id)
) ;

Если эта таблица уже используется в одной (или нескольких) средах в продакшне, вам потребуется выполнить шаг обновления. Для этого мы рекомендуем внедрить нашу миграционную библиотеку, вдохновленную миграциями Laravel.

В Laravel вы можете создать новую миграцию и добавить индекс к таблице с помощью метода $table->index(). Обратите внимание, что всегда нужно делать полный бэкап БД до выполнения любых запросов, изменяющих таблицы.

Schema::table('books', function (Blueprint $table) {
    $table->index('category_id', ‘category_id_index’);
});

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

Что мы получили в итоге

Какое влияние индекс оказывает на запрос? Если вы выполните запрос снова, вы должны увидеть заметное улучшение. В моей локальной среде время выполнения запроса сократилось до 0,08 с. Это в пять раз быстрее, чем раньше. Если вы снова запустите оператор EXPLAIN, вы увидите что-то подобное:

+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------+-------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys     | key               | key_len | ref   | rows  | filtered | Extra |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | wp_hfm_books | NULL       | ref  | category_id_index | category_id_index | 3       | const | 10189 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Теперь запрос использует новый индекс category_id, и благодаря ему количество строк, которые были просканированы, снизилось с 976 402 до 10 189. Отлично!

Отметим, что прирост производительности будет зависеть не только от количества данных в таблице, но и от типа выполняемого запроса. К слову говоря, мы могли бы просто добавить в таблицу внешний ключ category_id. Внешние ключи автоматически индексируют столбец, а также обеспечивают целостность данных. Правда, в ядре WP внешние ключи не поддерживаются (из-за движка MyISAM), потому лучше избегать их, чтобы сохранить согласованность данных.

Составные индексы

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

SELECT * FROM `books` WHERE category_id` = 1 AND author_id = 1

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

ALTER TABLE wp_hfm_books ADD INDEX `by_category_and_author_index` (`author_id`, `category_id`);

Если вы делаете фильтрацию по нескольким полям, выполнение запросов с составным индексом улучшает производительность запроса примерно на 50%. Однако имейте в виду следующее:

• Чем больше индексов вы добавите, тем больше будет размер вашей БД и тем выше будет использование памяти (поскольку MySQL пытается хранить индексы в памяти).
• MySQL может сам использовать множественные индексы по своему усмотрению.
• Если добавить оба индекса в пример выше, EXPLAIN отобразит только отдельный индекс category_id, который будет использоваться вместо составного индекса, даже если в моем запросе имеются разные условия WHERE.
• Порядок полей в составном индексе также может иметь значение. Как правило, их следует располагать от наиболее селективных (поле, уникальных значений которого в базе больше всего) к менее селективным.

Таким образом, вы можете увидеть прирост производительности даже без добавления дополнительных составных индексов. Использование EXPLAIN – то, что вам нужно. Вы увидите, что именно делает MySQL за кулисами.

Источник: spinupwp.com