Новости
- Таблиця wp_postmeta і клас WP_Query
- Варіанти вирішення проблеми
- 2) Розбити запит з об'єднаннями на кілька.
- 3) Додати індекс для поля meta_value
- 4) Для обмеженого набору дискретних значень використовувати таксономії замість метаданих.
- висновок
Запити до бази даних часто виявляються основною причиною зниження швидкості додатка. У деяких випадках ця проблема має об'єктивний характер, але іноді вона виникає через використання «універсальних інструментів». Тут я відразу хочу застерегти, що в 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!