MySQL中聚合函数count的使用和性能优化

本文将探讨以下问题

1.count(*) 、 count(n)、count(null)与count(fieldName)
2.distinct 与 count 连用
3.group by (多个字段) 与 count 实现分组计数

4.case when 语句与 count 连用实现按过滤计数

 

一、 COUNT()作用

count的基本作用是有两个：

• 统计某个列的数据的数量（不统计NULL）；
• 统计结果集的行数；

  准备表以及数据

 

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

create table bonus (
    empno numeric(4),
    job varchar(9),
    sal numeric,
    comm numeric
);

create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);

1.1、count(*) 与 count(列) 比较

mysql> select * from emp;

+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

1 row in set (0.00 sec)

二、基于 MYSQL存储引擎

1)MyISAM存储引擎

MyISAM的COUNT()函数总是非常快，不过这是有前提条件的，即只有没有任何where条件的COUNT(*)才非常快，因为此时无需实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。如果MySQL知道某列col不可能为NULL值，那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)。

当统计带WHERE子句的结果集行数，可以是统计某个列值的数量时，MySQL的COUNT()和其它存储引擎没有任何不同，就不再有神话般的速度了。所以在MyISAM引擎表上执行COUNT()有时候比别的引擎快，有时候比别的引擎慢，这受很多因素影响，要视具体情况而定。

2）Innodb存储引擎：

(1)     innodb存储引擎的物理结构包含 表空间、段、区、页、行 五个层级，数据文件按照主键排序存储在页中（页在逻辑上连续），主键的位置即为数据存储位置。

(2)     二级索引存储的数据为指定字段的值与主键值。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，故大多数情况下，通过二级索引统计数据效率 >= 基于主键统计效率。

(3)    由于二级索引存储的数据为指定字段的值与主键值，故在无索引覆盖的情况下，查询二级索引后会根据二级索引获取的主键到主键索引中提取数据，此过程可能造成大量的随机io，导致查询速度较慢。

(4)    由于主键索引与数据存储保持一致，故基于主键的查找数据要比通过二级索引查询数据要快（使用二级索引时，查询到的数据条数>总条数的20%时候mysql就选择全表扫描，但在主键索引上，即使符合条件的达到 90%依然会走索引）。

1.3、count慢的原因:

innodb为聚簇索引同时支持事物，其在count指令实现上采用实时统计方式。在无可用的二级索引情况下，执行count会使MySQL扫描全表数据，当数据中存在大字段或字段较多时候，其效率非常低下（每个页只能包含较少的数据条数，需要访问的物理页较多）。

1.4、innodb可优化点：

1. 主键需要采用占用空间尽量小的类型且数据具有连续性（推荐自增整形id），这样有利于减少页分裂、页内数据移动，可加快插入速度同时有利于增加二级索引密度（一个数据页上可以存储更多的数据）。

2.在表包含大字段或字段较多情况下，若存在count统计需求，可建一个较小字段的二级索引（例 char(1) , tinyint )来进行count统计加速。

 

 

 

下面做个count优化例子：

1.首先我们创建一直innodb表，并包含大字段（或包含较多字段）：

