《数据存储》之《分库，分表》

序言

这段时间有很多人问我关于数据量大了，数据库如何去满足写入和查询速度；

有没有做过分库分表结构算法；

其实在之前的文章也写过类似的文章《net.sz.framework 框架 ORM 消消乐超过亿条数据排行榜分析 天王盖地虎》

当时开心消消乐非常盛兴所以写了一个简单的分析，其实不一定符合规范，或者符合具体业务需求

只能说是抛砖引玉吧，也希望得到大家更好的推荐；

注解一

我们在开始今天的情况之前，先来看一下在我的架构下处理数据问题

由于这台电脑问题，没有装mysql，我们就用sqlite代替；因为其实主要是介绍分库分表的思路；

 想创建一下测试模型代码类

package com.db.test;

import com.ty.tools.db.struct.DbColumn;
import com.ty.tools.db.struct.DbModel;
import com.ty.tools.db.struct.DbTable;

/**
 * @author: Troy.Chen(失足程序员, 15388152619)
 * @version: 2021-07-20 10:36
 **/
@DbTable
public class RoleLoginLog extends DbModel {

    @DbColumn(key = true, comment = "主键id")
    private long id;
    @DbColumn(index = true, comment = "日志记录时间")
    private long logTime;
    @DbColumn(index = true)
    private long userId;
    @DbColumn(index = true)
    private long roleId;
    @DbColumn(index = true)
    private String roleName;

    public long getId() {
        return id;
    }

    public RoleLoginLog setId(long id) {
        this.id = id;
        return this;
    }

    public long getLogTime() {
        return logTime;
    }

    public RoleLoginLog setLogTime(long logTime) {
        this.logTime = logTime;
        return this;
    }

    public long getUserId() {
        return userId;
    }

    public RoleLoginLog setUserId(long userId) {
        this.userId = userId;
        return this;
    }

    public long getRoleId() {
        return roleId;
    }

    public RoleLoginLog setRoleId(long roleId) {
        this.roleId = roleId;
        return this;
    }

    public String getRoleName() {
        return roleName;
    }

    public RoleLoginLog setRoleName(String roleName) {
        this.roleName = roleName;
        return this;
    }
}

接下来看调用代码

 /*
    todo  自定义的id生成器；我们在写入日志或者其他数据，
          千万不要去自增id情况，一定要自定义id，
          只要你保证你的自定义的id是唯一那么就一定能写入数据库
      */
    final static IdFormat idFormat = new IdFormat();

    public static void main(String[] args) {

        final SqliteDataHelper db1 = createDataHelper("db1");
        /*检查数据库表结构*/
        db1.createTable(RoleLoginLog.class);

        RoleLoginLog roleLoginLog = new RoleLoginLog()
                .setId(idFormat.getId())
                .setUserId(1)
                .setLogTime(System.currentTimeMillis())
                .setRoleId(1)
                .setRoleName("1");
        db1.getBatchPool().replace(roleLoginLog);

        System.out.println(roleLoginLog.toString());

    }

    public static SqliteDataHelper createDataHelper(String dbName) {
        SqliteDataHelper dataHelper = new SqliteDataHelper("target/db/sqlite/" + dbName + ".db3", dbName, null);
        /*设置查看打印*/
        dataHelper.getDbConfig().setShow_sql(true);
        dataHelper
                /*初始化批量提交参数*/
                .initBatchPool("sqlite")
                /*设置批量提交的量*/
                .setCommitSize(10000)
                /*使用事务*/
                .setTransaction(true);
        return dataHelper;
    }

执行可以看到，建库建表的sql语句已经插入数据的结果

[07-20 12:16:00:706:[ERROR]:[main]:SqlExecute.java.executeScalar():263] 
select sum(1) `TABLE_NAME` from sqlite_master where type ='table' and `name`= ? ;
[07-20 12:16:00:721:[ERROR]:[main]:SqliteDataHelper.java.existsTable():128] 数据库：db1 表：roleloginlog 检查结果：无此表 
[07-20 12:16:00:730:[WARN ]:[main]:SqlExecute.java.execute():44] 
数据库：db1

