分库分表-ShardingSphere

作者: 久伴我还是酒伴我 | 来源:发表于2021-01-18 14:41 被阅读0次

简介

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar,这 3 款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，目前，数据分片、读写分离、数据加密、影子库压测等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协议的支持，均通过插件的方式织入项目，ShardingSphere 已于2020年4月16日成为 Apache 软件基金会的顶级项目。

背景

公司项目随着业务数据不断增多，单表数量达到五百W以上，查询新增速度不断变慢，严重影响客户使用，分库分表势在必行，大概看了下相关的技术，果断选择了ShardingJDBC（使用人多，稳定成熟，加Apache加持），本文主要围绕ShardingSphere 做分库分表，简单介绍下ShardingSphere。

公司先做的分表，后来数据增多，存储磁盘满足不了单个数据库的大小，后来才做的分库，所以可以结合着自己的实际业务去做考虑是否做需要做分库，如果分表已经能满足自己的三年内的业务需求，可以不做分库操作，直接分表即可。

分片场景

策略	表名称	分片字段	表数量
某字段分库分表	sh_in_conf	task_id	6
某字段分库分表	sh_in_log	company_id	4
通用默认某字段分库分表	sh_in_toll	biz_id	2
不分库分表	sh_in_info	只需在ds0库存在即可	1

库结构

ds0库

ds1库

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.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sharding.test</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-demo</name>
    <description>分库分表Demo</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <sharding.jdbc.version>4.0.0</sharding.jdbc.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.github.ulisesbocchio</groupId>
            <artifactId>jasypt-spring-boot-starter</artifactId>
            <version>1.16</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.74</version>
        </dependency>

        <!-- 与swagger一起使用,需要注意-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.8.0</version>
        </dependency>

        <!--mybatis plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.0</version>
        </dependency>

        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.29</version>
        </dependency>

        <!--前端-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!-- hutool 工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.6.1</version>
        </dependency>

        <!-- lang包-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </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>

    </dependencies>

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

</project>

分片算法测试

 public static void main(String[] args) {
        //分表表数量
        Integer table_num = 4;
        //分库数量
        Integer ds_num = 2;
        //分片内容
        String content = "a1922929222a1";
        Integer table = HashUtil.fnvHash(content) % table_num;
        Integer data = HashUtil.fnvHash(content) % ds_num;
        System.out.println("存入表："+table);
        System.out.println("存入库："+data);
    }

application.properties

server.port=8080
spring.application.name=sharding-demo
swagger2.enable=true
spring.profiles.active=sharding-db-table

##分库 数据库为偶数，每张表则保留偶数表 数据库为奇数表，则表为奇数表
spring.shardingsphere.datasource.names=ds0,ds1
####################################################  ds0 ####################################################
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://172.21.16.52:3306/release_degree?characterEncoding=UTF-8&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=dev
spring.shardingsphere.datasource.ds0.password=21758E78331B20E4
spring.shardingsphere.datasource.ds0.maximum-pool-size=128
spring.shardingsphere.datasource.ds0.connection-timeout=30000
spring.shardingsphere.datasource.ds0.max-lifetime=1200000
spring.shardingsphere.datasource.ds0.connection-test-query=SELECT 'X'
spring.shardingsphere.datasource.ds0.minimum-idle=10
########################################################  db1 ####################################################
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://172.21.16.52:3306/sco_logging_core?characterEncoding=UTF-8&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=dev
spring.shardingsphere.datasource.ds1.password=21758E78331B20E4
spring.shardingsphere.datasource.ds1.maximum-pool-size=128
spring.shardingsphere.datasource.ds1.connection-timeout=30000
spring.shardingsphere.datasource.ds1.max-lifetime=1200000
spring.shardingsphere.datasource.ds1.connection-test-query=SELECT 'X'
spring.shardingsphere.datasource.ds1.minimum-idle=10
##是否打印SQL
spring.shardingsphere.props.sql.show=true

application-sharding-db-table.properties

#分库时使用
# db type mysql
#mybatis-plus.global-config.db-config.db-type=mysql
# default data source ds0
spring.shardingsphere.sharding.default-data-source-name=ds0

#####根据biz_id分片
#分表
spring.shardingsphere.sharding.tables.sh_in_conf.actual-data-nodes=ds$->{0..1}.sh_in_conf_$->{0..5}
spring.shardingsphere.sharding.tables.sh_in_conf.table-strategy.standard.sharding-column=task_id
spring.shardingsphere.sharding.tables.sh_in_conf.table-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
#分库
spring.shardingsphere.sharding.tables.sh_in_conf.database-strategy.standard.sharding-column=task_id
spring.shardingsphere.sharding.tables.sh_in_conf.database-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
#####根据company_id分片
#分表
spring.shardingsphere.sharding.tables.sh_in_log.actual-data-nodes=ds$->{0..1}.sh_in_log_$->{0..3}
spring.shardingsphere.sharding.tables.sh_in_log.table-strategy.standard.sharding-column=company_id
spring.shardingsphere.sharding.tables.sh_in_log.table-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
#分库
spring.shardingsphere.sharding.tables.sh_in_log.database-strategy.standard.sharding-column=company_id
spring.shardingsphere.sharding.tables.sh_in_log.database-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm

####默认分片策略
spring.shardingsphere.sharding.tables.sh_in_toll.actual-data-nodes=ds$->{0..1}.sh_in_toll_$->{0..1}
####用于单分片键的标准分片场景
#默认表分片策略
spring.shardingsphere.sharding.default-table-strategy.standard.sharding-column=biz_id
spring.shardingsphere.sharding.default-table-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.DefaultPreciseShardingAlgorithm
#默认库分片策略
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=biz_id
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.DefaultPreciseShardingAlgorithm
spring.shardingsphere.sharding.binding-tables=sh_in_toll

自定义分片类

package com.sharding.test.sharding;

import cn.hutool.core.util.HashUtil;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * @author leijie.gao
 * @since 2020-05-27
 */
public class DefaultPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {

  /**
   * @param targetTableNames sharding 逻辑表 分表规则
   * @param shardingVal      PreciseShardingValue(logicTableName=真实表, columnName=company_id, value=company_id_value)
   * @return {{@link String}}
   */
  @Override
  public String doSharding(Collection<String> targetTableNames, PreciseShardingValue<String> shardingVal) {
    final String[] tables = targetTableNames.toArray(new String[targetTableNames.size()]);
    return ArrayUtils.isEmpty(tables) ? tables[0] : tables[HashUtil.fnvHash(shardingVal.getValue()) % targetTableNames.size()];
  }
}

结束语

每张表可以按照不同的分片策略，保存或者更新查询操作时，必须带着分片策略的条件。
建议分表规则和分库规则一致，代码通用率高
建表时,建议为偶数表,方便分库时，为数据迁移做准备，后期分库时，只需要在偶数库中保留偶数表数据，奇数库中保留奇数表数据
案例中为了方便，其实分片策略确定后，偶数库中保留偶数表，奇数库中保留奇数表即可，保留也不影响

5.代码地址 https://gitee.com/G_YutianMusi/sharding-demo.git

分库分表-ShardingSphere

简介

背景

分片场景

库结构

Pom文件

分片算法测试

application.properties

application-sharding-db-table.properties

自定义分片类

结束语

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SpringBoot