大家好，我是数据库小学妹👋 我踩过的坑，你别再踩。

去年我接手一个订单库，主表 2 亿行。接手第一周就撞上三件事。查最近一个月的订单，要从 2 亿行里翻，慢。想加个联合索引，DDL 排了两个小时还没跑完。做一次全备，备份窗口一路撑到凌晨五点。

我当时的第一反应是上分库分表。方案都写了一半，被组里一个老同事拦下来。他只问了我一句，这两亿行里，过去一年的有多少。我去查了一下只有 8000 多万，也就是说，有一亿两千多万行，是没人查的。

## 先量数，别急着动刀

那句话点醒我了。我当时默认"表大"就得"分表"，却没搞清楚一件事，表大到底是存量还是流量。存量是历史数据堆出来的，流量是当前写入和查询的规模，这两个问题的解法压根不一样。分库分表解决的是流量，把一个库的写入和容量摊到多个库上。可如果这两亿行里大半是历史数据，那你压根没碰上流量的天花板，你碰上的是存量。所以动手前先量三个数。

| 要量的东西 | 怎么量 | 量出来说明什么 |
| --- | --- | --- |
| 热数据占比 | 统计近 N 个月查询命中的时间范围 | 占比低，说明大部分数据没人查 |
| 写入增长 | 每月新增行数与增速 | 增速平缓，容量问题就不急 |
| 合规保留期 | 问业务和法务，数据要留多久 | 决定能不能删，能删到哪一年 |

从那次之后，我动大表前先把这三个数量出来。少一个，我都不敢往下写方案。存量还是流量，基本量完就分清了。

## 第一步不是删，是分区

我把那张表改成了按时间做 RANGE 分区，一个月一个分区。改分区时撞上一个硬约束，我事先不知道。InnoDB 要求分区键必须出现在表的每一个唯一索引里，所以主键不能只有 `id`，得改成 `(id, created_at)`。

```sql
-- 分区键必须进每一个唯一键，所以主键是复合的
CREATE TABLE orders (
    id         BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    order_no   VARCHAR(32) NOT NULL,
    user_id    BIGINT NOT NULL,
    amount     DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (id, created_at),
    UNIQUE KEY uk_order_no (order_no, created_at)
)
PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p2025_01 VALUES LESS THAN ('2025-02-01'),
    PARTITION p2025_02 VALUES LESS THAN ('2025-03-01'),
    PARTITION p2025_03 VALUES LESS THAN ('2025-04-01')
);
```

这里有个语义变化，我一开始没留意。`uk_order_no` 加上 `created_at` 之后，它保证的就不再是订单号全局唯一了。同一个订单号，只要时间不同，就能插进去两条。这个唯一性得靠业务层或者其他机制补回来。约束反而是变松的，这点得心里有数。

分区做完，两处都松了。带时间范围的查询会做分区裁剪，只扫它需要的那个分区，不再全表翻。更关键的是归档，它变成了一个元数据操作。

```sql
-- 归档一个月，秒级完成，不逐行删
ALTER TABLE orders DROP PARTITION p2025_01;
```

这两条路的代价差得很远。走 `DELETE FROM orders WHERE created_at < '2025-02-01'`，上亿行要删，undo 和 binlog 能把你写爆，主从延迟直接起飞，删完表空间还不还你。`DROP PARTITION` 是把那个分区的文件直接丢掉，快得多，也不产生海量日志。

分区裁剪也有前提，查询里得带上分区键。我们有些接口是按 `user_id` 查的，没带时间。那种查询在分区表上会扫全部三十几个分区，比不分区还慢。这类接口我后来单独补了时间范围参数，没补的干脆没资格上分区表。

## 第二步，把数据搬到该去的地方

`DROP PARTITION` 是删。删之前，数据得先搬走。

```bash
# pt-archiver：分批归档，避免大事务和主从延迟
pt-archiver \
  --source h=主库,D=order_db,t=orders \
  --dest   h=归档库,D=order_archive,t=orders \
  --where "created_at < '2025-02-01'" \
  --limit 1000 \
  --commit-each \
  --sleep 0.5
```

这几个参数是保命的。`--limit 1000` 让每次只搬一千行，事务短。`--sleep 0.5` 在批次之间歇一下，给主从复制留出追赶时间。`--commit-each` 保证每批都提交，不留长事务。我第一版没加 `--sleep`，跑起来主从延迟直接飙到几分钟，归档任务把主库的写入压力顶上去，从库追不上。加了 sleep 之后慢是慢了点，但稳。

搬到哪，看这些数据还要不要被人查。归档表如果还在同一个库，那只是把热表清空，存量压力一点没少。我们最后分了三层。