CREATE TABLE `roleloginlog` (
    `id` bigint NOT NULL PRIMARY KEY,
    `logTime` bigint,
    `userId` bigint,
    `roleId` bigint,
    `roleName` varchar(255)
);
执行结果：false
[07-20 12:16:00:751:[WARN ]:[main]:SqlExecute.java.execute():44] 
数据库：db1
CREATE INDEX roleloginlog_INDEX_logTime ON roleloginlog(logTime);
执行结果：false
[07-20 12:16:00:753:[WARN ]:[main]:SqlExecute.java.execute():44] 
数据库：db1
CREATE INDEX roleloginlog_INDEX_userId ON roleloginlog(userId);
执行结果：false
[07-20 12:16:00:754:[WARN ]:[main]:SqlExecute.java.execute():44] 
数据库：db1
CREATE INDEX roleloginlog_INDEX_roleId ON roleloginlog(roleId);
执行结果：false
[07-20 12:16:00:757:[WARN ]:[main]:SqlExecute.java.execute():44] 
数据库：db1
CREATE INDEX roleloginlog_INDEX_roleName ON roleloginlog(roleName);
执行结果：false


--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：1 条, 当前耗时：24.9069 ms, 当前平均分布：24.9069 ms/条, 当前性能：40.1495 条/S, 
累计操作总量：1 条, 历史耗时：24 ms, 历史平均分布：24.0000 ms/条, 历史性能：41.6667 条/S, 
当前剩余：0 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------

{
    "id":1072012160000000000,
    "roleName":"1",
    "logTime":1626754560764,
    "userId":1,
    "roleId":1
}

这里同样打印了写入数据的效率；

可能你觉得有点低；是不是？但是批量提交优势在于当你需要写入数据多，他才有优势体现；

接下来我们写入10万条数据

    public static void insert(SqliteDataHelper dataHelper) {
        RoleLoginLog roleLoginLog = null;
        for (int i = 0; i < 100000; i++) {
            roleLoginLog = new RoleLoginLog()
                    .setId(idFormat.getId())
                    .setUserId(1)
                    .setLogTime(System.currentTimeMillis())
                    .setRoleId(i)
                    .setRoleName("1_" + i);
            dataHelper.getBatchPool().replace(roleLoginLog);
        }
    }

测试结果

--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：120.6319 ms, 当前平均分布：0.0121 ms/条, 当前性能：82896.8203 条/S, 
累计操作总量：40001 条, 历史耗时：625 ms, 历史平均分布：0.0156 ms/条, 历史性能：64001.5977 条/S, 
当前剩余：60000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：116.97 ms, 当前平均分布：0.0117 ms/条, 当前性能：85492.0078 条/S, 
累计操作总量：50001 条, 历史耗时：741 ms, 历史平均分布：0.0148 ms/条, 历史性能：67477.7344 条/S, 
当前剩余：50000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：115.6237 ms, 当前平均分布：0.0116 ms/条, 当前性能：86487.4531 条/S, 
累计操作总量：60001 条, 历史耗时：856 ms, 历史平均分布：0.0143 ms/条, 历史性能：70094.6250 条/S, 
当前剩余：40000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：112.8095 ms, 当前平均分布：0.0113 ms/条, 当前性能：88645.0156 条/S, 
累计操作总量：70001 条, 历史耗时：968 ms, 历史平均分布：0.0138 ms/条, 历史性能：72315.0859 条/S, 
当前剩余：30000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：106.4188 ms, 当前平均分布：0.0106 ms/条, 当前性能：93968.3594 条/S, 
累计操作总量：80001 条, 历史耗时：1074 ms, 历史平均分布：0.0134 ms/条, 历史性能：74488.8281 条/S, 
当前剩余：20000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：102.0073 ms, 当前平均分布：0.0102 ms/条, 当前性能：98032.1953 条/S, 
累计操作总量：90001 条, 历史耗时：1176 ms, 历史平均分布：0.0131 ms/条, 历史性能：76531.4609 条/S, 
当前剩余：10000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：128.4668 ms, 当前平均分布：0.0128 ms/条, 当前性能：77841.1250 条/S, 
累计操作总量：100001 条, 历史耗时：1304 ms, 历史平均分布：0.0130 ms/条, 历史性能：76687.8828 条/S, 
当前剩余：0 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------

看到了吧，性能差异，批量提交的意义就在于数据量多才能体现出性能优势；

注解二-----分表

我们在写入数据的时候可以根据自己的架构设计或代码需求调整自己的方案；

