Sharding-JDBC分库不分表、分库分表，主从分库分表

柔光的暖阳◎ 2022-12-30 15:53 394阅读 0赞

分库不分表、分库分表，主从分库分表

分库不分表

  1. server:
  2.   port: 8800
  4. mybatis:
  5.   configuration:
  6.     map-underscore-to-camel-case: true
  7.     use-generated-keys: true
  9. spring:
  10.   shardingsphere:
  11.     datasource:
  12.       names: ds0,ds1
  13.       ds0:
  14.         type: com.alibaba.druid.pool.DruidDataSource
  15.         driver-class-name: com.mysql.jdbc.Driver
  16.         url: jdbc:mysql://127.0.0.1:3306/ds0?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  17.         username: xxx
  18.         password: xxx
  19.       ds1:
  20.         type: com.alibaba.druid.pool.DruidDataSource
  21.         driver-class-name: com.mysql.jdbc.Driver
  22.         url: jdbc:mysql://127.0.0.1:3306/ds1?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  23.         username: xxx
  24.         password: xxx
  25.     sharding:
  26.       default-database-strategy:
  27.         inline:
  28.           sharding-column: order_id
  29.           algorithm-expression: ds$->{ order_id % 2}
  30.       tables:
  31.         t_order:
  32.           actual-data-nodes: ds$->{ 0..1}.t_order
  33.           key-generator:
  34.             column: order_id
  35.             type: SNOWFLAKE
  36.             props:
  37.               worker:
  38.                 id: 123
  39.     props:
  40.       sql:
  41.         show: true # 打印sql

分库分表

  1. server:
  2.   port: 8800
  4. mybatis:
  5.   configuration:
  6.     map-underscore-to-camel-case: true
  7.     use-generated-keys: true
  9. spring:
  10.   shardingsphere:
  11.     datasource:
  12.       names: ds0,ds1
  13.       ds0:
  14.         type: com.alibaba.druid.pool.DruidDataSource
  15.         driver-class-name: com.mysql.jdbc.Driver
  16.         url: jdbc:mysql://127.0.0.1:3306/ds0?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  17.         username: xxx
  18.         password: xxx
  19.       ds1:
  20.         type: com.alibaba.druid.pool.DruidDataSource
  21.         driver-class-name: com.mysql.jdbc.Driver
  22.         url: jdbc:mysql://127.0.0.1:3306/ds1?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  23.         username: xxx
  24.         password: xxx
  25.     sharding:
  26.       default-database-strategy:
  27.         inline:
  28.           sharding-column: order_id
  29.           algorithm-expression: ds${ order_id % 2}
  30.       tables:
  31.         t_order:
  32.           actual-data-nodes: ds${ 0..1}.t_order_${ 0..1}
  33.           table-strategy:
  34.             inline:
  35.               sharding-column: order_id
  36.               algorithm-expression: t_order_${ (order_id % 5) % 2}
  37.           key-generator:
  38.             column: order_id
  39.             type: SNOWFLAKE
  40.             props:
  41.               worker:
  42.                 id: 123
  43.     props:
  44.       sql:
  45.         show: true # 打印sql

分表的策略不能跟分库一样，比如都用order_id取模的话，那么就会出现每个数据库中都有一半的数据表没有数据，所以这里分表采取了t_order_${(order_id % 5) % 2}这种先对一个基数取模来解决这个问题。

主从分库分表

  1. server:
  2.   port: 8800
  4. mybatis:
  5.   configuration:
  6.     map-underscore-to-camel-case: true
  7.     use-generated-keys: true
  9. spring:
  10.   shardingsphere:
  11.     datasource:
  12.       names: ds0master,ds0slave,ds1master,ds1slave
  13.       ds0master:
  14.         type: com.alibaba.druid.pool.DruidDataSource
  15.         driver-class-name: com.mysql.jdbc.Driver
  16.         url: jdbc:mysql://127.0.0.1:3306/ds0master?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  17.         username: xxx
  18.         password: xxx
  19.       ds0slave:
  20.         type: com.alibaba.druid.pool.DruidDataSource
  21.         driver-class-name: com.mysql.jdbc.Driver
  22.         url: jdbc:mysql://127.0.0.1:3306/ds0slave?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  23.         username: xxx
  24.         password: xxx
  25.       ds1master:
  26.         type: com.alibaba.druid.pool.DruidDataSource
  27.         driver-class-name: com.mysql.jdbc.Driver
  28.         url: jdbc:mysql://127.0.0.1:3306/ds1master?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  29.         username: xxx
  30.         password: xxx
  31.       ds1slave:
  32.         type: com.alibaba.druid.pool.DruidDataSource
  33.         driver-class-name: com.mysql.jdbc.Driver
  34.         url: jdbc:mysql://127.0.0.1:3306/ds1slave?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
  35.         username: xxx
  36.         password: xxx
  37.     sharding:
  38.       default-database-strategy:
  39.         inline:
  40.           sharding-column: order_id
  41.           algorithm-expression: ds${ order_id % 2}
  42.       tables:
  43.         t_order:
  44.           actual-data-nodes: ds${ 0..1}.t_order_${ 0..1}
  45.           table-strategy:
  46.             inline:
  47.               sharding-column: order_id
  48.               algorithm-expression: t_order_${ (order_id % 5) % 2}
  49.           key-generator:
  50.             column: order_id
  51.             type: SNOWFLAKE
  52.             props:
  53.               worker:
  54.                 id: 123
  55.       master-slave-rules:
  56.         ds0:
  57.           master-data-source-name: ds0master
  58.           slave-data-source-names: ds0slave
  59.         ds1:
  60.           master-data-source-name: ds1master
  61.           slave-data-source-names: ds1slave
  62.     props:
  63.       sql:
  64.         show: true # 打印sql

发表评论

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

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

相关阅读

    相关 分库分表

    分库分表 为什么分库分表 在高并发和海量数据的场景下，通过使用分库分表的手段，能够解决单机或者单库单表的性能瓶颈和压力，突破IO、连接数、硬件资源的瓶颈。当然，投入

    - 日理万妓- 日理万妓/ 0 赞/ 434 阅读

    相关 分库分表

    一. 数据切分 关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优

    小咪咪小咪咪/ 0 赞/ 532 阅读

    相关 分表分库

    一、常见面试题 1、为什么分表分库？ 2、分表分库中间件有哪些？分别有什么特点？ 3、垂直拆分还是水平拆分？有什么区别？ 二、问题分析 1、由于用户数量增长，

    野性酷女野性酷女/ 0 赞/ 547 阅读