拒绝花里胡哨,MySQL统计总数就用count(*)

2023-10-23 11:59

本文主要是介绍拒绝花里胡哨,MySQL统计总数就用count(*),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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

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

但是有很多小伙伴就会问为什么呢?本期文章就解决大家的为什么。

一、不同存储引擎的做法

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

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

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

问题:为什么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不在数组中,表明是提交的事务生成了该版本,可见

二、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就会重新触发采样统计。

三、不同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(),别花里胡哨的。

五、总结

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

这篇关于拒绝花里胡哨,MySQL统计总数就用count(*)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/267688

相关文章

MySQL中EXISTS与IN用法使用与对比分析

《MySQL中EXISTS与IN用法使用与对比分析》在MySQL中,EXISTS和IN都用于子查询中根据另一个查询的结果来过滤主查询的记录,本文将基于工作原理、效率和应用场景进行全面对比... 目录一、基本用法详解1. IN 运算符2. EXISTS 运算符二、EXISTS 与 IN 的选择策略三、性能对比

MySQL常用字符串函数示例和场景介绍

《MySQL常用字符串函数示例和场景介绍》MySQL提供了丰富的字符串函数帮助我们高效地对字符串进行处理、转换和分析,本文我将全面且深入地介绍MySQL常用的字符串函数,并结合具体示例和场景,帮你熟练... 目录一、字符串函数概述1.1 字符串函数的作用1.2 字符串函数分类二、字符串长度与统计函数2.1

SQL Server跟踪自动统计信息更新实战指南

《SQLServer跟踪自动统计信息更新实战指南》本文详解SQLServer自动统计信息更新的跟踪方法,推荐使用扩展事件实时捕获更新操作及详细信息,同时结合系统视图快速检查统计信息状态,重点强调修... 目录SQL Server 如何跟踪自动统计信息更新:深入解析与实战指南 核心跟踪方法1️⃣ 利用系统目录

MySQL 内存使用率常用分析语句

《MySQL内存使用率常用分析语句》用户整理了MySQL内存占用过高的分析方法,涵盖操作系统层确认及数据库层bufferpool、内存模块差值、线程状态、performance_schema性能数据... 目录一、 OS层二、 DB层1. 全局情况2. 内存占js用详情最近连续遇到mysql内存占用过高导致

Mysql中设计数据表的过程解析

《Mysql中设计数据表的过程解析》数据库约束通过NOTNULL、UNIQUE、DEFAULT、主键和外键等规则保障数据完整性,自动校验数据,减少人工错误,提升数据一致性和业务逻辑严谨性,本文介绍My... 目录1.引言2.NOT NULL——制定某列不可以存储NULL值2.UNIQUE——保证某一列的每一

解密SQL查询语句执行的过程

《解密SQL查询语句执行的过程》文章讲解了SQL语句的执行流程,涵盖解析、优化、执行三个核心阶段,并介绍执行计划查看方法EXPLAIN,同时提出性能优化技巧如合理使用索引、避免SELECT*、JOIN... 目录1. SQL语句的基本结构2. SQL语句的执行过程3. SQL语句的执行计划4. 常见的性能优

SQL Server 中的 WITH (NOLOCK) 示例详解

《SQLServer中的WITH(NOLOCK)示例详解》SQLServer中的WITH(NOLOCK)是一种表提示,等同于READUNCOMMITTED隔离级别,允许查询在不获取共享锁的情... 目录SQL Server 中的 WITH (NOLOCK) 详解一、WITH (NOLOCK) 的本质二、工作

MySQL 强制使用特定索引的操作

《MySQL强制使用特定索引的操作》MySQL可通过FORCEINDEX、USEINDEX等语法强制查询使用特定索引,但优化器可能不采纳,需结合EXPLAIN分析执行计划,避免性能下降,注意版本差异... 目录1. 使用FORCE INDEX语法2. 使用USE INDEX语法3. 使用IGNORE IND

SQL Server安装时候没有中文选项的解决方法

《SQLServer安装时候没有中文选项的解决方法》用户安装SQLServer时界面全英文,无中文选项,通过修改安装设置中的国家或地区为中文中国,重启安装程序后界面恢复中文,解决了问题,对SQLSe... 你是不是在安装SQL Server时候发现安装界面和别人不同,并且无论如何都没有中文选项?这个问题也

2025版mysql8.0.41 winx64 手动安装详细教程

《2025版mysql8.0.41winx64手动安装详细教程》本文指导Windows系统下MySQL安装配置,包含解压、设置环境变量、my.ini配置、初始化密码获取、服务安装与手动启动等步骤,... 目录一、下载安装包二、配置环境变量三、安装配置四、启动 mysql 服务,修改密码一、下载安装包安装地