MySQL的COUNT语句,竟然都能被面试官虐的这么惨!?

收录于话题
#解读阿里开发手册
15个
△Hollis, 一个对Coding有着怪异追求的人△

这是Hollis的第 232篇原创分享

作者 l Hollis
泉源 l Hollis(ID:hollischuang)
数据库查询信赖许多人都不生疏,所有经常有人讥讽程序员就是CRUD专员,这所谓的CRUD指的就是数据库的增删改查。

在数据库的增删改查操作中,使用最频仍的就是查询操作。而在所有查询操作中,统计数目操作更是经常被用到。

关于数据库中行数统计,无论是MySQL照样Oracle,都有一个函数可以使用,那就是COUNT

然则,就是这个常用的COUNT函数,却暗藏着许多玄机,尤其是在面试的时刻,一不小心就会被虐。不信的话请实验回覆下以下问题:

1、COUNT有几种用法?
2、COUNT(字段名)和COUNT(*)的查询效果有什么差异?
3、COUNT(1)和COUNT(*)之间有什么差异?
4、COUNT(1)和COUNT(*)之间的效率哪个更高?
5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*)
6、MySQL的MyISAM引擎对COUNT(*)做了哪些优化?
7、MySQL的InnoDB引擎对COUNT(*)做了哪些优化?
8、上面提到的MySQL对COUNT(*)做的优化,有一个要害的条件是什么?
9、SELECT COUNT(*) 的时刻,加不加where条件有差异吗?
10、COUNT(*)、COUNT(1)和COUNT(字段名)的执行历程是怎样的?

以上10道题,若是您可以所有准确无误的回覆的话,那说明你真的很领会COUNT函数了,若是有哪些知识点是不领会的,那么本文正好可以帮你答疑解惑。

1、熟悉COUNT

关于COUNT函数,在MySQL官网中有详细先容:

简朴翻译一下:

1、COUNT(expr) ,返回SELECT语句检索的行中expr的值不为NULL的数目。效果是一个BIGINT值。

2、若是查询效果没有掷中任何纪录,则返回0

3、然则,值得注意的是,COUNT(*) 的统计效果中,会包罗值为NULL的行数。

即以下表纪录

create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)

使用语句count(*),count(id),count(id2)查询效果如下:

select count(*),count(id),count(id2)
from #bla
results 7 3 2

除了COUNT(id)和COUNT()以外,还可以使用COUNT(常量)(如COUNT(1))来统计行数,那么这三条SQL语句有什么区别呢?到底哪种效率更高呢?为什么《阿里巴巴Java开发手册》中强制要求不让使用 COUNT(列名)或 COUNT(常量)来替换 COUNT()呢?

COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别

前面我们提到过COUNT(expr)用于做行数统计,统计的是expr不为NULL的行数,那么COUNT(列名)、 COUNT(常量) 和 COUNT()这三种语法中,expr分别是列名、 常量 和

那么列名、 常量 和 这三个条件中,常量 是一个固定值,一定不为NULL。可以理解为查询整行,以是一定也不为NULL,那么就只有列名的查询效果有可能是NULL了。

以是, COUNT(常量) 和 COUNT(*)示意的是直接查询相符条件的数据库表的行数。而COUNT(列名)示意的是查询相符条件的列的值不为NULL的行数。

除了查询获得效果集有区别之外,COUNT()相比COUNT(常量) 和 COUNT(列名)来讲,COUNT()是SQL92界说的尺度统计行数的语法,由于他是尺度语法,以是MySQL数据库对他举行过许多优化。

SQL92,是数据库的一个ANSI/ISO尺度。它界说了一种语言(SQL)以及数据库的行为(事务、隔离级别等)。

COUNT(*)的优化

前面提到了COUNT(*)是SQL92界说的尺度统计行数的语法,以是MySQL数据库对他举行过许多优化。那么,详细都做过哪些事情呢?

这里的先容要区分差异的执行引擎。MySQL中对照常用的执行引擎就是InnoDB和MyISAM。

MyISAM和InnoDB有许多区别,其中有一个要害的区别和我们接下来要先容的COUNT(*)有关,那就是MyISAM不支持事务,MyISAM中的锁是表级锁;而InnoDB支持事务,而且支持行级锁。

由于MyISAM的锁是表级锁,以是统一张表上面的操作需要串行举行,以是,MyISAM做了一个简朴的优化,那就是它可以把表的总行数单独纪录下来,若是从一张表中使用COUNT(*)举行查询的时刻,可以直接返回这个纪录下来的数值就可以了,固然,条件是不能有where条件。

