Sharding-JDBC 分库分表

r囧r小猫 2021-11-09 15:52 397阅读 0赞

一. 初识Sharding-JDBC

1. Sharding-JDBC是什么？

Sharding-JDBC提供标准化的数据分片、分布式事务和数据库治理功能，定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

2. 为什么要分片？

传统的将数据集中存储至单一数据节点的解决方案，在性能、可用性和运维成本这三方面已经难于满足互联网的海量数据场景。
从性能方面来说，由于关系型数据库大多采用B+树类型的索引，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的IO次数增加，进而导致查询性能的下降；同时，高并发访问请求也使得集中式数据库成为系统的最大瓶颈。
从可用性的方面来讲，服务化的无状态型，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。数据库的可用性，已成为整个系统的关键。
从运维成本方面考虑，当一个数据库实例中的数据达到阈值以上，对于DBA的运维压力就会增大。数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。一般来讲，单一数据库实例的数据的阈值在1TB之内，是比较合理的范围。
在传统的关系型数据库无法满足互联网场景需要的情况下，将数据存储至原生支持分布式的NoSQL的尝试越来越多。 但NoSQL对SQL的不兼容性以及生态圈的不完善，使得它们在与关系型数据库的博弈中始终无法完成致命一击，而关系型数据库的地位却依然不可撼动。
数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。 数据分片的有效手段是对关系型数据库进行分库和分表。分库和分表均可以有效的避免由数据量超过可承受阈值而产生的查询瓶颈。 除此之外，分库还能够用于有效的分散对数据库单点的访问量；分表虽然无法缓解数据库压力，但却能够提供尽量将分布式事务转化为本地事务的可能，一旦涉及到跨库的更新操作，分布式事务往往会使问题变得复杂。 使用多主多从的分片方式，可以有效的避免数据单点，从而提升数据架构的可用性。
通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。
3. 分片的方式

数据分片的拆分方式又分为垂直分片和水平分片。

垂直拆分：是把不同的表拆到不同的数据库中，根据公司不同的业务来拆分成不同的数据库

水平拆分：是把同一个表拆到不同的数据库中（或者是把一张表数据拆分成n多个小表）。相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外某些行又切分到其他的数据库中，主要有分表，分库两种模式 该方式提高了系统的稳定性跟负载能力，但是跨库join性能较差。

4. Sharding-JDBC的核心/工原理

Sharding-JDBC数据分片主要流程是由SQL解析 → 执行器优化 → SQL路由 → SQL改写 → SQL执行 → 结果归并的流程组成。

SQL解析
分为词法解析和语法解析。 先通过词法解析器将SQL拆分为一个个不可再分的单词。再使用语法解析器对SQL进行理解，并最终提炼出解析上下文。 解析上下文包括表、选择项、排序项、分组项、聚合函数、分页信息、查询条件以及可能需要修改的占位符的标记。

SQL解析分为两步， 第一步为 词法解析， 词法解析的意思是就是将SQL进行拆分。
例：
select * from t_user where id = 1
词法解析：
[select] [*] [from] [t_user] [where] [id=1]
第二步语法解析,语法解析器将SQL转换为抽象语法树。

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70

执行器优化
合并和优化分片条件，如OR等。

SQL路由
根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由。

举例说明，如果按照order_id的奇数和偶数进行数据分片，一个单表查询的SQL如下：
SELECT * FROM t_order WHERE order_id IN (1, 2);
那么路由的结果应为：
SELECT * FROM t_order_0 WHERE order_id IN (1, 2);
SELECT * FROM t_order_1 WHERE order_id IN (1, 2);

SQL改写
将SQL改写为在真实数据库中可以正确执行的语句，SQL改写分为正确性改写和优化改写。

  1.  从一个最简单的例子开始，若逻辑SQL为：  
  2.  SELECT order\_id FROM t\_order WHERE order\_id=1;  
  3.  假设该SQL配置分片键order\_id，并且order\_id=1的情况，将路由至分片表1。那么改写之后的SQL应该为：  
  4.  SELECT order\_id FROM t\_order\_1 WHERE order\_id=1;

SQL执行
通过多线程执行器异步执行。
结果归并
将多个执行结果集归并以便于通过统一的JDBC接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并这几种方式。

