分库分表不是万能药：先定位MySQL瓶颈再决定

做数据库设计的人几乎都被问过同一个问题我的数据量越来越大了到底要不要分库分表我在不同团队被这个问题问过不下几十次每回的答案都不是“要”或者“不要”这么简单。接触过MySQL的人都知道分库分表是应对数据库性能瓶颈的终极手段但它不是第一手段更不是免费的午餐。真正需要上分库分表的系统通常在连接数、单机资源、单表数据量这三个维度上已经走到了天花板。这篇文章就来把“为什么”和“什么情况”两件事讲透顺便说清那些网上很少人提的边界条件。1. 先搞清楚单库单表的瓶颈长什么样很多人直觉是“数据量大就会慢”这话只对了一半。更准确的描述是单库单表不会线性变慢而是会在某个临界点突然变慢。MySQL的系统瓶颈分散在连接数、CPU、磁盘IO、索引深度等多个环节你最先撞到哪个天花板系统就死在哪个环节。先定位瓶颈比急着做分库分表重要得多。1.1 MySQL的最大连接数不是你想的那样MySQL实例的默认max_connections一般是151。假设你的应用连接池每个节点配置20个连接有10个应用实例启动后光业务连接就是200已经超过默认值。连接数一旦耗尽新的请求全部阻塞在“获取连接”阶段现象是应用层大量超时但MySQL的CPU利用率可能还很低。这种故障极容易被误判成“数据库性能不行”实际上只是连接数配置不给力。我之前处理过一个案例单表才500万行某次流量高峰进来后数据库CPU不高、磁盘也没满但应用层全线超时。查SHOW PROCESSLIST发现140多个连接全是Sleep新连接排不上号。最后把连接池最大连接数从50降到10同时清理掉空闲连接问题当场缓解。这件事让我养成了一个习惯判断要不要分库分表之前先看连接数和空闲会话否则拆了库照样会被连接数打满。提示用SHOW GLOBAL STATUS LIKE Threads_connected;观察当前连接数配合SHOW PROCESSLIST;看有没有大量Sleep连接占着连接槽位。1.2 单表数据量为什么会拖慢查询MySQL的InnoDB存储引擎用B树组织索引。B树的高度直接决定一次查询要访问多少个数据页。每次访问叶子页在缓冲池不命中的情况下就是一次磁盘随机IO。粗略算一笔账InnoDB数据页默认16KB主键假设为bigint类型8字节加上索引里的页指针约6字节一个非叶子节点大约能存16 * 1024 / 14 ≈ 1165个索引项。三层B树的非叶子节点大约能管理1165 * 1165 ≈ 136万个叶子页每个叶子页如果存放约16行数据按每行1KB估算总记录数大约2200万。这就是网上流传“单表超过500万行要注意”这一说法的来源之一——超过2000万行后B树高度可能从3变成4每次查询多一次磁盘IO并发一上来延迟会肉眼可见地恶化。1.3 磁盘IO与CPU两类典型的资源瓶颈数据库读的真相是缓冲池命中了一切都好说没命中就要走随机读。普通SSD的随机IOPS大约在几千到几万不等一个不经济的查询如果扫描了几万个页瞬间打穿磁盘IO。写方向也有成本redo log刷盘、binlog同步、脏页刷盘全都消耗IO。CPU侧的瓶颈更常见filesort排序、哈希join、大范围聚合计算、过大事务的回滚。一个实例如果CPU长期超过85%你要做的第一件事绝对不是拆库拆表而是拉慢日志、EXPLAIN看执行计划把扫描行数上百万的SQL抓出来。索引建好了、扫描行数降到几百CPU自然就下来了。现象常见根因优先处理方案应用连接超时、CPU不高连接数耗尽调整连接池、清理空闲会话单表千万行、查询P99上涨索引深度上升、扫描行数大优化索引、冷热归档CPU长期大于85%全表扫描、大排序优化SQL与索引或上缓存磁盘IO打满随机读高、脏页多扩大缓冲池、控制刷脏、读写分离2. 分库分表真正的能力和它解决不了的雷区很多团队把分库分表当成数据库优化的“大招”一听到性能问题就想放大招。大招当然有用但你必须知道它真正的杀伤范围。2.1 分库解决的是“一台机器扛不住”当在线业务的QPS涨到单机极限根据硬件不同通常是几千到几万CPU、内存、磁盘IO就成了一台服务器的硬边界。分库的本质是横向扩展服务器把原来由一台MySQL实例承担的流量分摊给多台实例。最朴素的做法是按业务域拆开用户库、订单库、商品库各自独立部署。每个实例独享连接数、CPU、IO、内存还顺带获得了故障隔离——订单库挂了不会拖垮用户库。2.2 分表解决的是“一张表太大”单表过大带来的问题有三个层次。第一B树变深每次查询的IO变多。第二索引维护成本变大每插入一行要更新主键索引和所有二级索引表越大写放大越明显。第三表锁、行锁竞争变高还可能因为大事务长时间持锁。水平分表就是把一张大表按规则拆成多张物理表。比如订单表orders拆成orders_0 ~ orders_15单个分片的数据量变成原来的1/16。查询时根据分片键直接定位到某一两张表单表扫描量、索引维护成本、锁竞争都会显著下降。2.3 哪些问题分库分表解决不了这是我最想强调的一点。分库分表不是万能药下面这些问题拆了也一样难受慢SQL本身。一条没走索引的查询拆成16张表就是16张表全扫一遍。写热点。hash分片能均匀分布但如果所有订单都集中在某个大商家查询和写入还是会打到同一个分片。事务一致性。拆分后跨库事务需要分布式事务方案复杂度成倍上升。运维成本。监控、扩容、数据迁移、备份恢复每一项都比单库单表麻烦。业务改造。原本一张表上的join、外键、唯一约束拆了之后要重新设计。所以我的经验顺序是先优化SQL和索引再考虑缓存然后做冷热归档和读写分离最后才评估分库分表。顺序别搞反搞反了就是拿高复杂度换取本可以用低成本解决的问题。3. 什么情况下值得动手我的判断标准与量化依据这一节是全文最实用的部分。直接给你一套可以对照执行的标准。3.1 三个核心指标我自己做判断只盯三件事。一是数据量。单表行数超过2000万行并且能感受到索引深度带来的查询延迟上涨或者单库总体数据量接近磁盘可用容量的50%且一年内可能涨到80%以上。这里要说明2000万行不是绝对红线取决于行宽和查询模式但它是启动评估的信号。二是并发与QPS。活跃连接数长时间达到max_connections的70%以上CPU长期大于80%P99延迟持续恶化。注意观察的是“活跃连接”不是总连接数大量Sleep连接说明应用连接池配置有问题。三是写入吞吐。单实例TPS已经对redo log刷盘构成压力主从同步延迟持续拉大说明单库写入能力到顶了。3.2 从诊断到决策的四步流程第一步开慢日志。设置long_query_time1跑一周收集慢SQL用pt-query-digest之类工具汇总。第二步审执行计划。对top慢SQL跑EXPLAIN看type、key、rows。如果rows是几十万基本可以断定是索引或SQL写法问题。第三步判断瓶颈类型。对照第一部分的瓶颈表确定是连接、CPU、IO还是索引深度。第四步逐级处理。SQL和索引问题当场改缓存能兜住的热点查询上Redis历史数据按时间归档到冷库或备份表读写分离进一步分担读压力。走完这套流程如果指标依然摸到天花板再发起分库分表评估。多数系统到第三步就走完了根本不匹配“分库分表”这种重量级方案。3.3 不需要分库分表的信号最后给你几个“别拆”的明确信号单表才几百万行慢查询一大半是全表扫——先改SQL。QPS只有几百连接数、CPU、IO利用率都不高——做分库分表只会增加运维负担。业务增长不确定一年才涨几十GB磁盘完全够——不值得为假想压力买单。团队没有专职DBA或中间件运维经验——接手一套分库分表环境是持续的成本。判定维度放心继续用单库开始认真评估拆分单表行数小于500万大于2000万活跃连接小于50%持续大于70%CPU小于50%长期大于80%P99延迟稳定持续恶化数据年增长可预期半年翻倍4. 垂直拆分与水平拆分按业务形态选方案一旦确定要拆下一步不是选中间件而是选拆分策略。策略错了中间件再好也白搭。4.1 垂直拆分怎么拆垂直拆分分两种。一个是库级别按业务域拆。典型做法是用户、订单、商品、支付各自独立成库互相不跨库访问。这是成本最低、效果最直接的拆分也是绝大多数业务第一步该考虑的。另一种是表级别按字段拆。把text、blob这类大字段或者访问频率极低的补充信息拆到一张扩展表主表只留高频字段。比如商品表把“商品详情HTML”拆到product_detail查询列表时就不用拖出几KB的大字段IO和内存都省了。这个方案在单表数据量尚未特别大时非常有效很多团队却忽略了。4.2 水平拆分三种分片策略对比水平拆分是真正的“分库分表”重头戏。三种常见分片策略Hash取模sharding_key % N。数据分布均匀适合随机访问为主的场景。缺点是扩容难从4个分片扩到8个分片存量数据要全部重算重迁。Range范围按ID区间、按区域编码。分片可以提前规划扩容时新增分片不用动老数据范围查询也友好。缺点是可能热点不均比如最新订单全落在最后一个分片。时间分片按月、按天。适合流水、日志、操作记录这类强时序数据。配合冷热归档非常舒服缺点是最近一个月永远是热点。好在按月分片后各月天然独立可以把当月表放在高性能存储上历史表挪到低性能存储成本和性能都好控制。策略数据分布扩容难度典型场景Hash取模均匀重迁移用户表、订单表Range范围可能不均低数据有明显区间属性时间分片时序聚集低日志、流水、事件记录订单表这种高频访问的业务我建议用hash或“日期hash”复合日志流水表时间分片就够了。4.3 分片数怎么预估分片数不是拍脑袋拍出来的。先定两个量单分片的目标容量以及单实例的目标吞吐。举例订单表预计3年后到2亿行按单分片可稳定支撑3000万行计算最少需要7个分片取8个。QPS方面假设高峰期1万读写单实例实测稳定吞吐3000就需要4个实例。两个维度取max是8。稳妥起见再预留50%余量按12到16个分片去建。分片建少了后面扩容痛苦建多了日常查询会多出无谓的路由开销但总的来说宁多勿少。5. 拆分后真正考验人的细节中间件、全局主键与分布式事务拆分方案确定后工程细节才是分库分表真正耗时的部分。5.1 中间件选型业界主流方案现在基本集中在ShardingSphere和MyCat两个方向。ShardingSphere有JDBC客户端模式和Proxy代理模式两种部署形态。JDBC模式是应用直连数据库在应用内部做路由性能好、无额外网络跳但只适合Java技术栈统一的团队Proxy模式是独立部署一个代理服务对业务侧屏蔽路由细节客户端语言不受限代价是多一层的网络损耗和维护成本。MyCat很早就有了基于Proxy思路胜在历史沉淀但社区活力相比ShardingSphere要弱一些。选型的核心不是比框架而是看你的团队。如果全是Java且能接受对应用代码做少量改造JDBC模式收益最高如果有多语言团队或者不想动业务代码Proxy模式更稳妥。注意中间件版本和MySQL版本要匹配。尤其碰了MySQL 8.0之后连接协议、认证插件都变了旧版中间件容易踩认证协议的坑。5.2 全局主键方案分库分表之后自增主键失效因为多个分片各自生成的自增值必然冲突。全局主键要满足两个要求全局唯一、趋势递增。趋势递增不是必须但对写入性能和索引页利用率有帮助。我首推雪花算法的变体。雪花ID是64位其中1位符号位、41位时间戳、10位机器编号、12位序列号。单台机器每毫秒可以生成4096个ID时间戳能用很久完全满足绝大多数业务。网上有现成实现比如百度的uid-generator、美团的Leaf。用的时候注意机器编号要全局唯一还要处理时钟回拨大多数实现都带“等待时钟追上”的逻辑别自己造轮子。另一种思路是号段模式由一个发号服务统一从数据库取号段应用每次拿一批号段本地分发。这个方案对需要连续ID的业务更友好但要额外维护一套发号服务适合已经有微服务体系的团队。5.3 跨库查询、分页、事务的妥协拆分之后最影响日常开发的三件事是join、分页、事务。先说join。跨库join最好不要做代价极高。通常的做法是把要关联的字段冗余到查询侧的表里比如订单表冗余用户手机号、商品名称或者把数据异构到Elasticsearch、宽表里让查询走搜索引擎。刚开始会觉得冗余字段很丑但用过就知道它换来的查询简单和性能稳定非常值。再说分页。原来一条limit 100000, 20的翻页SQL在分库分表下要变成“各分片先取100020条中间件汇总内存排序后再截断”数据量一大屏幕直接卡死。破解思路是放弃深分页改用游标分页按上次查询的最后一条ID做条件或者落到应用侧定时物化成结果表。最后是事务。同一个分片内的事务中间件大多能保留原生事务能力跨分片事务就要上XA、TCC或者最终一致性。做交易类系统时我建议尽量把一次事务内的数据收敛到一个分片内比如订单及其明细都用同一个订单号取模从源头减少分布式事务次数。剩下少量跨分片场景用可靠消息加定时对账补偿别把系统做成每一步都强一致。关于取模还有一个细节订单号和用户ID往往不是同一个路由键。如果按用户ID取模查一个用户的订单很方便但商家端按商家查订单就会全片扫描如果按订单ID取模用户查自己的订单也要全片扫。业务上有两种破解法建映射表或者冗余一份“用户维度分片”的数据。这是业务建模里最容易被忽视的一环。6. 从定位瓶颈到迁移落地的完整链路到这里你已经判断出系统该拆了。真正落地时还有一套完整的迁移链路要走。6.1 上线前怎么再一次确认瓶颈我的建议是在下一批流量高峰来之前先做一次彻底的压测摸底。用sysbench对现有单实例跑一轮读写混合压测记录下当前硬件的极限QPS、TPS和延迟曲线。同时整理线上慢日志和SHOW ENGINE INNODB STATUS的锁等待数据把锁相关的问题单独列出来。这样拆库之后才有对比基线否则上线后效果是好是坏全靠感觉。6.2 迁移方案与双写切换迁移过程我会拆成五个阶段建分片环境。按分片规划创建新库表索引、字符集、参数尽量与原库对齐。历史数据分发。写一个分发程序读原表数据按分片键计算目标分片批量写入。数据量大时用并行任务按主键范围分批。增量同步双写。应用层改造为双写每次写入同时落到原表和新分片或者用订阅binlog的同步工具追平增量。binlog格式必须设为row级否则无法可靠解析。数据校验。两边行数一致还不够最好抽样对比关键字段的checksum比如用sum、CRC32对比主键、金额、状态字段。灰度切换。通过配置中心做开关先把5%流量切到新分片观察延迟、报错、数据一致性逐步放量到100%。我自己做迁移时一定会保留至少两个回滚点一个是双写阶段的开关随时能退回单写另一个是切到分片后保留原表只读7天出了大问题还能找回数据。6.3 一次真实搬迁的经验分享最后讲一个我接手过的真实例子。一个订单系统单表数据量到了2亿行QPS高峰期2000左右P99延迟从平时的80ms恶化到300msDBA已经把能加的索引都加了CPU还是经常顶到90%。第一反应是上分库分表但我在评估阶段发现慢日志里大量查询集中在“查最近30天订单”历史订单几乎没人读。最后的做法是分两步走第一步把6个月前的订单全部归档到只读历史库线上订单表瞬间降到3000万行P99回到100ms以内同时给热点查询加了一层Redis缓存。这一步只花了两周效果立竿见影。半年后订单量再次涨上来才按用户维度把订单表拆成16个分片。第二次拆分时踩了个坑最初分片键用用户ID直接取模商家端按店铺查订单的报表需求就一直全分片扫。后来在商家维度加了一张“店铺-订单分片映射表”查询先走映射表拿分片列表问题才解决。这件事让我意识到分片键的选择必须同时考虑所有核心查询维度不能只想着写入均匀。如果你也正在这个决策点上我的实用建议是把“分库分表”这四个字从必选清单里划掉换成“先优化再归档后缓存最后才拆”。真到了要拆的那一步也先拆垂直再拆水平分片数宁多勿少回滚开关一定要留。这套路我用了七八年几乎没有失手过。