MySQL数据库表索引采样统计

指数据库通过采样、统计出来的表、索引的相关信息，例如，表的记录数、索引page个数、字段的Cardinality选择率等等。MySQL在生成执行计划时,需要根据索引的统计信息进行估算,计算出最小代价的执行计划.MySQL支持有限的索引统计信息，MySQL 8.0版本，支持直方图。

一、采样统计信息参数

MySQL的InnoDB存储引擎的统计信息参数有7：

mysql> show variables like 'innodb_stats%';

+------------------------------------------+-------------+

| Variable_name | Value |

+------------------------------------------+-------------+

| innodb_stats_auto_recalc | ON | 当被修改的数据超过10%时就会触发统计信息重新统计计算

| innodb_stats_include_delete_marked | OFF | 重新计算统计信息时是否包含删除标记的记录。OFF不包含

| innodb_stats_method | nulls_equal | 统计方法，关于NULL值的统计

| innodb_stats_on_metadata | OFF | 操作元数据时是否触发更新统计信息，OFF否

| innodb_stats_persistent | ON | 统计信息是否持久化,默认ON

| innodb_stats_persistent_sample_pages | 20 | 持久化抽样page数

| innodb_stats_transient_sample_pages | 8 | 瞬时抽样page数

+------------------------------------------+-------------+

参数innodb_stats_auto_recalc

该参数innodb_stats_auto_recalc控制是否自动重新计算统计信息，当表中数据有大于10%被修改时就会重新计算统计信息（后台异步处理，这个可能存在延时，不会立即触发）。如果关闭了innodb_stats_auto_recalc,需要通过analyze table来保证统计信息的准确性。

即使innodb_stats_auto_recalc=OFF时，当新索引被增加到表中，所有索引的统计信息会被重新计算并且更新到innodb_index_stats表上。

下面验证一下系统变量innodb_stats_auto_recalc=OFF时，表的属性STATS_AUTO_RECALC=0。创建索引时，会触发该表所有索引重新统计计算。

mysql> set global innodb_stats_auto_recalc=off;

mysql> select * from mysql.innodb_index_stats

mysql> ALTER TABLE test STATS_AUTO_RECALC=0

-> where database_name='MyDB' and table_name = 'test';

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

| MyDB | test | GEN_CLUST_INDEX | 2019-10-28 14:54:48 | n_diff_pfx01 | 2 | 1 |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

3 rows in set (0.00 sec)

mysql> create index ix_test_name on test(name);

mysql> select * from mysql.innodb_index_stats

-> where database_name='MyDB' and table_name = 'test';

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

| MyDB | test | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | n_diff_pfx01 | 2 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx01 | 999 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx02 | 252 | 1 |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

7 rows in set (0.00 sec)

参数innodb_stats_include_delete_marked 重新计算统计信息时是否会考虑删除标记的记录.OFF不考虑不统计

参数innodb_stats_method

· 当变量设置为nulls_equal时，所有NULL值都被视为相同(即，它们都形成一个 value group)。

· 当变量设置为nulls_unequal时，NULL值不被视为相同。相反，每个NULL value 形成一个单独的 value group，大小为 1。

· 当变量设置为nulls_ignored时，将忽略NULL值。

还有一个系统变量myisam_stats_method控制MyISAM表对Null值的统计方法。

参数innodb_stats_on_metadata

在MySQL 5.6.6之前的版本默认开启（默认值为O），每当查询information_schema元数据库里的表时（例如，information_schema.TABLES、information_schema.TABLE_CONSTRAINTS .... ）或show table status、SHOW INDEX..这类操作时，Innodb还会随机提取数据库每个表索引页的部分数据，从而更新information_schema.STATISTICS表，并返回刚才查询的结果。当你的表很大，且数量很多时，耗费的时间就很长，以致很多经常不访问的数据也会进入Innodb_buffer_pool缓冲池中，造成池污染，关闭这个参数，可以加快对于schema库表访问，同时也可以改善查询执行计划的稳定性（对于Innodb表的访问）。所以从MySQL 5.6.6这个版本开始，此参数默认为OFF。

注意：仅当优化器统计信息配置为非持久性时，此选项才生效。

参数innodb_stats_persistent 此参数控制统计信息是否持久化

如果此参数启用，统计信息将会保存到mysql数据库的innodb_table_stats和innodb_index_stats表中。从MySQL 5.6.6开始，MySQL默认使用持久化的统计信息，能保证执行计划的稳定性。设置为OFF。很多操作会触发该操作。在数据表比较多、高并发下可能会造成一定的性能上影响，并且会导致执行计划变动，不稳定。

