Як InnoDB збирає статистику для оптимізатора і навіщо гістограми?

Оптимізатор обирає план за оцінками: скільки рядків у таблиці, скільки різних значень в індексі, скільки рядків підійде під умову. Якщо оцінки хибні, план теж.

Статистика індексів InnoDB:

• постійна (innodb_stats_persistent = ON, за замовчуванням) - зберігається в mysql.innodb_table_stats і mysql.innodb_index_stats, переживає перезапуск, тож плани стабільні;
• збирається вибірково: читається innodb_stats_persistent_sample_pages (20) випадкових сторінок індексу, а не весь індекс. Для великих таблиць з нерівномірними даними цього може бути замало;
• автоматично перераховується (innodb_stats_auto_recalc), коли змінилося понад 10% рядків, - у фоні, з невеликою затримкою.

Коли статистика підводить:

• щойно завантажено великий обсяг даних - автоперерахунок ще не відбувся;
• зміни малі відносно таблиці (менше 10%), але зачепили саме «гарячу» частину;
• сильний перекіс: 20 сторінок вибірки не відображають розподілу.

Що робити:

ANALYZE TABLE orders;   -- перерахувати статистику зараз

-- для конкретної великої таблиці - більше сторінок вибірки
ALTER TABLE orders STATS_SAMPLE_PAGES = 200;

ANALYZE TABLE для InnoDB швидкий (та сама вибірка сторінок), але викликає неявний коміт, тож усередині транзакції застосунку його не запускають.

Гістограми (MySQL 8.0+) описують розподіл значень колонки:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, country WITH 64 BUCKETS AUTO UPDATE;

SELECT column_name, JSON_EXTRACT(histogram, '$."histogram-type"')
FROM information_schema.column_statistics WHERE table_name = 'orders';

Навіщо вони, якщо є статистика індексів. Для колонок з індексом оптимізатор оцінює рядки через index dives. Для колонок без індексу він знає лише кількість рядків і бере грубі припущення. Гістограма каже, що status = 'refunded' - це 0,1% таблиці, а status = 'paid' - 90%. Це впливає на порядок з'єднань і вибір способу доступу до інших таблиць.

Особливості гістограм:

• корисні для колонок без індексу, з нерівномірним розподілом, що з'являються в умовах і з'єднаннях;
• не створюються для колонок з одноколонковим унікальним індексом;
• за замовчуванням оновлюються лише вручну. У MySQL 8.4 з'явилася опція AUTO UPDATE - тоді гістограму оновлює і ANALYZE TABLE, і фоновий перерахунок статистики;
• побудова гістограми читає таблицю (з вибіркою в межах histogram_generation_max_mem_size), тож на великих таблицях її варто запускати поза піком.

Добра практика: після масового імпорту чи міграції даних запускати ANALYZE TABLE для зачеплених таблиць - це дешево і прибирає цілий клас «раптом повільних» запитів.

Докладніше в документації: Постійна статистика оптимізатора