InnoDB之统计信息

一、InnoDB统计信息简介

InnoDB统计信息分为持久化统计信息和非持久化统计信息。持久化统计信息将统计信息存储在磁盘（mysql 库下），在数据库重启后保证统计信息的持久访问；非持久化统计信息在数据库重启或一些特定操作后会丢失，再次使用该表时会从新计算。

innodb_stats_auto_recalc 参数控制是否自动收集统计信息，在表发生重大改变后。默认为开启。

STATS_PERSISTENT, STATS_AUTO_RECALC ，STATS_SAMPLE_PAGES 子句可以加在CREATE TABLE和ALTER TABLE后，来单独指定innodb表的统计信息配置。

统计信息信息表位于mysql.innodb_table_stats 和mysql.innodb_index_stats ，可以查看last_update 列来确定统计信息的最后更新时间；还可以手动修改mysql.innodb_table_stats和mysql.innodb_index_stats的值来控制Innodb的执行计划。

innodb_stats_persistent 参数控制是否启用持久化统计信息，默认为开启。

二、持久化统计信息

​ innodb_stats_auto_recalc 参数设为1后，当表的DML更改超过10%后，innodb会异步的收集对应表的统计信息到innodb_index_stats 表，一般延迟可能会有几秒，如果需要立即完成统计信息的收集，使用ANALYZE TABLE 。

当一个表添加索引或者增加/删除列后，无论innodb_stats_auto_recalc参数是否启动，innodb都会收集索引的统计信息到innodb_index_stats 。

可以为单个表指定统信信息配置，例如：

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;  

STATS_PERSISTENT=1：开启持久化统计信息
STATS_AUTO_RECALC=1：开启自动收集统计信息
STATS_SAMPLE_PAGES=25;：在为索引列计算基数和其他统计信息时要采样的索引页数。

优化器使用关于键分布的估计统计信息，根据索引的相对选择性为执行计划选择索引。像ANALYZE TABLE这样的操作会导致InnoDB从表上的每个索引中随机抽样页面来估计索引的基数。Innodb_stats_persistent_sample_pages控制采样页面的数量。您可以在运行时调整设置，以管理优化器使用的统计估计的质量。缺省值为20。下列情况需要考虑修改采样值：

统计信息不准确，通过select distinct 索引列和mysql.innodb_index_stats的基数来确定统计信息是否准确，不准确则需要提高采样值。但是如果采样值设置的太高，又会造成ANALYZED TABLE执行效率低。如果无法在准确的统计数据和分析表执行时间之间取得平衡，可以考虑减少表中索引列的数量或限制分区的数量，以降低分析表的复杂性。考虑表的主键列的数量也很重要，因为主键列是附加到每个非唯一索引的

默认情况下，innoDB会收集未提交数据的统计信息，这样可能会导致执行计划并不是最优的执行计划。可以修改innodb_stats_include_delete_marked为ON（默认为off/0）,这样就可以将删除或未提交的记录标记。

示例：

mysql> select @@innodb_stats_include_delete_marked;
+--------------------------------------+
| @@innodb_stats_include_delete_marked |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
会话A:
mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name='t1'\G
*************************** 1. row ***************************
           database_name: test
              table_name: t1
             last_update: 2023-06-03 15:24:37
                  n_rows: 12
    clustered_index_size: 1
sum_of_other_index_sizes: 0
1 row in set (0.00 sec)

mysql> select count(*) from test.t1;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.05 sec)
--此时统计信息记录值为12行，查询的表也为23行

会话B
mysql> insert into test.t1 values(10,'jjj');
Query OK, 1 row affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
--没有commit，并且没有开启自动提交

会话A:
mysql> analyze table test.t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.t1;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.05 sec)

mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name='t1'\G
*************************** 1. row ***************************
           database_name: test
              table_name: t1
             last_update: 2023-06-03 15:26:08
                  n_rows: 13
    clustered_index_size: 1
sum_of_other_index_sizes: 0
1 row in set (0.00 sec)
--再次收集统计信息，统计信息的行值为13了，但是查询的仍为12行。

mysql.innodb_table_stats

database_name：数据库名称

table_name:表名

last_update:统计信息最后收集时间

n_rows:行数

clustered_index_size:集簇索引大小，页数量

sum_of_other_index_sizes:非集簇索引的大小，页数

mysql.innodb_index_stats

database_name: 数据库名称
table_name:表名
index_name:索引名
last_update:最后更新时间
stat_name:统计信息名称
stat_value:统计信息值
sample_size:采样大小
stat_description:统计信息描述

统计信息可以手动修改，来控制执行计划，手动修改统计信息后通过flush table table_name来加载统计信息到表。

三、非持久化统计信息

四、ANALYZED TABLE

ANALYZE TABLE 的复杂度取决于下面情况：

innodb_stats_persistent_sample_pages 参数配置

表中索引列的数量

表的分区数，如果没有分区，分区数为1