Від автора: ви знаєте, що швидкий сайт == щасливі користувачі, вищий рейтинг в google і підвищена конверсія. Можливо, ви думаєте, що ваш сайт на WordPress максимально швидкий: ви дивіться за продуктивністю за статтями «кращі практики налаштування сервера», «вирішення проблем з повільним кодом» та «переміщення зображень на CDN». Але все це?
У динамічних сайтах типу WordPress з базою даних у вас буде одна і та ж проблема – запити до бази даних уповільнюють сайт. У цій статті я покажу вам, як робиться оптимізація SQL-запитів, що викликають уповільнення, і як зрозуміти, в чому проблема у цих запитах. Я буду використовувати реальний запит, який ми нещодавно правили, так як він уповільнював портал покупця deliciousbrains.com.
Визначення
Перший крок у вирішенні повільних SQL запитів – знайти їх. Ashley вихваляла плагін налагодження Query Monitor в блозі. Безцінним цей інструмент робить функція плагіна запитів до бази даних для визначення повільних SQL запитів. Ви можете фільтрувати запити за кодом або компоненту (плагін, тема або ядро WordPress), які їх здійснюють. А також плагін вміє виділяти повторювані і повільні запити:
Якщо не хочете ставити плагін налагодження на продакшн сайт (може, вас хвилює продуктивність), можете запустити MySQL Slow Query Log, який логирует всі запити, що займають певний час на виконання. Інструмент дуже легко налаштувати і вказати папку для логування запитів. Так як це серверне рішення, то удар на продуктивність буде менше, ніж плагін налагодження. Однак коли він не потрібен, його потрібно вимкнути.
Поняття
Після знаходження повільних запитів далі необхідно спробувати зрозуміти, що уповільнює їх. Нещодавно при розробці нашого сайту ми знайшли запит, який виконувався 8 секунд!
SELECT
l.key_id,
l.order_id,
l.activation_email,
l.licence_key,
l.software_product_id,
l.software_version,
l.activations_limit,
l.created,
l.renewal_type,
l.renewal_id,
l.exempt_domain,
s.next_payment_date,
s.status,
pm2.post_id AS ‘product_id’,
pm.meta_value AS ‘user_id’
FROM
oiz6q8a_woocommerce_software_licences l
INNER JOIN
oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
INNER JOIN
oiz6q8a_posts p ON p.ID = l.order_id
INNER JOIN
oiz6q8a_postmeta pm ON pm.post_id = p.ID
AND pm.meta_key = ‘_customer_user’
INNER JOIN
oiz6q8a_postmeta pm2 ON pm2.meta_key = ‘_software_product_id’
AND pm2.meta_value = l.software_product_id
WHERE
p.post_type = ‘shop_order’
AND pm.meta_value = 279
ORDER BY s.next_payment_date
Для запуску магазину плагінів ми використовували WooCommerce і плагін WooCommerce Software Subscriptions. Суть запиту – отримання всіх підписок покупця за його номером. У WooCommerce досить складна модель даних. Хоча замовлення і зберігається у вигляді кастомного типу поста, але id покупця не зберігається в post_author, а є частиною post мета даних. Також є кілька підключень до користувальницьким таблиць, створеним плагіном підписки на ПО. Давайте більш детально розберемо запит.
MySQL твій друг
В MySQL є корисне вираз DESCRIBE, за допомогою якого можна виводити інформацію про структуру таблиці (її колонки, типи даних, значення за замовчуванням). Якщо виконати DESCRIBE wp_postmeta;, то ви побачите:
Круто, але ви, можливо, вже знаєте про це. Але чи знали ви, що префікс DESCRIBE можна використовувати на SELECT, INSERT, UPDATE, REPLACE і DELETE? Більш широко відомий синонім EXPLAIN, який дасть нам докладну інформацію про те, як буде виконуватися вираз.
Результат для повільного запиту:
На перший погляд розібратися складно. На щастя, хлопці з SitePoint зібрали повне керівництво до розуміння виразу.
Найголовніша колонка type, в ній описується спосіб об’єднання таблиць. Якщо стоїть ALL, значить MySQL читає всю таблицю з диска, збільшує швидкість читання/запису і перекладає завантаження на CPU. Процес називається повне сканування таблиці (більш докладно пізніше).
Колонка rows також вказує на те, що MySQL повинен робити. Вона показує, скільки рядків необхідно переглянути для знаходження результату.
Explain дає більше інформації, ніж ми можемо оптимізувати. Наприклад, таблиця pm2 (wp_postmeta) говорить нам, що ми використовуємо Using filesort, так як хочемо, щоб результати зберігалися за допомогою виразу ORDER BY. Якщо б ми ще групували запит, це додало б ще більше навантаження на виконання.
Візуальне розслідування
MySQL Workbench – ще один хороший безкоштовний інструмент для розслідувань. Для баз даних MySQL 5.6 і вище результат EXPLAIN можна вивести у вигляді JSON, а MySQL Workbench перетворює цей JSON в візуальний план виконання виразу:
Він автоматично малює попередження з проблем, забарвлюючи частини запиту, які будуть довго виконуватися. Ми відразу бачимо, що приєднання до таблиці wp_woocommerce_software_licences (аліас |) додає проблем.
Рішення
Частина запиту виконує повне сканування таблиці, чого слід уникати, так як вона використовує колонку без індексу order_id для об’єднання між таблицями wp_woocommerce_software_licences і wp_posts. Поширена проблема в повільних запитах. Її дуже легко вирішити.
Індекси
Order_id – досить важлива частина визначення даних в базі даних, і якщо ми будемо будувати запит таким чином, то нам знадобиться додати індекс в цю колонку, або MySQL буде буквально сканувати всі рядки таблиці, поки не знайде необхідні. Додамо індекс і подивимося, що він дасть:
CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id)
Нам вдалося заощадити 5 секунд запиту простим додаванням індексу, круто!
Знайте свій запит
Проаналізуйте запит – join за join, підзапит за підзапит. Запит робить те, що не має? Його можна оптимізувати?
У нашому випадку ми підключаємо таблицю ліцензій до таблиці постів з допомогою order_id, постійно обмежуючи вираз типами посад shop_order. Це необхідно для забезпечення цілісності даних, щоб переконатися, що ми використовуємо тільки правильні записи замовлень. Насправді ця частина запиту застаріла. Ми знаємо, що безпечніше, щоб у рядку ліцензії в таблиці був order_id, що відноситься до замовлення WooCommerce в таблиці постів, оскільки це застосовується в коді плагіна PHP. Давайте видалимо join і подивимося, чи покращить це запит:
Несильно, але запит тепер виконується менше 3 секунд.
Кэшируйте все!
Якщо на вашому сервері MySQL кешування запитів за замовчуванням, то краще його включити. Так MySQL буде зберігати запис всіх виконаних виразів з результатами, якщо вираз буде виконано знову повернеться ваш результат. Кеш не старіє, так як MySQL скидає кеш при зміні таблиці.
Query Monitor з’ясував, що наш запит запускається 4 рази за одне завантаження сторінки. Хоча і добре кешувати MySQL запити, але дублювати зчитування з бази даних в одному запиті точно не варто. Статичний кешування в коді PHP – простий і дуже ефективний спосіб вирішення проблеми. Ви отримуєте результат запиту з бази даних при першому запиті і зберігайте результат в статичному властивості класу. У наступних викликах буде повертатися результат із статичного властивості:
class WC_Software_Subscription {
protected static $subscriptions = array();
public static function get_user_subscriptions( $user_id ) {
if ( isset( static::$subscriptions[ $user_id ] ) ) {
return static::$subscriptions[ $user_id ];
}
global $wpdb;
$sql = ‘…’;
$results = $wpdb->get_results( $sql, ARRAY_A );
static::$subscriptions[ $user_id ] = $results;
return $results;
}
}
Кеш має час життя запиту, більш точно – час життя об’єкта. Якщо ви шукайте постійні результати запиту в інших запитах, вам потрібно реалізувати постійний Object Cache. Ваш код повинен вміти включати кеш і застарівати кеш при зміні дати.
Думайте нестандартно
Можна зробити й інші способи для прискорення виконання запитів, в яких потрібно трохи більше, ніж просто поправити запит або додати індекс. Одна з найбільш повільних частин нашого запиту – процес об’єднання таблиць для переходу від id покупця до товару id, і це необхідно робити для кожного покупця. А що якщо зробити все об’єднання за раз, щоб одержувати дані про покупця тоді, коли це необхідно?
Ви можете змінити звичайну структуру даних, створивши таблиці, в якій зберігаються дані ліцензії, а також id користувача і товару для всіх ліцензій, а також запит до конкретного покупця. Знадобиться зібрати таблицю MySQL triggers на INSERT/UPDATE/DELETE для таблиці ліцензій (або інших залежно від зміни даних), але це значно підвищить продуктивність запиту даних.
Якщо кілька join уповільнюють запит, можна прискорити його, розбивши на 2 і більше вирази, після чого виконувати їх окремо в PHP, збирати і фільтрувати результати в коді. Laravel робить щось схоже в жадібній завантаженні в Eloquent.
WordPress може уповільнювати запити до таблиці wp_posts, якщо обсяг даних великий, і присутня безліч кастомних типів посад. Якщо запити типів постів уповільнюють сайт, спробуйте піти від моделі зберігання кастомних типів постів в бік кастомних таблиці.
Результати
З допомогою цих підходів щодо оптимізації запитів нам вдалося прискорити наш запит з 8 до 2 секунд, а також знизити кількість викликів з 4 до 1. Час запитів записувалося на версії для розробки, продакшн воно було б менше.
Сподіваюся, це керівництво щодо прискорення повільних запитів допомогло вам. Оптимізація запитів може здатися складним, але як тільки ви спробуєте, і у вас щось вийде, ви захочете ще сильніше поліпшити запит.
