MySQL性能调优——count（）篇(二）

count(*)这么慢&＃xff0c;我该怎么办&＃xff1f;

在不同的 MySQL 引擎中&＃xff0c;count(*) 有不同的实现方式。

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

为什么 InnoDB 不跟 MyISAM 一样&＃xff0c;也把数字存起来呢&＃xff1f;

这是因为即使是在同一个时刻的多个查询&＃xff0c;由于多版本并发控制&＃xff08;MVCC&＃xff09;的原因&＃xff0c;InnoDB 表“应该返回多少行”也是不确定的。

InnoDB在count(*)上面是做了一些优化的。

InnoDB 是索引组织表&＃xff0c;主键索引树的叶子节点是数据&＃xff0c;而普通索引树的叶子节点是主键值。所以&＃xff0c;普通索引树比主键索引树小很多。对于 count(*) 这样的操作&＃xff0c;遍历哪个索引树得到的结果逻辑上都是一样的。因此&＃xff0c;MySQL 优化器会找到最小的那棵树来遍历&＃xff0c;count( * )它是不取值的&＃xff0c;它遍历的应该是不为null的小索引树字段。在保证逻辑正确的前提下&＃xff0c;尽量减少扫描的数据量&＃xff0c;是数据库系统设计的通用法则之一。

能不能用show table status 命令中的 TABLE_ROWS 用于显示这个表当前有多少行 &＃xff1f;

我在MySQL为什么会选错索引中说过采样统计(采样统计的介绍)这个概念。实际上&＃xff0c;TABLE_ROWS 就是从这个采样估算得来的&＃xff0c;因此它也很不准。有多不准呢&＃xff0c;官方文档说误差可能达到 40% 到 50%。所以&＃xff0c;show table status 命令显示的行数也不能直接使用。

小结&＃xff1a;

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

怎么解决这个count(*)这么慢的问题&＃xff1f;
把这个计数直接放到数据库里单独的一张计数表中&＃xff0c;InnoDB天生就支持崩溃恢复不丢失数据。

然后InnoDB支持事务&＃xff0c;插入操作和更新行数的操作放在一个事务中。这个解决了原子性问题。

应该先插入操作&＃xff0c;还是更新行数呢&＃xff1f;

这个问题涉及到行锁两阶段锁(两阶段锁内容链接)的知识&＃xff0c;在更新操作的时候才对其加锁&＃xff0c;并不是事务一开始就加上行锁&＃xff0c;语句执行完也并不是马上就释放锁&＃xff0c;会等到事务结束后在释放锁。这个是两阶段锁。将影响并发性能的操作放到事务执行流程靠后的位置&＃xff0c;就可以减少锁住资源的时间&＃xff0c;从而能够提高整体的并发性能。所以&＃xff0c;应该是先插入操作&＃xff0c;后更新计数表的行数。