count()聚合函数正确用法

╰+攻爆jí腚メ 2023-09-27 12:44 120阅读 0赞

## 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(\*)的区别和性能差异（分析性能差别的原则） ###

*  **count(id)**

InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

*  **count(1)**

InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字1进去，判断是不可能为空的，按行累加。

*  **count(字段)**

如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；如果这个字段定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

*  **count(\*)**

不会把全部字段取出来，而是专门做了优化，不取值。count(\*)肯定不是null，按行累加。

**【总结】按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以建议尽量使用count(*)，count(id)虽然走的索引，但是还是需要一行一行的扫描才能统计出来总数，所以效率没有那么高。**

### 三、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'

1、表中没有任何索引（表也没有主键）

2、表有主键则执行主键索引全扫描

3、表有二级索引，则使用二级索引key\_len最小的索引进行扫描，尽管这个二级索引的key\_len的值大于主键，都使用二级索引

4、表有多个二级索引，则使用key\_len小的二级索引进行扫描

### 取表行数的几种方式 ###

1、count(\*)取行数  
2、通过infomation\_schema可以快速拿到表的count值，但不是一个准确的值，通过show table status like 'tab\_name’查找到的table rows是通过采样方式得到行数，它的误差率达到了40到50%,  
3、MyISAM会存储具体的行数（可能因为myISAM事务要加表锁，才这样设计）,InnoDB则需要进行全表扫描