JAVA开发（ShardingSphere提供分库分表功能）

奋力向前123

已于 2023-04-20 14:54:07 修改

阅读量955

点赞数 1

分类专栏： java 文章标签： java 数据库 mysql

于 2023-02-06 10:26:57 首次发布

本文链接：https://blog.csdn.net/dongjing991/article/details/128897214

版权

java 专栏收录该内容

396 篇文章 26 订阅

订阅专栏

背景：

在高性能系统的要求下，复杂的系统当单表数据量增加到几千万甚至上亿条记录时，查询延迟无疑是影响高性能系统的瓶颈。业内提供的解决的方案是分库分表，意思是将数据量大的库进行分开，数据量大的表进行分割。通常有横向分割和纵向分割两种模式，其中横向分割较容易。但是当进行数据库分库分表，就产生了另外的问题，就是分布式问题。此时就需要封装一个强大的工具进行屏蔽，使我们在写代码的时候就像只对一个数据库或者一个表进行操作一样。这时ShardingShere就应运而生了。

ShardingShere能够提供的强大功能。

What is ShardingSphere

Introduction

Apache ShardingSphere is an ecosystem to transform any database into a distributed database system, and enhance it with sharding, elastic scaling, encryption features & more.

The project is committed to providing a multi-source heterogeneous, enhanced database platform and further building an ecosystem around the upper layer of the platform. Database Plus, the design philosophy of Apache ShardingSphere, aims at building the standard and ecosystem on the upper layer of the heterogeneous database. It focuses on how to make full and reasonable use of the computing and storage capabilities of existing databases rather than creating a brand new database. It attaches greater importance to the collaboration between multiple databases instead of the database itself.

Apache ShardingSphere是一个生态系统，可以将任何数据库转换为分布式数据库系统，并通过分片、弹性伸缩、加密功能等进行增强。

该项目致力于提供一个多源异构、增强型数据库平台，并进一步围绕平台上层构建生态系统。Database Plus是Apache ShardingSphere的设计理念，旨在在异构数据库的上层构建标准和生态系统。它关注的是如何充分合理地利用现有数据库的计算和存储能力，而不是创建一个全新的数据库。它更重视多个数据库之间的协作，而不是数据库本身

ShardingSphere-JDBC is a lightweight Java framework that provides additional services at Java’s JDBC layer.

springBoot的使用：

引入依赖

   <!-- 分库分表 -->
 
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-namespace</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>

版本：

<sharding-sphere.version>4.0.0-RC2</sharding-sphere.version>
<shardingsphere.version>5.0.0-beta</shardingsphere.version>

以对一个单据表进行分表为例

先建一个实体：

