MySQL分区分表实现方法示例详解

目录

• 分库分表依赖
• 核心概念与配置示例
• 1. 水平分表 (Horizontal Table Sharding)
• 2. 水平分库 (Horizontal Database Sharding)
• 3. 分区（自定义复合分片策略）
• 重要注意事项
• 总结

ShardingSphere-JDBC 通过在应用层进行数据分片，可以帮你轻松实现分区、分表和分库，下面我用具体的配置和代码示例来说明如何使用。

特别注意：以下示例基于 ShardingSphere-JDBC 5.x 版本（Spring Boot Starter）配置。实际使用时，请确保你的依赖版本匹配。以下示例主要展示核心配置和逻辑，实际应用请参考官方文档并根据业务调整。

为了让你对这几种分片方式有个快速的了解，我先用一个表格来汇总它们的主要特点和区别：

特性	分表	分库	分区（按特定规则如时间）
数据分布	同一库中多表	不同库中表	同一库或多库中按规则分表
性能影响	减轻单表压力	减轻单库压力（可配合不同服务器）	常用于按时间归档，优化查询和管理
配置要点	指定分表算法	指定分库算法	通常需要自定义复合分片算法
适用场景	单库数据量大	数据量大且并发高，需分散IO	数据有明显冷热特征，需定期归档

分库分表依赖

首先，确保你的 pom.XML 包含以下依赖（以 Spring Boot Starter 为例）：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version> <!-- 请使用最新稳定版本 -->
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-Java</artifactId>
</dependency>

核心概念与配置示例

1. 水平分表 (Horizontal Table Sharding)

概念：将一个逻辑表的数据，按照某种规guJWyGlvt则拆分到同一个数据库中的多个物理表中。

YAML 配置示例：

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order: # 逻辑表名
            actual-data-nodes: ds0.t_order_$->{0..1} # 实际数据节点，ds0库下t_order_0, t_order_1两张表
            table-strategy:
              standard:
                sharding-column: order_id # 分片字段
                sharding-algorithm-name: table-inline # 分表算法名称
        sharding-algorithms:
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2} # 分片算法表达式，按order_id取模分到两个表
    props:
      sql-show: true # 打印SQL，方便调试

代码使用：

配置好后，在代码中操作逻辑表 t_order 即可，ShardingSphere-JDBC 会自动路由到具体的物理表。

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一条order_id为123的订单，根据 123 % 2 = 1，会路由到 t_order_1 表
    String sql = "INSERT INTO t_order (order_id, user_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.update(sql, 123L, 1000L, 200.00);
    // 查询order_id为123的订单，同样会路由到 t_order_1 表
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE order_id = ?", 123L);
}

2. 水平分库 (Horizontal Database Sharding)

概念：将一个逻辑表的数据，按照某种规则拆分到多个不同的数据库中（每个数据库可以包含该逻辑表的一个或多个物理表）。

YAML 配置示例：

js

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1 # 定义两个数据源
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order:
            # 实际数据节点，两个库，每个库一张表（也可每个库多张表）
            actual-data-nodes: ds$->{0..1}.t_order
            database-strategy: # 分库策略
              standard:
                sharding-column: user_id # 分库字段
                sharding-algorithm-name: database-inline # 分库算法名称
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2} # 按user_id取模分库
    props:
      sql-show: true

代码使用：

操作逻辑表 t_order，ShardingSphere-JDBC 根据 user_id 自动路由到对应的数据库。

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一条user_id为1001的订单，根据 1001 % 2 = 1，会路由到 ds1 库的 t_order 表
    String sql = "INSERT INTO t_order (order_id, user_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.update(sql, 456L, 1001L, 300.00);
    // 查询user_id为1001的订单，同样会路由到 ds1 库
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE user_id = ?", 1001L);
}

3. 分区（自定义复合分片策略）

概念：这里的"分区"可以理解为更复杂的分片策略，例如按时间范围（如月份）分表，可能同时结合分库编程客栈。这通常需要自定义分片算法。

