count()这么慢,我们该怎么办

1. count(*)的实现方式

对于不同的MySQL存储引擎,count(*)有不同的实现方式。

链表

  1. MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,执行效率很高。
  2. InnDB引擎在执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后进行计数。

那么为什么InnoDB不像MyISAM一样,把数字存起来,直接读取呢?
这主要是因为MyISAM不支持并发版本控制,而InnoDB则支持MVCC导致的。
例如,假设表t中现在用10000条记录,目前有三个会话执行:

数据库架构

  1. 会话A先启动事务并查询一次表的总行数
  2. 会话B启动事务,插入一条记录后,查询表的总行数
  3. 会话C先启动一个单独的语句,插入一行记录后,查询表的总行数。

执行顺序如下图所示:
在这里插入图片描述

python爬虫

可以看到,三个会话看到的总行数是不一样的。因为InnoDB默认隔离级别是可重复读,所以每一行记录都要判断是否对于这个会话可见,因此就只能一行一行进行判断,计算行数。

LVGL

但是实际上,InnDB还是对count(*)做了一定的优化的。因为InnoDB的索引树是一颗B+树,主键索引的叶子节点存放的是该行的数据,普通索引的叶子节点则存储的是主键值。所以InnoDB会选择最小的那颗树来进行遍历,因为其实结果都是一样的。

信息科学技术与创新

此外,可能你会发现,当使用show table status命令时,有个TABLE_ROWS字段会显示这个表有多少行,但是之前我们介绍过,这个字段其实是不准确的,最大有50%的误差。

浏览器内核

那我们应该如何优化这个count()命令呢?

硬件工程

2. 用缓存系统保存计数

对于更新很频繁的库,可能会很容易想到使用Redis缓存。首先使用Redis保存原始行数,当插入行则+1,删除行则-1。但是Redis可能会存在丢失更新的问题,假如Redis宕机了,就可能会出现和数据库不一致的情况,就需要去重新读一遍。

微信授权

此外,Redis还可能会出现数据不精确的情况,无论是先写Redis,还是先写数据库都会出现Redis和数据库不一致的情况。

元宇宙

3. 在数据库保存计数

可行的一种方法是把这个计数直接放到数据库里单独的一张计数表C中

mysql 事务总结

首先,这解决了崩溃丢失更新的问题,InnoDB是支持崩溃恢复不丢失数据的。

阻塞赋值

其次,由于InnoDB是支持MVCC的,所以就可以利用事务进行处理。如下图所示:
在这里插入图片描述

工作流

在T3时刻,由于会话A还没有提交,所以计数器+1这个操作对于会话B是不可见的。这就保证了T表和C表的一致性

结构体

4. 不同count的比较

首先,我们要明白,count()是一个数和函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则就不加。最后返回累计值。

zhaoyanjun

所以count(*),count(字段),count(主键id)和count(1)都表示满足条件的总行数。也就是不为NULL的个数。

驼峰命名法

我们要记住以下几个原则:

java人生

  1. server层要什么就给什么
  2. InnoDB只给必要的值
  3. 现在的优化器只对count(*)做了优化。

对于count(主键id)来说,InnoDB会遍历整张表,把每一行的id都取出来,返回给server层。server层拿到id后,判断出主键id不可能为空,则直接计算总行数。

vant

对于count(1)来说,InnoDB会遍历整张表,会对返回的每一行放入1,判断出1不可能为空,则直接计算总行数。

freertos

对于count(字段)来说,如果这个字段是定义为not null,则一行行取出来,判断出该字段不可能为空,则直接计算总行数。如果这个字段是定义为允许null,则首先要把非null的行给server,server再进行二次判断,计算不为空的总行数。

职场和发展

对于count(*)来说,也不需要取字段,并且做了专门的优化,count(*)肯定不为null,直接计算总行数即可。

捕鱼达人

显而易见,count(字段)是最耗时的。由于count(主键id)相比于count(1)需要涉及到拷贝字段,所以效率肯定没count(1)高。而count(*)不需要判断是否null,则是效率最高的。

zabbix

因此,按照效率排的话,count(字段) < count(主键) < count(1)

\approx

count(*)

栈内存越界

来源:自己整理的MySQL实战45讲笔记

广州地铁

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注