文末有demo的git地址
Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈,它由 JDBC、Proxy 和 Sidecar(规划中)这 3 款既能够独立部署,又支持混合部署配合使用的产品组成。
目前,数据分片、读写分离、数据加密、影子库压测等功能,以及对 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协议的支持,均通过插件的方式织入项目。
ShardingSphere-JDBC
定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库,以 jar 包形式提供服务,无需额外部署和依赖,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。
- 适用于任何基于 JDBC 的 ORM 框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
- 支持任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP 等。
- 支持任意实现 JDBC 规范的数据库,目前支持 MySQL,Oracle,SQLServer,PostgreSQL 以及任何遵循 SQL92 标准的数据库。
ShardingSphere-Proxy
定位为透明化的数据库代理端,提供封装了数据库二进制协议的服务端版本,用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL 版本,它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端(如:MySQL Command Client, MySQL Workbench, Navicat 等)操作数据,对 DBA 更加友好。
- 向应用程序完全透明,可直接当做 MySQL/PostgreSQL 使用。
- 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。
本次我们主要使用SHardingJDBC进行分库分表
首先创建数据库
其中t_order是订单表,t_order_item订单商品信息表,t_config广播表,ds_user.t_user专库专表。也就是ShardingJDBC不仅可以配置分库分表,还可以配置单独的数据库使用
脚本已经放入git项目中
导入依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.22</version> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!-- mybatis plus 代码生成器 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.28</version> </dependency> <!-- fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency> <!-- ShardingSphere --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-core-common</artifactId> <version>4.1.0</version> </dependency> <!-- commons-lang3 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.10</version> </dependency> <!-- https://mvnrepository.com/artifact/cn.hutool/hutool-all --> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.5.8</version> </dependency>
然后通过代码自动生成controller,service,repository代码
package com.qjc.config; import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException; import com.baomidou.mybatisplus.core.toolkit.StringUtils; import com.baomidou.mybatisplus.generator.AutoGenerator; import com.baomidou.mybatisplus.generator.config.*; import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy; import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine; import java.util.Scanner; /** * @ClassName: GeneratorCodeConfig * @Description: 自动生成mybatisplus的相关代码 * @Author: qiaojiacheng * @Date: 2021/3/23 6:32 下午 */ public class GeneratorCodeConfig { public static String scanner(String tip) { Scanner scanner = new Scanner(System.in); StringBuilder help = new StringBuilder(); help.append("请输入" + tip + ":"); System.out.println(help.toString()); if (scanner.hasNext()) { String ipt = scanner.next(); if (!StringUtils.isBlank(ipt)) { return ipt; } } throw new MybatisPlusException("请输入正确的" + tip + "!"); } public static void main(String[] args) { // 代码生成器 AutoGenerator mpg = new AutoGenerator(); // 全局配置 GlobalConfig gc = new GlobalConfig(); String projectPath = System.getProperty("user.dir"); gc.setOutputDir(projectPath + "/src/main/java"); gc.setAuthor("astupidcoder"); gc.setOpen(false); //实体属性 Swagger2 注解 gc.setSwagger2(false); mpg.setGlobalConfig(gc); // 数据源配置 DataSourceConfig dsc = new DataSourceConfig(); dsc.setUrl("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true"); dsc.setDriverName("com.mysql.jdbc.Driver"); dsc.setUsername("root"); dsc.setPassword("123456"); mpg.setDataSource(dsc); // 包配置 PackageConfig pc = new PackageConfig(); // pc.setModuleName(scanner("模块名")); pc.setParent("com.qjc"); pc.setEntity("entity"); pc.setMapper("mapper"); pc.setService("service"); pc.setServiceImpl("service.impl"); mpg.setPackageInfo(pc);// 配置模板 TemplateConfig templateConfig = new TemplateConfig(); // 配置自定义输出模板 //指定自定义模板路径,注意不要带上.ftl/.vm, 会根据使用的模板引擎自动识别 // templateConfig.setEntity("templates/entity2.java"); // templateConfig.setService(); // templateConfig.setController(); templateConfig.setXml(null); mpg.setTemplate(templateConfig); // 策略配置 StrategyConfig strategy = new StrategyConfig(); strategy.setNaming(NamingStrategy.underline_to_camel); strategy.setColumnNaming(NamingStrategy.underline_to_camel); strategy.setSuperEntityClass("com.baomidou.mybatisplus.extension.activerecord.Model"); strategy.setEntityLombokModel(true); strategy.setRestControllerStyle(true); strategy.setEntityLombokModel(true); // 公共父类 // strategy.setSuperControllerClass("com.baomidou.ant.common.BaseController"); // 写于父类中的公共字段 // strategy.setSuperEntityColumns("id"); strategy.setInclude(scanner("表名,多个英文逗号分割").split(",")); strategy.setControllerMappingHyphenStyle(true); strategy.setTablePrefix(pc.getModuleName() + "_"); mpg.setStrategy(strategy); mpg.setTemplateEngine(new FreemarkerTemplateEngine()); mpg.execute(); } }
最后就是最主要的配置了,ShardingSphere-JDBC 可以通过 Java
,YAML
,Spring 命名空间
和 Spring Boot Starter
这 4 种方式进行配置,我们使用Spring Boot Starter,也就是配置文件properties进行配置
server.port=8081 server.servlet.context-path=/ spring.jackson.date-format=yyyy-MM-dd HH:mm:ss spring.jackson.time-zone=GMT+8 spring.jackson.serialization.write-dates-as-timestamps=false spring.main.allow-bean-definition-overriding=true # 命名数据源 这个是自定义的 spring.shardingsphere.datasource.names=ds-0,ds-1,ds-2 # 配置数据源ds-0 spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds-0.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.ds-0.username=root spring.shardingsphere.datasource.ds-0.password=123456 # 配置数据源ds-1 spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds-1.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.ds-1.username=root spring.shardingsphere.datasource.ds-1.password=123456 # 配置数据源ds-2 spring.shardingsphere.datasource.ds-2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds-2.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds-2.url=jdbc:mysql://127.0.0.1:3306/ds-user?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.ds-2.username=root spring.shardingsphere.datasource.ds-2.password=123456 # 配置默认数据源ds-0 spring.shardingsphere.sharding.default-data-source-name=ds-0 # 配置分片表 t_order # 配置真实数据节点 库:ds-0,ds-1;表:t_order_0,t_order_1,t_order_2 spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{0..2} # 分库分片建 spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id # 分库分片算法: 对order_id字段进行取模分库,2代表分片库的个数 spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2} # 分表分片健 spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id # 分表算法:对order_id字段进行取模分库,3代表分片表的个数 spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 3} # 配置自增主键字段 spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id # 自增主键ID 生成方案:雪花算法 spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE # 配置分片表 t_order_item spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds-$->{0..1}.t_order_item_$->{0..2} spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=order_id spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2} spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 3} #配置专库专表 spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds-2.t_user spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds-2 spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user spring.shardingsphere.sharding.tables.t_user.key-generator.column=id spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE # 绑定表关系 不加[0]查询时会导致笛卡尔积 spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item # 配置广播表=公共表(PS:所有数据库中都必须有这张表) spring.shardingsphere.sharding.broadcast-tables=t_config # 开启SQL解析日志 spring.shardingsphere.props.sql.show=true # 配置mybatis-plus mybatis-plus.configuration.map-underscore-to-camel-case=true mybatis-plus.configuration.auto-mapping-behavior=full mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl mybatis-plus.mapper-locations=classpath*:mapper/**/*Mapper.xml mybatis-plus.global-config.db-config.logic-not-delete-value=0 mybatis-plus.global-config.db-config.logic-delete-value=1 mybatis-plus.global-config.db-config.logic-delete-field=deleted
进行测试
package com.qjc; import com.alibaba.fastjson.JSON; import com.qjc.entity.TConfig; import com.qjc.entity.TOrder; import com.qjc.entity.TOrderItem; import com.qjc.entity.TUser; import com.qjc.mapper.TConfigMapper; import com.qjc.mapper.TOrderItemMapper; import com.qjc.mapper.TOrderMapper; import com.qjc.mapper.TUserMapper; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.math.BigDecimal; import java.util.List; @RunWith(SpringRunner.class) @SpringBootTest public class ShardingSphereDemoTest { @Autowired private TOrderMapper orderMapper; @Autowired private TOrderItemMapper orderItemMapper; @Autowired private TUserMapper userMapper; @Autowired private TConfigMapper configMapper; private static final Integer num = 1000; private static final String orderNoPre = "NO-"; /** * 测试分库分表-插入 */ @Test public void testOrderSave() { for (int i = 1; i <= num; i++) { String orderNo = orderNoPre + String.format("%04d", i); TOrder order = new TOrder(); order.setOrderNo(orderNo); order.setCreateName("订单名称" + i); order.setPrice(new BigDecimal("" + i)); orderMapper.insert(order); TOrderItem orderItem = new TOrderItem(); Long orderId = order.getOrderId(); orderItem.setOrderId(orderId); orderItem.setOrderNo(orderNo); orderItem.setItemName("商品名称" + i); orderItem.setItemDesc("商品描述" + i); orderItemMapper.insert(orderItem); } } /** * 测试分库分表-查询 */ @Test public void testOrderQuery() { List<TOrder> orderListByPage = orderMapper.findOrderListByPage(); System.err.println(JSON.toJSONString(orderListByPage)); } /** * 测试专库专表 */ @Test public void test() { // TUser user = new TUser(); // user.setName("用户"); // user.setAge(18); // user.setAddress("地址"); // userMapper.insert(user); TUser user1 = userMapper.selectById(1376363351721992194L); System.err.println(user1); } /** * 测试公共表 */ @Test public void testConfig() { TConfig config = new TConfig(); config.setRemark("bbbb"); configMapper.insert(config); } }
通过数据库中的数据就可以查看是否成功,首先插入1000条数据,t_order和t_order_item的三张表加起来肯定是1000,而且由于t_order和t_order_item是绑定关系,所以他们后缀相同的表,里面的数据条数以及分库分片键都是对应的
代码地址:https://gitee.com/xiaorenwu_dashije/sharding-sphere-demo.git
SpringBoot2.0.3.RELEASE+sharding-jdbc4.1.0+mybatis-plus3.4.1+druid1.1.22 搭建分库分表