Как бороться с неверной статистикой в плане запроса (EXPLAIN)?

Неверная статистика приводит к выбору субоптимальных планов выполнения (например, сканирование таблицы вместо использования индекса). Вот как с этим бороться:

1. Принудительное обновление статистики: В MySQL/MariaDB используйте ANALYZE TABLE. В PostgreSQL — ANALYZE.

-- MySQL
ANALYZE TABLE orders, order_items;

-- PostgreSQL
ANALYZE orders;
ANALYZE order_items;

Это предпочтительный метод, так как он не меняет запрос.

2. Использование подсказок (hints) для оптимизатора: Если обновление статистики не помогает или нужно временное решение.

-- MySQL: FORCE INDEX, USE INDEX, IGNORE INDEX
SELECT * FROM orders FORCE INDEX (idx_customer_status) 
WHERE customer_id = 123 AND status = 'shipped';

-- PostgreSQL: SET для конкретной сессии или использование pg_hint_plan
/*+ IndexScan(orders idx_customer_status) */
SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';

3. Рефакторинг запроса: Иногда можно помочь оптимизатору, переписав запрос.

• Упрощение условий: Разбейте сложный OR на UNION.

• Избегание функций в предикатах:

  -- ПЛОХО: Индекс по created_at не будет использован.
  WHERE DATE(created_at) = '2023-10-01';
  
  -- ХОРОШО: Используется диапазонный поиск по индексу.
  WHERE created_at >= '2023-10-01' AND created_at < '2023-10-02';

• Использование подзапросов/CTE: Чтобы изолировать части запроса.

4. Проверка реального выполнения с EXPLAIN ANALYZE: Запустите запрос с EXPLAIN ANALYZE (в PostgreSQL; в MySQL 8.0+ — EXPLAIN ANALYZE FORMAT=TREE). Это выполнит запрос и покажет фактические затраты времени и строк, что позволит сравнить с оценками оптимизатора и точно выявить проблему.

5. Глубокая настройка: Если проблема систематическая, возможно, нужна коррекция:

• Увеличение размера выборки для ANALYZE (например, ANALYZE TABLE orders PERSISTENT FOR ALL; в MySQL).
• Создание более селективных или составных индексов.
• Рассмотрение партиционирования таблицы, если статистика по разным периодам сильно различается.