MySQL统计信息更新小结

参考材料：

• mysql官网文档：
  • https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
  • https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html

MySQL 索引选择机制

MySQL优化器的工作之一是选择索引。通过选择索引，找到一个最优的执行方案，以最小的代价去执行语句。而评估代价大小的因素之一，就是扫描行数。因为扫描的行数越少，访问磁盘数据的次数越少，消耗的CPU资源就相应越少。另外，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

针对扫描行数，实际上MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。它是通过统计信息来估算记录数的。这个统计信息就是索引的“区分度”。一个索引上不同的值越多，这个索引的区分度就越好。

MySQL使用“采样统计”的方式来维护统计信息。采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

1. 设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
2. 设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

由于是采样统计，所以不管N是20还是8，这个基数都是很容易不准的。

MySQL 统计信息更新

• 默认情况下innodb_stats_persistent=ON，优化器的统计信息会持久化保存在mysql.innodb_table_stats和mysql.innodb_index_stats表里。STATS_SAMPLE_PAGES specifies the number of index pages to sample when cardinality and other statistics are calculated for an indexed column, by an ANALYZE TABLE operation, for example.
• innodb_stats_persistent_sample_pages控制每次更新统计信息时的采样数据，默认是20，多了会导致analyze命令执行变慢，少了可能导致统计数据不够准确。
• innodb_stats_auto_recalc参数默认也是开启的，当一个表数据更新超过10%的时候，会触发统计信息的更新计算。

mysql> show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent              | ON    |
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
2 rows in set (0.14 sec)

mysql> show variables like '%innodb_stats_auto_recalc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.03 sec)

• 另外也可以在建表时指定这些参数，例如：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;
• 统计信息的更新是在数据库服务端异步进行的，因此并不一定会在每次执行DML后触发，哪怕表数据更新超过10%也不一定是实时就会触发统计信息的更新（有些场景会延迟几秒后再触发）。mysql> select * from mysql.innodb_table_stats where database_name = 'test' limit 5 ; +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | test | name | 2022-08-09 21:30:42 | 4 | 1 | 0 | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ mysql> analyze table name; +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | test.name | analyze | status | OK | +-----------+---------+----------+----------+ 1 row in set (0.05 sec) mysql> select * from mysql.innodb_table_stats where database_name = 'test' limit 5 ; +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | test | name | 2022-11-14 23:58:18 | 4 | 1 | 0 | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ 5 rows in set (0.00 sec)
• 可以通过执行Analyze 命令主动触发统计信息的更新。
• analyze table命令从库也会执行。
• 如果将innodb_stats_auto_recalc设置为OFF，不开启自动更新统计信息，可以考虑通过定时任务的试，在业务低峰期触发Analyze命令来更新。
• 以下几种情况，不管innodb_stats_auto_recalc是ON还是OFF，都会更新innodb_index_stats表的索引统计信息：
  • 表增加索引
  • 表增加或者删除字段
• innodb_table_stats和innodb_index_stats这2个表的统计数据，其实也可以通过手动方式去修改它（直接update表数据），修改后，需要执行一下FLUSH TABLE命令更新对应的表统计信息让它加载生效.