数据分片 + 读写分离

一. 配置模板

1. 配置(数据分片 + 读写分离)模板

  1. schemaName: sharding_master_slave_db
  3. dataSources:
  4.   ds0:
  5.     url: jdbc:mysql://localhost:3306/ds0
  6.     username: root
  7.     password:
  8.     autoCommit: true
  9.     connectionTimeout: 30000
  10.     idleTimeout: 60000
  11.     maxLifetime: 1800000
  12.     maximumPoolSize: 65
  13.   ds0_slave0:
  14.     url: jdbc:mysql://localhost:3306/ds0_slave0
  15.     username: root
  16.     password:
  17.     autoCommit: true
  18.     connectionTimeout: 30000
  19.     idleTimeout: 60000
  20.     maxLifetime: 1800000
  21.     maximumPoolSize: 65
  22.   ds0_slave1:
  23.     url: jdbc:mysql://localhost:3306/ds0_slave1
  24.     username: root
  25.     password:
  26.     autoCommit: true
  27.     connectionTimeout: 30000
  28.     idleTimeout: 60000
  29.     maxLifetime: 1800000
  30.     maximumPoolSize: 65
  32.   ds1:
  33.     url: jdbc:mysql://localhost:3306/ds1
  34.     username: root
  35.     password:
  36.     autoCommit: true
  37.     connectionTimeout: 30000
  38.     idleTimeout: 60000
  39.     maxLifetime: 1800000
  40.     maximumPoolSize: 65
  41.   ds1_slave0:
  42.     url: jdbc:mysql://localhost:3306/ds1_slave0
  43.     username: root
  44.     password:
  45.     autoCommit: true
  46.     connectionTimeout: 30000
  47.     idleTimeout: 60000
  48.     maxLifetime: 1800000
  49.     maximumPoolSize: 65
  50.   ds1_slave1:
  51.     url: jdbc:mysql://localhost:3306/ds1_slave1
  52.     username: root
  53.     password:
  54.     autoCommit: true
  55.     connectionTimeout: 30000
  56.     idleTimeout: 60000
  57.     maxLifetime: 1800000
  58.     maximumPoolSize: 65
  60. shardingRule:  
  61.   tables:
  62.     t_order:
  63.       actualDataNodes: ms_ds${0..1}.t_order${0..1}
  64.       tableStrategy:
  65.         inline:
  66.           shardingColumn: order_id
  67.           algorithmExpression: t_order${order_id % 2}
  68.       keyGeneratorColumnName: order_id
  69.     t_order_item:
  70.       actualDataNodes: ms_ds${0..1}.t_order_item${0..1}
  71.       tableStrategy:
  72.         inline:
  73.           shardingColumn: order_id
  74.           algorithmExpression: t_order_item${order_id % 2}  
  75.   bindingTables:
  76.     - t_order,t_order_item
  77.   broadcastTables:
  78.     - t_config
  80.   defaultDataSourceName: ds0
  81.   defaultDatabaseStrategy:
  82.     inline:
  83.       shardingColumn: user_id
  84.       algorithmExpression: ms_ds${user_id % 2}
  85.   defaultTableStrategy:
  86.     none:
  87.   defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
  89.   masterSlaveRules:
  90.       ms_ds0:
  91.         masterDataSourceName: ds0
  92.         slaveDataSourceNames:
  93.           - ds0_slave0
  94.           - ds0_slave1
  95.         loadBalanceAlgorithmType: ROUND_ROBIN
  96.         configMap:
  97.           master-slave-key0: master-slave-value0
  98.       ms_ds1:
  99.         masterDataSourceName: ds1
  100.         slaveDataSourceNames:
  101.           - ds1_slave0
  102.           - ds1_slave1
  103.         loadBalanceAlgorithmType: ROUND_ROBIN
  104.         configMap:
  105.           master-slave-key1: master-slave-value1

2. 配套 SQL