Written by honors

COUNT优化

首先准备一张测试表

CREATE TABLE user_test_count (
  id int(11) NOT NULL,
  name varchar(50) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  email varchar(60) DEFAULT NULL,
  birthday date DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中如下数据
file

运行count(*)

EXPLAIN SELECT COUNT(*) FROM user_test_count

file

使用了主键索引，接着在表中字段创建一个索引

ADD INDEX usc_email(email);

再次运行
file
使用了新创建的索引，索引长度是183

再次新增索引

ADD INDEX usc_birthday(birthday);

运行

file
使用了usc_birthday，索引长度是4

结论

  • 当没有非主键索引时，会使用主键索引
  • 当存在非主键索引时，会优先使用非主键索引
  • 如果存在多个非主键索引时，会使用最小的非主键索引

原因
表的存储引擎为innodb,innodb非主键索引的叶子节点存储的是索引+主键。主键索引叶子节点存储的是主键+表数据。mysql是以页为单位的，在一个页里面，非主键索引可以存储更多的条目，对于一张表，如果有一百万条数据,使用非主键索引扫描的page可能有100个，使用主键索引，可能就会有500个，于是使用非主键索引比主键索引要好

运行sql

EXPLAIN SELECT COUNT(email) FROM user_test_count

结果
file
使用了索引

直接运行

 SELECT COUNT(email) FROM user_test_count

file
直接运行

 SELECT COUNT(*) FROM user_test_count

file

结论
count(字段)只会针对该字段进行统计，使用这个字段上的索引（如果存在索引的话）
count(字段)会排除掉该字段值为null的行，count(*)则不会

除count(*)和count(字段以外)，还有count(1),直接运行

EXPLAIN SELECT COUNT(1) FROM user_test_count

file

也是使用了最小的索引字段，在官方文档中这样写道
file
对于innodb来说，count(*)和count(1)没有区别，性能上是一样的，没有性能差异

SQL_CALC_FOUND_ROWS

在实际项目中，分页查询数据时，会有这样的两条sql，分别查询数据和总数

SELECT * FROM salaries LIMIT 0,10;
SELECT count(*) FROM salaries;

而使用SQL_CALC_FOUND_ROWS以后，mysql就会在查询完毕数据后立刻查询count(*)的数据，然后再数据后增加
SELECT FOUND_ROWS() as total_count;即可获取这个count

mybatis中可以这样使用

对于的dao层

public List listSalaries();

对于的service层

    List result = testExtDao.listSalaries();
    List extResults = (List) result.get(0);
    Integer count = ((List) result.get(1)).get(0);

缺点
官方文档中提到，这种方式在8.0.17以后，已经被废弃，后续可能会被删除

发表回复