Mysql COUNT(*)的原理和实现

Mysql实战45讲09：https://time.geekbang.org/column/article/72775

以下是自己的理解，如有不对请指正

实现方式

不同搜索引擎不同：

• MyISAM把表的总行数维护在磁盘上，需要时直接返回。

  但是如果带where条件，则就不能直接返回了

• InnoDB需要一行一行累加，所以数据越来越多则速度越来越慢。

为什么InnoDB不直接维护

• 因为InnoDB支持多版本并发控制(MVCC)，同一时间返回的COUNT(*)也是不确定的。
• 比如A,B开启两个事务，A插入1行B插入两行，然后同时执行COUNT(*)，此时得到的结果是不同的。

InnoDB做的优化

• InnoDB是索引组织表，主键索引的叶子节点是数据，而普通索引的叶子节点是主键值，所以普通索引树比主键索引树小很多。
• 对于COUNT(*)来说，遍历哪颗树都一样，所以mysql优化器会选择最小的树进行遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

其他方式

SHOW TABLE STATUS命令中有个字段TABLE_ROWS，表示行数，但是是估计值，不准确（误差40%-50%）。

上述情况

• MyISAM返回快，但是不支持事务。
• TABLE_ROWS返回值不准确。
• InnoDB返回值准确，但是性能会随着数据增长越来越差。

自己计数

在数据库中单独保存，利用事务特性保持数据准确性。

其他问题

• count(*)、count(id)、count(字段)和count(1)的区别
  count()是聚集函数，对于返回集一行行判断，不为NULL则加1。
• 所以count(*)、count(id)和count(1)都表示符合条件结果集的函数。而count(字段)表示该列值不为NULL的数量。

具体区别

• count(id)：innodb引擎会扫描整个表，取出id返回给server层，server判断判断不可能为NULL，累计得出数量。
• count(1)：innodb引擎会扫描整个表，但不取数据。server层对于每一行放个1进去，判断不可能为NULL，逐行累计。
• count(字段)：若该字段声明为NOT NULL 则和count(id)相同。若可以为NULL，则server层还要判断每一行的值，不为NULL的进行累计。
• count(*)：专门做了优化，不取值，判断不可能为NULL，逐行累计。
• 性能：count(字段)<count(id)<count(1)≈count(*)