17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters — MySQL 9.2

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 ни подошло лучше всего для системы, установите его и оставьте на этом значении. Выберите значение, которое приводит к разумно точным оценкам для всех таблиц в вашей базе данных без излишнего I/O. Поскольку статистика автоматически пересчитывается в разное время, помимо выполнения ANALYZE TABLE, увеличение размера выборки индексов, запуск ANALYZE TABLE и последующее уменьшение размера выборки не имеет смысла.

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