Sharding-Sphere:Sharding-JDBC分库分表(基于MyBatis)

分享了 Sharding-JDBC 搭建读写分离,本章将用 Sharding-JDBC 实现分库分表功能

为什么要分库分表?

举个栗子:当单表数据量超过1000万后,查询的速度将会慢下来,利用索引,读写分离等优化,如果当数据量超过5000万时,一些常见的优化方法将失去作用,此时把单表水平划分到多库多表中,提升查询速度,而sharding-jdbc可以帮助完成水平拆分,而我们只需添加一些配置即可

官方文档

GitHub的地址:https://github.com/apache/incubator-shardingsphere
shardingsphere:https://github.com/apache/incubator-shardingsphere-example
中文文档地址:https://shardingsphere.apache.org/document/current/cn/overview/

代码实现(MyBatis)

(1)有3张表t_membert_ordert_order_item,拆分到2个数据库中,每个数据库2份,相当于每张表拆成了4张,需要注意的是每个表的主键Id尽量设置成 bigint (我开始设置为varchar,结果分表时报错),结构如下:

分库分表设计

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
-- ----------------------------
-- Table structure for t_member0
-- ----------------------------
DROP TABLE IF EXISTS `t_member0`;
CREATE TABLE `t_member0` (
`member_id` bigint(20) NOT NULL,
`member_name` varchar(36) DEFAULT NULL,
`nick_name` varchar(36) DEFAULT NULL,
`account_no` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth_date` datetime DEFAULT NULL,
`ebl_flag` varchar(1) DEFAULT '1',
`del_flag` varchar(1) DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

-- ----------------------------
-- Table structure for t_member1
-- ----------------------------
DROP TABLE IF EXISTS `t_member1`;
CREATE TABLE `t_member1` (
`member_id` bigint(20) NOT NULL,
`member_name` varchar(36) DEFAULT NULL,
`nick_name` varchar(36) DEFAULT NULL,
`account_no` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth_date` datetime DEFAULT NULL,
`ebl_flag` varchar(1) DEFAULT '1',
`del_flag` varchar(1) DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

-- ----------------------------
-- Table structure for t_order0
-- ----------------------------
DROP TABLE IF EXISTS `t_order0`;
CREATE TABLE `t_order0` (
`order_id` bigint(20) NOT NULL,
`member_id` varchar(36) DEFAULT NULL,
`order_code` varchar(36) DEFAULT NULL,
`order_amount` varchar(20) DEFAULT NULL,
`status` varchar(1) DEFAULT '1',
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

-- ----------------------------
-- Table structure for t_order1
-- ----------------------------
DROP TABLE IF EXISTS `t_order1`;
CREATE TABLE `t_order1` (
`order_id` bigint(20) NOT NULL,
`member_id` varchar(36) DEFAULT NULL,
`order_code` varchar(36) DEFAULT NULL,
`order_amount` double DEFAULT NULL,
`status` varchar(1) DEFAULT '1',
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

-- ----------------------------
-- Table structure for t_order_item0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item0`;
CREATE TABLE `t_order_item0` (
`item_id` bigint(20) NOT NULL,
`order_id` varchar(36) DEFAULT NULL,
`product_name` varchar(128) DEFAULT NULL,
`item_account` double DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

-- ----------------------------
-- Table structure for t_order_item1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item1`;
CREATE TABLE `t_order_item1` (
`item_id` bigint(20) NOT NULL,
`order_id` varchar(36) DEFAULT NULL,
`product_name` varchar(128) DEFAULT NULL,
`item_account` double DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

(2)新建SpringBoot项目,引入依赖:

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
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>

(3)引入 mybatis generator 自动生成代码插件,生成实体类与mybatis xml文件

1
2
3
4
5
6
7
8
9
10
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.6</version>
<configuration>
<configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
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
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
<classPathEntry location="D:\software\database\mybatis-generator-core-1.3.2\lib\mysql-connector-java-5.1.25-bin.jar"/>
<context id="DB2Tables" targetRuntime="MyBatis3" defaultModelType="flat">
<commentGenerator>
<property name="suppressDate" value="true"/>
<!-- 是否去除自动生成的注释 true: 是, false: 否 -->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库链接URL,用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver" userId="xnxx" password="Xnxx85781245!" connectionURL="jdbc:mysql://47.107.177.150:3306/zy_sharding_business1?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;maxReconnects=15000&amp;allowMultiQueries=true">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 生成模型的包名和位置-->
<javaModelGenerator targetPackage="com.zypcy.sharding.sublibrarytable.entity" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成映射文件的包名和位置-->
<sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- 生成DAO的包名和位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.zypcy.sharding.sublibrarytable.repository" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
<!-- 注意:已生成的不要再生成,不然会覆盖已生成的文件 -->
<!--
<table tableName="t_member" domainObjectName="Member" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
<table tableName="t_order" domainObjectName="Order" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
<table tableName="t_order_item" domainObjectName="OrderItem" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
-->
</context>
</generatorConfiguration>

(4)添加配置文件,这点很重要,否则无法实现分库分表,ds$->{0..1}.t_member$->{0..1},表达式相当于:ds0.t_member0ds0.t_member1ds1.t_member0ds1.t_member1,在配置中配置了key-generator.column,所以新增该表数据时,不需要给主键id赋值,sharding-jdbc会调用内置的SNOWFLAKE算法生成分布式id,请往下看

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
server.port=8071

# mybatis 配置
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=com.zypcy.sharding.sublibrarytable.entity

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 分库配置,根据member_id分库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{member_id % 2}

# 分表配置,根据member_id分表
spring.shardingsphere.sharding.tables.t_member.actual-data-nodes=ds$->{0..1}.t_member$->{0..1}
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.algorithm-expression=t_member$->{member_id % 2}
spring.shardingsphere.sharding.tables.t_member.key-generator.column=member_id
spring.shardingsphere.sharding.tables.t_member.key-generator.type=SNOWFLAKE

# 分表配置,根据order_id分表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 分表配置,根据order_id分表
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
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 % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.binding-tables=t_member,t_order,t_order_item
#spring.shardingsphere.sharding.broadcast-tables=t_config

spring.shardingsphere.props.sql.show=true

(5)Durid配置

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
@Configuration
public class DuridConfig {

@Bean
public Filter statFilter() {
StatFilter filter = new StatFilter();
filter.setSlowSqlMillis(5000);
filter.setLogSlowSql(true);
filter.setMergeSql(true);
return filter;
}

@Bean
public ServletRegistrationBean statViewServlet() {
//创建servlet注册实体
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//设置ip白名单
servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
//设置ip黑名单,如果allow与deny共同存在时,deny优先于allow
//servletRegistrationBean.addInitParameter("deny","192.168.0.19");
//设置控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "123456");
//是否可以重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
}

(6)添加MemberService服务接口与实现

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
public interface IMemberService {

int insert(Member record);

Member selectByPrimaryKey(Long memberId);

int deleteByPrimaryKey(Long memberId);
}

@Service
public class MemberServiceImpl implements IMemberService {

@Autowired private MemberMapper memberMapper;

@Override
public int insert(Member record) {
return memberMapper.insertSelective(record);
}

@Override
public Member selectByPrimaryKey(Long memberId) {
return memberMapper.selectByPrimaryKey(memberId);
}

@Override
public int deleteByPrimaryKey(Long memberId) {
return memberMapper.deleteByPrimaryKey(memberId);
}
}

@Mapper
public interface MemberMapper {
int deleteByPrimaryKey(Long memberId);

int insert(Member record);

int insertSelective(Member record);

Member selectByPrimaryKey(Long memberId);

int updateByPrimaryKeySelective(Member record);

int updateByPrimaryKey(Member record);
}

(7)添加MemberController

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
@RequestMapping("/member")
@RestController
public class MemberController {

@Autowired
private IMemberService memberService;

@RequestMapping("/add")
public Member add(){
Member member = new Member();
//member.setMemberId(IdWorker.getLongId()); 不用手动设置主键id,新增时,sharding-jdbc会自动赋值,因为在配置文件中配置了该列使用SNOWFLAKE算法生成值
member.setMemberName("张三");
member.setNickName("闪耀的瞬间");
member.setAccountNo(member.getMemberId()+"");
member.setPassword("123465");
member.setAge(27);
member.setBirthDate(new Date());
member.setEblFlag("1");
member.setDelFlag("0");
member.setDescription("xxx");
member.setCreateTime(new Date());
member.setUpdateTime(new Date());
memberService.insert(member);
return member;
}

@RequestMapping("/findById")
public Member findById(Long memberId){
return memberService.selectByPrimaryKey(memberId);
}

@RequestMapping("/delete")
public String delete(Long memberId){
memberService.deleteByPrimaryKey(memberId);
return "success";
}
}

(8)启动项目,访问新增接口 http://localhost:8071/member/add ,可以看到页面返回了新增后的数据

同时查看Intellij IDEA控制台,可以看到数据插入到ds1库的t_member1表了

再查看ds1数据库的t_member1表,有刚刚新增的记录

(9)访问查询接口 http://localhost:8071/member/findById?memberId=xxxxxx

同时查看控制台,sql会自动路由到ds1库去查询数据

参考:https://gitee.com/zhuyu1991/spring-cloud/tree/master/sharding-business/sub-library-table

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2021 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :