分库分表 — Shardingjdbc
分库分表:分库分表
为什么分表? 肯定是因为需要
怎么进行分表?垂直分表和水平分表
什么是垂直分表?如果表过大,垂直分表就是按照业务角度将大列分为几个表,划分的原则一般是频繁变化的分为一个表,不频繁变化的,尤其大字段,如text和blob字段这种影响io性能的划分为另外一个表中,这样就实现了垂直分表。如下图:
什么是水平分表?就是将数据根据策略分到不同的表中,从而使数据均匀的分布在不同的数据库和表中,也是最常见的分表方式。如下就是将一个表分为四个表:
将ds库中的order表分成了ds0库的order0和order1以及ds1库的order0和order1表,
分库分表的策略是根据数据的sharding_db_id字段与2取余来决定放到ds0还是ds1,
如果取余是0就放到ds0,如果取余是1就放到ds1.
同理分表也是采用相同的策略,只不过根据列shardind_table_id与2取余。
这样一行数据就确定了这条数据应该放到哪个库那个表中。从而实现数据的分库分表。
同时,为了保证全局的order数据在不同的库和表中保持全局唯一,我们用key_id字段
存储雪花算法生成的全局id。
下面就给大家展示一下springboot_shardingjdbc_mybatis实现分库分表
先创建数据库和数据表
CREATE DATABASE ds_0; #创建数据库ds_0
DROP TABLE IF EXISTS `order0`; #创建表order0
CREATE TABLE `order0` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`sharding_db_id` int(0) NOT NULL COMMENT '分库用的ID',
`sharding_table_id` int(0) NOT NULL COMMENT '分表用的ID',
`key_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '全局id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `order1`; #创建表order1
CREATE TABLE `order1` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`sharding_db_id` int(0) NOT NULL COMMENT '分库用的ID',
`sharding_table_id` int(0) NOT NULL COMMENT '分表用的ID',
`key_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '全局id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
CREATE DATABASE ds_1; #创建数据库ds_1
DROP TABLE IF EXISTS `order0`; #创建表order0
CREATE TABLE `order0` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`sharding_db_id` int(0) NOT NULL COMMENT '分库用的ID',
`sharding_table_id` int(0) NOT NULL COMMENT '分表用的ID',
`key_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '全局id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `order1`; #创建表order1
CREATE TABLE `order1` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`sharding_db_id` int(0) NOT NULL COMMENT '分库用的ID',
`sharding_table_id` int(0) NOT NULL COMMENT '分表用的ID',
`key_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '全局id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
pom.xml的主要依赖内容
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>RELEASE</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
Mybatis 数据对象 Domain
/*
* 永旺数字科技有限公司版权所有.
*/
package com.example.demo.mapper.domain;
/**
* (与数据库表字段一一对应的实体类,公有字段继承至父类)
*
* @author 今天例外
* @version 1.0.0
* @date 2021-04-07
*/
public class OrderDomain {
private static final long serialVersionUID = 1L;
/**
* 主键ID
*/
private Long Id;
private Long shardingDbId;
private Long shardingTableId;
public OrderDomain(Long shardingDbId, Long shardingTableId) {
this.shardingDbId = shardingDbId;
this.shardingTableId = shardingTableId;
}
public OrderDomain() {
}
public static long getSerialVersionUID() {
return serialVersionUID;
}
public Long getShardingDbId() {
return shardingDbId;
}
public void setShardingDbId(Long shardingDbId) {
this.shardingDbId = shardingDbId;
}
public Long getShardingTableId() {
return shardingTableId;
}
public void setShardingTableId(Long shardingTableId) {
this.shardingTableId = shardingTableId;
}
public Long getId() {
return Id;
}
public void setId(Long id) {
Id = id;
}
}
Mybatis XML文件
注意这里的表名用的是配置分表时的抽象表名,而不是具体的表名
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.OrderMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.example.demo.mapper.domain.OrderDomain">
<id column="id" property="id" />
<result column="order_id" property="orderId" />
</resultMap>
<!-- 通用查询结果列 -->
<sql id="Base_Column_List">
id,
order_id
</sql>
<!--基础CRUD、分页、列表部分 begin-->
<insert id="save" parameterType="com.example.demo.mapper.domain.OrderDomain"
keyProperty="id" useGeneratedKeys="true">
<!--注意这里的表名用的是配置分表时的抽象表名,而不是具体的表名-->
insert into t_order
(
id,
sharding_db_id,
sharding_table_id
)
values
(
#{id},
#{shardingDbId},
#{shardingTableId}
)
</insert>
</mapper>
Mybatis Mapper文件
/*
* 永旺数字科技有限公司版权所有.
*/
package com.example.demo.mapper;
import com.example.demo.mapper.domain.OrderDomain;
import org.apache.ibatis.annotations.Mapper;
/**
* Mapper接口
*
* @author 今天例外
* @version 1.0.0
* @date 2021-04-07
*/
@Mapper
public interface OrderMapper {
void save(OrderDomain orderDomain);
}
property.yaml内容
mybatis:
mapper-locations: classpath:mapper/*.xml
#sharding-jdbc配置
spring:
shardingsphere:
datasource:
names: ds0,ds1 # 兹定于虚拟库名
# master数据源配置
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8&serverTimezone=UTC
username: root #你的账号
password: 123456 #你的密码
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8&serverTimezone=UTC
username: root #你的账号
password: 123456 #你的密码
# 单库分表 这里只定义一个库ds1
sharding:
tables:
t_order: #自定义虚拟表名 但是在mapper.xml中 操作表的时候 要用这个虚拟表名
actual-data-nodes: ds$->{0..1}.order$->{0..1}
databaseStrategy: #分库策略
inline:
shardingColumn: sharding_db_id #分库用的列名(真实列名)
algorithmExpression: ds${sharding_db_id % 2} #分库算法,根据上面的列里面的数据计算分到哪个库
table-strategy: #分表策略
inline:
sharding-column: sharding_table_id #分表用的列名(真实列名)
algorithm-expression: order$->{sharding_table_id % 2} #分表算法,根据上面的列里面的数据计算分到哪个表
keyGenerator:
type: SNOWFLAKE #雪花算法
column: key_id #用雪花算法生成全局(所有库所有order表)唯一
props:
sql.show: true
如果你想直接测试的话 你可以用我这个单元测试
package com.example.demo;
import com.example.demo.mapper.OrderMapper;
import com.example.demo.mapper.domain.OrderDomain;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.IOException;
import java.sql.SQLException;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootShardingjdbcMybatisApplicationTests {
@Autowired
private OrderMapper orderMapper;
@Test
public void testSharding() throws SQLException, IOException {
// 0库0表
OrderDomain orderDomain_0_0 = new OrderDomain(2l,2l);
orderMapper.save(orderDomain_0_0);
// 1库1表
OrderDomain orderDomain_0_1 = new OrderDomain(2l,1l);
orderMapper.save(orderDomain_0_1);
// 1库0表
OrderDomain orderDomain_1_0 = new OrderDomain(1l,2l);
orderMapper.save(orderDomain_1_0);
// 1库1表
OrderDomain orderDomain_1_1 = new OrderDomain(1l,1l);
orderMapper.save(orderDomain_1_1);
}
}
如果你还搞定不定你就直接下载这个项目的Demo吧