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

version 8.3

8.0 current
5.7

8.0 Japanese

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

15.7.3.1 分析表语句

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]

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。对于 ANALYZE TABLE 操作更新键分布，可能会失败，即使操作更新了表本身（例如，如果它是一个 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 输出

ANALYZE TABLE 返回一个结果集，具有以下表中的列。

Column	Value
`Table`	The table name
`Op`	`analyze` or `histogram`
`Msg_type`	`status`, `error`, `info`, `note`, or `warning`
`Msg_text`	An informational message

键分布分析

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

如果表自上次键分布分析以来没有更改，则表不再被分析。

MySQL 使用存储的键分布来决定连接顺序，以便在非常量连接时连接表。此外，键分布可以在查询中用于决定使用哪些索引。

要检查存储的键分布基数，请使用 SHOW INDEX 语句或 INFORMATION_SCHEMA STATISTICS 表。请参阅第 15.7.7.23 节，“SHOW INDEX 语句”，和第 28.3.34 节，“INFORMATION_SCHEMA STATISTICS 表”。

对于 InnoDB 表，ANALYZE TABLE 通过在每个索引树上执行随机dives 并相应地更新索引基数估算值来确定索引基数。因为这些只是估算值，因此重复运行 ANALYZE TABLE 可能会产生不同的数字。这使得 ANALYZE TABLE 在 InnoDB 表上快速执行，但不是 100% 准确的，因为它不考虑所有行。

您可以通过启用 innodb_stats_persistent 使 ANALYZE TABLE 收集的统计数据更加精确和稳定，如第 17.8.10.1 节，“配置持久优化器统计参数” 中所述。当 innodb_stats_persistent 启用时，重要的是在索引列数据发生重大变化后运行 ANALYZE TABLE，因为统计数据不会定期重新计算（例如，在服务器重新启动后）。

如果 innodb_stats_persistent 启用，您可以通过修改 innodb_stats_persistent_sample_pages 系统变量来更改随机dives 的数量。如果 innodb_stats_persistent 禁用，请修改 innodb_stats_transient_sample_pages 变量。

有关 InnoDB 中键分布分析的更多信息，请参阅第 17.8.10.1 节，“配置持久优化器统计参数”，和第 17.8.10.3 节，“估算 ANALYZE TABLE 复杂度 for InnoDB 表”。

MySQL 使用索引基数估计在连接优化中。如果连接没有正确优化，请尝试运行 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。
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 格式的数据更新直方图表中的一个列，该格式与 Information Schema COLUMN_STATISTICS 表中显示的直方图列值相同。只能使用 JSON 数据更新一个列的直方图。

我们可以通过使用 USING DATA 来说明，首先在表 t 的列 c1 上生成直方图，像这样：

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+

我们可以在 COLUMN_STATISTICS 表中看到生成的直方图：

mysql> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: mydb
 TABLE_NAME: t
COLUMN_NAME: c1
  HISTOGRAM: {"buckets": [[206, 0.0625], [456, 0.125], [608, 0.1875]],
"data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated":
"2022-10-11 16:13:14.563319", "sampling-rate": 1.0, "histogram-type":
"singleton", "number-of-buckets-specified": 100}

现在我们删除直方图，当我们检查 COLUMN_STATISTICS 时，它现在为空：

mysql> ANALYZE TABLE t DROP HISTOGRAM ON c1;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics removed for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+

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

我们可以通过插入之前从 COLUMN_STATISTICS 表的 HISTOGRAM 列获取的 JSON 表示形式来恢复删除的直方图，当我们再次查询该表时，我们可以看到直方图已经恢复到其之前的状态：

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1 
    ->     USING DATA '{"buckets": [[206, 0.0625], [456, 0.125], [608, 0.1875]],
    ->               "data-type": "int", "null-values": 0.0, "collation-id":
    ->               8, "last-updated": "2022-10-11 16:13:14.563319",
    ->               "sampling-rate": 1.0, "histogram-type": "singleton",
    ->               "number-of-buckets-specified": 100}';   
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+

mysql> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: mydb
 TABLE_NAME: t
COLUMN_NAME: c1
  HISTOGRAM: {"buckets": [[206, 0.0625], [456, 0.125], [608, 0.1875]],
"data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated":
"2022-10-11 16:13:14.563319", "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 ... CONVERT TO CHARACTER SET 删除字符列的直方图，因为它们受到字符集更改的影响。非字符列的直方图保持不变。

系统变量 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 采样，这是一种基于页面的采样方法。

可以查询 Information Schema COLUMN_STATISTICS 表中的 sampling-rate 值，以确定用于生成直方图的数据采样率。 sampling-rate 是一个介于 0.0 和 1.0 之间的数字。值为 1 表示所有数据都被读取（无采样）。

以下示例演示了采样。为了确保数据量超过 histogram_generation_max_mem_size 限制，以便于示例，限制被设置为较低的值（2000000 字节），然后生成雇员表的 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_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)

基于采样计数器数据的采样率大致等于 Information Schema COLUMN_STATISTICS 表的 HISTOGRAM 列中的 sampling-rate 值。

有关生成直方图时执行的内存分配，请监控 Performance Schema memory/sql/histograms 仪器。请参阅第 29.12.20.10 节，“内存摘要表”。

其他考虑事项

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

PREV HOME UP NEXT