WordPress: как получить медленный запрос с помощью метаданных и WP_Query

Владимир | | MySQL, PHP, Web разработка, WordPress.

wordpress search by meta

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

Рассмотрим в качестве примера работу с метаданными в WordPress. Допустим, у каждой статьи на сайте есть несколько атрибутов, например, рейтинг и количество проголосовавших посетителей. Нам нужно создать фильтры, которые позволят выбирать статьи по этим параметрам, т.е. что-то вроде рейтинг – от 3 до 5 и количество голосов – больше 100.

Такую информацию удобно хранить в таблице метаданных wp_postmeta. Это логичное решение, т.к. таблица wp_postmeta связана с wp_posts отношением «многие-к-одному». И мы можем для любой статьи хранить практически не ограниченное количество метаданных. Кроме того, для разных статей можно сохранять собственные наборы полей и это не приведёт к появлению пустых (NULL) значений в таблицах.

Примечание. Такой способ хранения данных очень распространён и используется в большинстве CMS. WordPress в качестве примера я взял только потому, что он широко распространён. Описанные ниже проблемы могут касаться любой системы.

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

Таблица wp_postmeta и класс WP_Query

Таблица wp_postmeta содержит 4 поля:

  1. meta_id – первичный ключ;
  2. post_id – bigint, индексированное;
  3. meta_key – varchar, индексированное;
  4. meta_value – longtext, не индексированное.

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

