数据分片 的实践主要有根据业务来分库,对大数量表进行分表,根据实际需要可以分库分表 ,也可以只分表不分库 。
分库 除了在拆分业务时各自使用独立的库,现在很多中小型的 SASS 系统,在租户数据隔离 这块也有采用分库 或分表 的方式实现。
此系列文章都是基于 Sharding-JDBC 4.x
版本, 在写此文章时,正式发布的是 4.1.0
版本,点此 4.x 官方文档 。
分表配置 ShardingSphere 分表配置支持 Java Api 配置,Yaml 配置,Spring Boot 配置,Spring 命名空间配置(XML配置)。
官方配置手册:Sharding-JDBC > 配置手册 ,提供了数据分片、读写分离、数据脱敏、数据分表 + 读写分离,数据分片 + 数据脱敏,治里 的配置示例。
分片配置示例和属性说明可参考 Sharding-JDBC系列(三):Sharding-JDBC分片配置示例与说明
单库分表实践 不分库只分表的业务场景也是非常多的,例如某块业务会产生大量数据,其对应的表数据量可能变得很大,只时就可以采用分表,在原有系统上改动可控,改造的风险也有限。常见的如订单表,订单详情表,支付表,支付详情表,退款表,退款详情表等可以采用分表。
下面基于 Spring Boot 框架实现分表。支持在 application.properties 文件中配置分表策略。
准备分表 在数据库里创建两张表:order_01,order_02
1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE `order_info_0` ( `id` bigint (20 ) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键' , `order_no` varchar (32 ) NOT NULL COMMENT '订单编号' , `user_id` bigint (20 ) unsigned NOT NULL COMMENT '用户ID' , PRIMARY KEY (`id`) ) ENGINE= InnoDB AUTO_INCREMENT= 15 DEFAULT CHARSET= utf8mb4 CREATE TABLE `order_info_1` ( `id` bigint (20 ) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键' , `order_no` varchar (32 ) NOT NULL COMMENT '订单编号' , `user_id` bigint (20 ) unsigned NOT NULL COMMENT '用户ID' , PRIMARY KEY (`id`) ) ENGINE= InnoDB AUTO_INCREMENT= 15 DEFAULT CHARSET= utf8mb4
添加依赖 创建 Spring Boot 项目,集成 Mybatis 和 Mybatis-Plus 操作数据库。
本示例演示:对订单表进行分表,根据用户ID 列(user_id % 2)分为 order_info_0
和 order_info_1
两个表。
pom.xml 文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 <?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.3.4.RELEASE</version > <relativePath /> </parent > <groupId > com.gxitsky</groupId > <artifactId > sharding-jdbc</artifactId > <version > 0.0.1-SNAPSHOT</version > <name > sharding-jdbc-table</name > <description > Demo project for Spring Boot</description > <properties > <java.version > 1.8</java.version > </properties > <dependencies > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-web</artifactId > <exclusions > <exclusion > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-logging</artifactId > </exclusion > </exclusions > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-log4j2</artifactId > </dependency > <dependency > <groupId > com.alibaba</groupId > <artifactId > fastjson</artifactId > <version > 1.2.67</version > </dependency > <dependency > <groupId > org.apache.commons</groupId > <artifactId > commons-lang3</artifactId > <version > 3.10</version > </dependency > <dependency > <groupId > commons-beanutils</groupId > <artifactId > commons-beanutils</artifactId > <version > 1.9.4</version > </dependency > <dependency > <groupId > com.alibaba</groupId > <artifactId > druid-spring-boot-starter</artifactId > <version > 1.1.21</version > </dependency > <dependency > <groupId > org.inurl.shardingsphere</groupId > <artifactId > sharding-jdbc-spring-boot-starter</artifactId > <version > 4.1.0</version > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > <scope > runtime</scope > </dependency > <dependency > <groupId > org.bgee.log4jdbc-log4j2</groupId > <artifactId > log4jdbc-log4j2-jdbc4.1</artifactId > <version > 1.16</version > </dependency > <dependency > <groupId > com.baomidou</groupId > <artifactId > mybatis-plus-boot-starter</artifactId > <version > 3.3.1.tmp</version > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-devtools</artifactId > <scope > runtime</scope > <optional > true</optional > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-configuration-processor</artifactId > <optional > true</optional > </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 > </dependencies > <build > <plugins > <plugin > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-maven-plugin</artifactId > </plugin > </plugins > </build > </project >
Mybatis配置 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 @Configuration @MapperScan(basePackages = "com.gxitsky.shardingjdbc.table.mapper") public class MybatisConfig { @Bean public PaginationInterceptor paginationInterceptor () { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true )); return paginationInterceptor; } }
配置文件设置 主配置文件 application.properties
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 spring.profiles.active =sharding_table spring.application.name =sharding-jdbc spring.mvc.throw-exception-if-no-handler-found =true spring.resources.add-mappings =false mybatis-plus.mapper-locations =classpath*:/mapper/**/*.xml mybatis-plus.type-aliases-package =com.gxitsky.shardingjdbc.table.entity mybatis-plus.configuration.map-underscore-to-camel-case =true spring.jackson.time-zone =GMT+8 spring.jackson.date-format =yyyy-MM-dd HH:mm:ss spring.jackson.default-property-inclusion =non_empty
分表配置文件 特别注意: 配置数据源的属性 是跟数据源类中的定义走的,不同的数据源的属性并不都一致,但大部分是一致的。
Spring Boot 默认提供的数据源 com.zaxxer.hikari.HikariDataSource 的连接属性 是:jdbc-url
使用 com.alibaba.druid.pool.DruidDataSource 数据源的连接属性 是:url
application-sharding_table.properties
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 spring.shardingsphere.props.sql.simple =true spring.shardingsphere.datasource.names =ds0 spring.shardingsphere.datasource.ds0.type =com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds0.driver-class-name =net.sf.log4jdbc.sql.jdbcapi.DriverSpy spring.shardingsphere.datasource.ds0.url =jdbc:log4jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&serverTimezone=GMT%2B8 spring.shardingsphere.datasource.ds0.username =root spring.shardingsphere.datasource.ds0.password =123456 spring.shardingsphere.sharding.tables.order_info.actual-data-nodes =ds0.order_info_$->{0..1} spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column =user_id spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression =order_info_$->{user_id % 2} spring.shardingsphere.sharding.binding-tables =order_info
上面示例的分片配置是基于行表达式 ,数据用户ID(user_id % 2)取模(奇数/偶数)分 2 个表。
解决数据源冲突 如果数据源使用的是 druid-spring-boot-starter 包,Druid 自动配置创建的数据源不能被 Sharding-JDBC 使用,需要在入口类配置排除 Druid 数据源自动配置,启用 spring.shardingsphere.datasource 数据源。
1 2 3 4 5 6 7 8 @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class) public class ShardingJdbcApplication { public static void main (String[] args) { SpringApplication.run(ShardingJdbcApplication.class, args); } }
业务代码示例 Entity OrderInfo 订单信息实体类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 @Data @Accessors(chain = true) @TableName(value = "order_info") public class OrderInfo implements Serializable { private static final long serialVersionUID = 7228334740131864851L ; @TableId(type = IdType.AUTO) private Long id; private String orderNo; private Long userId; public OrderInfo () { } public OrderInfo (String orderNo, Long userId) { this .orderNo = orderNo; this .userId = userId; } }
Controller OrderController
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 @RestController @RequestMapping("/order") public class OrderController { private static final Logger logger = LogManager.getLogger(OrderController.class); @Autowired private OrderService orderService; @GetMapping("/userId") public List<OrderInfo> orderList (Long userId) { return orderService.orderList(userId); } @PostMapping("/add") public int addOrder (Long userId) { return orderService.addOrder(userId); } @PostMapping("/userIdList") public List<OrderInfo> userIdList (@RequestParam List<Long> idList) { return orderService.userIdList(idList); } @PostMapping("/ge") public List<OrderInfo> ge (Long userId) { return orderService.ge(userId); } @PostMapping("/between") public List<OrderInfo> between (Long start, Long end) { return orderService.between(start, end); } }
Service OrderService 接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 public interface OrderService { List<OrderInfo> orderList (Long userId) ; int addOrder (Long userId) ; List<OrderInfo> userIdList (List<Long> userIdList) ; List<OrderInfo> ge (Long userId) ; List<OrderInfo> between (Long start, Long end) ; }
OrderServiceImpl 接口实现类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 @Service public class OrderServiceImpl implements OrderService { @Autowired private OrderMapper orderMapper; @Override public List<OrderInfo> orderList (Long userId) { LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(OrderInfo::getUserId, userId); return orderMapper.selectList(queryWrapper); } @Override public int addOrder (Long userId) { long millis = System.currentTimeMillis(); return orderMapper.insert(new OrderInfo(String.valueOf(millis), userId)); } @Override public List<OrderInfo> userIdList (List<Long> userIdList) { LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.in(OrderInfo::getUserId, userIdList); return orderMapper.selectList(queryWrapper); } @Override public List<OrderInfo> ge (Long userId) { LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.ge(OrderInfo::getUserId, userId); return orderMapper.selectList(queryWrapper); } @Override public List<OrderInfo> between (Long start, Long end) { LambdaQueryWrapper<OrderInfo> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.between(OrderInfo::getUserId, start, end); return orderMapper.selectList(queryWrapper); } }
Mapper OrderMapper 接口
1 2 3 4 5 6 @Repository public interface OrderMapper extends BaseMapper <OrderInfo > {}
分片日志分析 启动日志 启动项目,查看启动的日志打印:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 2020-09-27 23:07:20,381 INFO [LogId:] [o.a.s.c.l.ConfigurationLogger->log:104] [restartedMain] ShardingRuleConfiguration: bindingTables :- order_info tables : order_info : actualDataNodes : ds0.order_info_$->{0..1} logicTable : order_info tableStrategy : inline : algorithmExpression : order_info_$->{user_id % 2} shardingColumn : user_id 2020-09-27 23:07:20,381 INFO [LogId:] [o.a.s.c.l.ConfigurationLogger->log:104] [restartedMain] Properties: sql.simple : 'true' 2020-09-27 23:14:42,175 INFO [LogId:] [o.a.s.c.m.ShardingMetaDataLoader->loadShardingSchemaMetaData:131] [restartedMain] Loading 1 logic tables' meta data. 2020-09-27 23:14:42,189 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info_0` WHERE 1 != 1; 2020-09-27 23:14:42,195 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-27 23:14:42,203 INFO [LogId:] [o.a.s.s.p.b.m.s.SchemaMetaDataLoader->load:70] [restartedMain] Loading 4 tables' meta data. 2020-09-27 23:14:42,209 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info` WHERE 1 != 1; 2020-09-27 23:14:42,209 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-27 23:14:42,214 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info_0` WHERE 1 != 1; 2020-09-27 23:14:42,216 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-27 23:14:42,221 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_info_1` WHERE 1 != 1; 2020-09-27 23:14:42,221 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-27 23:14:42,226 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [restartedMain] SELECT * FROM `order_item` WHERE 1 != 1; 2020-09-27 23:14:42,227 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [restartedMain] |---|--------------|---------|----------| |id |order_info_id |order_no |good_name | |---|--------------|---------|----------| |---|--------------|---------|----------|
精准新增 Postman 或浏览器请求新增的接口,参数 userId 分别为 1001,1002,1003,1004
1 url : localhost:8080/order/add?userId=1008
查看日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 2020-09-27 23:19:58,644 INFO [LogId:] [o.a.j.l.DirectJDKLog->log:173] [http-nio-8080-exec-1] Initializing Spring DispatcherServlet 'dispatcherServlet' 2020-09-27 23:19:58,645 INFO [LogId:] [o.s.w.s.FrameworkServlet->initServletBean:525] [http-nio-8080-exec-1] Initializing Servlet 'dispatcherServlet' 2020-09-27 23:19:58,651 INFO [LogId:] [o.s.w.s.FrameworkServlet->initServletBean:547] [http-nio-8080-exec-1] Completed initialization in 6 ms 2020-09-27 23:19:59,174 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] INSERT INTO order_info_0 ( order_no, user_id ) VALUES ('1601219998675', 1008) 2020-09-27 23:19:59,246 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] |--------------| |generated_key | |--------------| |20 | |--------------| 2020-09-27 23:22:35,349 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] INSERT INTO order_info_1 ( order_no, user_id ) VALUES ('1601220155347', 1009) 2020-09-27 23:22:35,406 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] |--------------| |generated_key | |--------------| |22 | |--------------|
从插入数据上看分表已经实现了。
精准查询 Postman 或浏览器请求查询的接口,参数 userId 分别为 1001,1002,1003,1004
1 url :llocalhost:8080/order/userId?userId=1003
日志:精确路由到具体的物理分表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 2020-09-27 23:24:17,780 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-8] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id = 1003) 2020-09-27 23:24:17,793 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-8] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |14 |1601218184873 |1003 | |15 |1601218186473 |1003 | |16 |1601218187248 |1003 | |---|--------------|--------| 2020-09-27 23:25:51,600 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-2] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id = 1004) 2020-09-27 23:25:51,602 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-2] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |8 |1601218192586 |1004 | |9 |1601218193627 |1004 | |10 |1601218194203 |1004 | |11 |1601218194945 |1004 | |12 |1601218195693 |1004 | |13 |1601218196411 |1004 | |14 |1601218197192 |1004 | |---|--------------|--------|
分表联查 1 localhost :8080/order/userIdList?idList=1002&idList=1003
日志:会根据传入的分表字段的值,执行范围查询(between
与 in
),查询满足分表规则的多个表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 2020-09-27 23:57:37,499 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-4] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id IN (1001,1002)) 2020-09-27 23:57:37,501 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-4] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id IN (1001,1002)) 2020-09-27 23:57:37,503 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-4] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |15 |1601218216439 |1002 | |---|--------------|--------| 2020-09-27 23:57:37,504 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-4] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |17 |1601218213989 |1001 | |---|--------------|--------|
自定义分片算法 上面的分片配置都是基于 行表达式 在配置文件中设置,此方式方便,但不灵活,行表达式不支持范围查询(Between...and, >, >=, <, <=
)。
自定义分片算法使用最多的是标准分片策略 ,Sharding-JDBC 提供了标准分片策略配置类:StandardShardingStrategyConfiguration
标准分片策略配置类 StandardShardingStrategyConfiguration :该配置类中有三个属性:分别是 shardingColumn ,用于分片的列名;PreciseShardingAlgorithm 用于 =
和 in
的精确分片算法;RangeShardingAlgorithm 用于 between and, >, >=, <, <=
的范围分片算法。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 @Getter public final class StandardShardingStrategyConfiguration implements ShardingStrategyConfiguration { private final String shardingColumn; private final PreciseShardingAlgorithm preciseShardingAlgorithm; private final RangeShardingAlgorithm rangeShardingAlgorithm; public StandardShardingStrategyConfiguration (final String shardingColumn, final PreciseShardingAlgorithm preciseShardingAlgorithm) { this (shardingColumn, preciseShardingAlgorithm, null ); } public StandardShardingStrategyConfiguration (final String shardingColumn, final PreciseShardingAlgorithm preciseShardingAlgorithm, final RangeShardingAlgorithm rangeShardingAlgorithm) { Preconditions.checkArgument(!Strings.isNullOrEmpty(shardingColumn), "ShardingColumns is required." ); Preconditions.checkNotNull(preciseShardingAlgorithm, "PreciseShardingAlgorithm is required." ); this .shardingColumn = shardingColumn; this .preciseShardingAlgorithm = preciseShardingAlgorithm; this .rangeShardingAlgorithm = rangeShardingAlgorithm; } }
精准分片算法接口 PreciseShardingAlgorithm :用于 =
和 in
的精确分片算法。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 public interface PreciseShardingAlgorithm <T extends Comparable <?>> extends ShardingAlgorithm { String doSharding (Collection<String> availableTargetNames, PreciseShardingValue<T> shardingValue) ; }
范围分片算法接口 RangeShardingAlgorithm :用于 between and, >, >=, <, <=
的范围分片
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 public interface RangeShardingAlgorithm <T extends Comparable <?>> extends ShardingAlgorithm { Collection<String> doSharding (Collection<String> availableTargetNames, RangeShardingValue<T> shardingValue) ; }
当分片运算符是等于号时,路由结果将落入单库(表),当分片运算符是BETWEEN
或IN
时,则路由结果不一定落入唯一的库(表),因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。详细参考 路由引擎 描述 。
自定义标准分片算法 在上面行表达式的示例中,order_info 表改为 4 个分表:order_info_0, order_info_1, order_info_2, order_info_3
。
自定义分片算法使用最多的是 精准分片算法 和 范围分片算法 ,精准分片算法用于 =
和 in
SQL 操作,范围分片算法用于 between and, >, >=, <, <=
的SQL操作。
精准分片算法,需要实现 PreciseShardingAlgorithm 接口,重写里面的 doSharding 方法;
范围分片算法,需要实现 RangeShardingAlgorithm 接口,重写里面的 doSharding 方法。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 @Component public class UserIdShardingAlgorithm implements PreciseShardingAlgorithm <Long >, RangeShardingAlgorithm <Long > { private static final Logger logger = LogManager.getLogger(UserIdShardingAlgorithm.class); private static final int TABLES = 4 ; private static List<String> SUFFIX_LIST = new ArrayList<>(); static { for (int i = 0 ; i < TABLES; i++) { SUFFIX_LIST.add(String.valueOf(i)); } } @Override public String doSharding (Collection<String> availableTargetNames, PreciseShardingValue<Long> preciseShardingValue) { logger.info("----->availableTargetNames:{}" , JSON.toJSONString(availableTargetNames)); logger.info("----->preciseShardingValue:{}" , JSON.toJSONString(preciseShardingValue)); String logicTableName = preciseShardingValue.getLogicTableName(); Long userId = preciseShardingValue.getValue(); String index = hashCodeIndex(userId); return logicTableName + "_" + index; } @Override public Collection<String> doSharding (Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) { logger.info("----->availableTargetNames:{}" , JSON.toJSONString(availableTargetNames)); logger.info("----->rangeShardingValue:{}" , JSON.toJSONString(rangeShardingValue)); String logicTableName = rangeShardingValue.getLogicTableName(); Range<Long> range = rangeShardingValue.getValueRange(); logger.info("----->range lowerEndpoint:{}" , range.hasLowerBound() ? range.lowerEndpoint() : null ); logger.info("----->range upperEndpoint:{}" , range.hasUpperBound() ? range.upperEndpoint() : null ); if (range.hasLowerBound() && range.hasUpperBound()) { List<String> indexList = new ArrayList<>(); for (Long userId = range.lowerEndpoint(); userId <= range.upperEndpoint(); userId++) { String index = this .hashCodeIndex(userId); indexList.add(index); } return indexList.stream().map(index -> logicTableName + "_" + index).collect(Collectors.toList()); } return SUFFIX_LIST.stream().map(index -> logicTableName + "_" + index).collect(Collectors.toList()); } private String hashCodeIndex (Long userId) { int index = (userId.hashCode()) % TABLES; return String.valueOf(index); } }
Spring Boot 配置示例 上面示例工程,根配置文件设置启用此自定义分片算法的配置文件 。
application-sharding_table_cust.properties
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 spring.shardingsphere.props.sql.simple =true spring.shardingsphere.datasource.names =ds0 spring.shardingsphere.datasource.ds0.type =com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds0.driver-class-name =net.sf.log4jdbc.sql.jdbcapi.DriverSpy spring.shardingsphere.datasource.ds0.url =jdbc:log4jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&serverTimezone=GMT%2B8 spring.shardingsphere.datasource.ds0.username =root spring.shardingsphere.datasource.ds0.password =123456 spring.shardingsphere.sharding.tables.order_info.actual-data-nodes =ds0.order_info_$->{0..3} spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.sharding-column =user_id spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.preciseAlgorithmClassName =com.gxitsky.shardingjdbc.table.common.config.UserIdShardingAlgorithm spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.rangeAlgorithmClassName =com.gxitsky.shardingjdbc.table.common.config.UserIdShardingAlgorithm spring.shardingsphere.sharding.binding-tables =order_info
精准查询 1 url : localhost:8080/order/userId?userId=1001
日志:精准路由到具体的物理表
1 2 3 4 5 6 7 8 9 10 11 2020-09-28 15:37:07,080 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-6] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"] 2020-09-28 15:37:07,090 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-6] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1001} 2020-09-28 15:37:07,101 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-6] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id = 1001) 2020-09-28 15:37:07,111 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-6] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |1 |1601276568851 |1001 | |2 |1601276570891 |1001 | |---|--------------|--------|
精准新增 1 url : localhost:8080/order/add?userId=1007
日志 :精准路由到具体的物理表
1 2 3 4 5 6 7 8 9 10 2020-09-28 15:38:18,224 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-9] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"] 2020-09-28 15:38:18,225 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-9] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1007} 2020-09-28 15:38:18,228 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-9] INSERT INTO order_info_3 ( order_no, user_id ) VALUES ('1601278698185', 1007) 2020-09-28 15:38:18,299 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-9] |--------------| |generated_key | |--------------| |5 | |--------------|
IN 条件查询 1 url : localhost:8080/order/userIdList?idList=1002&idList=1003
日志 :IN 条件查询是精准查询,会精确路由到具体的物理分表。
注 :从下面日志可以看出,会按分表执行多条SQL,但 SQL 的 where 条件是一致的,where 条件不会精确到每一个分表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 2020-09-28 15:51:14,003 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-9] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"] 2020-09-28 15:51:14,004 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-9] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1002} 2020-09-28 15:51:14,004 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:39] [http-nio-8080-exec-9] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"] 2020-09-28 15:51:14,005 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:40] [http-nio-8080-exec-9] ----->preciseShardingValue:{"columnName":"user_id","logicTableName":"order_info","value":1003} 2020-09-28 15:51:14,007 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-9] SELECT id,order_no,user_id FROM order_info_2 WHERE (user_id IN (1002,1003)) 2020-09-28 15:51:14,008 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-9] SELECT id,order_no,user_id FROM order_info_3 WHERE (user_id IN (1002,1003)) 2020-09-28 15:51:14,010 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-9] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |1 |1601276582200 |1002 | |2 |1601276583184 |1002 | |---|--------------|--------| 2020-09-28 15:51:14,011 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-9] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |1 |1601276586103 |1003 | |2 |1601276587038 |1003 | |---|--------------|--------|
Between查询 1 url : localhost:8080/order/between?start=1006&end=1008
日志 :范围有是闭区间,有上限值和下限值,计算出的所有分表有:order_info_0,order_info_2,order_info_3
注:可以看到 SQL 的 where 条件 是不会按分表来拆分的,查询所有分表的 where 条件 是一致的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 2020-09-28 15:40:08,238 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:50] [http-nio-8080-exec-1] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"] 2020-09-28 15:40:08,238 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:51] [http-nio-8080-exec-1] ----->rangeShardingValue:{"columnName":"user_id","logicTableName":"order_info","valueRange":{"empty":false}} 2020-09-28 15:40:08,239 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:55] [http-nio-8080-exec-1] ----->range lowerEndpoint:1006 2020-09-28 15:40:08,239 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:56] [http-nio-8080-exec-1] ----->range upperEndpoint:1008 2020-09-28 15:40:08,241 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id BETWEEN 1006 AND 1008) 2020-09-28 15:40:08,243 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] SELECT id,order_no,user_id FROM order_info_2 WHERE (user_id BETWEEN 1006 AND 1008) 2020-09-28 15:40:08,244 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-1] SELECT id,order_no,user_id FROM order_info_3 WHERE (user_id BETWEEN 1006 AND 1008) 2020-09-28 15:40:08,246 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-28 15:40:08,247 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |3 |1601276599588 |1006 | |4 |1601276600374 |1006 | |---|--------------|--------| 2020-09-28 15:40:08,249 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-1] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |3 |1601276603232 |1007 | |4 |1601276603975 |1007 | |5 |1601278698185 |1007 | |---|--------------|--------|
大于等于查询 1 url : localhost:8080/order/ge?userId=1006
日志 :查询条件是开区间,查询所有分表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 2020-09-28 15:49:00,023 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:50] [http-nio-8080-exec-5] ----->availableTargetNames:["order_info_0","order_info_1","order_info_2","order_info_3"] 2020-09-28 15:49:00,023 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:51] [http-nio-8080-exec-5] ----->rangeShardingValue:{"columnName":"user_id","logicTableName":"order_info","valueRange":{"empty":false}} 2020-09-28 15:49:00,024 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:55] [http-nio-8080-exec-5] ----->range lowerEndpoint:1006 2020-09-28 15:49:00,024 INFO [LogId:] [c.g.s.t.c.c.UserIdShardingAlgorithm->doSharding:56] [http-nio-8080-exec-5] ----->range upperEndpoint:null 2020-09-28 15:49:00,026 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_0 WHERE (user_id >= 1006) 2020-09-28 15:49:00,027 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_1 WHERE (user_id >= 1006) 2020-09-28 15:49:00,028 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_2 WHERE (user_id >= 1006) 2020-09-28 15:49:00,029 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->sqlOccurred:228] [http-nio-8080-exec-5] SELECT id,order_no,user_id FROM order_info_3 WHERE (user_id >= 1006) 2020-09-28 15:49:00,030 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-28 15:49:00,031 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] |---|---------|--------| |id |order_no |user_id | |---|---------|--------| |---|---------|--------| 2020-09-28 15:49:00,032 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |3 |1601276599588 |1006 | |4 |1601276600374 |1006 | |---|--------------|--------| 2020-09-28 15:49:00,033 INFO [LogId:] [n.s.l.l.s.Slf4jSpyLogDelegator->resultSetCollected:610] [http-nio-8080-exec-5] |---|--------------|--------| |id |order_no |user_id | |---|--------------|--------| |3 |1601276603232 |1007 | |4 |1601276603975 |1007 | |5 |1601278698185 |1007 | |---|--------------|--------|
相关参考
文章中示例代码-demo
使用Sharding-JDBC 分库分表