2021-03-31

Sharding-Proxy简介

ShardingSphere-Proxy为数据库的代码服务端，需要独立部署，支持任何兼容MySQL协议的客户端连接。

业务应用只需要配置操作代理服务器。

• server.yml 配置：

authentication:
  users:
    root:
      password: root
 
props:
  max-connections-size-per-query: 1
  acceptor-size: 4  # The default value is available processors count * 2.
  executor-size: 2  # 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: true
  check-table-metadata-enabled: false

• config-sharding.yaml配置：

 schemaName: sharding_db

dataSources:
  ds0:
    url: jdbc:mysql://127.0.0.1:3307/course?serverTimezone=GMT%2B8
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50 
    minPoolSize: 1  
  ds1:
    url: jdbc:mysql://127.0.0.1:3307/course_test?serverTimezone=GMT%2B8
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50 
    minPoolSize: 1   

rules: 
 - !SHARDING 
  tables:
    t_order:
      actualDataNodes: ds${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order
  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

• config-replica-query.yaml配置：

 schemaName: sharding_db_1

dataSources:
  primary_ds:
    url: jdbc:mysql://127.0.0.1:3307/course?serverTimezone=GMT%2B8
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50 
    minPoolSize: 1   
  replica_ds_0:
    url: jdbc:mysql://127.0.0.1:3308/course?serverTimezone=GMT%2B8
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50 
    minPoolSize: 1  

rules:
 - !REPLICA_QUERY
  dataSources:
    pr_ds:
      name: pr_ds
      primaryDataSourceName: primary_ds
      replicaDataSourceNames:
        - replica_ds_0

• 启动Sharding-Proxy：
  
• 测试代码：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.miqi</groupId>
	<artifactId>sharding-jdbc-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>shardingjdbcdemo</name>
	<description>Demo project for Sharding-JDBC</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.20</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.0.5</version>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>

		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-generator</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.velocity</groupId>
			<artifactId>velocity-engine-core</artifactId>
			<version>2.1</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

package com.miqi.shardingjdbc;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.miqi.shardingjdbc.entity.Order;
import com.miqi.shardingjdbc.mapper.OrderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.event.TransactionalEventListener;

@SpringBootTest
class ShardingjdbcdemoApplicationTests {

    @Autowired
    private OrderMapper orderMapper;
    
    @Test
    void insertOrder(){
        Order order = new Order();
        order.setUserId(66);
        order.setStatus("miqi-55");
        orderMapper.insert(order);
    }
    
    @Test
    void  selectOrder(){
        LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<Order>();
        wrapper.eq(Order::getOrderId,584730682891677697L);
        wrapper.eq(Order::getUserId,66);
        Order order = orderMapper.selectOne(wrapper);
    }
}

执行结果：