14 | count(*)这么慢,我该怎么办?

一下内容出自《MySQL 实战 45 讲》

14 | count(*)这么慢,我该怎么办?

count(*) 的实现方式

不同的 MySQL 引擎中,count(*) 有不同的实现方式。

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

为什么 InnoDB 不和 MyISAM 一样,也把数字存起来

即使是在同一时刻的查询,因为 MVCC 的原因,InnoDB 表 “应该返回多少行” 也是不确定的。

InnoDB 在执行 count(*) 操作的优化。

InnoDB 是索引组织表,主键索引树到的叶子节点是数据,而普通索引树的叶子节点是主键值。所以,普通索引树比主键索引树小的多。对于 count(*) , MySQL 优化器会找到最小的那棵树来遍历保证逻辑正确的情况下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一

show table status 命令

image

命令中返回的 Rows 能代替 count(*) 吗?

这个值是采样估算来的,并不准确,官方说误差可能达到 45%~50%。

如何快速获取到表的行数?

从上面的可以得出:

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

如果现在有一个页面经常要显示交易系统的操作记录总数,到底应该怎么办呢?答案是,只能自己计数。

  • 用缓存系统计数
    • 问题:丢失更新(崩溃丢失)、值逻辑上不正确,因为插入数据库和修改内存值并不是原子性的(分布式一致性问题)
  • 数据库保存计数
    • 可以解决崩溃丢失,InnoDB 是支持崩溃恢复不丢数据的。
    • 利用事务,可以保证值逻辑上的一致性。

总结:

​ 不同的存储构成的系统,不支持分布式事务,无法拿到精确一致的视图

​ 把计数值也放在 MySQL 中,就解决了一致性视图的问题。

不同 count 用法

先说结论:按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*)

分析性能差别的原则:

1、server 层要什么就给什么;

2、InnoDB 只给必要的值;

3、现在的优化器只优化了 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(*) 肯定不是 null,按行累加。

posted @ 2023-07-02 12:17  LionelYee  阅读(52)  评论(0编辑  收藏  举报