二. SpringBoot整合Sharding-JDBC

  1. 创建两个数据库order1，order2，分别创建t_address表如下：

    DROP TABLE IF EXISTS t_address;
    CREATE TABLE t_address (
    id bigint(20) NOT NULL,
    code varchar(64) DEFAULT NULL COMMENT ‘编码’,
    name varchar(64) DEFAULT NULL COMMENT ‘名称’,
    pid varchar(64) NOT NULL DEFAULT ‘0’ COMMENT ‘父id’,
    type int(11) DEFAULT NULL COMMENT ‘1国家2省3市4县区’,
    lit int(11) DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  2. 开始整合SpringBoot，这种方式比较简单只要加入sharding-jdbc-spring-boot-starter依赖，在application.yml中配置数据源，分片策略即可使用，这种方式简单，方便。

pom.xml

  1. <dependency>
  2.     <groupId>io.shardingsphere</groupId>
  3.     <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  4.     <version>3.0.0</version>
  5. </dependency>
  6. <dependency>
  7.     <groupId>io.shardingsphere</groupId>
  8.     <artifactId>sharding-jdbc-spring-namespace</artifactId>
  9.     <version>3.0.0</version>
  10. </dependency>

application.yml

  1. mybatis:
  2.   configuration:
  3.     mapUnderscoreToCamelCase: true
  4. sharding:
  5.   jdbc:
  6.     datasource:
  7.       names: ds0,ds1
  8.       ds0:
  9.         type: com.alibaba.druid.pool.DruidDataSource
  10.         driver-class-name: com.mysql.jdbc.Driver
  11.         url: jdbc:mysql://localhost:3306/order1
  12.         username: root
  13.         password: 123456
  14.       ds1:
  15.         type: com.alibaba.druid.pool.DruidDataSource
  16.         driver-class-name: com.mysql.jdbc.Driver
  17.         url: jdbc:mysql://localhost:3306/order2
  18.         username: root
  19.         password: 123456
  20.     config:
  21.       sharding:
  22.         props:
  23.           sql.show: true
  24.         tables:
  25.           t_user: #t_user表【即分库，又分表】
  26.             key-generator-column-name: id # 主键
  27.             actual-data-nodes: ds${0..1}.t_user${0..1} #数据节点
  28.             database-strategy: #分库策略
  29.               inline:
  30.                 sharding-column: city_id
  31.                 algorithm-expression: ds${city_id % 2}
  32.             table-strategy: #分表策略
  33.               inline:
  34.                 shardingColumn: sex
  35.                 algorithm-expression: t_user${sex % 2}
  36.           t_address: #t_address表【只分库】
  37.             key-generator-column-name: id
  38.             actual-data-nodes: ds${0..1}.t_address
  39.             database-strategy:
  40.               inline:
  41.                 shardingColumn: lit
  42.                 algorithm-expression: ds${lit % 2}

编写Dao

  1. @Mapper
  2. public interface IndexDao {
  4.     @InsertProvider(type= AddressProvider.class,method="insertAddress")
  5.     @Options(useGeneratedKeys=true)
  6.     int insertAddress(AddressDo addressDo);
  8.     @Select("select * from t_address order by lit")
  9.     List<AddressDo> listAddress();
  11. }

编写controller

  1. @RestController
  2. public class IndexController {
  4.     @Autowired
  5.     private IndexDao indexDao;
  7.     @PostMapping("/addAddress")
  8.     public ResultBO addAddress(AddressDo addressDo){
  9.         int row = indexDao.insertAddress(addressDo);
  10.         return ResultTool.success(row);
  11.     }
  13.     @GetMapping("/listAddress")
  14.     public ResultBO listAddress(@RequestParam(required=false,defaultValue="1")Integer pageNum,
  15.                                 @RequestParam(required=false,defaultValue="5")Integer pageSize){
  16.         PageHelper.startPage(pageNum,pageSize);
  17.         List<AddressDo> list =  indexDao.listAddress();
  18.         if(list.isEmpty()){
  19.             ResultTool.success("查询内容为空");
  20.         }
  21.         PageInfo<AddressDo> info = new PageInfo<>(list);
  22.         return ResultTool.success(info);
  23.     }
  24. }

此时，启动项目，用postman访问插入接口：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70 1 20190801142330516.png

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70 2 watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70 3 watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70 4

插入四条数据，可以看到两个库，两个表中的数据如下：

20190801142653208.png 20190801142705260.png

可以看到，根据lit字段进行分片（取模算法），因为我们指定的为2：algorithm-expression: ds${lit % 2}，所以奇数和偶数会存到不同的库不同的表中；并且需要注意的是，由于我们指定了key-generator-column-name: id，即自动生成主键，采用雪花算法Twitter-Snowflake。【不同的库】

下面我们用postman请求查询接口，访问：localhost:8080/listAddress，查询结果为：

  1. {
  2.     "code": 0,
  3.     "msg": "成功",
  4.     "data": {
  5.         "pageNum": 1,
  6.         "pageSize": 2,
  7.         "size": 2,
  8.         "startRow": 1,
  9.         "endRow": 2,
  10.         "total": 4,
  11.         "pages": 2,
  12.         "list": [
  13.             {
  14.                 "id": 363696781952811008,
  15.                 "code": "1001",
  16.                 "name": "济南",
  17.                 "pid": "0",
  18.                 "type": 3,
  19.                 "lit": 1
  20.             },
  21.             {
  22.                 "id": 363696816295772160,
  23.                 "code": "1002",
  24.                 "name": "青岛",
  25.                 "pid": "0",
  26.                 "type": 3,
  27.                 "lit": 2
  28.             }
  29.         ],
  30.         "prePage": 0,
  31.         "nextPage": 2,
  32.         "isFirstPage": true,
  33.         "isLastPage": false,
  34.         "hasPreviousPage": false,
  35.         "hasNextPage": true,
  36.         "navigatePages": 8,
  37.         "navigatepageNums": [
  38.             1,
  39.             2
  40.         ],
  41.         "navigateFirstPage": 1,
  42.         "navigateLastPage": 2,
  43.         "firstPage": 1,
  44.         "lastPage": 2
  45.     }
  46. }

可以看到我们的order by 生效了，并且分页也生效了。

【测试没有配过分片策略的表】

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70 5

如上图，只有order2数据库里有订单表address_order_table，此时我们想查询的话直接执行如下SQL语句即可：

  1. @Select("select * from order2.address_order_table")
  2. List<OrderDo> listOrder();

【测试关联查询】

此时我们想关联查询order2库的address_order_table表和order1,order2库的t_address表，下图是address_order_table数据结构

20190801155317206.png

如果想关联两个表，直接执行如下SQL语句即可：

  1. @Select("select a.id,a.address_number,a.order_remark from order2.address_order_table a inner join t_address b " +
  2.         "on a.address_number = b.id where b.id=#{id}")
  3. OrderDo getOrder(@Param("id")Long id);

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FraXJhTmlja3k_size_16_color_FFFFFF_t_70 6

【同一个库进行分表】

上面是在不同的库进行分表，如果要想实现在同一个库下进行分表，则application.yml可以这样配置：

  1. mybatis:
  2.   configuration:
  3.     mapUnderscoreToCamelCase: true
  4. sharding:
  5.   jdbc:
  6.     datasource:
  7.       ####ds1
  8.       names: shardingjdbc
  9.       shardingjdbc:
  10.         type: com.alibaba.druid.pool.DruidDataSource
  11.         driver-class-name: com.mysql.jdbc.Driver
  12.         url: jdbc:mysql://127.0.0.1:3306/shardingjdbc
  13.         username: root
  14.         password: 123456
  15. ####  分片配置（这些表都是需要提前在数据库创建好）【一般都是在单个库进行分表】
  16.     config:
  17.       sharding:
  18.         tables:
  19.           # t_order表分片策略
  20.           t_order:
  21.             table-strategy:
  22.               inline:
  23.                 # 根据userid 进行分片
  24.                 sharding-column: user_id
  25.                 # shardingjdbc.t_order_0    shardingjdbc.t_order_1
  26.                 algorithm-expression: shardingjdbc.t_order_$->{user_id % 2}
  27.             # 分表的总数 0到1    t_order_0    t_order_1
  28.             actual-data-nodes: shardingjdbc.t_order_$->{0..1}
  29. #         t_member:
  30. #           ...
  31.         props:
  32.           sql: 
  33.           ### 开启分片日志
  34.             show: true

解读：在数据库建立两张表：t_order_0和t_order_1，会根据user_id进行分片，取模2，即生成订单的时候，会根据userId进行判断，如果是用户id是奇数，则存到t_order_1表，如果用户id是偶数，则存到t_order_0表中。

真实数据库名：shardingjdbc

逻辑表名： t_order

分表列 user_id

如果想分5个表，怎么修改配置？

答案：actual-data-node: order_service.t_order_${0..4} #分表总数0-4

  1.        **algorithm-expression: t\_order\_$\{user\_id % 5\}    \#取模算法%5**

发表评论

表情：
评论列表 （有 0 条评论，397人围观）

还没有评论，来说两句吧...

相关阅读