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

最新推荐文章于 2022-12-07 11:23:36 发布

舒明月

最新推荐文章于 2022-12-07 11:23:36 发布

阅读量94

点赞数

文章标签： bash mysql count()

本文链接：https://blog.csdn.net/weixin_34805308/article/details/113239195

版权

优化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;

+----+-------------+-------+-------+---------------+--------+---------+------+--------+--------------------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+--------------------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+--------------------------+

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;

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

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;

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

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;

+----+-------------+-------+-------+---------------+-----------+---------+------+--------+--------------------------+

+----+-------------+-------+-------+---------------+-----------+---------+------+--------+--------------------------+

+----+-------------+-------+-------+---------------+-----------+---------+------+--------+--------------------------+

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 |