GreatSQL统计信息维护管理
* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
相关知识点：
INNODB_STATS_PERSIST=ON或用STATS_PERSIST=1定义单个表时，优化器统计信息将持久化到磁盘。默认情况下，innodb_stats_persistent是启用的。
持久统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中。
默认情况下启用的innodb_stats_auto_recalc变量控制当表中超过10%的行发生更改时是否自动计算统计信息。可以通过在创建或更改表时指定STATS_AUTO_RECALC子句来为单个表配置自动统计数据重新计算。
由于在后台进行的自动统计数据重新计算的异步性质，即使启用了innodb_stats_auto_recalc，在运行影响表10%以上的DML操作后，也不会立即重新计算统计数据。在某些情况下，统计数据的重新计算可能会延迟几秒钟。如果立即需要最新的统计数据，运行 ANALYZE TABLE 以启动统计数据的同步（前台）重新计算。
如果禁用innodb_stats_auto_recalc则可以通过在对索引列进行大量更改后执行 ANALYZE TABLE 语句来确保优化器统计信息的准确性。
当INNODB_STATS_PERSIST=OFF或使用STATS_PERSIST=0创建或更改单个表时，优化器统计信息不会持久化到磁盘。相反，统计信息存储在内存中，当服务器关闭时会丢失。统计数据也会通过某些操作和在某些条件下定期更新。
当向现有表中添加索引时，或者当添加或删除列时，无论innodb_stats_auto_recalc的值如何，都会计算索引统计信息并将其添加到innodb_index_stats表中。
影响统计信息的五个参数
innodb_stats_persistent：指定InnoDB索引统计信息是否持久化到磁盘，默认打开。
innodb_stats_persistent_sample_pages：估计索引列的基数和其他统计信息（如由分析表计算的统计信息）时要采样的索引页数。增加该值可以提高索引统计信息的准确性，但为innodb_stats_persistent_sample_pages设置较高的值可能会导致分析表执行时间过长。
innodb_stats_auto_recalc：使InnoDB在表中的数据发生重大变化后自动重新计算持久统计信息。阈值为表中行数的10%，默认打开。
innodb_stats_include_delete_marked：计算持久优化器统计信息时InnoDB是否包括已标记删除的记录，默认关闭。
innodb_stats_transient_sample_pages：估计索引列的基数和其他统计信息（如由分析表计算的统计信息）时要采样的索引页数。默认值为8。增加该值可以提高索引统计信息的准确性，从而改进查询执行计划，但代价是在打开InnoDB表或重新计算统计信息时会增加I/O。该参数仅适用于为表禁用innodb_stats_persistent的情况，如果启用了INNODB_STATS_PERSIST则应用INNODB_STATS_PERSIST_SAMPLE_PAGES代替innodb_stats_sample_pages
总结：
1、非持久化统计信息在以下情况会被自动更新：
执行 ANALYZE TABLE innodb_stats_on_metadata=ON情况下，执 SHOW TABLE STATUS, SHOW INDEX, 查询 INFORMATION_SCHEMA 下的 TABLES, STATISTICS启用--auto-rehash功能情况下，使用mysql client登录 表第一次被打开 距上一次更新统计信息，表1/16的数据被修改
非持久化统计信息的缺点显而易见，数据库重启后如果大量表开始更新统计信息，会对实例造成很大影响，所以目前都会使用持久化统计信息。
2、持久化统计信息在以下情况会被自动更新：
INNODB_STATS_AUTO_RECALC=ON的情况下，表中10%的数据被修改
增加新的索引
3、统计信息不准确的处理
我们查看执行计划，发现未使用正确的索引，如果是innodb_index_stats中统计信息差别较大引起，可通过以下方式处理：
手动更新统计信息，注意执行过程中会加读锁：
ANALYZETABLE TABLE_NAME;
如果更新后统计信息仍不准确，可考虑增加表采样的数据页，两种方式可以修改：
a. 全局变量INNODB_STATS_PERSISTENT_SAMPLE_PAGES默认为20；
b. 单个表可以指定该表的采样：
ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;
经测试，此处STATS_SAMPLE_PAGES的最大值是65535，超出会报错。
c. 手动更新innodb_table_stats和innodb_index_stats表统计信息（修改这两个表不会产生binlog），然后使用FLUSH TABLE tbl_name语句加载更新后的统计信息。
相关知识点：
INNODB_STATS_PERSIST=ON或用STATS_PERSIST=1定义单个表时，优化器统计信息将持久化到磁盘。默认情况下，innodb_stats_persistent是启用的。
持久统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中。
默认情况下启用的innodb_stats_auto_recalc变量控制当表中超过10%的行发生更改时是否自动计算统计信息。可以通过在创建或更改表时指定STATS_AUTO_RECALC子句来为单个表配置自动统计数据重新计算。
由于在后台进行的自动统计数据重新计算的异步性质，即使启用了innodb_stats_auto_recalc，在运行影响表10%以上的DML操作后，也不会立即重新计算统计数据。在某些情况下，统计数据的重新计算可能会延迟几秒钟。如果立即需要最新的统计数据，运行 ANALYZE TABLE 以启动统计数据的同步（前台）重新计算。
如果禁用innodb_stats_auto_recalc则可以通过在对索引列进行大量更改后执行 ANALYZE TABLE 语句来确保优化器统计信息的准确性。
当INNODB_STATS_PERSIST=OFF或使用STATS_PERSIST=0创建或更改单个表时，优化器统计信息不会持久化到磁盘。相反，统计信息存储在内存中，当服务器关闭时会丢失。统计数据也会通过某些操作和在某些条件下定期更新。
当向现有表中添加索引时，或者当添加或删除列时，无论innodb_stats_auto_recalc的值如何，都会计算索引统计信息并将其添加到innodb_index_stats表中。
影响统计信息的五个参数
innodb_stats_persistent：指定InnoDB索引统计信息是否持久化到磁盘，默认打开。innodb_stats_persistent_sample_pages：估计索引列的基数和其他统计信息（如由分析表计算的统计信息）时要采样的索引页数。增加该值可以提高索引统计信息的准确性，但为innodb_stats_persistent_sample_pages设置较高的值可能会导致分析表执行时间过长。innodb_stats_auto_recalc：使InnoDB在表中的数据发生重大变化后自动重新计算持久统计信息。阈值为表中行数的10%，默认打开。innodb_stats_include_delete_marked：计算持久优化器统计信息时InnoDB是否包括已标记删除的记录，默认关闭。innodb_stats_transient_sample_pages：估计索引列的基数和其他统计信息（如由分析表计算的统计信息）时要采样的索引页数。默认值为8。增加该值可以提高索引统计信息的准确性，从而改进查询执行计划，但代价是在打开InnoDB表或重新计算统计信息时会增加I/O。该参数仅适用于为表禁用innodb_stats_persistent的情况，如果启用了INNODB_STATS_PERSIST则应用INNODB_STATS_PERSIST_SAMPLE_PAGES代替innodb_stats_sample_pages
总结：
1、非持久化统计信息在以下情况会被自动更新：
执行 ANALYZE TABLE innodb_stats_on_metadata=ON情况下，执 SHOW TABLE STATUS, SHOW INDEX, 查询 INFORMATION_SCHEMA 下的 TABLES, STATISTICS启用--auto-rehash功能情况下，使用mysql client登录 表第一次被打开 距上一次更新统计信息，表1/16的数据被修改
非持久化统计信息的缺点显而易见，数据库重启后如果大量表开始更新统计信息，会对实例造成很大影响，所以目前都会使用持久化统计信息。
2、持久化统计信息在以下情况会被自动更新：
INNODB_STATS_AUTO_RECALC=ON的情况下，表中10%的数据被修改增加新的索引
3、统计信息不准确的处理
我们查看执行计划，发现未使用正确的索引，如果是innodb_index_stats中统计信息差别较大引起，可通过以下方式处理：
手动更新统计信息，注意执行过程中会加读锁：
ANALYZETABLE TABLE_NAME;
如果更新后统计信息仍不准确，可考虑增加表采样的数据页，两种方式可以修改：
a. 全局变量INNODB_STATS_PERSISTENT_SAMPLE_PAGES默认为20；
b. 单个表可以指定该表的采样：
ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;
经测试，此处STATS_SAMPLE_PAGES的最大值是65535，超出会报错。
c. 手动更新innodb_table_stats和innodb_index_stats表统计信息（修改这两个表不会产生binlog），然后使用FLUSH TABLE tbl_name语句加载更新后的统计信息。
《深入浅出MGR》视频课程
戳此小程序即可直达B站
https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文章推荐：
想看更多技术好文，点个“在看”吧