Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як 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 для зачеплених таблиць - це дешево і прибирає цілий клас «раптом повільних» запитів.

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

Схожі питання