8.3.7 InnoDB and MyISAM Index Statistics Collection — MySQL 5.7

8.3.7 Сбор статистики индексов 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.