非持久化统计信息

• 统计信息没有保存在磁盘上，而是频繁的实时计算统计信息；

• 每次对表的访问都会重新计算其统计信息；

持久化统计信息

• 把一张表在某一时刻的统计信息值保存在磁盘上；

• 避免每次查询时重新计算；

• 如果表更新不是很频繁，或者没有达到 MySQL 必须重新计算统计信息的临界值，可直接从磁盘上获取；

• 即使 MySQL 服务重启，也可以快速的获取统计信息值；

• 统计信息的持久化可以针对全局设置也可以针对单表设置。

• innodb_stats_persistent  ：是否开启统计信息持久化，默认开启。

• innodb_stats_auto_recalc ：是否自动重新计算持久化统计信息，默认开启。

• innodb_stats_include_delete_marked ：更新持久化统计信息时，是否会计算已经标记为删除的行。

  默认是关闭的，会获取未提交的脏数据。开启这个选项，MySQL 计算统计信息时只会考虑已经提交的数据。

• innodb_stats_persistent_sample_pages ：用于更新持久化索引分布或者其他统计信息的随机基数页，默认 20 个。

  页数越多，统计信息也就越准确，也就有助于查询优化器选择最优的查询计划。

什么时候考虑更改这个值呢？

1. 当查询计划不是很准确时。比如对比指定表在系统表 mysql.innodb_index_stats 的数据跟 distinct 查询的结果，如果相差太大，可以考虑增加这个值。

2. 当 analyze table 变的非常慢时，可能是这个值设置的太大了，此时要考虑减小这个值。

• innodb_stats_transient_sample_pages：设置非持久化统计信息的采样页数目，默认 8 个。

• innodb_stats_on_metadata：当统计信息配置为非持久化时生效，默认关闭。

  参数 innodb_stats_persistent 为 0 或者建表时属性 STATS_PERSISTENT=0 才起作用。

当开启后，对以下元数据的访问会自动更新统计信息：

• show table status

• show index

• information_schema.tables

• information_schema.statistics

还有一些其他的场景会自动更新非持久化统计信息，比如：

1. 表第一次被访问；

2. InnoDB 检测到有十六分之一的表自从上次统计信息计算后被更新了，这时触发自动更新；

3. MySQL 客户端默认选项 --auto-rehash 打开所有 InnoDB 表，导致所有 InnoDB 表被自动更新统计信息；

• default：也就是默认值，依赖 server 端参数 innodb_stats_auto_recalc  的设置效果

• 0：表示禁用统计信息的自动重新计算，也就是永远不重新计算，需要手动执行 analyze table

• 1：表示当表数据有 10% 的数据变化后，则重新计算持久化统计信息。

STATS_PERSISTENT

• default：依赖 server 端参数 innodb_stats_persistent  的设置

• 0：表示不需要持久化统计信息

• 1：表示开启持久化统计信息

STATS_SAMPLE_PAGES

表统计信息保存在表 mysql.innodb_table_stats 里

• n_rows：表的行数
• clustered_index_size：主键的数据页个数
• sum_of_other_index_sizes：二级索引的数据页个数

mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes  from innodb_table_stats where database_name ='ytt' and table_name = 'ytt_sample_persist';+--------+----------------------+--------------------------+| n_rows | clustered_index_size | sum_of_other_index_sizes |+--------+----------------------+--------------------------+| 356960 |                15162 |                     4113 |+--------+----------------------+--------------------------+1 row in set (0.00 sec)

mysql> select count(*) from ytt_sample_persist;+----------+| count(*) |+----------+|   406644 |+----------+1 row in set (0.90 sec)

mysql> analyze table ytt_sample_persist;+------------------------+---------+----------+----------+| Table                  | Op      | Msg_type | Msg_text |+------------------------+---------+----------+----------+|+------------------------+---------+----------+----------+1 row in set (0.28 sec)

mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes from innodb_table_stats where database_name ='ytt' and table_name ='ytt_sample_persist';+--------+----------------------+--------------------------+| n_rows | clustered_index_size | sum_of_other_index_sizes |+--------+----------------------+--------------------------+| 387202 |                16380 |                     4562 |+--------+----------------------+--------------------------+1 row in set (0.01 sec)

• Index_name：索引名字
• stat_name / stat_value：统计名字和对应的值
• sample_size：采样页个数
• stat_description：统计名字详细信息描述

mysql> select index_name,stat_name,stat_value,sample_size,stat_description from innodb_index_stats where database_name ='ytt' and table_name ='ytt_sample_persist'  and index_name = 'PRIMARY';+------------+--------------+------------+-------------+-----------------------------------+| index_name | stat_name    | stat_value | sample_size | stat_description                  |+------------+--------------+------------+-------------+-----------------------------------+| PRIMARY    | n_diff_pfx01 |      14137 |          20 | i1                                || PRIMARY    | n_diff_pfx02 |      75398 |          20 | i1,i2                             || PRIMARY    | n_diff_pfx03 |     387202 |          20 | i1,i2,i3                          || PRIMARY    | n_leaf_pages |      15708 |        NULL | Number of leaf pages in the index || PRIMARY    | size         |      16380 |        NULL | Number of pages in the index      |+------------+--------------+------------+-------------+-----------------------------------+5 rows in set (0.00 sec)

简单总结下，本篇主要介绍了 MySQL 数据库中的表和索引的统计信息计算，包括持久化统计信息与非持久化统计信息。如果后期有 SQL 走的执行计划不对，或者不是最优的，那就可以断定相关统计信息太旧了，需要及时更新。比如有时候多表 JOIN 的顺序不对，导致查询效率变差，需要人工介入等等。