MyISAM之以是可以把表中的总行数纪录下来供COUNT(*)查询使用,那是由于MyISAM数据库是表级锁,不会有并发的数据库行数修改,以是查询获得的行数是准确的。

然则,对于InnoDB来说,就不能做这种缓存操作了,由于InnoDB支持事务,其中大部分操作都是行级锁,以是可能表的行数可能会被并发修改,那么缓存纪录下来的总行数就不准确了。

然则,InnoDB照样针对COUNT(*)语句做了些优化的。

,

以太坊高度

www.326681.com采用以太坊区块链高度哈希值作为统计数据,联博以太坊统计数据开源、公平、无任何作弊可能性。联博统计免费提供API接口,支持多语言接入。

,

在InnoDB中,使用COUNT(*)查询行数的时刻,不可避免的要举行扫表了,那么,就可以在扫表历程中下功夫来优化效率了。

从MySQL 8.0.13最先,针对InnoDB的SELECT COUNT(*) FROM tbl_name语句,确着实扫表的历程中做了一些优化。条件是查询语句中不包罗WHERE或GROUP BY等条件。

我们知道,COUNT(*)的目的只是为了统计总行数,以是,他基本不关心自己查到的详细值,以是,他若是能够在扫表的历程中,选择一个成本较低的索引举行的话,那就可以大大节省时间。

我们知道,InnoDB中索引分为聚簇索引(主键索引)和非聚簇索引(非主键索引),聚簇索引的叶子节点中保留的是整行纪录,而非聚簇索引的叶子节点中保留的是该行纪录的主键的值。

以是,相比之下,非聚簇索引要比聚簇索引小许多,以是MySQL会优先选择最小的非聚簇索引来扫表。以是,当我们建表的时刻,除了主键索引以外,建立一个非主键索引照样有需要的。

至此,我们先容完了MySQL数据库对于COUNT(*)的优化,这些优化的条件都是查询语句中不包罗WHERE以及GROUP BY条件。

COUNT(*)和COUNT(1)

先容完了COUNT(*),接下来看看COUNT(1),对于,这二者到底有没有区别,网上的说法众说纷纭。

有的说COUNT(*)执行时会转换成COUNT(1),以是COUNT(1)少了转换步骤,以是更快。

另有的说,由于MySQL针对COUNT()做了特殊优化,以是COUNT()更快。

那么,到底哪种说法是对的呢?看下MySQL官方文档是怎么说的:


InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

画重点:same way , no performance difference。以是,对于COUNT(1)和COUNT(*),MySQL的优化是完全一样的,基本不存在谁比谁快!

那既然COUNT(*)和COUNT(1)一样,建议用哪个呢?

建议使用COUNT(*)!由于这个是SQL92界说的尺度统计行数的语法,而且本文只是基于MySQL做了剖析,关于Oracle中的这个问题,也是众说纷纭的呢。

COUNT(字段)

最后,就是我们一直还没提到的COUNT(字段),他的查询就对照简朴粗暴了,就是举行全表扫描,然后判断指定字段的值是不是为NULL,不为NULL则累加。

相比COUNT(),COUNT(字段)多了一个步骤就是判断所查询的字段是否为NULL,以是他的性能要比COUNT()慢。

总结

本文先容了COUNT函数的用法,主要用于统计表行数。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)。

由于COUNT()是SQL92界说的尺度统计行数的语法,以是MySQL对他举行了许多优化,MyISAM中会直接把表的总行数单独纪录下来供COUNT()查询,而InnoDB则会在扫表的时刻选择最小的索引来降低成本。固然,这些优化的条件都是没有举行where和group的条件查询。

在InnoDB中COUNT(*)和COUNT(1)实现上没有区别,而且效率一样,然则COUNT(字段)需要举行字段的非NULL判断,以是效率会低一些。

由于COUNT()是SQL92界说的尺度统计行数的语法,而且效率高,以是请直接使用COUNT()查询表的行数!

参考资料:
https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count
《极客时间——MySQL实战45讲》

迎接人人关注Java之道民众号,也会定期公布原创的Java手艺文章~

  • MORE | 更多精彩文章 -

  • 女友历久午夜不回家,窃听了她手机后得知的效果令我溃逃
  • 字节跳动 CEO 张一鸣炮轰 HR , “按这要求我自己都进不来!”
  • 谷歌的软件工程文化:引发创造力、激励自主性
  • 2019年9月天下程序员人为统计,你处于什么位置?

若是你喜欢本文,
请长按二维码,关注 Hollis.

转发至朋友圈,是对我最大的支持。
好文章,我在看️