《MySQL实战45讲》——学习笔记14 “count(*)的原理、与count(1)/count(id)的区别“

港控/mmm° 2023-09-23 14:20 51阅读 0赞

本篇介绍了MySQL中获得表行数的方法——count语句，以及在不同引擎中count(\*)的实现方式的区别；分别分析了使用缓存和MySQL单独存储来获得表行数的方案，以及比较了不同的count用法——count(字段)/count(主键 id)/count(1)/count(\*) 在性能上的区别；

#### count(\*) 的实现方式 ####

在不同的MySQL引擎中，count(\*)有不同的实现方式：

*  MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(\*)的时候会直接返回这个数，效率很高；需要注意的是，如果加了where条件的话，MyISAM表也是不能返回得这么快的；
 *  InnoDB需要把数据**一行一行地从引擎里面读出来，然后累积计数**；这就是当记录数越来越多的时候，计算一个表的总行数会越来越慢的原因；

**为什么InnoDB不跟MyISAM一样，也把数字存起来呢？**——因为即使是在同一个时刻的多个查询，**由于版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的**；举个例子，假设表t中现在有10000条记录，有三个并行的会话：

*  会话A先启动事务并查询一次表的总行数；
 *  会话B启动事务，插入一行后记录后，查询表的总行数；
 *  会话C先启动一个单独的语句，插入一行记录后，查询表的总行数；

假设从上到下是按照时间顺序执行的，同一行语句是在同一时刻执行的，结果如下：

![3bf9397c774a4d3795e4aa78895b4b19.png][]

A先开启事务，因此B和C的插入对A不可见，A读到10000条；B开启事务在C的插入语句提交之后，因此可见，并且B事务内的更新可见，因此B读到10002条；C查询行数时，B的事务未提交，因此C读到10001条；

> 这和InnoDB的事务设计有关系，可重复读是它默认的隔离级别，通过MVCC来实现的；**每一行记录都要判断自己是否对这个会话可见**，因此对于count(\*)请求来说，**InnoDB只好把数据一行一行地读出依次判断**，可见的行才能够用于计算“基于这个查询”的表的总行数；

#### MySQL执行count(\*) 操作时做的优化 ####

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

InnoDB是索引组织表，主键索引树的叶子节点是完整的数据，而普通索引树的叶子节点是主键值；所以，普通索引树的存储大小比主键索引树小很多；考虑到数据数量和主键数量是一样的，因此无论遍历主键索引树还是二级索引，得到的结果逻辑上都是一样的；**因此，MySQL优化器会找到最小的那棵索引树来遍历**；

前面[《MySQL实战45讲》——学习笔记10 “优化器如何选索引、选错索引“][MySQL_45_10]中讲过 **show table status 命令**可以返回全表的行数rows，这个值能直接用做全表count(\*)的结果吗？

——不能，**show table status 命令**得到的 rows 是预估值，这个预估值是根据随机采样法计算出来的，MySQL会随机取N页数据，计算出每页中不同的记录数，合起来求每页的平均值，最后乘以总页数得到的就是预估值，不是扫描所有的行计算的；这个预估值是否接近真实值取决于索引字段的区分度和索引数据页是否紧凑，如果存在也分裂、索引空洞都会影响预估值的准确性；

小结一下：

*  MyISAM 表虽然 count(\*) 很快，但是不支持事务和条件查询；
 *  show table status 命令虽然返回很快，但是是基于随机采样，值不准确；
 *  InnoDB 表直接 count(\*) 会遍历全表，虽然结果准确，但会随数据量增长，引起性能问题；

#### 获得表行数的方案 ####

个人理解，这其实是个业务问题，如秒杀库存的场景；根据对数据准确性的要求不同，也有不同方案：（1）性能要求非常高，可以使用缓存，如redis；（2）对数据准确性要求非常高，强事务属性，只能使用MySQL冗余，保证事务的强一致性；

*  **使用缓存**

用一个Redis服务来保存这个表的总行数；这个表每被插入一行Redis计数就加1，每被删除一行Redis计数就减1；这种方式下，读和更新操作都很快；问题也很明显：

**（1）缓存失效**

**解决方案**：可以通过定时任务主动刷缓存，或者懒加载的方式将数据库中的count(\*)结果重新刷如缓存；也可以使用常驻缓存，即不设置TTL；

**（2）无法保证事务**

MySQL插入失败或Redis的incr命令失败都可能发生，因此即使Redis正常工作，这个值还是逻辑上还是可能不精确；

**解决方案**：这个问题我在设计一个"库存扣减"场景时遇到过，使用的方案很简单，只要MYSQL事务执行成功就认为业务执行成功，接下来就是通过消息异步的去修正redis的库存值就行了，当然库存的扣减还是要预留一些buffer，原则是避免超卖，哪怕牺牲用户体验；

*  **使用MySQL**

把这个计数直接放到数据库里单独的一张计数表C中；

首先，这解决了崩溃丢失的问题，InnoDB是支持崩溃恢复不丢数据的；其次，由于innoDB对事务的支持，也解决计数不精确的问题，即将对计数的更新和插入操作放在一个事务；如下示例：

![538193b6fd9c4627967401ff233846b9.png][]

问题：执行两个语句：insertinto数据表；update计数表，计数值加1；从系统并发能力的角度考虑，怎么安排这两个语句的顺序？

——**从并发系统性能的角度考虑，应该先插入操作记录，再更新计数表**；因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少事务之间的锁等待，提升并发度；

#### 不同的count用法 ####

> 基于InnoDB引擎下，在 select count(?) from t 这样的查询语句里面，count(\*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差别？

首先要弄清楚count()函数的语义：count()是一个聚合函数，对于返回的结果集，一行行地判断，**如果count函数的参数不是NULL，累计值就加1，否则不加**；最后返回累计值；

所以，count(\*)、count(主键id)和count(1)都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，**参数"字段"不为NULL的总个数**；

性能分析，先给结论：

> **按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(\*)**；因此，建议使用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才累加；

*  **count(\*)**

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

看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照count(\*)来处理，多么简单的优化啊；当然，MySQL专门针对这个语句进行优化，也不是不可以；但是这种需要专门优化的情况太多了，而且MySQL已经优化过count(\*)了，直接使用这种用法就可以了；

下篇文章：[【建议收藏】《MySQL实战45讲》——学习笔记15 “redo log与bin log答疑“总结][MySQL_45_15 _redo log_bin log]

本章参考：[14 | count(\*)这么慢，我该怎么办？-极客时间][14 _ count_-]

[3bf9397c774a4d3795e4aa78895b4b19.png]: https://img-blog.csdnimg.cn/3bf9397c774a4d3795e4aa78895b4b19.png
[MySQL_45_10]: https://blog.csdn.net/minghao0508/article/details/127415469
[538193b6fd9c4627967401ff233846b9.png]: https://img-blog.csdnimg.cn/538193b6fd9c4627967401ff233846b9.png
[MySQL_45_15 _redo log_bin log]: https://blog.csdn.net/minghao0508/article/details/127763412
[14 _ count_-]: https://time.geekbang.org/column/article/72775