Shardingsphere5分库分表

分库分表垂直分库按业务拆。把不同的表放到不通的数据库中。水平分库同一张表拆成多个表放到不同的库中相同表结构的数据库就是水平分表。水平分表一张表拆数据行把一张表横着拆成多张一般与水平分库一起用。垂直分表把一张大表的列拆开。分片 总称分库 分片的一种拆数据库分表 分片的一种拆表水平分表不与水平分库一起用db_order ├── t_order_0 ├── t_order_1 ├── t_order_2 ├── t_order_3水平分库与水平分表一起用db_order_0 ├── t_order_0 ├── t_order_1 db_order_1 ├── t_order_0 ├── t_order_1垂直分库 水平分库 水平分表最典型db_user t_user 对t_order进行水平分库和分表 db_order_0 t_order db_order_1 t_order只水平分库db_order_0 t_order db_order_1 t_order分库分表的核心思想数据分散存储使得单一数据库/表的数据量变小来缓解单一数据库的性能问题从而达到提升数据库性能的目的。垂直分库以表为依据根据业务将不同表拆分到不同库中。每个库的表结构都不一样。每个库的数据也不一样。所有库的并集是全量数据。垂直分表以字段为依据根据字段属性将不同字段拆分到不同表中。每个表的结构都不一样。每个表的数据也不一样一般通过一列主键/外键关联。所有表的并集是全量数据。水平分库以字段为依据按照一定策略将一个库的数据拆分到多个库中。每个库的表结构都一样。每个库的数据都不一样。所有库的并集是全量数据。水平分表以字段为依据按照一定策略将一个表的数据拆分到多个表中。每个表的表结构都一样。每个表的数据都不一样。所有表的并集是全量数据。配置环境JDK8SpringBoot(2.3.9.RELEASE)ShardingSphere-jdbc(5.1.1)MySQL8(一个主库两个从库配置好主从复制)。Maven依赖dependencies dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency dependency groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactId version5.1.1/version /dependency dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId scoperuntime/scope /dependency dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.3.1/version /dependency dependency groupIdorg.projectlombok/groupId artifactIdlombok/artifactId optionaltrue/optional /dependency dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-test/artifactId scopetest/scope exclusions exclusion groupIdorg.junit.vintage/groupId artifactIdjunit-vintage-engine/artifactId /exclusion /exclusions /dependency /dependencies读写分离配置参考文档读写分离 :: ShardingSphereShardingSphere启动模式模式作用Memory规则存在JVM内存Standalone单机持久化Cluster注册中心模式负载均衡算法常用ROUND_ROBIN、RANDOM。spring: application: name: sharding-jdbc-dome profiles: active: dev shardingsphere: mode: type: Memory datasource: # 声明所有真实数据源的名称。 # # 这里定义了3个真实数据库连接 # master主库 # slave1第1个从库 # slave2第2个从库 # # names中的名称必须与下面的数据源配置名称一致。 # 名称不是固定的也可以写成 # write-db,read-db-1,read-db-2 names: master,slave1,slave2 # 主库真实数据源配置。 master: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33388/db_user username: root password: 123456 # 第1个从库真实数据源配置。 slave1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33307/db_user username: root password: 123456 # 第2个从库真实数据源配置。 slave2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33308/db_user username: root password: 123456 rules: # 读写分离规则。 # ShardingSphere只负责SQL路由不负责MySQL数据同步。 # master到slave1、slave2的数据同步仍由MySQL主从复制完成。 readwrite-splitting: >load-balancer-name: olg_weight load-balancers: olg_weight: type: WEIGHT props: slave1: 1.0 slave2: 2.0如果在同一个事务下有增删改也有查询只会查询主数据库而不是从数据库。Transactional Test void transactionTest(){ User user new User(); user.setUname(张三丰); userMapper.insert(user); ListUser users userMapper.selectList(null); System.out.println(users); }垂直分库水平分库水平分表 配置环境说明一个逻辑表t_order两个真实数据库server-order0 → MySQL数据库 db_orderserver-order1 → MySQL数据库 db_order每个数据库两个真实表t_order0t_order1最终形成2个库 × 2张表 4张真实订单表对应的配置如下t_order: actual-data-nodes: server-order$-{0..1}.t_order$-{0..1}分库规则database-strategy: standard: sharding-column: user_id sharding-algorithm-name: alg_mod配置说明根据user_id决定数据库算法偶数用户server-order0奇数用户server-order1alg_mod: type: MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模user_id计算数据库11%21server-order122%20server-order033%21server-order144%20server-order0分表规则table-strategy: standard: sharding-column: order_no sharding-algorithm-name: alg_hash_mod表示同一个数据库里面再根据order_no决定放到哪个表中。alg_hash_mod: type: HASH_MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模例如订单号hash(10001)%20 进入t_order0另一个hash(10002)%21 进入:t_order1。雪花IDid字段自动生成id字段要配合mybatis-plus的“TableId(type IdType.AUTO)”。t_order: key-generate-strategy: column:id key-generator-name:alg_snowflake完整配置文件spring: application: name: sharding-jdbc-dome profiles: active: dev shardingsphere: mode: type: Memory datasource: names: server-user,server-order0,server-order1 server-user: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33008/db_user?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 server-order0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33016/db_order?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 server-order1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33017/db_order?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 props: sql-show: true rules: sharding: tables: t_user: actual-data-nodes: server-user.t_user t_order: actual-data-nodes: server-order$-{0..1}.t_order$-{0..1} database-strategy: standard: sharding-column: user_id sharding-algorithm-name: alg_mod table-strategy: standard: sharding-column: order_no sharding-algorithm-name: alg_hash_mod key-generate-strategy: column: id key-generator-name: alg_snowflake sharding-algorithms: alg_inline_userId: type: INLINE props: algorithm-expression: server-order$-{user_id % 2} alg_mod: type: MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模 alg_hash_mod: type: HASH_MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模 key-generators: alg_snowflake: type: SNOWFLAKE读写分离环境说明一个主库master负责写两个从库slave1、slave2负责读查询自动分发到从库插入、更新、删除自动走主库注意如果是在一个事务中进行了增删改之后并执行了查询那么此时为了保证数据一致性此时会自动查询主库。配置读写分离规则rules: readwrite-splitting:创建一个逻辑数据源myds对应的是“master slave1 slave2”三个数据源。data-sources: myds:手动指定读写库myds: type: Static props: write-data-source-name: master read-data-source-names: slave1,slave2负载均衡配置load-balancers: alg_round: type: ROUND_ROBIN # 轮训或者按权重 olg_weight: type: WEIGHT props: slave1: 1.0 slave2: 2.0完整配置文件spring: application: name: sharding-jdbc-dome profiles: active: dev shardingsphere: mode: type: Memory datasource: names: master,slave1,slave2 master: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33388/db_user username: root password: 123456 slave1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33307/db_user username: root password: 123456 slave2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33308/db_user username: root password: 123456 rules: readwrite-splitting: >SpringBoot应用 | ShardingSphere-JDBC | --------------------------------- | | server-order0 server-order1 | | t_order0 t_order0 t_order1 t_order1 t_order_item0 t_order_item0 t_order_item1 t_order_item1ShardingSphere的绑定表支持多个表组成一组也支持配置多个绑定表组。使用前提多个表的分库分表规则必须完全一致。SQL联查绑定表关联时最好使用分片键例如order_no进行关联。SELECT o.order_no, SUM(i.price * i.count) AS amount FROM t_order o JOIN t_order_item i ON o.order_no i.order_no GROUP BY o.order_no;完整配置文件spring: application: name: sharding-jdbc-dome profiles: active: dev shardingsphere: mode: type: Memory datasource: names: server-user,server-order0,server-order1 server-user: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33008/db_user?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 server-order0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33016/db_order?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 server-order1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33017/db_order?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 props: sql-show: true rules: sharding: tables: t_user: actual-data-nodes: server-user.t_user t_order: actual-data-nodes: server-order$-{0..1}.t_order$-{0..1} database-strategy: standard: sharding-column: user_id sharding-algorithm-name: alg_mod table-strategy: standard: sharding-column: order_no sharding-algorithm-name: alg_hash_mod key-generate-strategy: column: id key-generator-name: alg_snowflake t_order_item: actual-data-nodes: server-order$-{0..1}.t_order_item$-{0..1} database-strategy: standard: sharding-column: user_id sharding-algorithm-name: alg_mod table-strategy: standard: sharding-column: order_no sharding-algorithm-name: alg_hash_mod key-generate-strategy: column: id key-generator-name: alg_snowflake sharding-algorithms: alg_inline_userId: type: INLINE props: algorithm-expression: server-order$-{user_id % 2} alg_mod: type: MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模 alg_hash_mod: type: HASH_MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模 key-generators: alg_snowflake: type: SNOWFLAKE # 绑定表指分片规则一致的一组分片表。 使用绑定表进行多表关联查询时必须使用分片键进行关联 # 否则会出现笛卡尔积关联或跨库关联从而影响查询效率。 binding-tables[0]: t_order,t_order_item # 去掉笛卡尔积的关联广播表指所有的分片数据源中都存在的表表结构及其数据在每个数据库中均完全一致。 适用于数据量不大且需要与海量数据的表进行关联查询的场景例如字典表商品分类表。订单状态表。广播具有以下特性1插入、更新操作会实时在所有节点上执行保持各个分片的数据一致性2查询操作只从一个节点获取3可以跟任何一个表进行 JOIN 操作环境说明需要提前在这3个数据库中创建结构完全相同的t_dict表逻辑表 t_dict 对应3张真实表server-user对应的db_user.t_dictserver-order0对应的db_order.t_dictserver-order1对应的db_order.t_dict核心配置逻辑表 t_dict 对应3张真实表t_dict: actual-data-nodes: server-order$-{0..1}.t_dict,server-user.t_dict broadcast-tables[0]: t_dict完整配置文件spring: application: name: sharding-jdbc-dome profiles: active: dev shardingsphere: mode: type: Memory datasource: names: server-user,server-order0,server-order1 server-user: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33008/db_user?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 server-order0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33016/db_order?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 server-order1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:33017/db_order?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai username: root password: 123456 props: sql-show: true rules: sharding: tables: t_user: actual-data-nodes: server-user.t_user t_order: actual-data-nodes: server-order$-{0..1}.t_order$-{0..1} database-strategy: standard: sharding-column: user_id sharding-algorithm-name: alg_mod table-strategy: standard: sharding-column: order_no sharding-algorithm-name: alg_hash_mod key-generate-strategy: column: id key-generator-name: alg_snowflake t_order_item: actual-data-nodes: server-order$-{0..1}.t_order_item$-{0..1} database-strategy: standard: sharding-column: user_id sharding-algorithm-name: alg_mod table-strategy: standard: sharding-column: order_no sharding-algorithm-name: alg_hash_mod key-generate-strategy: column: id key-generator-name: alg_snowflake t_dict: actual-data-nodes: server-order$-{0..1}.t_dict,server-user.t_dict sharding-algorithms: alg_inline_userId: type: INLINE props: algorithm-expression: server-order$-{user_id % 2} alg_mod: type: MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模 alg_hash_mod: type: HASH_MOD props: sharding-count: 2 # 针对sharding-column: user_id字段对2取模 key-generators: alg_snowflake: type: SNOWFLAKE # 绑定表指分片规则一致的一组分片表。 使用绑定表进行多表关联查询时必须使用分片键进行关联 # 否则会出现笛卡尔积关联或跨库关联从而影响查询效率。 binding-tables[0]: t_order,t_order_item # 去掉笛卡尔积的关联 broadcast-tables[0]: t_dict