当我需要一个表的行数,可以使用这样的语句:

select count(*) from t

但是随着记录数越来越多,它会变慢。

count(*)实现方式

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

  • MyISAM引擎把一个表的总数存在磁盘上,执行时会直接返回
  • InnoDB需要把数据一行一行地从引擎里面读出来,然后累积计数

那为什么InnoDB不像MyISAM一样把总数存起来呢?

因为MVVC,InnoDB表“应该返回多少行”是不确定的。

经下面各会话为例,表t初始有10000条数据,最后查询结果会是多少呢?

会话A会话B会话C
begin;
select count(*) from t
insert into t(插入一行);
begin;
select count(*) from t
返回10000返回10002返回10001

这和InnoDB事务有关系,它默认的隔离级别是可重复读,在代码上通过MVVC控制,因此,每条记录都要判断是否对自己这个会话可见。InnoDB只好把数据一行一行读出,依次判断。

虽然看着有、笨,但是MySQL还是有优化的!

要知道,InnoDB是索引组织表,主键索引树的叶子结点是数据,而普通索引树叶子节点是主键值。所以,普通索引数比较小,对于count(*)这样的操作,遍历哪颗索引树都是一样的,在保证逻辑正确的前提下,尽量减少扫描的数据量,MySQL会找到最小的树遍历。

我该怎么计数

比如我有一个需求,要显示操作记录的总数,同时还要显示最近操作的100条记录。

使用缓存系统保存计数

用redis来存总行数,表每次被插入一行,redis就加1.

这有什么问题?

  • 缓存系统可能丢失更新

    redis不能永久保存在内存中,所以需要找个地方把值持久化存储。比如刚保存计数+1,然后异常重启了,重启后这条计数丢失了。当然,这还是有解的,每次重启都从数据库中取一次真实值,毕竟不是经常取,这成本还是可以接受的。

  • 逻辑不一致

    这是一个事务问题,计数分为两步:插入数据和redis+1,当我在这两步中间进行查询时,逻辑就不一致了。

    时刻会话A会话B
    T1
    T2Redis + 1
    T3 读redis计数;查最近100条记录
    T4插入1条数据
    T5

在数据库中保存计数

把计数单独放到数据库中的计数表中,这种做法怎样?

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

然后,它的事务能够处理上述的逻辑不一致问题,很赞。

讲讲各种count(xxx)

比如count(*)、count(主键 id) 、 count(1)。。。

count()是一个聚合函数,对于返回的结果集,一行一行判断,如果count函数的参数不为NULL,累计+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(字段),如果字段的定义是not null,一行一行读出里面的字段,判断不能为null,累加;若其能够为null,要执行时,还要把它取出来判断一下,不是null才累加。(也就是原则里面的server层要什么,InnoDB就返回什么字段)

但是,count(*)是例外,它被专门优化,不取值,直接累加!

因此,从结论来说,他们的效率比较是:count(字段)<<count(id)<count(1) ≈count(*)

人家都替你优化好了,直接用count(*)就是了!