MySQL分区表与分库分表选型：大表治理与性能优化实战

深夜两点，报警群里的消息一条接一条：某订单系统的主库磁盘繁忙，慢查询把连接池挤满了。我点开监控一看，订单表已经三亿多行，单表超过100GB，索引的B+树涨到了六层以上，每次插入和查询都在跟磁盘IO较劲。主从同步延迟从几十毫秒涨到了十几秒，读写分离快撑不住了。

这种局面我见过不止一次。很多人第一反应是“上分库分表”，但我得先泼一盆冷水：你缺的未必是分库分表，可能只是一张MySQL分区表，或者一次尽量靠前的垂直拆分。分库分表、MySQL分区表经常被放在一起说，但它们是两条路子，解决的是不同层次的问题。这篇文章我想把这两套东西掰开揉碎，讲讲怎么选、怎么做、中间有哪些坑是文档里写不出来的。适合正在做大表治理、准备做系统扩容、或者单纯被慢查询折磨得睡不着的人。

1. 别急着动手：分库分表和MySQL分区表到底选哪个

1.1 触发我研究这个问题的真实场景

先还原一下我当时面对的情况。某订单系统的订单表是核心热表，写入量每天新增两百万行，单表总行数到了三亿多。写入慢，是因为每次插入都触发了索引维护，索引碎片多；查询慢，是因为范围查询（比如查最近一个月的订单）扫了不少不必要的页；更麻烦的是，归档任务在凌晨跑，一个大事务把主库拖住，从库延迟直接爬升到无法直视。

我最初也是翻开中间件文档准备上分库分表，但仔细一算，如果只是为了解决“单表数据量过大”和“历史数据清理困难”，分库分表是把问题复杂化了——因为你一旦分片，所有SQL都要重新过一遍路由规则，跨分片聚合、分布式事务这些麻烦就全来了。而MySQL分区表能让一张表在逻辑上不变，物理存储上拆成多个分片，很多场景下分区表就够了，根本不需要动到分库分表。

1.2 分区表到底是做什么的

MySQL分区表是把一张表的数据按某个键拆到不同的物理文件段上，但在应用看来还是一张表。你可以类比成一个大仓库拆成了几十个小隔间，每个隔间放固定时间段的货物。查询的时候，只要条件涉及分区键，MySQL会做“分区裁剪”，只去对应隔间里翻，而不是把整个仓库扫一遍。

有些说法把“分区表能显著提升查询性能”吹得很高，我的实测结论是：它的优势主要在三个方面——第一，范围查询减少扫描量，比如按时间分区的订单表，查最近一个月数据时只扫对应几个分区；第二，历史数据的清理变成秒级，删除一个分区比执行几十条DELETE快好几个数量级，而且几乎不产生大事务和binlog风暴；第三，单表文件不再无限膨胀，物理文件分散以后，备份和IO压力都更可控。

1.3 分库分表又是做什么的

分库分表是在物理层面把一张逻辑表拆成多张物理表，分布在不同的库甚至不同的机器上。它解决的问题是“单机资源瓶颈”和“单表写入并发上限”。注意，分库分表不是为了让某条查询更快，而是为了让整个系统的吞吐能力可以水平扩展。比如订单表拆成32张物理表分布在4台数据库实例上，写入压力就被这4台机器摊掉了。

分区表解决的是“单表数据量过大”的问题，分库分表解决的是“单机扛不住写入和存储”的问题。两者可以叠加使用，比如先分库分表，每张物理分表再用分区表按时间分区。这种叠加设计在大型系统里非常常见，但叠加的前提是每个环节都有必要，不是为了技术上的“显得专业”。

1.4 选型决策：一张表格看明白

我把这套判断标准整理过很多次，给个可以直接抄的对照表：

我的决策顺序是：先看是否需要做历史数据分层，能不能用归档 + 分区表解决；再看单机瓶颈是否真的到了物理极限，是不是上了分库分表才能继续。绝大多数数据量大的系统，先用分区表搞定历史数据问题，再考虑分片。直接上手分库分表，大多是把一台机器的压力搬到了四台上，但该有的坑一个没少。

2. MySQL分区表实操：建表到维护的一整套步骤

2.1 四种分区方式怎么选

MySQL分区表支持RANGE、LIST、HASH、KEY四种方式。我简单梳理一下各自的适用场景：

