Apache ShardingSphere 实现分库分表及读写分离

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 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]

 

标签: ShardingSphere+Mycat

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
SQL 监控 Apache
钱大妈生鲜如何利用 CCR 实现 Apache Doris 集群读写分离
钱大妈基于 阿里云 SelectDB 内核 Apache Doris 搭建了实时数仓,为业务提供实时精准分析的数据查询及分析服务。凭借 Apache Doris 强大的性能,钱大妈能够实时监控生鲜产品的流通情况,为商品结构的优化和食品新鲜度的保障提供坚实的数据支撑。
|
SQL Java 关系型数据库
【java_wxid项目】【第八章】【Apache ShardingSphere集成】
主项目链接:https://gitee.com/java_wxid/java_wxid 项目架构及博文总结:
326 0
|
算法 小程序 搜索推荐
Apache ShardingSphere:由开源驱动的分布式数据库中间件生态
2021 年 7 月 21 日 2021 亚马逊云科技中国峰会现场,SphereEx 联合创始人、Apache ShardingSphere PMC 潘娟受邀参与此次峰会,以《Apache ShardingSphere 分布式数据库中间件开源生态构建》为主题,围绕开源理念扩散、社区建设、ShardingSphere 如何践行 Apache Way 等方面展开了介绍,本文总结自潘娟内容分享。
328 8
Apache ShardingSphere:由开源驱动的分布式数据库中间件生态
|
Cloud Native 安全 Java
Apache ShardingSphere Committer 孟浩然:我的开源之旅还在继续
Apache ShardingSphere Committer 孟浩然:我的开源之旅还在继续
165 0
|
Cloud Native 安全 Java
Apache ShardingSphere Committer 孟浩然:我的开源之旅还在继续
Apache ShardingSphere Committer 孟浩然:我的开源之旅还在继续
|
3月前
|
存储 消息中间件 Java
Apache Flink 实践问题之原生TM UI日志问题如何解决
Apache Flink 实践问题之原生TM UI日志问题如何解决
44 1
|
21天前
|
SQL Java API
Apache Flink 2.0-preview released
Apache Flink 社区正积极筹备 Flink 2.0 的发布,这是自 Flink 1.0 发布以来的首个重大更新。Flink 2.0 将引入多项激动人心的功能和改进,包括存算分离状态管理、物化表、批作业自适应执行等,同时也包含了一些不兼容的变更。目前提供的预览版旨在让用户提前尝试新功能并收集反馈,但不建议在生产环境中使用。
505 13
Apache Flink 2.0-preview released
|
25天前
|
存储 缓存 算法
分布式锁服务深度解析:以Apache Flink的Checkpointing机制为例
【10月更文挑战第7天】在分布式系统中,多个进程或节点可能需要同时访问和操作共享资源。为了确保数据的一致性和系统的稳定性,我们需要一种机制来协调这些进程或节点的访问,避免并发冲突和竞态条件。分布式锁服务正是为此而生的一种解决方案。它通过在网络环境中实现锁机制,确保同一时间只有一个进程或节点能够访问和操作共享资源。
59 3
|
2月前
|
SQL 消息中间件 关系型数据库
Apache Doris Flink Connector 24.0.0 版本正式发布
该版本新增了对 Flink 1.20 的支持,并支持通过 Arrow Flight SQL 高速读取 Doris 中数据。
|
3月前
|
消息中间件 监控 数据挖掘
基于RabbitMQ与Apache Flink构建实时分析系统
【8月更文第28天】本文将介绍如何利用RabbitMQ作为数据源,结合Apache Flink进行实时数据分析。我们将构建一个简单的实时分析系统,该系统能够接收来自不同来源的数据,对数据进行实时处理,并将结果输出到另一个队列或存储系统中。
205 2

推荐镜像

更多