sharing

sharing

1、对存量数据进行不停机数据迁移

2、对数据进行分片

不停机数据迁移

1、表中要有严格时间顺序的字段updateTime

同步记录表（id， tableName ，lastSyncTime ）

数据分片（水平分库，水平分表） 

1、水平分库用业务ID

2、水平分表用主键ID

3、sharding-jdbc不兼容sql进行排查

4、对查询添加分片字段过滤（尽可能减少全路由查询，减少数据归并运算）

5、sharding-jdbc会对一些sql进行优化，要仔细查看优化后，是否符合业务需求

如下会自动添加排序：

6、如果使用标准路由分片，对一些范围查询要加上RangeShardingAlgorithm分片策略查询，这个只会用作查询，新增数据的时候不会走这个策略，如果有查询sql中有> ,<，>=,<=,between and等，如果不加RangeShardingAlgorithm，查询会报错

 其中工作量最大的是对历史sql不兼容性排查：

sharing-jdbc配置：

server.port=8088
#指定mybatis信息
#mybatis.config-location=classpath:mybatis-config.xml
#打印sql
spring.shardingsphere.props.sql.show=truespring.shardingsphere.datasource.names=ds,master0,master1spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://localhost:3306/my_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
#设置默认数据库
spring.shardingsphere.sharding.default-data-source-name=dsspring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://192.168.211.128:3306/my_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root#spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
#spring.shardingsphere.datasource.slave0.url=jdbc:mysql://192.168.211.128:3307/my_test?characterEncoding=utf-8
#spring.shardingsphere.datasource.slave0.username=root
#spring.shardingsphere.datasource.slave0.password=rootspring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.url=jdbc:mysql://192.168.211.128:3316/my_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root#spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
#spring.shardingsphere.datasource.slave1.url=jdbc:mysql://192.168.211.128:3317/my_test?characterEncoding=utf-8
#spring.shardingsphere.datasource.slave1.username=root
#spring.shardingsphere.datasource.slave1.password=root#指定master0为主库，slave0为它的从库
#spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
#spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0
#指定master1为主库，slave1为它的从库
#spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
#spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1#-----------------------------user----------------------------------------
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master$->{0..1}.user_$->{1..3}
#分库
spring.shardingsphere.sharding.tables.user.database-strategy.standard.sharding-column=school_id
spring.shardingsphere.sharding.tables.user.database-strategy.standard.precise-algorithm-class-name=com.fen.dou.config.DBShardingAlgorithm
#分表
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.fen.dou.config.TableShardingAlgorithm#-----------------------------course_user----------------------------------------
spring.shardingsphere.sharding.tables.course_user.actual-data-nodes=master$->{0..1}.course_user_$->{1..3}
#分库
spring.shardingsphere.sharding.tables.course_user.database-strategy.standard.sharding-column=school_id
spring.shardingsphere.sharding.tables.course_user.database-strategy.standard.precise-algorithm-class-name=com.fen.dou.config.DBShardingAlgorithm
#分表
spring.shardingsphere.sharding.tables.course_user.key-generator.column=id
spring.shardingsphere.sharding.tables.course_user.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.course_user.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.course_user.table-strategy.standard.precise-algorithm-class-name=com.fen.dou.config.TableShardingAlgorithm# 配置公共表
# spring.shardingsphere.sharding.tables.config.actual-data-nodes=master$->{0..1}.config
spring.shardingsphere.sharding.broadcast-tables=config
#失效  具体要进行排查
spring.shardingsphere.sharding.binding-tables=user,course_user

 maven配置：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=".0.0"xmlns:xsi=""xsi:schemaLocation=".0.0 .0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>sharding-jdbc</artifactId><version>1.0-SNAPSHOT</version><properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.3.7.RELEASE</spring-boot.version><sharding-sphere.version>4.1.1</sharding-sphere.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.8.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.61</version></dependency><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope><exclusions><exclusion><groupId>org.junit.vintage</groupId><artifactId>junit-vintage-engine</artifactId></exclusion></exclusions></dependency><!-- for spring boot --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>${sharding-sphere.version}</version></dependency><!-- for spring namespace --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-namespace</artifactId><version>${sharding-sphere.version}</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-core</artifactId><version>${sharding-sphere.version}</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.5</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.7.3</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency></dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-dependencies</artifactId><version>${spring-boot.version}</version><type>pom</type><scope>import</scope></dependency></dependencies></dependencyManagement><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>1.8</source><target>1.8</target><encoding>UTF-8</encoding></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><version>2.3.7.RELEASE</version><configuration><mainClass>cc.rcbb.sharding.test.Application</mainClass></configuration><executions><execution><id>repackage</id><goals><goal>repackage</goal></goals></execution></executions></plugin></plugins></build></project>

对库精确分片：  

public class DBShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<Integer> shardingValue) {String columnName = shardingValue.getColumnName();Integer databaseNum = shardingValue.getValue() % collection.size();if ("school_id".equals(columnName)){return "master_"+databaseNum;}return null;}
}

对表精确分片： 

public class TableShardingAlgorithm  implements PreciseShardingAlgorithm<Integer> {@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<Integer> shardingValue) {String columnName = shardingValue.getColumnName();Integer tableNum = shardingValue.getValue() % collection.size();if ("user".equalsIgnoreCase(shardingValue.getLogicTableName()) && "id".equals(columnName)){return "user_"+tableNum;}return null;}
}

 通过spi机制自定义主键生成策略：

import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;import java.util.Properties;
import java.util.concurrent.atomic.AtomicInteger;public class MyShardingKeyGenerator implements ShardingKeyGenerator {public static AtomicInteger ac = new AtomicInteger();@Overridepublic Comparable<?> generateKey() {return ac.getAndIncrement();}@Overridepublic String getType() {return "MyShardingKey";}@Overridepublic Properties getProperties() {return null;}@Overridepublic void setProperties(Properties properties) {}
}

如sharding-jdbc不支持CASE WHEN、HAVING、UNION (ALL)，有限支持子查询。

官网上说这个以下sql不支持，但是我能运行成功

SELECT * FROM ds.tbl_name1

包含schema

SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name

同时使用普通聚合函数和DISTINCT聚合函数

 SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))

 

SELECT COUNT(*) FROM (SELECT * FROM t_order o)这个sql官网上支持，但是我本地运行不支持