一、为什么要采用分库分表

我们公司是做车联网项目的,其中车辆的定位啊、报警数据非常的多,
其中任意一项一个月就会有千万条数据,这个时候呢,
单是一个月的数据已是非常之庞大,查询效率已是非常缓慢,
如果不采用分表,那么半年后,一年后,表中数据不可想象..............
简单来说,就是数据库中的数据量猛增,访问性能也变慢,优化迫在眉睫。

我们这个时候就会分析,问题究竟出现在哪?

关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。当单表的数据量达到 1000W或100G以后,由于查询维度较多,即使添加从库、优化索引,做很多操作时性能仍下降严重。

解决方案:

1.如果是硬件问题导致(存储容量、CPU),则提升服务器硬件水平

2.如果是Mysql本身原因导致(数据量过多),则可以根据业务划分不同数据库,把数据分散在不同的数据库中,使得单一数据库的数据量变小来缓解单一数据库的性能问题

分库分表的核心作用:

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题,将原来独立的数据库拆分成若干数据库组成 ,将数据大表拆分成若干数据小表,使得单一数据库、单一数据表的数据量变小,从而提升数据库性能。

最终,我们决定采用,使用sharding-jdbc,来对我们的项目进行分库分表,至于为什么要是用sharding-jdbc因为其使用简单,兼容各种orm(我们项目才用的mp),而且非常轻量级,代码侵入小,详情请看下边介绍!

二、Sharding-JDBC介绍

来源与特性

Sharding-JDBC是当当网研发的开源分布式数据库中间件,
从 3.0 开始Sharding-JDBC被包含在 Sharding-Sphere 中,
之后该项目进入进入Apache孵化器,4.0版本之后的版本为Apache版本。 ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈,
它由Sharding-JDBC、ShardingProxy和Sharding-Sidecar(计划中)这3款相
互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和
数据库治理功能,可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。

Sharding-JDBC,它定位为轻量级Java框架,在Java的JDBC层提供的额外服务。 
它使用客户端 直连数据库,以jar包形式提供服务,无需额外部署和依赖,
可理解为增强版的JDBC驱动,完全兼容JDBC和各种 ORM框架。

Sharding-JDBC的核心功能为数据分片和读写分离,通过Sharding-JDBC,
应用可以透明的使用jdbc访问已经分库
分表、读写分离的多个数据源,而不用关心数据源的数量以及数据如何分布。

官方地址:https://shardingsphere.apache.org/document/current/cn/overview/

image-20210306171141740

逻辑表

水平拆分的数据库(表)的相同逻辑和数据结构表的总称。例:订单数据根据主键尾数拆分为10张表,分别是t_order_0t_order_9,他们的逻辑表名为t_order

真实表

在分片的数据库中真实存在的物理表。即上个示例中的t_order_0t_order_9

数据节点

数据分片的最小单元。由数据源名称和数据表组成,例:ds_0.t_order_0

分片键

用于分片的数据库字段,是将数据库(表)水平拆分的关键字段。例:将订单表中的订单主键的尾数取模分片,则订单主键为分片字段。 SQL中如果无分片字段,将执行全路由,性能较差。 除了对单分片字段的支持,ShardingSphere也支持根据多个字段进行分片。

分片算法

通过分片算法将数据分片,支持通过=>=<=><BETWEENIN分片。分片算法需要应用方开发者自行实现,可实现的灵活度非常高。

目前提供4种分片算法。由于分片算法和业务实现紧密相关,因此并未提供内置分片算法,而是通过分片策略将各种场景提炼出来,提供更高层级的抽象,并提供接口让应用开发者自行实现分片算法。

  • 精确分片算法

对应PreciseShardingAlgorithm,用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。

  • 范围分片算法

对应RangeShardingAlgorithm,用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用。

  • 复合分片算法

对应ComplexKeysShardingAlgorithm,用于处理使用多键作为分片键进行分片的场景,包含多个分片键的逻辑较复杂,需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。

  • Hint分片算法

对应HintShardingAlgorithm,用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。

分片策略

包含分片键和分片算法,由于分片算法的独立性,将其独立抽离。真正可用于分片操作的是分片键 + 分片算法,也就是分片策略。目前提供5种分片策略。

  • 标准分片策略

对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的,用于处理=和IN的分片。RangeShardingAlgorithm是可选的,用于处理BETWEEN AND, >, <, >=, <=分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理。

  • 复合分片策略

对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此并未进行过多的封装,而是直接将分片键值组合以及分片操作符透传至分片算法,完全由应用开发者实现,提供最大的灵活度。

  • 行表达式分片策略

