MySQL统计总数就用count(*),别花里胡哨的《死磕MySQL系列 十》

2021年11月26日 阅读数:0
这篇文章主要向大家介绍MySQL统计总数就用count(*),别花里胡哨的《死磕MySQL系列 十》,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

有一个问题是这样的统计数据总数用count(*)、count(主键ID)、count(字段)、count(1)那个效率高。数据库

先说结论,不用那么花里胡哨遇到统计总数所有使用count(*).数组

可是有不少小伙伴就会问为何呢?本期文章就解决你们的为何。并发

系列文章

五分钟,让你明白MySQL是怎么选择索引《死磕MySQL系列 六》学习

字符串能够这样加索引,你知吗?《死磕MySQL系列 七》优化

没法复现的“慢”SQL《死磕MySQL系列 八》url

什么?还在用delete删除数据《死磕MySQL系列 九》spa

1、不一样存储引擎的作法

你须要知道的是在不一样的存储引擎下,MySQL对于使用count(*)返回结果的流程是不同的。.net

在Myisam中,每张表的总行数都会存储在磁盘上,所以执行count(*)时,是直接从磁盘拿到这个值返回,效率是很是高的。但你也要知道若是加了条件的统计总数返回也不会那么快的。设计

在Innodb引擎中,执行count(*),须要把数据一行一行的读出来,而后再统计总数返回。3d

问题:为何Innodb不跟Myisam同样把表总数存起来呢?

这个问题就须要追溯的咱们以前的MVCC文章,就是由于要实现多版本并发控制,才会致使Innodb不能直接存储表总数。

由于每一个事务获取到的一致性视图都是不同的,因此返回的数据总数也是不一致的。

若是你没法理解,再回到MVCC文章好好看看,意思就跟不一样事务看到的数据不一致一回事。

实战案例

假设这三个用户是并行的,你会看到三个用户看到最终的数据总数都不一致。

每一个用户会根据read view存储的数据来判断那些数据是本身能够看见的,那些是看不见的。

read view

当执行SQL语句查询时会产生一致性视图,也就是read-view,它是由查询的那一时间全部未提交事务ID组成的数组,和已经建立的最大事务ID组成的。

在这个数组中最小的事务ID被称之为min_id,最大事务ID被称之为max_id,查询的数据结果要根据read-view作对比从而获得快照结果。

因而就产生了如下的对比规则,这个规则就是使用当前的记录的trx_id跟read-view进行对比,对比规则以下。

若是落在trx_id<min_id,表示此版本是已经提交的事务生成的,因为事务已经提交因此数据是可见的

若是落在trx_id>max_id,表示此版本是由未来启动的事务生成的,是确定不可见的

若在min_id<=trx_id<=max_id时

  • 若是row的trx_id在数组中,表示此版本是由还没提交的事务生成的,不可见,可是当前本身的事务是可见的

  • 若是row的trx_id不在数组中,代表是提交的事务生成了该版本,可见

2、MySQL对count(*)作了什么优化

先来看两个索引结构,一个是主键索引、另外一个是普通索引。

主键索引

主键索引

普通索引

普通索引

如今你应该知道了,主键索引的叶子节点存储的是整行数据,而普通索引叶子节点存储的是主键值。

得出结论就是普通索引的比主键索引会小不少。

因此,MySQL对于count(*)这样的操做,无论遍历那个索引树获得的结果在逻辑上都同样。

所以,优化器会找到最小的那棵树来遍历,在保证正确的逻辑前提下,尽可能减小扫描数据量,是数据库系统设计的通用法则之一。

问题:为何存储的有数据怎么不用?

这个图的数据怎么获得的,我想你应该知道了,没错,就是执行show table status \G;得来的。

那为何innodb存储引擎不直接使用Rows这个值呢?

还记不记得在第六期文章中,五分钟,让你明白MySQL是怎么选择索引《死磕MySQL系列 六》

先不要返回去看这篇文章,看下上文图中最后查到的数据总条数是多少。

你会发现这两个统计的数据是不一致的,所以这个值确定是不能够用的。

具体缘由

由于Rows这个值跟索引基数Cardinality同样,都是经过采样统计的。

采样规则

首先,会选出N个数据页,而后统计每一个数据页上不一样的值,最后获得一个平均值。再用这个平均值乘索引的数据页总数获得的就是索引基数。

而且这个索引基数也不是一成不变的,会随着数据持续增删改,当变动的数据超过1/M时才会触发,M值是根据MySQL参数innodb_stats_persistent获得的,设置为on是10,off是16。

在MySQL8.0这个默认值为on,也就是说当这张表的数据变动超过总数据的1/10就会从新触发采样统计。

3、不一样count的用法

如下全部的结论都基于MySQL的Innodb存储引擎。

count(主键ID)

innodb引擎会遍历整张表,把每一行的ID值都那出来,而后返回给server层,server层拿到ID后,判断不可能为空,进行累加。

count(1)

一样遍历整张表,但不取值,server层对返回的每一行,放一个数字1进去,判断是不可能为空的,按行累加。

count(字段)

分为两种状况,字段定义为not null和null

  • 为not null时:逐行从记录里面读出这个字段,判断不能为null,累加

  • 为 null时:执行时,判断到有多是null,还要把值取出来再判断一下,不是null才累加。

count(*)

这个哥们就厉害了,不是带了*就把全部值取出来,而是MySQL作了专门的优化,count ( * )确定不是null,按行累加。

结论

按照效率的话,字段 < 主键ID < 1 ~ ,最好都使用count(),别花里胡哨的。

5、总结

本期文章就一句话,统计总数就用count(*),别花里胡哨的

坚持学习、坚持写做、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见。