При этом поиск по ключу (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.

Недостатки:

  1. Если мы в будущем захотим использовать дополнительные метаданные, то нужно будет либо менять структуру новой таблицы, либо хранить их в wp_postmeta.
  2. Работать с такой таблицей сложнее, т.к стандартные функции WP тут ничем не помогут.
  3. Какая-то часть данных в такой таблице может иметь значение null. Например, если у вас для разных типов постов нужны разные метаполя.

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

2) Разбить запрос с объединениями на несколько.

  1. Выполняем поиск в таблице wp_postmeta отдельно для каждого условия.
  2. Получаем массивы, содержащие ID постов.
  3. Находим пересечение (или объединение) этих массивов.
  4. В основной запрос вместо условий поиска по метаданным, подставляем 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!

  • MAX

    А зачем делать CAST? В данном запросе именно он является основным виновником тормозов. В MySQL строка автоматом приведется к числовому типу.

    • Я понимаю, что при использовании BETWEEN произойдет не явное преобразование типов. Но я хотел показать более общий случай. Например, если в запросе используется сортировка, то неявного преобразования не произойдет и значения meta_value будут сортироваться именно как строки.

      Кроме того, в первом примере показан запрос, который формирует WP_Query. Я его не изменял и не оптимизировал.

      А вообще, согласен. Во втором варианте решения использовать CAST не нужно.

  • А какие еще есть варианты оптимизации запросов и базы под WP (как, например, добавление индекса к meta_value в таблице postmeta)? Сейчас делаю большой проект на WP, где много данных, таксономий, типов постов и метаданных

    • chumachkin_m

      Писать SQL выборки самостоятельно если хочется скорости или использовать ORM если хочется гибкости. Можно попытаться расширить WP_Query, но думаю это будет сложно и долго по времени.

      Выносите часть выборок в процедуры, благо они появились в MySQL.

      PS.
      К примеру, как сделать запрос с DISTINCT или с использованием подзапросов? Как создавать в запросе вычисляемые поля?

      • Просто написать SQL выборку не достаточно. Объект WP_Query очень тесно интегрирован с остальным функционалом WP и «выпилить» его не так просто. Например, если вы замените механизм разбора запроса своим собственным, то перестанут работать функции вроде is_category, is_home и т.п., которые используются в шаблонах. Если есть реальная необходимостью таких переделок, то я бы подумал о целесообразности использования WP.

        >> множество выборок в WP забирают все поля целиком …

        Согласен. В WP_Query есть параметр «fields», который позволяет выбирать только id.

        >> К примеру, как сделать запрос с DISTINCT или с использованием подзапросов? Как создавать в запросе вычисляемые поля?

        WP_Query не универсальный фреймворк для работы с базой. В нем нет поддержки возможностей, которые не планировалось использовать в WP. Тем не менее, вы можете сформировать практически любой запрос с помощью фильтров вроде «posts_join», «posts_fields», «posts_orderby» и т.п.

        Например, добавить свои правила сортировки можно так:

        add_filter( 'posts_orderby', 'my_posts_orderby' );

        function ps_posts_orderby( $order_by ) {
        global $wpdb;
        //имя поля + 0 - преобразует значение к типу int
        return ' (pm.meta_value+0) DESC, ' . $wpdb->prefix . 'posts.post_title ASC, ' . $order_by;
        }

        • Всё правильно. Ещё многие часто забывают про подготовку мета-данных и терминов для всех записей в WP_Query, и размещение их в кэш объектов.

        • chumachkin_m

          Не понял по подготовку мета-данных и кэш, можно небольшой пример?

        • Для найденных записей WP_Query выполнит функцию update_meta_cache() и update_term_cache(), которые выдернут из базы данных все мета-данные и термины для записей, и разместят их в объектный кэш WordPress. Таким образом при вызове функции get_post_meta() дополнительных запросов в базу данных не будет.

          Если сделать запрос в обход WP_Query и не подготовить мета-данные, то в цикле при вызове get_post_meta() будет вызываться update_meta_cache() (и соответственно SQL запрос) для каждой записи.

    • И общая рекомендация. Поставьте плагин вроде Debug Bar. Он выводит список запросов и время их выполнения.

  • chumachkin_m

    На самом деле, это экономия на спичках и сильно на скорость работы не повлияет.

  • Стас

    Если всем метавалюям задать свои индексы к примеру в ручную в базе, это решит проблему? Не очень во всем этом понимаю. Просто мое предположение..

    • Да. Это третий способ, описанный в статье.

      • Стас

        Да я прочел. Вы написали что основной причиной нагрузки является функция CAST, я посмотрел код wp_query и так понял что она вызывается если только укать тип ( 'type' => 'numeric' и тд.) . Или все таки основной причиной нагрузки бд является INNER JOIN?

        Отсюда вопрос повысится ли производительность если в метавалюе указать числавой тип, с учетом что сортировка будет производится только по числовым типам. Ведь тогда не надо будет указывать тип для CAST а можно просто указать ORDER BY => 'metavalue '. Повысится ли в этом случае скорость выборки больше чем в три раза или это тоже самое что описано в третьем пункте?

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

        • Если вы проектируете свою базу данных и укажите для поля metavalue числовой тип, то, да, CAST будет не нужен и скорость повысится. Проблема в том, что в таком поле нельзя будет хранить текстовые данные. В WP метаданные используются для хранения любых данных, а не только числовых. Поэтому с данными вроде цены, веса и т.п., которые по определению числовые, возникают проблемы.

          Сказать что именно является основной причиной нагрузки (CAST или INNER JOIN) сложно, т.к. ответ будет зависеть от разных факторов, например, размера таблицы. Но CAST и INNER JOIN не взаимозаменяемые вещи, т.е. если нужен INNER JOIN, то использовать вместо него CAST не получится.

  • Михаил

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

    Нужно посчитать количество записей по фильтру категория+метка.

    И нужно посчитать количество не только одной категории, а у нескольких
    дочерних категорий текущей категории. И все это дело генерит кучу запросов к базе(при 30
    000 записях и 400 категориях, таким образом получается около 300
    запросов).

    Вот пример кода.

    post_count;
    if ( $yyyy > '0' ) { чтобы не выводить ссылки где записей 0

    echo 'slug. '&tag='.$tags_s.'» >' . $news_cat->name.' ('.$yyyy.')';

    } ?>

    Это меню для доски объявлений, где метка это город.

    Вот пример сайта moskva.biglus.com/cat/uslugi/ (хоть это и поддомен, но это просто сделано через .htaccess
    biglus.com?tag=moskva&category_name=uslugi)

    • Михаил

      Не сохранился код

      [[?php
      $tags_s = 'rabota'; // слаг метки
      $news_cat_ID = '444'; // ид категори
      $news_cats = get_categories(«parent=$news_cat_ID»);//для вывода дочерних категорий

      foreach ($news_cats as $news_cat):
      ?]]
      [[?php
      $res_search = &new WP_Query( 'tag='.tags_s.'&cat='.$news_cat);
      $yyyy = $res_search-]]post_count;
      if ( $yyyy ]] '0' ) { чтобы не выводить ссылки где записей 0

      echo '[[li]][[a href=»http://site.com/?category_name=' . $news_cat-]]slug. '&tag='.$tags_s.'» ]]' . $news_cat-]]name.'[[/a]] ('.$yyyy.')[[/li]]';

      } ?]]

      [[?php endforeach; ?]]

      > замена ]]

  • Alex

    Здравствуйте. Есть какая нибудь информация по использованию memcached или аналогичных для работы с базой и хранения данных в оперативной памяти? Можно ли таким способом решить проблему быстродействия?

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