ShardingSphere -04- ShardingSphere-Proxy(水平分表，水平分库)

ShardingSphere-Proxy

介绍

 定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL 版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat 等)操作数据，对 DBA 更加友好。

• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用。
• 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

下载

https://shardingsphere.apache.org/document/current/cn/downloads/#%E5%85%A8%E9%83%A8%E7%89%88%E6%9C%AC

修改jar包

将lib下的包后缀全部换成jar

修改文件

• 进入 conf 目录，修改文件 server.yaml，打开两段内容注释

  authentication:
    users:
      root:
        password: root
      sharding:
        password: sharding 
        authorizedSchemas: sharding_db
  
  props:
    max.connections.size.per.query: 1
    acceptor.size: 16  # The default value is available processors count * 2.
    executor.size: 16  # Infinite by default.
    proxy.frontend.flush.threshold: 128  # The default value is 128.
      # LOCAL: Proxy will run with LOCAL transaction.
      # XA: Proxy will run with XA transaction.
      # BASE: Proxy will run with B.A.S.E transaction.
    proxy.transaction.type: LOCAL
    proxy.opentracing.enabled: false
    proxy.hint.enabled: false
    query.with.cipher.column: true
    sql.show: false
    allow.range.query.with.inline.sharding: false
  

• 进入 conf 目录，修改 config-sharding.yaml

需要添加mysq驱动

dataSources:
  ds_0:
    url: jdbc:mysql://localhost:3306/sharding_sphere?characterEncoding=UTF-8&serverTimezone=UTC
    username: jiang
    password: jiang
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0}.tb_order_${1..2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: tb_order_${order_id % 2+1}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - tb_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${0}
  defaultTableStrategy:
    none:

以水平分表为例

启动

#默认是3307端口，可以自行指定
start.bat 3308

如果出现未找到类的情况，应该是jar包问题,请修改lib下所有包的后缀为jar

连接数据库

mysql  -P3308 -u root -p

• 3308是启动端口
• 用户名为root，密码也是root,在配置文件中指定了

代理数据库中的库

与我们配置中设置的一样

水平分表

建表

CREATE TABLE `tb_order_1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10,2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `tb_order_2` (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10,2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

配置文件

dataSources:
  ds_0:
    url: jdbc:mysql://localhost:3306/sharding_sphere?characterEncoding=UTF-8&serverTimezone=UTC
    username: jiang
    password: jiang
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0}.tb_order_${1..2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: tb_order_${order_id % 2+1}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - tb_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${0}
  defaultTableStrategy:
    none:

连接数据库

mysql  -P3308 -u root -p

 查看数据库和数据表

可以看到我们这里只有一个t_order的表，而不是tb_order1和tb_order2两个表。至于为什么会是t_order而不是tb_order，看了一眼配置，我在tables里设置的就是t_order

操作

插入

insert into t_order values(1,10.0,1123,'管理员');
insert into t_order values(2,10.0,1123,'管理员');
insert into t_order values(3,10.0,1123,'管理员');
insert into t_order values(4,10.0,1123,'管理员');

 查看

查询

水平分库

建表

CREATE TABLE `tb_order_1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10,2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `tb_order_2` (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10,2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

配置

dataSources:
  ds_0:
    url: jdbc:mysql://localhost:3306/sharding_sphere_1?characterEncoding=UTF-8&serverTimezone=UTC
    username: jiang
    password: jiang
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://localhost:3306/sharding_sphere_2?characterEncoding=UTF-8&serverTimezone=UTC
    username: jiang
    password: jiang
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    tb_order:
      actualDataNodes: ds_${0,1}.tb_order_${1..2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: tb_order_${order_id % 2+1}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - tb_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id%2}
  defaultTableStrategy:
    none:

启动并连接数据库

start.bat 3308

mysql  -P3308 -u root -p

 查看数据库和数据表

操作

插入

insert into tb_order values(1,10.0,1123,'管理员');
insert into tb_order values(2,10.0,1123,'管理员');
insert into tb_order values(3,10.0,1123,'管理员');
insert into tb_order values(4,10.0,1123,'管理员');
insert into tb_order values(5,10.0,1124,'管理员');
insert into tb_order values(6,10.0,1124,'管理员');
insert into tb_order values(7,10.0,1124,'管理员');
insert into tb_order values(8,10.0,1124,'管理员');

查询