1. 为什么慢查询迟迟不改善：先看清执行计划
接手这套订单系统的时候，线上已经积累了几十条慢查询记录，每天早上九点高峰期一到，数据库CPU直接飙到80%以上，首页订单列表接口的响应时间从原来的300毫秒一路涨到了4秒多。几个人连续加了一周索引，有些SQL确实变快了一点，但更多只是心理安慰。后来我花了一整天把慢查询日志里的每一条都过了一遍，才发现一个扎心的事实：我们都在“盲调”，大部分人根本不知道这条SQL到底慢在哪里。
很多开发者对优化的理解就是“加索引”，但索引加在哪个字段、用什么样的索引结构、查询计划是不是真的走了索引，这些才是决定性的。我见过有人给一张千万级订单表连续加了七八个单列索引，结果大部分都没被用上，反而拖慢了写入速度。所以第一步永远不是改SQL，而是让数据库告诉我们它是怎么执行这条SQL的。
1.1 EXPLAIN 不是看了就完事，关键字段要逐行解读
MySQL里查看执行计划最常用的命令就是EXPLAIN，输出结果里每一列都有含义，但大多数人只看type和key就下结论。实际上哪怕key显示走了某个索引，也不代表这条SQL就高效，因为type字段里还有一个容易忽略的级别——range和ref的差距非常大，更别说index和all。
举一个我实际碰到过的例子，某条统计SQL查的是用户一年的下单金额，EXPLAIN之后显示type为index，key是idx_user_id，当时同事说“走了索引，应该没事”。可这条SQL实际跑了8秒。原因在于idx_user_id虽然是索引，但查询条件是group by user_id，优化器选择对整个索引做扫描，type为index意味着它遍历了索引的全部叶子节点，这和全表扫描本质上没多大区别，只是扫描的是索引文件而不是数据文件。如果把条件改成where user_id = 某个具体值，type才会变成ref，查询时间才会真正降下来。
所以看执行计划时要养成的习惯是：先看type，再看rows，最后才是key。type的优先级从好到差大致是system > const > eq_ref > ref > range > index > all。如果看到index和all，基本就是全量遍历，必须想办法把过滤条件加进去。rows是估算的扫描行数，如果预估行数和实际返回行数差距巨大，还需要考虑统计信息是否过期，必要时用ANALYZE TABLE刷新统计信息。
1.2 字段类型与索引失效的常见组合
索引失效是日常优化中踩得最多的坑，而且常常是“悄悄失效”，EXPLAIN里type显示all，但代码里明明能看到索引字段。我整理过几个高频失效场景，基本覆盖了90%的问题。
第一个坑是隐式类型转换。订单表的order_no字段是varchar类型，但代码里有大量where order_no = 123456这样的写法，MySQL会在比较时自动把varchar转成数字，一旦索引列被函数或类型转换包裹，B+树就无法按序定位了。修复方式很简单：把所有查询参数都按字符串类型传入，order_no = '123456'。更稳妥的是在前端入口就把参数类型校验掉。
第二个坑是函数运算。常见的有where DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-05-01'，这种写法直接把create_time索引废掉了。正确做法是改成范围查询：where create_time >= '2024-05-01 00:00:00' and create_time < '2024-05-02 00:00:00'。范围查询不仅能用上索引，执行计划的rows还会明显减少，性能差异在千万级数据下是几十倍的差别。
第三个坑是like前置模糊匹配。where name like '%关键字%'时，因为通配符在开头，索引无法使用。如果确实需要模糊搜索，业务允许的话可以退化为keyword列或 '关键字%'前缀匹配；如果不行，就要考虑全文索引或者搜索引擎，不要硬扛。
2. 索引设计：从“加索引”到“正确的索引”
大多数慢SQL的根因不是没有索引，而是索引设计不合理。单列索引盲目叠加、联合索引字段顺序颠倒、区分度不够的字段上了索引，这些问题远比“没加索引”更难发现。一个老同事说过一句话我觉得特别在理：索引不是越多越好，而是越贴合查询路径越好。
2.1 索引的底层结构，B+树为什么是默认选择
先补充一个基础认知。MySQL的InnoDB默认使用B+树索引，B+树和普通二叉树最大的区别是它把数据存在叶子节点，并且叶子节点之间用指针串联。这种设计带来的好处非常实际：对于等值查询，从根节点沿着分支往下走，最多三四次I/O就能定位到叶子；对于范围查询，比如查某个时间段的数据，只要找到起点叶子，顺着链表往后扫就行，非常高效。
理解了这个结构，就能解释为什么有些优化手段有效。联合索引的底层同是一个B+树，排序规则是先按第一列排序，第一列相同再看第二列，以此类推。这就是最左前缀原则的由来。如果你希望索引能高效支持where A = 1 and B = 2和where B = 2两种查询，靠一个(A, B)联合索引无法同时满足后者，因为B不是最左列，索引树不会先按B排序。这种情况下，要么再加一个单列索引B，要么让业务只走联合索引支持的前缀路径。
我在某项目里还遇到过一种情况：表里已经有联合索引(user_id, create_time)，但查询条件是where create_time between ... and user_id = ？，因为create_time在最左边所以顺序不对，索引完全用不上。调整字段顺序为(create_time, user_id)后，扫描行数从几十万降到了几百。所以设计联合索引之前，先看查询条件里哪些列是等值、哪些是范围，把等值列放在前面，范围列放后面，这是常规做法。
2.2 联合索引的最左前缀与字段顺序
最左前缀原则听起来简单，但实际设计时很容易被忽略。假设有一张订单表，高频查询条件是where seller_id = ？ and status = ？ order by create_time desc，如果只建了单列索引seller_id，排序阶段一定会产生filesort；如果把索引设计为(seller_id, status, create_time)，那查询的等值匹配、排序两个环节都能被同一棵索引树搞定。
不过索引不是越宽越好，每多一个字段，写入和更新时维护B+树的成本就增加一分。有一个实用的判断方法：把表里所有高频查询条件拉出来，按“等值字段优先、范围字段靠后、排序字段跟随”的规则合并，尽量用1到2个联合索引覆盖80%的查询场景，剩下的边缘查询宁可让它走慢一点的路径，也不要无节制加索引。
2.3 常见索引误用
几种典型的误用方式，我列一个自查清单：
- 对性别、状态这种低区分度字段加单列索引，区分度太低的索引会让优化器觉得“扫描索引还不如直接扫全表”，实际很少走索引。
- 给大文本字段加普通索引，除非配合前缀索引，否则占用空间大，收益却很小。
- 索引列参与了运算，比如where price * 1.1 > 100，索引失效。
- 一张表超过十个索引，写入性能明显下降，但查询提升有限，属于典型的“索引冗余”。
某次我们清理了一张流水表上的冗余索引，原本15个索引删到5个，写入速度提升了近30%，查询性能完全没有退化。这件事给我的启发是：索引维护也应该像代码重构一样定期做，而不是只增不减。
3. 查询写法上的改造实录
索引建对了之后，SQL本身的写法就成了新的瓶颈。同一个业务诉求，不同的写法在性能上可以差出几个数量级。这一节分享几个我在真实系统中改造过的典型场景。
3.1 避免隐式类型转换
我前面提到过隐式类型转换，再补充一个真实案例。某统计报表SQL如下：
select count(*) from payment_record where merchant_no = 2021050001 and pay_time >= '2025-01-01 00:00:00';表面上看pay_time用了索引，但merchant_no是varchar类型，这里用的是数字2021050001，MySQL会把索引列转成数字再比较，结果merchant_no上的索引直接失效。那条SQL跑了两分半，改成字符串写法后只用了1.8秒。排查技巧是：执行EXPLAIN时看到type为all且where里明明有条件时，先怀疑是否类型不匹配。也可以用如下SQL快速查看表结构来核对字段类型：
show create table payment_record;3.2 覆盖索引与回表优化
如果查询需要的所有列都在索引里，就不需要回表查数据行，这叫覆盖索引，速度比普通索引快很多。设计一个场景：订单表有字段id、order_no、user_id、status、create_time、remark，如果建了联合索引(user_id, create_time, status)，那么查询：
select user_id, create_time, status from order_tab where user_id = 100 order by create_time desc;可以直接从索引返回结果，避免回表。反之如果select里带了remark，就必须回表读数据，性能差不少。一个很实用的优化思路是：把高频查询需要的字段“塞”进联合索引，但注意不要为了覆盖索引无脑加字段，尤其避免把大文本字段放进索引，那样索引膨胀会非常厉害。
我还优化过一个分页深翻页的经典场景，原来的SQL是：
select * from order_tab order by create_time desc limit 100000, 20;这个写法在MySQL里等价于先扫描前100020行，然后丢弃前100000行，越往后翻页越慢。我把改造思路改成分两步，先用子查询在索引上定位起点，再回表取完整行：
select * from order_tab where create_time >= ( select create_time from order_tab order by create_time desc limit 1 offset 100000 ) order by create_time desc limit 20;当然，create_time如果存在重复值会有边界问题，生产上更适合用上一页返回的最大创建时间和ID做游标：
select * from order_tab where (create_time, id) < ('2025-04-01 10:00:00', 12345) order by create_time desc, id desc limit 20;这个做法的核心思路是“记住上一页位置，而不是跳过多少行”，深翻页性能稳定在毫秒级。
3.3 OR、IN、EXISTS 的取舍
很多人在OR和IN之间凭感觉选择。实际上如果OR两边是不同的索引字段，旧版MySQL经常无法合并，导致索引失效；IN则可以直接走索引，通常IN比OR表现更好。但IN的列表长度也不是越大越好，我见过有人一次性IN了几万个值，MySQL会把执行计划搞得很复杂，哪怕走索引也会很慢，这种做法建议改为分批处理。
EXISTS和IN的选择则要看表的数据规模和关联关系。小表驱动大表时，EXISTS通常更合适；但如果子查询能先从索引快速收敛到一个很小的集合，IN的优化器处理往往更好。说实话，现在的优化器版本已经越来越智能，纠结这两个关键字的场景很少了，真正值得关注的是子查询里有没有全表扫描。只要子查询能走索引，IN和EXISTS的差距基本可以忽略。
3.4 避免不必要的排序与临时表
GROUP BY和ORDER BY是隐藏的性能杀手，尤其是GROUP BY涉及到去重或排序时。SQL里group by某个非索引列，经常会产生临时表和filesort，数据量大时直接拖垮性能。一个优化案例：原先统计每天订单量用：
select date(create_time) as d, count(*) from order_tab where create_time >= '2025-03-01' group by date(create_time);这里date()函数包裹了索引列，既无法走索引，又需要全量临时表分组。我把业务稍微调整，直接在应用层按天缓存统计数据，每天定时计算写入统计表，查询时直接查统计表，耗时从6秒降到零点几毫秒。这类优化的本质是：把重计算从查询链路里挪走，而不是死磕SQL本身。
4. 表结构设计与查询性能的关系
查询性能不只取决于SQL和索引，表结构设计往往是第一道关卡。字段冗余、类型不合理、数据量无限膨胀，都会让优化做得非常吃力。
4.1 大字段、冗余字段与宽表问题
很多业务为了通用性，喜欢把大量可空字段塞进一张主表，甚至把JSON格式的扩展信息直接放主表。这种宽表在数据量小的时候看不出问题，一旦单表数据过千万，大字段会让索引和临时表的开销急剧上升。一个常见问题是表里存在text类型字段，查询时即使不select这个字段，某些排序或聚合操作也可能需要读取整行到临时表，I/O开销成倍增加。
我的建议是核心业务表尽量保持“瘦”，把低频访问的大字段拆到附属表，用相同的主键关联。比如商品表可以拆成商品主表和商品详情表，详情表只存详细介绍和图片JSON，查询列表页时根本不用触碰详情表，性能提升明显。
4.2 冷热数据分离与归档策略
单表数据量过大时，再好的索引也难以保证查询体验，因为B+树的层数增加，磁盘I/O次数上升。我们当时有一张流水表，一年下来超过8000万行，即使所有查询都走了索引，部分聚合查询依然要扫几百万行。后来做了冷热分离：热数据保留最近3个月，存放在主表，3个月前的数据定时归档到历史表。业务上查询近期数据走主表，历史明细通过独立的入口查询归档表。实施之后主表体积缩小了70%，热数据查询响应时间显著下降。
归档不是简单地把数据搬走，还要保证历史查询的可用性。我的做法是归档后对历史表重建索引，并且只保留必要字段，减少存储占用。某公司那种报表必须查一整年订单的诉求，我建议他们直接用汇总表，按天/按月预聚合，而不是去扫原始明细。
4.3 分区表的分区键选择
如果业务要求必须保留大量原始数据，又不想归档，可以考虑表分区。分区表在MySQL里常见的有RANGE、LIST、HASH几种方式。订单类数据最适合RANGE分区，按create_time分月分区。查询时如果带上分区的过滤条件，优化器可以直接剪枝掉无关分区，只扫描对应月份的数据。
但分区不是万能的，它的前提是查询条件必须包含分区键。如果业务经常用user_id查跨月订单，按时间分区就没什么帮助，反而增加了维护成本。我之前踩过一个坑：给某日志表按月分区时不小心用主键字段参与了分区条件，结果分区键和主键冲突，InnoDB要求分区键必须包含主键列，否则建表直接报错。后来调整了主键设计，把分区键加入联合主键才解决。所以做分区之前，一定要先想清楚最核心的查询路径是什么，按路径选分区键，不要拍脑袋。
5. 真实案例：一条慢查询的完整优化过程
说一个我从定位到改造全程走下来的例子，这条SQL来自某个跨平台系统的日报统计，每天凌晨跑一次，但它不只影响报表，还会抢占数据库资源，导致白天业务高峰出现抖动。
5.1 定位与排查
原始SQL大致是这样的：
select customer_id, sum(order_amount) as total_amount from order_tab where create_time >= date_sub(now(), interval 1 day) and order_status in ('PAID', 'DELIVERED') group by customer_id order by total_amount desc limit 100;第一次EXPLAIN时，type是all，rows估算接近600万，还出现了Using temporary和Using filesort。这个结果已经说明问题：create_time上的索引没有生效，因为date_sub(now(), interval 1 day)这个表达式并不是一个常量，优化器没法用它做范围定位。这一步就是因为对表达式缺乏敏感度，导致最基础的索引都没用上。
我先把日期计算放到变量里，写成明确的范围条件：
set @start_time = date_sub(now(), interval 1 day);然后查询变成：
where create_time >= @start_time and order_status in ('PAID', 'DELIVERED')执行计划的type立刻变成了range，rows降到50万以内，但Using temporary和filesort还在。因为这里group by customer_id，而表上的索引是(create_time)，分组字段不在索引里。
5.2 优化前后对比
想要完全消除临时表和排序，需要让索引顺序同时满足过滤和分组条件。我把联合索引调整为(create_time, customer_id, order_amount)，这样create_time负责范围定位，customer_id负责分组排序，order_amount可以让sum做覆盖索引读取，省掉回表。
改造后这条SQL的执行计划变成了type为range，key是新的联合索引，Extra里只剩Using index条件，Using temporary和Using filesort都消失了。实际跑出来的时间从原来的46秒降到了1.2秒不到，第二天报表任务的时间窗口压缩了95%以上。
这个案例给我最大的启发是：优化慢查询一定要把执行计划里的每一行提示都解决掉，尤其不要看到type是index就以为万事大吉。filesort和temporary table才是拖垮复杂查询的主力。
6. 常见问题与排查技巧实录
最后分享一批排查过程中总结的套路和避坑点，这些内容很零散，但对实际工作非常有用。
6.1 排查时必看的几个指标
遇到慢查询时，我一般按下面顺序排查，避免凭感觉调优：
- 先用慢查询日志定位TOP SQL，确认责任句。
- 执行EXPLAIN，看type、key、rows、Extra四项。
- 如果rows很大，查看where条件里的字段类型和索引匹配情况。
- 看是否有隐式转换或者函数包裹索引列。
- 看Extra里是否有Using filesort、Using temporary，有的话按联合索引规则调整。
- 检查数据量级，如果单表已经几千万行，考虑冷热分离或分区。
这套流程看起来简单，但能拦住八成以上的无效调优。很多人上来就改SQL的写法，结果改了半天发现索引根本没用上，执行计划还是all。
6.2 容易踩的坑
有一个很常见的坑是统计信息过期。MySQL的优化器依赖表统计信息估算rows，如果信息严重过期，优化器可能放弃正确的索引。某次线上一条SQL近期突变成慢查询，执行计划里连主键索引都不走，后来执行了一次ANALYZE TABLE，优化器重新估算后恢复到了正常的执行计划。所以遇到“突然变慢”的查询，先别急着重构SQL，分析一下是不是统计信息老化。
另一个坑是只加索引不验证。加索引之前必须看当前查询的执行计划，加完索引后要再看一次，并对比实际执行时间。我一直保留一套压测环境，所有索引变更都要先在压测库上跑一遍相同数据集，验证没有副作用再同步到生产。
再就是不要忽略局部性原理。索引字段的值如果是递增的，比如自增ID或时间戳，写入会比较集中；但如果索引字段随机性强，B+树的叶子节点会频繁分裂，写入性能受影响。设计时如果有大量写流量，尽量把随机值索引控制在一个合理范围内，避免所有字段都上随机索引。
最后提醒一个容易被忽略的点：分页查询如果和排序字段配合不佳，会导致每次翻页都做一次大排序。还有一种情况是排序字段在两个不同的索引里，优化器只能在内存或磁盘上完成合并排序，这种场景要特别注意order by字段是否和where字段在同一个联合索引中。
写在最后的一个小建议
在我处理过的所有性能问题里，SQL优化其实只占一半，另一半是数据模型和业务交互设计。一个查询能不能写得快，很大程度上在表结构定下来的那一刻就决定了。所以做优化时不要只看SQL语句本身，多往上游看一下：这个字段类型能不能改，这张表是不是可以拆，统计逻辑是不是可以预聚合。把这些点打通，你写出来的SQL天然就是快的。
