Sharding-Proxy是一个用于数据库分片的中间件，它可以将复杂的数据库分片操作简化为简单的配置。它提供了透明的分片功能，使得应用程序无需关心底层数据库的分片细节，从而降低了开发难度和维护成本。

通过Sharding-Proxy，可以将数据分散到多个数据库实例中，并支持水平扩展。它提供了多种分片算法和策略，可以根据不同的业务需求进行灵活配置。此外，Sharding-Proxy还支持读写分离、高可用性、负载均衡等功能，提高了数据库的性能和可靠性。

今天我们就来分享一下使用Sharding-Proxy 分库分表的实际操作过程。

一.安装 sharding-proxy

# 创建工作目录
mkdir /root/app
cd /root/app
# 下载sharding-proxy
wget https://mirrors.tuna.tsinghua.edu.cn/apache/shardingsphere/5.4.0/apache-shardingsphere-5.4.0-shardingsphere-proxy-bin.tar.gz
# 解压
tar -xvzf apache-shardingsphere-5.4.0-shardingsphere-proxy-bin
cd sharding-proxy

vim conf/server.yaml
# 添加如下配置
authority:
  users:
    - user: root@%
      password: root
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED

mkdir /root/app/sharding-proxy/ext-lib
cd /root/app/sharding-proxy/ext-lib
wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.11/mysql-connector-java-8.0.11.jar

vim conf/conf-sharding.yaml

databaseName: sharding_db
#
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.64.2:3306/sharding_db_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://192.168.64.2:3306/sharding_db_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
- !SHARDING
  tables:
    order_info:
      actualDataNodes: ds_${0..1}.order_info_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_no
          shardingAlgorithmName: order_info_inline
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    order_info_inline:
      type: INLINE
      props:
        algorithm-expression: order_info_${order_no % 2}
        type: DML_SHARDING_CONDITIONS

./bin/start.sh
tail -f /root/app/sharding-proxy/logs/stdout.log

spring:
  main:
    allow-bean-definition-overriding: true
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.64.2:3307/sharding_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: root

    void insert() {
        Order order1 = new Order();
        order1.setUserId(11);
        order1.setOrderNo(10);
        orderMapper.insert(order1);
    }

      @Test
    public void testList(){
        List<Order> orders = orderMapper.selectList(null);
        System.out.println(orders.size());
    }

    @Test
    public void testListByUserId(){
        LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<>();
        List<Order> orders = orderMapper.selectList(queryWrapper.eq(Order::getUserId,10));
        System.out.println(orders.size());
    }

    @Test
    public void testListByOrderNo(){
        LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<>();
          List<Order> orders = orderMapper.selectList(queryWrapper.eq(Order::getOrderNo,10));
        System.out.println(orders.size());
    }

    @Test
    public void testListByOrderNoAndUserId(){
        LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<>();
        List<Order> orders = orderMapper.selectList(queryWrapper.eq(Order::getOrderNo,10).eq(Order::getUserId,10));
        System.out.println(orders.size());
    }