mysql分表篇

背景：

 这个表已经达到百万级了。

1. 修改表结构（关键步骤）
MySQL 要求分区键必须是主键的一部分。你需要把 created_at 加入联合主键。
sql

编辑

 

1-- 1. 先去掉原来的自增主键（注意：这会暂时影响性能，建议在低峰期操作）
2ALTER TABLE sh_entertainment_order DROP PRIMARY KEY;
3
4-- 2. 添加联合主键 (id, created_at)
5-- 注意：id 仍然保持唯一性，但物理存储会按时间排序
6ALTER TABLE sh_entertainment_order ADD PRIMARY KEY (id, created_at);
2. 执行分区操作
假设你要按月进行分区（RANGE 分区最适合时间类型）：
sql

编辑

 

1ALTER TABLE sh_entertainment_order PARTITION BY RANGE (TO_DAYS(created_at)) (
2 -- 2024年1月及之前的数据
3 PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
4
5 -- 2024年2月
6 PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
7
8 -- ...以此类推...
9
10 -- 2025年1月
11 PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
12
13 -- 兜底分区：防止未来时间数据写入报错
14 PARTITION p_future VALUES LESS THAN MAXVALUE
15);
3. 日常维护（核心优势）
新增下个月分区（每月月底执行一次）：
你需要把 p_future 拆分，或者如果没设 p_future，直接 ADD：
sql

编辑

 

1ALTER TABLE sh_entertainment_order ADD PARTITION (
2 PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01'))
3);
(注：如果有 p_future，需要先 REORGANIZE p_future)
秒级删除历史数据（比如只保留半年）：
以前 DELETE FROM table WHERE created_at < '...' 可能需要几小时并产生大量碎片，现在只需要：
sql

编辑

 

1ALTER TABLE sh_entertainment_order DROP PARTITION p202301;
瞬间完成，释放磁盘空间。
⚠️ 重要注意事项
查询必须带时间：
以后所有的查询 SQL，必须带上 created_at 条件（例如 WHERE created_at > '2024-01-01'）。
带了时间：MySQL 只会去对应的分区找，速度飞快（Partition Pruning）。
不带时间：MySQL 会扫描所有分区（All Partitions），速度比不分表还慢！
关于唯一索引 order_number：
你原本的表有一个 UNIQUE KEY order_number。
在分区表中，所有的唯一索引都必须包含分区键。

核心前提：修改主键结构（必须做）
MySQL 的分区规则要求：分区键必须包含在主键或唯一索引中。
你目前的表结构主键是 id，分区键是 created_at。如果不改主键，执行分区命令会直接报错。
操作步骤
备份数据（生产环境操作前务必备份）。
删除旧主键：
sql

编辑

 

1ALTER TABLE sh_entertainment_order DROP PRIMARY KEY;
建立联合主键：将 created_at 加入主键。
sql

编辑

 

1ALTER TABLE sh_entertainment_order ADD PRIMARY KEY (id, created_at);
注意：这不会改变 id 的自增属性，但在物理存储上，数据会先按 id 排序，再按时间聚簇。这对你的业务查询（通常查最近订单）影响不大，甚至因为局部数据更紧凑而更快。
执行分区（按年/月拆分）
考虑到订单表的数据增长，建议按月或季度分区。这里以按月为例：
sql

编辑

 

1ALTER TABLE sh_entertainment_order PARTITION BY RANGE (TO_DAYS(created_at)) (
2 -- 2024年及之前的数据归拢到一个区（根据实际情况调整）
3 PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
4
5 -- 2025年1月
6 PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
7
8 -- 2025年2月
9 PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
10
11 -- ...以此类推，预建几个月的分区...
12
13 -- 【关键】兜底分区：防止未来时间数据写入报错
14 PARTITION p_future VALUES LESS THAN MAXVALUE
15);
提示：TO_DAYS() 函数将日期转换为天数进行整数比较，效率比直接用字符串比较更高。
日常维护策略（核心价值）
1. 秒级清理历史数据
以前删除一年前的数据：DELETE FROM table WHERE created_at < '2023-01-01'，可能需要跑几个小时，还会产生大量碎片和锁表。
现在只需要：
sql

编辑

 

1ALTER TABLE sh_entertainment_order DROP PARTITION p202301;
瞬间完成，物理文件直接删除，不产生碎片，不锁表。
2. 定期扩展分区
你需要写一个定时任务（如每月25号），把下个月的分区建好。
由于有 p_future 兜底，你可以使用 REORGANIZE 来拆分未来分区：
sql

编辑

 

1ALTER TABLE sh_entertainment_order REORGANIZE PARTITION p_future INTO (
2 PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01')),
3 PARTITION p_future VALUES LESS THAN MAXVALUE
4);
⚠️ 重要避坑指南（必读）
唯一索引的限制
你表中有 UNIQUE KEY order_number。在分区表中，所有唯一索引都必须包含分区键。
MySQL 会自动处理这个问题，但你需要知道：order_number 的唯一性校验依然有效，但索引结构变成了 (order_number, created_at)。
这意味着，如果你通过 order_number 查询单条记录，性能依然很好；但如果要更新 order_number，MySQL 可能会检查更多分区（虽然优化器通常会处理得很好）。
查询必须带“时间”
这是最重要的原则！
✅ SELECT * FROM sh_entertainment_order WHERE user_id = 100 AND created_at > '2024-10-01' -> 极快（只扫特定分区）。
❌ SELECT * FROM sh_entertainment_order WHERE user_id = 100 -> 极慢（如果没带时间，MySQL 可能会扫描所有分区，除非你的 user_id 是全局索引，但在分区表中全局唯一索引很难维护）。
建议：检查你的代码，确保列表查询接口都传了时间范围参数。
自增 ID 的全局唯一性
分区表依然使用同一个自增计数器，所以 id 依然是全局唯一的，不用担心 ID 冲突问题。