count在myisam和innodb下面的差异性以及效率研究

总结：

1.myisam保存表的总行数,因此count(*)并且无where子句,很快会返回表的总行数
2.myisam保存表的总行数,利用count(column)并且无where子句,并且此column不为null,很快会返回表的总行数
3.myisam保存表的总行数,利用count(column)并且无where子句,并且此column可以为null,mysql会对表进行全表或全索引扫描来确定行数
4.innodb查询count(*),count(column(not null)),count(column(may be null))并且无where子句,mysql会对表进行全表或全索引扫描来确定行数
5.myisam和innodb查询count(*),count(column(not null)),count(column(may be null))并且存在where子句,mysql会对表进行索引扫描(如果列上有索引),速度也比较快

下面我们用数据来验证一下：

test_lan 表结构如下，数据条数为160万左右。

mysql> desc test_lan;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(10)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(40) | NO   |     |         |                |
| value   | text        | NO   |     | NULL    |                |
| package | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

mysql> show create table test_lan;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                               查看表类型：先测试myisam类型的表

CREATE TABLE `test_lan` (
 `id` int(10) NOT NULL auto_increment,
 `name` varchar(40) NOT NULL default '' COMMENT '???',
 `value` text NOT NULL COMMENT '????',
 `package` varchar(50) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1618705 DEFAULT CHARSET=utf8

先清缓存

mysql> reset query cache;

也可以

mysql> flush tables test_lan;

测试1：count（0） 和count（*） 无 where 字句

mysql> select count(0) from test_lan;
+----------+
| count(0) |
+----------+
|  1618704 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test_lan;
+----------+
| count(*) |
+----------+
|  1618704 |
+----------+
1 row in set (0.00 sec)

差别不大，时间都很短

测试2：count（0） 和count（*） 有 where 字句（where字段为主键id）

mysql> select count(*) from test_lan where id >0;
+----------+
| count(*) |
+----------+
|  1618704 |
+----------+
1 row in set (0.78 sec)

mysql> flush tables test_lan;

mysql> flush tables test_lan;

mysql> select count(*) from test_lan where id >0;
+----------+
| count(*) |
+----------+
|  1618704 |
+----------+
1 row in set (0.77 sec)

可以看出count(0) 和count(*)时间相差不大，但是有where字句比没有where字句时间相差很大。

原因：myisam表的count是表的一个属性，如果没有where字句，count直接返回总函数，如果有where字句需要全表扫描。

测试2：count（column在null和not null 列上的影响）

PS:id为主键，非空列；package为may null列

mysql> flush tables test_lan;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from test_lan where id >0;
+----------+
| count(*) |
+----------+
|  1618704 |
+----------+
1 row in set (0.79 sec)

mysql> flush tables test_lan;

mysql> select count(id) from test_lan;
+-----------+
| count(id) |
+-----------+
|   1618704 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(`package`) from test_lan;
+------------------+
| count(`package`) |
+------------------+
|          1618704 |
+------------------+
1 row in set (6.17 sec)

可以看出在not null列和 非not null 列上的差距很大。

下面再来看看 count在innodb类型下面的表现。

mysql> alter table test_lan engine=innodb;
更改表类型

mysql> select count(*) from test_lan;
+----------+
| count(*) |
+----------+
|  1618704 |
+----------+
1 row in set (0.88 sec)

mysql> select count(*) from test_lan where id >0;
+----------+
| count(*) |
+----------+
|  1618704 |
+----------+
1 row in set (1.08 sec)

mysql> select count(`id`) from test_lan;
+-------------+
| count(`id`) |
+-------------+
|     1618704 |
+-------------+
1 row in set (0.84 sec)

mysql> select count(`package`) from test_lan;
+------------------+
| count(`package`) |
+------------------+
|          1618704 |
+------------------+
1 row in set (1.05 sec)

总结：innodb和myisam类型的最主要的区别就是

count(*) 在 无where字句和有where字句的情况下差别不大，都需要全表扫描

而在count（may null column）列的时候速度较快。