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

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

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

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

Чтобы проверить кардинальность сохранённого распределения ключей, используйте оператор SHOW INDEX или таблицу INFORMATION_SCHEMA STATISTICS. См. Раздел 15.7.7.23, «Оператор SHOW INDEX» и Раздел 28.3.34, «Таблица 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. Статистика собирается снова при следующем обращении к таблице.