MySQL 高级分表与分库实践指南

下面我将为你提供四个方面的 完整、可落地、实战向的解决方案，涵盖：

  1. ✅ 按天/按月 RANGE 分区的完整例子
  2. ✅ 使用 ShardingSphere 实现分库分表的入门案例
  3. ✅ 如何从分区表迁移到分库分表
  4. ✅ 云数据库（如 TDSQL、Aurora）的分区/分片功能介绍

一、按天/按月 RANGE 分区的完整例子

场景说明

适用于日志表、用户行为表、订单表等具有明显时间属性的数据，按天/月分区，便于快速查询、高效删除历史数据。

示例 1：按 月 分区的订单表（RANGE）

CREATE TABLE orders_by_month (
id BIGINT NOT NULL AUTO_INCREMENT,
order_id VARCHAR(50) NOT NULL,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
order_time DATETIME NOT NULL,
PRIMARY KEY (id, order_time)  -- 分区字段必须包含在主键中
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(order_time))  -- 按天数范围，也可以用 YEAR(order_time)*100+MONTH(order_time)
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 2
(
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE  -- 未来数据
);

或者更清晰的按月表达（推荐）：

PARTITION BY RANGE (YEAR(order_time) * 100 + MONTH(order_time))  -- 如 202301 表示2023年1月
(
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
PARTITION p202304 VALUES LESS THAN (202305),
PARTITION p_future VALUES LESS THAN MAXVALUE
);

示例 2：按 天 分区的日志表（更细粒度）

CREATE TABLE system_logs (
id BIGINT NOT NULL AUTO_INCREMENT,
log_level VARCHAR(10),
message TEXT,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at))  -- 按天分区
(
PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-02')),
PARTITION p20240602 VALUES LESS THAN (TO_DAYS('2024-06-03')),
PARTITION p20240603 VALUES LESS THAN (TO_DAYS('2024-06-04')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);

说明：

  • TO_DAYS(datetime) 将日期转为整数，方便做 RANGE 分区
  • 每天一个分区，适合日志类数据，保留近期数据，定期删除旧分区
  • 也可以按周、按月聚合，根据数据量权衡

管理分区（增删查）

-- 查看分区信息
SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders_by_month';
-- 添加新分区（比如下一个月）
ALTER TABLE orders_by_month
ADD PARTITION (
PARTITION p202305 VALUES LESS THAN (202306)
);
-- 删除旧分区（比如删除2023年1月的数据）
ALTER TABLE orders_by_month
DROP PARTITION p202301;

✅ 优势：删除整个分区比 DELETE 快得多，且不产生大量 undo 日志！

二、使用 ShardingSphere 实现分库分表（入门案例）

场景说明

当你面临 单表数据量极大（千万级以上）、高并发、需要真正水平扩展 时，推荐使用 ShardingSphere-JDBC（轻量级，对应用友好）或 ShardingSphere-Proxy（中间件模式）。

这里以 ShardingSphere-JDBC（Spring Boot + MySQL） 为例，实现 用户表按 user_id 取模分库分表（2库2表）。

1. 项目依赖（Maven）

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.3.2</version> <!-- 请使用最新稳定版 -->
</dependency>

2. 配置 application.yml

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/db0?useSSL=false&serverTimezone=UTC
username: root
password: 123456
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/db1?useSSL=false&serverTimezone=UTC
username: root
password: 123456
rules:
sharding:
tables:
users:
actual-data-nodes: ds$->{0..1}.users_$->{0..1}  # 2库 x 2表 = 4个实际表
table-strategy:
standard:
sharding-column: user_id  # 分片键
sharding-algorithm-name: user-inline
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: db-inline
sharding-algorithms:
db-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}  # user_id % 2 决定去哪个库
user-inline:
type: INLINE
props:
algorithm-expression: users_$->{user_id % 2}  # user_id % 2 决定去哪个表

3. Java 实体与 Mapper（和普通 JPA / MyBatis 一样）

// 实体类
@Data
public class User {
private Long id;
private Long userId;  // 分片键
private String name;
private String email;
}
// Mapper 接口（MyBatis 示例）
@Mapper
public interface UserMapper {
@Insert("INSERT INTO users (user_id, name, email) VALUES (#{userId}, #{name}, #{email})")
void insert(User user);
@Select("SELECT * FROM users WHERE user_id = #{userId}")
User findByUserId(@Param("userId") Long userId);
}

✅ 你无需关心数据在哪个库的哪张表，ShardingSphere 会根据 user_id 自动路由！

✅ 优势总结

特性说明
自动路由你只需操作逻辑表 users，ShardingSphere 自动路由到具体的库和表
线性扩展可轻松从 2库2表 → 4库4表 → 10库10表
⚙️ 配置驱动通过 YAML 配置，无需修改代码即可调整分片策略
️ 透明兼容支持大部分标准 SQL，兼容 MyBatis、JPA、JDBC

