拿线上订单库开刀的那天，我才算真正把MySL的优化摸透了。表象是接口从几十毫秒飙到三秒，一层层查下来，索引失效有之、慢查询日志开着却从不看有之、单表躺了过亿数据还傻乎乎全表扫也有之。很多团队其实都卡在这三个问题上：索引不会建、SL（慢查询日志）不会看、数据量上去了不知道该怎么拆。这篇文章我不讲空泛的理论，直接把我实际验证过的索引优化手段、慢查询诊断流程、分库分表落地方案完整捋一遍，每一步都有参数、有步骤、有坑点，照着做基本不会跑偏。

1. 内容整体设计与思路拆解

最开始接手这个优化项目时，我没有急着去改SQL或者加缓存，花了两天时间专门梳理整个优化路径。MySL的瓶颈本质上就三类：单条SQL执行不够快、慢查询太多导致整体负载下不来、数据容量太大导致单机物理上限被击穿。这三类问题对应的手段分别是索引优化、SL诊断体系搭建、分库分表架构改造，优先级也基本就是这个顺序。

做这个排序的原因很实际。先做索引优化，是因为它性价比最高，经常一条复合索引建下去，几个核心接口直接从秒级回到毫秒级，而且风险极小。接着做SL体系，是因为索引怎么建、建在哪张表上，依赖的是对真实慢查询的统计和分析，没有数据支撑的索引设计都是拍脑袋。最后才轮到分库分表，因为分库分表一旦实施，整个数据访问层的逻辑全要跟着变，跨库跨表的查询、事务、聚合全成了麻烦，如果前两步能把性能拉回来，就完全没必要走到这一步。

从方案选型上看，我没有选择全量重构，而是采用渐进式改造：先监控、再优化、最后按需拆分。这个思路适合大多数已有线上业务在跑的团队，改动小、回滚容易、每一步都有明确的收益验证。无论你的业务是电商订单、社交Feed流，还是IoT设备上报，这套节奏都适用。

2. 索引优化核心细节与实操要点

2.1 从执行计划看索引有没有被真正用上

很多同学建了一堆索引，但SQL根本不走，全表扫描依旧我行我素。MySL里判断索引是否生效最直接的手段就是看执行计划，关键字段就几个：type、key、rows、Extra。

type字段是我每次第一眼看的，它直接反映了访问类型。从好到差大概是：system、const、eq_ref、ref、range、index、ALL。如果看到ALL，基本就在全表扫描了，这类SQL是首要优化对象。ref和range都算正常范围，说明索引用上了，但还需要看rows字段估算实际扫了多少行。

真实项目里的执行计划示例：

EXPLAIN SELECT order_id, amount, status FROM t_order WHERE user_id = 10086 AND status = 1 ORDER BY create_time DESC LIMIT 20;

如果返回结果里type=ref、key=idx_user_status、rows=3500，说明这个查询走了索引，但扫了3500行才拿到结果，还有优化空间。如果type=ALL、key=NULL，说明索引压根没建对或者语句写法把索引废掉了。

Extra字段里出现的Using filesort、Using temporary尤其值得警惕，前者意味着排序没走索引，数据量大时效率极低；后者意味着查询创建了临时表，group by或distinct操作多了会非常吃内存。

2.2 复合索引设计最常见的三个误区

第一，把区分度最高的列放最前面。很多人建复合索引时习惯把查询最频繁的列放第一位，这是非常典型的错误做法。复合索引遵循最左前缀原则，但这里的核心逻辑是区分度优先。比如在订单表里，order_status字段虽然查询频率极高，但它的取值就那么几个，区分度极低。如果把status放在复合索引第一位，查询时即便用上了索引，也需要扫描大量重复数据。正确做法是把user_id这种筛选后能极大缩小范围的字段放前面。

第二，索引列上做运算和隐式转换。一旦在索引列上使用函数或者表达式，比如WHERE DATE(create_time) = '2024-01-01'，或者WHERE user_id = '10086'这种字符串和数字比较，MySL的优化器基本就放弃索引了，老老实实全表扫。这类问题排查起来特别隐蔽，因为SQL看起来似乎没有任何问题。

第三，排序字段与查询字段割裂。ORDER BY create_time这个需求如果没有跟WHERE条件里的字段建成同一个复合索引，就会触发Using filesort。比如前面那条SQL，如果索引只建了idx_user_id，没覆盖到status，排序就会在内存或磁盘里做文件排序。应该把它建成idx_user_id_status_time，即(user_id, status, create_time)，一次查询就全部走索引完成过滤和排序。

2.3 回表与覆盖索引的理解

