MySQL浅析之count函数(5)

MySQL浅析之count函数

1. 前言

我们在日常开发中可能会经常需要计算一个表中的行数，而执行SQL语句也挺简单：

select count(*) from table;

但是当我们表中数据量过大时，不难发现，执行一次这个SQL语句，需要消耗大量时间。

那么，MySQL是如何执行这个SQL语句的呢？

2. count(*) 执行过程

首先我们需要知道在MyISAM引擎中，它是把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；

而对于InnoDB引擎，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

我们知道，平常我们基本都是使用InnoDB引擎，那么问题来了，为什么InnoDB引擎不像MyISAM引擎一样，将表的总行数放在磁盘上呢？

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的。

当然我们也可以通过

show table status

命令来获取表中的行数，这个命令执行挺快的，但是他是会有误差的，并不能精准获取多少行。

最后我们能够得到结论：

• MyISAM表虽然count(*)很快，但是不支持事务；
• show table status命令虽然返回很快，但是不准确；
• InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

了解了这些后，那么当数据行过大时，我们该如何解决获取行数慢的问题呢？

有一个基本方法是：找一个地方，把操作记录表的行数存起来。

3. 缓存系统保存计数

看到标题，应该能够明白。我们可以使用Redis服务来保存这个表的总行数。这个表每被插入一行Redis计数就加1，每被删除一行Redis计数就减1。

当然，使用Redis缓存也是存在问题的：

1. 缓存系统可能会丢失更新
2. Redis缓存与数据库不一致

第一个问题好解决，独执行一次count(*)获取真实的行数，再把这个值写回到Redis里就可以了。异常重启毕竟不是经常出现的情况，这一次全表扫描的成本，还是可以接受的。但是第二个问题就无解了。

我们在使用中就会存在这两种情况：

假如有这么一个页面，要显示操作记录的总数，同时还要显示最近操作的100条记录。那么，这个页面的逻辑就需要先到Redis里面取出计数，再到数据表里面取数据记录。

那么就会出现一下两种问题：

1. 查到的100行结果里面有最新插入记录，而Redis的计数里还没加1
2. 查到的100行结果里没有最新插入的记录，而Redis的计数里已经加了1

时刻	事务A	事务B
T1
T2	插入一行数据
T3		读Redis计数，查询最近100条记录
T4	Redis计数加1

图中，事务A是一个插入交易记录的逻辑，往数据表里插入一行数据，然后Redis计数加1；事务B就是查询页面显示时需要的数据。

在图的这个时序里，在T3时刻事务B来查询的时候，会显示出新插入的R这个记录，但是Redis的计数还没加1。这时候，就会出现我们说的数据不一致。

在并发系统里面，我们是无法精确控制不同线程的执行时刻的，所以，我们说即使Redis正常工作，这个计数值还是逻辑上不精确的。

4. 在数据库保存计数

我们可以把这个计数直接放到数据库里单独的一张计数表中

首先通过这个方法解决了数据丢失问题，毕竟它是存放在数据库中，而不是缓存当中。

那么对于数据精度问题能否解决呢？答案是可以的。

时刻	事务A	事务B
T1
T2	begin；表中计数加1
T3		begin；读Redis计数，查询最近100条记录；commit；
T4	插入一行数据；commit；

我们来看下现在的执行结果。虽然事务B的读操作仍然是在T3执行的，但是因为这时候更新事务还没有提交，所以计数值加1这个操作对事务B还不可见。

因此，事务B看到的结果里， 查计数值和“最近100条记录”看到的结果，逻辑上就是一致的。

5. count用法

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

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

单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(字段)来说：

1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。

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

结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)

因此，我们尽量使用count(*)来进行数据行的计数。

6. 小结

我们了解了count函数的执行过程，以及数据行过多时的解决方案，最后还介绍了count函数不同用法的查询速度。

最后得出结论：当我们日常工作需要统计表中行数时，尽量使用count(*)来获取行数。其次，通过额外加表来存放行数的方法，来解决当表中数据行过大时，查询缓慢的问题。