另外，我们可以使用表的建表参数（STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句）来覆盖系统变量设置的值，建表选项可以在CREATE TABLE或ALTER TABLE语句中指定。表上面指定的参数会覆盖全局变量

例如：

mysql> ALTER TABLE test STATS_PERSISTENT=1;

mysql> ALTER TABLE test STATS_AUTO_RECALC=0;

持久化统计表mysql.innodb_index_stats和mysql.innodb_table_stats中，这两个表的定义如下：

innodb_table_stats：

Column name Description

database_name 数据库名

table_name 表名，分区名或者子分区名

last_update 统计信息最后一次更新时间戳

n_rows 表中数据行数

clustered_index_size 聚集索引page个数

sum_of_other_index_sizes 非聚集索引page个数

innodb_index_stats：

Column name Description

database_name 数据库名

table_name 表名，分区名或者子分区名

index_name 索引名

last_update 最后一次更新时间戳

stat_name 统计信息名

stat_value 统计信息不同值个数

sample_size 采样page个数

stat_description 描述

非持久化（Non-persistent optimizer statistics）存储在内存里，并在服务器关闭时丢失。某些业务和某些条件下也会定期更新统计数据。

其实这里指保存在内层表（MEMROY TABLE）

参数innodb_stats_persistent_sample_pages

如果参数innodb_stats_persistent设置为ON，该参数表示ANALYZE TABLE更新Cardinality值时每次采样页的数量。默认值为20个页面。innodb_stats_persistent_sample_pages太少会导致统计信息不够准确，太多会导致分析执行太慢。

我们可以在创建表的时候对不同的表指定不同的page数量、是否将统计信息持久化到磁盘上、是否自动收集统计信息，如下所示：

CREATE TABLE `test` (
`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_stats_transient_sample_pages

控制采样pages个数，默认为8。Innodb_stats_transient_sample_pages可以runtime设置

innodb_stats_transient_sample_pages在innodb_stats_persistent=0的时候影响采样。

注意点：

1.若值太小，会导致评估不准

2.若果值太大，会导致disk read增加。

3.会生产很不同的执行计划，因为统计信息不同。

还有一个参数information_schema_stats_expiry。这个参数的作用如下：

对于INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的信息，8.0中通过缓存的方式，以提高查询的性能。可以通过设置information_schema_stats_expiry参数设置缓存数据的过期时间，默认是86400秒。查询这两张表的数据的时候，首先是到缓存中进行查询，缓存中没有缓存数据，或者缓存数据过期了，查询会从存储引擎中获取最新的数据。如果需要获取最新的数据，可以通过设置information_schema_stats_expiry参数为0或者ANALYZE TABLE操作。

二、查看统计信息

持久化统计数据存储在mysql.innodb_index_stats和mysql.innodb_table_stats中

非持久化统计数据

MySQL 8.0之前，存储在information_schema.INDEXES和information_schema.TABLES中， MySQL8.0之后存放在INFORMATION_SCHEMA.TABLES、INFORMATION_SCHEMA.STATISTICS、INNODB_INDEXES。非持久化统计信息放在内存中，内存表（MEMORY Table）中。

mysql.innodb_index_stats的数据如何看懂，要搞懂stat_name和stat_value的具体含义：

mysql> select * from mysql.innodb_index_stats

-> where database_name='MyDB' and table_name = 'test';

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

| MyDB | test | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | n_diff_pfx01 | 2 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx01 | 999 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx02 | 252 | 1 |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

7 rows in set (0.00 sec)

stat_name=size时：stat_value表示索引的页的数量。

stat_name=n_leaf_pages时：stat_value表示叶子节点的数量。

stat_name=n_diff_pfxNN时：stat_value表示索引字段上唯一值的数量，此处做一下具体说明：

n_diff_pfxNN： NN代表数字（例如： 01，02等），当stat_name为n_diff_pfxNN时，stat_value列值显示索引的first column(即索引的最前索引列)列的唯一值数量，例如：当NN为01时，stat_value列值就表示索引的第一个列的唯一值数量，当NN为02时，stat_value列值就表示索引的第一和第二个列的组合唯一值数量，以此类推。此外，在stat_name = n_diff_pfxNN的情况下，stat_description列显示一个以逗号分隔的计算索引统计信息列的列表。

三、MySQL的直方图

MySQL 8.0推出了直方图(histogram), 直方图数据存放在information_schema.column_statistics这个系统表下，每行记录对应一个字段的直方图，以json格式保存。同时，新增了一个参数histogram_generation_max_mem_size来配置建立直方图内存大小。

对于RDBMS，直方图是特定列内数据分布的近似值。

mysql> show variables like 'histogram_generation_max_mem_size';

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| histogram_generation_max_mem_size | 20000000 |

+-----------------------------------+----------+

1 row in set (0.01 sec)

mysql> desc information_schema.column_statistics;

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

MySQL的直方图有两种：

等宽直方图每个桶（bucket）保存一个值以及这个值累积频率；

等高直方图每个桶需要保存不同值的个数，上下限以及累计频率等。MySQL会自动分配用哪种类型的直方图。

创建删除直方图

MySQL的直方图比较特殊,不会在创建索引的时候自动生成直方图数据，需要手工执行 ANALYZE TABLE [table] UPDATE HISTOGRAM .... 这样的命令产生表上各列的直方图，默认情况下这些信息会被复制到备库。

语法：

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;

ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

例如：

ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16 BUCKETS;

注意：可指定BUCKETS的值，也可以不指定，它的取值范围为1到1024，如果BUCKETS超过1024，就会报“ERROR 1690 (22003): Number of buckets value is out of range in 'ANALYZE TABLE'”

如果不指定BUCKETS值的话，默认值是100。低于100数据行的则会与数据行相关。

生成直方图数据:

ANALYZE TABLE test UPDATE HISTOGRAM ON name;

删除直方图

ANALYZE TABLE test DROP HISTOGRAM ON create_date

查看直方图数据：

直接将json格式展示出来，看起来非常不直观。常用格式化SQL

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM)

FROM information_schema.column_statistics

WHERE TABLE_NAME='test'\G

SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAME,

HISTOGRAM->>'$."data-type"' AS 'DATA_TYPE',

HISTOGRAM->>'$."sampling-rate"' AS SAMPLING_RATE,

HISTOGRAM->>'$."last-updated"' AS LAST_UPDATED,

HISTOGRAM->>'$."number-of-buckets-specified"' AS NUM_BUCKETS_SPECIFIED

JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'BUCKET_COUNT'

FROM INFORMATION_SCHEMA.COLUMN_STATISTICS

WHERE TABLE_NAME = 'test';

SELECT FROM_BASE64(SUBSTRING_INDEX(v, ':', -1)) value, concat(round(c*100,1),'%') cumulfreq,

CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') freq

FROM information_schema.column_statistics, JSON_TABLE(histogram->'$.buckets',

'$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist

WHERE schema_name = 'MyDB' and table_name = 'test' and column_name = 'name';

SELECT v value, concat(round(c*100,1),'%') cumulfreq,

CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') freq

FROM information_schema.column_statistics, JSON_TABLE(histogram->'$.buckets',

'$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist

WHERE schema_name = 'MyDB' and table_name = 'test' and column_name = 'name';

更新统计信息

非持久统计统计信息也会触发自动更新，非持久化统计信息在以下情况会被自动更新，官方文档介绍如下：

1 执行ANALYZE TABLE

2 innodb_stats_on_metadata=ON情况下，执SHOW TABLE STATUS, SHOW INDEX, 查询 INFORMATION_SCHEMA下的TABLES, STATISTICS

3 启用--auto-rehash功能情况下，使用mysql client登录会触发。

4 表第一次被打开

5 距上一次更新统计信息，表1/16的数据被修改

持久统计信息的统计信息更新上面已经有介绍，还有一种方法就是手动更新统计信息，

1、手动更新统计信息，注意执行过程中会加读锁：

ANALYZE TABLE TABLE_NAME;

2、如果更新后统计信息仍不准确，可考虑增加表采样的数据页，两种方式可以修改：

1) 全局变量INNODB_STATS_PERSISTENT_SAMPLE_PAGES，默认为20；

2) 单个表可以指定该表的采样：

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=100; #最大值是65535，超出会报错。

参考资料：

https://www.cnblogs.com/kerrycode/p/11821042.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

https://dev.mysql.com/doc/refman/8.0/en/index-statistics.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html

https://www.percona.com/blog/2019/10/29/column-histograms-on-percona-server-and-mysql-8-0/ 重点

http://chinaunix.net/uid-31396856-id-5787793.html

https://mysqlserverteam.com/histogram-statistics-in-mysql/

https://mp.weixin.qq.com/s/698g5lm9CWqbU0B_p0nLMw?

posted @ 2024-09-02 00:15 cdrcsy 阅读(57) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MySQL sys性能监控

· MySQL空间优化（空间清理）

· 13. InnoDB 统计数据是如何收集的

· 修正MySQL表统计信息以获得更好的执行计划

· MySQL8-中文参考-三十一-

阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！

公告

昵称： cdrcsy
园龄： 10个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

MySQL数据库表索引采样统计

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

推荐排行榜