mysql - 统计数据

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值如何处理有关联)