QA
统计数据是做什么的?
- 为了解释器在计算代价时, 选择最优的方案. 这个值如果与实际值差距过大, 会导致执行顺序的变更.
统计数据有哪些?
- 对表的统计数据 - mysql.innodb_table_stats
- 对表索引的统计数据 - mysql.innodb_index_stats
统计数据存在哪?
- 有两种方式, 一种存在磁盘, 一种存在内存
- 5.6.6 版本之后, mysql默认存在磁盘中
如何保证统计数据的准确性?
- 定期/手动更新统计数据的值
表统计信息
示例
mysql> select * from mysql.innodb_table_stats\G
*************************** 7. row ***************************
database_name: yang # 库名
table_name: x # 表名
last_update: 2022-12-08 17:09:33 # 该数据最后更新时间
n_rows: 3 # 预估条目数
clustered_index_size: 1 # 聚簇索引占叶子节点的页数
sum_of_other_index_sizes: 1 # 其他索引占叶子节点的页数参数
- innodb_stats_persistent_sample_pages 该参数影响持久性存储时, 采样页面的数量, 默认值为20
- innodb_stats_transient_sample_pages - 该参数影响临时性统计时, 采样的页面数量, 默认值为8
说明
- n_rows 是估值, 计算方式是从聚簇索引中(根据算法)选择几个叶子节点页面, 计算每个页面中平均条目数, 乘以总页数
存储方式
分类
- 磁盘永久性存储
- 内存临时性存储
设置
- 全局变量设置
# 配置文件中定义, 5.6.6 之前默认在内存中，之后默认在磁盘中
# 存储在磁盘中
innodb_stats_persistent = ON
# 存储在内存中
innodb_stats_persistent = OFF- 单表设置
# 通过创建或修改表的时候指定
# 1表示存到磁盘，0表示存到内存
# 如果未指定使用系统变量
create table tabeName (
...
) engine=InnoDB, stats_persistent=(1|0);查看
- 查看全局变量
mysql> show global variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON |
+-------------------------+-------+- 查看单表设置
mysql> show table status\G
*************************** 5. row ***************************
Name: xx
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-12-12 00:14:22
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options: stats_persistent=0 # <--- 这里能看到单独的设置信息, 使用全局设置的则该字段会为空
Comment:更新统计信息
自动更新
设置
- 全局设置
innodb_stats_recalc = ON- 单表设置
# 1表示开启, 0表示不开启
# 默认是开启的
create table tabeName (
...
) engine=InnoDB, stats_auto_recalc=1触发逻辑
- 每个表有个变量, 记录了增删改的记录条目数, 如果记录数超过了表大小的10%则重新计算
- 验证: 表中数据为20条时，插入约3条就会更新
手动更新
- 手动调用更新
analyze table tableName;- 手动写入更新
update innodb_table_stats set n_rows=1 where table_name="xxx"';
flush table tableName;表索引统计信息
- 大体上与表统计信息逻辑相同
示例
mysql> select * from mysql.innodb_index_stats\G
*************************** 29. row ***************************
database_name: yang # 库名
table_name: xx # 表名
index_name: index_xx # 索引名
last_update: 2022-12-08 17:09:19 # 最后更新时间
stat_name: n_leaf_pages # 统计项名称
stat_value: 1 # 统计项的值
sample_size: NULL # 采样页面数量
stat_description: Number of leaf pages in the index # 统计项的说明关于索引的Null值处理
- 首先在二级索引中, 可能出现null值, 那么mysql应该如何处理null值, 会影响索引的统计
- 约定中, mysql 默认认为 null 值都是不相同的, 所以mysql提供了参数来修改这个认定 --> innodb_stats_method
- nulls_equal 认为所有null都是相等的, 默认是这个
- nulls_unequal 认为所有null都是不等的
- nulls_ignored 忽略null
说明
- 统计项名称
- n_leaf_pages 该索引的叶子节点实际占用多少页面
- size 该索引总共占用多少页面
- n_diff_pfxNN 索引列不重复的值有多少(与null值如何处理有关联)