• RANGE分区：按连续区间拆分，最适合时间维度。比如订单表按月分区，post_time落在哪个月份就进哪个分区。这是实际使用率最高的一种。
• LIST分区：按离散值列表匹配，适合枚举字段，比如按省份、按地区编码，或者按订单状态分区。
• HASH分区：按分区键取模，把数据尽量打散到固定数量的分区。适合没有明确连续范围、只是要把数据均匀拆开的场景。
• KEY分区：和HASH类似，但支持更多数据类型，而且MySQL在内部做了哈希处理，字符串字段也能用。

一个很常见的误区是把HASH分区当成性能救星用在流水表上。实际上，订单、日志这类天然带时间属性的数据，RANGE分区是最合理的——既能按时间范围做分区裁剪，又能用DROP PARTITION做归档。HASH分区虽然均匀，但“按最近一周查询”这种高频查询会跨分区扫描，反而吃不到分区裁剪的红利。

2.2 建表实操：附一份能直接改的SQL

下面这份是按月份做RANGE分区的订单表示例，我实际项目里改几个字段就能用：

CREATE TABLE `order_info` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `order_no` VARCHAR(64) NOT NULL, `user_id` BIGINT NOT NULL, `seller_id` BIGINT NOT NULL DEFAULT 0, `amount` DECIMAL(10,2) NOT NULL, `status` TINYINT NOT NULL DEFAULT 0, `create_time` DATETIME NOT NULL, PRIMARY KEY (`id`, `create_time`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(`create_time`)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')), PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')), PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')), PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')) );

这里有个关键细节：分区键必须和主键一起构成联合主键，不然建表直接报错。MySQL要求每个唯一索引（包括主键）都必须包含分区键，因为InnoDB的聚簇索引一旦跨分区就无法保证唯一性约束。比如这里把create_time加进了主键，才能对create_time分区。

另一个细节是分区表达式的选择。用TO_DAYS(create_time)比用YEAR(create_time) * 100 + MONTH(create_time)更适合做精确的月份边界。TO_DAYS返回天数，用LESS THAN (TO_DAYS('2024-05-01'))天然划定的是“比5月1日小”的所有记录，符合月份边界逻辑。如果用YEAR和MONTH组合，等于每个分区存的是不连续的数值区间，边界控制容易把人绕晕。

2.3 分区键的约束条件，为什么这么苛刻

我曾经试过在分区表上单独建一个订单号的唯一索引，直接被MySQL拒绝。原因我已经说过了：InnoDB要求每个唯一索引包含分区键。分区键本身如果不在某条唯一索引里，那这个唯一索引只能在单个分区内部生效，跨分区无法保证唯一，MySQL为了避免这种逻辑漏洞，直接一刀切成全限制。

这个限制对业务建模的影响很大。比如订单表，order_no在业务上必须唯一，但你想按create_time分区，那唯一索引就得设计成(order_no, create_time)或者(create_time, order_no)。查询时如果只带order_no条件，你没法直接走这个唯一索引——因为缺少create_time，索引匹配规则会退化成普通二级索引，效果大打折扣。

我踩过这个坑之后的处理方式是：分区键优先选择“写入基本单调递增且查询经常携带”的字段，比如创建时间。然后在业务表上通过冗余或查询补丁来绕过约束。比如订单号已经带了日期前缀，查询时能把日期解析出来再补到条件里。如果你觉得麻烦，那说明这个场景可能不太适合分区表。

2.4 分区维护：清历史数据是真的香

分区表最大的爽点之一，是删历史数据像切豆腐一样利索。普通DELETE三亿行里的两亿行，跑一个大事务，锁一堆行，binlog膨胀到把磁盘写满；用分区表，直接：

ALTER TABLE `order_info` DROP PARTITION p202401;

这相当于直接把InnoDB的那个分区表空间文件删掉，不逐行删除，速度是秒级的，对线上基本无感。这个特性在金融、电商、日志类系统里非常实用，归档不再是DBA每天提心吊胆的大操作。

动态维护也很容易。要加下个月的分区，就执行：

