分库分表

分库分表概述

当数据库性能出现瓶颈时，可以通过分库分表来提高性能。

拆分方式

水平拆分

每个表结构都一样，比如订单表数据量较大，可以拆分成多个表分摊压力。
垂直拆分

一个多字段的表拆分成多个表。比如可以把订单表拆分成订单简略信息表和订单详情表，提高数据库性能。

分库分表解决方案

常用的分库分表解决方案：

Sharding Sphere(Sharding JDBC)
MyCat

Sharding Sphere下属的JDBC组件是一个Java Client，可以很方便地在应用中使用，不需要额外进行部署。而MyCat需要在服务器进行安装，需要较高的运维能力。综合来看，Sharding Sphere使用起来更方便更能满足要求。

Sharding Sphere实践

环境参数：

Spring Boot 2.3.2.RELEASE
shardingsphere-jdbc:5.0.0-alpha
Mysql 5.5
Jpa + Hikari

引入依赖

1
2
3
4
5


<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.0.0-alpha</version>
</dependency>

创建库表及实体类

为了实现分库分表，需要新建两个数据库，每个数据库中包含相同的两张订单表。数据库初始化SQL如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE TABLE `t_order_0` (
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_1` (
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新建两个数据库ds0、ds1之后，分别在这两个数据库中执行一次上述SQL即可。最终效果如下：

数据库	表
ds0	t_order_0
	t_order_1
ds1	t_order_0
	t_order_1

对应的实体类如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


import lombok.Data;

import javax.persistence.*;

@Data
@Entity
@Table(name = "t_order")
public class OrderEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderId;

    private Integer userId;

}

配置Sharding Sphere

添加对应的分库分表配置项:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


server.port=8085



# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.common.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.common.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.common.username=root
spring.shardingsphere.datasource.common.password=123456


spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?serverTimezone=UTC&useSSL=false

# 配置 t_order 表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..1}

# 配置分库策略，此处设置为根据订单表t_order的user_id进行分库
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=database-inline

# 配置分表策略，此处设置为根据订单表t_order的order_id进行分库
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=table-inline

# 省略配置 t_order_item 表规则...
# ...

# 配置 分片算法
# 注意，此处的database-inline和table-inline是上方确定分库分表策略时定义的名字
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.databaseinline.props.algorithm-expression=ds$->{user_id % 2}
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_order_$->{order_id % 2}


# 分布式序列策略配置,此处设置order_id为全局分布式雪花算法策略
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake

# 分布式序列算法配置
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123

# Shardingsphere 打印SQL log配置
spring.shardingsphere.props.sql-show=true

使用及测试

使用JPA创建对应的DAO层：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface OrderDao extends JpaRepository<OrderEntity,Long> {

    OrderEntity findByOrderId(Long orderId);

    List<OrderEntity> findByUserId(Integer userId);
}

创建对应的测试类：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42


import lombok.extern.slf4j.Slf4j;
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.util.List;
import java.util.Random;

@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class DistributedShardingApplicationTest {

    @Autowired
    private OrderDao orderDao;

    @Test
    public void insert(){
        for (int i = 0 ;i<10;i++){
            OrderEntity orderEntity = new OrderEntity();
            orderEntity.setUserId(new Random().nextInt(999));
            orderDao.save(orderEntity);
        }

    }

    @Test
    public void findByOrderId(){
        OrderEntity orderEntity = orderDao.findByOrderId(558795615644397569L);
        log.info("OrderId={}",orderEntity);

    }

    @Test
    public void findByUserId(){
        List<OrderEntity> orderEntity = orderDao.findByUserId(150);
        log.info("UserId={}",orderEntity);

    }
}

运行insert()方法，可以看到日志中向数据库插入了十条数据，而且根据user_id和order_id的奇偶结果分别插入到了不同的数据库：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


2021-01-21 12:19:18.579  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.579  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.579  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order_0 (user_id, order_id) values (?, ?) ::: [467, 558988141005352960]
2021-01-21 12:19:18.657  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.657  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.657  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_1 (user_id, order_id) values (?, ?) ::: [336, 558988142448193537]
2021-01-21 12:19:18.680  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.680  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.680  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_0 (user_id, order_id) values (?, ?) ::: [526, 558988142540468224]
2021-01-21 12:19:18.688  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.688  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.689  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_1 (user_id, order_id) values (?, ?) ::: [382, 558988142569828353]
2021-01-21 12:19:18.696  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.696  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.697  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_0 (user_id, order_id) values (?, ?) ::: [678, 558988142607577088]
2021-01-21 12:19:18.704  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.704  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.705  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order_1 (user_id, order_id) values (?, ?) ::: [357, 558988142641131521]
2021-01-21 12:19:18.718  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.718  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.718  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_0 (user_id, order_id) values (?, ?) ::: [732, 558988142704046080]
2021-01-21 12:19:18.718  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.718  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.718  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_1 (user_id, order_id) values (?, ?) ::: [860, 558988142704046081]
2021-01-21 12:19:18.734  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.734  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.734  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into t_order_1 (user_id, order_id) values (?, ?) ::: [739, 558988142771154945]
2021-01-21 12:19:18.734  INFO 10096 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into t_order (user_id) values (?)
2021-01-21 12:19:18.734  INFO 10096 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2021-01-21 12:19:18.734  INFO 10096 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into t_order_0 (user_id, order_id) values (?, ?) ::: [710, 558988142771154946]

同理，调用findByOrderId和findByUserId方法也可发现Sharding Sphere已经根据user_id和order_id的奇偶数去对应的库表中查询了。

文章目录