我的分表思路是根据roleid来进行hash分表；为什么这么说呢，因为这样可以，
无论你是分布式也好，多进程集群也好，能保证针对单个角色(roleid)数据写进同一张表

 我们来看看刚才roleloginlog表修改方案；我们通过覆盖父类DbModel的tableName代码来得到实例model的对应表名

我们只需要改动这里就能做到数据自动落地到不同的表

    @Override
    public String tableName() {
        /*
        测试情况，假设我们这个表数据量非常，我们把表拆分成10张
        但是通常我们为了写入和查询方便，保证同一个角色数据在同一张表里面

        */
        long hashcode = roleId % 42 % 10;
        return "roleloginlog_" + hashcode;
    }

接下来是初始化创建数据表；

这个地方只是为了表现出如何创建分表；你也可以改成你自己的

    public static void createTable(SqliteDataHelper dataHelper, Class<?> clazz) {
        /*这个是数据模型映射关系，也就是把模型转化成sql，把sql结果集转化成模型*/
        SqlDataModelMapping modelMapping = dataHelper.dataWrapper(clazz);
        final String tableName = modelMapping.getTableName();
        try (Connection connection = dataHelper.getConnection()) {
            for (int i = 0; i < 10; i++) {
                modelMapping.setTableName(tableName + "_" + i);
                dataHelper.createTable(connection, modelMapping);
            }
        } catch (SQLException sqlException) {
            throw new ThrowException(sqlException);
        }

    }

我们可以看到，表已经创建成功了；

接下来我们尝试写入数据

还是刚才的10万条数据代码;它就会自动分解到不同的表

--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：408.4178 ms, 当前平均分布：0.0408 ms/条, 当前性能：24484.7324 条/S, 
累计操作总量：80001 条, 历史耗时：3700 ms, 历史平均分布：0.0462 ms/条, 历史性能：21621.8906 条/S, 
当前剩余：20000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：346.221 ms, 当前平均分布：0.0346 ms/条, 当前性能：28883.2871 条/S, 
累计操作总量：90001 条, 历史耗时：4046 ms, 历史平均分布：0.0450 ms/条, 历史性能：22244.4395 条/S, 
当前剩余：10000 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------



--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------
当前操作总量：10000 条, 当前耗时：327.8309 ms, 当前平均分布：0.0328 ms/条, 当前性能：30503.5293 条/S, 
累计操作总量：100001 条, 历史耗时：4373 ms, 历史平均分布：0.0437 ms/条, 历史性能：22867.8262 条/S, 
当前剩余：0 条未处理
--------------------------------------------------sqlite-Batch-Thread-异步写入-----------------------------------------------------------------------

可以很清晰的看到嘛，数据写入到数据库了，而且指定的roleid数据写入表结构；

我们为啥要把指定roleid放在一张表呢，主要是为了读取数据的时候不需要联合查询，多表查询情况；针对同样的数据；（当然你可以可以根据你的业务进行二次拆分）

分表，主要解决问题是当你数据量巨大，你预估都会超过千万级别或者亿级别；

这样我们对数据的写入和查询都会变的非常慢；特别是某些表结构索引变的很多的时候，这个性能问题就越加明显；

我们把数据拆分过后，数据分布在不同的表里面，加入说一亿条数据拆分之后每张表就是1000万条数据；

性能对比就不言而喻；

注解三-----分库

针对分表过后，我们日渐增值的数据集合；只可能越来越大；越来越多；数据级别 GB -> TB -> PB；量级

我们统一按照刚才覆盖Dbmodel函数方式，覆盖dbNme方式来落地数据库折选；

    @Override
    public String dbName() {
         /*
        测试情况，假设我们这个表数据量非常大，我们不仅需要拆分表，同时还需要拆分数据库了
        */
        long hashcode = roleId % 27 % 5;
        return "db" + hashcode;
    }

这样我们能看到写入数据库数据，彻底被hash到不同的库里面；

才有这样的形式，分布式数据一致性算法，保证同一个数据条件能完整的唯一处理条件；

可以看到，如果这样数据拆分之后，我还是假设一亿数据数据，拆分5个库之后一个库就是2000万条数据；

每个库拆分10个表，一个表的数据立马就变成一张表数据量200万条数据；

注解四-----查询

我们分库分表之后怎么去查询呢？

同样的道理，我们既然数据拆分是roleid进行的，那么我们拆分的，那么我们查询肯定也是以这个roleid查找为方向；

把roleid查询按照刚才的拆分方式，获取dbname，和tablename，就能进行查询处理；