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
DROP HISTOGRAM ON col_name [, col_name] ...
● 不带任何 HISTOGRAM 子句的 ANALYZE TABLE 执行键分布分析并存储指定表的分布。对于 MyISAM 表，用于键分布分析的 ANALYZE TABLE 等效于使用 myisamchk --analyze。● 带有 UPDATE HISTOGRAM 子句的 ANALYZE TABLE 为指定的表列生成直方图统计信息，并将其存储在数据字典中。此语法只允许使用一个表名。● 带有 DROP HISTOGRAM 子句的 ANALYZE TABLE 从数据字典中删除指定表列的直方图统计信息。此语法只允许使用一个表名。此语句需要表的 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 分析一个或多个分区。在分析过程中，InnoDB 和 MyISAM 中的表被只读锁锁定。ANALYZE TABLE 从表定义缓存中删除该表，这需要一个刷新锁。如果有长时间运行的语句或事务仍在使用该表，则后续语句和事务必须等待这些操作完成，然后才能释放刷新锁。因为 ANALYZE TABLE 通常很快完成，所以可能不太明显能察觉到，涉及同一个表的事务或语句的延迟是由于剩余的刷新锁造成的。默认情况下，服务器将 ANALYZE TABLE 语句写入二进制日志，以便它们复制到副本。要禁止日志记录，请指定可选的 NO_WRITE_TO_BINLOG 关键字或使用其别名 LOCAL。ANALYZE TABLE 返回一个结果集，其列如下表所示。| 列 | 值 |
|---|
Table
| 表名称 |
Op
| analyze
或 histogram
|
Msg_type
| status
, error
, info
, note
或 warning
|
Msg_text
| 消息 |
不带 HISTOGRAM 子句的 ANALYZE TABLE 执行键分布分析并存储表的键分布。任何现有的直方图统计信息都不会受到影响。如果自上次键分布分析以来该表没有更改，则不会再次分析该表。MySQL 使用存储的键分布来决定表的连接顺序，而不是某种固定的连接。此外，在决定对查询中的特定表使用哪些索引时，可以使用键分布。要检查存储的键分布，请使用 SHOW INDEX 语句或 INFORMATION_SCHEMA STATISTICS 表。对于 InnoDB 表，ANALYZE TABLE 通过对每个索引树执行随机深入分析并相应地更新索引基数估计来确定索引基数。因为这些只是估计，所以重复运行 ANALYZE TABLE 可能会产生不同的数字。这使得 ANALYZE TABLE 在 InnoDB 表上的速度很快，但由于没有考虑到所有行，所以不是100%准确。可以通过启用 innodb_stats_persistent，使 ANALYZE TABLE 收集的统计数据更加精确和稳定，当启用 innodb_stats_persistent 时，在对索引列数据进行重大更改之后运行 ANALYZE TABLE 是很重要的，因为统计信息不会定期重新计算（例如在服务器重启之后）。如果启用了 innodb_stats_persistent，则可以通过修改 innodb_stats_persistent_sample_pages 系统变量来更改随机深入分析次数。如果禁用了 innodb_stats_persistent，则改为修改 innodb_stats_transient_sample_pages。（random dives on 此处个人理解为：随机深入分析，不知道是否合适）MySQL 在连接优化中使用索引基数估计。如果连接没有以正确的方式优化，请尝试运行 ANALYZE TABLE。在少数 ANALYZE TABLE 不能为特定表生成足够好值的情况下，您可以在查询中使用 FORCE INDEX 来强制使用特定索引，或者设置 max_seeks_for_key 系统变量，以确保 MySQL 更喜欢索引查找而不是表扫描。带有 HISTOGRAM 子句的 ANALYZE TABLE 支持管理表列值的直方图统计信息。● 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 的直方图不受影响。加密表（以避免在统计信息中暴露数据）或 TEMPORARY 表不支持生成直方图。直方图生成适用于除几何体类型（空间数据）和 JSON 之外的所有数据类型的列。直方图管理语句尝试尽力执行请求操作，对于无法进行的操作会报告诊断消息。例如，如果 UPDATE HISTOGRAM 语句指定多个列，但其中一些列不存在或具有不受支持的数据类型，则会为有效列生成直方图，并为无效列生成诊断消息。● 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 值需要足够的权限来设置受限会话系统变量。如果为生成直方图而要读入内存的估计数据量超过了由 histogram_generation_max_mem_size 定义的限制，MySQL 将对数据进行采样，而不是将其全部读入内存。采样均匀分布在整个表中。MySQL 使用 SYSTEM 采样，这是一种页级采样方法。INFORMATION_SCHEMA.COLUMN_STATISTICS 表的 HISTOGRAM 列存储了采样率，可以查询它的值来确定为创建直方图而采样的数据部分。采样率是一个介于 0.0 和 1.0 之间的数字。值为1表示读取了所有数据。下面的示例演示采样。在本例中为了确保数据量超过 histogram_generation_max_mem_size 限制，来达到演示目的，在为 employees 表的 birth_date 列生成直方图统计信息之前，将该限制设置为较低值（2000000字节）。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 |
+---------------------------------+
采样率值为 0.0491431208869665 意味着 birth_date 列中约 4.9% 的数据被读入内存以生成直方图统计。从 MySQL 8.0.19 开始，InnoDB 存储引擎为 InnoDB 表中存储的数据提供了自己的采样实现。当存储引擎不提供自己的采样实现时，MySQL 使用的默认采样实现需要进行全表扫描，这对于大型表来说代价高昂。InnoDB 采样实现通过避免全表扫描来提高采样性能。sampled_pages_read 和 sampled_pages_skipped INNODB_METRICS 计数器可用于监视 INNODB 数据页的采样。下面的示例演示采样计数器的用法，这要求在生成直方图统计信息之前启用计数器。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 列的采样率大致相同。ANALYZE TABLE 从 INFORMATION_SCHEMA.INNODB_TABLESTATS 表清除统计信息，并将 STATS_INITIALIZED 列设置为 Uninitialized。下次访问表时，将再次收集统计信息。https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
