ANALYZE TABLE возвращает результирующий набор со столбцами, показанными в следующей таблице.

ANALYZE TABLE без HISTOGRAM определания выполняет анализ распределения ключей и сохраняет распределение для таблицы или таблиц. Любая существующая статистика гистограмм остаётся неизменной.

Если таблица не изменилась с момента последнего анализа распределения ключей, таблица больше не анализируется.

MySQL использует сохранённое распределение ключей, чтобы определить порядок объединения таблиц для объединений, не основанных на константах. Кроме того, распределения ключей могут использоваться при выборе индексов для конкретной таблицы в запросе.

Чтобы проверить кардинальность сохранённого распределения ключей, используйте SHOW INDEX заявление или INFORMATION_SCHEMA STATISTICS таблица. См. Раздел 15.7.7.24, «Заявление SHOW INDEX» и Раздел 28.3.36, «Таблица INFORMATION_SCHEMA STATISTICS».

Для InnoDB таблиц, ANALYZE TABLE определяет кардинальность индекса, выполняя случайные проходы по каждому дереву индекса и соответственно обновляя оценки кардинальности индекса. Поскольку это только оценки, повторные запуски ANALYZE TABLE могут давать разные результаты. Это делает ANALYZE TABLE быстрым на InnoDB таблицах, но не на 100% точным, поскольку не учитываются все строки.

Вы можете сделать собранные ANALYZE TABLE более точными и стабильными, включив innodb_stats_persistent, как описано в Разделе 17.8.10.1, «Настройка параметров сохраняемых статистических данных оптимизатора». Когда innodb_stats_persistent включён, важно запустить ANALYZE TABLE после крупных изменений в данных столбцов индекса, поскольку статистика не пересчитывается периодически (например, после перезагрузки сервера).

Если innodb_stats_persistent включён, вы можете изменить количество случайных проходов, изменив системную переменную innodb_stats_persistent_sample_pages. Если innodb_stats_persistent выключен, измените innodb_stats_transient_sample_pages вместо этого.

Дополнительную информацию об анализе распределения ключей в InnoDB см. в Разделе 17.8.10.1, «Настройка параметров сохраняемых статистических данных оптимизатора» и Разделе 17.8.10.3, «Оценивание сложности ANALYZE TABLE для таблиц InnoDB».

MySQL использует оценки кардинальности индекса при оптимизации объединений. Если объединение не оптимизировано должным образом, попробуйте запустить ANALYZE TABLE. В тех редких случаях, когда ANALYZE TABLE не даёт достаточно хороших значений для ваших конкретных таблиц, вы можете использовать FORCE INDEX со своими запросами, чтобы принудительно использовать определённый индекс, или установить системную переменную max_seeks_for_key, чтобы гарантировать, что MySQL предпочитает поиск по индексу сканированию таблицы. См. Раздел B.3.5, «Вопросы, связанные с оптимизатором».

ANALYZE TABLE с клаузой HISTOGRAM позволяет управлять статистикой гистограмм для значений столбцов таблицы. Сведения о статистике гистограмм см. в разделе 10.9.6, «Статистика оптимизатора».

Доступны следующие операции с гистограммами:

Команды управления сохранёнными гистограммами влияют только на указанные столбцы. Рассмотрим эти команды:

ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS;
ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;
ANALYZE TABLE t DROP HISTOGRAM ON c2;

Первая команда обновляет гистограммы для столбцов c1, c2 и c3, заменяя любые существующие гистограммы для этих столбцов. Вторая команда обновляет гистограммы для c1 и c3, оставляя гистограмму c2 без изменений. Третья команда удаляет гистограмму для c2, оставляя гистограммы для c1 и c3 без изменений.

