MySQL：如何才能实现高效数据统计

我们在业务中经常遇到的一个场景就是统计当前已有的业务数据，比如说商品库内商品的数量、每天的用户订单数量等等。

这时候，我们一般就需要MySQL的统计功能实现。

1 count(*)实现方式

不同的引擎，count(*)实现逻辑也不一致：

• MyISAM引擎将一个表的总数存在磁盘上，当执行count(*)没有where条件时，直接从磁盘读取数据返回即可，效率比较高；如果是有where条件，则和InnoDB实现逻辑类似；
• InnoDB执行count(*)需要将一行行数据从引擎中读取出来后累积计数；

InnoDB利用多版本控制机制支持事务，一行记录会记录多个MVCC，统计行数这一行为和隔离级别直接相关。在RR级别下，每一行记录都要判断自己是否对这个会话可见，每个会话也会执行增删改操作，导致每个事务统计的行数不一致，因此，对于count(*)来说，InnoDB只好把数据一行行读出来，对可见的行进行统计。因此，InnoDB不能像MyISAM引擎一样在磁盘保存数据行树。

表中，会话C没有显示开始事务，因此每条语句都是独立事务，由于AB会话都没有提交事务，因此，AB的修改对C不可见。

事实上，InnoDB对count(*)做了一定优化，由于InnoDB是索引组织表，主键索引树的叶子节点是数据，普通索引树的叶子阶段是主键值，因此，普通索引树比主键索引树小很多。执行count(*)的逻辑就是遍历，因此，MySQL优化器会选择最小的索引树用于遍历，相对于每次都读取所有的数据行，只是遍历主键，自然IO开销要小的多。

因此说：在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

另外，还有一个显示行数的命令为：

show table status;

• 1.

也会显示表的行数，但是这里的rows是预估值，这个预估值是根据随机采集计算出来的，MySQL随机取N页数据，计算出每页中不同记录数，求取平均值后乘以总页数得到的就是预估值。这个预估值是否接近真实值，取决于索引字段区分度、索引数据页紧凑程度、是否存在页分裂、索引空洞等元素。这个预估值也是造成MySQL选错索引的原因。

2 如何实现计数逻辑

2.1 用缓存系统统计计数

如果是一般场景，使用缓存系统执行计数是满足需求的，即使说，由于redis服务集群异常重启导致数据丢失，但是可以再次扫描一次表获取表的总数。

但是如果是非常严谨的场景（银行统计实际支付的订单数据等），那可能有如下的问题。

第一个是缓存可能会丢失数据，即使是开启持久化，还是存在丢失数据可能性。redis持久化有RDB和AOF两种方式；RDB按照备份策略，比如60秒1000个k-v被修改，备份过程中宕机，那么这个阶段的所有更新都会丢失；AOF按照备份策略，比如 Appendfsync always 策略，同步记录所执行的指令到日志文件，但是它的日志和mysql的WAL不同，它是写后日志，可能指令执行后写日之前宕机，那这个数据就丢失了，虽然丢失数据较少且概率较低，但依然存在这个可能。

第二个是数据一致性保证问题，Redis和MySQL是两个数据源，可以看成是一种分布式一致性的问题，而分布式一致性由于不能保证原子性，因此一般只能保证最终一致性，而不能保证实时一致性。

数据一致性问题目前可分为三类：

1.主从不一。解决办法:半同步复制可以保证实时的一致性，因为写时写主和从之后才响应，只不顾这样写的并发上不去；其他访问有强制读主、消息中间件路由读主和缓存是否失效读主；

2.数据库与缓存的不一。解决办法:读操作直接读缓存,写操作先更新到数据库,淘汰缓存(程序需要保证两个操作的原子性，如果淘汰失败，则发一条小实现异步淘汰).由于该key的缓存已经清理掉,那么下次读的时候需要先读数据库,在重建缓存. 由于redis是单线程,保证了一个操作的原子性.可以通过设置appendfsync always来保证每次操作都把该操作记录并落盘到aof文件里(不过一般redis该值为everysec),毕竟使用redis的目的不是为了保证acid.还是要根据业务来选择 。

3.一个事务跨多个节点或者多种数据库(分库分表和银行转账这种例子) 。目前好像都是通过2pc,3pc来保证的。 

2.2 用数据库保存计数

在数据库中设计单独的计数表，将插入数据、删除数据的SQL和更新计数表的SQL语句作为同一个事务执行。

图片

在统计时，将读取计数器和查询最近数据也作为一个事务执行，这样拿到的就是理论上的实际值。

但是实际上，在高并发场景以及一般场景，这种统计的意义可能并不是很大，因为当你刚刚统计的数据，可能在返回的期间已经有变化。

这时候再次有个问题：在并发系统性能的角度考虑，在事务序列里，是先插入操作记录，还是应该先更新计数表？

答案是：先插入新纪录。

• 因为插入新纪录只会影响到行锁和间隙锁，但是更新计数表会占用计数表的写锁，而很多其他事务的插入操作就必须阻塞等待，即：并发度高的操作放在后面执行，可以减少锁等待；
• 计数表是公用表，根据锁的二阶段协议，在需要的时候获取，在事务提交的时候释放，晚获取可以减少并发，提高吞吐量；

3 不同的count方法

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

• InnoDB存储引擎查询数据结果集；
• Server层根据结果集进行遍历统计；

所以，count(*)、count(1)、count(主键)都表示返回满足条件的结果集的总行数；count(列）表示返回满足条件的数据行里面，参数“字段”不为Null的总个数。

MySQL执行统计的原则是：

• server层要什么就给什么；
• InnoDB只给必要的值；
• 现在的优化器只优化了count(*)的语义为取行数，其他的语句没有做优化。

count(主键)：InnoDB引擎会遍历整张表，把每一行的id取出来（存在数据行数据解析），把主键返回给server层（存在字段值拷贝）。判定id是否为空，不为空直接按行累加即可（这里应该是可以优化的，因为主键一定不允许为空）；同时，count(id)可能会走最小的索引来遍历，并不一定非要走主键索引；

count(1)：InnoDB引擎遍历整张表，但是无需取值。server层对返回的每一行放一个数字1，按行累加；

count(column)：

• 如果字段不允许为空，一行行从记录里面读取这个字段，按行累加；
• 如果字段允许为空，一行行从记录读取字段后，先判定是否为null，如果为null，则过滤掉，不为Null，则累加；
• 如果字段为索引，那么这里的统计就会走该索引；

count(*)：count(*)不取值，按行累加即可；

MySQL文档中有如下说明：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference。

因此，按照效率排序为：count(字段)<count(主键 id)<count(1)=count(*)，所以我建议你，尽量使用 count(*)。

从获取的数值来看，count(字段)也一定是最小的，因为列字段的值可能为null。

4 本章回顾问题

把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:

1. count(*)的实现方式在MySAM引擎和InnoDB引擎的实现方式各是怎么样的? 为什么会有这种不同?
2. 使用缓存保存count总数存在什么问题?
3. 如果使用一场单独的表来记录其他各张表的记录数的话,是怎么解决统计结果不精确的问题的?
4. count(字段),count(id),count(1), count(*)各自是怎么样的执行机制, 效率排序是怎么样的?