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

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

前言

前几个月公司要做一个发电站的项目，需要实时采集各个机器的发电用电情况，进行统计展示。初步估计一天产生的数据为 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 还有强大的柔性事务功能，改天我研究下分布式事务，又可以水一篇文章了。

七 统计功能实现

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

八 缓存实现实时查询

要提高查询速度缓存是必不可少的，除了将用户查询过的数据缓存，我们还将近几个小时的所有数据都缓存在 Redis 的 Sorted 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 为值进行分库分表，则在张强查询自己的好友时，需要去全库查找。

3. 数据冗余法
   既然只能以一个字段为值去分库分表，那么我们就做一次数据冗余。在以我的 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 一套的，但是无奈我对这个不太了解，没办法说服组里其他人。有时间再去研究下。毕竟这个场景其实就是另类的日志。