MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.1 ANALYZE TABLE Statement

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / ANALYZE TABLE Statement

15.7.3.1 ANALYZE TABLE 语句

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]
    [{MANUAL | AUTO} UPDATE]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] 
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [USING DATA 'json_data']

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

ANALYZE TABLE 生成表统计信息：

ANALYZE TABLE 没有任何HISTOGRAM子句执行键分布分析，并将其存储到指定的表或表中。对于MyISAM表，ANALYZE TABLE 键分布分析等同于使用myisamchk --analyze。
ANALYZE TABLE 带有UPDATE HISTOGRAM子句生成表列的直方图统计信息，并将其存储到数据字典中。只允许使用该语法指定一个表名。MySQL 还支持将单个列的直方图设置为用户定义的 JSON 值。
ANALYZE TABLE 带有DROP HISTOGRAM子句从数据字典中删除表列的直方图统计信息。只允许使用该语法指定一个表名。

该语句需要对表的SELECT和INSERT权限。

ANALYZE TABLE支持InnoDB、NDB和MyISAM表，不支持视图。

如果启用了innodb_read_only系统变量，ANALYZE TABLE可能失败，因为它不能更新数据字典中的统计表，这些表使用InnoDB。即使操作更新了表本身（例如，如果是MyISAM表），也可能出现失败。要获得更新的分布统计信息，设置information_schema_stats_expiry=0。

ANALYZE TABLE 对分区表支持，并且可以使用ALTER TABLE ... ANALYZE PARTITION对一个或多个分区进行分析；更多信息请见第15.1.9节，“ALTER TABLE 语句”，和第26.3.4节，“分区维护”。

在分析过程中，对于InnoDB和MyISAM表格，使用读锁定。

默认情况下，服务器将ANALYZE TABLE语句写入二进制日志，以便于复制到副本。要抑制日志记录，指定可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL。

ANALYZE TABLE 返回一个结果集，包含以下表格的列。

Column	Value
`Table`	表名
`Op`	`analyze` 或 `histogram`
`Msg_type`	`status`, `error`, `info`, `note` 或 `warning`
`Msg_text`	信息性消息

ANALYZE TABLE 没有HISTOGRAM子句时，执行键分布分析，并将分布存储到表或表中。任何现有的直方图统计信息保持不变。

如果表自上次键分布分析以来未发生变化，则不会再次分析该表。

MySQL 使用存储的键分布来决定在其他于常量以外的连接中，哪些表应该首先连接。此外，键分布也可以用来确定某个查询中使用哪些索引。

检查存储键分布卡方度，可以使用SHOW INDEX语句或INFORMATION_SCHEMASTATISTICS表。见第15.7.7.23节，“SHOW INDEX 语句”，和第28.3.34节，“INFORMATION_SCHEMA STATISTICS 表”。

对于InnoDB表，ANALYZE TABLE通过对每个索引树进行随机探测来确定索引卡方度，并相应地更新索引卡方度估计。由于这些只是估计，多次运行ANALYZE TABLE可能会产生不同的结果。这使得ANALYZE TABLE在InnoDB表上快速，但是不准确，因为它不考虑所有行。

可以通过启用统计信息，使ANALYZE TABLE收集的统计信息更加精准和稳定，如第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节，“InnoDB表ANALYZE TABLE复杂度估算”。

MySQL在连接优化中使用索引卡inality估计。如果连接没有被正确优化，尝试运行ANALYZE TABLE。在少数情况下ANALYZE TABLE不能为您的特定表生成合适的值，您可以使用FORCE INDEX强制使用某个索引，或者设置max_seeks_for_key系统变量以确保MySQL优先选择索引查找而不是表扫描。请参见第B.3.5节，“优化器相关问题”。

ANALYZE TABLE 中的HISTOGRAM子句启用表列值的直方图统计管理。关于直方图统计信息，请参见第10.9.6节，“优化器统计信息”。

这些直方图操作可用：

ANALYZE TABLE 中的UPDATE HISTOGRAM子句生成指定表列的直方图统计信息，并将其存储在数据字典中。只允许一个表名使用该语法。

可选的WITH N BUCKETS子句指定直方图的分桶数。N 的值必须是1到1024之间的整数。如果省略该子句，分桶数默认为100。

可选的AUTO UPDATE子句启用表自动更新直方图。当启用时，使用ANALYZE TABLE语句对该表进行自动更新，使用最后一次指定的桶数，如果之前已经为该表设置了WITH ... BUCKETS。此外，在重新计算表的持久统计信息时（见第17.8.10.1节，“配置持久优化器统计参数”），InnoDB后台统计线程也会更新直方图。MANUAL UPDATE禁用自动更新，默认情况下如果不指定。
ANALYZE TABLE语句中带有DROP HISTOGRAM子句，删除数据字典中的指定表列的直方图统计信息。只允许一个表名。

存储直方图管理语句仅影响命名列。考虑以下语句：

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列值的信息架构COLUMN_STATISTICS表中的数据。只能修改一个列时更新直方图。

我们可以通过首先在表t的列c1生成直方图来演示USING DATA的使用：

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)

我们可以恢复之前删除的直方图，通过之前从HISTOGRAM列中获取的JSON表示形式，然后再次查询该表，我们可以看到直方图已经恢复到之前的状态：

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语句影响：

DROP TABLE 删除掉直方图中列所在的表。
DROP DATABASE 删除掉数据库中的所有表的直方图，因为该语句删除了整个数据库。
RENAME TABLE 不删除直方图，而是将直方图与新表名关联。
ALTER TABLE 删除或修改列时删除该列的直方图。
ALTER TABLE ... 转换到字符集 会将字符列的直方图删除，因为字符集的变化会影响它们。非字符列的直方图保持不变。

系统变量 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值在信息_schemaCOLUMN_STATISTICS表的HISTOGRAM列中，可以查询来确定创建直方图时采样数据的比例。sampling-rate是一个介于0.0和1.0之间的数字。值为1表示读取了所有数据（无采样）。

以下示例演示了采样。为了使示例中的数据量超过histogram_generation_max_mem_size限制，先将限制设置为低值（2000000字节），然后生成employees表的birth_date列直方图统计信息。

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_skippedINNODB_METRICS计数器来监控InnoDB数据页的采样情况。关于INNODB_METRICS计数器使用信息，见第28.4.21节，“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值在信息SchemaCOLUMN_STATISTICS表的HISTOGRAM列中。

关于直方图生成所需内存分配情况，监控性能Schemamemory/sql/histograms工具。见第29.12.20.10节，“Memory Summary Tables”。

ANALYZE TABLE 将信息_schema中的INNODB_TABLESTATS表的统计信息清除，并将STATS_INITIALIZED列设置为Uninitialized。下次访问该表时，统计信息将重新收集。

PREV HOME UP NEXT