ALTER TABLE `order_info` ADD PARTITION ( PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')) );

我在生产环境一般不会手动去敲这些DDL，而是写一个按月任务的自动化脚本来维护，提前一个月预创建好下一批分区。维护规则很简单：查询时自动裁剪到当前月份分区，走任务创建未来两个月的分区线段即可。

2.5 分区表的四个坑，提前说清楚

第一，分区数别太多。我见过有人把三年数据做成36个分区，每个分区才几百万行，执行计划却要在分区元数据上花额外开销。一般单个分区数据量在千万级到亿级比较健康，一年12个分区是常态。

第二，查询条件不带分区键，分区裁剪就失效。如果应用端某条高频SQL只传了个订单号没传时间，MySQL没办法知道该去哪个分区，只能所有分区都扫一遍。这种SQL在分区表下可能比普通表还慢——分区越多，扫描总量越大。

第三，NULL值会进最小分区。如果create_time允许为空，插入NULL时MySQL会把它放进第一个分区。如果第一个分区是最小时间边界，NULL和早期数据混在一起，后续查询可能会命中异常数据。

第四，沿用表达式后，范围条件可能出现不等价。比如对时间字段用了复杂的表达式，优化器无法精确判断区间对应哪个分区，裁剪失效。所以分区表达式尽量保持简单，直接用字段本身或TO_DAYS这类标准函数。

3. 分库分表实操：先垂直、再水平、最后套中间件

3.1 第一步永远先做垂直拆分

很多团队一上来就把热表水平拆成三十二片，结果发现业务SQL全都变了样，开发团队叫苦不迭。我自己的常规操作是先做垂直拆分，把一张超宽表拆成多张职责单一的表，能解决一大半问题。

举个例子，订单系统里订单表原来挂了三十多个字段，支付信息、退款信息、发票信息全塞一起。这么做的好处是写入一次完成，坏处是页的大小和索引的宽度都上去了，缓存命中率下降。垂直拆分就是分成三张表：订单主表只放核心流转字段，订单支付表放支付相关字段，订单扩展表放低频或大字段。按业务访问频率来分库，虽然还在同一个实例，但每张表的行宽度变小，单页能容纳的行数增加，磁盘IO明显减少。

垂直拆分基本是纯逻辑调整，不动路由规则，应用端最多改几次查询的关联方式。这一步做完还顶不住，才谈得上水平拆分。垂直拆分属于人人都该做的基础工作，拉到物理机器层面，它解决不了写入吞吐，但能让现有资源的老本吃得更久。

3.2 水平拆分的第一步：选对分片键

水平拆分的核心就是分片键。分片键选得好不好，决定了整个系统的命运，因为后续想换分片键，基本上等于重构。

分片键的核心原则是三个层面：

• 高频等值查询的字段。业务里百分之八十的订单查询都是按订单号或用户ID来的，那分片键就该从这两个里选。
• 具备足够的基数，避免热点聚集。如果选用户ID，高价值用户的订单量会让部分分片数据量飙升，所以要在“均匀分布”和“查询习惯”之间做取舍。
• 写路径均匀。如果订单号本身包含了用户ID的片段，那按订单号分片和按用户ID分片可以等价换算，写路径能均衡打散。

单根据查询习惯来定还不够，我用过一个很土但有效的方法：把线上读写日志拉出来，统计高频SQL的WHERE条件里哪个字段出现次数最多，并且是等值查询。以数据说话，而不是拍脑袋——这一条我强烈建议照着做。

3.3 路由计算的完整过程：取模和映射

水平拆分最朴素也最常用的路由方式是取模。假设订单表拆成32个物理分片，分布在4个库上，每个库8张表。分片键是订单ID，路由规则：

• 先算逻辑分片号：shard_id = order_id % 32
• 再算物理库：db_index = shard_id / 8
• 再算物理表：table_index = shard_id % 8

举例，order_id = 1234567：

shard_id = 1234567 % 32 = 7 db_index = 7 / 8 = 0 table_index = 7 % 8 = 7

所以这条数据的落点就是db_0.order_info_7。应用或中间件拿到这个结果，拼出完整表名去执行SQL。

取模路由的好处是简单、可靠、规则透明，缺点是扩容时几乎要全量重分布。比如分片数从32扩到64，原来order_id % 32 = 7的数据，扩容后% 64的结果可能落在7或39，这就意味着近半数据要搬家。后面我会讲一致性哈希怎么缓解这个问题，但如果你能接受停机扩容，取模反而最可控。

3.4 数据迁移的三种方案，按停业容忍度选

从单表切到分库分表，数据迁移方案直接决定改造成本。我实操接触过的有三种：

停机迁移：找一个低流量窗口（通常是凌晨），停写、导数据、校验、切流量。操作最稳，回滚最简单，适合核心交易类系统。唯一代价是要忍受一段维护窗口。我的经验是：无论你怎么优化，把窗口压缩到半小时内是不太现实的，要把索引的构建时间预留给导数阶段。

双写迁移：在灰度期间，新数据同时写旧表和新分片，老数据用批处理搬迁，最后校验差异。好处是无停机，缺点是双写逻辑侵入业务，需要处理幂等和补偿。实际操作中，得加一个补偿对账任务，定期排查两份数据不一致的记录。

同步工具迁移：订阅旧库的binlog，把增量变更实时同步到新的分片库。同步工具只是搬运工，一致性校验和切换动作还是要人把控。这个方法我比较喜欢：先全量初始化，再追增量，追平后可短时间停写完成切换，主动权一直在自己手里。

迁移方案没有银弹，我的建议是：不追求一步到位，把一个晚上拆成“全量初始化、增量追平、切流量、观察”四个阶段，每一阶段都有独立的终止和回滚标准。至少比一次性大动作稳定得多。

3.5 中间件选型：应用层还是代理层

分库分表中间件现在主流有两大形态：

• 应用层中间件（以JDBC形态接入）：应用直接集成，连接真实数据库，路由规则在客户端完成。优点是没有额外的网络跳点，性能损耗极小，适合对延迟敏感的核心链路。缺点是只对Java生态友好，接入时要改数据源配置。
• 代理层中间件（以独立服务形态部署）：应用连接到代理，代理解析SQL做路由。优点是语言无关，DBA可以直接运维，业务端几乎不用改代码。缺点是多跳一层，延迟有损耗，代理本身要做高可用。

我的个人倾向很明确：Java团队的内部系统，优先选择应用层中间件，集成简单，排障链路短。代理层更适合多语言混合的团队，统一收拢运维复杂度。关键不是中间件的品牌，而是你能不能在业务代码之外把路由规则、分片策略、主键生成这套东西管理清楚。

4. 分片后的连锁问题：全局主键、跨分片查询、事务和扩容

4.1 全局主键怎么生成

分库分表之后，数据库自增主键一定会冲突——每个实例的自增值都从1开始，两张分表可能出现相同的主键值。全局主键的生成方案有几个：

• 雪花算法：64位整型，由时间戳、机器ID、序列号组成。生成快、趋势递增、无中心化依赖，是目前最常用的方案。要注意时钟回拨问题，我见过几个因为云服务器时间跳变导致ID重复的案例，实际的实现里要加入回拨保护。
• 号段模式：相当于在某个集中点（比如一张独立的序列表）批量拿一段ID区间缓存到本地，用完再取。性能好且能保证单调递增，适合对ID趋势特性敏感的业务。
• UUID/随机数：拼接简单，但没有顺序性，作为InnoDB主键会造成聚簇索引的随机写，页分裂严重，写入性能下降。除非业务不在乎写性能，否则不推荐。

一个额外提醒：主键趋势递增很重要，但别把它当业务排序键。分片之后全局排序本来就做不到，老老实实按月或按状态分页，别迷信“全局自增ID = 全局有序”。

4.2 跨分片查询和Join怎么处理

分库分表之后，跨分片查询是最大的麻烦。

非分片键的条件查询，比如按卖家ID查订单，但因为分片键是订单ID或用户ID，中间件只能把请求广播到所有分片，再做内存聚合。这个流程在数据量大的时候非常昂贵，每一次广播都等于做了N次全表扫描。水准算下来，这种SQL不适合高频执行。

Join问题更严格：跨分片Join基本要杜绝。如果A表和B表都按用户ID分片，同一用户的数据必然落在相同的物理分片里，在单分片内部完成Join是可以的。但如果你要Join的两张表分片键不一致，那就不要想着SQL层面解决，用三条方案选一：

• 冗余字段：在从表里冗余主表的几个关键字段，省去Join。
• 应用层组装：先查主表分页，再按ID集合去查从表，最后在内存里拼接。
• 预聚合/宽表：建一张满足高频查询场景的宽表，用异步任务更新。

这三种方案我都用过，最推荐的是对高频且只读的场景直接做宽表，用异步同步保证数据最终一致。与其把时间耗在跨分片恢复数据上，不如提前把查询形态梳理清楚。

4.3 分布式事务，别把复杂度浪费在交易强一致上

分库之后，同一笔业务要更新多个库多张表，本地事务就搞不定了。分布式事务的解决方案从“刚”到“柔”排个序：

• 二阶段提交（XA）：保证强一致，但开销大，锁区间长，吞吐下降明显，不适合高频交易。
• TCC模式：业务自己实现Try、Confirm、Cancel，能控制隔离性，强一致效果也很好，但侵入性强，每一段业务都要写补偿逻辑。
• 本地消息表/事务消息：把一个事务拆成主事务和消息投递，消费方做承接，最后对账。属于最终一致方案。
• 自研补偿+重试：依赖幂等，把失败节点的数据记录到补偿表，定时批量重试。

我在订单和库存这类业务上的经验是：先想清楚业务到底能不能接受最终一致。绝大多数互联网业务，弱一致 + 对账补偿是性价比最高的选择。如果真碰上了强一致需求，先把事务边界收窄，能用单库解决的部分绝不拆出去。

4.4 扩容不重分片：一致性哈希怎么落地

传统取模在扩容时面临大规模数据迁移，一致性哈希是缓解这个问题的经典手段。原理是把哈希值空间看成一个环，分片节点分布在环上，数据按哈希值落在某个节点的“管辖范围”内。

以订单分片为例，正常有32个分片节点，每个分片顺时针范围只负责落在自己区间里的数据。新增一个分片节点时，只有该节点逆时针方向相邻的那个旧分片上的部分数据需要搬家，其他分片完全不受影响。配合虚拟节点（每个物理节点映射几十个环上位置），还能缓解节点分布不均导致的热点问题。

但一致性哈希也有代价：路由规则从简单的%变成了“查哈希环”，复杂度上来了。如果你的扩容频率极低（一年都不扩一次），取模方案在停机运维窗口内全量重分布，反而比一致性哈希的长期复杂度更省心。扩容是低频事件，但分片路由规则一旦定了，所有服务都在用它，别为了理论上的优雅牺牲掉日常排障的简单感。

5. 真实排障记录与避坑清单

5.1 案例一：分区表查询没走分区裁剪

某运营后台的报表SQL，查最近三个月订单汇总，但WHERE里漏了create_time，只传了order_no = x AND seller_id = y。表面上看是查一条单子，实际上MySQL把所有分区都扫了一遍。上线以后数据库CPU直线飙升，我第一件事就是拿EXPLAIN看partitions列，发现显示的是全部24个分区。

这类问题很好修复：业务查询条件把时间参数补上，同时在前端交互里把时间控件做成必选。之后我定了一条铁律：所有走分区表的线上SQL，EXPLAIN计划的partitions列必须只包含一个或极少数分区，否则不允许上生产。这也是我写给研发团队SQL规范里的硬性条款。

5.2 案例二：分片键选择错误导致的数据倾斜

某团队做用户消息分库分表，按用户ID取模分片。上线三个月后，一张分表的数据量比其他分表高三倍。原因也不复杂：少数头部用户的消息量远大于普通用户，这些用户的消息全落到同一个分片。

数据倾斜是分片场景最常见的并发症。处理办法有几层：可以把大用户单独做冷热分离，头部用户走专属分片；也可以在做分片键时考察数据分布的均匀度，而不是只看基数。我的实践法是：选分片键之前，先跑一遍线上数据的字段分布直方图，模拟分片后的数据分布，看模拟结果是否均匀。这个步骤成本很低，但能救你半年后的大命。

5.3 案例三：双写迁移断点恢复绕晕人

某次双写迁移，灰度脚本跑了一半，网络抖动导致离线同步任务中断。重启续跑后发现，部分老数据已经通过binlog追平，部分新数据双写了两次，幂等逻辑没写好，出现重复记录。当时排查真是头疼，只能连夜用“业务唯一键+全表对账”手工修复。

这之后我明白了一个道理：迁移工具可以糙，但幂等和断点续跑必须第一天就做扎实。每次迁移任务都带一个批次状态表，入账、Kafka投递、清理数据都先查状态。宁可多查一次，也不许重复写。离线追平任务配合增量Lag判断，追平到个位数记录级别才允许切换。

5.4 避坑清单一键速查

如果你正准备在一套业务系统里做数据量演进，我强烈建议从分区表开始，它简单直接，大多数大表问题到这一步就能缓解。真正走到分库分表的时刻，一定是单机物理资源的硬约束已经顶到了，或是写入通道确实无法再加宽——而不是为了在简历上添一行技能。

最后一件事，所有方案上线前先把回滚方案写清楚：分区表的回滚是重建一张普通表导数据，分库分表的回滚是切换回单库。别让“上线没问题”成为唯一的预期，多准备一套退路，才算真正对线上数据负责。