深入解析：MySQL分区分表实现方法详解

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

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

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

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

分库分表依赖

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


    org.apache.shardingsphere
    shardingsphere-jdbc-core-spring-boot-starter
    5.2.1 


    com.zaxxer
    HikariCP


    mysql
    mysql-connector-java

核心概念与配置示例

1. 水平分表 (Horizontal Table Sharding)

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

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> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE order_id = ?", 123L);
}

2. 水平分库 (Horizontal Database Sharding)

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

YAML 配置示例：

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> 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:
        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_$->{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 {
    private final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue 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-JDBC 根据 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> 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));
}

重要注意事项

  1. 确保物理表和库存在：ShardingSphere-JDBC 不会自动创建配置中涉及的物理表和数据库，你需要在数据库中手动创建好。

  2. 选择合适的分片键：分片键的选择至关重要，应尽量选择数据分布均匀、业务查询常用的字段。一旦确定，修改分片规则会非常困难。

  3. 避免跨库/表关联查询：复杂的关联查询（尤其是跨库的JOIN）在分片环境中性能很差，甚至不被支持。设计时应尽量减少此类操作，或考虑使用绑定表。

  4. 分布式主键：在分片环境中，数据库自增主键不再适用，建议使用 ShardingSphere 提供的分布式序列算法（如雪花算法 Snowflake）。

  5. SQL 限制：ShardingSphere-JDBC 对某些复杂 SQL（如子查询、函数的使用）支持有限，使用时需参考官方文档的支持列表。

总结

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

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

posted @ 2025-11-09 01:29  ycfenxi  阅读(40)  评论(0)    收藏  举报