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 命令
命令中返回的 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,按行累加。