在Innodb可重复读隔离级别下,对count(字段)、count(主键 id)、count(1)、count(*)四种方案,我们应如何进行选择?
1.原因
1.1为什么有四个count
count定义:count()是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加。最后返回累计值。
count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数;而count(字段),则表示返回满足条件的数据行里面,参数“字段”不为 NULL 的总个数。
根据定义能推断出,使用count(*)、count(主键 id) 和 count(1)获得的结果是相同的,count(字段)因为”字段“可能不为NULL,所以结果存在与其它三个不一致的可能。
虽有4个count,其实对应相同函数。
1.2count有时很慢
很多同学有直观感受,当表比较大时,count速度很慢,有时需要几十秒甚至几分钟,但MyISAM能快速返回值。
主要原因为:
-
MyISAM会保存表的具体行数,因此这段代码在MyISAM存储引擎中执行,只需读出保存好的行数即可。
-
Innodb不保存具体行数,count需遍历全表,一行一行获取。之所以这么做,是因为MVCC需判断对当前的count语句,哪些行可见,哪些行不可见,具体细节可看Innodb事务隔离性实现原理,你了解吗?。
2.count流程
count操作涉及MySQL的Server层和存储引擎层。Server负责判断与计数, Innodb存储引擎负责获取数据集。
在Innodb为啥要刷脏页?里讲过,读取数据时,需要把数据从磁盘读到内存,所以count耗时的节点有如下几个:
-
将数据从磁盘读入内存,这会发生了很多次I/O,因此造成了主要的时间消耗
-
将数据从内存读出,放入返回数据集。引擎返回数据集会涉及到解析数据行,以及拷贝字段值
-
server层对数据集进行NULL判断,进行计数操作
由此可分析出影响性能的几个方面:
-
从磁盘读入内存的数据量
-
是否需要将数值从内存中取出
-
server层是否需要进行NULL判断
3.性能区别
根据以上几点,我们看一下count(字段)、count(主键 id)、count(1)、count(*)的区别。
对于 count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。
对于 count(字段) 来说:
-
如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;
-
如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。
但 count(*) 是例外,并不会把全部字段取出来,而是专门做了优化,不取值。count(*)肯定不是 null,按行累加。
总结下各个操作:
操作 | 是否取值 | 是否判断 | 备注 |
---|---|---|---|
count(*) | 否 | 否 | 最快 |
count(1) | 否 | 是 | |
count(主键id) | 是 | 是 | 可能使用最小的索引树 |
count(字段) | 是 | 是 | 字段上无索引时,只能选主键索引 |
按照效率排序的话,count(字段)<=count(主键 id)<count(1)≈count(*) 。
count(字段)<=count(主键 id)是因为count(主键 id)极有可能选择最小的非聚簇索引,而count(字段)如果“字段”没有索引的话,只能选主键索引,这样加载到内存的数据量就大了很多。另外两者有可能选用同一索引树。
当然,count(主键id)选用最小索引树有一个前提,查询语句中不包含where条件和group by条件。
其实count()、count(1)、count(主键id)可做相同优化,都选用最小索引树、不取值、无需判断的方案,不过目前只有count()做了这个优化。
大家count的时候,尽量选择count(*)即可。
4.优化
count()已是最快的方案了,如果还不满足条件,如何优化count()性能呢?
有两个方案:
-
创建一个最小索引,这样count(*)计算时会选择最小索引,减少IO
-
利用事务,自己计数。建立计数表,增加或删除数据时,同时更新计数表。
总结
大家对count的了解相对多一些,不过仍有很多小细节值得思考。
资料
-
innodb中count(*),count(字段),count(主键id),count(1)的区别
-
MySQL学习笔记:count(1)、count(*)、count(字段)的区别
-
MySQL 全表 COUNT(*) 简述
-
在MySQL的InnoDB存储引擎中count(*)函数的优化
-
MySQL InnoDB count()函数
最后
大家如果喜欢我的文章,可以关注我的公众号(程序员麻辣烫)
我的个人博客为:https://shidawuhen.github.io/
往期文章回顾: