目录
2、COUNT(字段名 ) 和 COUNT(*) 的查询结果有什么不同❓
3、COUNT(1) 和 COUNT(*) 之间有什么不同❓
4、COUNT(1) 和 COUNT(*) 之间的效率哪个更高❓
5、为什么《阿里巴巴Java开发手册》建议使用 COUNT(*)❓
6、MySQL 的 MyISAM 引擎对 COUNT(*) 做了哪些优化❓
7、MySQ L的 InnoDB 引擎对 COUNT(*) 做了哪些优化❓
8、上面提到的 MySQL 对 COUNT(*) 做的优化,有一个关键的前提是什么❓
9、SELECT COUNT(*) 的时候,加不加 where 条件有差别吗❓
10、COUNT(*)、COUNT(1) 和 COUNT(字段名) 的执行过程是怎样的❓
写在最前:
对于数据库,相信很多人都不陌生,其实在座的各位都是 ‘CRUD 专员‘(🌶🐔),这所谓的 CRUD 指的就是数据库的增删改查。
小知识:CRUD 是指在做计算处理时的增加(Create)、读取(Read)、更新(Update)和删除(Delete)几个单词的首字母简写。CRUD 主要被用在描述软件系统中数据库或者持久层的基本操作功能。
在数据库增删改查操作中,使用最频繁的就是查询操作。而在查询操作中,统计数量的操作应该是更频繁的。
关于数据库中行数统计,无论是 MySQL 还是 Oracle,都有一个函数可以使用,那就是 COUNT。
但是,就是这个常用的 COUNT 函数,却暗藏着很多玄机哦。是不是不信,不信的话咋们就先把问题摆出来,尝试回答下下面的十问:
- COUNT 有几种用法❓
- COUNT(字段名 ) 和 COUNT(*) 的查询结果有什么不同❓
- COUNT(1) 和 COUNT(*) 之间有什么不同❓
- COUNT(1) 和 COUNT(*) 之间的效率哪个更高❓
- 为什么《阿里巴巴Java开发手册》建议使用 COUNT(*)❓
- MySQL 的 MyISAM 引擎对 COUNT(*) 做了哪些优化❓
- MySQ L的 InnoDB 引擎对 COUNT(*) 做了哪些优化❓
- 上面提到的 MySQL 对 COUNT(*) 做的优化,有一个关键的前提是什么❓
- SELECT COUNT(*) 的时候,加不加 where 条件有差别吗❓
- COUNT(*)、COUNT(1) 和 COUNT(字段名) 的执行过程是怎样的❓
以上 10 道题,如果你可以全部准确无误回答的话,说明你真的很了解 COUNT 函数,那在下输了,不能让你不要不要的了😂(道路千万条,安全第一条,行车要规范)。如果有哪些知识点不是很了解的,那就由我来帮你答疑解惑。
一、认识 COUNT
1、关于 COUNT 函数,MySQL 官网有详细介绍
2、简单翻译一下
- COUNT(expr) ,返回 SELECT 语句检索的行中 expr 的值不为 NULL 的数量,结果是一个BIGINT值。如果么有匹配的行,则返回 0。
- COUNT(*) 有点不同,返回的是行数的计数,会包含值为 NULL 的行数。
- 对于事务性存储引擎(如 InnoDB),存储精确的行计数是有问题的,多个事务可能同时发生,每个事务都可能影响计数。所以 InnoDB 不在表中保留行的内部计数。SELECT COUNT(*)语句只对当前事务可见的行进行计数。
- 在 MySQL5.7.18 之前,InnoDB 通过扫描聚集索引来处理 SELECT COUNT(*) 语句。
- 从 MySQL5.7.18 开始,InnoDB 通过遍历最小的可用的二级索引来处理 SELECT COUNT(*) 语句,除非提示优化器使用不同的索引。如果不存在辅助索引,则扫描聚集索引。
- 如果近似的行计数足够,请使用 SHOW TABLE STATUS。当然 SHOW CREATE TABLE table_name \G; 也是可以的。
- InnoDB 以同样的方式处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作。没有性能差异。
- 对于 MyISAM 表,如果 SELECT 检索整张表,不是具体某一列,也没有 WHERE 子句,COUNT(*) 将优化为非常快速地返回。此优化仅适用于MyISAM表,因为此存储引擎存储了准确的行计数,并且可以非常快速地访问。仅当第一列定义为非空时,COUNT(1) 才受相同优化的约束。
二、答疑解惑
1、COUNT 有几种用法❓
显而易见的的了吧,COUNT(字段名)、COUNT(*)、COUNT(常量)
2、COUNT(字段名 ) 和 COUNT(*) 的查询结果有什么不同❓
COUNT(字段名 ) ,返回 SELECT 语句检索的行中 expr 的值不为 NULL 的数量,结果是一个BIGINT值。如果么有匹配的行,则返回 0。
COUNT(*) ,返回的是行数的计数,会包含值为 NULL 的行数。
3、COUNT(1) 和 COUNT(*) 之间有什么不同❓
InnoDB 以同样的方式处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作。
MyISAM 仅当第一列定义为非空时,COUNT(1) 才受与 COUNT(*) 相同优化的约束。
4、COUNT(1) 和 COUNT(*) 之间的效率哪个更高❓
没有性能差异。
5、为什么《阿里巴巴Java开发手册》建议使用 COUNT(*)❓
【强制】不要使用 COUNT(字段名) 或 COUNT(常量) 来替代 COUNT(*),COUNT(*) 是 SQL92 定义的标准统计计数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
说明一下:COUNT(*) 会统计值为 NULL 的行,而 COUNT(字段名 ) 不会统计此列为 NULL 值的行。常量是一个固定值,肯定不为 NULL,* 可以理解为查询整行,所以肯定也不为 NULL,那么就只有字段名的查询结果可能为 NULL 了。
除了查询得到结果集有区别之外,COUNT(*) 相比 COUNT(常量) 和 COUNT(字段名) 来讲,COUNT(*) 是 SQL92 定义的标准统计计数的语法,因为它是标准语法,所以 MySQL 对它进行了很多优化(后面具体分析)。
小知识:SQL92,是数据库的一个ANSI/ISO标准。它定义了一种语言(SQL)以及数据库的行为(事务、隔离级别等)。
6、MySQL 的 MyISAM 引擎对 COUNT(*) 做了哪些优化❓
对于 MyISAM 表,如果 SELECT 检索整张表,不是具体某一列,也没有 WHERE 子句,COUNT(*) 将优化为非常快速地返回。此优化仅适用于MyISAM表,因为此存储引擎存储了准确的行计数,并且可以非常快速地访问。仅当第一列定义为非空时,COUNT(1) 才受相同优化的约束。
7、MySQ L的 InnoDB 引擎对 COUNT(*) 做了哪些优化❓
在 MySQL5.7.18 之前,InnoDB 通过扫描聚集索引来处理 SELECT COUNT(*) 语句。
从 MySQL5.7.18 开始,InnoDB 通过遍历最小的可用的二级索引来处理 SELECT COUNT(*) 语句,除非提示优化器使用不同的索引。如果不存在辅助索引,则扫描聚集索引。
8、上面提到的 MySQL 对 COUNT(*) 做的优化,有一个关键的前提是什么❓
这些优化的前提都是查询语句中不包含 WHERE 以及 GROUP BY 条件。
9、SELECT COUNT(*) 的时候,加不加 where 条件有差别吗❓
简单说一下 MyISAM 与 InnoDB 加不加 where 的差别。
MyISAM:如果没有 WHERE 限制的话,MySQL直接返回保存有总的行数,而在有 WHERE 限制的情况下,总是需要对 MySQL 进行全表遍历。
InnoDB:如果没有 WHERE 限制的话通过遍历最小的可用的二级索引来处理 SELECT COUNT(*) 语句,而在有 WHERE 限制的情况下,会根据 WHERE 限制中选择遍历最小的可用的二级索引,没有则为全表遍历。
10、COUNT(*)、COUNT(1) 和 COUNT(字段名) 的执行过程是怎样的❓
COUNT(*) 包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为 NULL
COUNT(1) 包括了忽略所有列,用 1 代表代码行,在统计结果的时候,不会忽略列值为 NULL
COUNT(字段名) 只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者 0,而是表示 null)的计数,即某个字段值为 NULL 时,不统计。
三、总结
COUNT 函数主要用于统计表行数,主要用法有 COUNT(*)、COUNT(1) 和 COUNT(字段名)。
COUNT(*) 是 SQL92 定义的标准统计行数的语法。
MyISAM 中会直接把表的总行数单独记录下来供 COUNT(*) 查询,而 InnoDB 则会在扫表的时候选择最小的索引来降低成本。当然,这些优化的前提都是没有进行 where 和 group 的条件查询。(至于为什么选择最小的索引能降低成本请参考什么是 B+树❓)
在 InnoDB 中 COUNT(*) 和 COUNT(1) 实现上没有区别,而且效率一样,但是 COUNT(字段名) 需要进行字段的非 NULL 判断,所以效率会低一些。