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;
+----------+-------