MySQL统计数据

MySQL知识总结

《MySQL是怎样运行的》知识总结

单表访问方法

表的连接原理

优化 基于查询成本的优化

优化需要的统计数据

优化 基于规则的优化

Explain详解

InnoDB缓冲区

事务

redo日志

undo 日志

MVCC原理

MySQL 锁

13 统计数据

表结构

CREATE TABLE single_table (
    id           int not null auto_increment,
    key1         VARCHAR(100),
    key2         int,
    key3         varchar(100),
    key_part1    varchar(100),
    key_part2    varchar(100),
    key_part3    varchar(100),
    common_field varchar(100),
    primary key (id),
    key idx_key1 (key1),
    unique key uk_key2 (key2),
    key idx_key3 (key3),
    key idx_part (key_part1, key_part2, key_part3)
) ENGINE = INNODB
  CHARSET = utf8;

统计数据的存储方式

​ InnoDB提供了两种的统计数据存储方式,分别是永久性地统计数据(存储在磁盘)、非永久性地统计数据(存储在内存),InnoDB默认以表为单位来存储统计数据,可以通过指定stats_persistent属性来指明表统计数据的存储方式。

永久性地统计数据

​ 当选择把表的统计数据存储在磁盘上时,实际上是把这些统计数据存储到2个表上:

show tables from mysql like 'innodb%stats';

+--------------------------------+
| Tables_in_mysql (innodb%stats) |
+--------------------------------+
| innodb_index_stats             |
| innodb_table_stats             |
+--------------------------------+

innodb_table_stats:表中地每一条记录对应一个表的统计数据

innodb_index_stats:表中的每一条记录对应一个索引的统计数据

查看single_table表的统计数据

select *
from mysql.innodb_table_stats where table_name='single_table';
database_nametable_namelast_updaten_rowsclustered_index_sizesum_of_other_index_sizes
mysql_runsingle_table2022-01-13 22:24:5299373363

clustered_index_size:聚簇索引占用页面的数量

sum_of_other_index_sizes:其他索引占用页面的数量

n_rows:表中记录数量的估计值,InnoDB计算记录数量的大致过程是,按照一定的算法从聚簇索引中选取叶子节点页面,统计页面中的记录数量,然后计算平均值乘叶子节点的数量

计算表记录数量估算时,页面采样数量是系统变量innodb_stats_persistent_sample_pages决定的

聚簇索引页面及其他索引页面数量的统计

​ 一个索引有两个段(叶子节点段、非叶子节点段),每个段由一些零星的页面及一个完整的区(64个页)构成。统计一个段的页面数量步骤如下:

  1. 从数据字典找到表的各个索引对应的根页面位置,sys_indexes表存储了各个索引对应的根页面信息
  2. 根页面中的Page Header部分中包含了Segment Header信息(对应叶子结点段、非叶子节点段)
  3. 从两个段中的找到对应的INODE Entry结构
  4. 针对每个段的INODE Entry结构,可以找出各个零星页面的地址、freenot_freefull的链表基节点
  5. 统计零星页面的个数、在链表基节点上读取占用区的数量(在区中可能有些页面并没有被占用,但统计时也把它们算进去了)

查看single_table索引的统计数据

