10.3.8 InnoDB and MyISAM Index Statistics Collection — MySQL 8.4

10.3.8 Сбор статистики индексов InnoDB и MyISAM

Двигатели хранения собирают статистику о таблицах для использования оптимизатором. Статистика таблиц основана на группах значений, где группа значений — это набор строк с одинаковым префиксом ключа. Для целей оптимизатора важной статистикой является средний размер группы значений.

MySQL использует средний размер группы значений следующим образом:

Для оценки количества строк, которые необходимо прочитать для каждого доступа ref
Для оценки количества строк, которые создаёт частичное объединение, то есть количество строк, полученное операцией вида
```
(...) JOIN tbl_name ON tbl_name.key = expr
```

По мере увеличения среднего размера группы значений для индекса, индекс становится менее полезным для этих двух целей, поскольку увеличивается среднее количество строк на запрос. Для того, чтобы индекс был полезен для целей оптимизации, лучше всего, чтобы каждое значение индекса нацеливалось на небольшое количество строк в таблице. Когда данное значение индекса возвращает большое количество строк, индекс становится менее полезным, и MySQL менее вероятно будет его использовать.

Средний размер группы значений связан с мощностью таблицы, которая представляет собой количество групп значений. Выражение SHOW INDEX отображает значение мощности, основанное на N/S, где N — количество строк в таблице, а S — средний размер группы значений. Это соотношение даёт приблизительное количество групп значений в таблице.

Для объединения, основанного на операторе сравнения <=>, значение NULL не обрабатывается по-другому, чем любое другое значение: NULL <=> NULL, так же как и N <=> N для любого другого N.

Однако для объединения, основанного на операторе =, значение NULL отличается от значений, не являющихся NULL: expr1 = expr2 неверно, когда expr1 или expr2 (или оба) являются NULL. Это влияет на доступ ref для сравнений вида tbl_name.key = expr: MySQL не обращается к таблице, если текущее значение expr является NULL, потому что сравнение не может быть истинным.

Для сравнений = не имеет значения, сколько NULL значений находится в таблице. Для целей оптимизации соответствующим значением является средний размер групп значений, не являющихся NULL. Однако MySQL в настоящее время не позволяет собирать или использовать этот средний размер.

Для таблиц InnoDB и MyISAM вы можете контролировать сбор статистики таблиц с помощью системных переменных innodb_stats_method и myisam_stats_method соответственно. Эти переменные имеют три возможных значения, которые отличаются следующим образом:

Когда переменная установлена в nulls_equal, все NULL значения обрабатываются как идентичные (то есть они все образуют одну группу значений).

Если размер группы значений NULL значительно выше среднего размера группы значений, не являющихся NULL, этот метод смещает средний размер группы значений вверх. Это заставляет индекс казаться оптимизатору менее полезным, чем он есть на самом деле, для объединений, которые ищут значения, не являющиеся NULL. Соответственно, метод nulls_equal может привести к тому, что оптимизатор не будет использовать индекс для доступа ref, когда это необходимо.
Когда переменная установлена в nulls_unequal, значения NULL не считаются одинаковыми. Вместо этого каждое значение NULL образует отдельную группу значений размером 1.

Если у вас много значений NULL, этот метод смещает средний размер группы значений вниз. Если средний размер группы значений, не являющихся NULL, велик, подсчёт значений NULL как каждой отдельной группы размером 1 заставляет оптимизатор переоценивать ценность индекса для объединений, которые ищут значения, не являющиеся NULL. Соответственно, метод nulls_unequal может заставить оптимизатор использовать этот индекс для поисков ref, когда другие методы могут быть лучше.
Когда переменная установлена в nulls_ignored, значения NULL игнорируются.

Если вы часто используете объединения, использующие <=>, а не =, значения NULL не являются особыми в сравнениях, и одно NULL равно другому. В этом случае nulls_equal является подходящим методом статистики.

Системная переменная innodb_stats_method имеет глобальное значение; системная переменная myisam_stats_method имеет как глобальные, так и сеансовые значения. Установка глобального значения влияет на сбор статистики для таблиц соответствующего двигателя хранения. Установка сеансового значения влияет на сбор статистики только для текущего подключения клиента. Это означает, что вы можете принудительно перегенерировать статистику таблицы с заданным методом, не затрагивая других клиентов, установив сеансовое значение myisam_stats_method.

Для перегенерации статистики таблицы MyISAM вы можете использовать любой из следующих методов:

Выполните myisamchk --stats_method=method_name --analyze
Измените таблицу, чтобы её статистика устарела (например, вставьте строку, а затем удалите её), а затем установите myisam_stats_method и выполните оператор ANALYZE TABLE

Некоторые замечания по использованию innodb_stats_method и myisam_stats_method:

Вы можете явно заставить собрать статистику таблицы, как только что описано. Однако MySQL также может собирать статистику автоматически. Например, если в ходе выполнения запросов к таблице некоторые из этих запросов изменяют таблицу, MySQL может собирать статистику. (Это может произойти при массовых операциях вставки или удаления или некоторых операторах ALTER TABLE, например.) Если это произойдёт, статистика собирается с использованием значения innodb_stats_method или myisam_stats_method на момент сбора статистики. Таким образом, если вы собираете статистику с использованием одного метода, но системная переменная установлена на другой метод, когда статистика таблицы собирается автоматически позже, используется другой метод.
Нет способа узнать, какой метод был использован для генерации статистики для данной таблицы.
Эти переменные применяются только к таблицам InnoDB и MyISAM. Другие двигатели хранения имеют только один метод сбора статистики таблиц. Обычно он ближе к методу nulls_equal.