对应InlineShardingStrategy。使用Groovy的表达式,提供对SQL语句中的=和IN的分片操作支持,只支持单分片键。对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8,而分成8张表,表名称为t_user_0t_user_7

  • Hint分片策略

对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。

  • 不分片策略

对应NoneShardingStrategy。不分片的策略。

分片策略配置

对于分片策略存有数据源分片策略和表分片策略两种维度。

  • 数据源分片策略(根据什么规则进行分库)

对应于DatabaseShardingStrategy。用于配置数据被分配的目标数据源。

  • 表分片策略(根据什么规则进行分表)

对应于TableShardingStrategy。用于配置数据被分配的目标表,该目标表存在与该数据的目标数据源内。故表分片策略是依赖与数据源分片策略的结果的。

两种策略的API完全相同。

自增主键生成策略

通过在客户端生成自增主键替换以数据库原生自增主键的方式,做到分布式主键无重复。

逻辑表与物理表联系

这个逻辑表与真实表有什么联系或者怎么理解呢?比如说,我原来只有一张表t_order我只需要精准的指向这个表进行操作即可,现在分表后有了t_order_0t_order_9这十张表,那么我在代码中要自己精确指定操作到某张表吗?当然不是,我们仍像原来一样,只需要定义好表的分片策略后,以及定位好t_order_0t_order_9这十张表的逻辑表名为t_order后,我们自己在代码中仍像原来一样操作t_order,sharding-jdbc就会根据策略选择到具体的表(更改sql 找到对应库与表)进行操作!

三、Sharding-JDBC的实际运用

本次演示采用 springboot版本:2.2.5.RELEASEsharding-jdbc版本:sharding-jdbc-spring-boot-starter:4.0.0-RC1

(1)数据库与表准备

image-20210306184313583

(2)项目核心依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
            <scope>runtime</scope>
        </dependency>
        <!--sharding jdbc springboot-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>

(3)sharding-jdbc的配置

(1)配置

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 打印sql
    props:
      sql:
        show: true
    # 数据源配置
    datasource:
      # names必须对应数据库的名字,且与下方配置相对应
      names: alarm-sc,alarm-bj
      alarm-sc:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xxxx:3306/alarm-sc?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: root
      alarm-bj:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xxx:3306/alarm-bj?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: root
    sharding:
      # 默认数据库策略 如果不想分库,可不设此配置
      defaultDatabaseStrategy:
        # 分库
        standard:
          # 分片键 zone  表示根据 数据zone这一列的值与自定义的算法进行分片
          shardingColumn: zone
          # 精准分片算法
          preciseAlgorithmClassName: com.leilei.algorithm.SubDataBasePreciseAlgorithm
          # 范围分片算法
          rangeAlgorithmClassName: com.leilei.algorithm.SubDataBaseRangeAlgorithm
      # 表策略配置
      tables:
        # vehicle_alarm 是逻辑名表
        vehicle_alarm:
          table-strategy:
            standard:
              #分片键 device_time 即根据device_time这列的值与设定的算法进行分表
              shardingColumn: device_time
              # 精准分片算法
              preciseAlgorithmClassName: com.leilei.algorithm.SubTableTimePreciseAlgorithm
              # 范围分片算法
              rangeAlgorithmClassName: com.leilei.algorithm.SubTableTimeRangeAlgorithm
          keyGenerator:
            # 唯一值生成类型为雪花算法
            type: SNOWFLAKE
            # 对id列采用 sharding-jdbc的全局id生成策略
            column: id
# mybatis-plus相关配置
mybatis-plus:
  type-aliases-package: com.leilei.entity
  global-config:
    db-config:
      id-type: auto
      table-underline: true
      logic-not-delete-value: 0
      logic-delete-value: 1
  configuration:
    cache-enabled: false
    map-underscore-to-camel-case: true

您也可以选择简单的配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 打印sql
    props:
      sql:
        show: true
    # 数据源配置
    datasource:
      names: sc
      sc:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xxx:3306/alarm_sc?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: root
    sharding:
      # 表策略配置
      tables:
        # vehicle_alarm 是逻辑表
        vehicle_alarm:
          # 分表节点 可以理解为分表后的那些表 比如 vehicle_alarm_1 ,vehicle_alarm_2
          actualDataNodes: sc.vehicle_alarm_$->{1..2}
          tableStrategy:
            inline:
              # 根据哪列分表
              shardingColumn: id
              # 分表算法 例如:Id为奇数 则为vehicle_alarm_2 id为偶数 vehicle_alarm_1
              algorithmExpression: vehicle_alarm_$->{id % 2 + 1}
          # 分表后,sharding-jdbc的全局id生成策略
          keyGenerator:
            type: SNOWFLAKE
            # 对id列采用 sharding-jdbc的全局id生成策略
            column: id

