一说MySQL优化，大部分人脑子里跳出来的就是三个词：索引、SQL、分库分表。这样理解不算错，但很容易把顺序搞反。我见过不少团队一遇到性能问题就嚷嚷着要分库分表，结果把系统搞得无比复杂，最后发现是几条SQL没写对。这篇文章不是教科书，是我在实际项目里处理慢查询、压测、以及一次真实拆分后沉淀下来的操作路径：先索引、再SQL、最后才是架构层拆库拆表。内容适合正在写业务代码的开发和运维同学，哪怕你只是第一次接触优化，也能按着里面的步骤查到问题在哪。
1. MySQL优化的整体思路：先定位瓶颈，再谈方案
1.1 慢SQL背后最常见的四种瓶颈
先说一个我反复遇到的场景：线上某业务突然变慢，监控面板上CPU或IO飙升，用户开始投诉。这时如果直接去看SQL语句，往往发现并不是语句写错了，而是它在执行时触发了某种瓶颈。结合个人排障经验，慢SQL背后基本逃不出以下四类问题：
- CPU瓶颈：大量排序、聚合、关联查询，以及非常深的子查询，都会消耗CPU。典型特征就是数据库所在主机的CPU使用率居高不下。
- IO瓶颈：最常见的是走了全表扫描，或者索引没有覆盖查询列，导致大量随机读磁盘。特征是磁盘IO等待时间明显增加。
- 锁等待瓶颈：行锁竞争、间隙锁冲突、元数据锁等待，都可能让一条本来很快的SQL被卡住。这种问题从执行时间上看很慢，但CPU和IO都很平静。
- 连接数瓶颈：慢查询增多后连接堆积，线程池被打满，新的请求排队。这种已经属于“并发症”了，但根因还是慢SQL。
真正接手过一个订单类系统之后，我最大的感受是：大多数慢SQL并不是单一原因，而是“索引没建对”和“SQL写得贪心”叠加在一起，数据量一上来就彻底暴露。所以优化的第一步不是动方案，而是确认瓶颈到底属于哪一类，再决定动手顺序。
1.2 为什么优先做索引和SQL，而不是一上来就分库分表
很多同学对“分库分表”有执念，总觉得拆完库、拆完表，性能问题就自动解决了。这个想法非常危险。分库分表会引入分布式事务、跨分片查询、全局唯一ID、数据迁移等一系列问题，每一件都够喝一壶的。如果你只是因为单表数据量到了一千万就想拆分，大概率会把自己绕进去。
我习惯把优化比作整理房间：索引和SQL优化是“把常用的东西放到顺手的位置”，分库分表则是“换个大房子重新装修”。房间还没乱到住不了人，为了找一双鞋就砸墙改造，明显不合理。实际操作中有个经验法则：单表数据量在500万到1000万左右，只要索引设计合理，读写压力通常还能扛住；如果单表超过2000万，而且字段冗余、慢SQL频发，那才是需要认真评估分库分表的时刻。
所以顺序永远是这样：先看索引和SQL能不能解决80%的问题，再讨论架构调整。这不仅是技术理性，也是成本考量——改写SQL是小时级的工作，分库分表是周月级的工作。
1.3 优化前先定好量化指标
没有指标就谈不上优化。我每次动手前，至少先盯住三个数字：扫描行数、响应时间、锁等待时间。其中扫描行数最直观，优化前后最好能下降一个数量级。
EXPLAIN输出的rows字段就是估算扫描行数，type字段给出了访问类型。常见的访问类型从好到差依次是：
| type值 | 含义 | 说明 |
|---|---|---|
| system | 系统表，几乎不访问 | 极少出现 |
| const | 主键或唯一索引等值匹配 | 性能最好 |
| ref | 普通索引等值匹配 | 典型的最优状态 |
| range | 索引范围扫描 | 可接受 |
| index | 扫描了整棵索引树 | 比ALL好，但仍需小心 |
| ALL | 全表扫描 | 最需要警惕 |
后面实战部分会详细演示。这里只需要记住一个原则：优化不是靠感觉，而是靠前后对比这些数值，才能真正验证方案有没有生效。
2. 索引优化：让MySQL少做无用功
2.1 B+树加速与回表那些事
索引为什么快？因为MySQL的InnoDB存储引擎使用B+树组织索引，查找一条记录的时间从逐行扫全表变成了沿着树形结构做很少几次比较。就好比你在一本按拼音排序的字典里查某个字，翻开目录直接定位，而不是从第一页往后翻。
但索引也不是免费的。每个索引都是一棵独立的B+树，写入数据时除了更新主键索引，还要同步更新每一个二级索引。所以索引越多，写放大越明显。有些表一建就是七八个索引，查询倒是快了，写入慢到怀疑人生。另外，二级索引保存的是主键值，查询字段如果不在索引里，还得拿着主键去主键索引里再捞一次，这个动作叫“回表”。回表次数多了，性能和全表扫描也没多大差别。
明白这两个概念之后，索引设计的核心就清晰了：让查询尽量在索引树上就能拿到所有需要的字段，也就是“覆盖索引”，同时避免索引数量失控。
2.2 索引失效的七种典型场景
很多人建了索引但查询还是慢，就是因为SQL写法让索引“失效”了。以下七种场景我全都踩过，每一种都配了反面示例。
- 前置模糊匹配：
LIKE '%abc'无法使用索引，因为B+树只能按前缀匹配；如果必须这样查，考虑全文索引或换查询方案。 - 对索引列做函数运算：
WHERE YEAR(create_time)=2025会让索引失效。应该改成范围条件：WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'。 - 隐式类型转换：
WHERE phone=13800138000，如果phone列是varchar类型，MySQL会把字符串隐式转成数字比较，导致索引失效。改成字符串字面量带上引号就好。 - 联合索引不满足最左前缀：建立了
(a,b,c)联合索引，却只查询b和c，那索引根本用不上。 - 范围查询影响右侧列：
WHERE a=1 AND b>100 AND c=2，b用了范围后，c无法继续命中联合索引。这时c的过滤只能靠回表。 - OR条件中有一侧无索引：
WHERE a=1 OR c=2，如果c没有索引，MySQL可能放弃索引扫描，转成全表扫描。 - 区分度太低：性别字段建索引，每个值对应的数据行数太多，优化器认为还不如全表扫。
遇到这些情况，先不要急着怪MySQL，而是检查SQL写法是否破坏了索引条件。破坏索引不是说索引不存在，而是优化器算下来用索引导航的成本更高，所以放弃它。
2.3 联合索引怎么设计才算合理
联合索引设计是整个索引优化的核心。以订单表为例，最常见的查询是：
SELECT order_id, amount, status FROM orders WHERE buyer_id = 123456 AND status = 1 ORDER BY create_time DESC LIMIT 20;候选索引有很多种建法，但最有价值的方案是围绕高频查询设计一个联合索引：
ALTER TABLE orders ADD INDEX idx_buyer_status_time (buyer_id, status, create_time);设计逻辑有三点：
- 买家查询是最核心路径，所以
buyer_id放在第一列，满足最左前缀。 status作为第二列过滤掉大量无效数据，同时让等值条件尽可能多命中。create_time放在最后，因为ORDER BY create_time可以利用联合索引完成排序，避免额外的filesort。
不要盲目在每一列上都建独立索引。普通二级索引之间无法自动合并（虽然MySQL有Index Merge优化，但场景有限），而且独立索引越多，写放大越严重。好的索引设计，往往三到五个联合索引就足够覆盖一个核心表的主要查询路径。
2.4 用EXPLAIN验证索引是否生效
设计完索引，必须用EXPLAIN验证。上面那条查询的验证结果应该是：
+----+-------------+--------+----+--------------------------+------+---------+------+------+--------------------------+ | id | select_type | table | type| key | rows | filtered| Extra | +----+-------------+--------+----+--------------------------+------+---------+------+--------------------------+ | 1 | SIMPLE | orders | ref | idx_buyer_status_time | 200 | 1.00 | Using index condition | +----+-------------+--------+----+--------------------------+------+---------+------+--------------------------+重点关注三个地方：type是ref而不是ALL；key显示实际用了哪个索引；rows是否从几十万降到几百。Extra里如果出现Using filesort，说明排序没有走索引，需要微调联合索引的字段顺序或者改变查询条件。
有一个小技巧：如果发现优化器没有用你设计的索引，可以先试试FORCE INDEX：
SELECT order_id, amount FROM orders FORCE INDEX (idx_buyer_status_time) WHERE buyer_id = 123456 AND status = 1;但这只能作为临时验证手段。线上如果经常需要FORCE INDEX，说明索引本身的设计和真实数据分布不匹配，必须重新审视。
3. SQL优化：把查询写得更聪明
3.1 常见的几种“贪心”SQL写法
索引解决了访问路径问题，SQL写法解决的是“让数据库做的事越少越好”。以下三种写法我在代码评审里经常见到，每次都要花很长时间讲道理。
第一种是SELECT *。看起来方便，实际上会让覆盖索引失效，并加大网络传输压力。以订单表为例，明明只需要order_id和amount，结果把几十个字段全部查出来，索引覆盖不到时就会回表。更好的做法是只选需要的列。
第二种深分页问题，典型症状是页面越翻越慢：
SELECT * FROM orders WHERE buyer_id = 123456 ORDER BY create_time DESC LIMIT 100000, 20;MySQL要扫描到第100000行才开始返回，前面扫描的行全部浪费。优化思路是用延迟关联：
SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE buyer_id = 123456 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id = t.id;先用索引覆盖查询只取主键，回表只发生在最后20条结果，扫描成本大幅下降。
第三种是选择性不严，比如WHERE status != 1这种负向条件，很少能用上索引。能改成等值条件就尽量改成等值条件，不能改就考虑重新设计查询角度。
3.2 JOIN与子查询的取舍
数据库开发里最经典的问题就是“多表关联到底怎么查效率高”。我个人的经验是：能用JOIN，尽量别在业务代码里逐条查再拼接。原因很简单，每发一次SQL都有网络往返和SQL解析开销，循环几百次累加起来非常可观。
但JOIN也不是随便写。核心原则是小表驱动大表：
SELECT user.name, order.amount FROM orders o JOIN users u ON o.buyer_id = u.id WHERE o.status = 1;在上面的SQL里，orders作为驱动表，users作为被驱动表。如果外层表只有100条，内层表用主键u.id关联，速度会非常快；但如果反过来，让几千上万行的用户表做驱动表，每行都要去订单表里查一次，成本就大了。
判断驱动表的方法很简单：EXPLAIN第一行显示的表就是驱动表。如果发现驱动表是数据量大的一方，尝试调整STRAIGHT_JOIN提示顺序，或者增加过滤条件，让驱动表先变小。
3.3 批量写入与锁的权衡
查询优化完了，写入也要注意。最典型的低效写法是循环单条插入：
for (item : list) { INSERT INTO orders (order_id, amount, buyer_id) VALUES (...); }每条INSERT独立提交，不仅开销大，还会频繁获取和释放行锁。改成批量插入会快很多：
INSERT INTO orders (order_id, amount, buyer_id) VALUES (1, 100, 123), (2, 200, 456), (3, 300, 789);但批量值也不是越大越好。一个事务里插入几万条，执行时间很长，导致锁持有时间变长，还可能阻塞其他事务。我通常控制单批在500到1000条之间，同时配合事务提交，这样能在吞吐量和锁竞争之间找到平衡。
4. 分库分表：时机、策略和避坑
4.1 分库分表的启动条件与替代方案
什么情况下才需要分库分表？我个人总结的启动条件有三条，满足两条以上才值得动手：
- 单表数据量长期在2000万以上，并且还在快速增加。
- 慢SQL数量经过索引和SQL优化后仍然居高不下。
- 磁盘、IO或备份恢复时间已经明显影响运维操作。
这个条件说完，相信你已经明白，它的门槛比很多人想象中高得多。我遇到过某个业务表不到三百万行，但一到大促就卡，最后排查发现是几条JOIN没写好，跟数据量半毛钱关系没有。
在真正分库分表之前，有一些替代方案值得先试：分区表适合按时间归档数据；读写分离可以分担只读压力；冷热分离把老订单扔到历史库。这些方案的实现成本远低于分库分表，而且不会改变业务层的访问方式。如果这些方案都救不了你，再考虑拆分。
4.2 垂直拆分与水平拆分怎么选
分库分表两个方向，很多人都会混淆。
垂直拆分是把一张宽表拆成多张窄表，或者把不同业务的表放到不同库。比如把商品表拆成基本信息表和库存表。适用场景是表中存在大量不常访问的大字段（比如文本内容、图片URL）。垂直拆分的缺点是操作比较复杂，一次查询可能需要关联多张表，跨库JOIN基本不可能，最后还是要在业务层组合数据。
水平拆分是同一个表名按某种规则分布到多个库/多张表里。比如订单表拆成orders_0、orders_1……直到orders_15。拆分的具体规则有两种：
- 范围分片：按时间或ID区间切分，优点是可以方便归档，缺点是数据热点不均匀，比如本月的数据全落在同一片。
- 哈希分片：对分表键取模，
table_id = hash(sharding_key) % N。优点是分布均匀，缺点是不好拓展，扩容时需要迁移数据。
实际项目中，绝大多数选择了哈希分片，因为热点均匀才是高并发查询更关心的事情。
4.3 分表键怎么选，是最核心的决策
分表键选错，整个拆分方案就废了一半。网上很多文档会告诉你“选高频查询字段”，但这句话太模糊了。我通常用三个标准衡量：
- 区分度高：每个分片的行数尽量接近，避免数据倾斜。
- 查询模式集中：业务中至少80%的查询都带这个字段作为条件。
- 粗粒度和扩展性：优先选择业务主键，比如买家ID、商家ID。
拿订单系统举例，最常见的查询是“用户查自己订单”，所以分表键选择buyer_id非常合理。但代价是，如果业务方需要“商家查自己店铺的订单”，由于查询条件不带buyer_id，就会变成一次全分片扫描。这个坑在拆分初期就必须要想到，通常是提前把这些跨分片查询引导到备用的查询引擎（比如一份同步到分析库或搜索引擎）。
还有一点：不要用数据库自增主键作为分表键。不是说不行，而是自增主键在业务层没有查询意义。用户问你“我买了什么”，你拿一个自增主键完全无从判断去哪个分片找。所以我建议分表键必须和业务访问路径强关联。
4.4 拆分之后必定要面对的三座大山
第一座是全局唯一ID。原来依赖单库自增主键，拆分后每个分片都各自递增，必然冲突。业界方案非常多，我比较推荐的是号段模式：每次从某个序号发号器取一段连续ID，应用层在内存里分配，用完再取。也可以用雪花算法生成64位分布式ID，但要注意时钟回拨问题。
第二座是跨分片查询。分表键之外的条件查询很难直接命中分片。例如按buyer_id分库，商家想看跨所有买家的订单，只能把所有分片结果合并。面对这种需求，我的建议是不要试图在MySQL层解决，而是建立一份同步到统计库或搜索引擎的数据副本，必要时甚至接受分钟级延迟。
第三座是数据迁移。拆分不是建好新表就能切换，线上老库数据要平滑搬到新结构。稳妥的做法是“历史数据分批搬迁 + 增量双写”。新老链路同时写入，并在迁移过程中做行数校验和抽样比对，确认无误后再切读流量。我见过有人直接把数据导出再导入，结果丢了几小时订单，这种教训千万别再犯。
5. 一条慢SQL的完整排查实战
5.1 从慢查询日志抓目标SQL
MySQL本身提供了慢查询日志，最好一开始就在生产环境打开，但参数要收敛。更推荐的做法是：无论默认是否开启，把阈值设到2秒来观察。两步就能搞定：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 2;注意long_query_time的单位是秒，线上如果怕误伤，可以先从5秒甚至10秒开始，慢慢往下调。慢查询日志会持续记录执行时间超过阈值的SQL。找到目标后先看SQL文本，简单分析字段和表，不要急着改。
我更推荐使用pt-query-digest这类分析工具，它可以按指纹把慢SQL分组，统计出哪几条语句累计耗时最久。一条SQL出现100次，每次2秒，比一条出现1次但耗时100秒更值得先治理。
5.2 用EXPLAIN和性能分析定位瓶颈
拿到慢SQL，接下来就是套用前面讲的方法。假设我从日志里抓到了这样一条：
SELECT order_id, amount FROM orders WHERE buyer_name = '张伟' ORDER BY create_time DESC LIMIT 20;执行EXPLAIN后看到：
type = ALL key = NULL rows = 850000 Extra = Using where; Using filesort一眼就能判断：850万行全表扫，还发生了文件排序。buyer_name可能没有索引，或者有索引但因某种写法导致失效。这时再用一次SHOW PROFILE看阶段耗时：
SET profiling = 1; -- 再次执行慢SQL SHOW PROFILE FOR QUERY 1;如果Sending data耗时最长，基本可以确认是IO读行；如果Sorting result耗时最长，问题就在排序。这个信息能帮你决定，是先加索引减少扫描行数，还是调整SQL避免排序。
5.3 索引上线与效果验证
确定要加索引后，不要随手就在生产环境ALTER TABLE。在线DDL虽然在InnoDB里不阻塞读写，但大表仍然可能产生较大压力。更稳妥的做法是使用在线表结构变更工具，把添加索引过程做得更平滑。
上线后重新执行EXPLAIN，再看type、rows和Extra，你会看到全表扫描变成了ref，排序也通过索引完成，不再出现Using filesort。执行时间从几秒降到几十毫秒，这才算数。
还有一个小提醒：索引添加后，旧的慢SQL可能还在缓存中，验证时要刷新或强制走新索引。新增索引本身不复杂，真正的风险是在线变更期间对生产资源的影响，提前在预发环境压测过再执行比较好。
6. 避坑清单与个人经验记录
6.1 典型问题速查表
把记忆中多次被人问到的问题整理成一张表，方便直接定位。
| 现象 | 可能原因 | 处理建议 |
|---|---|---|
| 明明建了索引却不生效 | 对索引列用了函数或隐式类型转换 | 改写SQL条件，保持列原始类型 |
| 分页页数越深越慢 | 深分页导致扫描大量行 | 延迟关联或用游标传入上一页ID |
| 联合索引查询还是慢 | 最左前缀不满足 | 重排列索引字段顺序 |
| 锁等待严重 | 事务过度或索引缺失导致范围锁扩大 | 缩短事务，优化索引减少锁范围 |
| 拆分后查询超时 | 查询条件不带分表键 | 增加路由能力或引入外部索引副本 |
| 写入速度越来越慢 | 表上索引太多 | 精简冗余索引，评估覆盖索引必要性 |
表格只能帮你快速缩小范围，真正解决还是要落到具体SQL和表结构上。
6.2 我踩过的三个坑
第一个坑是在低区分度字段上建索引。某次给一个状态字段加索引，EXPLAIN看起来走了索引，但rows还是非常大。优化器觉得选择性不够，经常走全表扫。后来把索引改成联合索引，把高频等值条件的另一个字段放前面，效果才出来。
第二个坑是直接在线上大表ALTER TABLE加索引。操作倒是没锁表，但执行期间IO升高，影响了同在实例上的其他业务。从那以后我养成习惯：大表结构变更必须用限流工具或者在线变更工具，选在业务低峰期执行。
第三个坑是分库分表之后，业务查询条件不带分表键。当时只想到“用户查自己的订单”这个路径，忽略了后台客服需要“按手机号查订单”的场景。结果客服页面一打开，全分片扫描，比不分库还慢。最后是通过定期同步一份订单索引到独立查询库，才算把这个问题压下去。这个教训告诉我：分库分表前必须把业务的所有查询场景盘清楚，尤其是那些低频但影响业务运营的查询。
索引、SQL、分库分表这三件事，是一套层层递进的体系。如果你能把前两层做好，绝大多数系统根本不需要走到第三步。就算真的需要拆分，前面积累的SQL排查能力和索引设计经验也会让你少踩很多坑。我个人始终建议：每做一次优化，都要保留优化前后的EXPLAIN结果和执行时间数字，这是最有说服力的交付物。
