count()聚合函数正确用法
程序员文章站
2022-03-15 22:08:59
count()是聚合函数,对于返回的结果集,一行行地判断,累计值加1,最后返回累计值,count(*)、count(主键ID)和count(1)表示返回满足条件的结果集的总行数。 count()聚合函数统计非NULL与NULL值的区别: 1、count(字段)不统计NULL记录,即表示满足条件的数据 ......
count()聚合计算
count()是聚合函数,对于返回的结果集,一行行地判断,累计值加1,最后返回累计值,count(*)、count(主键id)和count(1)表示返回满足条件的结果集的总行数。
count()聚合函数统计非null与null值的区别:
1、count(字段)不统计null记录,即表示满足条件的数据行里参数字段不为null的行
2、count(1)和count(*)会记录null值
count(主键id)、count(字段)、count(1)、count(*)的区别和性能差异(分析性能差别的原则)
1、server层要什么就给什么
2、innodb只给必要的值
3、现在的优化器对count(*)的取行数做了优化,其他没有做优化
count(主键id)比count(1)慢的原因
对于 count(主键 id) 来说,innodb 引擎会遍历主键索引树,把每一行的id值取出来,返回给server层,server层拿到id后,判断是不可能为空的,按行累加加1,最后返回累计值。
对于count(1),innodb引擎会扫描主键索引树,但不取值,server层对于返回的每一行,按行累计加1,判断不可能为null,返回累计值。
从innodb引擎层返回id会涉及到解析数据行、拷贝字段值的操作,因此count(主键 id)执行要比count(1)执行慢。
count(字段)
1、如果这个字段定义为not null的话,一行行地从记录里面读出这个字段,判断不能为null,按行累计加1
2、如果这个字段定义允许为null,一行行地从记录里面读出这个字段,执行的时候还要判断是否为null,不为null的按行累计加1,返回累加值
count(主键id)走主键索引的时候效率较count(*)差的原因?
平时我们检索一列的时候,基本上等值或范围查询,那么索引基数大的索引必然效率很高(符合走主键索引查找速度最快的原则)。
但是在做count(*)的时候并没有检索具体的一行或者一个范围,那么选择基数小的索引对count操作效率会更高。在做count操作的时候,mysql会遍历每个叶子节点,所以基数越小,效率越高。mysql非聚簇索引叶子节点保存指向主键id的指针,所以需要检索两遍索引。但是这里相对于遍历主键索引,即使检索两遍索引效率也比单纯的检索主键索引快。
innodb是索引组织表,主键索引树的叶子节点是数据,而普通索引树的叶子节点是主键值,索引普通索引树小很多,索引长度越小树的大小就越小。
myisam与innodb,正如在不同的存储引擎中,count(*)函数的执行是不同的
在myisam存储引擎中,count()函数是直接读取数据表保存的行记录数并返回,效率很高,但是如果添加了where条件的话,myisam表也不能返回得很快。
在innodb存储引擎中,count(*)函数是先从内存中读取表中的数据到内存缓冲区,然后扫描全表获得行记录数。在使用count函数中加上where条件时,在两个存储引擎中的效果是一样的,都会扫描全表计算某字段有值项的次数。
count(*)中关于select count(*) from tab_name几种不走索引和走那种索引情景分析
create table `t1` ( `c1` varchar(30) not null, `c2` varchar(20) not null, `c3` varchar(40) not null, `c4` varchar(10) default null) engine=innodb default charset=utf8 comment='ceshi_count'
mysql> explain select count(*) from t1; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | simple | t1 | all | null | null | null | null | 1 | null | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.01 sec)
mysql> alter table t1 add primary key (c1); query ok, 0 rows affected (0.16 sec) records: 0 duplicates: 0 warnings: 0 mysql> explain select count(*) from t1; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | simple | t1 | index | null | primary | 92 | null | 1 | using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
mysql> alter table t1 add index idx_c3(c3); query ok, 0 rows affected (0.04 sec) records: 0 duplicates: 0 warnings: 0 mysql> explain select count(*) from t1; +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ | 1 | simple | t1 | index | null | idx_c3 | 122 | null | 1 | using index | +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 1 row in set (0.00 sec)
4、表有多个二级索引,则使用key_len小的二级索引进行扫描
mysql> alter table t1 add index idx_t1_c4(c4); query ok, 0 rows affected (0.03 sec) records: 0 duplicates: 0 warnings: 0 mysql> explain select count(*) from t1; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | simple | t1 | index | null | idx_t1_c4 | 33 | null | 1 | using index | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.00 sec)
取表行数的几种方式
1、count(*)取行数
2、通过infomation_schema可以快速拿到表的count值,但不是一个准确的值,通过show table status like 'tab_name'查找到的table rows是通过采样方式得到行数,它的误差率达到了40到50%,
3、myisam会存储具体的行数(可能因为myisam事务要加表锁,才这样设计),innodb则需要进行全表扫描
2、通过infomation_schema可以快速拿到表的count值,但不是一个准确的值,通过show table status like 'tab_name'查找到的table rows是通过采样方式得到行数,它的误差率达到了40到50%,
3、myisam会存储具体的行数(可能因为myisam事务要加表锁,才这样设计),innodb则需要进行全表扫描