(2)配置解读

分库 ,数据源配置(本示例中使用了两个库,alarm-sc,alarm-bj)

image-20210306175812767

分库核心配置

配置中指明了分片键为zone,表示根据 数据zone这一列的值与自定义的算法进行分片

image-20210306175936631

分表核心配置

分片键 device_time 即根据device_time这列的值与设定的算法进行分表

image-20210306180336352

整体配置预期效果:

image-20210306175420068

由于分表后,我们就没法再使用自增主键了(因为会出现相同ID情况,所以呢,采用了雪花算法生成分布式唯一ID)

image-20210306180444804

Mybatis-plus配置这里不做讲解,如对Mybatis-plus不熟悉的请看我的mybatos-plus相关博客

SpringBoot整合Mybatis-plus(一)基本使用与自定义模板代码生成器

SpringBoot整合Mybatis-plus(二) 多数据源Druid监控,Atomikos处理事务,跨库连表查询

springboot2.3 整合mybatis-plus 高级功能

(3)自定义分片算法

① 自定义精准分表算法
package com.leilei.algorithm;

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

import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * @author lei
 * @version 1.0
 * @date 2021/2/28 16:59
 * @desc 自定义精准分表算法 (根据时间 按月分表)
 */
public class SubTableTimePreciseAlgorithm implements PreciseShardingAlgorithm<Long> {
    /**
     * @param availableTargetNames 所有的分片集 由于我这个算法是指定了分表算法,则这里是逻辑表名列表 目前则为"vehicle_alarm" 
     * @param shardingValue 分片键(指定的那列作为分片条件)
     * @return
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        return shardingValue.getLogicTableName() + "_" + toDate(shardingValue.getValue());
    }

    public static String toDate(Long dateTimeMillSec) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        return sdf.format(new Date(dateTimeMillSec));
    }
}

image-20210306181550428

② 自定义范围分表算法
package com.leilei.algorithm;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedHashSet;

/**
 * @author lei
 * @version 1.0
 * @date 2021/2/28 20:59
 * @desc 自定义范围分表算法 (根据时间范围筛选数据表)
 */
public class SubTableTimeRangeAlgorithm implements RangeShardingAlgorithm<Long> {
    private static final String ACTUAL_TABLE_PREFIX = "vehicle_alarm_";

    /**
     * @param availableTargetNames 所有的分片集 由于我这个算法是指定了分表算法,则这里是逻辑表名列表 目前则为"vehicle_alarm"
     * @param shardingValue 分片键(指定的那列作为分片条件)
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>();
        Range<Long> range = shardingValue.getValueRange();
        Integer start = toDateMonth(range.lowerEndpoint());
        int end = toDateMonth(range.upperEndpoint());
        for (int i = start; i <= end; i = toNextDateMonth(i)) {
            result.add(ACTUAL_TABLE_PREFIX + i);
        }
        return result;
    }

    public static Integer toDateMonth(Long dateTimeMillSec) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        return Integer.valueOf(sdf.format(new Date(dateTimeMillSec)));
    }

    public static Integer toNextDateMonth(Integer dateMonth) {
        SimpleDateFormat dft = new SimpleDateFormat("yyyyMM");
        try {
            Date date = dft.parse(dateMonth.toString());
            Calendar cal = Calendar.getInstance();
            cal.setTime(new Date(date.getTime()));
            cal.add(Calendar.MONTH, 1);
            String preMonth = dft.format(cal.getTime());
            return Integer.valueOf(preMonth);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

image-20210306181428418

③ 自定义精准分库算法
package com.leilei.algorithm;

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

import java.util.Collection;
import java.util.Objects;

/**
 * @author lei
 * @version 1.0
 * @date 2021/3/2 21:52
 * @desc 自定义精准分片策略 根据Zone(数据库)
 */
public class SubDataBasePreciseAlgorithm implements PreciseShardingAlgorithm<String> {
    /**
     * @param availableTargetNames 所有的分片集 由于我这个算法是指定了分表算法,则这里是库列表即names 指定的名字列表
     * @param shardingValue 分片键(指定的那列作为分片条件)
     * @return
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        for (String databaseName : availableTargetNames) {
            String zoneValue = shardingValue.getValue();
            zoneValue = zoneValue == null || Objects.equals(zoneValue.replace(" ", ""), "") ? "sc" : zoneValue;
            if (databaseName.endsWith(zoneValue)) {
                return databaseName;
            }
        }
        throw new IllegalArgumentException();
    }
}

image-20210306183556703

④ 自定义范围分库算法
package com.leilei.algorithm;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author lei
 * @version 1.0
 * @date 2021/3/2 22:10
 * @desc 自定义范围分片策略 根据Zone(数据库)
 */
public class SubDataBaseRangeAlgorithm implements RangeShardingAlgorithm<String> {
    public static final String DB_PREFIX = "alarm-";

