Intereting Posts
Почему WordPress автоматически изменяет URL-адреса изображений? (Отслеживание пикселей из RSS) $ is_iphone – перенаправить страницу Переопределение короткого кода $ atts для предлагаемых продуктов Использование twitter bootstrap в теме Как отключить задания wordpress cron для дочернего сайта? Очень упрямый wp_register_script / add_action vs remove Как отображать связанные сообщения из той же категории? Добавьте страницу администратора, но не показывайте ее в меню администратора Добавление javascript в functions.php вызывает проблемы с моим шаблоном Поиск и замена текста во всех сообщениях WPAlchemy: Как выводить значения? Как получить название случайных изображений из медиа-библиотеки? wordpress threaded comments – настройка вложенной ul / как отображается дочерняя ul Добавление категорий к настраиваемому типу сообщений в permalink интерактивная диаграмма с положительным результатом

простой sql-запрос на wp_postmeta очень медленный

У меня есть установка wordpress, и мне нужен этот запрос для каждого сообщения:

select post_id, meta_key from wp_postmeta where meta_key = 'mykey' and meta_value = 'somevalue' 

У меня есть 3M строк в этой таблице, и запрос занимает около 6 секунд. Я думаю, это должно быть намного быстрее. Если я покажу индекс таблицы, он вернет мне:

 SHOW INDEX FROM wp_postmeta Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment wp_postmeta 0 PRIMARY 1 meta_id A 3437260 NULL NULL BTREE wp_postmeta 1 post_id 1 post_id A 1718630 NULL NULL BTREE wp_postmeta 1 meta_key 1 meta_key A 29 191 NULL YES BTREE 

Если я объясню, он вернет мне это:

 explain select post_id, meta_key from wp_postmeta where meta_key = 'mykey' and meta_value = 'somevalue' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE wp_postmeta ref meta_key meta_key 767 const 597392 Using where 

Я не очень хорошо разбираюсь в mysql, поэтому не знаю, как его проверить или решить. Можете ли вы дать мне некоторую ориентацию, где проблема?

Спасибо вам всем.

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

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

wp_postmeta есть неэффективные индексы. Опубликованная таблица (см. Википедию)

 CREATE TABLE wp_postmeta ( meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, post_id bigint(20) unsigned NOT NULL DEFAULT '0', meta_key varchar(255) DEFAULT NULL, meta_value longtext, PRIMARY KEY (meta_id), KEY post_id (post_id), KEY meta_key (meta_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Проблемы:

  • AUTO_INCREMENT дает никакой пользы; на самом деле он замедляет большинство запросов (из-за необходимости искать во вторичном индексе, чтобы найти auto_inc id, а затем искать в данных для фактического идентификатора вам нужно)
  • AUTO_INCREMENT – дополнительный беспорядок – как на диске, так и в кеше.
  • Гораздо лучше PRIMARY KEY(post_id, meta_key) – сгруппировано, обрабатывает обе части обычного JOIN .
  • BIGINT является излишним, но это невозможно зафиксировать без изменения других таблиц.
  • VARCHAR(255) может быть проблемой в MySQL 5.6 с utf8mb4 ; см. обходные пути ниже.
  • Когда meta_key или meta_value когда-либо будут NULL ?

Решения:

 CREATE TABLE wp_postmeta ( post_id BIGINT UNSIGNED NOT NULL, meta_key VARCHAR(255) NOT NULL, meta_value LONGTEXT NOT NULL, PRIMARY KEY(post_id, meta_key), INDEX(meta_key) ) ENGINE=InnoDB; 

Типичное использование:

 JOIN wp_postmeta AS m ON p.id = m.post_id WHERE m.meta_key = '...' 

Заметки:

  • Компонента PRIMARY KEY направляется прямо к нужной строке, без отступления по второстепенному индексу и по нескольким строкам.
  • INDEX(meta_key) может быть или не быть полезным, в зависимости от того, какие другие запросы у вас есть.
  • InnoDB требуется для «кластеризации».
  • Вперёд используйте utf8mb4, а не utf8. Однако вы должны быть согласованы во всех таблицах WP и в параметрах подключения.

Ошибка «максимальная длина ключа равна 767», что может случиться в MySQL 5.6 при попытке использовать CHARACTER SET utf8mb4. Выполните одно из следующих действий (каждый из них имеет недостаток), чтобы избежать ошибки:

  • Обновите до 5.7.7 для ограничения по 3072 байтам – ваше облако может не обеспечить этого;
  • Измените 255 на 191 на VARCHAR – вы потеряете все ключи длиннее 191 символа (маловероятно?);
  • ALTER .. CONVERT TO utf8 – вы теряете Emoji и некоторые китайцы;
  • Используйте индекс «префикс» – вы теряете некоторые преимущества производительности;
  • Переконфигурируйте (если остаетесь с 5.6.3 – 5.7.6) – 4 вещи для изменения: Barracuda + innodb_file_per_table + innodb_large_prefix + динамический или сжатый.

Потенциальная несовместимость

  • meta_id , вероятно, нигде не используется. (Но это риск удалить его).
  • Вы можете сохранить meta_id и получить большую часть преимуществ, изменив эти индексы: PRIMARY KEY(post_id, meta_key, meta_id), INDEX(meta_id), INDEX(meta_key, post_id) . (Примечание. Имея meta_id в конце PK, возможно, что post_id + meta_key не является уникальным.)
  • Переход от BIGINT к меньшему типу данных также потребует изменения других таблиц.

utf8mb4

  • Переход на 5.7 не должен быть несовместимым.
  • Сокращение до VARCHAR(191) потребовало бы от пользователя понять, что предел теперь является произвольным «191» вместо предыдущего произвольного предела «255».
  • «Реконфигурирующее» исправление – это проблемы администраторов баз данных, а не проблемы несовместимости.

Комментарий

Надеюсь, что кое-что из того, что я защищаю, находится на дорожной карте WordPress. Между тем, stackoverflow и dba.stackexchange загромождают «почему WP работает так медленно». Я считаю, что исправления, приведенные здесь, значительно сократятся в таких Вопросах типа жалобы.

Обратите внимание, что некоторые пользователи меняются на utf8mb4, несмотря на проблемы совместимости. Тогда у них возникают проблемы. Я попытался решить все проблемы MySQL, которые они имеют.