• 分库分表与sharding-jdbc


    分库分表与sharding-jdbc

    分库分表

    关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从库、优化索引,做很多操作时性能仍下降严重。

    此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。

    数据库分布式核心内容无非就是数据切分(Sharding),以及切分后对数据的定位整合

    数据切分就是将数据分散存储到多个数据库中,使得单一数据库中的数据量变小,通过扩充主机的数量缓解单一数据库的性能问题,从而达到提升数据库操作性能的目的。

    数据切分根据其切分类型,可以分为两种方式:垂直切分水平切分

    垂直分库分表

    在这里插入图片描述

    垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似,按业务分类进行独立划分。与"微服务治理"的做法相似,每个微服务使用单独的一个数据库。

    垂直分表是基于数据库中的"列"进行,某个表字段较多,可以新建一张扩展表,将不经常用或字段长度较大的字段拆分出去到扩展表中

    在字段很多的情况下(例如一个大表有100多个字段),通过"大表拆小表",更便于开发与维护,也能避免跨页问题。

    垂直切分的优点:

    • 解决业务系统层面的耦合,业务清晰
    • 与微服务的治理类似,也能对不同业务的数据进行分级管理、维护、监控、扩展等
    • 高并发场景下,垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

    缺点:

    • 部分表无法join,只能通过接口聚合方式解决,提升了开发的复杂度
    • 分布式事务处理复杂
    • 依然存在单表数据量过大的问题(需要水平切分)
    水平分库分表

    在这里插入图片描述

    一般我们在提到分库分表的时候,大多是以水平切分模式(水平分库、分表)为基础来说的

    当一个应用难以再细粒度的垂直切分,或切分后数据量行数巨大,存在单库读写、存储性能瓶颈,这时候就需要进行水平切分了。

    水平切分是根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多个表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。

    数据分片将原本一张数据量较大的表 order 拆分生成数个表结构完全一致的小数据量表 order_0order_1、···、order_n

    每张表只存储原大表中的一部分数据,当执行一条SQL时会通过 分库策略分片策略 将数据分散到不同的数据库、表内。

    水平切分的优点:

    • 不存在单库数据量过大、高并发的性能瓶颈,提升系统稳定性和负载能力
    • 应用端改造较小,不需要拆分业务模块

    缺点:

    • 跨分片的事务一致性难以保证
    • 跨库的join关联查询性能较差
    • 数据多次扩展难度和维护量极大
    分表方式
    1. 根据数值范围:按照时间区间或ID区间来切分
      • 例如:按日期将不同月甚至是日的数据分散到不同的库中;将userId为19999的记录分到第一个库,10000~20000的分到第二个库,以此类推。
    2. 根据数值取模:一般采用hash取模mod的切分方式
      • 例如:将 Customer 表根据 cusno 字段切分到4个库中,余数为0的放到第一个库,余数为1的放到第二个库,以此类推。
    分库分表带来的问题

    分库分表能有效的环节单机和单库带来的性能瓶颈和压力,突破网络IO、硬件资源、连接数的瓶颈,同时也带来了一些问题。下面将描述这些技术挑战以及对应的解决思路。

    事务一致性问题
    解决方案

    分布式事务

    当更新内容同时分布在不同库中,不可避免会带来跨库事务问题。跨分片事务也是分布式事务,没有简单的方案,一般可使用"XA协议"和"两阶段提交"处理。

    分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点,推后了提交事务的时间点,延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多,这种趋势会越来越严重,从而成为系统在数据库层面上水平扩展的枷锁。

    最终一致性

    对于那些性能要求很高,但对一致性要求不高的系统,往往不苛求系统的实时一致性,只要在允许的时间段内达到最终一致性即可,可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同,事务补偿是一种事后检查补救的措施,一些常见的实现方法有:对数据进行对账检查,基于日志进行对比,定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。

    跨节点关联查询 join 问题

    切分之前,系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后,数据可能分布在不同的节点上,此时join带来的问题就比较麻烦了,考虑到性能,尽量避免使用join查询。

    解决方案

    1、全局表

    全局表,也可看做是"数据字典表",就是系统中所有模块都可能依赖的一些表,为了避免跨库join查询,可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改,所以也不担心一致性的问题。

    2、字段冗余

    一种典型的反范式设计,利用空间换时间,为了性能而避免join查询。例如:订单表保存userId时候,也将userName冗余保存一份,这样查询订单详情时就不需要再去查询"买家user表"了。

    但这种方法适用场景也有限,比较适用于依赖字段比较少的情况。而冗余字段的数据一致性也较难保证,就像上面订单表的例子,买家修改了userName后,是否需要在历史订单中同步更新呢?这也要结合实际业务场景进行考虑。

    3、数据组装

    在系统层面,分两次查询,第一次查询的结果集中找出关联数据id,然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

    4、ER分片

    关系型数据库中,如果可以先确定表之间的关联关系,并将那些存在关联关系的表记录存放在同一个分片上,那么就能较好的避免跨分片join问题。在1:1或1:n的情况下,通常按照主表的ID主键切分。

    跨节点分页、排序、函数问题

    跨节点多库进行查询时,会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序,当排序字段就是分片字段时,通过分片规则就比较容易定位到指定的分片;当排序字段非分片字段时,就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序,最终返回给用户。

    主键重复问题

    在分库分表环境中,由于表中数据同时存在不同数据库中,主键值平时使用的自增长将无用武之地,某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键,以避免跨库主键重复问题。

    解决方案

    雪花Snowflake分布式自增ID算法

    Twitter的snowflake算法解决了分布式系统生成全局ID的需求,生成64位的Long型数字,组成部分:

    • 第一位未使用
    • 接下来41位是毫秒级时间,41位的长度可以表示69年的时间
    • 5位datacenterId,5位workerId。10位的长度最多支持部署1024个节点
    • 最后12位是毫秒内的计数,12位的计数顺序号支持每个节点每毫秒产生4096个ID序列
    Leaf——美团点评分布式ID生成系统

    https://tech.meituan.com/2017/04/21/mt-leaf.html

    解决了高可用、容灾、分布式下时钟的问题

    数据迁移、扩容问题

    当业务高速发展,面临性能和存储的瓶颈时,才会考虑分片设计,此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据,然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和QPS,以及业务发展的速度,进行容量规划,推算出大概需要多少分片(一般建议单个分片上的单表数据量不超过1000W)

    如果采用数值范围分片,只需要添加节点就可以进行扩容了,不需要对分片数据迁移。如果采用的是数值取模分片,则考虑后期的扩容问题就相对比较麻烦。

    sharding jdbc介绍

    参考https://zhuanlan.zhihu.com/p/301031380

    Sharding-JDBC 最早是当当网内部使用的一款分库分表框架,到2017年的时候才开始对外开源,这几年在大量社区贡献者的不断迭代下,功能也逐渐完善,现已更名为 ShardingSphere,2020年4⽉16⽇正式成为 Apache 软件基⾦会的顶级项⽬。

    随着版本的不断更迭 ShardingSphere 的核心功能也变得多元化起来。从最开始 Sharding-JDBC 1.0 版本只有数据分片,到 Sharding-JDBC 2.0 版本开始支持数据库治理(注册中心、配置中心等等),再到 Sharding-JDBC 3.0版本又加分布式事务 (支持 AtomikosNarayanaBitronixSeata),如今已经迭代到了 Sharding-JDBC 4.0 版本。

    现在的 ShardingSphere 不单单是指某个框架而是一个生态圈,这个生态圈 Sharding-JDBCSharding-ProxySharding-Sidecar 这三款开源的分布式数据库中间件解决方案所构成。

    ShardingSphere 的前身就是 Sharding-JDBC,所以它是整个框架中最为经典、成熟的组件,我们先从 Sharding-JDBC 框架入手学习分库分表。

    术语

    数据节点

    是分库分表中一个不可再分的最小数据单元(表),它由数据源名称和数据表组成,例如order_db_1.t_order_0order_db_2.t_order_1 都能表示一个数据节点。

    逻辑表真实表

    逻辑表是指一组具有相同逻辑和数据结构表的总称。比如我们将订单表order 拆分成 order_0 ··· order_9 等 10张表。此时我们会发现分库分表以后数据库中已不在有 order 这张表,取而代之的是 order_n,但我们在代码中写 SQL依然按 order 来写。此时 order 就是这些拆分表的逻辑表

    真实表也就是上边提到的order_0 ··· order_9这样在数据库中真实存在的物理表。

    分片键

    用于分片的数据库字段。

    例如:我们将 order 表分片以后,当执行一条SQL时,通过对字段 order_id 取模的方式来决定,这条数据该在哪个数据库中的哪个表中执行,此时 order_id 字段就是 order 表的分片健。

    sharding jdbc和JDBC的猫腻

    Sharding-JDBC 重写了 JDBC 规范并完全兼容了 JDBC 规范

    对原有的 DataSourceConnection 等接口扩展成 ShardingDataSourceShardingConnection,而对外暴露的分片操作接口与 JDBC 规范中所提供的接口完全一致,只要开发者熟悉 JDBC 就可以轻松应用 Sharding-JDBC 来实现分库分表。

    因此它适用于任何基于 JDBCORM 框架,如:JPAHibernateMybatisSpring JDBC Template 或直接使用的 JDBC。完美兼容任何第三方的数据库连接池,如:DBCPC3P0BoneCPDruidHikariCP 等,几乎对主流关系型数据库都支持。

    sharding jdbc的职责——改造SQL

    Sharding-JDBC 拓展 JDBC API 接口后,在新增的分片功能里又做了哪些事情呢?

    一张表经过分库分表后被拆分成多个子表,并分散到不同的数据库中,在不修改原业务 SQL 的前提下,Sharding-JDBC 就必须对 SQL进行一些改造才能正常执行。

    大致的执行流程:SQL 解析 -> 执⾏器优化 -> SQL 路由 -> SQL 改写 -> SQL 执⾏ -> 结果归并 六步组成,

    在这里插入图片描述

    SQL路由

    SQL 路由通过解析分片上下文,匹配到用户配置的分片策略,并生成路由路径。

    简单点理解就是可以根据我们配置的分片策略计算出 SQL该在哪个库的哪个表中执行

    SQL 改写

    将基于逻辑表开发的SQL改写成可以在真实数据库中可以正确执行的语句。比如查询 t_order 订单表,我们实际开发中 SQL是按逻辑表 t_order 写的。

    SELECT * FROM t_order
    
    • 1

    因为分库分表以后真实数据库中 t_order 表就不存在了,而是被拆分成多个子表 t_order_n 分散在不同的数据库内,按原SQL执行显然是行不通的,这时就需要根据SQL路由及分片策略,来将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。

    SELECT * FROM t_order_1
    SELECT * FROM t_order_2
    
    • 1
    • 2
    结果归并

    将从各个数据节点获取的多数据结果集,合并成一个大的结果集并正确的返回至请求客户端,称为结果归并。而我们SQL中的排序、分组、分页和聚合等语法,均是在归并后的结果集上进行操作的。

    sharding jdbc实战

    参考https://zhuanlan.zhihu.com/p/301031380

    建库建表

    建立sharding_01和sharding_02

    分别建s_user_01、s_user_02、s_user_03

    CREATE TABLE `s_user_01` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(64) NOT NULL COMMENT '用户名',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
    
    • 1
    • 2
    • 3
    • 4
    • 5
    导入依赖
            <dependency>
                <groupId>org.apache.shardingspheregroupId>
                <artifactId>sharding-jdbc-spring-boot-starterartifactId>
                <version>4.0.0version>
            dependency>
    
    • 1
    • 2
    • 3
    • 4
    • 5
    分库分表配置文件

    注释写的很详细了

    注:在spring boot版本 不是1.x 的情况下,用了下划线当做数据库和策略名称,导致策略一直没生效,这个bug找了好久问题才找出来

    # 注:无论是数据源还是策略名称,如果boot版本不是1.x的话,千万不能用_   不然会导致无法识别
    # 定义两个全局数据源
    spring.shardingsphere.datasource.names=ds-01,ds-02
    # sharding SQL解析日志
    spring.shardingsphere.props.sql.show=false
    # 指定真实数据节点,$为占位符,ds-0$->{1..2}.s_user_0$->{1..3}表示6个数据节点(ds是数据源节点的名称)
    spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds-0$->{1..2}.s_user_0$->{1..3}
    
    # 分库策略
    # 分库分片健
    spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id
    # 分库分片算法(节点的名称)
    spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=ds-0$->{id%2 +1}
    
    # 分表策略
    # 分表分片健
    spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
    # 分表分片算法——行分片策略:该分片策略只支持 = 和 in 操作符,不支持范围查询(解决方案:使用标准分片策略)
    spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=s_user_0$->{id%3 +1}
    # 自增主键字段
    spring.shardingsphere.sharding.tables.user.key-generator.column=id
    # 自增主键ID生成方案,目前内置了SNOWFLAKE 和 UUID 两种方式
    spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
    
    # 配置数据源ds-01
    spring.shardingsphere.datasource.ds-01.type=org.springframework.jdbc.datasource.DriverManagerDataSource
    spring.shardingsphere.datasource.ds-01.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-01.url=jdbc:mysql://localhost:3306/sharding_01?characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    spring.shardingsphere.datasource.ds-01.username=root
    spring.shardingsphere.datasource.ds-01.password=123321
    
    # 配置数据源ds-02
    spring.shardingsphere.datasource.ds-02.type=org.springframework.jdbc.datasource.DriverManagerDataSource
    spring.shardingsphere.datasource.ds-02.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-02.url=jdbc:mysql://localhost:3306/sharding_02?characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    spring.shardingsphere.datasource.ds-02.username=root
    spring.shardingsphere.datasource.ds-02.password=123321
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    编写测试案例
    @SpringBootTest
    @RunWith(SpringRunner.class)
    class ShardingJdbcStudyApplicationTests {
    
        @Autowired
        private UserService userService;
    
        @Test
        void test1() {
            List<User> list = Lists.newArrayList();
            for (int i = 1; i <= 20; i++) {
                list.add(User.builder().build().setName("第" + i + "个用户"));
            }
            userService.saveBatch(list);
        }
    
        @Test
        void test2() {
            // 行分片策略:该分片策略只支持 = 和 in 操作符,不支持范围查询(解决方案:使用标准分片策略)
            // 报错
            List<User> list = userService.lambdaQuery()
                    .between(User::getId, 1565364510500000000L, 1565364510600000000L)
                    .list();
            System.out.println(list);
        }
    
        @Test
        void test3() {
            // 结果归并:将从各个数据节点获取的多数据结果集,合并成一个大的结果集并正确的返回至请求客户端,称为结果归并。
            // 而我们SQL中的排序、分组、分页和聚合等语法,均是在归并后的结果集上进行操作的。
            StopWatch sw = new StopWatch();
            sw.start("in查询id");
            List<User> list1 = userService.lambdaQuery()
                    .in(User::getId, 1565364510510927876L, 1565364510494150658L)
                    .list();
            sw.stop();
            System.out.println(list1);
    
            sw.start("in查询name");
            List<User> list2 = userService.lambdaQuery()
                    .in(User::getName, "第18个用户", "第1个用户")
                    .list();
            sw.stop();
            System.out.println(list2);
    
            sw.start("like查询name");
            List<User> list3 = userService.lambdaQuery().like(User::getName, "%8%").list();
            sw.stop();
            System.out.println(list3);
    
            sw.start("全表查询");
            List<User> list4 = userService.list();
            sw.stop();
            System.out.println(list4.size());
    
            // 可能因为版本原因,这里StopWatch给出的是ns单位
            System.out.println(sw.prettyPrint());
        }
    }
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    结果
    插入结果

    这里就展示第一个库的第一张表的截图了

    在这里插入图片描述

    查询结果

    [User(id=1565364510510927876, name=第11个用户), User(id=1565364510494150658, name=第6个用户)]
    [User(id=1565364510544482306, name=第18个用户), User(id=1565364507654606849, name=第1个用户)]
    [User(id=1565364510544482306, name=第18个用户), User(id=1565364510502539266, name=第8个用户)]
    20

    查询时间

    可以看到,第一次查询居然花费了900ms,而后面的全表查询甚至是只有20ms

    StopWatch ‘’: running time = 977 ms

    ms % Task name

    901 092% in查询id
    29 003% in查询name
    26 003% like查询name
    20 002% 全表查询

    于是我将全表查询提到上面,发现只要是第一次查询都会很慢

    可能是由于sharding在归并结果后做了某些逻辑,使得后续查询受益于该逻辑,才让后续查询与未分库分表时的查询速度基本一致

    StopWatch ‘’: running time = 1001 ms

    ms % Task name

    683 068% 全表查询
    258 026% in查询id
    29 003% in查询name
    29 003% like查询name

  • 相关阅读:
    蓝鹏精准测量系统 助力圆钢负公差轧制
    记一次高校学生账户的“从无到有”
    立仪科技光谱共焦在半导体领域的应用
    就业班 第四阶段(k8s) 2401--6.4 day2 Dashboard+国产kuboard(好用)+简单命令
    《金字塔原理》阅读笔记
    面向多段落高考阅读理解的答案句抽取方法
    Linux 基础-新手必备命令
    MySQL——MySQL环境搭建
    组装式应用为何成为十二大技术趋势
    24、订单和购物车-srv服务
  • 原文地址:https://blog.csdn.net/Jay_Chou345/article/details/126653803