Оптимізація SQL-запитів та бази даних

Архітектурна база#

Архітектура збереження даних WordPress базується на реляційних СКБД MySQL 8.0+ та MariaDB 11.0+. Ключовим архітектурним викликом при масштабуванні високонавантажених порталів та каталогів є антипатерн EAV (Entity-Attribute-Value), закладений у структуру мета-таблиць (wp_postmeta, wp_usermeta, wp_termmeta, wp_commentmeta). У цій схемі будь-яка додаткова сутність зберігається як рядок із полями post_id, meta_key та meta_value.

Поле meta_value має тип longtext без стандартного B-Tree індексу. Будь-яка вибірка через WP_Query з використанням meta_query (особливо з операторами порівняння LIKE, <, >, BETWEEN або логікою OR) змушує СКБД виконувати Full Table Scan (повне сканування таблиці) на мільйонах рядків зі створенням тимчасових дискових таблиць (Disk-based Temporary Tables) та важким файловим сортуванням (Using filesort).

Другим критичним фактором деградації виступає антипатерн N+1 Queries: при відображенні списку з $N$ дописів у шаблоні відбувається 1 первинний запит за записами, після чого розробники викликають get_post_meta() або wp_get_post_terms() всередині циклу, генеруючи ще $N$ або $2N$ синхронних звернень до СКБД.

Третій фактор — деградація глибокої пагінації (Deep Pagination) через OFFSET. При запиті сторінки 10 000 з параметром LIMIT 10 OFFSET 100000 СКБД зобов'язана зчитати з B-Tree індексу та відфільтрувати 100 010 рядків, відкинувши перші 100 000, що збільшує час відповіді від 2 мс до кількох секунд.

Інженерний стандарт 2026 року вимагає переходу на курсорну пагінацію (Keyset / Seek Pagination), пакетне попереднє завантаження мета-кешів (update_post_caches), побудову складених покриваючих індексів (Covering Indexes) та відключення розрахунку загальної кількості рядків (no_found_rows => true).

Ментальна модель#

Різниця між класичною пагінацією зі зміщенням (Offset) та курсорною пагінацією (Keyset) у структурі B-Tree індексу:

ЗВИЧАЙНИЙ OFFSET: O(N) складність, сканування та відкидання всіх попередніх рядків
=================================================================================
SQL: SELECT ID FROM wp_posts ORDER BY post_date DESC LIMIT 10 OFFSET 100000;
  [1] -> [2] -> [3] -> ... -> [100000] (СКБД читає та викидає 100 тис. рядків!)
                                  |
                                  v
                             [100001 ... 100010] (Повернення 10 записів) -> Повільно!

КУРСОРНИЙ KEYSET (SEEK): O(log N) складність, миттєвий стрибок по B-Tree індексу
=================================================================================
SQL: SELECT ID, post_date FROM wp_posts 
     WHERE (post_date, ID) < ('2026-03-01 10:00:00', 45200) 
     ORDER BY post_date DESC, ID DESC LIMIT 10;
     
  [B-Tree Root]
        |
        +---> [Branch: 2026-03]
                    |
                    +---> [Leaf: ('2026-03-01 10:00:00', 45200)] 
                               | (Миттєве позиціонування вказівника)
                               v
                          [Наступні 10 записів з індексу] -> Час: 0.8 мс!

На практиці застосовується#

1. Каталоги електронної комерції (1 000 000+ товарів): Заміна багаторівневих вибірок за wp_postmeta на спеціалізовані денормалізовані таблиці зі складеними B-Tree індексами (патерн WooCommerce HPOS).
2. Нескінченний скрол у Headless SPA (Next.js 16): Реалізація курсорної пагінації за часовою міткою post_date_gmt та ID, що виключає уповільнення завантаження сторінок на 50-й чи 100-й сторінці видачі.
3. Оптимізація REST API ендпоінтів стрічки новин: Застосування no_found_rows => true та заголовкової пагінації Link: rel="next", що усуває важкі SELECT COUNT(*) по всій таблиці wp_posts.
4. Тюнінг запитів у Query Monitor: Аналіз логу повільних запитів (slow_query_log), виявлення прихованих блокувань Using filesort та додавання покриваючих префіксних індексів.

Як це працює#

При оптимізації бази даних необхідно розуміти архітектуру фізичного виконання запиту в MySQL 8.4+:

Чому OFFSET 100000 вбиває сервер#

Оператор LIMIT 20 OFFSET 100000 змушує базу даних обчислити порядок сортування для перших 100 020 записів. Якщо сортування не може бути виконане суворо за індексом, MySQL створює тимчасову таблицю в оперативній пам'яті (або скидає її на диск у простір tmpdir, якщо розмір перевищує tmp_table_size), сортує дані алгоритмом filesort і відкидає перші 100 000 рядків.

