count(*)与count(col)对比
Masql的MyISAM引擎 count(*)与count(col)对比
不带where的条件下:
使用的是Server version: 5.1.34 MySQL Community Server (GPL)
使用count(*)执行结果
mysql>?select count(*) from my_cms_25;?????????????????
+----------+
| count(*) |
+----------+
| 1022711 |
+----------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration???| Query??????????????????????????|
+----------+------------+--------------------------------+
|????????1 |?0.00027000?| select count(*) from my_cms_25 |
+----------+------------+--------------------------------+
使用count(id)执行结果:
mysql> select count(id) from my_cms_25;
+-----------+
| count(id) |
+-----------+
|???1022711 |
+-----------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration???| Query???????????????????????????|
+----------+------------+---------------------------------+
|????????1 | 0.00027800 | select count(id) from my_cms_25 |
+----------+------------+---------------------------------+
1 row in set (0.00 sec)
为了保证cache,每次执行一条语句,就把mysql重启动.
mysql> select count(x_rank) from my_cms_25;
+---------------+
| count(x_rank) |
+---------------+
|???????1022711 |
+---------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration???| Query???????????????????????????????|
+----------+------------+-------------------------------------+
|????????1 | 0.00029200 | select count(x_rank) from my_cms_25 |
+----------+------------+-------------------------------------+
1 row in set (0.00 sec)
count(*)比其他两种都快, 不管是count(*), count(id)还是count(x_rank) 执行时间差的不是太多.
注: id是primary key, x_rank是非index.
?
?
带where从句:
mysql> select count(*) from my_cms_25 where id > 100000;
+----------+
| count(*) |
+----------+
|???922711 |
+----------+
1 row in set (0.55 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration???| Query????????????????????????????????????????????|
+----------+------------+--------------------------------------------------+
|????????1 | 0.54154100 | select count(*) from my_cms_25 where id > 100000 |
+----------+------------+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(id) from my_cms_25 where id > 100000;
+-----------+
| count(id) |
+-----------+
|????922711 |
+-----------+
1 row in set (0.52 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration???| Query?????????????????????????????????????????????|
+----------+------------+---------------------------------------------------+
|????????1 | 0.51955100 | select count(id) from my_cms_25 where id > 100000 |
+----------+------------+---------------------------------------------------+
1 row in set (0.00 sec)
?
mysql> select count(*) from my_cms_25 where id > 100;
+----------+
| count(*) |
+----------+
| 1022611 |
+----------+
1 row in set (0.60 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration???| Query?????????????????????????????????????????|
+----------+------------+-----------------------------------------------+
|????????1 | 0.59619000 | select count(*) from my_cms_25 where id > 100 |
+----------+------------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(id) from my_cms_25 where id > 100;
+-----------+
| count(id) |
+-----------+
|???1022611 |
+-----------+
1 row in set (0.57 sec)
mysql> show profiles;
+----------+------------+------------------------------------------------+
| Query_ID | Duration???| Query??????????????????????????????????????????|
+----------+------------+------------------------------------------------+
|????????1 | 0.57422700 | select count(id) from my_cms_25 where id > 100 |
+----------+------------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(x_rank) from my_cms_25 where id > 100;
+---------------+
| count(x_rank) |
+---------------+
|???????1022611 |
+---------------+
1 row in set (1.31 sec)
mysql> show profiles;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration???| Query??????????????????????????????????????????????|
+----------+------------+----------------------------------------------------+
|????????1 | 1.30890800 | select count(x_rank) from my_cms_25 where id > 100 |
+----------+------------+----------------------------------------------------+
1 row in set (0.00 sec)
带where条件的, 测试的结果count(id) > count(*) > count(x_rank)
就是说,count(*)不一定比count(pk)快.
?
?
对于innodb engine的, 叶兄做了一个测试,自己也做了一个测试,测试结果不相同:
http://imysql.cn/2008_06_24_speedup_innodb_count
http://bugs.mysql.com/bug.php?id=19271
?
使用的版本:Server version: 5.1.30-log Source distribution
mysql> select count(*) from relation;
+----------+
| count(*) |
+----------+
| 3010500 |
+----------+
1 row in set (10.60 sec)
mysql> show profiles;
+----------+-------------+-------------------------------+
| Query_ID | Duration????| Query?????????????????????????|
+----------+-------------+-------------------------------+
|????????1 | 10.59423300 | select count(*) from relation |
+----------+-------------+-------------------------------+
1 row in set (0.01 sec)
?
?
mysql> select count(id) from relation;
+-----------+
| count(id) |
+-----------+
|???3010329 |
+-----------+
1 row in set (3.87 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration???| Query??????????????????????????|
+----------+------------+--------------------------------+
|????????1 | 3.87004700 | select count(id) from relation |
+----------+------------+--------------------------------+
1 row in set (0.00 sec)
?
?
mysql> select count(*) from relation where id>100;
+----------+
| count(*) |
+----------+
| 3010158 |
+----------+
1 row in set (1.03 sec)
mysql> show profiles;????????????????
+----------+------------+--------------------------------------------+
| Query_ID | Duration???| Query??????????????????????????????????????|
+----------+------------+--------------------------------------------+
|????????1 | 3.87004700 | select count(id) from relation?????????????|
|????????2 | 1.03079800 | select count(*) from relation where id>100 |
+----------+------------+--------------------------------------------+
?
?
mysql> show profiles;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration???| Query????????????????????????????????????????????|
+----------+------------+--------------------------------------------------+
|????????1 | 3.87004700 | select count(id) from relation???????????????????|
|????????2 | 1.03079800 | select count(*) from relation where id>100???????|
|????????3 | 1.01789300 | select count(id) from relation where id>100??????|
|????????4 | 1.32853100 | select count(*) from relation where infoid > 100 |
+----------+------------+--------------------------------------------------+
?
?
?
?
测试结果不全相同. 不管带不带where条件时count(pk)是比count(*)快的．
count(secondary key)?是没有count(pk)快的。难道bug fix了在5.1.30?
?
感觉应该是pk的存储结构和secondary key不同.这个和官方说的符合,primary key的scan是最快的原因吧.
?
从mysql query optimizer角度上说, count(*) 需要mysql分析更多的column,这个是有一定的overhead的。
但直接count(pk)就没有分析的这个开销.:)
?
下面是更详细,可以看出慢在那一步:
在myisam engine上:
?
?
?
mysql> select count(*) from my_cms_25;
+----------+
| count(*) |
+----------+
| 1022711 |
+----------+
1 row in set (0.00 sec)
mysql> show profile;
+--------------------------------+----------+
| Status?????????????????????????| Duration |
+--------------------------------+----------+
| starting???????????????????????| 0.000033 |
| checking query cache for query |?0.000088?|
| Opening tables?????????????????| 0.000014 |
| System lock????????????????????| 0.000005 |
| Table lock?????????????????????| 0.000026 |
| init???????????????????????????| 0.000033 |
| optimizing?????????????????????| 0.000013 |
| executing??????????????????????| 0.000015 |
| end????????????????????????????| 0.000004 |
| query end??????????????????????| 0.000003 |
| freeing items??????????????????| 0.000024 |
| storing result in query cache | 0.000012 |
| logging slow query?????????????| 0.000003 |
| cleaning up????????????????????| 0.000004 |
+--------------------------------+----------+
14 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration???| Query??????????????????????????|
+----------+------------+--------------------------------+
|????????1 | 0.00027700 | select count(*) from my_cms_25 |
+----------+------------+--------------------------------+
1 row in set (0.00 sec)
?
?
mysql> select count(id) from my_cms_25;
+-----------+
| count(id) |
+-----------+
|???1022711 |
+-----------+
1 row in set (0.00 sec)
mysql> show profile;
+--------------------------------+----------+
| Status?????????????????????????| Duration |
+--------------------------------+----------+
| starting???????????????????????| 0.000056 |
| checking query cache for query |?0.000077?|
| Opening tables?????????????????| 0.000014 |
| System lock????????????????????| 0.000005 |
| Table lock?????????????????????| 0.000025 |
| init???????????????????????????| 0.000047 |
| optimizing?????????????????????| 0.000014 |
| executing??????????????????????| 0.000015 |
| end????????????????????????????| 0.000004 |
| query end??????????????????????| 0.000003 |
| freeing items??????????????????| 0.000027 |
| storing result in query cache | 0.000011 |
| logging slow query?????????????| 0.000004 |
| cleaning up????????????????????| 0.000003 |
+--------------------------------+----------+
14 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration???| Query???????????????????????????|
+----------+------------+---------------------------------+
|????????1 | 0.00030500 | select count(id) from my_cms_25 |
+----------+------------+---------------------------------+
1 row in set (0.00 sec)
?
?
innodb engine:
?
mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (1.98 sec)
mysql> show profile;
+--------------------------------+----------+
| Status?????????????????????????| Duration |
+--------------------------------+----------+
| starting???????????????????????| 0.000041 |
| checking query cache for query | 0.000082 |
| Opening tables?????????????????| 0.000015 |
| System lock????????????????????| 0.000004 |
| Table lock?????????????????????| 0.000042 |
| init???????????????????????????| 0.000033 |
| optimizing?????????????????????| 0.000012 |
| statistics?????????????????????| 0.000014 |
| preparing??????????????????????| 0.000010 |
| executing??????????????????????| 0.000009 |
| Sending data???????????????????|?1.987284?|
| end????????????????????????????| 0.000019 |
| query end??????????????????????| 0.000010 |
| freeing items??????????????????| 0.000078 |
| storing result in query cache | 0.000016 |
| logging slow query?????????????| 0.000005 |
| cleaning up????????????????????| 0.000008 |
+--------------------------------+----------+
17 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration???| Query???????????????????????|
+----------+------------+-----------------------------+
|????????1 |?1.98768200?| select count(*) from sbtest |
+----------+------------+-----------------------------+
1 row in set (0.00 sec)
?
?
?
mysql> select count(id) from sbtest;
+-----------+
| count(id) |
+-----------+
|???1000000 |
+-----------+
1 row in set (1.99 sec)
mysql> show profile;
+--------------------------------+----------+
| Status?????????????????????????| Duration |
+--------------------------------+----------+
| starting???????????????????????| 0.000034 |
| checking query cache for query | 0.000079 |
| Opening tables?????????????????| 0.000015 |
| System lock????????????????????| 0.000005 |
| Table lock?????????????????????| 0.000042 |
| init???????????????????????????| 0.000037 |
| optimizing?????????????????????| 0.000013 |
| statistics?????????????????????| 0.000013 |
| preparing??????????????????????| 0.000010 |
| executing??????????????????????| 0.000021 |
| Sending data???????????????????|?1.982898?|
| end????????????????????????????| 0.000021 |
| query end??????????????????????| 0.000009 |
| freeing items??????????????????| 0.000089 |
| storing result in query cache | 0.000017 |
| logging slow query?????????????| 0.000006 |
| cleaning up????????????????????| 0.000010 |
+--------------------------------+----------+
17 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration???| Query????????????????????????|
+----------+------------+------------------------------+
|????????1 |?1.98331900?| select count(id) from sbtest |
+----------+------------+------------------------------+
1 row in set (0.00 sec)
?
