1.引入依赖

  1. <dependency>
  2.     <groupId>com.xy</groupId>
  3.     <artifactId>xy-core-framework-dal-shardingsphere</artifactId>
  4.     <version>${xy-core-framework-dal-shardingsphere.version}</version>
  5. </dependency>

2.添加配置

2.1读写分离示列

  1. spring.shardingsphere.datasource.names=master,slave
  3. # 主数据源
  4. spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.master.username=root
  8. spring.shardingsphere.datasource.master.password=123456
  10. # 从数据源
  11. spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
  12. spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
  13. spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
  14. spring.shardingsphere.datasource.slave.username=root
  15. spring.shardingsphere.datasource.slave.password=123456
  17. # 读写分离配置
  18. spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
  19. spring.shardingsphere.masterslave.name=dataSource
  20. spring.shardingsphere.masterslave.master-data-source-name=master
  21. spring.shardingsphere.masterslave.slave-data-source-names=slave
  23. # 显示SQL
  24. spring.shardingsphere.props.sql.show=true

2.2垂直拆分

不同的表在不同的库中 
  1. spring.shardingsphere.datasource.names=ds0,ds1
  3. # 数据源
  4. spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.ds0.username=root
  8. spring.shardingsphere.datasource.ds0.password=123456
  10. spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
  11. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
  13. spring.shardingsphere.datasource.ds1.username=root
  14. spring.shardingsphere.datasource.ds1.password=123456
  16. # 绑定loudong表所在节点
  17. spring.shardingsphere.sharding.tables.loudong.actual-data-nodes=ds1.loudong
  19. # 绑定user表所在节点
  20. spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user
  21. spring.shardingsphere.sharding.tables.user.key-generator.column=id
  22. spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

2.3JDBC 垂直拆分+ 读写分离

不同的表在不同的库中 
  1. spring.shardingsphere.datasource.names=ds0,ds0slave,ds1,ds1slave
  3. # 数据源
  4. spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.ds0.username=root
  8. spring.shardingsphere.datasource.ds0.password=123456
  10. spring.shardingsphere.datasource.ds0slave.type=com.alibaba.druid.pool.DruidDataSource
  11. spring.shardingsphere.datasource.ds0slave.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.ds0slave.url=jdbc:mysql://localhost:3306/ds0slave?characterEncoding=utf-8
  13. spring.shardingsphere.datasource.ds0slave.username=root
  14. spring.shardingsphere.datasource.ds0slave.password=123456
  16. spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
  17. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
  18. spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
  19. spring.shardingsphere.datasource.ds1.username=root
  20. spring.shardingsphere.datasource.ds1.password=123456
  22. spring.shardingsphere.datasource.ds1slave.type=com.alibaba.druid.pool.DruidDataSource
  23. spring.shardingsphere.datasource.ds1slave.driver-class-name=com.mysql.jdbc.Driver
  24. spring.shardingsphere.datasource.ds1slave.url=jdbc:mysql://localhost:3306/ds1slave?characterEncoding=utf-8
  25. spring.shardingsphere.datasource.ds1slave.username=root
  26. spring.shardingsphere.datasource.ds1slave.password=123456
  28. # 绑定loudong表所在节点
  29. spring.shardingsphere.sharding.tables.loudong.actual-data-nodes=ds1.loudong
  31. # 绑定user表所在节点
  32. spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user
  33. spring.shardingsphere.sharding.tables.user.key-generator.column=id
  34. spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
  36. # 读写分离
  37. spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=ds0
  38. spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=ds0slave
  40. spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=ds1
  41. spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=ds1slave

2.4不分库，只分表

  1. spring.shardingsphere.datasource.names=master
  3. # 数据源
  4. spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.master.username=root
  8. spring.shardingsphere.datasource.master.password=123456
  10. # 分表配置
  11. spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_${0..3}
  13. # 自定义分表算法
  14. #spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
  15. #spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.cxytiandi.sharding.algorithm.MyPreciseShardingAlgorithm
  17. # inline 表达式
  18. spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
  19. spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_${id.longValue() % 4}

2.5不分库,只分表+读写分离

  1. spring.shardingsphere.datasource.names=master,slave
  3. # 主数据源
  4. spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.master.username=root
  8. spring.shardingsphere.datasource.master.password=123456
  10. # 从数据源
  11. spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
  12. spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
  13. spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
  14. spring.shardingsphere.datasource.slave.username=root
  15. spring.shardingsphere.datasource.slave.password=123456
  17. # 分表配置
  18. spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_${0..3}
  19. spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
  20. spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_${id.longValue() % 4}
  22. # 读写分离配置
  23. spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
  24. spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
  26. # 显示SQL
  27. spring.shardingsphere.props.sql.show=true

2.6分库分表

  1. spring.shardingsphere.datasource.names=master
  3. # 数据源
  4. spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.master.username=root
  8. spring.shardingsphere.datasource.master.password=123456
  10. # 分表配置
  11. spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_${0..3}
  13. # 自定义分表算法
  14. #spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
  15. #spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.cxytiandi.sharding.algorithm.MyPreciseShardingAlgorithm
  17. # inline 表达式
  18. spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
  19. spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_${id.longValue() % 4}

