ANALYZE TABLE语句
ANALYZE [NO_WRITE_TO_BINLOG |LOCAL ]TABLE tbl_name [, tbl_name] ...ANALYZE [NO_WRITE_TO_BINLOG |LOCAL ]TABLE tbl_nameUPDATE HISTOGRAM ON col_name [, col_name] ... [WITH NBUCKETS ]ANALYZE [NO_WRITE_TO_BINLOG |LOCAL ]TABLE tbl_nameDROP HISTOGRAM ON col_name [, col_name] ...
ANALYZE TABLE
生成表统计信息:
ANALYZE TABLE
没有任一个HISTOGRAM
子句的情况下,将执行键分布分析并存储一个或多个命名表的分布。对于MyISAM
表,ANALYZE TABLE
进行键分布分析等效于使用myisamchk --analyze。ANALYZE TABLE
带有该UPDATE HISTOGRAM
子句的列将为命名表列生成直方图统计信息,并将其存储在数据字典中。此语法仅允许使用一个表名。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
用来分析一个或多个分区;有关更多信息,请参见“ ALTER TABLE语句”和“分区的维护”。
在分析过程中,该表已被锁定与读锁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 | 信息性消息 |
密钥分布分析
ANALYZE TABLE
没有任一个HISTOGRAM
子句的情况下,将执行键分布分析并存储一个或多个表的分布。任何现有的直方图统计信息均不受影响。
如果自上次密钥分配分析以来该表未更改,则不会再次分析该表。
MySQL使用存储的键分布来决定表的连接顺序,以连接常量以外的其他对象。此外,在决定对查询中的特定表使用哪些索引时,可以使用键分布。
要检查存储的密钥分发基数,请使用SHOW INDEX
语句或INFORMATION_SCHEMA
STATISTICS
表。请参见“ SHOW INDEX语句”和“ INFORMATION_SCHEMA STATISTICS表”。
对于InnoDB
表,ANALYZE TABLE
通过对每个索引树执行随机潜水并相应地更新索引基数估计来确定索引基数。由于这些只是估计,因此重复运行ANALYZE TABLE
可能会产生不同的数字。这样可以ANALYZE TABLE
快速处理InnoDB
表,但不能100%准确,因为它没有考虑所有行。
通过启用,可以使收集的统计信息ANALYZE TABLE
更加精确和稳定innodb_stats_persistent
,如“配置持久性优化器统计信息参数”中所述。当innodb_stats_persistent
启用时,它是运行重要的ANALYZE TABLE
重大变化索引列的数据后,由于统计信息不会定期重新计算(如服务器重启后)。
如果innodb_stats_persistent
启用,则可以通过修改innodb_stats_persistent_sample_pages
系统变量来更改随机潜水的次数。如果innodb_stats_persistent
已禁用,请修改innodb_stats_transient_sample_pages
。
有关中的键分布分析的更多信息InnoDB
,请参见“配置持久性优化器统计参数”和“估计InnoDB表的分析表复杂性”。
MySQL在联接优化中使用索引基数估计。如果没有以正确的方式优化联接,请尝试运行ANALYZE TABLE
。在少数情况下,ANALYZE TABLE
不能为特定表提供足够好的值,可以FORCE INDEX
与查询一起使用以强制使用特定索引,或者设置max_seeks_for_key
系统变量以确保MySQL优先选择索引查找而不是表扫描。请参见第B.4.5节“与优化器有关的问题”。
直方图统计分析
ANALYZE TABLE
这些HISTOGRAM
子句可以管理表列值的直方图统计信息。有关直方图统计信息,请参见“优化器统计”。
这些直方图操作可用:
ANALYZE TABLE
与UPDATE HISTOGRAM
子句一起生成命名表列的直方图统计信息并将其存储在数据字典中。此语法仅允许使用一个表名。可选子句指定直方图的存储桶数。的值必须是1到1024之间的整数。如果省略此子句,则存储桶数为100。
WITH N BUCKETS
N
ANALYZE TABLE
带有DROP HISTOGRAM
子句的对象将从数据字典中删除命名表列的直方图统计信息。此语法仅允许使用一个表名。
存储的直方图管理语句仅影响命名列。考虑以下语句:
ANALYZE TABLE tUPDATE HISTOGRAM ON c1, c2, c3WITH 10BUCKETS ;ANALYZE TABLE tUPDATE HISTOGRAM ON c1, c3WITH 10BUCKETS ;ANALYZE TABLE tDROP HISTOGRAM ON c2;
第一条语句将更新列的直方图c1
,c2
以及c3
,替换任何现有的直方图那些列。第二条语句更新直方图c1
和c3
,离开c2
直方图不受影响。第三条语句消除了直方图c2
,留下那些c1
和c3
影响。
加密表(以避免暴露统计数据中的数据)或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
值需要足够的特权来设置受限制的会话系统变量。请参见“系统变量特权”。
如果要读取的用于直方图生成的估计数据量超出了定义的限制histogram_generation_max_mem_size
,则MySQL 会对数据进行采样,而不是将其全部读取到内存中。采样均匀地分布在整个表中。MySQL使用SYSTEM
采样,这是一种页面级采样方法。
可以查询表列中的sampling-rate
值,以确定为创建直方图而采样的数据比例。的是在0.0和1.0之间的数字。值为1表示已读取所有数据(无采样)。HISTOGRAM
INFORMATION_SCHEMA.COLUMN_STATISTICS
sampling-rate
以下示例演示了采样。为了确保histogram_generation_max_mem_size
示例中的数据量超过限制,在为表的birth_date
列生成直方图统计信息之前,将限制设置为一个较低的值(2000000字节)employees
。
mysql>SET histogram_generation_max_mem_size = 2000000; mysql>USE employees; mysql>ANALYZE TABLE employeesUPDATE HISTOGRAM ON birth_dateWITH 16BUCKETS \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_STATISTICSWHERE TABLE_NAME = "employees" ANDCOLUMN_NAME = "birth_date"; +--------------------------------- + | HISTOGRAM ->>'$."sampling -rate"' | +--------------------------------- + | 0.0491431208869665 | +--------------------------------- +
甲sampling-rate
的0.0491431208869665手段值从数据的大约4.9%birth_date
柱被读入存储器,用于产生直方图统计。
从MySQL 8.0.19开始,InnoDB
存储引擎为InnoDB
表中存储的数据提供了自己的采样实现。当存储引擎不提供它们时,MySQL使用的默认采样实现需要全表扫描,这对于大型表来说是昂贵的。该InnoDB
取样实施提高了避免全表扫描采样性能。
sampled_pages_read
和sampled_pages_skipped
INNODB_METRICS
计数器可以被用于监测的取样InnoDB
数据页。(有关常规INNODB_METRICS
计数器使用情况的信息,请参见“ INFORMATION_SCHEMA INNODB_METRICS表”。)
以下示例演示了采样计数器的用法,该用法要求在生成直方图统计信息之前启用计数器。
mysql>SET GLOBAL innodb_monitor_enable = 'sampled%'; mysql>USE employees; mysql>ANALYZE TABLE employeesUPDATE HISTOGRAM ON birth_dateWITH 16BUCKETS \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 , COUNTFROM INNODB_METRICSWHERE 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
有关为直方图生成执行的内存分配的信息,请监视性能架构memory/sql/histograms
工具。请参见“内存摘要表”。
其他注意事项
ANALYZE TABLE
从INFORMATION_SCHEMA.INNODB_TABLESTATS
表中清除表统计信息并将STATS_INITIALIZED
列设置为Uninitialized
。下次访问该表时,将再次收集统计信息。