CREATE TABLE `qstardbcontent` (
  `id` BIGINT(20) NOT NULL DEFAULT '0',
  `content` MEDIUMTEXT,
  `length` INT(11)  NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

2.插入50万条数据，每条数据 5K

3.执行select count(*) from qstardbcontent

可以看到，近50万条内容较多的数据执行一个count（*） 就需要耗时 13分28秒

下面我们做个优化，在length字段上加个索引， 执行sql： ALTER TABLE qstardbcontent ADD KEY(LENGTH)；

索引建完成后，再执行 select count(*) from qstardbcontent;

可以看到，整个统计查询非常快，仅用了 354毫秒就完成了查询。

 

1.5、加速原因：

 

    在innodb表上创建了一个二级索引，Innodb在执行count（*）时候由优化器选择执行路径。

本例中， 二级索引的存储空间仅包含length字段值、数据主键，假设二级索引辅助结构不占用空间（仅计算数据占用空间），在默认情况下，MySQL的一个数据页大小为16K，一个页可存储的数据条数为 16*1024/(4+8) =1365 ，按照单页存储空间占用为50%（页分裂现象导致页不满）计算，50万条数据的统计仅需要读取约732个物理页，而页在连续的情况下，数据库一次可读取多个连续的页，数据读取总量为 16k*732约 12MB，因mysql空间分配为按区分配，每个区1M，一次分配1-5个连续区，当数据量较小，一次仅分配一个区，12M数据会分配在12个区中，按照pc硬盘(转速7200转/分) 70m/s 的读取速度，整个过程的io寻址时间(12*8.5ms=102)+读取时间（12m/70m=171ms）=273ms，而数据解析统计约为 30-100ms，故总耗时会在300ms附近（注：count优化功能在5.1版本并不支持）。

三、性能优化

 

通常情况下，count(*)操作需要大量扫描数据表中的行，如果避免扫描大量的数据就成为优化该语句的关键所在。针对这个问题可以从如下两个角度考虑。

3.1 在数据库的层次上优化

   索引

 

3.2、在应用的层次上优化

在应用的层次上优化，可以考虑在系统架构中引入缓存子系统，比如在过去中常用的Memcached，或者现在非常流行的Redis， 但是这样会增加系统的复杂性。

 

总结：count(*) 将返回表格中所有存在的行的总数包括值为 null 的行；

        count(列名) 将返回表格中除去 null 以外的所有行的总数 (有默认值的列也会被计入）；

 

 

优化方法：count（*）与count（COL）的区别

 

count（*）在统计时会统计上空值（NULL），但是count（COL）则不会。所以要灵活运用count的这个特性，来实现相应的查询。

    但是从性能上来说：count（*）是找一个占用空间最小的索引字段，然后对它进行记数，在count命令中，它指的是“任意一个“。
    对于一个大表来说，如果你的字段有bit类型，如性别字段，表示真假关系的字段，我们需要为它加上索引，加上之后，我们的count速度就会快很多。
        1、任何情况下 SELECT COUNT(*) FROM tablename; 是最优选择；
        2、尽量减少SELECT COUNT(*) FROM tablename WHERE COL = value; 这种查询；
        3、杜绝SELECT COUNT(COL) FROM tablename 后面跟各种WHERE条件； 的出现。

不同count的用法

    count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

    count的用法有多种，分别是count(*)、count(字段)、count(1)、count(主键id)。那么多种用法，到底有什么差别呢？当然，「前提是没有where条件语句」。

    count(id)：InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

    count(1)：InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字1进去，判断是不可能为空的，按行累加。

    count(字段)：

        如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

        如果这个字段定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

    count(*)：不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

    所以结论很简单：「按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以建议读者，尽量使用count(*)。」

    「注意」：这里肯定有人会问，count(id)不是走的索引吗，为什么查询效率和其他的差不多呢？陈某在这里解释一下，虽然走的索引，但是还是要一行一行的扫描才能统计出来总数。

总结

    MyISAM表虽然count(*)很快，但是不支持事务；

    show table status命令虽然返回很快，但是不准确；

    InnoDB直接count(*)会遍历全表(没有where条件)，虽然结果准确，但会导致性能问题。

    缓存系统的存储计数虽然简单效率高，但是无法保证数据的一致性。

    数据库保存计数很简单，也能保证数据的一致性，建议使用。

但是我的数据量比这个大很多，而对数据的准确性要求就不那么高。所以首先要明确需求。其他答案有的说了用缓存，有的答案对比了count(*)、count(1)的区别，都很好，但是我认为还是要看一下题主的场景。我根据我实际开发的经验总结如下几个方面，FYI。

数据量大/准确性要求低/请求量大

    这种场景一般是C端产品，比如上面说的得到APP的订阅数目，如果对一致性要求不高，可以直接在内存中使用缓存，用guava在内存中做一个缓存定时刷新即可，百万量级count(*)有缓存的频率还不至于有啥性能问题；
    但是内存内缓存有一个问题就是不同服务器之间的缓存数量是不一致的，可以考虑用redis作为计数，一般这种场景是大多数同学遇到的，简单粗暴搞定即可；
    用show table status。这个建议还是不要用了，翻了下mysql 的doc，40%的误差概率，碰上就有点大了呀。

    TABLE_ROWS
    The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

数据量大/准确性要求高/请求量一般

这种场景一般出现在账务上，比如有多少人打款。而且估计DAU在亿级别的公司可能才会遇到。这里最关键的问题还是一致性的要求。在并发系统中，看看我们用redis，我们看看会出现什么样的一致性问题：

时间       A processor         B processor
T1         插入数据
T2                             1.redis#get计数器；2. 查询最新的N条数据
T3         redis#incr

在T2的时间点的时候会出现数据不一致，B看到的是数据已经更新，但是数据库还没更新。我们就在想，如果放到一个事务里面，就可以完美解决这个问题了呀。由于事务，innoDB不支持像MyISAM准确计数，解铃还须系铃人，所以我们建一个计数表（count_table）+事务，解这个问题了。

时间         会话A                            会话B
T1         begin;
           在计数表中插入一条数据;
T2                                          begin;
                                            1. 读count_table；
                                            2. 查询最新的N条数据
                                            commit;
T3         更新conut_table;
           commit;

 

在T1的时候，如果采用Mysql默认的事务隔离级别：读提交。因为T1事务还没有提交，所以插入的数据，B是读不到的，所以从逻辑上来说是一致的。
数据量大/准确性要求高/请求量特别高

抱歉，没遇到过。如果你觉得你遇到了，你的架构需要你重新design and review，相信我。
带条件count(*)

很多时候我们的业务场景不是数据量多，而是条件复杂。这其实就是一个查询优化的问题了，和是不是count(*)没有关系，那么有以下两招常用，这个得具体问题具体分析了。比如时间维度可以加一个索引来优化；

select * from table_name where a = x and b = x;

    加索引
    业务拆分

count性能比较

    count(primary key)。遍历整个表，把主键值拿出来，累加；
    count(1)。遍历整个表，但是不取值，累加；
    count(非空字段)。遍历整个表，读出这个字段，累加；
    count(可以为空的字段)。遍历整个表，读出这个字段，判断不为null累加；
    count(*)。遍历整个表，做了优化，不取值，累加。

结合mysql的一些索引查询知识，我们可以大致得出如下结论。

建议直接使用count(*)。