count函数的运行机制
问题
在开发系统的时候,你可能需要计算一个表的行数,比如一个交易系统的所有变更记录总数,这时候就会使用select count(*) from t来解决,随着系统记录数越来越多,这些语句执行也会越来越慢,这是个啥原因呢?
count(*)的实现方式
在不同的Mysq存储引擎上,count(*)有不同的实现方式
- MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候直接返回这个数
- InnoDB引擎执行count(*)的时候
需要把数据一行一行的从引擎里读出来
,然后累积计数
当然,如果加了Where条件的话,MyISAM也是不能返回这么快的
为什么InnoDB不跟MyISAM一样,也把数字存起来呢?
- 因为即使是在同一个时刻的多个查询,由于多版本并发控制的原因,InnoDB表
应该返回多少行
也是不确定的
But, InnoDB表肯定不是简单的一行一行的取,由于它是索引组织表,它对count(*)还是有优化的
- 主键索引树的叶子节点是数据,普通索引树的叶子节点是主键ID,InnoDB在做扫描的时候,会选择最小的索引树进行扫描
TABLE_ROWS
在mysql中使用如下命令可以有一个返回值TABLE_ROWS(或者ROWS)用于显示当前这个表有多少行,这个命令执行的还挺快的
mysql> show table status;
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| SUser | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-08-08 22:14:39 | NULL | NULL | latin1_swedish_ci | NULL | | |
| t | InnoDB | 10 | Dynamic | 10122 | 35 | 360448 | 0 | 327680 | 0 | NULL | 2020-08-10 22:20:47 | NULL | NULL | latin1_swedish_ci | NULL | | |
| user | InnoDB | 10 | Dynamic | 11 | 1489 | 16384 | 0 | 0 | 0 | 1290839374338056202 | 2020-08-08 09:22:10 | NULL | NULL | latin1_swedish_ci | NULL | | |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
3 rows in set (0.00 sec)
所以,能够使用show table status
来代替count(*)吗?
- 不能,因为TABLE_ROWS是通过采样来估算的,官方文档说误差可能达到40%或者50%
因此,靠MYSQL系统是不太可能的,我们需要自己研究计数
自己计数的方式
用缓存系统保存计数
这种情况是不准确的:
- 将计数保存在缓存系统中的方式,还不只是丢失更新的问题,即使Redis正常工作,这个值还是逻辑上不精确的(最好别使)
在数据库保存计数
- 在数据库中创建一张数据表专门用来count
- 我们可以利用事务的特性和InnoDB的WAL机制保证数据的一致性和可靠性
不同的count
- count()是一个聚合函数,对于返回的结果集,一行行的判断,如果count函数的参数不是null,累计值就加1,否则不加,最后返回累计值
- 所以
count(*),count(主键ID),count(1),
都表示返回满足条件的结果集的总行数 - count(字段)都表示返回满足条件的数据行里面,不为null的总行数
- 所以
现在的优化器只是给count(*)做了取行数的优化,其他的count都会进行判断,大大的耗时
count深度解析
- 对于count(主键ID)来说,InnoDB引擎会遍历整张表,把每一行的id给取出来,返回给Server层,Server层拿到后,判断是不可能为空的,就按行累加
- 对于count(1)来说,InnoDB引擎遍历整张表,但不取值,server层对于返回的每一行,放一个数字1进去,判断是不可能为空的,按行累加
- 对于count(字段)来说:
- 如果这个“字段”是定义为not null的话,一行行的从记录里面读出来这个字段,判断不能为null,按行累加
- 如果这个“字段”定义允许为null,那么执行的时候,判断到有可能是null,还要把值取出来再判断一下,不是null才累加
按照效率排序
count(字段)< count(主键id) < count(1) ~ count(*)
所以我们在取行数是要尽量使用count(*)