我们知道,mysql里比较常用的数据表引擎是myisam和innodb,这两种表引擎相信大家在面试题里已经被问到很多了不用我再讲什么大概有以下一些异同。

myisam的介绍  

这里是分隔行

innodb的介绍  

这里又是分隔行

两者的异同

先写重点,之后有时间再补充上面的内容

在我司线上数据库强制要求innodb,事实上在大多数公司innodb也已经是事实标准了。count计数在加上where条件的时候实际上两种表引擎没有什么本质区别因为都要扫全表啊,MySQL大部分的数据都在硬盘上,你是神仙也没辙

所以我们还是得来解决这个count很慢的问题。

先明确一下业务场景:

  1. 某业务表有3kw条数据
  2. 没有可以用的分布式计算平台,想要也没有机器给你用啦哈哈哈哈
  3. 查询条件主要是时间,需要的是实时变化的数据,要求数据准确
  4. 报表页面上有50个左右需要呈现的数据项,因为都是大表的count,且时间范围跨度内数据较多(从几万到千万不起),所以单个报表项计算很多都在秒级,每个都去查询数据库的话会大幅度拖慢数据库性能

本来报表类的需求有通用的静态化解决方案,之前的几个东家在遇到这种问题时也基本都只会要以天为维度的数据。这种需求是很容易满足的(比如每夜crontab,数据塞静态表)。

然而这里的需求是实时count,而且一个报表页有56个数据项。顿时想打死pm了有没有。

回到问题,因为没有办法直接dump线上的数据库,这里先拿测试库的数据来演示:

mysql> select count(1) from service_worksheet;  
+----------+
| count(1) |
+----------+
|  2082047 |
+----------+
1 row in set (0.40 sec)  

看,已经400ms了

接着来加个where

mysql> select count(1) from service_worksheet where allocation = 1;  
+----------+
| count(1) |
+----------+
|  2081952 |
+----------+
1 row in set (2.51 sec)  

直接飙三秒了有没有,线上数据量乘10,一般情况下就是更加夸张了(当然也有人说我们机器好,ssd,大内存,64核cpu,还怕你个count不成)

这里有一个上卷(Roll-up)和下钻(Drill-down)的概念,援引一下知乎某用户的回答:

钻取(Drill-down) 使统计维度降到更细的层级,比如按年统计的指标,降低到按月份统计;
上卷(Roll-up) 则是反过程,比如把细粒度的数据按照稍微大的纬度进行汇总,比如本来北京、天津、河北、山西等省市的细粒维度的数据可以汇总到华北地区这样;

虽然他讲的是数据仓库工具OLAP里的概念,但是我们这里也可以借鉴一下。当然如果你对他的具体讲解感兴趣,可以移步这里:

http://www.zhihu.com/question/19955124

回到上卷的问题,为了应用上卷的概念,我们可以把本来分散到不同时间点去的数据进行一些汇总,因为这里最主要的查询条件是时间,所以我们把散落在不同时间的数据先按照小时来计算总量。

然后将这些以小时维度统计的数据静态化下来到中间表,那么实际上统计实时数据的逻辑就变成了中间加两端的数据之和的模式,举例说明:

计算6:25~9:25的数据,实际上是计算:
6:25~7:00 + 7:00~8:00 + 8:00~9:00 + 9:00~9:25  
的数据之和

计算昨日3:10~今日3:10的数据,实际上是计算:
昨日3:10~昨日4:00 + 中间23小时的数据 + 今日3:00~今日3:10
的数据之和

那么问题就简单了,实际上中间整点的数据我们可以直接在中间表内查询得到,因为最频繁的需求也只会限定在72小时之内,所以从中间表中取出的数据量并不大,再去原始表中拿到时间段两端的数据求和,就可以得到最终的结果了~

这个方案为什么会比直接count要快呢?

原理其实也很简单,减少了MySQL实际要扫描的纪录数,当你对innodb表的单一字段(比如记录的创建时间)建索引之后,只用该字段查询count确实会快很多,但一旦引入了其它的查询条件,就会使你的查询变慢,就算强制使用了较好的索引,还是难以避免大量数据的直接扫描,同时带来大量的磁盘IO,磁盘是万恶之源,容我装个逼,Hardrive is evil

实际需要查询的数据只覆盖1小时的时间段,这一小时内纪录数在大多数应用场景下不会太多。

看起来很美好是不是,我们接着来分析一下这个方案的缺点:

首先,定制性不强,因为我们的中间表一条记录代表通过某种筛选条件之后得到的总count数,这就意味着当我们的筛选条件很多的时候,用这种方案就会产生NNN*N....张中间表。

其次,程序逻辑会变得比较复杂,比如pm提的需求页上有56个指标需要计算,但是这些指标有很多时间段上的重合,为了做进一步的优化,需要对数据做各种姿势的聚会,着实蛋疼。

再次,中间表的数据count更新依赖于业务逻辑,一旦有某条记录状态进行了迁移,就会影响到中间表内的结果,需要重新update。这还没有考虑到之后增加同类的业务代码的时候码农们忘记了去更新中间表而产生的数据不一致的问题。

所以这个方案只能做具体需求的临时开发,不适合成为通用方案。当然了,我们的架构师脑洞大开说可以写成通用的消息通知机制的各种数据更新worker,然而感觉还是会变得异常复杂,而且难以将数据更新逻辑和业务代码解耦,除非不停地扫描数据库。。

之后我会讲更通用的解决方法。