


Блог IT-шника
Блог IT-шника
Привіт:) Індексація бази даних — часто забута, але цінна практика при управлінні користувацькими (які не входять у список за замовчуванням під час встановлення копії WordPress) таблицями. Аналогічно індексу книги, індекс таблиць бази даних значно підвищує швидкість вибірки даних з таблиць вашої бази даних. Це має бути перше, про що ви повинні замислитись, коли створюєте власну таблицю.
У цьому посібнику я покажу, як оптимізувати бази даних за допомогою індексації, і розповім, як відсутність індексів може вплинути на продуктивність вашого сайту WordPress і, отже, на його рейтинг у пошукових системах і користувальницький досвід.
Перевірка правильності індексації користувацьких таблиць показує свої результати. У недавньому тестуванні з 1 мільйоном записів, вибираючи близько 100 000 записів одночасно, я побачив скорочення часу запиту приблизно на 150% при використанні індексів!
до змісту ↑Багато розробників WordPress не дуже добре знайомі з індексацією таблиць бази даних. Зрештою, таблиці бази даних WordPress за замовчуванням вже проіндексовані правильно, що оптимізує час вибірки для складних пошукових запитів, таких як автоматичні чернетки, видалені повідомлення, незатверджені коментарі й т.п.
В офіційному гайді розробника плагінів не згадуються користувацькі таблиці та рекомендується використовувати користувацький тип записів (Custom Post Type). Однак ми знаємо, що використання останнього має свої обмеження (див. https://deliciousbrains.com/syncing-wordpress-database-changes-merging/#why-custom-post-types-complicate). У деяких випадках вигідніше створити користувацьку таблицю (див. https://codex.wordpress.org/Creating_Tables_with_Plugins) для зберігання даних плагіну. Кодекс WordPress все ще має посібник зі створення таблиць за допомогою плагінів. Іронія полягає в тому, що якщо ви коли-небудь створювали таблицю у MySQL, використовуючи цей метод, зі стовпцем первинного ключа (зазвичай називається id
), MySQL автоматично створює індекс для цього стовпця (за замовчуванням називається PRIMARY
).
У цьому гайді розглянемо спосіб оптимізації інформації у базі даних WordPress, яку багато розробників WordPress не враховують: додавання табличних індексів до полів, за якими ви можете виконувати запити WHERE
або JOIN
.
Спершу важливо оцінити ситуацію, перш ніж приймати рішення про додавання індексів. Таблиці з невеликою кількістю записів не отримають великої користі від індексів, оскільки MySQL може швидко зчитувати всю таблицю в пам'ять, а запити, що виконують повне сканування таблиці, не займуть багато часу.
З іншого боку, якщо таблиця містить багато даних і немає великої кількості запитів на запис (тобто запитів на вставку і оновлення), індекси можуть значно підвищити продуктивність запитів вибірки, уникаючи повного сканування таблиці. Хорошим прикладом є інтернет-магазин на основі плагіну WooCommerce для з великою кількість замовлень.
Для таблиці, яка отримує багато запитів на запис, додавання індексів сповільнить їх, оскільки індекси необхідно перебудовувати щоразу, коли додається або оновлюється рядок.
Слід враховувати табличні індекси, коли ви намагаєтеся оптимізувати сайт з повільним часом завантаження. Як правило, якщо на сайті повільно завантажуються сторінки, його власник шукає рішення для кешування або балансування навантаження. Це потужні інструменти для скорочення кількості запитів до сторінок, що можуть бути кешовані. Але для некешованих також важливо оптимізувати продуктивність.
Щоб показати на прикладі, для керування записами для книжкового інтернет-магазину створимо кілька користувацьких таблиць з наступною структурою:
CREATE TABLE `wp_hfm_categories` (
`id` mediumint NOT NULL,
`slug` tinytext NOT NULL,
`description` text NOT NULL,
`title` text NOT NULL
);
CREATE TABLE wp_hfm_books (
id mediumint(9) NOT NULL AUTO_INCREMENT,
category_id mediumint(9) NOT NULL,
author_id mediumint(9) NOT NULL,
title tinytext NOT NULL,
description text NOT NULL,
url varchar(100) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) ;
Таблиця wp_hfm_categories
була заповнена 1000, а wp_hfm_books
- 1000000 записів книг. Це означає, що в кожній категорії налічується близько 100 000 книг.
Оцінимо, скільки часу потрібно для запиту даних для всіх книг у першій категорії.
<?php
$time_start = microtime( true );
$connection = mysqli_connect( "localhost", "root", "password", "wordpress" );
$query = "SELECT * FROM wp_hfm_books WHERE category_id = 1";
$time_end = microtime( true );
$setup_time = round( $time_end - $time_start, 5 );
$time_start = microtime( true );
$result = mysqli_query( $connection, $query );
$time_end = microtime( true );
$query_time = round( $time_end - $time_start, 5 );
if ( ! $result ) {
echo "Query failed!" . '</br>';
die();
}
$time_start = microtime( true );
$book_list = '';
while ( $book = $result->fetch_object() ) {
$book_list .= '<p>' . $book->title . '</p>';
}
$time_end = microtime( true );
$execution_time = round( $time_end - $time_start, 5 );
echo "Setup in {$setup_time} seconds!" . '</br>';
echo "Query in {$query_time} seconds!" . '</br>';
echo "Books gathered in {$execution_time} seconds!" . '</br>';
echo $book_list;
Результат виконання:
Setup in 0.00031 seconds!
Query in 0.53784 seconds!
Books gathered in 0.00497 seconds!
Отже, на взаємодію з базою даних припадає майже весь час завантаження сторінки, і перевищує 0,5 секунди при позначці в 1 мільйон записів.
до змісту ↑Гіпотетичні приклади — це добре, але переваги індексів по-справжньому можна оцінити лише тоді, коли ви відчуєте їх відсутність на робочому WordPress-сайті чи додатку.
Це сталося зі мною, коли я працював у службі розміщення подкастів.
Feed є важливим елементом будь-якого успішного подкасту. Це XML-дані, які надсилаються на такі платформи, як Apple Podcasts, Spotify і Amazon Music.
Кожна платформа регулярно опитує канал RSS, перевіряючи наявність нових даних у каналі та оновлюючи інформацію на своїй платформі.
Якщо у подкастах величезний потік даних, то з його обробкою виникають проблеми.
У міру розвитку бізнесу у нас почалися труднощі, коли RSS-канали для великих подкастів не оновлювалися належним чином. Перше, що ми зробили, — це використали кешування Redis у каналах. Деякий час це працювало, але незабаром проблема знову виникла зі збільшенням інформації у подкастах. Один із наших розробників помітив, що таблиці, які запитуються для створення каналу, неправильно проіндексовані. Ми витратили близько півгодини, щоб встановити правильні індекси. Відразу час запиту скоротився майже до десятої частини початкового часу, і проблема зникла.
Подивимося, як наш приклад із книгами працює в більш реалістичному середовищі. Я створив шаблон сторінки в дочірній темі WordPress, який виводить деякі дані для кожної книги.
<?php
/**
* Template Name: Books Template
*
* @package WordPress
* @subpackage Twenty_TwentyTwo_Child
* @since 1.0.0
*/
$execution_time = 0;
$time_start = microtime(true);
get_header(); ?>
<header class="page-header alignwide">
<h1 class="page-title">Books</h1>
</header><!-- .page-header -->
<?php
$books = hfm_books_get_books();
foreach ( $books as $book ) {
?>
<article id="post-<?= $book['id'] ?>" <?php post_class(); ?>>
<header>
<h1><a href="<?= $book['url'] ?>"><?= $book['title'] ?></a></h1>
</header><!-- .entry-header -->
<div>
<?php echo $book['description']; ?>
</div><!-- .entry-content -->
</article>
<?php
}
get_footer();
Функція, яка запитує книги, використовує той самий запит, що й наш попередній приклад:
function wp_books_get_books() {
global $wpdb;
$query = "SELECT * FROM wp_hfm_books where category_id = 1;";
return $wpdb->get_results($query, ARRAY_A);
}
Час створення вмісту сторінки в моєму локальному середовищі розробки становить 2,79 секунди, загальний час запиту – 0,52 секунди (оскільки всі інші запити виконуються для відтворення сторінки), а споживана пам’ять становить 20 мегабайт.
Зараз ви, ймовірно, думаєте: "Добре, але я можу пришвидшити все це, додавши розбивку сторінок і обмеживши виведення кількості постів на кожну сторінку". Ви не помилилися. Однак, припустімо, ви показуєте 10 записів на сторінці. Ви можете пришвидшити запит окремої сторінки, але чи зможе той самий запит обробляти 1000 одночасних користувачів? А як щодо 10 000? А як щодо 1000 користувачів, кожен із яких запитує книги для кожної з 1000 категорій? Це фактично 1 мільйон записів! Тепер подумайте про збереження та запит додаткових даних про книгу, можливо, інформацію про автора в таблиці авторів, або якщо вам потрібно зробити запит до таблиці файлів для цифрових версій книги.
до змісту ↑Є два способи визначити, чи є запити до бази даних причиною проблеми. Якщо ви працюєте з веб-сайтом WordPress, ви можете встановити плагін Query Monitor. Він додає безліч інструментів розробника до інформаційної панелі WordPress, але його головною особливістю є можливість перегляду всіх запитів до бази даних, які складають запит сторінки.
Якщо ви не можете встановити цей плагін або ви не працюєте з сайтом WordPress, ви можете увімкнути повільний журнал запитів MySQL (див. https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html). У ньому будуть зафіксовані будь-які запити, які займають більше часу, ніж значення змінної long_query_time
MySQL, яке за замовчуванням становить 10 секунд. Ви можете увімкнути цей журнал, підключившись до терміналу MySQL і ввімкнувши MySQL-змінну slow_query_log
.
SET GLOBAL slow_query_log = 'ON';
Також можете налаштувати глобальну змінну long_query_time
на менший час (на відміну від 10 секунд за замовчуванням):
SET GLOBAL long_query_time = 1; ## log any queries slower than 1 second
Цей файл журналу можна переглянути вручну або за допомогою команди mysqldumpslow
.
Якщо проблема дійсно полягає у запитах до бази даних, можете спробувати деякі способи її очищення:
wp_options
, wp_posts
, wp_postmeta
та wp_commentmeta
, а також спеціальні таблиці, які не видаляються після видалення плагіну. Непотрібні дані також можуть включати невикористовувані таксономії. Стандартними таксономіями WordPress є теги та категорії. Останні можна побачити у адмін-меню Записи. (див. Як очистити базу даних WordPress)OPTIMIZE TABLE
.Давайте повернемося до прикладу із книгами. Уявіть, що ви виявили, що запити книг уповільнюють роботу. Щоб розпочати діагностику цієї проблеми, вам знадобиться необроблений SQL-запит:
SELECT * FROM wp_hfm_books where category_id = 1;
Вхід у термінал MySQL і виконання цього запиту займає 0,48 секунди на моїй машині.
Щоб побачити час виконання, можемо використати оператор MySQL EXPLAIN
, який допоможе зрозуміти, що відбувається. Виконайте запит ще раз, але додайте EXPLAIN
до початку:
EXPLAIN SELECT * FROM wp_hfm_books where category_id = 1;
Виконання цього запиту дасть багато інформації, але зверніть увагу на поля possible_keys
і key
. Вони скажуть вам, які індекси (ключі) доступні для цього запиту та який індекс фактично використовується. Важливо зазначити, що поле rows
повідомляє нам, скільки рядків було проскановано під час цього запиту. Отже, коли я запускаю це для нашого запиту, я отримую такі результати:
Зверніть увагу, що в цьому випадку індекси не використовувалися, і нам довелося просканувати 976 402 рядки! Не дивно, що запит тривав так довго.
до змісту ↑Тепер, коли ми визначили, що є проблема індексу, давайте додамо індекс до стовпця category_id
для таблиці wp_hfm_books
. Є кілька способів зробити це.
Перший спосіб — додати індекс безпосередньо до таблиці. Цього можна досягти, виконавши запит ALTER TABLE
у терміналі MySQL, або використовуючи інструмент керування базою даних MySQL, наприклад TablePlus, або через phpMyAdmin. Запит ALTER TABLE
для таблиці книг виглядатиме так:
ALTER TABLE wp_hfm_books ADD INDEX category_id_index (category_id);
Це додає індекс під назвою category_id_index
до поля category_id
.
Якщо ви працюєте з власними (користувацькими) таблицями WordPress, доцільно додати індекси при створенні бази даних. Це досягається шляхом додавання INDEX
після PRIMARY KEY
у запиті:
CREATE TABLE wp_hfm_books (
id mediumint(9) NOT NULL AUTO_INCREMENT,
category_id mediumint(9) NOT NULL,
author_id mediumint(9) NOT NULL,
title tinytext NOT NULL,
description text NOT NULL,
url varchar(100) DEFAULT '' NOT NULL,
PRIMARY KEY (id),
INDEX category_id_index (category_id)
) ;
до змісту ↑
Отже, який вплив має індекс на запит? Якщо ви запустите його знову, ви побачите помітне покращення. У моєму локальному середовищі це скоротило час, необхідний для запиту, до 0,08 секунди. Це в п’ять разів швидше, ніж раніше! Якщо ви знову запустите оператор EXPLAIN
, ви побачите щось на зразок цього:
Ви бачите, що запит тепер використовує новий індекс category_id
, який ви щойно створили, і за допомогою цього нового індексу кількість рядків, які він має сканувати, зменшилася з 976 402 до 10 189. Успіх!
Підвищення продуктивності залежатиме не лише від кількості даних у таблиці, а й від типу запиту, який виконується. Ми могли просто додати category_id
як зовнішній ключ до таблиці. Зовнішні ключі автоматично індексують стовпець, а також забезпечують цілісність даних. Оскільки обмеження зовнішнього ключа не використовуються ядром WordPress, ви можете уникнути його, щоб зберегти сумісність.
Якщо у вашому запиті є кілька WHERE
, можливо, буде доцільно додати багатостовпцевий (складений) індекс, який включає кожне поле вашого WHERE
. Скажімо, ви також зберігаєте дані автора, які вам потрібно відфільтрувати, і ваш запит:
SELECT * FROM `books` WHERE category_id` = 1 AND author_id = 1
Ви можете додати такий складений індекс:
ALTER TABLE wp_hfm_books ADD INDEX `by_category_and_author_index` (`author_id`, `category_id`);
Якщо ви фільтруєте кілька полів, виконання запитів із складеним індексом покращує продуктивність запиту приблизно на 50%. Однак майте на увазі:
WHERE
. Якби я додав обидва індекси вище, EXPLAIN
показав би єдиний індекс category_id
, який використовувався б замість складеного індексу, навіть якщо у моєму запиті було кілька WHERE
.EXPLAIN
є вашим другом тут. Він покаже вам, що MySQL робить за лаштунками.Використаний тут приклад книг/категорій досить спрощений, але швидкість збільшується завдяки додаванню індексів до одного стовпця таблиці. Коли ви фільтруєте або об’єднуєте дані на основі певних полів, додавання індексів до цих полів є важливим кроком для забезпечення оптимізації ваших запитів.
Джерело: https://spinupwp.com/wordpress-database-optimization-indexing/