场景：订单表按月度分表，如 t_order_202410, t_order_202411。

YAML 配置示例：

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        androidtype: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds0.t_order_$->{202410..202412} # 假设配置未来几个月的表
            table-strategy:
              standard:
                sharding-column: create_time # 分片字段为创建时间
                sharding-algorithm-name: table-time-month # 使用自定义的时间按月分表算法
        sharding-algorithms:
          table-time-month:
            type: CLASS_BASED # 使用自定义算法
            props:
              strategy: standard
              algorithmClassName: com.yourpackage.algorithm.TimeMonthShardingAlgorithm # 自定义算法类
    props:
      sql-show: true

自定义分片算法实现：

你需要实现 StandardShardingAlgorithm 接口。

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class TimeMonthShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {
    private final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public String DOSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
        // 处理精确分片，如 =, IN
        String logicTableName = shardingValue.getLogicTableName();
        LocalDateTime createTime = shardingValue.getValue();
        String actualTableSuffix = formatter.format(createTime); // 根据时间生成表后缀，如202410
        String actualTableName = logicTableName + "_" + actualTableSuffix; // 拼接实际表名，如t_order_202410
        // 检查计算出的表是否存在配置中
        for (String each : availableTargetNames) {
            if (each.equals(actualTableName)) {
                return actualTableName;
            }
        }
        throw new IllegalArgumentException("No actual table found for: " + actualTableName);
    }
    @Override
    public Properties getProps() {
        return new Properties();
    }
    @Override
    public void init(Properties properties) {
        // 初始化操作，如果需要
    }
    // 注意：ShardingSphere 5.x 及以上版本，可能需要实现其他方法，如 `getType`。请根据实际版本调整。
}

代码使用：

操作逻辑表 t_order，ShardingSphere-JDBCguJWyGlvt 根据 create_time 自动路由到对应月份的表。

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一条创建时间为当前的订单，会路由到对应月份的表，如 t_order_202410
    String sql = "INSERT INTO t_order (order_id, user_id, amount, create_time) VALUES (?, ?, ?, ?)";
    jdbcTemplate.update(sql, 789L, 1002L, 400.00, LocalDateTime.now());
    // 查询特定时间范围的订单，自定义算法中的doSharding方法会被调用
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE create_time BETWEEN ? AND ?", 
        LocalDateTime.of(2024, 10, 1, 0, 0), 
        LocalDateTime.of(2024, 10, 31, 23, 59));
}

重要注意事项

• 确保物理表和库存在：ShardingSphere-JDBC 不会自动创建配置中涉及的物理表和数据库，你需要在数据库中手动创建好。
• 选择合适的分片键：分片键的选择至关重要，应尽量选择数据分布均匀、业务查询常用的字段。一旦确定，修改分片规则会非常困难。
• 避免跨库/表关联查询：复杂的关联查询（尤其是跨库的JOIN）在分片环境中性能很差，甚至不被支持。设计时应尽量减少此类操作，或考虑使用绑定表。
• 分布式主键：在分片环境中，数据库自增主键不再适用，建议使用 ShardingSphere 提供的分布式序列算法（如雪花算法 Snowflake）。
• SQL 限制：ShardingSphere-JDBC 对某些复杂 SQL（如子查询、函数的使用）支持有限，使用时需参考官方文档的支持列表。

总结

总的来说，ShardingSphere-JDBC 通过灵活的配置，让你能以对业务代码低侵入的方式实现分片。关键在于理解分片概念，并根据业务特点设计合理的分片策略。

希望这些示例能帮助你理解和使用 ShardingSphere-JDBC。如果你能分享更多具体的业务场景，比如数据量、增长速度和常见的查询模式，我可以给出更贴合的建议。

到此这篇关于MySQL分区分表实现方法详解的文章就介绍到这了,更多相关mysql分区分表内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！