【MySQL】COUNT

待我称王封你为后i 2023-08-17 16:45 140阅读 0赞

# count(\*)、count(1)、count(主键)、count(字段)的执行效率 #

在没有where条件的情况下  
`MyISAM`引擎返回结果会比`InnoDB`快上很多，主要是因为MyISAM会单独记录了表的总行数，而`InnoDB`没有这么做。  
为什么没有这么做呢？主要`InnoDB`支持了事务的原因，在事务中不同的版本上查询出来的结果是不一样的。例如表中总行数现有10条，事务A启动后未查询，这时启动事务B对表插入一条数据。这时候事务A查询表行数为10条，事务B查询得行数为11条。`InnoDB默认使用了可重复读的隔离级别`。  
mysql中有个`show table status`的查询，这个查询结果中记录了表行数的字段`Rows`。查询执行速度很快，但这个结果不可以用，因为这个结果是mysql采样估算得来的，比较不准确。

对表数据为54万的数据进行查询比较，其中`a`字段未加索引可为空，`d`字段未加索引不可为空，`b`字段加了索引不可为空，`c`字段加了索引可为空。

执行结果耗时：

[SQL] -- 1 select count(*) from cyj_test ; 受影响的行: 0 时间: 0.086ms [SQL] -- 2 select count(1) from cyj_test; 受影响的行: 0 时间: 0.083ms [SQL] -- 3 select count(id) from cyj_test; 受影响的行: 0 时间: 0.101ms [SQL] -- 4 未加索引可为空 select count(a) from cyj_test; 受影响的行: 0 时间: 0.635ms [SQL] -- 5 加了索引不可为空 select count(b) from cyj_test; 受影响的行: 0 时间: 0.101ms [SQL] -- 6 加了索引可为空 select count(c) from cyj_test; 受影响的行: 0 时间: 0.129ms [SQL] -- 7 未加索引不可为空 select count(d) from cyj_test; 受影响的行: 0 时间: 0.426ms

根据执行时间可得执行效率为：`count(*)≈count(1)>count(主键)≈>count(加了索引不可为空字段)>count(加了索引可为空字段)>count(未加了索引不可为空字段)>count(未加了索引可为空字段)`

`EXPLAIN`结果

-- 1 EXPLAIN select count(*) from cyj_test ;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>cyj_test</td> 
   <td></td> 
   <td>index</td> 
   <td></td> 
   <td>idex_b</td> 
   <td>4</td> 
   <td></td> 
   <td>544598</td> 
   <td>100</td> 
   <td>Using index</td> 
  </tr> 
 </tbody> 
</table>

-- 2 EXPLAIN select count(1) from cyj_test;

-- 3 EXPLAIN select count(id) from cyj_test;

-- 4 未加索引可为空 EXPLAIN select count(a) from cyj_test;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>cyj_test</td> 
   <td></td> 
   <td>ALL</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>544598</td> 
   <td>100</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

-- 5 加了索引不可为空 EXPLAIN select count(b) from cyj_test;

-- 6 加了索引可为空 EXPLAIN select count(c) from cyj_test;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>cyj_test</td> 
   <td></td> 
   <td>index</td> 
   <td></td> 
   <td>idex_c</td> 
   <td>123</td> 
   <td></td> 
   <td>544598</td> 
   <td>100</td> 
   <td>Using index</td> 
  </tr> 
 </tbody> 
</table>

-- 7 未加索引不可为空 EXPLAIN select count(d) from cyj_test;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>cyj_test</td> 
   <td></td> 
   <td>ALL</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>544598</td> 
   <td>100</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

`EXPLAIN`结果得知未加索引的会遍历全表扫描得到查询结果，没有走索引，所以4和7查询速度会比其他慢了很多。  
count(\*)、count(1)、count(id)、count(b)都走了`index_b`的索引，count(c)走了`index_c`的索引。这里你可能会有几个问题要问：  
1、count(\*)、count(1)、count(id)为什么不走主键索引而走了`index_b`呢？

> 因为mysql默认使用了InnoDB，索引是B+树的形式。这里主键索引的页子节点存的是数据，而普通索引树存的是主键值，所以主键索引肯定比普通索引树的大很多，优化器会使用找到的那棵最小的树来进行遍历，所以走了`index_b`。

2、那为什么走了`index_b`而不是走了`index_c`呢？

> 从`EXPLAIN`结果得知，`index_b`的`key_len`为4，`index_c`的`key_len`为123，`key_len`表示索引中使用的字节数，所以肯定使用`index_b`的数据量更小。

从`EXPLAIN`我们简单得知了没加索引会比加了索引的查询慢了很多，那么都加了索引的情况下会是怎么样的呢？其实是mysql对count(*)、count(1)、count(id)、count(b)、count(c)的判断各不相同导致的。***注：取值和不取值会影响执行速度，因为取值会对数据行进度解析以得到想要的字段。**

> count(\*)  
> InnoDB遍历整张表，但不取值，count(\*)肯定不为空，按行累加就行了。