package com.example.wyd.dao;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.util.Date;
@Data
@TableName("t_bill")
public class Bill {
    private Long orderId;
    private Integer userId;
    private Long addressId;
    private String status;
    private Date createTime;
    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public void setAddressId(Long addressId) {
        this.addressId = addressId;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

maybitsplus映射接口

package com.example.wyd.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.wyd.dao.Bill;
public interface BillMapper extends BaseMapper<Bill> {

}

服务类接口：

package com.example.wyd.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.wyd.dao.Bill;
public interface BillService extends IService<Bill> {

}

服务类接口实现：

package com.example.wyd.service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.wyd.dao.Bill;
import com.example.wyd.mapper.BillMapper;
import org.springframework.stereotype.Service;
@Service
public class BillServiceImpl extends ServiceImpl<BillMapper, Bill> implements BillService {

}

自定义分库算法：

package com.example.wyd;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
//自定义数据库分片算法
public class DBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        //真实数据库节点
        availableTargetNames.stream().forEach((item) -> {
           System.out.println("actual db:" + item);
        });
        //逻辑表以及分片的字段名
        System.out.println("logicTable:"+shardingValue.getLogicTableName()+";shardingColumn:"+ shardingValue.getColumnName());
        //分片数据字段值
        System.out.println("shardingColumn value:"+ shardingValue.getValue().toString());
        //获取字段值
        long orderId = shardingValue.getValue();
        //分片索引计算 0 , 1
        long db_index = orderId & (2 - 1);
        for (String each : availableTargetNames) {
            if (each.equals("ds"+db_index)) {
                //匹配的话，返回数据库名
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}

自定义分表算法:

package com.example.wyd;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
import java.util.Date;
//表按日期自定义分片
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        //真实数据库节点
        availableTargetNames.stream().forEach((item) -> {
            System.out.println("actual db:" + item);
        });
        //逻辑表以及分片的字段名
        System.out.println("logicTable:"+shardingValue.getLogicTableName()+";shardingColumn:"+ shardingValue.getColumnName());
        //分片数据字段值
        System.out.println("shardingColumn value:"+ shardingValue.getValue().toString());
        //获取表名前缀
        String tb_name = shardingValue.getLogicTableName() + "_";
        //根据日期分表
        Date date = shardingValue.getValue();
        String year = String.format("%tY", date);
        String mon =String.valueOf(Integer.parseInt(String.format("%tm", date)));
        //String dat = String.format("%td", date); //也可以安装年月日来分表
        // 选择表
        tb_name = tb_name + year + "_" + mon;
        //实际的表名
        System.out.println("tb_name:" + tb_name);
        for (String each : availableTargetNames) {
            //System.out.println("availableTableName:" + each);
            if (each.equals(tb_name)) {
                //返回物理表名
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}

yml需要配置的数据源信息：

server.port=8080
#########################################################################################################
# 配置ds0 和ds1两个数据源
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://127.0.0.1:3306/mydb?characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username = uname
spring.shardingsphere.datasource.ds0.password = pwd

#ds1 配置
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://127.0.0.1:3306/mydb2characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username = uname
spring.shardingsphere.datasource.ds1.password = pwd
#########################################################################################################
# 默认的分库策略：id取模
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{id % 2}
#########################################################################################################
spring.shardingsphere.sharding.tables.t_bill.actual-data-nodes=ds$->{0..1}.t_bill_$->{2021..2021}_$->{1..12}
#数据库分片字段
spring.shardingsphere.sharding.tables.t_bill.database-strategy.standard.sharding-column=order_id
#自定义数据库分片策略
spring.shardingsphere.sharding.tables.t_bill.database-strategy.standard.precise-algorithm-class-name=com.example.wyd.DBShardingAlgorithm
#表分片字段
spring.shardingsphere.sharding.tables.t_bill.table-strategy.standard.sharding-column=create_time
#自定义表分片策略
spring.shardingsphere.sharding.tables.t_bill.table-strategy.standard.precise-algorithm-class-name=com.example.wyd.TableShardingAlgorithm
#########################################################################################################
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_bill.key-generator.column = order_id
spring.shardingsphere.sharding.tables.t_bill.key-generator.type = SNOWFLAKE
spring.shardingsphere.sharding.tables.t_bill.key-generator.props.worker.id=123
#########################################################################################################
spring.shardingsphere.props.sql.show = true

测试使用：

package com.example.wyd.controller;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.wyd.dao.Bill;
import com.example.wyd.service.BillService;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("/api")
public class HomeController {
    @Autowired
    private BillService billService;
    //http://localhost:8080/api/query?start=2021-02-07%2000:00:00&end=2021-03-07%2000:00:00
    @RequestMapping("/query")
    public List<Bill> queryList(@RequestParam("start") String start, @RequestParam("end") String end) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse(start);
            Date date2 = sdf.parse(end);
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.ge("create_time",date)
                    .and(qw-> qw.le("create_time", date2)).last("limit 1,10");
            List<Bill> billIPage = billService.list(queryWrapper);
            System.out.println(billIPage.size());
            billIPage.forEach(System.out::println);
            return billIPage;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //http://localhost:8080/api/save?userid=999&addressId=999&status=M&date=2021-03-07%2000:00:00
    @RequestMapping("/save")
    public String Save(@RequestParam("userid") int userId, @RequestParam("addressId") long AddressId,
                       @RequestParam("status") String status
            ,@RequestParam("date") String strDate) {
        String ret ="0";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse(strDate);
            Bill bill = new Bill();
            bill.setUserId(userId);
            bill.setAddressId(AddressId);
            bill.setStatus(status);
            bill.setCreateTime(date);
            boolean isOk = billService.save(bill);
            if (isOk){
                ret ="1";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return ret;
    }
}

package com.example.wyd;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.wyd.dao.Bill;
import com.example.wyd.dao.Order;
import com.example.wyd.service.BillService;
import com.example.wyd.service.OrderService;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class OrderServiceImplTest extends WydApplicationTests {
    @Autowired
    private BillService billService;
    @Test
    public void testBillSave(){
        for (int i = 0 ; i< 120 ; i++){
            Bill bill = new Bill();
            bill.setUserId(i);
            bill.setAddressId((long)i);
            bill.setStatus("K");
            bill.setCreateTime((new Date(new DateTime(2021,(i % 11)+1,7,00, 00,00,000).getMillis())));
            billService.save(bill);
        }
    }
    @Test
    public void testGetByOrderId(){
        long id = 626038622575374337L; //根据数据修改，无数据会报错
        QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("order_id", id);
        Bill bill = billService.getOne(queryWrapper);
        System.out.println(bill.toString());
    }

    @Test
    public void testGetByDate(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse("2021-02-07 00:00:00");
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("create_time",date);
            List<Bill> billIPage = billService.list(queryWrapper);
            System.out.println(billIPage.size());
            System.out.println(billIPage.toString());
        } catch (ParseException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void testGetByDate2(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse("2021-02-07 00:00:00");
            Date date2 = sdf.parse("2021-03-07 00:00:00");
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.ge("create_time",date)
            .and(qw-> qw.le("create_time", date2));
            List<Bill> billIPage = billService.list(queryWrapper);
            System.out.println(billIPage.size());
            billIPage.forEach(System.out::println);

        } catch (ParseException e) {
            e.printStackTrace();
        }

    }
}

奋力向前123

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
JAVA开发（ShardingSphere提供分库分表功能）

在高性能系统的要求下，复杂的系统当单表数据量增加到几千万甚至上一条记录时，查询延迟无疑是影响高性能系统的瓶颈。业内提供的解决的方案是分库分表，意思是将数据量大的库进行分开，数据量大的表进行分割。通常有横向分割和纵向分割两种模式，其中横向分割较容易。但是当进行数据库分库分表，就产生了另外的问题，就是分布式问题。此时就需要封装一个强大的工具进行屏蔽，使我们在写代码的时候就像只对一个数据库或者一个表进行操作一样。这时ShardingShere就应运而生了。
复制链接

扫一扫