本文介绍了如何在SpringBoot项目中结合Mybatis-Plus利用PostgreSQL的声明式分区技术，包括添加依赖、配置数据源、实体类注解、自定义分区策略和注意事项，以优化大型数据表的查询性能和管理效率。
文章目录
什么是分区表
PostgreSQL数据库中分区（Partitioning） 是一种在数据库中管理大量数据的有效方法。通过将表分割成更小的、可管理的部分，可以提高查询性能、简化维护以及优化数据存储。
分区表的应用场景
PostgreSQL 分区表是将大型的逻辑表分割成小型的物理部分，这样可以提高查询性能，管理数据和维护效率。它的主要目的是为了提高大型表的管理和性能。使用场景：
- 数据管理：将数据分散到不同的分区中，可以将数据分散到多个磁盘上，提高I/O性能。
- 性能优化：查询、更新和删除等操作可以直接在对应的分区上执行，不需要扫描全表。
- 数据维护：分区表可以方便地进行数据的备份、恢复和删除。
分区表的实现
PostgreSQL数据库有各式各样的分区实现方式。官方支持的有声明式分区和继承式分区，而三方插件包括pathman、partman等等。在官方声明式分区实现后，基本只推荐一种分区方式：声明式分区。由于再拓展不同实现的分区表的功能、细节、历史等差别会使篇幅过长，且未来意义不大，本篇主要讨论的是声明式分区，其他方式实现的分区功能只会简单介绍。但是由于历史存量和一些功能差异，了解声明分区、继承分区、pathman还是有必要的。
声明式分区
声明分区也叫原生分区，从PG10版本开始支持，相当于“官方支持”的分区表，也是最为推荐的分区方式。虽然与继承分区不一样，但是其内部也是用继承表实现的。声明分区只支持3种分区方式：range分区、list分区、hash分区
- 范围分区（Range Partitioning）：根据某个列的范围将数据分布到不同的分区中。适用于按照时间、数字范围等进行分区的场景。例如，大型日志表、股票交易表等。
- 列表分区（List Partitioning）：根据某个列的值将数据分布到不同的分区中。适用于按照离散值进行分区的场景。例如，按照地理位置、产品类别等进行分区的表。
- 哈希分区（Hash Partitioning）：根据哈希算法将数据均匀地分布到不同的分区中。适用于需要均匀地将数据分布到多个分区中的场景。例如，将数据分布到多个物理机器上，以提高查询性能和可伸缩性。
基于SpringBoot、Mybatis-Plus代码实现
在Spring Boot项目中，结合MyBatis-Plus和PostgreSQL，可以通过声明式分区实现更灵活和高效的数据管理。以下是关于Spring Boot、MyBatis-Plus和PostgreSQL声明式分区的详细介绍：
使用MyBatis-Plus的声明式分区
添加依赖
在项目的Maven或Gradle配置中添加MyBatis-Plus和PostgreSQL的相关依赖。
<!-- Maven 示例 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.x.x</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>版本号</version>
</dependency>
<!-- Sharding-Jdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.21</version>
</dependency>
配置数据源
在application.properties或application.yml中配置PostgreSQL数据源。
spring:
shardingsphere:
datasource:
names: db0
db0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://your-database-url
username: your-username
password: your-password
sharding:
tables:
your-table-name:
actual-data-nodes: iot0.your-table-name_${0..1}
table-strategy:
standard:
sharding-column: partition-column
precise-algorithm-class-name: your.package.xxxAlgorithm
实体类注解
在需要分区的实体类上添加相关注解
import com.baomidou.mybatisplus.annotation.TableName;
@TableName("my_partitioned_table")
public class MyEntity {
// Entity fields and methods
}
自定义分区策略实现类
创建一个实现PreciseShardingAlgorithm接口的自定义分区策略类。
@Slf4j
public class BaseTestAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
// 获取分片键的值
Integer value = shardingValue.getValue();
// 获取逻辑
String logicTableName = shardingValue.getLogicTableName();
log.info("分片键的值:{},逻辑表:{}", shardingValue, logicTableName);
// 根据分表键的值计算分表名称的逻辑，返回实际数据表的名称
String shardingRule = value % 2 + "";
for (String each : availableTargetNames) {
if (each.endsWith(shardingRule)) {
return each;
}
}
throw new IllegalArgumentException();
}
}
实例分区策略
PostgreSQL的分区策略实例化配置。
@Configuration
public class ShardingConfig {
@Bean
public BaseTestAlgorithm baseTestAlgorithm() {
return new BaseTestAlgorithm();
}
}
自动创建表
创建好分表需要的表{0…1},代表有your_table_name_0,your_table_name_1两张表。也可以根据业务做自动创建表。
@Slf4j
public class ShardingAlgorithmTool {
private static final HashSet<String> tableNameCache = new HashSet<>();
/**
* 判断 分表获取的表名是否存在 不存在则自动建表
*
* @param logicTableName 逻辑表名(表头)
* @param resultTableName 真实表名
* @return 确认存在于数据库中的真实表名
*/
public static String shardingTablesCheckAndCreatAndReturn(String logicTableName, String resultTableName) {
synchronized (logicTableName.intern()) {
// 缓存中有此表 返回
if (tableNameCache.contains(resultTableName)) {
return resultTableName;
}
// 缓存中无此表 建表 并添加缓存
List<String> sqlList = selectTableCreateSql(logicTableName);
for (int i = 0; i < sqlList.size(); i++) {
sqlList.set(i, sqlList.get(i).replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS").replace(logicTableName, resultTableName));
}
executeSql(sqlList);
tableNameCache.add(resultTableName);
}
return resultTableName;
}
/**
* 缓存重载方法
*/
public static void tableNameCacheReload() {
// 读取数据库中所有表名
List<String> tableNameList = getAllTableNameBySchema();
// 删除旧的缓存(如果存在)
ShardingAlgorithmTool.tableNameCache.clear();
// 写入新的缓存
ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
}
private static void executeSql(List<String> sqlList) {
Environment env = SpringUtil.getApplicationContext().getEnvironment();
try (Connection conn = DriverManager.getConnection(Objects.requireNonNull(env.getProperty("spring.datasource.hikari.jdbc-url")), env.getProperty("spring.datasource.hikari.username"), env.getProperty("spring.datasource.hikari.password"))) {
try (Statement st = conn.createStatement()) {
conn.setAutoCommit(false);
for (String sql : sqlList) {
st.execute(sql);
}
conn.commit();
} catch (Exception ex) {
conn.rollback();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
private static List<String> selectTableCreateSql(String tableName) {
List<String> res = new ArrayList<>();
if (tableName.equals("hss_history")) {
res.add("CREATE TABLE `hss_history` (\n" +
" `id` bigint unsigned NOT NULL,\n" +
" `type_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '设备类型id',\n" +
" `equipment_id` bigint unsigned NOT NULL COMMENT '设备id',\n" +
" `data` json DEFAULT NULL COMMENT '原始数据',\n" +
" `parse_data` json DEFAULT NULL COMMENT '解析数据',\n" +
" `parse_time` bigint NOT NULL DEFAULT '0' COMMENT '解析时间',\n" +
" `create_time` bigint NOT NULL DEFAULT '0',\n" +
" PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='设备历史数据'");
res.add("ALTER TABLE `hss_history` ADD INDEX n1 ( `create_time`, `equipment_id` ) USING BTREE");
res.add("ALTER TABLE `hss_history` ADD INDEX n2 ( `equipment_id` ) USING BTREE");
res.add("ALTER TABLE `hss_history` ADD INDEX n3 ( `parse_time` ) USING BTREE");
}
return res;
}
public static List<String> getAllTableNameBySchema() {
List<String> res = new ArrayList<>();
Environment env = SpringUtil.getApplicationContext().getEnvironment();
try (Connection connection = DriverManager.getConnection(env.getProperty("spring.datasource.hikari.jdbc-url"), env.getProperty("spring.datasource.hikari.username"), env.getProperty("spring.datasource.hikari.password"));
Statement st = connection.createStatement()) {
try (ResultSet rs = st.executeQuery("show TABLES like 'hss_history%'")) {
while (rs.next()) {
res.add(rs.getString(1));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
public static HashSet<String> cacheTableNames() {
return tableNameCache;
}
}
注意事项
- 分区列必须是分区表的一部分，且必须是可比较类型（例如，整数、日期等）。
- 分区列必须包含非空值，否则会导致分区操作失败。
- 分区表的主键必须包含分区列，以便在执行查询时可以正确路由到相应的分区。
- 分区表的索引必须也包含分区列，以便在执行查询时可以正确路由到相应的分区。
- 分区表的触发器必须确保数据插入到正确的分区中，以避免数据插入到错误的分区中。
扫一扫
751
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
