互联网建站公司,房产网站建设什么类型,合肥瑶海区新楼盘在售,网站建设收费明细表优化mysql数据库时#xff0c;经常有开发询问 count(1)和count(primary_key) VS count(*)的性能有何差异#xff1f;看似简单的问题#xff0c;估计会有很多人对此存在认知误区#xff1a;1. 认为count(1) 和 count(primary_key) 比 count(*) 的性能好。2. count(column) 和…优化mysql数据库时经常有开发询问 count(1)和count(primary_key) VS count(*)的性能有何差异看似简单的问题估计会有很多人对此存在认知误区1. 认为count(1) 和 count(primary_key) 比 count(*) 的性能好。2. count(column) 和 count(*) 效果是一样的。本文对上述两点做如下测试测试环境:rootyang 07:17:04CREATE TABLE mytab (- id int(10) unsigned NOT NULL,- v1 int(11) default NULL,- v2 int(10) unsigned NOT NULL,- KEY idx_id (id)- ) ENGINEMyISAM DEFAULT CHARSETlatin1;Query OK, 0 rows affected (0.00 sec)1 select语句 不含有where 条件rootyang 07:41:11select count(*) from mytab;----------| count(*) |----------| 2000000 |----------1 row in set (0.00 sec)rootyang 07:41:22select count(id) from mytab;-----------| count(id) |-----------| 2000000 |-----------1 row in set (0.00 sec)rootyang 07:41:37select count(v1) from mytab;-----------| count(v1) |-----------| 2000000 |-----------1 row in set (0.12 sec)rootyang 07:41:41select 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 条件的查询rootyang 01:13:13select count(*) from t1 where id 200000;----------| count(*) |----------| 200000 |----------1 row in set (0.06 sec)rootyang 01:15:22explain 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)rootyang 01:15:30 select count(v1) from t1 where id 200000;-----------| count(v1) |-----------| 200000 |-----------1 row in set (0.17 sec)rootyang 01:15:37explain 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)rootyang 01:15:42 select count(v2) from t1 where id 200000;-----------| count(v2) |-----------| 200000 |-----------1 row in set (0.16 sec)rootyang 01:15:49explain 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(*) 做同样的处理这样速度会更快一些。下面修改索引结构使用复合索引。rootyang 01:17:07alter 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: 0rootyang 01:17:36 select count(v1) from t1 where id 200000;-----------| count(v1) |-----------| 200000 |-----------1 row in set (0.07 sec)rootyang 01:17:49explain 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(*) 是一样的其实是有差别的。请看下面的例子rootyang 10:01:38create table t3 (id int ,v1 int ) engine innodb;Query OK, 0 rows affected (0.01 sec)rootyang 10:03:54insert 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: 0rootyang 10:03:57select 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 存储引擎的测试rootyang 01:29:53alter table t1 engineinnodb;Query OK, 2000000 rows affected (11.25 sec)Records: 2000000 Duplicates: 0 Warnings: 0rootyang 01:30:26select 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更多。rootyang 01:30:32select count(v1) from t1;-----------| count(v1) |-----------| 2000000 |-----------1 row in set (0.77 sec)rootyang 01:30:40select count(v2) from t1;-----------| count(v2) |-----------| 2000000 |-----------1 row in set (0.73 sec)在使用where条件的情况下等值查询和使用到索引情况下 myisam 表和innodb的速度是几乎无差别的具体的性能表现和where 条件有关。rootyang 10:14:03select count(v2) from t1 where id 20000;-----------| count(v2) |-----------| 1 |-----------1 row in set (0.00 sec)推荐阅读