| 数据 | 放哪 | 还要查吗 |
| --- | --- | --- |
| 热数据，近 3 个月 | 主库，SSD | 天天查 |
| 温数据，3 个月到 2 年 | 归档库，普通盘 | 偶尔查，走独立接口 |
| 冷数据，2 年以上 | 列存/分析库 | 只做报表，不做点查 |

冷的那部分我们扔进了分析库。交易库做点查快，做分析型查询本来就不擅长，让列存去干更合适。这块我单独写过一篇，讲为什么 MySQL 做报表这么慢。

## 一致性怎么保证

搬数据最怕丢和重，我们给归档任务加了两道闸。第一道是主键去重。归档的目标表主键和源表一样，搬重复了唯一键会拦住，不会写进去两条。第二道是对账，归档跑完不直接删源数据，先比一遍。

```sql
-- 归档区与源分区各算一次，对上了才允许 DROP
SELECT COUNT(*) AS cnt, SUM(amount) AS total
FROM orders PARTITION (p2025_01);

SELECT COUNT(*) AS cnt, SUM(amount) AS total
FROM order_archive.orders
WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-02-01';
```

行数和金额汇总都对得上，才执行 `DROP PARTITION`。这条是我从一次归档事故里学来的。那次少了几百行，原因是任务中途被重启，边界条件没写对，把当天的数据一起圈进去了。

## 归档完之后

三个月下来，主表从 2 亿行降到 8000 多万行。带时间范围的查询走分区裁剪，扫描行数下来了。DDL 终于能排进窗口，之前要两个多小时的加索引，现在四十多分钟。全备时长也回落了，备份窗口从凌晨五点缩回两点多。

但我要说句实话。归档不是万能的，它清的是存量。这张表每个月还在新增五六百万行，写 QPS 也一直在涨。哪天真顶到单机天花板，那还是得回到分库分表那条路上去。只是现在，还远没到。

## 避坑清单

`DROP PARTITION` 快，但它也是 DDL，一样要拿 MDL。执行前先看一眼有没有长事务在跑。我们有次在业务小高峰执行，一个报表长事务卡在那儿，DROP 一直等，后面的 INSERT 全堵在队列里。现在这条操作写进了我们的变更流程，只能走低峰窗口。

归档任务得能断点续跑。被重启、被 kill、断网，这些都躲不掉。我们的做法是每次记录归档到的最大主键和时间边界，重启后从边界往后接着搬，不从头再来。在亿级表上从头重跑，就是一场灾难。

还有一条。`DELETE` 删不掉表空间。如果因为某些原因你用不了分区，只能走 DELETE，那删完记得看一眼表空间还回来了没有。多半没有。你得重建表才能真把空间交还给操作系统，而重建本身又要一份等量的临时空间。所以规划磁盘的时候，别按"删完就省出来"来算。

## 写在最后

这期我想说的其实只有一句。**表大不等于要分库分表。先看清楚，大的是存量还是流量。**

存量大了，做个分区，把历史数据搬到该去的地方，多半就够了。要是流量真到顶，单机的写入和容量扛不住，那才轮到分库分表上场。这两件事的解法、成本、风险都不一样，混着看就会做错决策。

我写方案那次差点就错在这儿。拦我的老同事其实只问了一个问题，这两亿行里过去一年的有多少。问题问对了，方向自己就出来了。后来再有人拿方案来找我，我也先问这一句，量过没有。

你们手上最大的那张表，现在多少行？热数据占了多大比例？评论区聊聊。

我是数据库小学妹，帮你少走弯路少踩坑，咱们下篇见👋


去年我接手一个订单库，主表 2 亿行。接手第一周就撞上三件事。查最近一个月的订单，要从 2 亿行里翻，慢。想加个联合索引，DDL 排了两个小时还没跑完。做一次全备，备份窗口一路撑到凌晨五点。

单表2亿行的治理复盘：分区归档、冷热分离与三步对账流程

从接手 2 亿行订单表撞上查询慢、DDL 跑不完、备份窗口爆炸切入，先量热数据占比、写入增长、合规保留期三个数，再用时间分区 + 分区裁剪 + pt-archiver 分批归档 + 归档对账把存量清掉，并给出冷热分离三层架构与三个真实踩坑，最后说明归档是清存量、分库分表才是扩流量。

数据库

运维

后端

数据库表大不等于要分库分表，先分清存量还是流量。本文分享2亿行订单表优化实战：通过分区、归档、pt-archiver工具，将主表降至8000万行，DDL从2小时缩至40分钟。提供避坑清单，教你正确判断数据优化方向。

代码解释

MySQL

腾讯云数据库特惠，新用户首单0.5折起


腾讯云BI特惠9.9元起！


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

单表2亿行的治理复盘：分区归档、冷热分离与三步对账流程-腾讯云开发者社区-腾讯云

单表2亿行的治理复盘：分区归档、冷热分离与三步对账流程

单表2亿行的治理复盘：分区归档、冷热分离与三步对账流程

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