统计信息表 - xiaoboluo768/mysql-system-schema GitHub Wiki

配置统计信息持久化优化

持久化统计功能是通过将内存中的统计数据存储到磁盘总，使其在数据库重启时可以快速重新读入这些统计信息而不用重新执行统计，从而使得查询优化器可以利用这些持久化的统计信息准确地选择执行计划（如果没有这些持久化的统计信息，那么数据库重启之后内存中的统计信息将会丢失，下一次访问到某库某表的时候，统计信息需要重新计算，并且重新计算可能会因为估算值的差异导致查询计划发生变更，从而导致查询性能可能发生变化），如果启用统计信息的持久化功能呢？当innodb_stats_persistent = ON时或者建表时使用了建表选项STATS_PERSISTENT = 1，则表示开启统计信息的持久化功能（注意，后者表示只开启单表的统计信息持久化且无论innodb_stats_persistent 参数是否启用，前者代表开启全局所有表的统计信息持久化。innodb_stats_persistent系统变量默认开启，如果要单独关闭某个表的持久化统计信息功能，可以通过语句ALTER TABLE tbl_name STATS_PERSISTENT = 0 来修改）

持久统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中，前者存放表结构、数据行相关的统计信息，后者存放索引值相关的统计信息

配置统计信息的持久化优化自动计算

innodb_stats_auto_recalc系统变量控制是否启用统计信息的自动计算功能，默认开启，当自动计算功能开启时，表中的数据量变更超过10%时会触发统计信息自动计算功能。如果innodb_stats_auto_recalc变量未启用，您还可以在CREATE TABLE或ALTER TABLE语句中使用STATS_AUTO_RECALC子句为单个表配置统计信息自动重新计算功能。

自动重新计算在后台运行，所以即使启用了innodb_stats_auto_recalc系统变量，当表中的数据DML操作超过10%之后，统计信息也可能不会立即重新计算，某些情况下可能会延迟几秒钟，如果需要统计信息精确，则你可以手动执行ANALYZE TABLE语句来确保优化程序统计信息的准确性。

当某表添加新的索引时，无论系统参数innodb_stats_auto_recalc的值如何，都会触发重新计算索引统计信息并将其添加到innodb_index_stats表中。但要注意，这里说的是会触发重新计算索引统计信息，而不是表mysql.innodb_table_stats表中的表及其数据相关的统计信息，要想在添加索引时数据相关的统计信息同时更新到mysql.innodb_table_stats表中，需要启用系统变量innodb_stats_auto_recalc或者修改表的innodb_stats_auto_recalc建表选项，或者对表执行ANALYZE TABLE语句

配置单个表的统计信息持久化优化

innodb_stats_persistent，innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局的系统变量。如果你有需要忽略全局变量的值而单独指定某个表是否需要配置持久化统计信息，那么可以使用表的建表选项（STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句）来覆盖系统变量设置的值，建表选项可以在CREATE TABLE或ALTER TABLE语句中指定

* STATS_PERSISTENT：指定是否启用InnoDB表的持久统计信息。如果不设置，默认为DEFAULT，表示表的持久统计信息功能设置由innodb_stats_persistent系统变量确定。如果设置为1，则表示启用该表的持久统计信息，如果设置为则表示关闭此表的持久统计信息功能。如果通过CREATE TABLE或ALTER TABLE语句启用持久性统计信息功能，那么将在代表性数据加载到表中后，调用ANALYZE TABLE语句来计算统计信息。

* STATS_AUTO_RECALC：指定是否自动重新计算InnoDB表的持久统计信息。默认值为DEFAULT，表示表的持久统计信息重新计算功能由系统变量innodb_stats_auto_recalc的值确定。当设置为1时表示启用自动重新计算功能。启用之后当表中数据的10％发生变更时会重新计算统计信息。当设置为0，表示关闭表的自动重新计算统计信息功能，要注意的是，如果关闭之后，表的数据发生了较大更改，那么请手动执行ANALYZE TABLE语句来重新计算统计信息。否则有可能造成因为统计信息不精确而导致执行计划不精确。

