90做网站,网站上传文件存储方式,全网视频合集网站建设,wordpress随机大家好。我们知道#xff0c;mysql中存在许多的统计数据#xff0c;比如通过SHOW TABLE STATUS 可以看到关于表的统计数据#xff0c;通过SHOW INDEX可以看到关于索引的统计数据#xff0c;那么这些统计数据是怎么来的呢#xff1f;它们是以什么方式收集的呢#xff1f;今…大家好。我们知道mysql中存在许多的统计数据比如通过SHOW TABLE STATUS 可以看到关于表的统计数据通过SHOW INDEX可以看到关于索引的统计数据那么这些统计数据是怎么来的呢它们是以什么方式收集的呢今天我们来说说InnoDB 存储引擎的统计数据收集策略。
一、统计数据的存储方式
InnoDB 提供了两种存储统计数据的方式
永久性统计数据 这种统计数据存储在磁盘上服务器重启之后这些统计数据还在。
非永久性统计数据 这种统计数据存储在内存中当服务器关闭时这些统计数据就会被清除掉等到服务器重启之后在某些场景下会重新收集这些统计数据。
MySQL 通过系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前innodb_stats_persistent 的值默认是OFF 也就是说 nnoDB 的统计数据默认是存储到内存的之后的版本中innodb_stats_persistent的值默认是ON 也就是统计数据默认被存储到磁盘中。
InnoDB 默认是以表为单位来收集和存储统计数据的所以我们可以把某些表的统计数据存储在磁盘上把另一些表的统计数据存储在内存中。我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式
CREATE TABLE 表名 (...) EngineInnoDB, STATS_PERSISTENT (1|0);
ALTER TABLE 表名 EngineInnoDB, STATS_PERSISTENT (1|0);当STATS_PERSISTENT1时该表的统计数据存储到磁盘上当 STATS_PERSISTENT0时该表的统计数据临时的存储到内存中。如果我们在创建表时未指定STATS_PERSISTENT属性那默认采用系统变量innodb_stats_persistent 的值作为该属性的值。
二、永久性统计数据
永久性统计数据存放到磁盘上时实际上是存储到下面这两个表里
可以看到这两个表都位于mysql 系统数据库下边其中
innodb_table_stats 存储了关于表的统计数据每一条记录对应着一个表的统计数据。
innodb_index_stats 存储了关于索引的统计数据每一条记录对应着一个索引的一个统计项的统计数据。
下面我们看一下这两个表里边都有什么以及表里的数据是如何生成的。
1、innodb_table_stats 上图就是innodb_table_stats表里存放的数据下面我们以single_table 表的统计信息为例介绍一下几个重要统计信息项
n_rows 的值是9446表明single_table表中大约有9446条记录注意这个数据是估计值实际是10000条。
clustered_index_size 的值是97表明single_table表的聚簇索引占用97个页面这个值是也是一个估计值。
sum_of_other_index_sizes 的值是68表明single_table表的其他索引一共占用68个页面这个值是也是一个估计值。
1. n_rows统计项的收集
InnoDB 统计一个表中有多少行记录的方式是这样的按照一定算法选取几个叶子节点页面计算每个页面中主键值记录数量然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。所以n_rows值是一个估计值并不是真正的记录数。
可以看出来这个n_rows 值精确与否取决于统计时采样的页面数量MySQL通过一个名为innodb_stats_persistent_sample_pages的系统变量来控制使用永久性统计数据时计算统计数据时采样的页面数量。该值设置的越大统计出的n_rows值越精确但是统计耗时也就最久该值设置的越小统计出的n_rows值越不精确但是统计耗时特别少。该系统变量的默认值是20。
我们也可以单独设置某个表的采样页面的数量设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计 数据存储方式
CREATE TABLE 表名 (...) EngineInnoDB, STATS_SAMPLE_PAGES 具体的采样页面数量;
ALTER TABLE 表名 EngineInnoDB, STATS_SAMPLE_PAGES 具体的采样页面数量;如果我们在创建表的语句中并没有指定STATS_SAMPLE_PAGES 属性的话将默认使用系统变量 innodb_stats_persistent_sample_pages的值作为该属性的值。
2. clustered_index_size和sum_of_other_index_sizes统计项的收集
这两个统计项的收集过程如下 从数据字典里找到表的各个索引对应的根页面位置。系统表SYS_INDEXES里存储了各个索引对应的根页面信息。 从根页面的Page Header里找到叶子节点段和非叶子节点段对应的 Segment Header。在每个索引的根页面的Page Header部分都有两个字段
PAGE_BTR_SEG_LEAF 表示B树叶子段的Segment Header信息。
PAGE_BTR_SEG_TOP 表示B树非叶子段的Segment Header信息。
下面是Segment Header结构示意图 从叶子节点段和非叶子节点段的Segment Header中找到这两个段对应的INODE Entry结构。 下面是INODE Entry结构示意图 从对应的INODE Entry结构中可以找到该段对应所有零散的页面地址以及FREE 、NOT_FULL 、FULL链表的基节点。下面是链表的基节点结构示意图 直接统计零散的页面有多少个然后从FREE 、NOT_FULL 、FULL三个链表的List Length字段中读出该段占用的区的大小每个区占用64 个页所以就可以统计出整个段占用的页面。
通过上述5个步骤可以统计出索引的某个段占用的页面数量。分别计算聚簇索引的叶子节点段和非叶子节点段占用的页面数它们的和就是clustered_index_size的值按照同样的套路把其余索引占用的页面数都算出来加起来之后就是sum_of_other_index_sizes的值。
注意一个段的数据在非常多时超过32个页面会以区为单位来申请空间以区为单位申请空间中有一些页可能并没有使用但是在统计clustered_index_size和sum_of_other_index_sizes时都把它们算进去了所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小。
2、innodb_index_stats 我们依旧以single_table表为例上图是single_table表在innodb_index_stats中存储的记录信息。下面我们来看一下如何查看这些信息
先查看index_name列这个列说明该记录是哪个索引的统计信息。我们可以看出来PRIMARY索引也就是主键占了3条记录idx_key_part索引占了6条记录。针对index_name列相同的记录 stat_name表示针对该索引的统计项名称 stat_value 展示的是该索引在该统计项上的值stat_description指的是来描述该统计项的含义的。 我们来具体看一下一个索引都有哪些统计项 n_leaf_pages表示该索引的叶子节点占用多少页面。 size表示该索引共占用多少页面。 n_diff_pfx NN表示对应的索引列不重复的值有多少。
这里的NN 可以被替换为01、02、03… 这样的数字。比如对于 idx_key_part 来说 n_diff_pfx01表示的是统计key_part1这单单一个列不重复的值有多少。 n_diff_pfx02表示的是统计key_part1、key_part2这两个列组合起来不重复的值有多少。 n_diff_pfx03 表示的是统计key_part1、key_part2、key_part3 这三个列组合起来不重复的值有多少。 n_diff_pfx04 表示的是统计key_part1、key_part2、key_part3、id 这四个列组合起来不重复的值有多少。
注意对于普通的二级索引并不能保证它的索引列值是唯一的此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。
在计算某些索引列中包含多少不重复值时需要对一些叶子节点页面进行采样sample_size列就表明了采样的页面数量是多少。
对于有多个列的联合索引来说采样的页面数量是innodb_stats_persistent_sample_pages × 索引列的个数。当需要采样的页面数量大于该索引的叶子节点数量的话就直接采用全表扫描来统计索引列的不重复值数量了。所以不同索引对应的sample_size列的值可能是不同的。
3、定期更新统计数据
随着我们不断的对表进行增删改操作表中的数据也一直在变化这时innodb_table_stats和innodb_index_stats表里的统计数据也会跟着变。MySQL提供了两种更新统计数据的方式
开启innodb_stats_auto_recalc 系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据它的默认值是ON开启。每个表都维护了一个变量该变量记录着对该表进行增删改的记录条数如果发生变动的记录数量超过了表大小的10%并且自动重新计算统计数据的功能是打开的那么服务器会重新进行一次统计数据的计算并且更新innodb_table_stats 和 innodb_index_stats 表。不过自动重新计算统计数据 的过程是异步发生的也就是即使表中变动的记录数超过了10%自动重新计算统计数据也不会立即发生 可能会延迟几秒才会进行计算。
InnoDB 默认是以表为单位来收集和存储统计数据的我们可以单独为某个表设置是否自动重新计算统计数的属性设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC属性来指明该表的统计数据存储方式
CREATE TABLE 表名 (...) EngineInnoDB, STATS_AUTO_RECALC (1|0);
ALTER TABLE 表名 EngineInnoDB, STATS_AUTO_RECALC (1|0);当STATS_AUTO_RECALC1时表明我们想让该表自动重新计算统计数据当STATS_PERSISTENT0时表明不想让该表自动重新计算统计数据。如果我们在创建表时未指定STATS_AUTO_RECALC属性那默认采用系统 变量innodb_stats_auto_recalc 的值作为该属性的值。
手动调用ANALYZE TABLE语句来更新统计信息 如果innodb_stats_auto_recalc系统变量的值为OFF我们也可以手动调用 ANALYZE TABLE语句来重新计算统计数据ANALYZE TABLE语句会立即重新计算统计数据也就是这个过程是同步的。
4、 手动更新innodb_table_stats和innodb_index_stats表
其实innodb_table_stats和innodb_index_stats表就相当于一个普通的表一样我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。比如说我们想把single_table 表关于行数的 统计数据更改一下可以这么做
步骤一更新innodb_table_stats 表。
UPDATE innodb_table_stats SET n_rows 1 WHERE table_name single_table步骤二让MySQL 查询优化器重新加载我们更改过的数据。
更新完innodb_table_stats只是单纯的修改了一个表的数据需要运行下边的命令让MySQL查询优化器重新加载我们更改过的数据
FLUSH TABLE single_table;三、非永久性统计数据
当我们把系统变量innodb_stats_persistent的值设置为OFF时创建的表的统计数据默认就都是非永久性的了或者我们直接在创建表或修改表时设置STATS_PERSISTENT属性的值为0那么该表的统计数据就是非永久性的了。
与永久性的统计数据不同非永久性的统计数据采样的页面数量是由innodb_stats_transient_sample_pages控制的这个系统变量的默认值是8。
最近的MySQL版本都不怎么使用这种基于内存的非永久性统计数据这里我们了解即可。
四、innodb_stats_method的使用
我们知道索引列不重复的值的数量对于MySQL查询优化器十分重要因为通过它可以计算出在索引列中平均一个值重复多少行它的应用场景主要有两个
1. 单表查询中单点区间太多
SELECT * FROM tbl_name WHERE key IN (xx1, xx2, ..., xxn);当上述sql中IN里的参数数量过多时采用index dive的方式直接访问 B树索引去统计每个单点区间对应的记录的数量就太耗费性能了所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。
2. 连接查询时如果有涉及两个表的等值匹配连接条件该连接条件对应的被驱动表中的列又拥有索引时则可以使用ref 访问方法来对被驱动表进行查询
SELECT * FROM t1 JOIN t2 ON t1.column t2.key WHERE ...;在真正执行对t2表的查询前t1.comumn 的值是不确定的所以也不能通过index dive的方式直接访问B树索引去统计每个单点区间对应的记录的数量只能依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。
下面我们思考一下在统计索引列不重复的值的数量时如果索引列中出现NULL值怎么办例如下面这列
此时计算这个col 列中不重复的值的数量就有下边的分歧
有的人认为NULL值代表一个未确定的值在统计索引列不重复的值的数量时应该把NULL值当作 一个独立的值所以col列的不重复的值的数量就是4分别是1、2、NULL、NULL这四个值。有的人认为其实NULL值在业务上就是代表没有所有的NULL值代表的意义是一样的所以col列不重复的值的数量就是3分别是1、2、NULL这三个值。有的人认为这NULL 完全没有意义所以在统计索引列不重复的值的数量时压根儿不能把它们算进来所以col 列不重复的值的数量就是2分别是1、2这两个值。
MySQL提供了一个名为innodb_stats_method 的系统变量这个系统变量有三个候选值
nulls_equal 认为所有NULL值都是相等的。这个值也是 innodb_stats_method的默认值。如果某个索引列中NULL 值特别多的话这种统计方式会让优化器认为某个列中平均一个值重复次数特别多所以倾向于不使用索引进行访问。
nulls_unequal 认为所有 NULL 值都是不相等的。如果某个索引列中NULL 值特别多的话这种统计方式会让优化器认为某个列中平均一个值重复次数特别少所以倾向于使用索引进行访问。
nulls_ignored 直接把 NULL 值忽略掉。
好了到这里我们就讲完了今天主要讲了InnoDB的统计数据是如何产生的大家有什么想法欢迎留言讨论。也希望大家能给作者点个关注谢谢大家最后依旧是请各位老板有钱的捧个人场没钱的也捧个人场谢谢各位老板
