14.8.11.2 Configuring Non-Persistent Optimizer Statistics Parameters — MySQL 5.7

14.8.11.2 Настройка параметров статистики неперсистентного оптимизатора

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

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

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

Неперсистентная статистика оптимизатора обновляется, когда:

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

Значение параметра innodb_stats_on_metadata было изменено на OFF, когда персистентная статистика оптимизатора была включена по умолчанию в MySQL 5.6.6. Включение 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 страниц, чего может быть недостаточно для точной оценки, что приведет к неправильному выбору индексов оптимизатором запросов. Этот метод особенно важен для больших таблиц и таблиц, используемых в . Необязательное для таких таблиц может быть существенной проблемой производительности. Смотрите раздел 8.2.1.20 «Избегание полных сканирований таблиц», чтобы получить советы по настройке таких запросов. 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, чем если у вас в основном меньше таблиц.