[MySQL] count(*) vs count(1) vs count(column)

左手的ㄟ右手 2022-07-15 14:39 126阅读 0赞

MySQL版本：5.7.9

数据表结构为：  
category: InnoDB 引擎，无索引

CREATE TABLE `category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(220) NOT NULL, `created_time` datetime NOT NULL, `updated_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10277277 DEFAULT CHARSET=utf8

category2: InnoDB 引擎，name 字段有索引

CREATE TABLE `category2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(220) NOT NULL, `created_time` datetime NOT NULL, `updated_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `Index` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=10277277 DEFAULT CHARSET=utf8

category3: MyISAM 引擎，无索引

CREATE TABLE `category3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(220) NOT NULL, `created_time` datetime NOT NULL, `updated_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=10277277 DEFAULT CHARSET=utf8

category4: MyISAM 引擎，name字段有索引

CREATE TABLE `category4` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(220) NOT NULL, `created_time` datetime NOT NULL, `updated_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `Index` (`name`) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=10277277 DEFAULT CHARSET=utf8

注：表的数据完全一致，数据量：10277276 行。

## 一、count(\*) vs count(1) ##

#### 1、对以下语句进行测试（不带where条件） ####

select count(*) from category;
    select count(1) from category;

各执行5次结果为：

<table> 
 <thead> 
  <tr> 
   <th>执行次数</th> 
   <th align="center">category count(*)</th> 
   <th align="right">category count(1)</th> 
   <th align="right">category2 count(*)</th> 
   <th align="right">category2 count(1)</th> 
   <th>category3 count(*)</th> 
   <th>category3 count(1)</th> 
   <th>category4 count(*)</th> 
   <th>category4 count(1)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">1.26 s</td> 
   <td align="right">1.22 s</td> 
   <td align="right">1.22 s</td> 
   <td align="right">1.23 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">1.24 s</td> 
   <td align="right">1.20 s</td> 
   <td align="right">1.24 s</td> 
   <td align="right">1.24 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">1.22 s</td> 
   <td align="right">1.22 s</td> 
   <td align="right">1.22 s</td> 
   <td align="right">1.23 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">1.22 s</td> 
   <td align="right">1.20 s</td> 
   <td align="right">1.23 s</td> 
   <td align="right">1.23 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">1.23 s</td> 
   <td align="right">1.24 s</td> 
   <td align="right">1.24 s</td> 
   <td align="right">1.22 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
 </tbody> 
</table>

从以上结果可以看出：  
1、不带where条件下，count(\*) 和 count(1) 查询性能大致相同；  
2、 MyISAM 引擎性能最好，这是由于 MySQL 对 MyISAM 引擎的优化：该引擎存储了当前数据总行数，当执行无where条件的count查询时，直接返回该变量（可参看[MySQL官方文档][MySQL]）。

#### 2、对以下语句进行测试（带where条件） ####

select count(*) from category where name = 'cate_1000';
    select count(1) from category where name = 'cate_1000';

各执行5次结果为：

<table> 
 <thead> 
  <tr> 
   <th>执行次数</th> 
   <th align="center">category count(*)</th> 
   <th align="right">category count(1)</th> 
   <th align="right">category2 count(*)</th> 
   <th align="right">category2 count(1)</th> 
   <th>category3 count(*)</th> 
   <th>category3 count(1)</th> 
   <th>category4 count(*)</th> 
   <th>category4 count(1)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">2.27 s</td> 
   <td align="right">2.29 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.72 s</td> 
   <td>1.61 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">2.28 s</td> 
   <td align="right">2.30 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.49 s</td> 
   <td>1.67 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">2.34 s</td> 
   <td align="right">2.30 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.48 s</td> 
   <td>1.59 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">2.35 s</td> 
   <td align="right">2.25 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.47 s</td> 
   <td>1.66 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">2.33 s</td> 
   <td align="right">2.23 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.52 s</td> 
   <td>1.69 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
 </tbody> 
</table>

从以上结果可以看出：  
1、带where条件时，count(\*) 和 count(1) 性能大致相同；  
2、带索引条件下，性能远远超过不带索引情况；  
3、不带索引时，MyISAM 执行 count 性能比 InnoDB 性能好；

--------------------

综上：count(\*) 和 count(1) 性能大致相同。

## 二、count(id)(主键) vs count(name)(普通列) ##

#### 1、对以下语句进行测试（不带where条件） ####

select count(id) from category;
    select count(name) from category;

各执行5次结果为：

<table> 
 <thead> 
  <tr> 
   <th>执行次数</th> 
   <th align="center">category count(id)</th> 
   <th align="right">category count(name)</th> 
   <th align="right">category2 count(id)</th> 
   <th align="right">category2 count(name)</th> 
   <th>category3 count(id)</th> 
   <th>category3 count(name)</th> 
   <th>category4 count(id)</th> 
   <th>category4 count(name)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">1.41 s</td> 
   <td align="right">1.47 s</td> 
   <td align="right">1.41 s</td> 
   <td align="right">1.50 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">1.42 s</td> 
   <td align="right">1.48 s</td> 
   <td align="right">1.41 s</td> 
   <td align="right">1.47 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">1.41 s</td> 
   <td align="right">1.48 s</td> 
   <td align="right">1.40 s</td> 
   <td align="right">1.48 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">1.40 s</td> 
   <td align="right">1.48 s</td> 
   <td align="right">1.44 s</td> 
   <td align="right">1.48 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">1.40 s</td> 
   <td align="right">1.45 s</td> 
   <td align="right">1.42 s</td> 
   <td align="right">1.47 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
 </tbody> 
</table>

以上结果可以看出：  
1、count(id) 和 count(name) 性能大致相同;  
2、不带where条件时，执行count(column)性能和有无索引无关；

#### 2、对以下语句进行测试（带where条件） ####

select count(id) from category where name = 'cate_1000'; 
    select count(name) from category where name = 'cate_1000';

各执行5次结果为：

<table> 
 <thead> 
  <tr> 
   <th>执行次数</th> 
   <th align="center">category count(id)</th> 
   <th align="right">category count(name)</th> 
   <th align="right">category2 count(id)</th> 
   <th align="right">category2 count(name)</th> 
   <th>category3 count(id)</th> 
   <th>category3 count(name)</th> 
   <th>category4 count(id)</th> 
   <th>category4 count(name)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">2.50 s</td> 
   <td align="right">2.29 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.50 s</td> 
   <td>1.47 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">2.66 s</td> 
   <td align="right">2.25 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.48 s</td> 
   <td>1.46 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">2.67 s</td> 
   <td align="right">2.28 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.53 s</td> 
   <td>1.46 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">2.50 s</td> 
   <td align="right">2.25 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.49 s</td> 
   <td>1.49 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">2.65 s</td> 
   <td align="right">2.28 s</td> 
   <td align="right">0.00 s</td> 
   <td align="right">0.00 s</td> 
   <td>1.63 s</td> 
   <td>1.48 s</td> 
   <td>0.00 s</td> 
   <td>0.00 s</td> 
  </tr> 
 </tbody> 
</table>

以上结果可以看出：  
1、InnoDB 引擎下，带where参数且无索引时，count(id) （主键）性能比 count（column）（普通列）性能差；  
2、不带索引情况下，MyISAM 执行 count(column) 性能比 InnoDB性能好；

--------------------

综上，  
1、大多数情况下，环境相同时，count(id) 和 count(name) 性能大致相同；  
2、InnoDB引擎下，带where条件且无索引时，count(id)性能比count(name)差；

## 三、总结 ##

1、无where条件时：  
MyISAM( count(\*) = count(1) = count(id) = count(name) ) > InnoDB( count(\*) = count(1) > count(id) = count(name) )

2、带where条件时：  
(1)、查询字段有索引时，MyISAM、InnoDB引擎下：  
count(\*) = count(1) = count(id) = count(name)

(2)、查询字段无索引时：  
MyISAM( count(\*) = count(1) = count(id) = count(name) ) > InnoDB( count(\*) = count(1) = count(name) > count(id) )  
注：有索引性能 大于 无索引。

[MySQL]: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count