    /**
     * @param availableTargetNames 所有的分片集 由于我这个算法是指定了分表算法,则这里是库列表即names 指定的名字
     * @param shardingValue 分片键(指定的那列作为分片条件)
     * @return
     * @desc 由于我这里指定的一个业务列作为分库策略 比如zone为(四川 sc,北京 bj,山西 sx等)故此这里采用ThreadLocal 每次有涉及到分库操作时,
     * 先将需要查询的库,存入到threadLocal中。详情请查看单元测试
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> shardingValue) {
        List<String> dbList = DbSelectUtil.DB_SELECTOR.get();
        System.out.println(dbList);
        Collection<String> finaDbList = new LinkedList<>();
        if (dbList.isEmpty()) {
            finaDbList.add(DB_PREFIX + "sc");
        }else {
            finaDbList.addAll(dbList.parallelStream().map(e->DB_PREFIX+e).collect(Collectors.toList()));
        }
        return finaDbList;
    }
    
    
}


public class DbSelectUtil {
    public static ThreadLocal<List<String>> DB_SELECTOR = new ThreadLocal<>();
}

image-20210306183713324

(4)测试

插入数据

    @Test
    public void testInsertAlarm() {
        VehicleAlarm vehicleAlarm = new VehicleAlarm();
        long[] timeArr = {1611827071123L, 1612274617000L, 1614693856000L};
        for (int i = 1; i < 20; i++) {
            vehicleAlarm.setLicensePlate("川E" + System.currentTimeMillis()/123456789);
            vehicleAlarm.setPlateColor("黄");
            vehicleAlarm.setZone(i % 2 == 0 ? "sc" : "bj");
            vehicleAlarm.setDeviceTime(timeArr[(int) (Math.random() * 3)]);
            vehicleAlarmMapper.insert(vehicleAlarm);
        }
    }

image-20210306184736863

由于我们开启了SQL打印,可以看到sharding-jdbc对我们的sql进行的改写,找到了对应的库与表

将真实sql与逻辑sql一一打印了出来

image-20210306185013098

image-20210306185132881

    @Test
    public void testEq() {
        LambdaQueryWrapper<VehicleAlarm> wrapper = new QueryWrapper<VehicleAlarm>().lambda();
        wrapper.eq(VehicleAlarm::getZone, "bj");
        List<VehicleAlarm> vehicleAlarms = vehicleAlarmMapper.selectList(wrapper);
        System.out.println(vehicleAlarms);
    }
逻辑sql:Logic SQL: SELECT  id,license_plate,plate_color,device_time,zone  FROM vehicle_alarm  WHERE (zone = ? AND device_time = ?)

实际sql:Actual SQL: alarm-bj ::: SELECT  id,license_plate,plate_color,device_time,zone  FROM vehicle_alarm_202102 
 WHERE (zone = ? AND device_time = ?) ::: [bj, 1612274617000]

测试范围选择

    @Test
    public void testTableDbBetween1() {
        LambdaQueryWrapper<VehicleAlarm> wrapper = new QueryWrapper<VehicleAlarm>().lambda();
        List<String> zoneList = Arrays.asList("bj","sc");
        DbSelectUtil.DB_SELECTOR.set(zoneList);
        wrapper.between(VehicleAlarm::getZone, "bj","sc")
                .eq(VehicleAlarm::getDeviceTime,  1614517065621L);
        List<VehicleAlarm> vehicleAlarms = vehicleAlarmMapper.selectList(wrapper);
        DbSelectUtil.DB_SELECTOR.remove();
        System.out.println(vehicleAlarms);
    }

image-20210306185656083

(5)注意事项:

如果您定义了分片策略后,您的逻辑SQL操作必须要命中对应的分片策略 例如精准 ,范围等(例如我必须要 EQ device_time,EQ zone,BeTween device_time zone等等,如果未命中分片算法,则sharding-jdbc无法知道您要作用到哪个库与表,会使用默认的逻辑表与库,如过您不存在这样的库与表,则会报错库 或者表不存在的异常)


项目地址:springboot-sharding-jdbc

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