17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters — MySQL 8.4

17.8.10.2 Настройка параметров статистики оптимизатора без сохранения в базе данных

В этом разделе описывается, как настроить статистику оптимизатора без сохранения в базе данных. Статистика оптимизатора не сохраняется на диск, когда innodb_stats_persistent=OFF или когда отдельные таблицы создаются или изменяются с помощью STATS_PERSISTENT=0. Вместо этого статистика хранится в оперативной памяти и теряется при выключении сервера. Статистика также периодически обновляется некоторыми операциями и при определенных условиях.

По умолчанию статистика оптимизатора сохраняется на диск, что включено параметром конфигурации innodb_stats_persistent. Дополнительную информацию о статистике оптимизатора, сохраняемой в базе данных, см. в Разделе 17.8.10.1, «Настройка параметров статистики оптимизатора, сохраняемой в базе данных».

Обновление статистики оптимизатора

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

Выполнении ANALYZE TABLE.
Выполнении SHOW TABLE STATUS, SHOW INDEX или запросе к таблицам Информационной схемы TABLES или STATISTICS с включенным параметром innodb_stats_on_metadata.

Значение по умолчанию для innodb_stats_on_metadata равно OFF. Включение innodb_stats_on_metadata может снизить скорость доступа к схемам с большим количеством таблиц или индексов, а также снизить стабильность планов выполнения запросов, которые включают InnoDB таблиц. innodb_stats_on_metadata настраивается глобально с помощью оператора SET.
```
SET GLOBAL innodb_stats_on_metadata=ON
```
Примечание

innodb_stats_on_metadata применяется только тогда, когда статистика оптимизатора настроена на не сохранение в базе данных (когда innodb_stats_persistent отключен).
Запуск клиента mysql с включенным параметром --auto-rehash, что является значением по умолчанию. Параметр auto-rehash приводит к открытию всех InnoDB таблиц, а операции открытия таблиц приводят к перерасчёту статистики.

Для повышения времени запуска клиента mysql и обновления статистики можно отключить auto-rehash с помощью опции --disable-auto-rehash. Функция auto-rehash позволяет автоматически завершать ввод имен баз данных, таблиц и столбцов для интерактивных пользователей.
Таблица открывается впервые.
InnoDB обнаруживает, что 1/16 таблицы была изменена с момента последнего обновления статистики.

Настройка количества выборочных страниц

Оптимизатор запросов MySQL использует приблизительные оценки распределения ключей для выбора индексов в плане выполнения, основываясь на относительной частоте использования индекса. При обновлении оптимизатором статистики InnoDB он выбирает случайные страницы из каждого индекса в таблице, чтобы оценить частоту использования индекса. (Этот метод известен как ...)

Чтобы контролировать качество оценки статистики (и, таким образом, получить лучшую информацию для оптимизатора запросов), вы можете изменить количество выборочных страниц, используя параметр innodb_stats_transient_sample_pages. По умолчанию используется 8 выборочных страниц, что может быть недостаточно для получения точной оценки, что приводит к неправильному выбору индексов оптимизатором запросов. Этот метод особенно важен для больших таблиц и таблиц, используемых в ... Необходимые ... для таких таблиц могут быть существенной проблемой производительности. См. Раздел 10.2.1.23, «Избегание полных сканирований таблиц» для советов по настройке таких запросов. innodb_stats_transient_sample_pages — глобальный параметр, который можно установить во время выполнения.

Значение innodb_stats_transient_sample_pages влияет на выборку индексов для всех InnoDB таблиц и индексов, когда innodb_stats_persistent=0. Обратите внимание на следующие потенциально значимые последствия при изменении размера выборки индексов:

Малые значения, такие как 1 или 2, могут привести к неточным оценкам кардинальности.
Увеличение значения innodb_stats_transient_sample_pages может потребовать больше операций чтения с диска. Значения намного больше 8 (скажем, 100) могут значительно замедлить время открытия таблицы или выполнения SHOW TABLE STATUS.
Оптимизатор может выбрать совершенно другие планы запросов на основе различных оценок селективности индекса.

Какое бы значение innodb_stats_transient_sample_pages ни было оптимальным для вашей системы, установите его и оставьте в таком состоянии. Выберите значение, которое обеспечивает достаточно точные оценки для всех таблиц в вашей базе данных без чрезмерного использования ввода-вывода. Поскольку статистика автоматически пересчитывается в разное время, а не при выполнении ANALYZE TABLE, нет смысла увеличивать размер выборки индекса, выполнять ANALYZE TABLE, а затем снова уменьшать размер выборки.

Для меньших таблиц обычно требуется меньше выборок индексов, чем для больших таблиц. Если ваша база данных содержит много больших таблиц, рассмотрите возможность использования большего значения для innodb_stats_transient_sample_pages, чем если бы у вас было много меньших таблиц.