2.7分库分表+读写分离

  1. spring.shardingsphere.datasource.names=master0,master0slave,master1,master1slave
  3. # 数据源
  4. spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master0.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.master0.username=root
  8. spring.shardingsphere.datasource.master0.password=123456
  10. spring.shardingsphere.datasource.master0slave.type=com.alibaba.druid.pool.DruidDataSource
  11. spring.shardingsphere.datasource.master0slave.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.master0slave.url=jdbc:mysql://localhost:3306/ds0slave?characterEncoding=utf-8
  13. spring.shardingsphere.datasource.master0slave.username=root
  14. spring.shardingsphere.datasource.master0slave.password=123456
  16. spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
  17. spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
  18. spring.shardingsphere.datasource.master1.url=jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
  19. spring.shardingsphere.datasource.master1.username=root
  20. spring.shardingsphere.datasource.master1.password=123456
  22. spring.shardingsphere.datasource.master1slave.type=com.alibaba.druid.pool.DruidDataSource
  23. spring.shardingsphere.datasource.master1slave.driver-class-name=com.mysql.jdbc.Driver
  24. spring.shardingsphere.datasource.master1slave.url=jdbc:mysql://localhost:3306/ds1slave?characterEncoding=utf-8
  25. spring.shardingsphere.datasource.master1slave.username=root
  26. spring.shardingsphere.datasource.master1slave.password=123456
  28. # 分表配置
  29. spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->{0..2}
  30. spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
  31. spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 3}
  32. spring.shardingsphere.sharding.tables.user.key-generator.column=id
  33. spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
  35. # 分库配置
  36. spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
  37. spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
  39. # 不分库分表的数据源指定
  40. #spring.shardingsphere.sharding.default-data-source-name=ds0
  42. # 广播表，每个节点复制一份，适用于配置类的数据
  43. #spring.shardingsphere.sharding.broadcast-tables=loudong
  45. # 读写分离
  46. spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
  47. spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave
  49. spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1
  50. spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave

2.8分库分表+读写分离案例

范围分表+取模=无限扩容 
  1. spring.shardingsphere.datasource.names=master0,master0slave,master1,master1slave,masters0,master0slave,masters1,master1slave
  3. # 数据源
  4. spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
  5. spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master0.url=jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
  7. spring.shardingsphere.datasource.master0.username=root
  8. spring.shardingsphere.datasource.master0.password=123456
  10. spring.shardingsphere.datasource.master0slave.type=com.alibaba.druid.pool.DruidDataSource
  11. spring.shardingsphere.datasource.master0slave.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.master0slave.url=jdbc:mysql://localhost:3306/ds0slave?characterEncoding=utf-8
  13. spring.shardingsphere.datasource.master0slave.username=root
  14. spring.shardingsphere.datasource.master0slave.password=123456
  16. spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
  17. spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
  18. spring.shardingsphere.datasource.master1.url=jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
  19. spring.shardingsphere.datasource.master1.username=root
  20. spring.shardingsphere.datasource.master1.password=123456
  22. spring.shardingsphere.datasource.master1slave.type=com.alibaba.druid.pool.DruidDataSource
  23. spring.shardingsphere.datasource.master1slave.driver-class-name=com.mysql.jdbc.Driver
  24. spring.shardingsphere.datasource.master1slave.url=jdbc:mysql://localhost:3306/ds1slave?characterEncoding=utf-8
  25. spring.shardingsphere.datasource.master1slave.username=root
  26. spring.shardingsphere.datasource.master1slave.password=123456
  28. spring.shardingsphere.datasource.masters0.type=com.alibaba.druid.pool.DruidDataSource
  29. spring.shardingsphere.datasource.masters0.driver-class-name=com.mysql.jdbc.Driver
  30. spring.shardingsphere.datasource.masters0.url=jdbc:mysql://localhost:3306/ds_2?characterEncoding=utf-8
  31. spring.shardingsphere.datasource.masters0.username=root
  32. spring.shardingsphere.datasource.masters0.password=123456
  34. spring.shardingsphere.datasource.masters1.type=com.alibaba.druid.pool.DruidDataSource
  35. spring.shardingsphere.datasource.masters1.driver-class-name=com.mysql.jdbc.Driver
  36. spring.shardingsphere.datasource.masters1.url=jdbc:mysql://localhost:3306/ds_3?characterEncoding=utf-8
  37. spring.shardingsphere.datasource.masters1.username=root
  38. spring.shardingsphere.datasource.masters1.password=123456
  40. # 分表配置
  41. spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->{0..2},dss$->{0..1}.user_$->{0..2}
  42. spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
  43. spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 3}
  45. # 分库配置
  46. spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=id
  47. spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.cxytiandi.sharding.algorithm.MyPreciseShardingAlgorithm
  49. # 不分库分表的数据源指定
  50. spring.shardingsphere.sharding.default-data-source-name=ds0
  52. # 广播表，每个节点复制一份，适用于配置类的数据
  53. #spring.shardingsphere.sharding.broadcast-tables=loudong
  55. # 读写分离
  56. spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
  57. spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave
  59. spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1
  60. spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave
  62. spring.shardingsphere.sharding.master-slave-rules.dss0.master-data-source-name=masters0
  63. spring.shardingsphere.sharding.master-slave-rules.dss0.slave-data-source-names=master0slave
  65. spring.shardingsphere.sharding.master-slave-rules.dss1.master-data-source-name=masters1
  66. spring.shardingsphere.sharding.master-slave-rules.dss1.slave-data-source-names=master1slave

3.强制选库

TODO

4.本地事务

TODO

5.分布式事务

TODO