MySQL基础优化(7)：高效count函数

count方法用于统计记录数目是很常见的，但是如果使用不当会导致效率低下。

首先创建测试表，

use test; 
drop table if exists t1; 

CREATE TABLE t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) DEFAULT NULL,
  b int(11) NOT NULL,
  c int(11) DEFAULT NULL,
  d int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_a (a),
  KEY idx_b (b)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */

delimiter ;;
create procedure insert_t1() /* 创建存储过程insert_t1 */
begin
declare i int; 
set i=1; 
while(i<=10000)do 
insert into t1(a,b,c,d) values(i,i,i,i); 
set i=i+1; 
end while;
end;;
delimiter ; 
call insert_t1(); /* 运行存储过程insert_t1 */

insert into t1(a,b,c,d) values (null,10001,10001,10001),(10002,10002,10002,10002);

drop table if exists t2; 
create table t2 like t1; /* 创建表t2，表结构与t1一致 */
alter table t2 engine =myisam; /* 把t2表改为MyISAM存储引擎 */
insert into t2 select * from t1;  /* 把t1表的数据转到t2表 */

CREATE TABLE t3 (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) DEFAULT NULL,
  b int(11) NOT NULL,
  c int(11) DEFAULT NULL,
  d int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  CHARSET=utf8mb4;
insert into t3 select * from t1;  /* 把t1表的数据转到t3表 */

重新认识count函数

1）count(*)和count(字段)的区别

当使用count统计某一列时，如count(a)其中a是列名，是不会统计null的。如，

select count(a) from t1;

建表过程中写入了10002行数据，因为其中一行数据a字段值为null，所以会显示少一行记录。

而count(*)则是无论是否存在null值，都会被统计。如，

select count(*) from t1;

统计的是所有的行。

2）MyISAM引擎和InnoDB引擎count(*)的区别

当没有where子句时，count(*)对于MyISAM引擎速度非常快。因为MyISAM引擎会把表的总行数存在磁盘上。

explain select count(*) from t2;

Extra项的值为 Select tables optimized away，表示从MyISAM引擎维护的准确行数上获取统计值。

而InnoDB不会保留表中的行数，因为InnoDB支持并发，并发事务可能同时读取到不同的行数。所以InnoDB执行count(*)时，每次都是临时计算的，比MyISAM慢很多。

explain select count(*) from t1;

可见使用的是b字段的索引，也就是说InnoDB会去遍历普通索引树计算表中的数据总量。

3）5.7.18版本前后count(*)的区别

• 该版本之前InnoDB通过扫描聚簇索引(简单理解为主键索引树)处理count(*)

• 该版本之后，通过遍历最小的可用二级索引树(简单理解为普通索引树)处理count(*)，如果不存在二级索引，则扫描聚簇索引。

因为InnoDB的二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据。所以二级索引书比主键索引树要小。因此优化器基于成本的考虑，优先选择二级索引树。

因此对于InnoDB而言，count(主键)没有count(*)快。

4）count(1)和count(*)效率比较

两种方式效率是相同的。

优化count

1）show table status

有时候只需要知道大概的数据量时，可以使用show table status，如

show table status like 't1'\G

这个值是估算值，可能与实际相差40%到50%。

2）增加计数表