[大数据][Mysql]使用ShardingSphere实现分库分表操作

下载ShardingSphere

二进制发布包：下载地址
Docker镜像：docker pull apache/shardingsphere-proxy

配置

Server配置

使用ShardingShphere后，应用程序通过ShardingSphere代理操作数据库，也就是程序中原本的数据源连接(JDBC、Druid)更换为连接ShardingShphere。需要在ShardingSphere的server端进行配置或权限管理
取消server.yml这块区域的注释使用默认配置即可，需要权限管理的同学可以参考：
官方文档

分库分表配置

数据源

dataSources:
  ds_0:
    url: jdbc:mysql://192.168.31.200:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: w2740201
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://192.168.31.200:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: w2740201
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

配置两个测试数据库，demo_ds_0和demo_ds_1

分库分表策略

rules:
- !SHARDING
  tables:  # tables下可对单表配置分表策略 需要全局分表策略可在defaultTableStrategy中进行配置
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline # 分表策略 声明在shardingAlgorithms中
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake # id生成策略 声明在keyGenerators中
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:  # 绑定表后，ShardingSphere会认为order表和order_item表中相关记录都在同一节点，避免ShardingSphere跨节点联查
    - t_order,t_order_item
  defaultDatabaseStrategy: # 默认分库策略
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:  # 默认分表策略
    none:

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}
  keyGenerators:
    snowflake:
      type: SNOWFLAKE
      props:
        worker-id: 123

如果不使用默认分库策略也可以像下图一样对每个表配置分库策略

读写分离

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################

schemaName: sharding_db

dataSources:
  write_ds_0:
    url: jdbc:mysql://192.168.31.200:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: w2740201
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_0:
    url: jdbc:mysql://192.168.31.200:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: w2740201
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  write_ds_1:
    url: jdbc:mysql://192.168.31.200:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: w2740201
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_1:
    url: jdbc:mysql://192.168.31.200:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: w2740201
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
- !READWRITE_SPLITTING
  dataSources:
    pr_ds_0:
      writeDataSourceName: write_ds_0
      readDataSourceNames:
        - read_ds_0
    pr_ds_1:
      writeDataSourceName: write_ds_1
      readDataSourceNames:
        - read_ds_1

此处仅作配置演示，主从配置和读写分离效果请参考另一篇博客：Mysql主从配置

创建测试数据库

CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`user_id` int(11) NOT NULL,
`status` varchar(50) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `t_order_item` ( `order_item_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `user_id` int(11) NOT NULL,
`content` varchar(255) COLLATE utf8_bin DEFAULT NULL, `status` varchar(50) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

主库

从库

查看分库分表效果