"亿级数据存储实现 + 分库分表优化方案 [图片] 前言 前几个月公司要做一个发电站的项目,需要实时采集各个机器的发电用电情况,进行统计展示。初步估计一天产生的数据为 4000W 左右。于是和组长一起讨论设计了数据库方案。下面说下我们的解决思路,如果有不足,需要改进的地方,欢迎提出(因为这也是我第一次做分库分表╮( ̄▽ ̄ .."

亿级数据存储实现 + 分库分表优化方案

亿级数据存储实现 + 分库分表优化方案

前言

前几个月公司要做一个发电站的项目,需要实时采集各个机器的发电用电情况,进行统计展示。初步估计一天产生的数据为 4000W 左右。于是和组长一起讨论设计了数据库方案。下面说下我们的解决思路,如果有不足,需要改进的地方,欢迎提出(因为这也是我第一次做分库分表╮( ̄▽ ̄")╭)。

一 基本情况

数据是从 2000 多个节点分别采集输入到系统中,大约每 5s 采集一次,一天数据量在 4000W 到 5000W 之间。
数据从采集系统采集后,放入Kafka队列,我们的消费服务不停的从队列中取出数据,推送给系统主体。
最少数据库中要存放
保留上个月的历史数据,再往前的历史数据基本很少会用到,直接备份起来就好。也就是说最多数据库会存在 24 亿的数据。

二 建索引

数据主要用来统计,因此将关键字段加上索引。使用的每条查询SQL,都最好explain一下,确保使用了索引。

三 分表

MySQL的推荐单表不超过 500W 条数据(含 char 等字符),或者不超过 1000W 数据(纯 int 等数字),当然网上有很多操作让单表达到几千万级、亿级。但是我们既然都提前知道了数据量大,而且鬼知道发电厂会不会加新设备进来(我赌 5 毛,肯定会加),那么最好把单表数据量不要超过 1000W,因为单表数据量超过 1000W 以后会出现明显的瓶颈。

四 分库

5s 采集一次数据,平均Kafka里每秒会增加 400 多个数据,我们可以每秒批量写一次数据,并发量并不大。(分库主要为了解决并发量大的问题)

但是考虑到数据量大了以后,对机器的 CPU 和内存会造成很大的压力,并且逻辑上也是两套发电机组,因此分了两个库。

再加上一个存放其他数据的数据库,一共 3 个写库。
现在是频繁写入的场景,因此又加了 3 个读库,做主从分离,并且 3 个读库也能当做备份库使用。

一共使用了 6 个库。

五 分表分库

经过大概的估算,分成 2 个库,每个库分成 160 张表,一共 320 张表。一张表数据最多时会达到 750W(考虑到我们的表字段数不超过 10 个,并且对查询操作的实时性要求不是特别高,没有严格准守不超过 500W 条)

六 分表分库实践

分表分库中间件,我们选择了张亮大大的Sharding-JDBC,原因很简单,其他中间件的很多功能我们都不需要,而且Sharding-JDBC的使用非常简单。

根据ShardingSphere 使用手册,配置好分表分库策略,分库策略根据机组 ID(之前说过总共有两套机组),分表策略是自定义的策略,首先根据设备的编号进行划分范围,如每个设备划分 10 张表,那么根据设备编号 hash 出来值为 1,则设备放在表 1 到表 10 之间,然后根据主键 ID 再一次 hash,落到具体的某张表上。这样比较方便后续的查询操作。

分库分表有很多算法,最最实用的就是 hash 法,就是 hash 后取模。

并且用Sharding-JDBC自带的分布式 ID 生成算法,workId 用了主机名hostname。分布式 ID 生成算法有兴趣的 key 参看我的文章——数据库主键生成策略选择

Sharding-JDBC还有强大的柔性事务功能,改天我研究下分布式事务,又可以水一篇文章了。

七 统计功能实现

因为主要是做统计系统,那么必然需要统计各种报表,主要有小时报表,日报表,周报表,月报表,年报表。目前我们的做法是将各种统计表的信息放在单独的库中,并且新建了几十个表用来保存统计出的结果。
后台起一个线程,每次入库成功后,都会把数据传入该线程。该线程会取出对应的本小时报表,然后重新计算一遍。
并且在数据库里写了几个定时任务,如每天凌晨统计前一天小时报表生成日报表,每月生成月报表......

八 缓存实现实时查询

要提高查询速度缓存是必不可少的,除了将用户查询过的数据缓存,我们还将近几个小时的所有数据都缓存在RedisSorted Set里。因为用户需要精确查看的数据,往往也就是最近几个小时产生的。通过Sorted Set以时间戳为Score,可以迅速的查出来一段范围内的数据进行展示。

九 其他的优化方案

主要有三种方案

  1. 映射表
  2. 基因法
  3. 数据冗余法

推荐基因法和数据冗余法,下面来看看使用场景。

9.1 多维度查询

一张用户表,我们一般会按照 user_id 来分库分表,但是用户查询的时候经常是按照 user_name 或者 email 来查询的。这时候如果直接进行全库扫描肯定是很慢的。

  1. 映射表
    建立一张映射表,映射了 user_name 到 user_id 的关系,查询时直接查一次映射表即可。因为映射表很小,还可以提前加载到缓存中。
    缺点:多了一次查询。
  2. 基因法
    网上看到大神分享的一个方法,一般分库分表都是取模算法,如:user_id%8,可以看出实际的取模结果完全取决于 user_id 的后 3 个 bit(因为 8 的二进制为 1000),那么我们可以改用 user_name 来生成 user_id 的后 3 个 bit,即 user_id= 随机数 +f(user_name)。这样我们在分库分表查找时,只需要重新计算一遍搜索 user_name 的哈希值即可定位到具体库或表
    缺点:基因只能关联一个字段。

9.2 一对多查询

常见的一个场景:根据用户信息,查询该用户的消费记录。用户 ID 与消费记录 ID 是明显的一对多关系。
这时候怎么通过用户信息查到对应被分库分表的消费记录呢——很明显!还是采用映射表或者基因法。

9.3 多对多查询

QQ 上任何人之间都可能存在好友关系,那么这就是明显的多对多关系了。正常情况下我们存一条好友关系只需要一行,如:我和张强是好友,则插入一条数据,上面包含了我的 user_id 和张强的 user_id。但是考虑到分库分表的情况下,只能使用一个信息进行分库分表,即以我的 user_id 为值进行分库分表,则在张强查询自己的好友时,需要去全库查找。

  1. 数据冗余法
    既然只能以一个字段为值去分库分表,那么我们就做一次数据冗余。在以我的 user_id 为值分库分表插入后,再以张强的 user_id 为值分库分表插入一次。
    在查询的时候不管是使用我的 user_id 还是张强的 user_id 都能顺利的查到对应库和表。
    数据冗余法为了保证系统的可用性和实时性。最好使用异步的冗余插入,并且用分布式事务的最终一致性保证异步插入成功。

9.4 多对多查询 + 主键查询

后端开发中利用主键查询数据是必不可少的,比如典型的订单表,有 order_id,buyer_id,seller_id。怎么办呢,很简单结合基因法与数据冗余法即可。
以 buyer_id 为基因生成 order_id 插入一次,再以 seller_id 为基因生成 order_id 插入一次。那么不管从哪个维度查询,都能直接定位到库和表。

总结

对于大数据量的查询存储,往往就是以下几个步骤:

  1. 优化表和索引,提高 SQL 速度
  2. 使用缓存,推荐 Redis
  3. 读写分离提高查询速度
  4. 分表提高单表查写性能
  5. 分库提高并发量,并且降低 CPU, 内存的压力。业务耦合度不高时使用。
  6. 分库分表时要考虑到多种可能的查询。

听别人提起这种业务好像可以直接上 ELK 一套的,但是无奈我对这个不太了解,没办法说服组里其他人。有时间再去研究下。毕竟这个场景其实就是另类的日志。

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    233 引用 • 522 回帖 • 2 关注
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    417 引用 • 459 回帖 • 957 关注
  • 解决方案
    1 引用 • 4 回帖
4 回帖   
请输入回帖内容...
  • tiangao  

    这种感觉不需要用 mysql

    1 回复
  • EricTao2        

    那用什么比较好呢

  • tiangao  

    Hive

  • MistRay  

    我这边也用的是 sharding-JDBC。在 3.0 版本的自定义 between-and 策略中,range 范围内只有一张表的话,会产生很多奇奇怪怪的 bug,据说后面的版本修复了😂