Оптимизация 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 дерева ровно за O(log 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).