Apache ShardingSphere 实现分库分表及读写分离
本文为博主原创，未经允许不得转载:
项目demo 源码地址：https://gitee.com/xiangbaxiang/apache-shardingjdbc
1. 创建Maven项目，并配置 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 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>apache-shardingjdbc</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.11.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <!--排除默认的tomcat-jdbc--> <exclusion> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.9</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.22</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.3.2</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <!-- <version>2.1.3.RELEASE</version>--> </plugin> </plugins> </build> </project>
2.创建实体类
package com.sharding.entity; import lombok.Data; @Data public class User { private Long id ; private Integer age ; private String name; private Long cid; }
3. 创建 mapper 类
package com.sharding.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.sharding.entity.User; import org.apache.ibatis.annotations.Mapper; @Mapper public interface UserMapper extends BaseMapper<User> { }
4. 创建控制层
package com.sharding.controller; import com.sharding.entity.User; import com.sharding.mapper.UserMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.Random; @RestController @RequestMapping("sharding") public class UserController { @Autowired private UserMapper userMapper; @RequestMapping("saveUser") public int saveUser(){ User user = new User(); Random random = new Random(); int value = random.nextInt(20); user.setAge(value); long longValue = random.nextLong(); user.setCid(longValue); user.setName("test"+value); int result = userMapper.insert(user); return result; } }
5. 创建启动类
package com.sharding; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication @MapperScan(basePackages = "com.sharding.mapper") public class ApacheShardingJdbcApplication { public static void main(String[] args) { SpringApplication.run(ApacheShardingJdbcApplication.class,args); } }
6. 创建分库分表的配置文件
#数据库m1,m2 spring.shardingsphere.datasource.names=m1,m2 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/userdb?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=zengjian spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/userdb2?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=zengjian # 分库策略 根据id取模确定数据进哪个数据库 spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{age % 2+1} spring.shardingsphere.sharding.binding-tables=user # user表进行分表 spring.shardingsphere.sharding.tables.user.actual-data-nodes=m$->{1..2}.user_$->{1..2} # 分表字段id spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id # 分表策略 根据id取模,确定数据最终落在那个表中 spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression = user_$->{id % 2+1} #分表列 spring.shardingsphere.sharding.tables.user.key-generator.column=id #id数据生成算法 spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE spring.shardingsphere.props.sql.show=true spring.main.allow-bean-definition-overriding=true
7. 读写分离的配置文件
#shardingsphere 读写分离,master-slave,可以一主多从 spring.shardingsphere.datasource.names=ds-master,ds-slave0 #主库 spring.shardingsphere.datasource.ds-master.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds-master.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds-master.jdbc-url=jdbc:mysql://112.125.26.63:3306/userdb_master?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8 spring.shardingsphere.datasource.ds-master.username=root spring.shardingsphere.datasource.ds-master.password=root #从库0 spring.shardingsphere.datasource.ds-slave0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds-slave0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds-slave0.jdbc-url=jdbc:mysql://112.125.26.64:3306/userdb_slave?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8 spring.shardingsphere.datasource.ds-slave0.username=root spring.shardingsphere.datasource.ds-slave0.password=root #从库1 #spring.shardingsphere.datasource.ds-slave1.type=com.alibaba.druid.pool.DruidDataSource #spring.shardingsphere.datasource.ds-slave1.type=com.zaxxer.hikari.HikariDataSource #spring.shardingsphere.datasource.ds-slave1.driver-class-name=com.mysql.cj.jdbc.Driver #spring.shardingsphere.datasource.ds-slave1.jdbc-url=jdbc:mysql://112.125.26.65:3306/shop_ds_slave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8 #spring.shardingsphere.datasource.ds-slave1.username=root #spring.shardingsphere.datasource.ds-slave1.password=root #读写分离主从规则设置，当有2个以上从库时，从库读采用轮询的负载均衡机制 spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin spring.shardingsphere.masterslave.name=ms spring.shardingsphere.masterslave.master-data-source-name=ds-master spring.shardingsphere.masterslave.slave-data-source-names=ds-slave0 spring.shardingsphere.props.sql.show=true spring.main.allow-bean-definition-overriding=true
8.创建启动类
package com.sharding; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication @MapperScan(basePackages = "com.sharding.mapper") public class ApacheShardingJdbcApplication { public static void main(String[] args) { SpringApplication.run(ApacheShardingJdbcApplication.class,args); } }
9.创建对应数据库：userdb , userdb2 , 并分别执行以下脚本
-- ---------------------------- -- Table structure for user_1 -- ---------------------------- DROP TABLE IF EXISTS `user_1`; CREATE TABLE `user_1` ( `id` bigint(20) NOT NULL, `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `cid` bigint(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact; -- ---------------------------- -- Table structure for user_2 -- ---------------------------- DROP TABLE IF EXISTS `user_2`; CREATE TABLE `user_2` ( `id` bigint(20) NOT NULL, `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `cid` bigint(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
10. 验证
浏览器请求： http://localhost:8080/sharding/saveUser
查看日志：可以看到sql 执行的逻辑过程如下
2021-11-13 23:54:20.056 INFO 186124 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=0}), Condition(column=Column(name=age, tableName=user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=4, logicSQL=INSERT INTO user ( id, age, name, cid ) VALUES ( ?, ?, ?, ? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, age, name, cid], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@62520960, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@14c57902, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7c661b99, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6c580fa2])]) 2021-11-13 23:54:20.056 INFO 186124 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m1 ::: INSERT INTO user_2 (id, age, name, cid) VALUES (?, ?, ?, ?) ::: [1459550199242993665, 14, test14, -2186172803516415750]