> count(1)  
> InnoDB遍历整张表，但不取值，server层对于每一行数据返回1，判断1不可能空，按行累加。

> count(id)  
> InnoDB遍历整张表，把每一行的id取出来返回给server层，server层判断不可能为空，按行累加。

> count(不可为空字段)  
> InnoDB遍历整张表，把每一行的这个字段取出来返回给server层，server层判断不可能为空，按行累加。

> count(可空字段)  
> InnoDB遍历整张表，把每一行的这个字段取出来返回给server层，server层判断是不是为空，不为空的按行累加。

# count(判断 or null) #

假设存在一张子任务表，表主要信息如下：

CREATE TABLE `app_task_child` ( `task_child_id` varchar(40) NOT NULL, `status` int(11) NOT NULL DEFAULT '1' COMMENT '1.待提交；2.审核中；3.已提交；4.已归档；', `task_id` varchar(40) DEFAULT NULL COMMENT '母任务', PRIMARY KEY (`task_child_id`), KEY `FK6m...` (`task_id`), CONSTRAINT `FK6m...` FOREIGN KEY (`task_id`) REFERENCES `app_task` (`task_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在有一个需求：统计出各任务下的子任务数、已归档数、审核中数的数据。

SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(t.STATUS = 4) AS ongoingNum, count(t.STATUS = 2) AS archiveNum FROM app_task_child t GROUP BY t.task_id

![图一][1240]

上面的SQL会查询出图一的数据来，这数据一看就知道不对，已归档数和审核中的数量肯定错了。文章上面大概有说到一个意思：`count计算的是除了NULL值，其他数据都会加1，例如0或false也都是会加数量1`。  
`t.STATUS = ?`判断为false或true，所以count总为加1，导致结果总跟子任务数是一样的。那么就需要想办法当为false时把结果置为NULL。例如有下面两种方法都能得到正确的结果：

-- 方法一 SELECT SQL_NO_CACHE t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(IF(t. STATUS = 4, true, NULL)) AS ongoingNum, count(IF(t. STATUS = 2, true, NULL)) AS archiveNum FROM app_task_child t GROUP BY t.task_id

-- 方法二 SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(t.STATUS = 4 or NULL) AS ongoingNum, count(t.STATUS = 2 or NULL) AS archiveNum FROM app_task_child t GROUP BY t.task_id

方法一的不难理解，这里不进行说明。  
方法二`(判断 or NULL)`可以理解为当判断为0时，会走or后面的表达式，当判断为1时，不走or后面的表达式。判断为1的直接count为1，判断为0时进行NULL的表达式判断，而且`0 or NULL`为NULL。  
**在mysql中的or和and判断不像java那样，更像是JavaScript这种弱类型语言的判断，可以把NULL直接进行判断。**例如下图中的判断结果  
![图二][1240 1]

# count(判断 or null)性能怎么样？ #

对面上的表进行加status索引。

ALTER TABLE `app_task_child` ADD INDEX `index_status` (`status`) USING BTREE ;

执行sql

-- 写法一 EXPLAIN SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(t.STATUS = 2 or null) AS archiveNum FROM app_task_child t GROUP BY t.task_id;

结果为：

<table> 
 <thead> 
  <tr> 
   <th>...</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>...</td> 
   <td>index</td> 
   <td>FK6m...</td> 
   <td>FK6m...</td> 
   <td>123</td> 
   <td></td> 
   <td>39</td> 
   <td>100</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

执行sql

-- 写法二 EXPLAIN SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(*) AS archiveNum FROM app_task_child t where t.status = 2 GROUP BY t.task_id;

结果为：

<table> 
 <thead> 
  <tr> 
   <th>...</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>...</td> 
   <td>ref</td> 
   <td>FK6m...,index_status</td> 
   <td>index_status</td> 
   <td>123</td> 
   <td>const</td> 
   <td>1</td> 
   <td>100</td> 
   <td>Using index condition; Using temporary; Using filesort</td> 
  </tr> 
 </tbody> 
</table>

就只单单从type字段一个为ref一个为index就可得知写法二性能完爆写法一（[可以参考别人的文章][Link 1]）  
。那么为什么上面不用写法二呢？实际开发中统计的往往不只统计一个num，可能会统计八九个。所以如果使用写法二，需要写八九个SQL去执行，而写法一只需要一条SQL搞定。还有就是这时写法二花费在数据库连接上的损耗加起来往往是比写法一性能更差些。

**如果不在status字段上加索引，`EXPLAIN`比较出来的结果也是方法二性能稍微好一点，这点大家可以自己试一下**

转载于:https://www.cnblogs.com/cnJun/p/11404567.html

[1240]: /images/20230809/9521ae8a150b4d0c9037bd7ffdc0fc3d.png
[1240 1]: /images/20230809/a3fbf150bebb4b7e9972e1806ffdd039.png
[Link 1]: https://www.cnblogs.com/tufujie/p/9413852.html

【MySQL】COUNT

发表评论取消回复

还没有评论，来说两句吧...

相关阅读