ShardingSphere实践（1）水平分表_dynamic-datasource shardingsphere postgresql水平分表

水平分表

水平分表，是一种数据库分表策略，它将一个逻辑表按照某个规则拆分成多个物理表，将数据水平分散存储在这些物理表中。每个物理表通常存储一部分数据，使得数据库的负载可以均匀地分布在多个表中，提高数据库的性能和扩展性。

水平分表的主要思想是根据某个列或一组列的值将数据进行分割，并将相同分割规则的数据存储到不同的物理表中。这样，当执行查询时，可以只针对特定的物理表进行操作，避免全表扫描，提高查询效率。

常见的水平分表策略包括：

1.范围分表：按照某个范围将数据进行划分，例如按照时间范围将订单数据分散到不同的表中，如user_2023、user_2024等。

2.散列分表：根据某个列的散列值将数据分布到多个表中，例如使用用户ID的散列值将用户数据分散到不同的表中。

3.列分表：根据某个列的特定值将数据分散到不同的表中，例如按照地区将用户数据分散到不同的表中，如user_shanghai、user_beijing等。

下面我们进行基于散列分表策略的实践

一.加入依赖

参考我的pom文件

<properties>
        <mysql.version>5.1.20</mysql.version>
        <mybatis.plus.boot.starter.version>3.4.2</mybatis.plus.boot.starter.version>
        <lombok.version>1.18.24</lombok.version>
        <druid.version>1.1.22</druid.version>
        <shardingsphere.version>4.0.0-RC1</shardingsphere.version>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
 
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
            <scope>provided</scope>
            <version>${lombok.version}</version>
        </dependency>
 
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis.plus.boot.starter.version}</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
 
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
    </dependencies>

二.配置ShardingSphere

application.properties

# 单数据库分表，指定数据源名称
spring.shardingsphere.datasource.names=master0
 
# 分表时一个实体类对应多张表，设置为true覆盖
spring.main.allow-bean-definition-overriding=true
 
#配置第一个数据源，包括连接池、驱动类、数据库地址、用户名、密码
spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://localhost:3306/shardingsphere-demo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=123456
 
#指定数据库分布情况，数据库里面表分布情况，如下user表，对应user_0，user_1，user_2三张表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master0.user_$->{0..2}
 
# 指定表的主键id，生成策略，SNOWFLAKE（雪花算法）
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
 
# 指定表分片策略，分片列为id字段，根据id字段的值进行分片，分片策略为id的值 % 3（表的个数），余数为0的放入user_0表，为1的放入user_1表，为2的放入user_2表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 3}
 
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

三.建表

建立user_0、user_1、user_2三张表

CREATE TABLE `user_0` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `user_1` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `user_2` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

四.编写业务代码

实体类

@Data
public class User {
    @TableId
    private Long id;
    private String name;
}

UserMapper

public interface UserMapper extends BaseMapper<User> {
}

UserService 

public interface UserService extends IService<User> {
}

UserServiceImpl

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}

五.测试

@SpringBootTest(classes = ShardingsphereDemoApplication.class)
public class UserServiceTest {
 
    @Autowired
    private UserService userService;
 
    @Test
    public void testUser() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setName("user_" + i);
            userService.save(user);
        }
        List<User> list = userService.list();
        for (User user: list) {
            System.out.println(user.toString());
        }
    }
}

我们往user表中插入10条数据，结果如下

数据根据id取余后的数分别分配到三张表中，同时通过

userService.list() 

 查询出三张表的所有数据，日志中，可以看到实际上执行了3个SQL，从三张表中查出所有数据

自定义分片策略

范围分表

上面实践了如何通过散列策略进行水平分表，这里通过自定义分片策略，实现根据数据的创建时间来分配到不同的表上

1.自定义日期分片算法类

/**
 * 日期分片算法
 */