При выборке выборочных данных в качестве части создания гистограммы не все значения считываются; это может привести к пропускам некоторых важных значений. В таких случаях может быть полезно изменить гистограмму или явно задать собственную гистограмму на основе собственных критериев, таких как полный набор данных. ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name USING DATA 'json_data' обновляет столбец таблицы гистограммы данными в том же формате JSON, который используется для отображения значений столбца HISTOGRAM из таблицы Information Schema COLUMN_STATISTICS. При обновлении гистограммы данными JSON может быть изменён только один столбец.

Приведём пример использования USING DATA, сперва сгенерировав гистограмму для столбца c1 таблицы t:

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1;
+-------+-----------+----------+-----------------------------------------------+
| Table | Op        | Msg_type | Msg_text                                      |
+-------+-----------+----------+-----------------------------------------------+
| h.t   | histogram | status   | Histogram statistics created for column 'c1'. |
+-------+-----------+----------+-----------------------------------------------+
1 row in set (0.00 sec)

Мы можем увидеть сгенерированную гистограмму в таблице COLUMN_STATISTICS:

mysql> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: h
 TABLE_NAME: t
COLUMN_NAME: c1
  HISTOGRAM: {"buckets": [], "data-type": "int", "auto-update": false,
"null-values": 0.0, "collation-id": 8, "last-updated": "2024-03-26
16:54:43.674995", "sampling-rate": 1.0, "histogram-type": "singleton",
"number-of-buckets-specified": 100}
1 row in set (0.00 sec)

Теперь мы удаляем гистограмму, и когда мы проверяем COLUMN_STATISTICS, она пуста:

mysql> ANALYZE TABLE t DROP HISTOGRAM ON c1;
+-------+-----------+----------+-----------------------------------------------+
| Table | Op        | Msg_type | Msg_text                                      |
+-------+-----------+----------+-----------------------------------------------+
| h.t   | histogram | status   | Histogram statistics removed for column 'c1'. |
+-------+-----------+----------+-----------------------------------------------+
1 row in set (0.01 sec)

mysql> TABLE information_schema.column_statistics\G
Empty set (0.00 sec)

Мы можем восстановить удалённую гистограмму, вставив её JSON-представление, полученное ранее из столбца HISTOGRAM таблицы COLUMN_STATISTICS, и когда мы снова запросим эту таблицу, мы увидим, что гистограмма была восстановлена в исходном состоянии:

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1
    ->     USING DATA '{"buckets": [], "data-type": "int", "auto-update": false,
    ->               "null-values": 0.0, "collation-id": 8, "last-updated": "2024-03-26
    ->               16:54:43.674995", "sampling-rate": 1.0, "histogram-type": "singleton",
    ->               "number-of-buckets-specified": 100}';
+-------+-----------+----------+-----------------------------------------------+
| Table | Op        | Msg_type | Msg_text                                      |
+-------+-----------+----------+-----------------------------------------------+
| h.t   | histogram | status   | Histogram statistics created for column 'c1'. |
+-------+-----------+----------+-----------------------------------------------+

mysql> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: h
 TABLE_NAME: t
COLUMN_NAME: c1
  HISTOGRAM: {"buckets": [], "data-type": "int", "auto-update": false,
"null-values": 0.0, "collation-id": 8, "last-updated": "2024-03-26
16:54:43.674995", "sampling-rate": 1.0, "histogram-type": "singleton",
"number-of-buckets-specified": 100}

Генерирование гистограмм не поддерживается для зашифрованных таблиц (чтобы избежать раскрытия данных в статистике) или TEMPORARY таблиц.

Генерирование гистограмм применяется к столбцам всех типов данных, кроме типов геометрии (пространственные данные) и JSON.

Гистограммы могут быть сгенерированы для хранимых и виртуальных сгенерированных столбцов.

Гистограммы не могут быть сгенерированы для столбцов, которые покрываются уникальными индексами по одному столбцу.

Команды управления гистограммами пытаются выполнить как можно больше запрошенной операции и сообщают диагностические сообщения об оставшейся части. Например, если команда UPDATE HISTOGRAM называет несколько столбцов, но некоторые из них не существуют или имеют неподдерживаемый тип данных, гистограммы генерируются для других столбцов, а сообщения генерируются для недопустимых столбцов.

