Запросы к базе данных часто оказываются основной причиной снижения скорости приложения. В некоторых случаях эта проблема имеет объективный характер, но иногда она возникает из-за использования «универсальных инструментов». Тут я сразу хочу оговориться, что в 90% случаев такие инструменты отлично работают и экономят время, но когда снижается скорость, желательно понимать что именно они делают и как исправить ситуацию.
Рассмотрим в качестве примера работу с метаданными в WordPress. Допустим, у каждой статьи на сайте есть несколько атрибутов, например, рейтинг и количество проголосовавших посетителей. Нам нужно создать фильтры, которые позволят выбирать статьи по этим параметрам, т.е. что-то вроде рейтинг – от 3 до 5 и количество голосов – больше 100.
Такую информацию удобно хранить в таблице метаданных wp_postmeta. Это логичное решение, т.к. таблица wp_postmeta связана с wp_posts отношением «многие-к-одному». И мы можем для любой статьи хранить практически не ограниченное количество метаданных. Кроме того, для разных статей можно сохранять собственные наборы полей и это не приведёт к появлению пустых (NULL) значений в таблицах.
Примечание. Такой способ хранения данных очень распространён и используется в большинстве CMS. WordPress в качестве примера я взял только потому, что он широко распространён. Описанные ниже проблемы могут касаться любой системы.
Проблема возникает при поиске по значениям нескольких метаполей стандартными средствами WP. Такие запросы могут выполняться очень медленно. Давайте разберемся почему так происходит.
Таблица wp_postmeta и класс WP_Query
Таблица wp_postmeta содержит 4 поля:
meta_id– первичный ключ;post_id– bigint, индексированное;meta_key– varchar, индексированное;meta_value– longtext, не индексированное.
Такая структура позволяет хранить практически любые значения метаданных, т.к. тип meta_value – longtext.
При этом поиск по ключу (meta_key) выполняется быстро, т.к. поле индексировано. Т.е. функция
get_post_meta( $post_id, $key, $single );
работает быстро, т.к. поиск выполняется по двум индексированным полям.
А вот поиск постов на основе значений этих полей – совсем другое дело.
В WordPress для выборки постов используется класс WP_Query, который поддерживает поиск по значениям метаданных.
Делается это следующим образом.
$args = array(
...
'relation' => 'AND',
'meta_query' => array(
array(
'key' => 'rating',
'value' => array(3, 4),
'type' => 'numeric',
'compare' => 'BETWEEN',
),
array(
'key' => 'votes',
'value' => 100,
'type' => 'numeric',
'compare' => '>=',
)
)
);
$query = new WP_Query($args);
Такой запрос вернёт все статьи у которых значение рейтинга находится в диапазоне от 3-х до 4-х, а число проголосовавших – больше или равно 100.
Обратите внимание, что мы задали тип полей numeric, т.е. мы хотим, чтобы значения, записанные в поле meta_value, считались числовыми, а не текстовыми.
В результате мы получим запрос вроде
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE 1=1
AND wp_posts.post_type IN ('post', 'page')
AND (wp_posts.post_status = 'publish')
AND (
(wp_postmeta.meta_key = 'rating' AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '3' AND '4')
AND
(mt1.meta_key = 'votes' AND CAST(mt1.meta_value AS SIGNED) >= '100')
)
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
Для каждого условия создается внутреннее объединение (INNER JOIN) с таблицей wp_postmeta. Кроме того, при фильтрации значений используется функция CAST, которая преобразует значения wp_postmeta.meta_value в числовой тип.
В реальном запросе будут еще дополнительные условия и объединения, например, с таблицей wp_term_relationships для того, чтобы выбрать посты, которые относятся к определённой категории.
В результате мы скорость выполнения запроса резко падает. И основная причина — INNER JOIN.
Конкретные значения времени выполнения запроса будут зависеть от размера БД (количества записей в таблицах wp_posts, wp_postmeta) и производительности сервера. Я сталкивался с ситуациями, когда время выполнения запроса с 4-мя такими условиями при 100к+ записях в таблице wp_postmeta (около 5000 постов с примерно 20-ю метаданными на пост) доходило до нескольких минут. Более мощное железо в какой-то степени улучшит ситуацию, но проблему не решит.
Дело в том, что MySQL должен перебрать все возможные сочетания метаданных, а их количество экспоненциально увеличивается с каждым объединением.
Варианты решения проблемы
1) Создать дополнительную таблицу с нужными полями и хранить данные в ней.
Тип связи новой таблицы с wp_posts – «один-к-одному». Как вариант, можно добавить дополнительные поля в таблицу wp_posts.
Выборка будет выполняться быстро, т.к. для полей можно указать числовой тип и будет только одно объединение, при этом количество записей в новой таблице будет меньше, чем в wp_postmeta.
Недостатки:
- Если мы в будущем захотим использовать дополнительные метаданные, то нужно будет либо менять структуру новой таблицы, либо хранить их в wp_postmeta.
- Работать с такой таблицей сложнее, т.к стандартные функции WP тут ничем не помогут.
- Какая-то часть данных в такой таблице может иметь значение null. Например, если у вас для разных типов постов нужны разные метаполя.
Из собственного опыта могу сказать, что это очень эффективное решение. Особенно, если нужно не только проверить значения метаданных. Конечно, некоторое время уходит на то, что написать необходимый код. Но зато запросы выполняются быстро.
2) Разбить запрос с объединениями на несколько.
- Выполняем поиск в таблице wp_postmeta отдельно для каждого условия.
- Получаем массивы, содержащие ID постов.
- Находим пересечение (или объединение) этих массивов.
- В основной запрос вместо условий поиска по метаданным, подставляем ID постов (
WHERE ID IN (...)).
Количество запросов будет на один больше количества необходимых объединений. Но за счёт того, что выполняются они быстрее, получаем выигрыш в скорости.
Хоть скорость всё-равно остаётся довольно низкой из-за вызова CAST, время выполнения запроса значительно меньше, чем у запроса с объединениями. В одном из моих экспериментов, этим способом получилось уменьшить время с 27 до 0,3 сек.
3) Добавить индекс для поля meta_value
ALTER TABLE `wp_postmeta` ADD INDEX USING BTREE (meta_value(255));
Этот совет я взял отсюда. Решение далеко не идеальное, автору той статьи тоже не нравится.
Эффект есть, но недостаточный. В моём случае время выполнения запроса уменьшилось примерно в 3,5 раза, но всё-равно это слишком медленно.
4) Для ограниченного набора дискретных значений использовать таксономии вместо метаданных.
Это не решение данной проблемы, просто иногда метаполя используются там, где нужны таксономии.
Для рейтинга или количества проголосовавших такое решение не подходит, но для таких параметров как цвет, размер (XL, XXL, …) и т.п. (т.е. любых данных которые имеют фиксированный набор значений) его можно использовать.
При поиске по терминам таксономий тоже используются объединения. Но он выполняется быстрее, чем по метаполям, т.к. в большинстве случаев таблица wp_terms содержит гораздо меньше значений, и вместо BETWEEN будет использоваться IN или =.
Заключение
С подобными проблемами можно столкнуться при использовании более-менее сложных библиотек для работы с базой данных. Такие библиотеки, как и любой универсальный инструмент имеют свои ограничения и недостатки, которые могут проявиться при определённых условиях.
Но если вы потратили некоторое время на изучение SQL и знаете как посмотреть запросы, которые формирует ваша библиотека, то сможете решить все проблемы 🙂
Happy querying!