InnoDB的二级索引叶子节点存的是主键值，通过二级索引查数据时，得先拿到主键，再回主键索引查整行数据，这个过程叫回表。回表本身不是问题，但回表次数过多时，性能就会明显下降。

覆盖索引的意思，就是要查的列全部包含在索引里，查询就不需要回表。举例来说，如果业务上只需要查订单的order_id、amount、status三个字段，把复合索引设为(user_id, status, order_id, amount)，那这个查询全程都在idx这个索引上完成，Extra字段里会出现Using index，这也叫索引覆盖。

实际项目中，覆盖索引对高并发查询的提升是肉眼可见的。我见过一个查询订单列表的接口，添加覆盖索引后，整体耗时从800毫秒降到了120毫秒。但需要注意别为了覆盖而盲目把大字段（如text类型、varchar(500)以上）塞进索引，那会导致索引体积爆炸，反而拖慢写入和索引维护。

2.4 索引数量与写入性能的平衡

索引不是越多越好。每多一个索引，写入数据时就要多更新一棵B+树，插入和更新的开销都会增加。有个项目里的用户表原来有八个索引，接口写入速率一度压不下去，后来清理掉三个利用率极低且重复的索引，写入性能提升了近百分之三十。

最典型的冗余索引场景是：已有idx_user_id(user_id)，又单独建了idx_user_status(user_id, status)。后者实际上完全包含了前者的功能，前者就是典型的冗余索引，直接删除即可。

我自己整理了一套检查索引冗余的SQL思路，核心是根据information_schema.statistics表统计每个索引的字段组合，找出前缀完全重复的索引，再结合slow log里的实际使用频率判断谁该留谁该删。

3. 慢了才知道：SL慢查询体系搭建与排查实录

3.1 如何正确开启和配置慢查询日志

很多团队慢查询日志是开着的，但从来没分析过，这等于白开。SL（Slow Log）的第一要务是把慢查询时间阈值设置得合理，默认的10秒在绝大多数业务里太过宽松，等到接口超过10秒才记录，用户早就跑光了。

我的建议是OLTP业务设置在1秒，核心接口甚至可以压到500毫秒。同时要把log_queries_not_using_indexes打开，这样那些没走索引的查询也会被记录下来，很多隐藏的性能地雷就是这样暴露出来的。

slow_query_log = ON slow_query_log_file = /data/mysql/log/slow-query.log long_query_time = 1 log_queries_not_using_indexes = ON

注意修改配置后需要重启MySL实例才生效。如果用的是云数据库，通常控制台有参数组直接可以调整，不必重启。

3.2 基于mysqldumpslow的快速分析

慢查询日志文件可能一天就增长几百MB，不能让DBA用人眼去翻。直接用mysqldumpslow按执行次数、查询时长排序，把最值得优化的SQL捞出来。

mysqldumpslow -s at -t 20 /data/mysql/log/slow-query.log

这条命令的含义是按平均查询时间排序，取前20条。我习惯先把按执行次数排序的结果看一遍，因为执行次数多但单次不慢的SQL，对总体负载的影响往往比单次极慢的大。真正的优化优先级应该是：高频率慢查询 > 低频率极慢查询。

3.3 一次真实慢SQL的完整诊断过程

有一个查订单详情的SQL经常出现在慢日志里，单条执行1.2秒，每天被调用数万次。原始SQL大致长这样：

SELECT * FROM t_order WHERE user_id = 10086 AND status IN (1, 2, 3) AND create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59' ORDER BY create_time DESC;

执行计划一跑，发现虽然建了(user_id, status)的复合索引，但rows已经扫描了十几万行，Extra里还出现了Using filesort。问题出在IN条件和范围查询的组合上。

IN条件里的多个值会分别走索引回表，如果IN列表很长，相当于多次索引查找；BETWEEN的范围查询又会让复合索引里的create_time排序失效。优化思路拆成两步：第一步把SELECT *改成只查询必要字段，配合覆盖索引；第二步把复合索引调整成(user_id, create_time)，让维度查询和排序都走索引。

修改后的效果立竿见影，同样的查询耗时从1.2秒降到了80毫秒左右，这个案例也验证了一个经验：IN和范围查询尽量拆成两条SQL由应用层合并，或者调整复合索引顺序使范围条件靠后。

3.4 慢查询日志的周期性巡检

慢查询分析不能想起来才看一眼，应该形成固定节奏。我在项目里养成了每周定时拉取慢查询报表的习惯，观察慢查询数量的趋势变化。如果发现某个时间段慢查询数量陡增，通常是数据量涨了导致索引区分度下降，或者业务代码里新上线了一个糟糕的SQL。这种趋势监控比被动等告警有效得多，帮团队提前规避过好几次线上事故。

4. 分库分表最佳实践：从评估到落地的完整路径

