Springboot + Mybatis-Plus + Sharding-JDBC
现在开源的分库分表方案中, 常用的就是MyCat和Sharding-JDBC, MyCat需要有专门的服务器, 通常要的配置也比较高, 公司之前就是专门部署了两台64G的MyCat服务器, 后面为了节省资源, 逐步切换成Sharding-JDBC
这里主要介绍Springboot, Mybatis-Plus, Sharding-JDBC的整合, 基于Springboot的yml配置文件配置分库分表策略, 使用snowflake雪花算法生成分布式主键, 分片策略是哈希取模
-
pom文件
<dependencies> <dependency> <groupId>de.codecentric</groupId> <artifactId>spring-boot-admin-starter-client</artifactId> <version>1.5.6</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-starter-eureka</artifactId> </dependency> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-starter-feign</artifactId> </dependency> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-starter-hystrix</artifactId> </dependency> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-starter-ribbon</artifactId> </dependency> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-starter-zipkin</artifactId> </dependency> <dependency> <groupId>com.eric</groupId> <artifactId>common</artifactId> <version>1.0-SNAPSHOT</version> </dependency> <dependency> <groupId>com.eric</groupId> <artifactId>cache</artifactId> <version>1.0-SNAPSHOT</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <!--dozer依赖开始--> <dependency> <groupId>net.sf.dozer</groupId> <artifactId>dozer</artifactId> <version>5.5.1</version> <exclusions> <!--目前项目中使用的是apache自带的log4j--> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> </exclusion> <!--<exclusion> <groupId>log4j</groupId> <artifactId>log4j</artifactId> </exclusion>--> </exclusions> </dependency> <dependency> <groupId>net.sf.dozer</groupId> <artifactId>dozer-spring</artifactId> <version>5.5.1</version> </dependency> <!--dozer依赖结束--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <exclusion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-tomcat</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-undertow</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.41</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.0</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.6</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.32</version> </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> <version>18.0</version> </dependency> <!-- mybatis的分页插件 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.7</version> </dependency> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> <version>1.18.2</version> </dependency> <!--sharding-jdbc依赖开始--> <!-- for spring boot --> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding-sphere.version}</version> </dependency> <!-- for spring namespace --> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>${sharding-sphere.version}</version> </dependency> <!--sharding-jdbc依赖结束--> </dependencies> <dependencyManagement> <dependencies> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-dependencies</artifactId> <version>Camden.SR5</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> <build> <finalName>${project.artifactId}</finalName> <plugins> <!-- 资源文件拷贝插件 --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <configuration> <encoding>UTF-8</encoding> </configuration> </plugin> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>1.8</source> <target>1.8</target> <encoding>UTF8</encoding> </configuration> </plugin> </plugins> </build>
-
yml配置文件
需要创建两个数据库实例ds0和ds1, 每个数据实例要创建order_master0和order_master1两个表server: port: 12890 undertow: io-threads: 16 worker-threads: 256 buffer-size: 1024 buffers-per-region: 1024 direct-buffers: true accesslog: dir: my-undertow enabled: true pattern: '%t %a "%r" %s (%D ms)' # sharding-jdbc分库分表的配置 sharding: jdbc: datasource: ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds0 username: root password: 123456 ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds1 username: root password: 123456 names: ds0,ds1 config: sharding: tables: # 分表策略 order_master: table-strategy: inline: sharding-column: order_id algorithm-expression: order_master$->{order_id % 2} key-generator-column-name: order_id actual-data-nodes: ds$->{0..1}.order_master$->{0..1} default-database-strategy: # 分库策略 inline: sharding-column: user_id algorithm-expression: ds$->{user_id % 2} mybatis-plus: datasource: dataSource mapper-locations: classpath:/mapper/*.xml #实体扫描,多个package用逗号或者分号分隔 typeAliasesPackage: com.eric.shardingjdbc.bean global-config: #主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID"; id-type: 1 #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断" field-strategy: 2 #驼峰下划线转换 db-column-underline: true #刷新mapper 调试神器 refresh-mapper: true #数据库大写下划线转换 #capital-mode: true #序列接口实现类配置 #key-generator: com.baomidou.springboot.xxx #逻辑删除配置 logic-delete-value: 0 logic-not-delete-value: 1 #自定义填充策略接口实现 #meta-object-handler: com.baomidou.springboot.xxx #自定义SQL注入器 #sql-injector: com.baomidou.springboot.xxx configuration: map-underscore-to-camel-case: true cache-enabled: false snowflake: datacenter: 1 # 数据中心的id bizType: sharding_order_id_ # 业务类型
-
bean对象
@Data @Accessors(chain = true) public class OrderMaster implements Serializable { /** * 订单ID */ @TableId private Long orderId; /** * 订单编号 */ private String orderSn; /** * 下单人ID */ private Long userId; private static final long serialVersionUID = 1L; }
-
mapper(mapper.xml可以生成空的xml)
import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.eric.shardingjdbc.bean.OrderMaster; /** * @author wang.js on 2019/1/24. * @version 1.0 */ public interface OrderMasterMapper extends BaseMapper<OrderMaster> { }
-
service层
/** * 主订单 * * @author wang.js on 2019/3/12. * @version 1.0 */ public interface OrderMasterService { /** * 保存订单 * * @param t 订单 * @return boolean */ boolean insert(OrderMaster t); /** * 查询订单 * @param i * @return */ OrderMaster findById(int i); } /** * @author wang.js on 2019/3/12. * @version 1.0 */ @Service public class OrderMasterServiceImpl extends ServiceImpl<OrderMasterMapper, OrderMaster> implements OrderMasterService { @Override public boolean insert(OrderMaster t) { return this.baseMapper.insert(t) > 0; } @Override public OrderMaster findById(int i) { return this.baseMapper.selectById(i); } }
-
sql脚本(在ds0和ds1中分别执行)
DROP TABLE IF EXISTS `order_master0`; CREATE TABLE `order_master0` ( `order_id` varchar(40) DEFAULT NULL COMMENT '订单ID', `order_sn` varchar(40) DEFAULT NULL COMMENT '订单编号', `user_id` varchar(40) DEFAULT NULL COMMENT '下单人ID', `shipping_user` varchar(40) DEFAULT NULL COMMENT '收货人姓名', `payment_method` varchar(1) DEFAULT NULL COMMENT '支付方式', `order_money` int(11) DEFAULT NULL COMMENT '订单金额', `district_money` int(11) DEFAULT NULL COMMENT '优惠金额(不包含优惠券)', `shipping_money` int(11) DEFAULT NULL COMMENT '运费金额', `payment_money` int(11) DEFAULT NULL COMMENT '支付金额', `shipping_sn` varchar(40) DEFAULT NULL COMMENT '快递单号', `create_time` datetime DEFAULT NULL COMMENT '下单时间', `shipping_time` datetime DEFAULT NULL COMMENT '发货时间', `pay_time` datetime DEFAULT NULL COMMENT '支付时间', `receive_time` datetime DEFAULT NULL COMMENT '收货时间', `order_status` varchar(1) DEFAULT NULL COMMENT '订单状态', `order_point` int(11) DEFAULT NULL COMMENT '订单积分', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `coupon_discount` int(11) DEFAULT NULL COMMENT '优惠券金额' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for order_master1 -- ---------------------------- DROP TABLE IF EXISTS `order_master1`; CREATE TABLE `order_master1` ( `order_id` varchar(40) DEFAULT NULL COMMENT '订单ID', `order_sn` varchar(40) DEFAULT NULL COMMENT '订单编号', `user_id` varchar(40) DEFAULT NULL COMMENT '下单人ID', `shipping_user` varchar(40) DEFAULT NULL COMMENT '收货人姓名', `payment_method` varchar(1) DEFAULT NULL COMMENT '支付方式', `order_money` int(11) DEFAULT NULL COMMENT '订单金额', `district_money` int(11) DEFAULT NULL COMMENT '优惠金额(不包含优惠券)', `shipping_money` int(11) DEFAULT NULL COMMENT '运费金额', `payment_money` int(11) DEFAULT NULL COMMENT '支付金额', `shipping_sn` varchar(40) DEFAULT NULL COMMENT '快递单号', `create_time` datetime DEFAULT NULL COMMENT '下单时间', `shipping_time` datetime DEFAULT NULL COMMENT '发货时间', `pay_time` datetime DEFAULT NULL COMMENT '支付时间', `receive_time` datetime DEFAULT NULL COMMENT '收货时间', `order_status` varchar(1) DEFAULT NULL COMMENT '订单状态', `order_point` int(11) DEFAULT NULL COMMENT '订单积分', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `coupon_discount` int(11) DEFAULT NULL COMMENT '优惠券金额' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
测试类
@Test public void t2() { Random random = new Random(); for (long i = 1; i < 100L; i++) { OrderMaster t = new OrderMaster().setOrderId(defaultKeyGenerator.generateKey().longValue()).setUserId((long) (random.nextInt(100) + 1)); orderMasterService.insert(t); } }
ps: 如果并发度不高的情况下, defaultKeyGenerator.generateKey()生成的id很大几率会是偶数
项目已经上传到gitee和github