* STATS_SAMPLE_PAGES：设置估算索引列的基数和其他统计数据时要抽样的索引页数（例如：ANALYZE TABLE计算需要的采样页数）

以下为这三个建表选项的使用示例：

CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
  STATS_PERSISTENT=1,
  STATS_AUTO_RECALC=1,
  STATS_SAMPLE_PAGES=25;

配置InnoDB优化器统计信息的采样页数

MySQL查询优化器使用关于索引的键值统计信息来计算索引选择度，根据选择度来选择执行计划的索引。那么这些统计信息是如何得来的呢？例如：当执行ANALYZE TABLE之类的操作时，InnoDB会从表中的每个索引中抽取随机页面来估计索引的基数。（这种技术被称为随机采样），采样页的数量由系统参数innodb_stats_persistent_sample_pages设置，默认为20，该变量为动态变量。通常情况下不需要修改，增大该变量置可能导致每次采样时间变长(因为需要读取更多的页)，但如果确定默认的采样数量会导致索引统计信息不精确，那么可以尝试逐步增加该系统变量值，直到具有足够精确的统计信息为止。统计信息是否精确可以通过SELECT DISTINCT(index_name)返回的值与mysql.innodb_index_stats持久统计信息表中提供的估计值来进行对比检查。

配置在持久统计信息的计算中包括删除标记的记录

默认情况下，InnoDB在计算统计信息时会读取未提交的数据。对于从表中执行删除行的操作的未提交事务，InnoDB在估算行和索引统计信息时会忽略这些被打上删除标记的记录，所以这可能会导致对该表执行并行查询的其他事务的执行计划并不精确。为了避免这种情况，可以启用系统参数innodb_stats_include_delete_marked来确保InnoDB在计算持久化统计信息时包含被打上删除标记的记录。当启用innodb_stats_include_delete_marked时，执行ANALYZE TABLE语句时会统计被打上删除标记的记录。要注意的是：innodb_stats_include_delete_marked是全局变量，且不能单独设置某个表，innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的

统计信息持久化依赖于mysql数据库下的表innodb_table_stats和innodb_index_stats，这些表在安装，升级和源代码构建过程中会自动设置

innodb_table_stats和innodb_index_stats表都包含last_update列，表示InnoDB上次更新索引统计信息的时间

innodb_table_stats和innodb_index_stats表是普通表，可以手动执行更新。通过手动更新统计信息的功能，可以强制执行特定的查询优化计划或测试备选计划，而无需修改数据库。要注意：如果手动更新统计信息，需要执行语句FLUSH TABLE tbl_name命令以使MySQL重新加载更新过后的统计信息

持久性统计信息被视为本地信息，因为它们与实例自身相关。因此innodb_table_stats和innodb_index_stats表的自动统计信息数据变更不会在主备架构之间复制。但如果是手动执行ANALYZE TABLE语句来触发统计信息重新计算，那么该ANALYZE TABLE语句本身会在主备架构之间复制，以在备库启动统计信息的同步重新计算操作（除非在主库操作时设置了set sql_log_bin=0之类的语句关闭了日志记录）

PS：以下统计信息表示例模拟数据

root@localhost : test 07:57:28> CREATE TABLE test ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB;   
Query OK, 0 rows affected (0.02 sec)

root@localhost : test 08:00:03> insert into test values(1,1,1,1,1,1),(2,2,2,2,2,2),(3,3,3,3,3,3),(4,4,4,4,4,4),(5,5,4,4,5,5),(5,6,6,6,6,6);     
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

root@localhost : test 08:00:40> select * from test;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 | 4 |
| 5 | 5 | 4 | 4 | 5 | 5 |
| 5 | 6 | 6 | 6 | 6 | 6 |
+---+---+------+------+------+------+
6 rows in set (0.00 sec)

上一篇：proxies_priv表 |下一篇：innodb_table_stats表

统计信息表 - xiaoboluo768/mysql-system-schema GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️