分库分表解决方案-ShardingSphere-JDBC

发布时间:2024年01月01日

ShardingSphere-JDBC

简介

ShardingSphere-JDBC 是一个工作在客户端的,定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。可以将任意数据库转换为分布式数据库,并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

能力

分库分表

  1. 垂直分库/分表
mybatis:
  type-aliases-package=com.xiaohei.sharding.verticaldatabase.mapper

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2

      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere_01?serverTimezone=UTC
        username: root
        password: 123456
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere_02?serverTimezone=UTC
        username: root
        password: 123456

    sharding:
      tables:
        order:
          actual-data-nodes: ds1.order
#          key-generator:
#            column: id
#            type: SNOWFLAKE
#          table-strategy:
#            inline:
#              sharding-column: id
#              algorithm-expression: order
        customer:
          actual-data-nodes: ds2.customer
#          key-generator:
#            column: id
#            type: SNOWFLAKE
#          table-strategy:
#            inline:
#              sharding-column: id
#              algorithm-expression: customer

    props:
      sql:
        show: true
  1. 水平分库
mybatis:
  type-aliases-package=com.xiaohei.sharding.horizontaldatabase.mapper

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2

      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere_01?serverTimezone=UTC
        username: root
        password: 123456
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere_02?serverTimezone=UTC
        username: root
        password: 123456

    sharding:
      tables:
        order:
          actual-data-nodes: ds$->{1..2}.orders_$->{1..2}
          key-generator:
            column: id
            type: MyAtomicLong
            props:
              worker:
                id: 1000
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: orders_$->{id%2+1}
          database-strategy:
            inline:
              sharding-column: customer_id
              algorithm-expression: ds$->{customer_id%2+1}

    props:
      sql:
        show: true
  1. 水平分表
mybatis:
  type-aliases-package=com.xiaohei.sharding.horizontaltable.mapper

spring:
  shardingsphere:
    datasource:
      names: ds1

      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere?serverTimezone=UTC
        username: root
        password: 123456

    sharding:
      tables:
        order:
          actual-data-nodes: ds1.orders_$->{1..2}
          key-generator:
            column: id
            type: SNOWFLAKE
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: orders_$->{id%2+1}

    props:
      sql:
        show: true

分布式事务

shardingsphere-jdbc默认使用的事务类型为本地事务(LOCAL)

  1. 支持项
  • 完全支持非跨库事务,例如:仅分表,或分库但是路由的结果在单库中;
  • 完全支持因逻辑异常导致的跨库事务。例如:同一事务中,跨两个库更新。更新完毕后,抛出空指针,则两个库的内容都能够回滚。
  1. 不支持项
  • 不支持因网络、硬件异常导致的跨库事务。例如:同一事务中,跨两个库更新,更新完毕后、未提交之前,第一个库宕机,则只有第二个库数据提交,且无法回滚。

读写分离

mybatis:
  type-aliases-package=com.xiaohei.sharding.readwritesplitting.mapper

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2

      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere_01?serverTimezone=UTC
        username: root
        password: 123456
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_sphere_02?serverTimezone=UTC
        username: root
        password: 123456

    masterslave:
      name: ms
      master-data-source-name: ds1
      slave-data-source-names: ds2

    props:
      sql:
        show: true

参考

  1. https://shardingsphere.apache.org/document/5.3.2/cn/features/sharding/
  2. https://www.cnblogs.com/ityml/p/14970508.html#6sharding-jdbc%E5%AE%9E%E7%8E%B0%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB
  3. https://www.jianshu.com/p/4dccb71ee51b
  4. https://blog.csdn.net/qq_31708899/article/details/121577253
  5. https://blog.csdn.net/u014528861/article/details/116655292
文章来源:https://blog.csdn.net/hudmhacker/article/details/135324961
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。