4.1 什么情况下才真的需要分库分表

分库分表是重型武器，后坐力极大，不到万不得已不要上。判断标准我总结为三点：单表数据量超过2000万且还在快速增长；单库写入QPS持续打满或磁盘IO长期高位；按索引优化和SL治理后仍解决不了容量与并发问题。

有个非常经典的评估方法：算一算单表未来十二个月的预估容量。假设当前订单表有1000万行，月增量为200万行，那一年后就是3400万行。如果业务对查询延迟的要求非常苛刻，那基本上就该准备分片方案了。反过来，如果月增量只有20万，说明表还能扛很久，与其分库分表，不如把时间花在索引优化和归档冷数据上。

4.2 垂直拆分与水平拆分的取舍

垂直拆分的核心是把宽表拆成多张窄表，按业务域划分字段。比如把订单主表只留下单核心字段，把订单扩展信息（买家备注、卖家备注、发票信息）拆分到订单扩展表。这种方式特别适合字段特别多、部分字段又大又很少被查询的表，改造难度低于水平拆分，应用层只需要把单次查询改成两三次查询，或者用join关联。

水平拆分则是将同一张表的数据行按某种规则分布到多张结构相同的表中。最常见的做法是按user_id做哈希取模，或者按时间按月分表。这种方案能真正解决单表数据量过大的问题，但代价是查询逻辑大幅复杂化，跨表聚合、分页排序都成了问题。

从我的经验看，如果单表行数还没有触碰到物理极限，优先做垂直拆分；只有垂直拆分加索引优化都无法解决延迟问题时，才考虑水平拆分。多数中小团队走到垂直拆分那一步，性能问题已经解决大半了。

4.3 分片键选错了会有什么后果

分片键是整个分库分表方案里最不能选错的字符，因为一旦数据分布下去，想再改分片键基本等于重构数据库。我见过选分片键只考虑查询频率不考虑数据分布均匀性的案例。

比如按订单归属的商家ID分片，结果某几个大商家的订单量是普通商家的上千倍，于是这几个分片负载极高，其他分片闲着。数据倾斜让分库分表形同虚设。正确做法是选一个业务查询必经、且取值足够离散的字段。对绝大多数C端业务而言，user_id是第一选择；如果是后台报表系统，create_time按年月分桶也很常见。

还要特别注意分片键必须覆盖绝大多数高频查询。如果选了user_id做分片键，但业务上经常根据order_id直接查详情，那每次查询都得在全部片里广播，开销非常大。这种情况要么强制让应用层先通过user_id路由，要么用映射表记录order_id与user_id的对应关系。

4.4 路由与分片算法细节

目前业界比较成熟的方案有两类：一类是应用层集成SDK，直接在代码里根据分片键算出目标库表；另一类是引入代理层中间件，SQL发给代理，由代理解析并路由到底层库表。SDK方式性能更好、侵入性更大，代理层方式对应用透明、但多一跳网络延迟。

我实操下来更推荐先用SDK方式，它方便精细控制路由逻辑，等团队对分片规则完全吃透了再考虑代理层。分片算法的选择上，最简单的取模方式虽然老套但足够可靠。比如设计16个库，每个库64张表，总共1024张表，路由规则就是：

库序号 = (user_id / 64) % 16 表序号 = user_id % 64

这套规则的精妙之处在于相同的user_id永远落在同一张表，单用户数据完整，不会出现跨表查询。日后的扩容也有平滑方案：1024张表是一个逻辑整体，扩容时只增加库数，表数不变，通过迁移部分数据就能实现水平扩展。

4.5 全局主键的生成方案

分表之后，数据库自增主键就不能用了，因为每个表都从1开始，铁定冲突。全局唯一主键的几个可选方案里，我实际测试过UUID、雪花算法、号段模式三种。

UUID生成简单，但长度长、无序，在InnoDB里作为主键会导致页分裂极其频繁，写入性能下降非常明显，直接排除了。雪花算法生成的是趋势递增的18位长整型，性能好、占用空间小，基本成了业界标配，但它依赖机器ID和时钟，时钟回拨时要靠缓存和等待来解决。号段模式则是从发号器批量取号段，比如一次取1000个ID，应用层内存分配，性能也很好，只是多一个发号器的维护成本。

对大多数项目，我建议直接采用雪花算法，网上各种语言的实现都很成熟，关键是处理好workId的分配和时钟回拨的兜底逻辑。

4.6 分库分表后的三大常见难题

跨库join是第一个必须面对的坎。分库分表后两张业务相关的表可能落在不同的库甚至不同的机器上，数据库层面的join基本废掉了。通用的解法是在应用层做两次查询再内存组装。虽然多一次网络开销，但能保持系统简单可控，实测性能下降幅度远小于预期。

