10.3.8 InnoDB and MyISAM Index Statistics Collection — MySQL 9.2

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.