Как бороться с неверной статистикой Explain?

Борьба с некорректной статистикой в EXPLAIN: стратегии и практические методы

Проблема некорректной статистики — одна из самых коварных в оптимизации SQL-запросов. Когда EXPLAIN показывает неоптимальный план выполнения, основанный на устаревших или неточных метаданных, это приводит к катастрофическому падению производительности. Вот системный подход к решению этой проблемы.

Диагностика проблемы

Сначала убедитесь, что проблема именно в статистике:

-- Проверка времени последнего обновления статистики для таблицы
SELECT 
    table_name,
    update_time,
    table_rows
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
    AND table_name = 'your_table';

-- Анализ расхождений между реальными данными и статистикой
SELECT COUNT(*) as real_count FROM your_table WHERE some_column = 'value';
-- Сравните с cardinality в EXPLAIN для этого предиката

Основные стратегии решения

1. Принудительное обновление статистики

-- Для таблицы
ANALYZE TABLE your_table;

-- Для конкретного индекса (MySQL 8.0+)
ANALYZE TABLE your_table UPDATE HISTOGRAM ON column1, column2;

-- Для всей базы
mysqlcheck --analyze your_database

2. Настройка глубины статистики

В MySQL настройте параметры:

-- Увеличьте количество страниц для анализа
SET GLOBAL innodb_stats_persistent_sample_pages = 2000;
SET GLOBAL innodb_stats_transient_sample_pages = 2000;

-- Для Percona/MySQL 8.0 используйте гистограммы
ANALYZE TABLE table_name PERSISTENT FOR COLUMNS (col1, col2) BUCKETS 100;

3. Использование оптимизаторных подсказок (Hints)

Когда статистика не может быть исправлена быстро:

SELECT /*+ INDEX(your_table idx_column) */ *
FROM your_table 
WHERE column = 'value';

-- Или FORCE INDEX для критических случаев
SELECT * FROM your_table FORCE INDEX (idx_column) 
WHERE column = 'value';

Практические рекомендации

Проактивный мониторинг:

• Настройте регулярный сбор статистики для часто изменяемых таблиц
• Мониторьте расхождения между table_rows и реальным количеством записей
• Используйте Percona Monitoring или подобные инструменты для отслеживания

Архитектурные решения:

• Разделение горячих и холодных данных — статистика по историческим данным меняется реже
• Партиционирование — статистика собирается по партициям отдельно
• Материализованные представления для сложных агрегаций с контролируемой пересборкой

Работа с нестабильными данными:

-- Для таблиц с резко меняющимся распределением
SET SESSION optimizer_switch='condition_fanout_filter=off';

-- Используйте динамические запросы с разными планами
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;

Автоматизация процесса

Создайте скрипт для регулярной проверки:

class StatisticsMonitor {
    public function checkAndFixStatistics(PDO $pdo, string $table): void {
        // Проверяем расхождения
        $stmt = $pdo->query("SHOW TABLE STATUS LIKE '{$table}'");
        $status = $stmt->fetch();
        
        $realCount = $pdo->query("SELECT COUNT(*) FROM {$table}")->fetchColumn();
        
        if (abs($status['Rows'] - $realCount) / $realCount > 0.3) {
            // Расхождение >30%
            $pdo->exec("ANALYZE TABLE {$table}");
            $this->logStatisticsRebuild($table);
        }
    }
    
    public function analyzeProblematicQueries(array $slowQueries): void {
        foreach ($slowQueries as $query) {
            $plan = $this->explainQuery($query);
            if ($this->hasCardinalityMismatch($plan)) {
                $this->suggestIndexesOrAnalyze($query);
            }
        }
    }
}

Экстренные меры

1. Временное решение до обновления статистики:

-- Использование подзапросов для ограничения выбора оптимизатора
SELECT * FROM (
    SELECT * FROM your_table 
    WHERE conditions
    ORDER BY indexed_column
) AS subquery LIMIT 1000;

1. Пересборка всей статистики БД в период низкой нагрузки:

mysqlcheck --analyze --all-databases

Долгосрочная стратегия

• Регламентные работы: ежедневное обновление статистики для OLTP-таблиц
• Версионирование планов запросов: использование Query Plan Management в MySQL 8.0+
• A/B тестирование планов: внедрение механизмов сравнения эффективности разных планов

Ключевой принцип: бороться с некорректной статистикой нужно системно, сочетая автоматический мониторинг, регулярное обслуживание и архитектурные решения. Наиболее эффективен превентивный подход, когда статистика обновляется до того, как вызовет проблемы в production.