При курсорній пагінації (Keyset Pagination) умова WHERE (post_date, ID) < ('2026-03-01 12:00:00', 89231) дозволяє СКБД спуститися по гілках B-Tree дерева рівно за $\log_B(N)$ операцій дискового читання та одразу почати потокове читання необхідних 20 записів. Час виконання залишається константним (~1 мс) незалежно від того, запитується перша чи мільйонна сторінка.

Продакшн-артефакт: High-Performance Keyset Query Engine#

Нижче наведено спеціалізований рушій запитів на PHP 8.4+, що реалізує курсорну пагінацію, превентивне придушення проблеми N+1 та телеметрію виконання через EXPLAIN FORMAT=JSON.

<?php
declare(strict_types=1);

namespace AgenticLab\Performance;

use wpdb;
use WP_Post;

/**
* High-Performance Keyset Query Engine.
* 
* Оптимізований рушій курсорної пагінації та пакетної гідратації.
*
* Author: Artur Yusupov (Fullstack Agentic Lab)
*/
final class AgenticQueryEngine {
  private wpdb $db;

  public function __construct(?wpdb $db = null) {
      global $wpdb;
      $this->db = $db ?? $wpdb;
  }

  /**
   * Вибірка дописів із курсорною пагінацією (Keyset Pagination).
   *
   * @param string $post_type Тип запису
   * @param int $limit Кількість елементів
   * @param array{date: string, id: int}|null $cursor Курсор пагінації (null для першої сторінки)
   * @return array{items: list<WP_Post>, next_cursor: array{date: string, id: int}|null}
   */
  public function fetch_posts_keyset(
      string $post_type = 'post',
      int $limit = 20,
      ?array $cursor = null
  ): array {
      $limit = max(1, min($limit, 100)); // Обмеження пагінації
      $where_clauses = [
          $this->db->prepare('p.post_type = %s', $post_type),
          "p.post_status = 'publish'",
      ];

      // Додаємо умову курсора, якщо він переданий
      if ($cursor !== null) {
          $where_clauses[] = $this->db->prepare(
              '(p.post_date, p.ID) < (%s, %d)',
              $cursor['date'],
              $cursor['id']
          );
      }

      $where_sql = implode(' AND ', $where_clauses);

      // Запитуємо тільки ID та дату для курсора за B-Tree індексом
      $sql = "
          SELECT p.ID, p.post_date
          FROM {$this->db->posts} p
          WHERE {$where_sql}
          ORDER BY p.post_date DESC, p.ID DESC
          LIMIT %d
      ";

      /** @var list<object{ID: string|int, post_date: string}> $rows */
      $rows = $this->db->get_results(
          $this->db->prepare($sql, $limit + 1)
      );

      $has_more = count($rows) > $limit;
      if ($has_more) {
          array_pop($rows); // Видаляємо перевірочний елемент
      }

      if (empty($rows)) {
          return ['items' => [], 'next_cursor' => null];
      }

      $post_ids = array_map(static fn($r): int => (int)$r->ID, $rows);

      // 1. Пакетна гідратація кешу дописів (Усунення N+1)
      _prime_post_caches($post_ids, true, true);

      // 2. Отримання повністю гідратованих об'єктів WP_Post з оперативної пам'яті
      /** @var list<WP_Post> $posts */
      $posts = [];
      foreach ($post_ids as $id) {
          $post = get_post($id);
          if ($post instanceof WP_Post) {
              $posts[] = $post;
          }
      }

      $last_row = end($rows);
      $next_cursor = $has_more && $last_row !== false ? [
          'date' => $last_row->post_date,
          'id'   => (int)$last_row->ID,
      ] : null;

      return [
          'items'       => $posts,
          'next_cursor' => $next_cursor,
      ];
  }

  /**
   * Аналіз плану виконання SQL-запиту через EXPLAIN FORMAT=JSON.
   *
   * @param string $query_sql Безпечний підготовлений SQL
   * @return array<string, mixed>
   */
  public function explain_query(string $query_sql): array {
      $explain_sql = 'EXPLAIN FORMAT=JSON ' . $query_sql;
      $json_raw = $this->db->get_var($explain_sql);

      if (!is_string($json_raw)) {
          return ['error' => 'Unable to generate EXPLAIN plan'];
      }

      try {
          /** @var array<string, mixed> $result */
          $result = json_decode($json_raw, true, 512, JSON_THROW_ON_ERROR);
          return $result;
      } catch (\JsonException $e) {
          return ['error' => $e->getMessage()];
      }
  }
}

Поширені помилки#

Резюме архітектора#

Оптимізація реляційної бази даних у високонавантажених проєктах на WordPress базується на трьох фундаментальних принципах:

• Усунення EAV та денормалізація: Використання таксономій та кастомних плоских таблиць для атрибутів з інтенсивним пошуком замість wp_postmeta.
• Пакетна гідратація та ліквідація N+1: Обов'язковий виклик _prime_post_caches() та update_post_caches() перед входом у цикли виведення даних.
• Курсорна пагінація (Keyset): Відмова від OFFSET на користь курсорів за складеними B-Tree індексами та обов'язкове відключення підрахунку рядків (no_found_rows => true).