select *
from mysql.innodb_index_stats
where table_name = 'single_table';
database_nametable_nameindex_namelast_updatestat_namestat_valuesample_sizestat_description
mysql_runsingle_tablePRIMARY2022-01-13 22:24:52n_diff_pfx01993720id
mysql_runsingle_tablePRIMARY2022-01-13 22:24:52n_leaf_pages32NULLNumber of leaf pages in the index
mysql_runsingle_tablePRIMARY2022-01-13 22:24:52size33NULLNumber of pages in the index
mysql_runsingle_tableidx_key12022-01-13 22:24:52n_diff_pfx01418313key1
mysql_runsingle_tableidx_key12022-01-13 22:24:52n_diff_pfx021000013key1,id
mysql_runsingle_tableidx_key12022-01-13 22:24:52n_leaf_pages13NULLNumber of leaf pages in the index
mysql_runsingle_tableidx_key12022-01-13 22:24:52size14NULLNumber of pages in the index
mysql_runsingle_tableidx_key32022-01-13 22:24:52n_diff_pfx01420914key3
mysql_runsingle_tableidx_key32022-01-13 22:24:52n_diff_pfx021000014key3,id
mysql_runsingle_tableidx_key32022-01-13 22:24:52n_leaf_pages14NULLNumber of leaf pages in the index
mysql_runsingle_tableidx_key32022-01-13 22:24:52size15NULLNumber of pages in the index
mysql_runsingle_tableidx_part2022-01-13 22:24:52n_diff_pfx01418816key_part1
mysql_runsingle_tableidx_part2022-01-13 22:24:52n_diff_pfx02849216key_part1,key_part2
mysql_runsingle_tableidx_part2022-01-13 22:24:52n_diff_pfx03990516key_part1,key_part2,key_part3
mysql_runsingle_tableidx_part2022-01-13 22:24:52n_diff_pfx041000016key_part1,key_part2,key_part3,id
mysql_runsingle_tableidx_part2022-01-13 22:24:52n_leaf_pages16NULLNumber of leaf pages in the index
mysql_runsingle_tableidx_part2022-01-13 22:24:52size17NULLNumber of pages in the index
mysql_runsingle_tableuk_key22022-01-13 22:24:52n_diff_pfx011000016key2
mysql_runsingle_tableuk_key22022-01-13 22:24:52n_leaf_pages16NULLNumber of leaf pages in the index
mysql_runsingle_tableuk_key22022-01-13 22:24:52size17NULLNumber of pages in the index

stat_name列的值中有

  1. n_leaf_pages:索引`叶子节点实际占用的页面数
  2. size:索引占用的页面数(包括未使用的页面)
  3. n_diff_pfxNN:索引中第一列到该列不重复的个数,对于联合索引,NN为01,则为联合索引中第1个列不重复值,02则为1~2列,在上面表格中04,则为key_part1,key_part2,key_part3,id这4个索引列组合中的不重复的个数。

对于普通索引,它们可能有重复的值,此时只有在索引列基础上加上主键值才可以区分索引值相同的记录。对于主键索引、唯一索引,它们保证索引列的值唯一,所以不需要在索引列的基础上加上主键列就可以进行区分。

定期更新统计数据

​ 随着对表进行增删改操作,相关的统计数据也会发生变化,MySQL提供了2种更新统计数据的方式:

  1. 开启innodb_stats_auto_recalc:默认开启, 发生变动的记录超过表大小的10%时,就会自动重新计算统计数据,这个操作是异步的

  2. 手动调用analyze table:analyze table single_table;,这个操作是同步的,表的索引较多时,这个过程可能比较慢

  • 存储统计数据的表和普通表一样,我们可以对它进行更新操作

非永久性地统计数据

​ 把系统变量innodb_stats_persistent设置为关闭、创建或修改表时设置stats_persistent为0,之后创建的表(对应的表)地统计数据地方式是非永久性的,非永久性的统计数据页面采样数量由

innodb_stats_transient_sample_pages决定(默认值是8)。

Null值与innodb_stas_method的使用

​ 索引列的不重复值的数量对MySQL的优化器十分重要,它只要应用在

  1. 单调扫描区间太多时,将使用统计数据计算扫描区间的记录数量,而不使用index dive
  2. 在执行连接查询时,如果涉及了等值匹配连接条件,而被驱动表对应列上又有索引,那么就可以使用reff访问方法

查询被驱动表,在执行真正的查询前,on子句连接条件上的常数值并没有确定,也就不能使用index dive的方法来计算扫描区间中记录的数量,此时只能依赖于统计数据进行计算

​ 在统计索引列不重复值的记录数时,可能会遇到对应的列包含NULL的情况,对于NULL值,我们可以有不同的方式看待NULL值:

  1. 每一个NULL值都是不同的
  2. NULL都是相同的
  3. NULL是没有意义的,计算不重复值时不考虑NULL

MySQL认为任何与NULL值进行比较的值都为NULL,但它提供了innodb_stats_method来设置对待NULL的方式,这些方式分别对应上面看待NULL值的方式

  • nulls_unequal:索引列NULL值多时,优化器认为重复值比较多,会倾向于不使用索引方法

  • nulls_equal:索引列NULL值多时,优化器认为重复值比较少,会倾向于使用索引方法

  • nulls_ignored

  • 1
    点赞
  • 1
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:创作都市 设计师:CSDN官方博客 返回首页
评论

打赏作者

011eH

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值