На гистограммы влияют эти команды DDL:

Системная переменная histogram_generation_max_mem_size контролирует максимальный объём памяти, доступный для генерации гистограмм. Глобальные и сессионные значения могут быть установлены во время выполнения.

Изменение глобального значения histogram_generation_max_mem_size требует привилегий, достаточных для установки глобальных системных переменных. Изменение сессионного значения histogram_generation_max_mem_size требует привилегий, достаточных для установки ограниченных сессионных системных переменных. См. раздел 7.1.9.1, «Привилегии на системные переменные».

Если рассчитанный объём данных, подлежащих чтению в память для генерации гистограммы, превышает предел, определённый histogram_generation_max_mem_size, MySQL выбирает данные, а не читает все данные в память. Выборка распределяется равномерно по всей таблице. MySQL использует SYSTEM выбороку, которая является методом выборки на уровне страницы.

Значение sampling-rate в столбце HISTOGRAM таблицы Information Schema COLUMN_STATISTICS может быть запрошено для определения доли данных, которые были выбраны для создания гистограммы. Значение sampling-rate — число от 0,0 до 1,0. Значение 1 означает, что все данные были прочитаны (без выборки).

Следующий пример демонстрирует выборку. Чтобы убедиться, что объём данных превышает предел histogram_generation_max_mem_size для целей примера, предел устанавливается на низкое значение (2000000 байт) до генерации статистики гистограммы для столбца birth_date таблицы employees.

mysql> SET histogram_generation_max_mem_size = 2000000;

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> SELECT HISTOGRAM->>'$."sampling-rate"'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
       WHERE TABLE_NAME = "employees"
       AND COLUMN_NAME = "birth_date";
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 0.0491431208869665              |
+---------------------------------+

Значение sampling-rate 0,0491431208869665 означает, что приблизительно 4,9% данных из столбца birth_date были загружены в память для генерации статистики гистограмм.

Слой хранения InnoDB предоставляет собственную реализацию выборки для данных, хранящихся в InnoDB таблицах. По умолчанию реализация выборки, используемая MySQL, когда слои хранения не предоставляют свою собственную, требует полного сканирования таблицы, что является дорогостоящим для больших таблиц. Реализация выборки InnoDB улучшает производительность выборки, избегая полного сканирования таблицы.

Счётчики sampled_pages_read и sampled_pages_skipped INNODB_METRICS могут использоваться для мониторинга выборки InnoDB страниц данных. (Для общей информации об использовании счётчиков INNODB_METRICS см. раздел 28.4.21, «Таблица INFORMATION_SCHEMA INNODB_METRICS».)

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

mysql> SET GLOBAL innodb_monitor_enable = 'sampled%';

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> USE INFORMATION_SCHEMA;

mysql> SELECT NAME, COUNT FROM INNODB_METRICS WHERE NAME LIKE 'sampled%'\G
*************************** 1. row ***************************
 NAME: sampled_pages_read
COUNT: 43
*************************** 2. row ***************************
 NAME: sampled_pages_skipped
COUNT: 843

Эта формула приблизительно вычисляет показатель выборки на основе данных счётчика выборки:

sampling rate = sampled_page_read/(sampled_pages_read + sampled_pages_skipped)

Скорость выборки, основанная на данных счётчика выборки, примерно совпадает со значением sampling-rate в столбце HISTOGRAM таблицы Information Schema COLUMN_STATISTICS.

Для получения информации об операциях выделения памяти, выполняемых для генерации гистограмм, отслеживайте инструмент Performance Schema memory/sql/histograms. См. раздел 29.12.20.10, «Таблицы сводки памяти».

ANALYZE TABLE очищает статистику таблицы из схемы информации INNODB_TABLESTATS таблицы и устанавливает столбец STATS_INITIALIZED в значение Uninitialized. Статистика собирается снова при следующем обращении к таблице.