官方文档： https://shardingsphere.apache.org/document/current/cn/overview/

三、如何从分区表迁移到分库分表

适用场景

  • 你当前使用 MySQL 分区表，但发现数据量持续增长，单机性能/存储出现瓶颈
  • 你希望获得 真正的水平扩展能力、分布式部署、高可用
  • 你愿意引入 分库分表中间件（如 ShardingSphere）或云服务

✅ 迁移步骤概览

1. 评估当前分区表情况
  • 数据量、QPS、访问模式、热点数据
  • 分区数量、分区策略（RANGE/HASH/LIST）
  • 是否有跨分区查询、事务
2. 设计分库分表方案
  • 选择分片键（如 user_id, order_id）
  • 确定分库数量与分表数量（如 2库 × 2表 = 4个分片）
  • 选择技术方案：ShardingSphere / MyCat / 云服务（TDSQL/Aurora）
3. 搭建新的分库分表环境
  • 准备多个数据库实例（或使用云数据库）
  • 部署 ShardingSphere-JDBC / Proxy
  • 创建目标分表（如 users_0, users_1…分布在多个库）
4. 数据迁移
  • 使用 数据同步工具：如 Canal、DataX、阿里云DTS、自研脚本
  • 或停机迁移：停写 → 导出分区表数据 → 导入到分库分表目标表
  • 注意：需要处理主键冲突、自增ID、分布式ID
5. 验证与切换
  • 双写验证（可选）
  • 流量灰度切换
  • 监控性能与稳定性

提示： 迁移是复杂工程，建议在 低峰期进行，并做好 回滚预案。

四、云数据库（如 TDSQL、Aurora）的分区/分片功能介绍

1. 腾讯云 TDSQL（MySQL 兼容）

✅ 支持功能：
  • ✅ 原生分区表（RANGE/HASH/LIST）：和 MySQL 一样，支持按时间、ID 等分区
  • ✅ 读写分离：自动主从同步，读流量分发
  • ✅ 分布式实例（TDSQL-C 分布式版 / TBase）：真正的分库分表，透明分片
  • ✅ 自治运维：自动备份、故障切换、慢查询分析
适用场景：
  • 传统业务升级，希望使用云上 高可用、免运维、自带分区 的 MySQL
  • 数据量较大，希望使用 分布式数据库能力但不想自研分片逻辑

官网：https://cloud.tencent.com/product/tdsql

2. AWS Aurora（MySQL/PostgreSQL 兼容）

✅ 支持功能：
  • ✅ 原生分区表：支持 MySQL 原生分区策略
  • ✅ Aurora Serverless / 多主 / 读写分离
  • ✅ 全局数据库（跨区复制）
  • ❌ 不直接提供分库分表，但可通过 Aurora + 应用层分片 / 中间件 实现
适用场景：
  • 云原生架构，追求高可用、弹性伸缩
  • 希望使用分区表优化时间序列数据（如日志、IoT）

官网：https://aws.amazon.com/rds/aurora/

3. 阿里云 PolarDB-X / TDSQL-C 分布式版

  • ✅ 专为分库分表设计的分布式数据库
  • ✅ 透明分片、自动路由、分布式事务支持
  • ✅ 弹性扩缩容、强一致、兼容 MySQL 协议
  • ✅ 适合超高并发、海量数据场景

如果你真正需要 分布式、弹性、海量数据支持，推荐优先考虑这类 云原生分布式数据库，而不是自己维护分库分表逻辑。

✅ 总结 & 推荐方案速查表

你的需求推荐方案
单表数据量大，希望提升查询效率、定期删除✅ MySQL 原生 RANGE/HASH/LIST 分区表
希望按天/月自动管理数据✅ 按天/月 RANGE 分区（TO_DAYS/YEAR-MONTH）
数据量极大，需要真正水平扩展✅ 分库分表（ShardingSphere/JDBC 或 云服务）
不想改代码，希望透明分片✅ ShardingSphere-JDBC / 云数据库分布式版
云上部署，希望免运维+分区功能✅ 腾讯云 TDSQL / AWS Aurora（分区表）
超大规模、高并发、真正分布式✅ 云原生分布式数据库（如 PolarDB-X、TDSQL-C 分布式版）

如你希望我提供：

  • ShardingSphere 分库分表 完整项目模板
  • 数据从分区表迁移到分库分表的 详细脚本/流程
  • Aurora / TDSQL 分区表 实操案例
  • 如何设计分布式 ID、避免热点

欢迎继续提问，我可以为你提供 开箱即用的代码、配置和操作指南！