MySQL count()理解

 

COUNT()函数有两种使用方式：

• count(1)与count(*)基本没区别， 使用COUNT(*)对表中行的数目进行计数，不管表列值是空值（NULL）还是非空值；
• 使用COUNT(colum)对特定列中具有值的行进行计数，忽略NULL值的行

 

 

执行效率：

• 若列名为主键，count(column）会比count(1)快
• 若列名不为主键，count(1)比count(column）快
• 若表多个列并且没有主键，则 count(1)的执行效率优于 count(*)  
• 若表有主键，则select count(主键)的执行效率最优
• 若表只有一个字段，则select count(*)最优。

 

 

mysql> select chpswtime,logon_time from account;
+------------+------------+
| chpswtime  | logon_time |
+------------+------------+
| 1578031301 | 1578031451 |
| 1578028081 | 1578028085 |
| 1569812147 | 1577690844 |
| 1569812147 | 1577690843 |
| 1526991828 | 1576947679 |
| 1526991828 | 1576741394 |
| 1526993353 | 1576316102 |
| 1523954294 | 1578558931 |
| 1525436500 | 1574663261 |
| 1523954294 | 1578637910 |
| 1523954294 | 1576947678 |
| 1523954880 | 1578639966 |
| 1523954294 | 1578648662 |
|       NULL | 1576741391 |
|       NULL | 1576741391 |
|       NULL | 1576316099 |
|       NULL | 1576316099 |
|       NULL | 1576316099 |
|       NULL | 1578637919 |
...........................................................

|       NULL | 1579081157 |
| 1523954294 | 1579140723 |
| 1523954294 | 1579142857 |
|       NULL | 1579223489 |
|       NULL | 1579170946 |
|       NULL | 1579170946 |
+------------+------------+
74 rows in set (0.00 sec)  # 共74行


mysql>  select chpswtime,count(*) from account;
+------------+----------+
| chpswtime  | count(*) |
+------------+----------+
| 1578031301 |       74 |
+------------+----------+
1 row in set (0.00 sec)  # count(*)统计结果是74行

mysql>  select chpswtime,count(chpswtime) from account; #数目少了
+------------+------------------+
| chpswtime  | count(chpswtime) |
+------------+------------------+
| 1578031301 |               22 |
+------------+------------------+
1 row in set (0.00 sec)    # count(column)统计结果不包括空值NULL