ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MySQL】性能优化之 count(*) VS count(col)

【MySQL】性能优化之 count(*) VS count(col)

原创 Linux操作系统 作者:杨奇龙 时间:2013-10-20 10:16:41 0 删除 编辑
优化mysql数据库时,经常有开发询问 count(1)和count(primary_key) VS count(*)的性能有何差异?看似简单的问题,估计会有很多人对此存在认知误区:
1. 认为count(1) 和 count(primary_key) 比 count(*) 的性能好。
2. count(column) 和 count(*) 效果是一样的。
本文对上述两点做如下测试,
测试环境:
root@yang 07:17:04>CREATE TABLE `mytab` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `v1` int(11) default NULL,
    ->   `v2` int(10) unsigned NOT NULL,
    ->   KEY `idx_id` (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
1 select语句 不含有where 条件
root@yang 07:41:11>select count(*) from mytab;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.00 sec)
root@yang 07:41:22>select count(id) from mytab; 
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.00 sec)
root@yang 07:41:37>select count(v1) from mytab;  
+-----------+
| count(v1) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.12 sec)
root@yang 07:41:41>select count(v2) from mytab; 
+-----------+
| count(v2) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.00 sec)
以上使用了myisam表做了测试,四种查询方式的结果有所不同,注意到count(V1) 的时间是0.12s 因为myisam 表的特性其已经保存了表的总行数, count(*)相对非常快。
coun(v2) 比count(v1) 快是因为v1 字段可以为空,mysql 在执行count(col) 是表示结果集中有多少个col字段不为空的记录,mysql 存储引擎会去检查表中说有行记录中col字段是否为空,并计算出非空的个数。

2 使用带有where 条件的查询:
root@yang 01:13:13>select count(*) from t1 where id < 200000;
+----------+
| count(*) |
+----------+
|   200000 |
+----------+
1 row in set (0.06 sec)
root@yang 01:15:22>explain select count(*) from t1 where id < 200000;
+----+-------------+-------+-------+---------------+--------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1    | range | idx_id        | idx_id | 4       | NULL | 205923 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
root@yang 01:15:30> select count(v1) from t1 where id < 200000;        
+-----------+
| count(v1) |
+-----------+
|    200000 |
+-----------+
1 row in set (0.17 sec)
root@yang 01:15:37>explain  select count(v1) from t1 where id < 200000;
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1    | range | idx_id        | idx_id | 4       | NULL | 205923 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
1 row in set (0.00 sec)
root@yang 01:15:42> select count(v2) from t1 where id < 200000;         
+-----------+
| count(v2) |
+-----------+
|    200000 |
+-----------+
1 row in set (0.16 sec)
root@yang 01:15:49>explain select count(v2) from t1 where id < 200000;
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1    | range | idx_id        | idx_id | 4       | NULL | 205923 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
1 row in set (0.00 sec)
count(*) 可以使用覆盖索引 ,而count(col)不行。v2 是非空列,处理起来应该和count(*)类似才对,这里显示却和v1 字段的处理一致。如果mysql 优化器处理的足够好,检查到字段为非空时,即可和count(*) 做同样的处理,这样速度会更快一些。下面修改索引结构使用复合索引。
root@yang 01:17:07>alter table t1 drop key idx_id,add key idx_id_v1(id,v1);
Query OK, 2000000 rows affected (1.49 sec)
Records: 2000000  Duplicates: 0  Warnings: 0
root@yang 01:17:36> select count(v1) from t1 where id < 200000;               
+-----------+
| count(v1) |
+-----------+
|    200000 |
+-----------+
1 row in set (0.07 sec)
root@yang 01:17:49>explain  select count(v1) from t1 where id < 200000;
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1    | range | idx_id_v1     | idx_id_v1 | 4       | NULL | 196079 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
对于字段v1 的查询性能相对上例中提升两倍多,当然如果是生产环境可能提升更高。最终面向开发是,最好先有开发修改应用程序中的sql 避免使其选择count(col)。
对于第二个误区:认为 count(column) 和 count(*) 是一样的,其实是有差别的。请看下面的例子:
root@yang 10:01:38>create table t3 (id int ,v1 int ) engine =innodb;
Query OK, 0 rows affected (0.01 sec)
root@yang 10:03:54>insert t3 values (null,null),(1,null),(null,1),(1,null),(null,1),(1,null),(null,null);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0
root@yang 10:03:57>select count(id),count(id),count(v1) from t3;
+-----------+-----------+-----------+
| count(id) | count(id) | count(v1) |
+-----------+-----------+-----------+
|         3 |         3 |         2 |
+-----------+-----------+-----------+
1 row in set (0.00 sec)
count(col) 是表示结果集中有多少个column字段不为空的记录。
count(*) 是表示整个结果集有多少条记录。
3 增加对innodb 存储引擎的测试
root@yang 01:29:53>alter table t1 engine=innodb;
Query OK, 2000000 rows affected (11.25 sec)
Records: 2000000  Duplicates: 0  Warnings: 0
root@yang 01:30:26>select count(*) from t1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.69 sec)
对于 innodb表比myisam 表查询总行数 性能慢是因为innodb 存储引擎并没有保存行的总数,innodb 表支持mvcc ,不同的事务可能看到不同的行记录数。因此每次count(*) 和count(col)(没有使用索引的情况下) 都要对表进行索引扫描,可能大家对最终获取结果的时间有疑问,为什么myisam 表是0.17s 左右,而innodb 是0.77s ,因为innodb 表在磁盘存储的大小比myisam大,扫描的物理page更多。
root@yang 01:30:32>select count(v1) from t1; 
+-----------+
| count(v1) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.77 sec)
root@yang 01:30:40>select count(v2) from t1; 
+-----------+
| count(v2) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.73 sec)
在使用where条件的情况下:等值查询和使用到索引情况下 ,myisam 表和innodb的速度是几乎无差别的,具体的性能表现和where 条件有关。 
root@yang 10:14:03>select count(v2) from t1 where id =20000;  
+-----------+
| count(v2) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

推荐阅读:
http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/
http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/
http://stackoverflow.com/questions/433913/in-sql-is-there-a-difference-between-count-and-countfieldname
http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count?answertab=active#tab-top

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-774679/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7626019