Селективность индексов и статистика

Оптимизатор MySQL решает, какой индекс использовать, на основе статистики — оценок кардинальности и распределения данных. Если статистика устарела или неточна, оптимизатор принимает неверные решения.

#Селективность и кардинальность

Кардинальность — количество уникальных значений в индексе. Селективность — отношение уникальных значений к общему количеству строк.

-- Кардинальность индексов
SHOW INDEX FROM users;
-- Столбец Cardinality: оценка уникальных значений

-- Селективность столбца
SELECT 
    COUNT(DISTINCT email) / COUNT(*) AS selectivity_email,
    COUNT(DISTINCT status) / COUNT(*) AS selectivity_status
FROM users;

Высокая селективность (близкая к 1.0) означает, что большинство значений уникальны:

email — селективность ~1.0 (почти все уникальны)
status — селективность 0.0001 (много дубликатов, например 2-3 значения)

Индекс с высокой селективностью отлично фильтрует: по WHERE email = ? находится 1-2 строки. Индекс с низкой селективностью может не использоваться — если условие фильтрует 50% таблицы, full table scan часто дешевле.

#Почему оптимизатор игнорирует индекс

Оптимизатор использует cost-based подход: сравнивает стоимость разных планов выполнения. Стоимость зависит от:

Количества строк, которые нужно прочитать (rows в EXPLAIN)
Типа доступа (ALL, range, ref, const)
Дополнительных операций (filesort, temporary)

-- Индекс на status (селективность низкая)
CREATE INDEX idx_status ON orders(status);

-- Запрос: 50% строк со status = 'active'
EXPLAIN SELECT * FROM orders WHERE status = 'active';
-- type: ALL — оптимизатор решил, что scan дешевле

Почему? Для status = 'active' (50% строк) оптимизатор оценивает:

Index lookup: ~500K lookup по кластерному индексу (случайный I/O)
Full table scan: 1M последовательных чтений

Последовательное чтение (scan) быстрее случайного (lookup), поэтому оптимизатор выбирает ALL.

#Обновление статистики

InnoDB не обновляет статистику в реальном времени. Она обновляется:

При ANALYZE TABLE
Автоматически при изменении ~10% строк (innodb_stats_auto_recalc)
При перезапуске сервера

-- Обновить статистику для таблицы
ANALYZE TABLE orders;

-- Проверить актуальность кардинальности
SHOW INDEX FROM orders;

-- Настройка автопересчёта
SET GLOBAL innodb_stats_auto_recalc = ON; -- по умолчанию ON

Проблема: после массовой вставки 1 миллиона строк статистика может быть устаревшей. Оптимизатор 'думает', что таблица маленькая, и выбирает suboptimal план. Решение — запускать ANALYZE TABLE после массовых операций.

#Persistent статистика

MySQL 5.6+ сохраняет статистику на диске (persistent):

SHOW VARIABLES LIKE 'innodb_stats_persistent';
-- ON — статистика сохраняется между перезапусками

-- Размер выборки для оценки кардинальности
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';
-- По умолчанию 20; больше = точнее, но медленнее ANALYZE

Persistent статистика обеспечивает стабильные планы выполнения — после перезапуска сервера оптимизатор использует те же оценки. Без persistent статистики (OFF) оценки сбрасываются при restart, что может привести к разным планам до/после перезагрузки.

#Skewed Data Distribution

Неравномерное распределение данных — частая причина ошибок оптимизатора:

-- orders: 90% 'completed', 2% 'pending', 8% другие
SELECT status, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders) AS pct
FROM orders GROUP BY status;

Оптимизатор использует среднюю кардинальность. Он 'думает', что каждое значение status возвращает ~20% строк. Реальность:

WHERE status = 'completed' → 90% строк (оптимизатор недооценивает, выберет index lookup вместо scan — плохо)
WHERE status = 'pending' → 2% строк (оптимизатор переоценивает, выберет scan вместо index lookup — неоптимально)

В MySQL 8.0 можно использовать гистограммы для столбцов без индекса:

-- Создать гистограмму для столбца
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- Удалить гистограмму
ANALYZE TABLE orders DROP HISTOGRAM ON status;

Гистограмма хранит распределение значений и помогает оптимизатору точнее оценивать селективность для неравномерных данных.

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

Запускайте ANALYZE TABLE после массовых операций. ETL-загрузка, миграции, архивирование — все они меняют распределение данных.

Проверяйте селективность перед созданием индекса. Если COUNT(DISTINCT column) / COUNT(*) < 0.01, индекс может не использоваться.

Мониторьте стабильность планов. Если один и тот же запрос иногда медленный — возможно, статистика меняется и оптимизатор выбирает разные планы.