现在开源的分库分表方案中, 常用的就是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, 每个数据实例要创建ordermaster0和ordermaster1两个表
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
gitee: https://gitee.com/ericwo/second-kill
github: https://github.com/wangjisong1993/second-kill
Springboot + Mybatis-Plus + Sharding-JDBC
原文：https://www.cnblogs.com/shanzhai/p/10542700.html