分布式事务是第二个难点。分片后原来一个本地事务可能跨多个库，MySL自己的XA事务在跨库场景下性能损耗很大。我建议尽量从业务设计上规避：把需要事务强一致的多个表放在同一个分片内，也就是按相同的分片键分布。如果一定要跨分片，就需要考虑TCC或可靠消息最终一致性方案，这属于另一个层面的架构话题了。

聚合查询是第三个坑。比如后台需要统计某月订单总量，这条SQL在分片前很简单，分片后就得把1024张表的数据全汇总起来，再在应用层做聚合，慢且折腾。常规做法是提前建设汇总表或数据仓库，实时性要求不高的统计查询全部走汇总表，避免直接查分片。

4.7 平滑扩容的实操步骤

扩容最怕的是停服。我在项目里验证过一套基于双写的平滑迁移方案：先创建新的分片集群，让应用层同时写新旧两套库，并把历史数据按新分片规则回放迁移。数据迁移期间用脚本对账，保证新老数据一致。等到校验通过、灰度切换读流量到新库，观察稳定后再下线旧库。

这套方案的每一步都要手工脚本配合监控来推进，整个过程大概持续两到三天，但业务全程无感。很多团队之所以不敢分库分表，就是怕扩容复杂度失控，其实只要把迁移拆成小批次、可回滚的步骤，风险是可控的。

5. 踩坑实录与常见问题速查表

5.1 三个真实踩坑案例

第一个案例是隐式类型转换导致索引失效。有个接口查询订单ID时传入的参数被定义成了varchar，拼SQL时没有加引号，MySL会自动把字符串转为数字比较，结果索引列上发生了类型转换，几千万行的表直接全表扫，接口超时告警一片。排查过程就是打开慢日志，发现一条扫描行数吓人的SQL，用EXPLAIN一看type=ALL，再一看字段类型和参数类型不匹配，问题一目了然。这个教训后来被我写进了团队的代码规范：条件字段是什么类型，传参就必须是什么类型。

第二个案例是分页深度越深越慢。有个运营后台的分页查询，页数到100页之后接口基本就卡死了。原因是传统的LIMIT 100000, 20会让MySL把前面10万行全部查出来再丢弃，即使走索引也扛不住。优化手段改成基于上一页最大ID的游标分页，也就是WHERE id > 上一页最大ID ORDER BY id LIMIT 20。性能从原来的二十多秒直接降到几十毫秒，运营同事再也没抱怨过。

第三个案例是分库分表后统计报表不可用。有一段时间老板要出全国各区域的销售周报，分片前一条SQL能算完，分片后每次都要汇总几十张表再在应用层做聚合，耗时长而且经常内存溢出。最后是专门建了离线数仓同步任务，把分片数据同步到一张汇总大宽表，报表查询全部走数仓，实时数据采取分钟级延迟同步，问题才彻底解决。这个案例给我最大的启发是：分库分表之前就要把查询场景分类，哪些走实时分片，哪些走汇总数据，提前定清楚，后续少掉很多头发。

5.2 常见问题速查表

6. 工具选型与实际工作流

聊完具体技术点，再花点篇幅说说工具链。优化MySL不是只靠一个命令行就能搞定的，我日常依赖的是一套组合：慢查询日志分析用mysqldumpslow加自己写的解析脚本；索引分析和重复索引检测直接查information_schema.statistics视图；真实执行计划用EXPLAIN ANALYZE来验证优化后的实际耗时和扫描行数。

如果数据规模更大，慢查询日志量大到用工具已经无法快速定位问题，可以考虑引入开源APM链路追踪系统，把SQL耗时和接口调用关联起来，快速定位是哪个接口的哪条SQL拖慢了整个请求。这套工作流的统一原则是：先有监控数据，再谈优化动作，让每一次索引变更和分片调整都有数据支撑。

还有一个经验值得说：每次上线索引变更或者分库分表规则变更，一定要提前准备回滚方案。索引变更一般就是DROP掉新索引，操作成本低；分库分表的回滚就麻烦得多，所以更要在设计阶段就把双写和切换开关做好。线上系统的第一原则永远是稳定，而不是炫技。

我在实际项目里感受到最深的体会是：优化数据库性能，七成靠的是清晰的数据感知和诊断，不在闷头建索引，更不在盲目分库分表。先构建起SL监控体系，让所有慢查询都暴露在明面上，再针对性地用索引解决90%的问题，最后剩下不到10%的容量和并发瓶颈才轮到分库分表出场。这个顺序一旦倒过来，团队很容易陷进分布式事务、跨库查询这些深坑里出不来。分库分表是重型手段，永远应该是优化路径上最后一张牌，而不是第一张。