@Slf4j
public class DateShardingAlgorithm implements PreciseShardingAlgorithm<LocalDateTime> {
 
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
        // 将传进来的createTime拼接为表名
        String table = shardingValue.getLogicTableName() + "_" + shardingValue.getValue().getYear();
        // 匹配分表
        for (String targetName : availableTargetNames) {
            if (targetName.equals(table)) {
                return targetName;
            }
        }
        throw new IllegalArgumentException();
    }
}

2.User实体类上增加一个createTime字段

@Data
public class User {
    @TableId
    private Long id;
    private String name;
    @TableField(value = "create_time")
    private LocalDateTime createTime;
}

3.分片策略配置修改

spring.shardingsphere.sharding.tables.user.actual-data-nodes=master0.user_$->{2023..2024}
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.demo.config.DateShardingAlgorithm

4.创建user_2023、user_2024两张表

CREATE TABLE `user_2023` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `user_2024` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5.测试

@SpringBootTest(classes = ShardingsphereDemoApplication.class)
public class UserServiceTest {
 
    @Autowired
    private UserService userService;
 
    @Test
    public void testUser() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setName("user_" + i);
            LocalDateTime now = LocalDateTime.now();
            if (i > 6) {
                now = now.minusYears(1);
            }
            user.setCreateTime(now);
            userService.save(user);
        }
    }
}

列分表

通过自定义策略，我们还可以根据用户所在区域进行分片

1.自定义区域分片算法类

/**
 * 区域分片算法
 */
@Slf4j
public class AreaShardingAlgorithm implements PreciseShardingAlgorithm<String> {
 
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        // 将传进来的area拼接为表名
        String table = shardingValue.getLogicTableName() + "_" + shardingValue.getValue();
        // 匹配分表
        for (String targetName : availableTargetNames) {
            if (targetName.equals(table)) {
                return targetName;
            }
        }
        throw new IllegalArgumentException();
    }
}

2.User实体类上增加一个area字段

@Data
public class User {
    @TableId
    private Long id;
    private String name;
    private String area;
}

3.分片策略配置修改

spring.shardingsphere.sharding.tables.user.actual-data-nodes=master0.user_shanghai,master0.user_beijing
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=area
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.demo.config.AreaShardingAlgorithm

4.创建user_shanghai、user_beijing两张表

CREATE TABLE `user_shanghai` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `user_beijing` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5.测试

@SpringBootTest(classes = ShardingsphereDemoApplication.class)
public class UserServiceTest {
 
    @Autowired
    private UserService userService;
 
    @Test
    public void testUser() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setName("user_" + i);
            String area = "shanghai";
            if (i > 6) {
                area = "beijing";
            }
            user.setArea(area);
            userService.save(user);
        }
    }
}

  

水平分表的优缺点

优点

水平分表将大量的数据分配到不同的表上，解决了单表过大，检索性能降低的问题，为后期数据迁移、扩展、备份提供了很大的便利，当我们根据id进行查询时，速度非常快，ShardingSphere将查询的id根据分片策略找到数据存放的分片，在分片表上查询

执行下面的语句进行测试

User byId = userService.getById(1780139288814874625L);

可见此语句直接在user_2表中进行查找

缺点

分页查询问题

在分页查询上，我们希望像在单表中一样，按照顺序进行分页，而在水平分表时，数据被分配到多张表上，ShardingSphere会将三张表的数据拼接为一张全表，再进行分页，由于分片的原因，查询出来的数据并不能像我们预想的那样垂直分页，而是乱序的分页

执行下面SQL进行测试

Page page = new Page();
page.setCurrent(1);
page.setSize(3);
Page<User> data = userService.page(page);

最终查询出来的结果不是name等于user_0、user_1、user_2的数据，而是user_0表上的user_2、user_3、user_6

其他条件查询

在单表的情况下，如果我们执行以下SQL

User byId = userService.getOne(Wrappers.<User>lambdaQuery()
                .eq(User::getName, "user_6")
                .last(" limit 1"));

那么在检索到user_6时就会停止了，而如果通过ShardingSphere进行分表查询

可以发现，在三张表上都执行了同样的SQL