🪴 Obsidian Publish

MySQL count逻辑

6分钟阅读

InnoDB count* 是将数据一行行取到内存进行计算，一个原因是每个事务的可看到数目不同。

count(*) ≈ count(1) > count(主键 id) > count(字段)，建议尽量使用 count(*)

如果一定要有一个计数表，记录行数，那么 update 行数的操作放到这个事务的最后做，因为涉及到计数表行锁的竞争情况。

count(*) 的实现方式

你首先要明确的是，在不同的 InnoDB 引擎 中，count(*)有不同的实现方式。

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*)的时候会直接返回这个数，效率很高；
  • 而 InnoDB 引擎就麻烦了，它执行 count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB 不存储 —— MVCC 的原因。多个事务一起操作数据库的时候，每个人看到的行数可能是不一样的。

MySQL 整理-5.png|600

如果你现在有一个页面经常要显示交易系统的操作记录总数，到底应该怎么办呢？需要将 count(*) 数值存起来。

  1. Redis。不可行，最主要还是和 DB 的更新顺序问题。
  2. 数据库表。可行，利用事务，一次访问两个数据库。

MySQL 整理-6.png|600

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

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

对于 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，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键 id 肯定非空啊，为什么不能按照 count(*)来处理，多么简单的优化啊。

当然，MySQL 专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且 MySQL 已经优化过 count(*)了，你直接使用这种用法就可以了。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

技术表记录 count 数目

用一个计数表记录一个业务表的总行数，在往业务表插入数据的时候，需要给计数值加 1。

逻辑实现上是启动一个事务，执行两个语句：

  1. insert into 数据表；
  2. update 计数表，计数值加 1。

从系统并发能力的角度考虑，怎么安排这两个语句的顺序。

从并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。

因为更新计数表涉及到行锁 的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。

事务开启后，更新操作放到最后。较少锁等待时间的影响。

技术表可以设计如下，这样多个数据表更新时，只占有行锁：

CREATE TABLE `rows_stat` (
  `table_name` varchar(64) NOT NULL,
  `row_count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`table_name`)
) ENGINE=InnoDB;

Related：行锁

反向链接