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)列的时候速度较快。

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*