Java知识分享
热爱技术,分享技术

SpringBoot Data-JPA集成Sharding-JDBC4.0.0-RC1实现单库分表

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈,它由 JDBC、Proxy 和 Sidecar(规划中)这 3 款相互独立,却又能够混合部署配合使用的产品组成。 它们均提供标准化的数据分片、分布式事务和数据库治理功能,可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

Apache ShardingSphere 定位为关系型数据库中间件,旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力,而并非实现一个全新的关系型数据库。 它通过关注不变,进而抓住事物本质。关系型数据库当今依然占有巨大市场,是各个公司核心业务的基石,未来也难于撼动,我们目前阶段更加关注在原有基础上的增量,而非颠覆。

SpringBoot Data-JPA集成Sharding-JDBC4.0.0-RC1实现单库分表插图

如何单库分表集成

  • 首先集成一个不分库只分表的模式。创建一个springboot项目,这里使用Sharding-JDBC4.0.0-RC1版本。使用sharding-jdbc-spring-boot-starter集成

pom.xml配置

<?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>1.5.10.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.ibfbc.shardingjdbc</groupId>
    <artifactId>shardingjbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>shardingjbc</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </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>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.60</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

创建测试数据局tb_user。分别创建三张表 tb_user0, tb_user1,tb_user2。这里假设tb_user这个预计随着系统的运行。公司发展很好,以后数据量会暴增。所以提前进行水平分片存储。相对于垂直分片,它不再将数据根据业务逻辑分类,而是通过某个字段(或某几个字段),根据某种规则将数据分散至多个库或表中,每个分片仅包含数据的一部分。这样单表数据量降下来了,mysql的B+树的检索效率就提高了

CREATE TABLE t_user0 (id bigint(20) primary key auto_increment NOT NULL,name varchar(64) DEFAULT NULL COMMENT ‘名称’,cert_no varchar(18) DEFAULT NULL COMMENT ‘身份证号码’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘创建时间’,password varchar(32) DEFAULT NULL COMMENT ‘密码’,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

cert_no 请加上索引

下面就是基本的curd骚操作配置了

添加实体类

@Entity
@Table(name = "tb_user")
@Data
public class TUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private String certNo;

    private int age;
}

创建Repository

@Repository
public interface UserRepository extends JpaRepository<TUser, Long> {
    TUser findByCertNo(String cerNo);
}

使用精确切分算法(Precise sharding algorithm)

package com.ibfbc.shardingjdbc.config;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    static final int HASH_BITS = 0x7fffffff; // usable bits of normal node hash

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String key = preciseShardingValue.getValue();
        int suffix = spread(key.hashCode()) % 2;
        return "tb_user" + suffix;
    }

    static final int spread(int h) {
        return (h ^ (h >>> 16)) & HASH_BITS;
    }
}

配置数据源

package com.ibfbc.shardingjdbc.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource dataSource() {
        return new DruidDataSource();
    }

}

设置分片规则application.properties配置

数据源定义
spring.shardingsphere.datasource.names=master

# 数据源 主库0
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://127.0.0.1:3306/test?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=root

#根据id分表
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=master.tb_user$->{0..1}
#spring.shardingsphere.sharding.tables.sys_user.table-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.tables.sys_user.table-strategy.inline.algorithm-expression=tb_user$->{id % 2}
spring.shardingsphere.sharding.tables.tb_user.table-strategy.standard.sharding-column=cert_no
spring.shardingsphere.sharding.tables.tb_user.table-strategy.standard.precise-algorithm-class-name=com.ibfbc.shardingjdbc.config.TableShardingAlgorithm
spring.shardingsphere.sharding.binding-tables=tb_user

#打印sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

测试类


@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingjbcApplication.class)
public class ShardingjbcApplicationTests {
    @Resource
    UserRepository userRepository;

    @Test
    public void add() {
        for (int i = 0; i < 20000000; i++) {
            TUser user = new TUser();
            user.setAge(i);
            user.setCertNo(UUID.randomUUID().toString());
            user.setName("并发编程网(www.ibfbc.com)" + i);
            userRepository.save(user);
        }

    }
    @Test
    public void list() {
        List<TUser> users = userRepository.findAll();
    }
}
打赏
本站所有资源均来源于网络,仅供学习使用,请支持正版!Java技术开源 » SpringBoot Data-JPA集成Sharding-JDBC4.0.0-RC1实现单库分表

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续提供更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫

微信扫一扫

登录

找回密码

注册