Кардинальность, селективность, обновление статистики, влияние на выбор оптимизатора
Оптимизатор 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 подход: сравнивает стоимость разных планов выполнения. Стоимость зависит от:
-- Индекс на status (селективность низкая)
CREATE INDEX idx_status ON orders(status);
-- Запрос: 50% строк со status = 'active'
EXPLAIN SELECT * FROM orders WHERE status = 'active';
-- type: ALL — оптимизатор решил, что scan дешевлеПочему? Для status = 'active' (50% строк) оптимизатор оценивает:
Последовательное чтение (scan) быстрее случайного (lookup), поэтому оптимизатор выбирает ALL.
InnoDB не обновляет статистику в реальном времени. Она обновляется:
ANALYZE TABLEinnodb_stats_auto_recalc)-- Обновить статистику для таблицы
ANALYZE TABLE orders;
-- Проверить актуальность кардинальности
SHOW INDEX FROM orders;
-- Настройка автопересчёта
SET GLOBAL innodb_stats_auto_recalc = ON; -- по умолчанию ONПроблема: после массовой вставки 1 миллиона строк статистика может быть устаревшей. Оптимизатор 'думает', что таблица маленькая, и выбирает suboptimal план. Решение — запускать ANALYZE TABLE после массовых операций.
MySQL 5.6+ сохраняет статистику на диске (persistent):
SHOW VARIABLES LIKE 'innodb_stats_persistent';
-- ON — статистика сохраняется между перезапусками
-- Размер выборки для оценки кардинальности
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';
-- По умолчанию 20; больше = точнее, но медленнее ANALYZEPersistent статистика обеспечивает стабильные планы выполнения — после перезапуска сервера оптимизатор использует те же оценки. Без persistent статистики (OFF) оценки сбрасываются при restart, что может привести к разным планам до/после перезагрузки.
Неравномерное распределение данных — частая причина ошибок оптимизатора:
-- 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, индекс может не использоваться.
Мониторьте стабильность планов. Если один и тот же запрос иногда медленный — возможно, статистика меняется и оптимизатор выбирает разные планы.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.