十亿数据这个体量绝大多数团队不是一口气碰上而是像我们一样某天看数据库容量报表才发现单表已经涨到三亿、五亿查询开始明显变慢然后才把ShardingJDBC请进来。当时我们推进的是一个模拟项目X的资金流水库存量数据来到十亿之后ShardingJDBC在面儿上能跑但线上时不时冒出一两条慢查询日志凌晨跑批还会卡十几分钟。这篇文章就是整理我们这段时间是怎么把ShardingJDBC从能跑调到润滑的分享一些踩坑和调优思路尤其适合那些正在做分库分表但没想清楚下一步怎么优化的团队。1. 十亿数据面前ShardingJDBC真正卡住的三个环节先说结论十亿数据量下ShardingJDBC的慢绝大多数不是数据库引擎本身慢而是下面三件事拖了后腿——路由计算、连接复用、归并排序。很多人一开始都盯着SQL消耗结果把系统里里外外扒了一遍才发现瓶颈全在前面这几层。1.1 路由计算一条SQL到底要打多少个分片ShardingJDBC和MyCat最大的区别在于它是客户端分库分表应用进程直接和多个数据库实例建连接。ShardingJDBC拿到一条SQL之后会先做解析、改写、路由最后生成真正要发往各个分片的执行SQL。分片键一旦被函数包裹、参与运算或者SQL里压根没带分片键那它就只能走全路由——把所有分片都扫一遍。我们刚开始有段时间业务查询很多是根据用户ID查最近订单这个没问题。但有部分运营查询是按商户查询今日流水分片键是user_id商户ID不是分片键结果每来一次这样的查询16个分片全被拉一遍。虽然每个分片的数据量在3000万左右单库查询也就一百多毫秒但16个分片并发查完再归并整体要一秒钟以上。这种查询在非高峰期偶尔用还好一旦白天运营高频点击慢查询日志瞬间被刷满。这里要提醒的是ShardingJDBC的路由日志非常重要。打开配置里的sql-show开关或者让DBA把连接日志拉出来看能看到每一条逻辑SQL实际拆成了多少条真实SQL。如果一条查询被拆出十几个分片那就是需要重点优化的信号。1.2 连接管理分片一多连接池先被卡住ShardingJDBC自己管理多数据源但它还是复用底层连接池。我们最初用的是默认配置连接池最大大小写得偏小单个分片也就10个连接。结果促销活动一上来并发请求合理地分散到16个分片后感觉每个分片压力不大但连接池却在疯狂等待获取连接很多请求直接超时。这个道理有点像商场结账收银台数量固定但购物车太多每个顾客都要排队。你在每个收银台分一个人看着不多可人流全涌进来之后队伍就开始堵在门口。连接池的等待时间会反映在Tomcat线程上线程池再被占满整个应用都会跟着卡顿。后来我们把HikariCP的maximumPoolSize调大同时把连接池的最小空闲连接也保持在一个合理水位。不能用默认值硬扛16个数据源每个数据源至少要根据平时TPS估算连接数。我们调整之后高峰期基本不再出现获取连接超时。1.3 归并排序与深度分页真正的无底洞这是十亿数据下最容易被忽视的坑。单表数据少时普通的order by limit 100走索引很快但在分片环境下order by需要每个分片先把符合条件的记录排序然后把top N结果返回到ShardingJDBC的内存中再整体排序。数据量一大这层归并排序就特别吃内存和CPU。我们的案例是用户端的分页查询一页只显示20条用户点了第100页之后MySQL这边在每个分片都limit 2000然后ShardingJDBC把总量几个分片乘以2000条记录全部放在内存里排序最后再取20条。点得越往后内存中堆积得越多偶尔还会碰见内存溢出的情况。这个问题的解法不是调大JVM内存而是尽量避免深度分页。后面会单独写我们怎么用游标和自定义分页参数来绕开这个坑。2. 分片键与分片策略前期定错的成本是后期最大的债聊调优之前必须把分片策略这块掰开揉碎。因为后面踩的很多坑都能回溯到分片键选得不够准和分片数规划得太随意上。如果你还没上线请多花一周时间来验证分片键如果已经上线了也请认真评估是否要做迁移。这比调一百个参数都值。2.1 怎么选分片键访问模式比数据均匀更重要当时给我们这个资金流水库选分片键时团队里第一反应是用流水号ID做hash数据肯定均匀。均匀性确实好但业务访问却并不总是带流水号来查。用户最常做的是查我名下最近的流水和查某笔订单关联的流水几乎都围绕user_id和order_id。如果按流水号分片一次查用户最近流水的请求就会落到所有分片系统需要等所有分片查完再做归并。数据均匀的收益全被路由压力抵消了。所以最终我们把分片键换成了user_id虽然不同用户的数据量不见得完全均等但绝大多数查询都能直接定位到某几个分片路由效率立刻高了一个量级。关于数据倾斜可以通过二次分片或者把热门大商户的数据单独放一个分片集来处理。倾斜不可怕可怕的是所有查询都在全分片路由。2.2 分片数量与容量规划用膨胀系数估算分片数量不是越大越好。很多团队以为分片越多未来扩容越方便结果把库分成了64片、128片。实际上分片越多连接数越多路由和归并成本也越高甚至可能因为某些单分片故障导致整体可用性下降。我们的做法是用一个膨胀系数来做估算。假设单表数据量涨到1亿单库能健壮支撑的合理数据量是2000万到3000万那么分片数大致等于 1亿 / 2500万 ≈ 4。考虑未来三年的增长再乘上一个系数最终定到8到16个分片。不要一上来就搞64个这样运维成本和硬件投入都会失控。具体到ShardingJDBC分片数量配置在分片算法里。如果你用inline表达式可以直接写ds_$-{0..15}共16个分片。如果未来需要扩容sharding算法改成自定义的哈希取模即可但对存量数据的迁移要提前准备好方案。我们当时选择16个分片有两个原因一方面是数据量算出来的另一方面是16个分片对应4台物理实例每台上4个库迁移和扩缩容都留有余量。2.3 分片算法选型hash、range还是时间分片这三种模式各有适用场景算法优点缺点适用场景hash取模数据相对均匀写入分散范围查询需路由全分片用户维度的点查和更新为主range分片方便范围扫描可按段归档热点容易集中在新范围按时间归档、流水明细追加时间分片天然贴合历史数据数据可冷热分离热数据集中在最近分片日志、订单、流水时间维度明显我们的资金流水表最终用的是以user_id hash为主、以create_time做二级路由的复合思路。ShardingJDBC支持多分片键但并不是所有查询都能同时命中所以一定要设置一个必须路由键的兜底规范。比如所有查询都必须带上user_id否则拒绝执行运营要按时间查就走专门的数据分析库不直接打到分片库上。这里有个关键细节分片键在SQL中的写法会影响路由。例如WHERE date_format(create_time, %Y%m%d) ?这种写法ShardingJDBC是没法识别出分片键的因为分片键被函数包住了。同理在字段上做隐式类型转换也会导致匹配失败。这正好引到后面的踩坑案例。3. 连接池、批量写入与事务边界让引擎本身热起来分片策略解决的是查哪的问题。再往下连接池和事务配置解决的是怎么让ShardingJDBC的底层执行更顺畅的问题。这部分改起来最琐碎但收益往往立竿见影。3.1 数据源参数调优HikariCP与多数据源的配合ShardingJDBC底层走的是标准DataSource接口我们用的连接池是HikariCP。多数据源模式下每个分片库都会创建一套连接池参数如果沿用单库时代的配置很容易出现每个分片都缺连接的假象。几个关键参数可以这样调maximumPoolSize单分片并发不高时建议设在CPU核心数的2倍1左右不要盲目设到50、100。每个连接都是资源分片多时总量会指数放大。minimumIdle设为maximumPoolSize的一半到三分之二避免突发流量时才去建连。connectionTimeout通常保持默认30000ms但如果应用侧超时时间更短需要同步调低否则连接池还在排队上层线程已经放弃了。maxLifetime建议小于数据库的wait_timeout比如设180000030分钟防止数据库端主动断开后连接池还在复用失效连接。改这些参数时最好做一次压测。我们当时在测试环境模拟了高峰期并发用Jmeter把用户查询、写入、批跑三种流量都打了一遍对比不同连接池配置下的P99耗时最后确定每分片maximumPoolSize2016个分片一共320个连接。这个数字不高但足够用了因为大部分操作都能精准路由到一两个分片。3.2 批量写入的正确姿势几步就提升十倍性能十亿数据不只是查出来的更是一条条写出来的。导入历史数据那段时间我们最痛苦每天要写几百万条流水到16个分片。刚开始用的逐条insert每秒只能写几千条。后来把连接地址加上rewriteBatchedStatementstrue同时把应用里的批量操作改成真正的批量SQL写入速度直接翻了十倍。这里有个很容易踩的坑MySQL驱动需要开启rewriteBatchedStatements否则addBatch只是伪装成批量实际还是逐条发SQL。而ShardingJDBC对批量SQL的处理比普通JDBC更复杂分片键必须在批量语句中保持一致否则一次批量操作会被拆散到多个分片。我们后来统一要求批量写入时按user_id分组同一用户的数据打包成一个batch既保证路由稳定又能充分利用驱动优化。批量写入还有一点和普通单库不同ShardingJDBC会把一条insert语句按照分片结果改写成多段SQL分别发往对应分片。如果我们自己计算好分片也可以选择直接通过分片数据源直连数据库做低层写入把ShardingJDBC只作为路由层。不过这种姿势对代码侵入很大后期维护成本高除非是跑批否则不推荐。3.3 跨分片事务能用最终一致性就别用强一致分布式事务是分库分表下最头疼的问题。ShardingJDBC集成了Seata、ShardingSphere事务管理器但跨分片强一致事务的性能损耗很大。我们刚上线时有个创建流水并更新余额的神操作天天跑出分布式事务异常后来发现业务本身并不需要严格实时一致改为先写流水再异步补偿余额问题立刻消失。如果你的业务确实需要强一致至少要理解ShardingJDBC中的分布式事务是通过XA协议实现在本地事务基础上多加了协调流程网络开销翻倍。能用本地事务绑定的数据尽量放在同一个分片内。比如某用户的所有订单和余额都按user_id分片那该用户的多条更新其实都落在同一个分片天然是单库事务根本不需要跨分片。这个设计看起来老套但在十亿数据场景下特别重要。4. 查询优化把全分片扫描打成得分片快扫分片键和连接层都顺了之后还可以在查询路径上做几层优化。核心思路就一句话让每条SQL尽可能多地命中分片键让落不到分片键的查询走旁路。4.1 强制分片键的SQL规范与限制我们在应用层做了一个SQL路由规范的拦截主要做了三件事。一是按用户ID查询时强制带上user_id_ ?否则打回重写二是按订单ID查询时同时在映射表里找到对应的user_id再拼进SQL三是不带任何可路由条件的操作禁止直接调用分片库统一走只读分析库。有人会担心多一次映射表查询不是更慢吗实际上这个开销远小于全分片路由。映射表在最开始设计时就作为分库分表的路由元数据存在Redis里查询一个user_id对应的分片号基本是亚毫秒级别比全库扫描快几个数量级。如果团队一开始没有维护映射表也可以使用ShardingJDBC的hint功能强制路由。比如在ThreadLocal里设置分片值然后执行SQLShardingJDBC会优先使用hint里的分片路由。比如HintManager hintManager HintManager.getInstance(); hintManager.addDatabaseShardingValue(order_flow, 42); hintManager.addTableShardingValue(order_flow, 42); // 执行查询 hintManager.close();这种方案适合分片键字段在SQL里被隐式隐藏的场景但别滥用hint里写死了分片后续一旦分片规则调整这里也要跟着改。4.2 Like查询和模糊匹配别让ShardingJDBC硬扛十亿数据的模糊查询是任何分库分表方案都绕不过的坎。ShardingJDBC不像搜索引擎那样维护倒排索引like %关键词%这种语句只能用全分片扫描。我们这边有个合作的运营系统经常按收款方名称模糊查流水刚开始直接查询分片库慢到几乎不可用。我们尝试过两种处理方式。一种是把查询条件加上前缀匹配比如like 张%配合分片键user_id可以把扫描范围缩到几个分片。另一种干脆把模糊搜索全部迁移到Elasticsearch里只保留用户ID、金额、时间等稳定字段然后回表到分片库捞原文。前者适合简单的名字匹配后者适合复杂全文搜索两者不冲突。这块还要注意一个细节ShardingJDBC在做结果归并时跨分片的聚合函数、去重和排序都是内存操作。类似select count(*) from order_flow where remark like %活动%这种查询每个分片都要遍历一遍再把计数汇总。表面上SQL挺小实际背后的IO和CPU非常可观。如果想做这种统计建议先离线把数据同步到分析型数据库再跑分析任务。4.3 分页、排序的工程处理游标代替limit前面提到深度分页的问题我们最终采用了两套方案。第一套是业务允许的情况下用上一页最后一条记录的ID代替偏移量。比如用户想看第100页客户端会传入上一页最后一条的主键IDSQL变成where id ? order by id limit 20这样每个分片只需要取20条归并压力瞬间降低。这种方案适合数据按时间或ID有序排列的场景比如流水列表、通知列表。第二套是做一些运营数据导出场景时不提供精确分页改用分批拉取。写一个批量任务按create_time区间把数据分成一小块一小块处理每块几千条处理完之后记录游标位置。整个过程不依赖ShardingJDBC的归并排序性能稳定也不会把内存撑爆。如果实在无法避免深度分页后台可以通过LIMIT的offset做粗略估算但在十亿数据量下还是建议产品上做交互改造比如加载更多按钮取代页码或者只允许查看前N页。这不是技术妥协而是在海量数据场景下合理的体验设计。5. 实战踩坑路由失效、广播表没同步、分页慢到30秒前面讲了很多理想方案但真实运营中一定会遇到几个看起来配置没问题但还是慢的案例。这里挑三个我们实际踩过、排查了挺久的坑完整还原一下排查链路。5.1 案例一字段类型不一致导致分片路由失败有次线上加了一个查询根据手机号查最近流水用了user_id作为分片键理论上没问题。结果一上线所有这类查询都走了全分片路由日志里明明打了where user_id 135xxxx分片数还是16。排查链路先在ShardingJDBC配置里打开路由日志发现逻辑SQL被拆成了16条真实SQL说明分片键没有被识别。然后检查表结构发现order_flow表里user_id列是varchar但应用里面传的是Long类型。ShardingJDBC对分片键做路由判断时会把SQL中的值和分片键表达式做比对类型不一致导致精确路由失效最终退化成全路由。把应用侧参数从Long改成String重新测试后路由日志立刻变成1条。这个坑很隐蔽因为普通查询传错类型时数据库也能正常处理无非是隐式转换但到了分片路由层就会被无情打回。建议所有分片键字段保持同一个Java类型和数据库类型最好直接定义成一个公共常量。5.2 案例二广播表数据不一致我们的系统里有一张商户信息表行数不多但有更新配置成广播表。最初测试环境一切正常到了生产环境却偶尔出现用户A在分片3查到商户名字是旧名称用户B在分片12查到新名称的情况。排查时惊觉直接原因不是ShardingJDBC的广播逻辑失效而是部分内部工具脚本为了省事跳过ShardingJDBC连接直连了某个分片库只更新了那一张表。广播表在ShardingJDBC中的正常更新会被同步到所有分片但绕过它之后数据自然就裂了。我们后来做了两层防护一是所有核心配置表的更新只能走同一个管理服务不允许研发直接连库改数据二是加了一个巡检任务定时对比各分片间广播表的数据指纹发现不一致立刻告警。如果你也在用广播表记得把绕过路由直连数据这个场景写进规范里否则数据不一致几乎必然会出现。5.3 案例三深度分页让归并层内存吃紧有一次凌晨跑批任务卡了30秒定位到是一条分页查询order by amount desc limit 100000, 20。这条SQL把所有分片都拉起来每个分片先取100080条再return到ShardingJDBC内存排序最后丢掉外面的100000条。当时把JVM堆从8G调到12G也只是缓解没有根治。而且这个任务本身是导出前10万条结果产品认知是导出不关心页数。于是我们把导出逻辑改成分批流式拉取用游标每次取2000条循环拉完。没有深度分页内存占用一下降到几百兆跑批时间也缩到几秒。经验总结一旦看到分页查询的offset特别大立刻想能不能改成游标或流式不要试图靠内存硬扛。5.4 排查链路慢SQL→执行计划→路由日志→底层连接上面这些坑排查时有一个通用链路可以复用先抓慢SQL看应用里是哪个接口发出来的。用ShardingJDBC的路由日志确认这条逻辑SQL被拆成了多少条物理SQL。如果拆出来的分片数大于预期去看分片键是否匹配、类型是否一致、有没有函数包裹。如果分片数正常但依然慢去底层数据库看执行计划确认是不是索引失效、锁等待甚至磁盘IO在高位。这套流程最好固化成自动化工具。我们在生产环境做了一套简单的慢SQL自动路由分析每次抓到慢SQL后自动回放路由日志并附上分片数和底层执行计划研发同学省去了手工对比的步骤。排查效率至少翻倍。6. 容量预警与巡检脚本让十亿数据持续润滑优化不是一锤子买卖十亿数据之后还在涨。我们需要建立一套持续的健康检查机制否则数据量继续翻倍前面这些调优点会逐渐失效。6.1 分片数据倾斜监控数据倾斜是个慢变量可能某天某个商户集中上传某个分片的数据量突然涨到其他分片的1.5倍。这种倾斜如果不处理单个分片会先达到瓶颈连带影响这个分片上的所有用户。我们每个小时会跑一次巡检收集各分片表的行数、容量、自增ID水位然后用一个简单的阈值来判断倾斜度。比如最大分片行数 / 平均分片行数 1.2就告警。倾斜后一般有两种处理暂时性的活动高峰可以通过限流或缓存顶过去持续性的倾斜则要考虑热点商户单独建表或者做迁移。不要等到磁盘报警才去看。6.2 只读扩展与读写分离十亿数据读多写少时可以考虑把只读流量迁到从库。ShardingJDBC支持读写分离配置但要注意的是主从延迟。写入时刚提交的事务读从库可能看不到。我们为这个专门做了一个短时的写后读一致性控制刚写入的数据如果需要立刻读取走强制主库路由等延迟窗口过去之后再正常走从库。这种配置看着简单实际需要业务接口配合。我们用一个注解标识必须主库读比如订单支付后立刻查支付结果。别的查询普通走从库性能会好很多。6.3 数据归档与冷热分离十亿数据里真正被频繁访问的通常只有最近三个月的数据再往前的历史数据偶尔会被业务方翻出来对账。所以我们在分片库之上又加了一层归档策略定期把超过半年的流水迁移到冷库冷库里可以是单库大表也可以是另一个分片数更少的集群。归档时的坑在于跨库迁移必须保证全局一致性否则对账对不上。我们用的是一个简单的双写标记方案迁移任务扫描数据把记录标记为迁移中写进冷库校验条数和金额最后再删除原分片数据。整个过程对业务无感但还是建议先小范围灰度跑几次。6.4 巡检脚本里我最后悔没早做的事我们上线两个月后才发现巡检脚本里最该监控的其实是慢路由数而不是慢SQL数。有些SQL本身不慢但被路由到了全分片导致整体资源消耗高。我们后来把命中分片数写进日志并按天统计平均值。一旦某天某个查询的平均命中分片数异常上升基本上就是在提醒分片键开始失效了——要么数据倾斜要么SQL写法被改动要么分片键字段类型被调整。这是早期发现问题最有效的一个信号。最后说点我自己的体会用了大半年ShardingJDBC处理十亿数据最大的感触是这个框架在中小数据量下很轻盈但数据量一旦上来能不能跑得润滑关键是看我们有没有把路由规范和运维体系建起来。分片键、分片数、连接池、SQL规范这些东西像轴承里的滚珠单个看着不起眼少了任何一颗都会卡壳。我们也曾想过要不要换更重的中间件但后来发现大多数慢并不是框架不行而是我们对业务查询模式的理解还不够彻底。把分片键真正切进业务访问路径里把路由日志当成日常观测指标把批处理和游标用熟练十亿数据并没有想象中那么可怕。如果你们也正在这个仓位上挣扎我建议先从路由日志开始看。把每一条慢SQL的分片命中情况拉出来你会很快知道该从哪儿下手。