目录
测试表(开发过程中通常不这么建表,此表为了测试区别)
CREATE TABLE `my_test` (
`id` int(11) COMMENT '主键ID',
`test1` int(11) COMMENT '测试字段1',
`test2` int(11) COMMENT '测试字段2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';
测试
存储引擎:InnoDB
情况 | count(*) | count(1) | count(id) |
空表 | 0 | 0 | 0 |
一行null | 1 | 1 | 0 |
2 | 2 | 1 |
测试结论
count(*)、 count(1) 都表示返回满足条件的结果集的总行数;
而 count(字段),则表示返回满足条件的数据行里面,参数“字段”不为 NULL 的总个数。
底层分析
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;但是如果有where条件,效率就没这么快了
InnoDB 引擎执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。为什么InnoDB不直接存储表的总数?这是因为InnoDB使用MVCC实现事务保证隔离级别的方式,使同一时刻不同事务执行count(*),返回的行数也是不一样的。所以需要去判断每行数据是否可见再去计数
基于InnoDB,对于 count(*) 这样的操作,遍历哪个索引树得到的结果逻辑上都是一样的。因此,MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一。
B+树只有叶子结点上有数据,全部遍历其实就是对叶子结点的链表进行遍历。此时如果遍历主键索引树,由于其叶子结点上存放的是完整的行信息,对于一个数据页而言其行密度会比较小,最终导致要扫描的数据页较多,进而IO开销也比较大。如果遍历第二索引树,其叶子结点只存放主键信息,其数据页的行密度比较大,最终扫描的数据页较少,节省了IO开销。
对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值(忽略所有行的意思)。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。
对于count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。
对于 count(字段) 来说,如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;(count(主键id))
如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。
count(主键id)执行要比count(字段)快。因为主键ID必不为null,在count(主键 id)时MySQL优化器会找到最小的索引树遍历,二级索引树叶子节点存储的为主键值。而count(字段)的话,如果字段上没有索引,就只能选主键索引
但是 count(*) 是例外,优化器只优化了 count(*) 的语义为“取行数”,并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是 null,按行累加。
MySQL原则:server 层要什么就给什么;InnoDB 只给必要的值
结论
按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以建议尽量使用 count(*)。