分库分表后SQL全崩这些坑我替你踩完了还记得我们第一次把2亿条订单数据拆成16个分片段上线的那天本来信心满满觉得性能肯定能起飞结果上线刚十分钟告警就炸了订单列表接口超时率冲到35%订单count统计接口最长要15秒返回运营翻列表到第30页直接把ShardingProxy节点干OOM整个订单链路卡了20多分钟最后紧急切回单库回滚代码全组加班排查了一整夜。很多人觉得分库分表是解决大数据量性能问题的银弹把表一拆就万事大吉根本没想过拆完之后原来写的SQL90%都会出问题从单库毫秒级查询到跨分片雪崩可能就是一次上线的距离。我前前后后主导过三次核心库的分库分表落地踩过的坑能写半本书今天把分库分表场景下的SQL改写、避坑、调优经验全部分享给你看完你再做分库分表绝对不会上线就崩。分库分表场景下SQL改写与性能调优实战一、为什么分库分表之后原来的SQL全变慢了很多人对分库分表的认知停留在“把大表拆成小表查询就变快”根本没意识到分库分表之后SQL的执行逻辑已经完全变了。单库场景下MySQL自己会做执行计划优化所有数据都在本地哪怕写的稍微差点最多也就是慢一点不会出大问题但分库分表之后所有SQL都要经过中间件ShardingSphere、MyCat这类做解析、路由、改写、结果归并四个步骤数据分散在不同物理节点上任何一步设计不好性能都会指数级下降。我给你算过一笔账同样一条SQL单库和分16个分片的执行逻辑差异有多大整理成了对比表表格SQL类型 单库执行逻辑 分库分表后执行逻辑 性能下降倍数等值查询带分片键 本地索引查找一次返回结果 直接路由到对应分片和单库逻辑一致 1倍无性能损失等值查询不带分片键 本地B树查找 全路由到所有分片每个分片执行完拉回结果归并 16倍等于分片数深分页LIMIT 10000,20 扫描10020条记录返回20条 每个分片扫描10020条拉16万条到内存排序归并 50~100倍跨分片ORDER BY排序 利用索引直接返回有序结果 每个分片返回有序结果中间件做内存多路归并排序 2~10倍跨分片COUNT/聚合 本地聚合后返回单个结果 每个分片做预聚合拉回中间件做二次聚合计算 5~20倍跨库JOIN关联 本地嵌套循环关联 拉取所有关联表数据到内存做嵌套循环匹配 10~100倍你看只要你的SQL带上分片键路由到单个分片性能和单库是一模一样的但只要不带分片键或者做跨分片的复杂操作性能会立刻暴跌这也是为什么很多人分库分表之后发现性能反而更差的核心原因——根本不是分库分表没用是写的SQL不符合分布式场景的规则。二、分库分表最容易踩的SQL坑每个都能搞崩线上我们当时上线后梳理了所有慢SQL发现99%的问题都是几个固定的坑每个坑之前单库的时候根本不是问题到了分布式场景下直接成了P0故障的导火索。1、不带分片键的全路由查询性能直接差N倍分片键是你拆表用的那个维度比如订单表用user_id做分片键同一个用户的所有订单都会落在同一个分片上。如果SQL带上了user_id中间件直接能定位到具体分片一次查询就能返回结果但如果SQL不带分片键比如按order_no查订单、按create_time范围查订单中间件根本不知道数据在哪个分片只能把SQL发给所有16个分片每个分片都执行一遍再把结果拉回来归并性能直接差16倍QPS高了能把所有分片库和中间件的CPU全打满。我们上线第一天的慢查询TOP10全是这类SQL比如有个查订单详情的接口开发只传了order_no没传user_id每次查都要扫16个分片原来单库10毫秒的查询变成了180毫秒高峰期直接把中间件的连接池占满了。后来我们做了两个优化一是给order_no做了全局索引映射建了一张小表存order_no对应的user_id和分片位置查order_no的时候先查映射表拿到分片键再路由到对应分片性能直接回到8毫秒二是在SQL审核阶段加了硬卡口核心业务表的查询SQL必须带分片键不带的直接卡CI不让上线。sql-- 反例不带分片键user_id全路由扫16个分片执行时间180毫秒SELECT * FROM order_info WHERE order_no DD20250601123456;-- 优化后拿到分片键直接路由到单分片执行时间8毫秒SELECT * FROM order_info WHERE user_id 12345 AND order_no DD20250601123456;2、深分页查询直接干爆中间件内存深分页在单库就慢到了分库分表场景直接是灾难级别的。比如你写LIMIT 10000,20单库场景只要扫10020条记录扔掉前10000条返回20条但分16个分片的话中间件会给每个分片发LIMIT 0,10020的SQL把每个分片的前10020条数据全拉到中间件内存再做归并排序取第10001到10020条总共要拉16*1002016万条数据到内存如果翻到第100页就要拉160万条数据中间件直接OOM。当时上线第一天运营翻订单列表到第50页直接打挂了两个ShardingProxy节点就是因为这个问题两个节点堆内存占满Full GC都回收不了最后只能重启。后来我们把所有C端的分页全改成了游标分页书签式分页永远带上一页最后一条记录的id和create_time作为查询条件每次只查下一页的20条每个分片也只需要返回20条结果不管翻多少页性能都是毫秒级再也不会出现拉大量数据到内存的问题。sql-- 反例深分页全分片拉取数据内存归并执行时间12秒容易OOMSELECT * FROM order_info WHERE create_time 2025-01-01 ORDER BY id DESC LIMIT 10000, 20;-- 优化后游标分页带分片键单分片范围查询执行时间15毫秒SELECT * FROM order_infoWHERE user_id ? AND create_time 2025-01-01 AND id 上一页最后一条IDORDER BY id DESC LIMIT 20;对于后台管理系统必须跳转到任意页的场景我们用了二次查询法第一步只查所有分片的主键ID内存排序找到当前页的ID范围第二步拿着ID去对应分片查详情不需要拉所有字段数据传输量减少90%以上哪怕翻到几百页也不会慢。3、跨分片排序聚合性能差到离谱很多人写SQL习惯用COUNT(*)、GROUP BY、ORDER BY这些操作在单库很正常到了跨分片场景就会特别慢。比如你要查某个时间段所有订单的总金额中间件要让每个分片先统计自己分片内的总金额再把16个分片的结果拉回来加起来要是带复杂WHERE条件每个分片都要扫几万条数据几秒都出不了结果。如果是GROUP BY中间件还要把每个分片的分组结果拉回来再按分组维度做二次聚合数据量稍大内存就扛不住。我们当时订单列表的总条数COUNT接口每次要3秒多才能返回后来干脆做了一张独立的统计宽表通过Binlog异步同步订单数据按天、按状态、按商家维度提前把COUNT算好查的时候直接查宽表只要10毫秒就能返回。对于大跨度的GROUP BY、多维度统计需求我们全移到了离线数仓做不让线上库跑实时大聚合。这里还要提醒一个坑跨分片排序的时候一定要保证每个分片的排序字段上有索引让每个分片返回的结果本身就是有序的这样中间件做归并排序的时候效率特别高如果分片内排序没走索引每个分片都要做文件排序拉回来的结果是乱序的归并的开销会大到无法想象。4、跨分片JOIN基本等于“自杀式查询”分库分表之后JOIN是当之无愧的性能杀手。如果两个关联表的数据在同一个分片那还能做本地JOIN性能和单库一样但如果数据不在同一个分片中间件只能把两个表的数据全拉到内存里自己做嵌套循环关联要是两个表各有几十万行内存里要匹配几亿次不仅慢还会直接把中间件搞挂。我之前见过一个开发在分库分表之后还写5表关联的SQL执行一次要22秒把整个分片集群的CPU都打到了100%影响了所有核心业务。后来我们定了死规矩分库分表后禁止跨分片JOIN所有关联需求用三个方案解决第一个是字段冗余用空间换时间比如订单表要关联查用户昵称、手机号直接把这两个字段冗余存在订单表里更新用户信息的时候异步更新订单的冗余字段即可查订单的时候不需要关联任何表性能最好。第二个是ER分片绑定如果两个表是强关联的一对多关系比如订单表和订单商品表就用同一个分片键做分片绑定成ER表保证同一个父表的所有子表数据都落在同一个分片上关联的时候直接在单分片内做JOIN和单库性能完全一致。第三个是全局表比如字典表、配置表这种数据量小、变动少的表在每个分片里都存一份写操作的时候广播到所有分片更新关联的时候直接读本地分片的表不需要跨节点。实在满足不了的关联需求就拆成多次单表查询在应用层按ID做内存组装比跨库JOIN快几十倍而且好维护。5、跨分片分布式事务性能直接打对折单库的时候你开一个事务更新好几张表是本地事务性能高不会有一致性问题分库分表之后如果一个事务要更新不同分片的数据就变成了分布式事务不管用XA强一致事务还是TCC柔性事务性能都比本地事务差50%以上XA事务还要长时间持有跨节点的锁并发高了很容易出现死锁、锁等待。我们当时的优化原则非常简单尽量不让跨分片事务出现。所有写操作必须带上分片键保证同一个事务的所有写操作都落在同一个分片上用本地事务提交性能和单库完全一致实在需要跨分片更新的场景比如用户下单扣库存、加积分我们全用RocketMQ事务消息做最终一致性不用强一致分布式事务性能高也不容易出锁问题。三、分库分表后SQL编写的10条军规照着写就不会出故障踩了无数坑之后我们总结了10条写SQL的硬规则所有开发必须严格遵守落地之后再也没出过分库分表相关的线上故障1、所有在线业务的查询SQL必须带上分片键保证路由到单分片执行禁止无分片键的全路由SQL上线特殊场景必须做全局索引映射。2、禁止写大offset的深分页C端场景全用游标分页后台跳页场景必须用二次查询法禁止拉取大量数据到中间件内存归并。3、禁止跨分片JOIN优先用字段冗余、ER绑定、全局表解决关联需求必须关联的拆成单表查询在应用层组装。4、禁止大跨度的跨分片COUNT、GROUP BY、ORDER BY统计类需求走宽表或者离线数仓禁止在线上主库跑实时大规模聚合。5、尽量避免跨分片分布式事务写操作必须落到单分片跨分片一致性用事务消息做最终一致性。6、每个分片的索引设计和单库规则一致分片键必须建索引过滤、排序字段建对应联合索引禁止分片内全表扫描。7、IN查询的元素数量不要超过50个避免SQL过长、路由分片过多导致性能下降。8、单事务操作的记录数不要超过100条批量操作按分片键分组每批100条以内分批次提交不要一次提交上千条数据。9、禁止写SELECT *只查需要的字段减少跨分片传输的数据量降低中间件的内存压力。10、所有复杂查询、报表查询必须走从库禁止在主库跑大查询避免影响线上写业务。四、真实优化案例从12秒到18毫秒订单列表优化全流程我们当时上线后最慢的一个接口是商家后台的订单列表优化前的SQL是这样的sql-- 优化前的SQL执行时间12.1秒全路由深分页跨库JOINSELECT o.*, u.nickname, u.mobileFROM order_info oLEFT JOIN user_info u ON o.user_id u.idWHERE o.merchant_id ? AND o.create_time BETWEEN 2025-01-01 AND 2025-06-01 AND o.order_status 1ORDER BY o.create_time DESCLIMIT 5000, 20;我们拆解了一下问题首先merchant_id不是分片键SQL全路由到16个分片其次是5000条offset的深分页每个分片要扫5020条数据然后跨分片JOIN用户表要拉用户数据做内存关联最后还要排序归并慢是必然的。我们按规则一步步优化第一步解决JOIN问题把nickname、mobile字段冗余到订单表去掉LEFT JOIN不需要跨表关联查用户信息。第二步解决全路由问题商家后台的多条件筛选、分页查询全走Elasticsearch订单数据通过Binlog实时同步到ES筛选、分页、排序全在ES里做拿到当前页的订单ID和对应的user_id再直接路由到对应分片查订单详情不需要全路由扫所有分片。第三步解决分页问题ES的分页用search_after做游标分页不做深分页拿到ID之后都是按主键单条查询每个查询都路由到单分片性能极高。第四步优化分片内索引给每个分片的order_info表建联合索引(merchant_id, order_status, create_time, id)分片内查询直接走覆盖索引不需要回表。优化之后整个接口的平均响应时间从12秒降到了20毫秒以内性能提升了600多倍哪怕翻到几百页也不会卡顿更不会出现OOM的问题。很多人觉得分库分表是架构升级的“标配”不管自己公司业务量多大上来就要做分库分表觉得这样显得技术厉害。实际上我做了这么多年架构最大的感受是分库分表从来不是银弹它是解决单库容量瓶颈和性能瓶颈的最后手段会带来SQL编写、事务、运维、监控上的成倍复杂度。如果你的单表数据没到千万级单库能扛住性能完全没必要拆库拆表通过索引优化、读写分离、缓存就能解决绝大多数问题。如果真的到了必须拆的地步一定要提前把所有业务SQL过一遍提前把坑填上不要等上线崩了才去救火毕竟业务可用性永远比“看起来厉害的架构”重要。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
分库分表后SQL全崩这些坑我替你踩完了还记得我们第一次把2亿条订单数据拆成16个分片段上线的那天本来信心满满觉得性能肯定能起飞结果上线刚十分钟告警就炸了订单列表接口超时率冲到35%订单count统计接口最长要15秒返回运营翻列表到第30页直接把ShardingProxy节点干OOM整个订单链路卡了20多分钟最后紧急切回单库回滚代码全组加班排查了一整夜。很多人觉得分库分表是解决大数据量性能问题的银弹把表一拆就万事大吉根本没想过拆完之后原来写的SQL90%都会出问题从单库毫秒级查询到跨分片雪崩可能就是一次上线的距离。我前前后后主导过三次核心库的分库分表落地踩过的坑能写半本书今天把分库分表场景下的SQL改写、避坑、调优经验全部分享给你看完你再做分库分表绝对不会上线就崩。分库分表场景下SQL改写与性能调优实战一、为什么分库分表之后原来的SQL全变慢了很多人对分库分表的认知停留在“把大表拆成小表查询就变快”根本没意识到分库分表之后SQL的执行逻辑已经完全变了。单库场景下MySQL自己会做执行计划优化所有数据都在本地哪怕写的稍微差点最多也就是慢一点不会出大问题但分库分表之后所有SQL都要经过中间件ShardingSphere、MyCat这类做解析、路由、改写、结果归并四个步骤数据分散在不同物理节点上任何一步设计不好性能都会指数级下降。我给你算过一笔账同样一条SQL单库和分16个分片的执行逻辑差异有多大整理成了对比表表格SQL类型 单库执行逻辑 分库分表后执行逻辑 性能下降倍数等值查询带分片键 本地索引查找一次返回结果 直接路由到对应分片和单库逻辑一致 1倍无性能损失等值查询不带分片键 本地B树查找 全路由到所有分片每个分片执行完拉回结果归并 16倍等于分片数深分页LIMIT 10000,20 扫描10020条记录返回20条 每个分片扫描10020条拉16万条到内存排序归并 50~100倍跨分片ORDER BY排序 利用索引直接返回有序结果 每个分片返回有序结果中间件做内存多路归并排序 2~10倍跨分片COUNT/聚合 本地聚合后返回单个结果 每个分片做预聚合拉回中间件做二次聚合计算 5~20倍跨库JOIN关联 本地嵌套循环关联 拉取所有关联表数据到内存做嵌套循环匹配 10~100倍你看只要你的SQL带上分片键路由到单个分片性能和单库是一模一样的但只要不带分片键或者做跨分片的复杂操作性能会立刻暴跌这也是为什么很多人分库分表之后发现性能反而更差的核心原因——根本不是分库分表没用是写的SQL不符合分布式场景的规则。二、分库分表最容易踩的SQL坑每个都能搞崩线上我们当时上线后梳理了所有慢SQL发现99%的问题都是几个固定的坑每个坑之前单库的时候根本不是问题到了分布式场景下直接成了P0故障的导火索。1、不带分片键的全路由查询性能直接差N倍分片键是你拆表用的那个维度比如订单表用user_id做分片键同一个用户的所有订单都会落在同一个分片上。如果SQL带上了user_id中间件直接能定位到具体分片一次查询就能返回结果但如果SQL不带分片键比如按order_no查订单、按create_time范围查订单中间件根本不知道数据在哪个分片只能把SQL发给所有16个分片每个分片都执行一遍再把结果拉回来归并性能直接差16倍QPS高了能把所有分片库和中间件的CPU全打满。我们上线第一天的慢查询TOP10全是这类SQL比如有个查订单详情的接口开发只传了order_no没传user_id每次查都要扫16个分片原来单库10毫秒的查询变成了180毫秒高峰期直接把中间件的连接池占满了。后来我们做了两个优化一是给order_no做了全局索引映射建了一张小表存order_no对应的user_id和分片位置查order_no的时候先查映射表拿到分片键再路由到对应分片性能直接回到8毫秒二是在SQL审核阶段加了硬卡口核心业务表的查询SQL必须带分片键不带的直接卡CI不让上线。sql-- 反例不带分片键user_id全路由扫16个分片执行时间180毫秒SELECT * FROM order_info WHERE order_no DD20250601123456;-- 优化后拿到分片键直接路由到单分片执行时间8毫秒SELECT * FROM order_info WHERE user_id 12345 AND order_no DD20250601123456;2、深分页查询直接干爆中间件内存深分页在单库就慢到了分库分表场景直接是灾难级别的。比如你写LIMIT 10000,20单库场景只要扫10020条记录扔掉前10000条返回20条但分16个分片的话中间件会给每个分片发LIMIT 0,10020的SQL把每个分片的前10020条数据全拉到中间件内存再做归并排序取第10001到10020条总共要拉16*1002016万条数据到内存如果翻到第100页就要拉160万条数据中间件直接OOM。当时上线第一天运营翻订单列表到第50页直接打挂了两个ShardingProxy节点就是因为这个问题两个节点堆内存占满Full GC都回收不了最后只能重启。后来我们把所有C端的分页全改成了游标分页书签式分页永远带上一页最后一条记录的id和create_time作为查询条件每次只查下一页的20条每个分片也只需要返回20条结果不管翻多少页性能都是毫秒级再也不会出现拉大量数据到内存的问题。sql-- 反例深分页全分片拉取数据内存归并执行时间12秒容易OOMSELECT * FROM order_info WHERE create_time 2025-01-01 ORDER BY id DESC LIMIT 10000, 20;-- 优化后游标分页带分片键单分片范围查询执行时间15毫秒SELECT * FROM order_infoWHERE user_id ? AND create_time 2025-01-01 AND id 上一页最后一条IDORDER BY id DESC LIMIT 20;对于后台管理系统必须跳转到任意页的场景我们用了二次查询法第一步只查所有分片的主键ID内存排序找到当前页的ID范围第二步拿着ID去对应分片查详情不需要拉所有字段数据传输量减少90%以上哪怕翻到几百页也不会慢。3、跨分片排序聚合性能差到离谱很多人写SQL习惯用COUNT(*)、GROUP BY、ORDER BY这些操作在单库很正常到了跨分片场景就会特别慢。比如你要查某个时间段所有订单的总金额中间件要让每个分片先统计自己分片内的总金额再把16个分片的结果拉回来加起来要是带复杂WHERE条件每个分片都要扫几万条数据几秒都出不了结果。如果是GROUP BY中间件还要把每个分片的分组结果拉回来再按分组维度做二次聚合数据量稍大内存就扛不住。我们当时订单列表的总条数COUNT接口每次要3秒多才能返回后来干脆做了一张独立的统计宽表通过Binlog异步同步订单数据按天、按状态、按商家维度提前把COUNT算好查的时候直接查宽表只要10毫秒就能返回。对于大跨度的GROUP BY、多维度统计需求我们全移到了离线数仓做不让线上库跑实时大聚合。这里还要提醒一个坑跨分片排序的时候一定要保证每个分片的排序字段上有索引让每个分片返回的结果本身就是有序的这样中间件做归并排序的时候效率特别高如果分片内排序没走索引每个分片都要做文件排序拉回来的结果是乱序的归并的开销会大到无法想象。4、跨分片JOIN基本等于“自杀式查询”分库分表之后JOIN是当之无愧的性能杀手。如果两个关联表的数据在同一个分片那还能做本地JOIN性能和单库一样但如果数据不在同一个分片中间件只能把两个表的数据全拉到内存里自己做嵌套循环关联要是两个表各有几十万行内存里要匹配几亿次不仅慢还会直接把中间件搞挂。我之前见过一个开发在分库分表之后还写5表关联的SQL执行一次要22秒把整个分片集群的CPU都打到了100%影响了所有核心业务。后来我们定了死规矩分库分表后禁止跨分片JOIN所有关联需求用三个方案解决第一个是字段冗余用空间换时间比如订单表要关联查用户昵称、手机号直接把这两个字段冗余存在订单表里更新用户信息的时候异步更新订单的冗余字段即可查订单的时候不需要关联任何表性能最好。第二个是ER分片绑定如果两个表是强关联的一对多关系比如订单表和订单商品表就用同一个分片键做分片绑定成ER表保证同一个父表的所有子表数据都落在同一个分片上关联的时候直接在单分片内做JOIN和单库性能完全一致。第三个是全局表比如字典表、配置表这种数据量小、变动少的表在每个分片里都存一份写操作的时候广播到所有分片更新关联的时候直接读本地分片的表不需要跨节点。实在满足不了的关联需求就拆成多次单表查询在应用层按ID做内存组装比跨库JOIN快几十倍而且好维护。5、跨分片分布式事务性能直接打对折单库的时候你开一个事务更新好几张表是本地事务性能高不会有一致性问题分库分表之后如果一个事务要更新不同分片的数据就变成了分布式事务不管用XA强一致事务还是TCC柔性事务性能都比本地事务差50%以上XA事务还要长时间持有跨节点的锁并发高了很容易出现死锁、锁等待。我们当时的优化原则非常简单尽量不让跨分片事务出现。所有写操作必须带上分片键保证同一个事务的所有写操作都落在同一个分片上用本地事务提交性能和单库完全一致实在需要跨分片更新的场景比如用户下单扣库存、加积分我们全用RocketMQ事务消息做最终一致性不用强一致分布式事务性能高也不容易出锁问题。三、分库分表后SQL编写的10条军规照着写就不会出故障踩了无数坑之后我们总结了10条写SQL的硬规则所有开发必须严格遵守落地之后再也没出过分库分表相关的线上故障1、所有在线业务的查询SQL必须带上分片键保证路由到单分片执行禁止无分片键的全路由SQL上线特殊场景必须做全局索引映射。2、禁止写大offset的深分页C端场景全用游标分页后台跳页场景必须用二次查询法禁止拉取大量数据到中间件内存归并。3、禁止跨分片JOIN优先用字段冗余、ER绑定、全局表解决关联需求必须关联的拆成单表查询在应用层组装。4、禁止大跨度的跨分片COUNT、GROUP BY、ORDER BY统计类需求走宽表或者离线数仓禁止在线上主库跑实时大规模聚合。5、尽量避免跨分片分布式事务写操作必须落到单分片跨分片一致性用事务消息做最终一致性。6、每个分片的索引设计和单库规则一致分片键必须建索引过滤、排序字段建对应联合索引禁止分片内全表扫描。7、IN查询的元素数量不要超过50个避免SQL过长、路由分片过多导致性能下降。8、单事务操作的记录数不要超过100条批量操作按分片键分组每批100条以内分批次提交不要一次提交上千条数据。9、禁止写SELECT *只查需要的字段减少跨分片传输的数据量降低中间件的内存压力。10、所有复杂查询、报表查询必须走从库禁止在主库跑大查询避免影响线上写业务。四、真实优化案例从12秒到18毫秒订单列表优化全流程我们当时上线后最慢的一个接口是商家后台的订单列表优化前的SQL是这样的sql-- 优化前的SQL执行时间12.1秒全路由深分页跨库JOINSELECT o.*, u.nickname, u.mobileFROM order_info oLEFT JOIN user_info u ON o.user_id u.idWHERE o.merchant_id ? AND o.create_time BETWEEN 2025-01-01 AND 2025-06-01 AND o.order_status 1ORDER BY o.create_time DESCLIMIT 5000, 20;我们拆解了一下问题首先merchant_id不是分片键SQL全路由到16个分片其次是5000条offset的深分页每个分片要扫5020条数据然后跨分片JOIN用户表要拉用户数据做内存关联最后还要排序归并慢是必然的。我们按规则一步步优化第一步解决JOIN问题把nickname、mobile字段冗余到订单表去掉LEFT JOIN不需要跨表关联查用户信息。第二步解决全路由问题商家后台的多条件筛选、分页查询全走Elasticsearch订单数据通过Binlog实时同步到ES筛选、分页、排序全在ES里做拿到当前页的订单ID和对应的user_id再直接路由到对应分片查订单详情不需要全路由扫所有分片。第三步解决分页问题ES的分页用search_after做游标分页不做深分页拿到ID之后都是按主键单条查询每个查询都路由到单分片性能极高。第四步优化分片内索引给每个分片的order_info表建联合索引(merchant_id, order_status, create_time, id)分片内查询直接走覆盖索引不需要回表。优化之后整个接口的平均响应时间从12秒降到了20毫秒以内性能提升了600多倍哪怕翻到几百页也不会卡顿更不会出现OOM的问题。很多人觉得分库分表是架构升级的“标配”不管自己公司业务量多大上来就要做分库分表觉得这样显得技术厉害。实际上我做了这么多年架构最大的感受是分库分表从来不是银弹它是解决单库容量瓶颈和性能瓶颈的最后手段会带来SQL编写、事务、运维、监控上的成倍复杂度。如果你的单表数据没到千万级单库能扛住性能完全没必要拆库拆表通过索引优化、读写分离、缓存就能解决绝大多数问题。如果真的到了必须拆的地步一定要提前把所有业务SQL过一遍提前把坑填上不要等上线崩了才去救火毕竟业务可用性永远比“看起来厉害的架构”重要。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围
)
