当公司业务量上去之后,单表支撑不了的时候,分库分表就是一个绕不开的话题,小弟最近新入职一家公司,发现这边公司在用ShardingSphere来进行分库分表,之前没接触过这方面,所以就写了个demo学习一下,下面文章就记录一下如何用ShardingSphere来进行分库分表!(
能力有限,本章不会讲原理奥,只是记录如何分库分表)
1、官网文档地址
这是ShardingSphere官网文档的地址,有需要的可以点进去看一下。
2、技术及环境
数据库 | 项目用到的技术 |
---|---|
mysql5.6 | springboot、mybatis、shardingsphere |
3、建库建表
1、新建两个数据库:
一个user0,一个user1。
2、在user0数据库新建两个表:
一个us_admin0,一个us_admin1,新建语句如下:
CREATE TABLE `us_admin0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_id` int(11) NOT NULL COMMENT '用户id', `addr_id` int(11) NOT NULL COMMENT '地址id', `user_name` varchar(64) NOT NULL COMMENT '用户编号', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台用户表'; CREATE TABLE `us_admin1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_id` int(11) NOT NULL COMMENT '用户id', `addr_id` int(11) NOT NULL COMMENT '地址id', `user_name` varchar(64) NOT NULL COMMENT '用户编号', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台用户表';
3、在user1数据库新建两个表:
一个us_admin0,一个us_admin1,新建语句如下:
CREATE TABLE `us_admin0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_id` int(11) NOT NULL COMMENT '用户id', `addr_id` int(11) NOT NULL COMMENT '地址id', `user_name` varchar(64) NOT NULL COMMENT '用户编号', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台用户表'; CREATE TABLE `us_admin1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_id` int(11) NOT NULL COMMENT '用户id', `addr_id` int(11) NOT NULL COMMENT '地址id', `user_name` varchar(64) NOT NULL COMMENT '用户编号', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台用户表';
4、搭建springboot项目
这部分就idea自己创建一下就好了,pom文件及配置文件内容如下:
pom文件:
<?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.0.7.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.sharding</groupId> <artifactId>sphere</artifactId> <version>1.0.0-SNAPSHOT</version> <name>sphere</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> <spring-cloud.version>Hoxton.SR3</spring-cloud.version> <redisson.version>3.8.2</redisson.version> <mysql-connector.version>8.0.12</mysql-connector.version> </properties> <dependencies> <!-- 单元测试 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- web --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web-services</artifactId> </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> <!-- shardingsphere的jar包 --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency> <!--阿里数据库连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency> <!-- Mysql驱动包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <!-- <scope>runtime</scope>--> </dependency> <!-- 健康检查 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.2</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
application.yml配置:
# 服务端口 server: port: 8888 # 服务名 spring: application: name: test-user # 配置sharding jdbc分片规则 shardingsphere: datasource: # 连接名称(下面要用这个名称来区分库) names: ds0,ds1 ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.1.19:3306/user0?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8 username: root password: 123 initialSize: 5 #初始化大小 maxActive: 200 #最大值 maxWait: 2000 #最大等待时间,配置获取连接等待超时,时间单位都是毫秒ms timeBetweenEvictionRunsMillis: 60000 #配置间隔多久才进行一次检测,检测需要关闭的空闲连接 ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.1.19:3306/user1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8 username: root password: 123 initialSize: 5 #初始化大小 maxActive: 200 #最大值 maxWait: 2000 #最大等待时间,配置获取连接等待超时,时间单位都是毫秒ms timeBetweenEvictionRunsMillis: 60000 #配置间隔多久才进行一次检测,检测需要关闭的空闲连接 # 配置分片规则 sharding: # 按表来区分 tables: us_admin: # 配置数据节点 actualDataNodes: ds${0..1}.us_admin${0..1} # 分库策略 databaseStrategy: inline: # 分库的规则 用user_id这个字段来分库 总共有两个库 及ds0(user0)与ds1(user1) shardingColumn: user_id algorithmExpression: ds${user_id % 2} # 分表策略 tableStrategy: inline: shardingColumn: addr_id algorithmExpression: us_admin${addr_id % 2} keyGenerator: column: id type: SNOWFLAKE bindingTables: us_admin broadcastTables: t_config defaultDataSourceName: ds0 props: sql.show: true # 配置xml 的扫描路径 mybatis: mapper-locations: classpath:mapper/*.xml check-config-location: true type-aliases-package: com.sharding.sphere.model configuration: cacheEnabled: true mapUnderscoreToCamelCase: true
5、接口测试
编写增删改接口进行测试,分库根据user_id来切片,分表根据addr_id来切片,分别插入一些数据,结果如下:
@RestController public class UserController { @Resource UserService userService; @RequestMapping("add") public Integer add(@RequestBody UsAdmin usAdmin){ Integer add = userService.add(usAdmin); return add; } @RequestMapping("select") public List<UsAdmin> select(){ List<UsAdmin> select = userService.select(); return select; } @RequestMapping("delect") public Integer delect(Long id){ Integer delect = userService.delect(id); return delect; } }
查询sql语句:
<select id="selectAll" resultMap="BaseResultMap"> select id, addr_id, user_id, user_name from us_admin </select>
新增sql语句:
<insert id="insertSelective" parameterType="com.sharding.sphere.model.UsAdmin"> insert into us_admin <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="userId != null"> user_id, </if> <if test="addrId != null"> addr_id, </if> <if test="userName != null"> user_name, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=BIGINT}, </if> <if test="userId != null"> #{userId,jdbcType=INTEGER}, </if> <if test="addrId != null"> #{addrId,jdbcType=INTEGER}, </if> <if test="userName != null"> #{userName,jdbcType=VARCHAR}, </if> </trim> </insert>
删除sql语句:
delete from us_admin where id = #{id,jdbcType=BIGINT}
数据新增分表分库结果:
查询结果:
可以看到,新增数据的时候以user_id%2来计算分库,双数在user0库,单数在user1库,addr_id作为分表id,双数在us_admin0表,单数在us_admin1表,先确定库然后确定表
,而查询一条语句可以查询到所有,不过ShardingSphere好像有些sql语句是不支持的,比如关联自己这种操作(us_admin left join us_admin这种)
,还有一些不支持的sql,具体的可以百度看看。
讲道理来看,ShardingSphere其实对代码的侵入量并不算多,只是有一些配置,配置好之后该写的sql跟原来一样的,可能是还没有踩到该踩的坑吧