[MYSQL] 参数/变量浅析(2) -- 统计信息(statistics)

导读

统计信息是啥? 对表(索引)的信息的统计. 有对表(索引)级统计的, 比如有多少行数据,平均行长度; 也有针对某些字段的更具体的统计(直方图), 比如a字段在某个范围有多少条,占比多少.

统计信息有啥用? 优化器需要知道表的基础信息从而生成相关的执行计划.

什么时候收集统计信息?

自动收集:

1. 表数据量变化超过10%. Mysql存在一个计数来统计表的数据量变化(重启后重新计数), 若变化量达到10%,则触发自动收集统计信息
2. 当表新增/删除字段的时候. mysql是索引组织表, 主键含有所有字段, 当新增字段之后,会更新表的统计信息
3. 当mysql增加索引的时候

手动收集:

1. 使用analyze table tblname命令收集指定表的统计信息.
2. 直接使用update/insert修改统计信息相关的表(mysqldump等逻辑备份恢复时也会更新这张表)

怎么收集统计信息 使用analyze table tblname即可收集表的统计信息. 完整语法如下:

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] 
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [USING DATA 'json_data']

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ..

收集的统计信息放哪的?

统计信息相关参数

我们再来看统计信息相关的参数和表吧:

innodb_stats_persistent

innodb_stats_persistent表示是否持久化统计信息到磁盘(表), 默认:ON

若为OFF: 表示仅存放在内存中, 重启后就没了, 只有下次访问的时候才会重新收集

若为ON: 则还会存储到系统表中:

持久化统计信息的存储位置

1. mysql.innodb_table_stats 表级别的统计信息

database_name: 该表对应的数据库名

table_name: 表名/分区名/子分区名

last_update: 收集统计信息的时间

n_rows: 有多少行数据

clustered_index_size: 主键索引的总大小,单位:page.

sum_of_other_index_sizes: 其它索引的大小,单位:page

1. mysql.innodb_index_stats 索引级别的统计信息

database_name: 该表对应的数据库名

table_name: 表名/分区名/子分区名

index_name: 索引名

last_update: 统计信息收集时间

stat_name:

若为size,则stat_value表示该索引的总页数;

若为n_leaf_pages,则stat_value表示该索引的叶子节点数量

若为n_diff_pfx, 则stat_value表示该索引的该字段有多少行

sample_size: 采样数量(page), 默认20, 我这里是设置表级别的STATS_SAMPLE_PAGES=65535

stat_description: 描述信息, 方便理解改行内容的

1. mysql.column_statistics 直方图

我们无法直接查询mysql.column_statistics,

但是我们可以查询视图information_schema.COLUMN_STATISTICS

这里涉及到buckets(桶), 也就是'直方图'有多少'方', 默认100, 范围是1-1024. 说白了就是把这一列数据分为多少份.

表级别设置统计信息属性

除了修改参数, 我们还可以指定表级别的属性来控制是否收集统计信息.

create table db1.t20250816_statistics(
id int
) stats_auto_recalc=1 stats_sample_pages=200 stats_persistent=1;

stats_auto_recalc 是否自动收集,

stats_sample_pages 每次收集时采样多少

stats_persistent 是否持久化

这里有个没卵用的细节, 在sdi信息中, stats_auto_recalc 0:不显示, 1:启用 2: 禁用

innodb_stats_auto_recalc

启用持久化统计信息时,是否自动收集统计信息 若启用, 则在数据量变化10%或者create/alter table时自动收集统计信息.

innodb_stats_transient_sample_pages

若未启用持久化统计信息时, 则每次收集(比如analyze table)多少页. 默认是8页.

innodb_stats_persistent_sample_pages

若启用持久化统计信息时, 每次收集多少页(叶子节点), 默认是20, 上限是18446744073709551615,

表级别的上限是65535. 不然会报错ERROR 1064 (42000): The valid range for stats_sample_pages is [1, 65535]. Error near '200000 stats_persistent=1' at line 1

innodb_stats_on_metadata

若未开启持久化统计信息,访问元数据信息时,是否更新统计信息: 若启用该参数(默认未启用),执行show table status,或者访问 information_schema.tables/statistics时会自动更新统计信息

innodb_stats_include_delete_marked

收集统计信息时, 被标记为deleted的数据是否还要计算在内. 默认off,即删除的数据不考虑

innodb_stats_method

主要是关于null的统计策略

nulls_equal(默认): null的一共算一个

nulls_unequal: null的每个都单独计算

nulls_ignored: null的一个都不算, 全部跳过

总结

简单的讲解了下统计信息相关的变量, 部分变量存在依赖关系, 大概如下图:

参考:

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

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html