数据库中间件sharding-jdbc实现分库分表

最新推荐文章于 2024-07-26 14:20:58 发布

红丶

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量243

点赞数

分类专栏：数据库中间件文章标签：数据库 java mysql sharding 分库分表

本文链接：https://blog.csdn.net/weixin_42494845/article/details/108940126

版权

数据库中间件专栏收录该内容

8 篇文章 0 订阅

订阅专栏

数据分片工作原理

核心由SQL解析 => 执行器优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并的流程组成。

在这里插入图片描述
SQL解析
分为词法解析和语法解析。先通过词法解析器将SQL拆分为一个个不可再分的单词。再使用语法解析器对SQL进行理解，并最终提炼出解析上下文。解析上下文包括表、选择项、排序项、分组项、聚合函数、分页信息、查询条件以及可能需要修改的占位符的标记。
执行器优化
合并和优化分片条件，如OR等。
SQL路由
根据解析上下文匹配用户配置的分片策略，并生成路由路径。
SQL改写
将SQL改写为在真实数据库中可以正确执行的语句。SQL改写分为正确性改写和优化改写。
SQL执行
通过多线程执行器异步执行。
结果归并
将多个执行结果集归并以便于通过统一的JDBC接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并这几种方式。

垂直切分

按照业务拆分的方式称为垂直分片，又称为纵向拆分，是把不同的表拆到不同的数据库中，它的核心理念是专库专用。

水平切分

水平分片又称为横向拆分。相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段）把同一个表拆到不同的数据库中（或者是把一张表数据拆分成n多个小表）。例如：根据主键分片，偶数主键的记录放入0库（或表），奇数主键的记录放入1库（或表）

下面通过一个测试案例进行分库分表。

在两个mysql服务中的testdb数据库中均创建两个表t_order0，t_order1

create table t_order0(orderId bigint primary key,orderTime datetime,customerId int);
create table t_order1(orderId bigint primary key,orderTime datetime,customerId int);

java测试项目代码

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>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
		<relativePath/>
	</parent>
	<groupId>com.sunyuqi</groupId>
	<artifactId>sharding-jdbc-study</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>sharding-jdbc-study</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>9</java.version>
	</properties>

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

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.6</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>4.0.0-RC1</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.14</version>
		</dependency>

		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.3.1</version>
		</dependency>
		<dependency>
			<groupId>com.sun.xml.bind</groupId>
			<artifactId>jaxb-impl</artifactId>
			<version>2.3.1</version>
		</dependency>
		<dependency>
			<groupId>org.glassfish.jaxb</groupId>
			<artifactId>jaxb-runtime</artifactId>
			<version>2.3.1</version>
		</dependency>
		<dependency>
			<groupId>javax.activation</groupId>
			<artifactId>activation</artifactId>
			<version>1.1</version>
		</dependency>
	</dependencies>

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

配置文件

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.130.128:3306/testdb?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
        username: root
        password: qwe123
        maxPoolSize: 50
        minPoolSize: 1
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.130.129:3306/testdb?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
        username: root
        password: qwe123
        maxPoolSize: 50
        minPoolSize: 1
    sharding:
      tables:
        t_order:
           actual-data-nodes: ds$->{0..1}.t_order$->{0..1}
           #分库策略
           database-strategy:
             inline:
               sharding-column: customerId
               algorithm-expression: ds$->{customerId % 2}
           #分表策略
           table-strategy:
             inline:
               sharding-column: orderId
               algorithm-expression: t_order$->{orderId % 2}
           #主键生成策略
           key-generator:
           	#主键列名
            column: orderId
            #雪花算法
            type: SNOWFLAKE
    props:
      sql.show: true

mybatis:
  configuration:
    map-underscore-to-camel-case: true

model

package com.sunyuqi.model;

public class Order {

	private Long orderId;

	private String orderTime;
	
	private Long customerId;

	public Long getOrderId() {
		return orderId;
	}

	public void setOrderId(Long orderId) {
		this.orderId = orderId;
	}

	public String getOrderTime() {
		return orderTime;
	}

	public void setOrderTime(String orderTime) {
		this.orderTime = orderTime;
	}

	public Long getCustomerId() {
		return customerId;
	}

	public void setCustomerId(Long customerId) {
		this.customerId = customerId;
	}
	
}

dao

package com.sunyuqi.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.sunyuqi.model.Order;

import java.util.List;

@Mapper
public interface OrderDao {

	@Insert("insert into t_order(orderTime,customerId) values(#{orderTime},#{customerId})")
	void addOrder(Order o);
	
	@Select("select orderId,orderTime,customerId from t_order where orderId=#{id}")
	Order get(@Param("id")Long orderId);

	@Select("select orderId,orderTime,customerId from t_order")
	List<Order> findAll();
}

service

package com.sunyuqi.service;

import com.sunyuqi.dao.OrderDao;
import com.sunyuqi.model.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class OrderService {

	@Autowired
	private OrderDao orderDao;
	
	public void addOrder(Order o) {
		this.orderDao.addOrder(o);
	}
	
	public Order getOrder(Long orderId) {
		return this.orderDao.get(orderId);
	}

	public List<Order> findAll(){
		return this.orderDao.findAll();
	}
}

controller

package com.sunyuqi.controller;

import com.sunyuqi.model.Order;
import com.sunyuqi.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

@Controller
@RequestMapping("/order")
public class OrderController {

	@Autowired
	private OrderService orderService;
	
	@RequestMapping("add")
	@ResponseBody
	public String addOrder(Order order) {
		order.setOrderTime(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
		this.orderService.addOrder(order);
		return "添加成功！！";
	}
	
	@RequestMapping("get")
	@ResponseBody
	public Order getOrder(Long orderId) {
		return this.orderService.getOrder(orderId);
	}

	@RequestMapping("findAll")
	@ResponseBody
	public List<Order> findAll(){
		return this.orderService.findAll();
	}
}

引导类

package com.sunyuqi;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ShardingJdbcStudyApplication {

	public static void main(String[] args) {
		SpringApplication.run(ShardingJdbcStudyApplication.class, args);
	}
}

运行引导类

分库分表测试

添加一条数据
在这里插入图片描述
customerId为4，存入ds0中，主键由雪花算法生成，为奇数，存入t_order1中。

再增加一条数据

customerId为5，存入ds1中，主键由雪花算法生成，为奇数，存入t_order1中。

查询所有

红丶

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库中间件sharding-jdbc实现分库分表

在两个mysql服务中的testdb数据库中均创建两个表t_order0，t_order1create table t_order0(orderId int primary key,orderTime int,customerId int);create table t_order1(orderId int primary key,orderTime int,customerId int);
复制链接

扫一扫

专栏目录