mysql - 第9课 - count()查询优化 原来count(*)是最快的，请大胆使用

注：当前测试mysql版本：mysql5.7，编码utf8mb4，ENGINE：InnoDB

准备表和数据的脚本：

-- 创建A表
DROP TABLE IF EXISTS `A`;
CREATE TABLE A (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 往A表插入1万条记录
drop procedure if exists insert_A; 
delimiter ;;
create procedure insert_A()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=10000)do                 
    insert into A(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_A();

-- 给a,b分别插入一个 null 字段
insert into A(a,b) values (1,null);
insert into A(a,b) values (null,1);

测试脚本：

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;
-- sql1：count(1)
EXPLAIN select count(1) from A;
-- sql2：count(主键)
EXPLAIN select count(id) from A;
-- sql3：count(*)
EXPLAIN select count(*) from A;
-- sql4：count(普通索引)
EXPLAIN select count(a) from A;
-- sql5：count(普通字段)
EXPLAIN select count(b) from A;

先看查询结果：

注意：a有普通索引，b无索引

说明 count 一个可为空的字段时，只记录不为 null 的总合。

 

再来看执行计划：

注意：a有普通索引，b无索引

 

前4个执行计划一样，都走索引扫描行数，说明前4个执行效率差不多。

执行效率：

count(*) ≈ count(1) > count(a) > count(主键 id) > count(b)

1.count(*)：mysql专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。

2.count(1)：跟count(a)过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(a)还需要取出字段，所以理论上count(1)比count(a)会快一点。

3.count(a)：走索引取出字段统计，而且还要做 null 判断，字段值为null时不计入总数，所以比count(1)性能稍差。

4.count(id)：主键索引的数据最全，二级索引a的数据量相对主键id索引较少，所以count(id)比count(a)性能稍差。

5.count(b)：普通字段全表磁盘扫描，还要做null判断。性能最差。

 

优化方案：

1.InnoDB引擎需要扫描行数，myisam引擎在表磁盘有记录行数。myisam引擎的表查询总行数时直接返回。

2.show table status

如果只需要返回总行数的估值可以用如下sql，性能很高。

show table status like 'A';

3.使用redis记录行数，每次操作都在redis记录，不过一致性不好保证。

 

总结

按效率排行：

count(*) ≈ count(1) > count(索引字段) > count(主键 id) > count(非索引字段)

count(*)效率最高！