作为开发人员你可能有过这样的经历一条SQL把数据库压垮接口从毫秒级响应直接变成几十秒超时线上告警响个不停。我做过几年后端开发和数据库性能优化踩过不少坑今天就把SQL优化里最核心的两块内容——索引策略和查询重写彻底讲透。文章里会包含EXPLAIN怎么看、索引为什么生效又为什么失效、常见慢SQL怎么改写以及大量的实战案例和避坑经验适合被慢查询困扰的开发人员、刚入门想建立优化体系的DBA以及准备面试需要在系统设计里讲清楚SQL优化的同学。1. 内容整体设计与思路拆解1.1 为什么SQL优化要先看执行计划而不是直接改SQL很多人一遇到慢SQL第一反应就是“这个查询太慢了我来改写一下”。这个思路其实顺序有问题。我见过太多人花了一下午把SQL翻来覆去地改结果执行时间一点没变原因就是压根没搞清楚数据库到底是怎么执行这条SQL的。SQL是一种声明式语言你告诉数据库“我要什么数据”但数据库并不一定按照你写的顺序去执行。它内部有一个优化器会根据统计信息、索引情况、表数据量等因素生成一个它认为最优的执行计划。所以同样的SQL在不同数据量、不同索引条件下执行计划可能完全不同。这就是为什么第一步永远是看执行计划。MySQL里用EXPLAINOracle里用EXPLAIN PLAN FORSQL Server里是SET SHOWPLAN_ALL ON。执行计划会告诉你数据库是走索引还是全表扫描、预估扫描多少行、是否需要回表、排序怎么做的、表之间的连接顺序是什么。拿到这些信息你才知道问题出在哪改写才有针对性。我自己的优化流程基本固定先用慢查询日志定位问题SQL然后EXPLAIN分析执行计划找出瓶颈点接着有针对性地设计索引或改写SQL改完再EXPLAIN验证执行计划是否变化最后用真实数据压测对比效果。这套流程看起来朴实无华但能解决90%以上的SQL性能问题。1.2 索引策略和查询重写各自的定位与边界索引策略和查询重写是SQL优化的两条腿但它们的角色完全不同很多人会把它们混为一谈。索引策略解决的是“数据库怎么找数据”的问题。它的核心目标是让数据库通过索引快速定位到目标数据而不是把整张表从头到尾扫一遍。这部分工作通常是在不改SQL语义的前提下通过创建合适的索引、调整索引结构来提升查询效率。它像给书加目录目录建得好翻书找内容就快。查询重写解决的是“SQL表达方式是否高效”的问题。有时候即使有索引但因为SQL写法有问题索引根本用不上这时候就要改写SQL。比如在索引列上做函数运算、隐式类型转换、前导通配符模糊匹配等都会让索引失效。改写SQL不是改变业务逻辑而是换一种等价写法让优化器能走索引。两者的边界在于如果一条慢SQL已经走到全表扫描你先看是索引缺失还是索引失效。索引缺失就建索引索引失效就改SQL。实际工作中我发现很多性能问题需要索引和改写配合解决——遇到一条复杂的慢SQL往往是先改写成更清晰的形式再设计匹配的复合索引两者缺一不可。1.3 慢SQL优化到底在优化什么很多新手容易陷入一个误区觉得优化就是把执行时间降下来。执行时间当然是最直观的指标但不是唯一的指标。我更关注的是三个层面的东西。第一是响应时间这个不用多说。第二是资源消耗包括CPU、IO、内存。有时候一条SQL虽然执行时间不长但它的执行计划导致扫描了大量磁盘页IO开销很高在高并发场景下就会拖垮整个数据库。第三是扫描行数和返回行数的比例这个比例如果严重失衡说明数据库做了大量无效工作。举个简单的例子一条SQL执行需要200毫秒对一个日活不大的系统来说好像还能接受。但如果这条SQL每秒被调用100次那每秒就有20秒的数据库处理时间被它消耗。优化一条高频SQL哪怕只减少50毫秒对系统整体压力的改善都是巨大的。所以定位慢SQL时我一般会关注两个维度单次执行耗时和执行频率。单次耗时高而频率低的可能是凌晨跑批任务、报表统计类查询这类优化往往效果不明显但也不紧急单次耗时中等但频率极高的才是系统性能的隐形杀手优先级最高。2. EXPLAIN详解看懂慢SQL优化的第一步2.1 EXPLAIN核心字段逐个拆解EXPLAIN的输出结果有很多列我刚接触的时候看得一头雾水后来总结出几个关键字段把它们的含义彻底吃透之后基本就能判断一条SQL的问题所在了。下面我用一个实际例子来说明。EXPLAIN SELECT u.name, o.order_no FROM t_user u INNER JOIN t_order o ON u.id o.user_id WHERE u.age 25 AND o.status 1;执行后返回的结果包含id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra这些列。其中最重要的几个type列这是访问类型直接反映了SQL的性能表现。性能排序从好到差依次是system const eq_ref ref range index ALL。system是表中只有一行数据const是主键或唯一索引等值查询eq_ref是联表查询中被驱动表通过主键或唯一索引等值匹配ref是普通索引等值匹配range是索引范围扫描index是遍历整个索引树ALL就是全表扫描。看到ALL基本就要注意了说明这条SQL有优化空间。key列表示实际使用的索引。如果为NULL说明没有使用任何索引这条SQL在硬扫全表。possible_keys列是优化器可以考虑的索引列表key是优化器最终选择的索引两者对比很有价值——如果possible_keys有值但key为NULL说明优化器判断走索引不如全表扫描这种情况常见于数据量小或者索引区分度不够。rows列这是优化器预估的需要扫描的行数。这个值只是个预估值不一定精确但作为参考足够。rows越大说明定位目标数据的成本越高。如果rows跟表的总行数差不多那基本就是全表扫描了。filtered列表示经过WHERE条件过滤后剩余记录占扫描行数的百分比。比如rows10000filtered10意味着最终返回1000行左右。这个值越小说明扫描的行数中浪费的比例越高越需要优化。Extra列包含很多关键信息。看到Using index说明查询所需数据全部在索引中不需要回表这是最理想的情况Using index condition说明使用了索引下推Using where说明存储引擎返回数据后还需要server层进一步过滤Using filesort说明需要额外排序操作Using temporary说明使用了临时表。这些出现时尤其filesort和temporary都意味着SQL有优化的空间。2.2 通过EXPLAIN定位全表扫描和索引失效EXPLAIN最有价值的应用场景是帮我们快速判断一条SQL到底卡在哪里。我总结了几个高频信号。信号一typeALLkeyNULL。这就是全表扫描没有任何可用索引。出现这种情况要么是表的索引设计有问题要么是WHERE条件里的列压根没建索引。处理思路是检查WHERE和JOIN关联字段给合适的列加索引。信号二typeALLkeyNULLExtra里还有Using where。这种情况更尴尬说明扫描了全表每一个记录然后逐行去匹配过滤条件。比如一张千万级的订单表按user_id筛选但没有给user_id建索引数据库就不得不把一千万行全部读出来然后过滤。信号三typeref或range但rows特别大。这种情况有时候容易被忽略因为看起来走了索引。但如果你查的是区分度很低的列比如status字段只有几个枚举值优化器走索引后发现要匹配的仍然有几十万行性能照样很差。这时候单纯的索引解决不了问题需要从查询重写角度去思考。信号四Extra出现Using filesort。SQL里有ORDER BY但排序字段没有索引或者索引顺序不对数据库就得把结果集全部加载到内存里做一次额外排序。数据量大时这非常消耗资源和时间。2.3 一个EXPLAIN实战判断流程我第一次系统梳理EXPLAIN判断流程是在一个用户中心项目里当时有个统计接口经常超时定位到一条SQL之后我建立了如下判断链路。先看type是否为ALL。是看possible_keys是否为空为空说明没有可用索引去检查WHERE和JOIN条件里的列有没有索引有值但最终没走说明索引区分度不够或优化器认为代价更高可以考虑强制索引或优化统计信息。type是range或ref的看rows大小和filtered比例如果rows几十万但filtered很低说明扫描了大量行但返回很少问题可能出在数据分布和索引顺序不匹配上。最后看Extra里是否有Using filesort和Using temporary有则处理排序和分组字段的索引覆盖问题。这套流程走下来基本上每条慢SQL的问题都能定位清楚。这也印证了一句话EXPLAIN是SQL优化的眼睛看不懂执行计划优化就是盲人摸象。3. 索引策略全解析从原理到实战3.1 B树索引到底快在哪里理解索引策略首先要理解索引的底层数据结构。MySQL的InnoDB引擎使用的默认索引结构是B树这是一种多路平衡查找树。B树和普通二叉树的区别在于每个节点可以存储多个子节点引用树的层数因此非常浅。比如一张千万级别的表主键索引的B树高度通常只有3到4层这意味着定位一行数据最多只需要进行三四次磁盘IO。为什么这点很重要因为磁盘IO是数据库性能的命脉。内存里读数据是纳秒级的磁盘上读数据是毫秒级的中间差了几个数量级。B树这种低层高的特性保证了在大数据量下查询的磁盘IO次数仍然可控。另外B树的叶子节点之间是通过指针连接的形成一个有序链表。这意味着范围查询可以顺着链表顺序扫描不需要反复从根节点开始遍历。这就是为什么对索引列做范围条件、、BETWEEN时数据库能够高效处理的原因。还有一个特性是聚簇索引。InnoDB的主键索引就是聚簇索引它的叶子节点直接存了整行数据。而二级索引普通索引的叶子节点存的是主键值。用二级索引查询时需要先在二级索引树里找到主键值再回到主键索引树里查完整行数据这个过程叫回表。理解了这个你就知道为什么我们要追求覆盖索引了。3.2 复合索引设计需要避开的几个雷区单列索引很好理解一个字段建立一个索引。但在真实业务场景里WHERE条件往往涉及多个字段于是就有了复合索引。复合索引的原理和命中规则是SQL优化里大部分人最容易搞混的地方。复合索引的核心规则是最左前缀原则。索引按照定义时的字段顺序构建一个多级排序结构因此查询条件必须从最左字段开始连续匹配索引才会生效。比如有一个复合索引idx_user_age(user_id, age, status)它能命中(user_id)、 (user_id, age)、(user_id, age, status)这几种查询组合但无法命中只查age或者只查status的查询。很多开发同事跟我抱怨“我明明建了复合索引为什么走不了”一问才发现SQL里跳过了最左字段。这就像查字典你只知道某个字有“三点水偏旁”但不知道它的总笔画数就没法快速定位到那一页。最左前缀原则要求你必须从“第一个笔画维度”开始。设计复合索引时我还总结了一条经验等值条件列放前面范围条件列放后面。因为范围条件比如age 25一旦命中后面的索引列就无法用于精确定位了只能用于排序和覆盖。把等值判断的列放在前面可以让索引最大程度地过滤数据。另一个常见错误是把区分度最高的字段放在第一位。这条原则在多数情况下是对的但有一种例外——如果查询中某个等值条件经常出现即使区分度不高也应该放在前面因为等值条件能精确定位而范围条件会打断索引的连续性。3.3 覆盖索引让SQL起飞的回表消除方案前面讲了回表的概念用二级索引找到主键再回到主键索引查整行。回表本身多一次IO数据量大时性能损耗很明显。如果查询需要的数据全部包含在二级索引的字段里数据库就不用回表了这种索引叫覆盖索引。我用一个真实优化案例来说明。有一个订单导出功能SQL大概是这样的SELECT id, order_no, create_time FROM t_order WHERE create_time 2024-01-01 ORDER BY create_time LIMIT 1000;原本表里有订单表主键索引因为create_time上有索引查询能走索引拿到id和create_time但order_no字段不在索引里所以每条记录都要回表拿order_no。在数据量大的场景下回表一千次性能就很差了。优化方式是把订单号和创建时间一起放进复合索引里ALTER TABLE t_order ADD INDEX idx_create_time_order_no(create_time, order_no);索引里包含create_time和order_no之后SEEK和扫描过程中就可以直接从索引取到全部需要的数据Extra列会显示Using index。这种优化效果非常直观尤其是统计类、列表导出类的查询收益巨大。需要注意的一点是覆盖索引不能滥用。每多一个索引写入数据时就要多做一次索引更新会拖慢INSERT、UPDATE和DELETE。对于写多读少的表加覆盖索引要谨慎衡量。3.4 索引失效的8个高频场景索引建了SQL也看着正常但执行计划就是不走索引。这种问题我在排查中遇到过太多次整理一下高频场景。第一对索引列使用函数。比如WHERE DATE(create_time) 2024-01-01为了让索引生效应该改写为create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。函数操作让优化器无法使用正常索引顺序。第二对索引列做强转等隐式类型转换。比如phone字段是varchar但查询时WHERE phone 13812345678数字会被转成字符串做比较就会使索引失效。第三模糊匹配时前导通配符。LIKE %keyword无法走索引但LIKE keyword%可以走。第四索引列参与计算。WHERE age 1 30要改写为WHERE age 29。第五使用OR连接非索引列。如果OR两边有一个条件不能用索引整个查询就可能走全表扫描可以用UNION ALL拆分。第六负向查询。WHERE status ! 1、WHERE status NOT IN (1, 2)、WHERE name IS NOT NULL这些负向条件很难走索引除非数据分布极其倾斜否则优化器通常会选择全表扫描。第七复合索引违反最左前缀原则这个前面详细说过。第八数据量太小和统计信息不准确。表里只有几百行数据优化器觉得全表扫描更快自然不走索引这其实是合理的。我在审查代码时看到这些写法基本一眼就能判断有没有问题。更重要的是除了知道这些场景还要明白失效背后的逻辑——索引是按有序排列存储的一旦对列做了加工处理原有的顺序就被破坏了数据库自然无法利用索引的有序性来加速查找。3.5 联合索引与排序ORDER BY的索引优化很多人忽略了索引对排序的加速作用。其实B树本身就按顺序存储如果ORDER BY的字段正好是索引前缀数据库就可以直接利用索引的有序性省掉filesort。比如有一个复合索引idx(user_id, create_time)那么下面的查询就可以避免排序SELECT * FROM t_order WHERE user_id 1001 ORDER BY create_time LIMIT 10;因为先按user_id定位到具体分支在这个分支里create_time已经天然有序。但如果改为ORDER BY create_time DESC且user_id是范围条件情况就不一样了。比如WHERE user_id 1000 ORDER BY create_time这时候user_id范围条件下create_time在整体上不是有序的数据库还是需要额外排序。另一个典型坑是ORDER BY字段顺序与索引定义顺序不一致。索引是(user_id, create_time)但排序是ORDER BY create_time, user_id由于字段顺序不匹配索引无法直接用于排序。所以设计复合索引时不仅要考虑WHERE条件还要把ORDER BY和GROUP BY的字段一起考虑进去尽量让一个索引满足筛选和排序双重需求。4. 查询重写实战从慢SQL到秒级响应4.1 用UNION ALL替换OR的实战收益OR导致的索引失效前面提到过。具体来说WHERE status 1 OR create_time 2024-01-01这种条件如果status和create_time分别有单列索引优化器理论上可以走索引合并但很多情况下走的是全表扫描。而且OR连接的子条件如果都走索引可能还要做索引合并索引合并在某些场景下代价也不低。我习惯的改写方式是把OR拆成两个独立的查询再用UNION ALL合并。比如SELECT * FROM t_order WHERE user_id 1001 UNION ALL SELECT * FROM t_order WHERE coupon_id 888;这里有个关键点一定要用UNION ALL而不是UNION。UNION会对结果集做去重这需要额外的排序和临时表操作在数据量大时非常消耗性能。只有两个子查询可能产生重复行时才需要UNION业务上能保证不重复的都用UNION ALL。实测过一个案例原本一条OR查询要跑4秒多改写成UNION ALL之后两个子查询各自走索引总耗时降到了300毫秒以内。这是因为每条子查询的过滤条件都能独立高效地定位避免了多个条件叠加导致优化器放弃索引。4.2 NOT IN和NOT EXISTS的改写技巧NOT IN和NOT EXISTS在语义上很接近但性能差别可能很大。核心问题在于NOT IN子查询在某些数据库版本和场景下会被优化成低效的执行计划。比如这条SQLSELECT id FROM t_user WHERE id NOT IN (SELECT user_id FROM t_order WHERE status 1);如果子查询返回的结果集很大NOT IN的语义要求检查每一个外层记录的id是否都不在子查询结果里数据库可能采用一种称为anti join的方式进行但有时会退化成低效的逐行子查询执行。一个常见的改写方案是改成LEFT JOIN加IS NULL判断SELECT u.id FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status 1 WHERE o.user_id IS NULL;这种写法把“不存在于”的语义转换为“左连接后右表为空”的语义优化器对这种join方式有丰富的优化策略通常能获得更好的执行计划。注意JOIN条件里一定要把status 1放进ON子句而不是WHERE子句否则会把LEFT JOIN结果过滤成内连接完全改变语义查询结果就错了。还有一个基础前提子查询和驱动表的关联字段都要有索引。用上面的例子t_order.user_id必须有索引否则LEFT JOIN会走全表扫描结果比NOT IN还慢。4.3 分页查询深翻页的优化方案分页慢是很多业务系统都会遇到的问题。普通LIMIT分页在页码小的时候没问题但翻到10000页时SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100000, 20;这个查询会扫描出前100020条记录然后丢掉前100000条只返回20条扫描的行数随着页码增加而线性增长。深翻页优化的经典方案有两种。第一种是延迟关联。先用索引快速定位到需要的主键范围然后再回表取完整数据SELECT o.* FROM t_order o INNER JOIN ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id;这个方案的思路是让内层查询只扫描索引覆盖索引不包含完整列确定需要返回的20个主键后再关联取整行数据大大减少了无效回表。第二种是基于游标的分页。记录上一次查询返回的最后一条记录的create_time和id下一页就直接用这两个条件往后取SELECT * FROM t_order WHERE create_time 2024-01-01 12:00:00 OR (create_time 2024-01-01 12:00:00 AND id 888) ORDER BY create_time DESC LIMIT 20;游标分页的好处是每一步都走索引范围扫描不管翻多深性能都很稳定。代价是用户不能随意跳页。实际业务中绝大多数场景用户都是顺序翻页所以游标分页的适用面非常广。4.4 COUNT查询的合理优化COUNT()在数据量上来之后会变得很慢尤其InnoDB引擎下不支持类似MyISAM的计数器缓存必须扫描所有行才能统计。我见过有人把一张千万级表的COUNT()查询直接暴露在接口里每次统计都要跑好几秒非常伤。首先要明确业务场景。如果是后台看板上需要实时精确的COUNT建议改用缓存方案在业务代码里维护计数器或者用独立的统计表。如果是列表分页需要总条数可以考虑近似值方案直接用EXPLAIN的rows预估值代替精确值很多列表场景对总数精确要求并不高。有些COUNT场景可以通过改写来优化。比如统计一个大表里符合条件的数据量原来的SQL是COUNT()加复杂条件如果条件里的列可以通过覆盖索引命中优化器就不需要回表扫描索引树即可。另外COUNT(1)和COUNT()在MySQL里没有性能差别不需要纠结这个。4.5 关联查询的优化小表驱动大表联表查询的性能问题很大程度取决于驱动表的选择。驱动表就是查询中先被访问的表然后拿驱动表的结果集去匹配另一张表。优化器通常会选择小表作为驱动表因为小表的行数少需要执行的关联次数就少。碰到复杂的多表关联时我会手动确认一下执行计划里的驱动表是否合理。如果发现驱动表不是小表可以调整SQL结构来影响优化器的选择比如使用STRAIGHT_JOIN强制指定连接顺序。不过这种做法要非常谨慎因为强制指定的顺序一旦遇到数据分布变化可能反而变差。更重要的一点是被驱动表的关联字段必须有索引。否则每拿驱动表的一行去匹配数据就要对被驱动表做一次全表扫描那代价是灾难级的。这对应了EXPLAIN里的eq_ref和ref类型用主键或唯一索引关联时走eq_ref效率最好。5. 实战案例一次慢SQL优化的完整过程5.1 问题SQL与初始EXPLAIN分析一个真实项目的案例。某订单系统的批量查询接口在高峰期频繁超时定位到一条SQLSELECT o.order_no, o.amount, u.mobile, u.nickname FROM t_order o LEFT JOIN t_user u ON o.user_id u.id WHERE o.create_time 2024-06-01 AND o.create_time 2024-07-01 AND o.status 1 ORDER BY o.create_time DESC LIMIT 200;t_order表有3000万行t_user表有500万行。初始EXPLAIN的结果是t_order表typeALLrows预估2900万Extra里还有Using where和Using filesortt_user表typeeq_refrows1。显然瓶颈在t_order表的全表扫描。5.2 优化过程与每一步的调整思路第一步给t_order表添加复合索引。WHERE条件里create_time是范围条件status是等值条件ORDER BY也用到了create_time。按照等值条件放前面的原则我把status放在前面create_time放在后面ALTER TABLE t_order ADD INDEX idx_status_create_time(status, create_time);加完索引再EXPLAINt_order表的type变成了rangerows降到了80万左右。但80万依然很大而且Extra仍然有Using filesort。第二步分析为什么还要filesort。索引顺序是(status, create_time)但查询里status 1是等值条件所以在这个索引分支下create_time确实是有序的ORDER BY create_time应该能直接用索引顺序。为什么还有filesort因为我SELECT了order_no、amount这些字段索引里没有需要回表取数据。回表之后的数据顺序不是create_time的顺序所以最终排序还是需要在内存里做一遍。这时候有两个思路一是把排序字段和查询字段都塞进索引做成覆盖索引二是想办法减少回表的数据量。第三步权衡后我决定做一个覆盖索引。因为接口的核心查询固定同时需要返回order_no和amount。我把索引扩展成ALTER TABLE t_order ADD INDEX idx_status_create_time_cover(status, create_time, order_no, amount);但这里有个问题业务表还有一个需求是按user_id查询订单列表不同查询场景对索引的需求不同一个覆盖索引并不能满足所有场景。所以这个索引是为这条高频SQL定制的同时保留了idx_status_create_time作为通用索引。第四步再把联表查询的驱动顺序确认一下。这个SQL的LEFT JOIN里t_order是驱动表t_user是被驱动表t_user表通过主键id关联走了eq_ref这部分没有问题。加上覆盖索引之后驱动表在索引上获取了所有需要的字段连回表都省了。EXPLAIN里Extra出现了Using index conditionfilesort也消失了rows降到几千行。5.3 优化前后的性能对比优化前这条SQL在测试环境的数据量下执行了约4.2秒高峰期线上要跑到10秒以上已经触发了慢查询告警。优化后同样的数据量下执行时间降到了180毫秒左右提升了超过20倍。更重要的是资源消耗的变化。优化前全表扫描需要读取将近3000万的记录磁盘IO和内存占用都非常夸张。优化后通过索引定位到约3万条记录再通过覆盖索引取列磁盘读取量几乎可以忽略不计。在高并发调用下这个提升对数据库整体负载的影响极其明显。每次查这个案例我都有几个体会。第一索引设计一定要针对实际SQL而不是对着表结构凭空想。把表里的所有SQL收集起来按WHERE、ORDER BY、GROUP BY、JOIN字段做分类再设计匹配的复合索引效率远高于凭感觉建索引。第二覆盖索引是应对大查询的杀器但要根据场景取舍不能把所有表的查询都指望一个覆盖索引解决。第三优化完必须用真实数据验证不能只看EXPLAIN结果测试环境和线上数据分布差异导致的执行计划偏差很常见。6. 常见问题与排查技巧实录6.1 为什么加了索引却不生效这个问题我几乎每周都会遇到一次。加了索引但不生效高频原因无非这几类索引列上做了函数或计算操作字符串列查询时没加引号导致隐式类型转换复合索引违反最左前缀原则使用了LIKE前导通配符OR条件里混入了非索引列区分度太低的列即使有索引优化器也会放弃走索引因为全表扫描的代价可能更低。排查方法就是把EXPLAIN打开一条条对比。如果possible_keys有值但key是NULL说明优化器权衡后决定不走索引可能是区分度问题。如果possible_keys本身就是NULL说明根本没有可用索引检查索引定义和SQL条件是否匹配。有一种情况容易被忽略就是统计信息太旧。MySQL的优化器依赖表统计信息来估算行数如果统计信息长时间没有更新优化器可能做出错误判断。执行ANALYZE TABLE刷新统计信息后有时会发现执行计划恢复正常。6.2 查看慢查询日志和定位问题SQL很多中小团队没有接入专业的数据库监控平台这时候使用慢查询日志是最直接的定位手段。MySQL里通过参数设置开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time设置为1秒意味着执行超过1秒的SQL都会被记录。还有一个容易被忽略的配置log_queries_not_using_indexes开启后会把没有走索引的SQL也记录到慢查询日志里对于发现隐藏的全表扫描问题很有帮助。拿到慢查询日志后我可以借助mysqldumpslow或pt-query-digest这类工具做聚合分析把同类SQL汇总按执行次数、总耗时排序。很多问题SQL不是单次慢而是被频繁调用累积出来的这类SQL必须按照执行频次加单次耗时的综合排序来决定优化优先级。6.3 SQL优化中不容忽视的隐式类型转换问题隐式类型转换是我见过最隐蔽的索引失效原因之一。表中user_id字段是varchar(32)SQL写成WHERE user_id 12345MySQL会在比较时把字符串转成数字一旦发生转换索引列上就相当于加了CAST函数索引自然失效。排查方式很直接——看到执行计划里key为NULL先检查所有比较条件里字段的类型和值类型是否完全一致。我用一个习惯写SQL时对字符串字段严格要求加引号哪怕是数字字符串也一律写成12345这种形式。这不仅是规范问题更是性能问题。还有一种发生在联表场景。两张表关联字段分别是int和varchar字段值相同但类型不一致JOIN时也会发生隐式转换导致索引失效。设计表结构时就把关联字段的类型统一能在源头上规避这个问题。6.4 怎么用profiling精细定位SQL耗时分布EXPLAIN能告诉我们执行计划长什么样但无法告诉我们SQL执行过程中每个阶段的真实耗时。如果想精细定位瓶颈可以用MySQL的profiling功能。SET profiling 1; -- 执行慢SQL SHOW PROFILES; -- 查看详细耗时 SHOW PROFILE FOR QUERY 1;输出的结果会列出SQL执行过程中的各个阶段耗时包括Sending data、Sorting result、Creating sort index等。我在一次奇怪的慢查询排查中发现SQL本身逻辑很简单索引也走了但总执行时间就是居高不下。用profiling一看发现大量时间花在Sending data阶段进一步排查发现是网络传输问题加上返回了大量LOB字段数据纯查索引解决不了这个问题最后通过减少返回字段和增加网络带宽解决了。这个例子说明一个道理SQL优化是系统性的不能只看执行计划这一个维度。返回数据量、网络环境、客户端处理逻辑都有可能成为瓶颈。7. 优化思路的沉淀与总结做了这么多年SQL优化我发现真正的优化高手和普通开发之间最大的差别不是背了多少优化技巧而是有没有一套清晰的排查思路和沉淀下来的习惯。我个人习惯在项目里做三件事。第一件建立慢SQL台账。每次优化过的慢SQL把SQL原文、EXPLAIN结果、问题原因、优化方案、优化前后耗时对比都记录下来。下次看到类似的SQL直接翻台账就能找到参考方案效率高很多。第二件把索引设计纳入代码评审。每次新上线一张表或者一条新查询都要求设计人员把EXPLAIN结果贴到评审文档里把索引问题扼杀在上线之前。第三件定期巡检数据库。每周把慢查询日志拿出来过一遍看看有没有新出现的性能隐患。SQL优化不是一次性的工作数据量在增长业务逻辑在变复杂今天好用的执行计划明天可能就失效了。但只要掌握方法紧跟执行计划的变化保持对慢SQL的高敏感度性能问题就不会成为系统的瓶颈。希望这篇文章里的索引策略和查询重写方法能帮你在实际项目中少走一些弯路多省一些时间。 
长河君 · 2026/9/13 18:49:35 · 阅读 1.2k SQL优化实战：索引策略与查询重写从原理到案例
作为开发人员你可能有过这样的经历一条SQL把数据库压垮接口从毫秒级响应直接变成几十秒超时线上告警响个不停。我做过几年后端开发和数据库性能优化踩过不少坑今天就把SQL优化里最核心的两块内容——索引策略和查询重写彻底讲透。文章里会包含EXPLAIN怎么看、索引为什么生效又为什么失效、常见慢SQL怎么改写以及大量的实战案例和避坑经验适合被慢查询困扰的开发人员、刚入门想建立优化体系的DBA以及准备面试需要在系统设计里讲清楚SQL优化的同学。1. 内容整体设计与思路拆解1.1 为什么SQL优化要先看执行计划而不是直接改SQL很多人一遇到慢SQL第一反应就是“这个查询太慢了我来改写一下”。这个思路其实顺序有问题。我见过太多人花了一下午把SQL翻来覆去地改结果执行时间一点没变原因就是压根没搞清楚数据库到底是怎么执行这条SQL的。SQL是一种声明式语言你告诉数据库“我要什么数据”但数据库并不一定按照你写的顺序去执行。它内部有一个优化器会根据统计信息、索引情况、表数据量等因素生成一个它认为最优的执行计划。所以同样的SQL在不同数据量、不同索引条件下执行计划可能完全不同。这就是为什么第一步永远是看执行计划。MySQL里用EXPLAINOracle里用EXPLAIN PLAN FORSQL Server里是SET SHOWPLAN_ALL ON。执行计划会告诉你数据库是走索引还是全表扫描、预估扫描多少行、是否需要回表、排序怎么做的、表之间的连接顺序是什么。拿到这些信息你才知道问题出在哪改写才有针对性。我自己的优化流程基本固定先用慢查询日志定位问题SQL然后EXPLAIN分析执行计划找出瓶颈点接着有针对性地设计索引或改写SQL改完再EXPLAIN验证执行计划是否变化最后用真实数据压测对比效果。这套流程看起来朴实无华但能解决90%以上的SQL性能问题。1.2 索引策略和查询重写各自的定位与边界索引策略和查询重写是SQL优化的两条腿但它们的角色完全不同很多人会把它们混为一谈。索引策略解决的是“数据库怎么找数据”的问题。它的核心目标是让数据库通过索引快速定位到目标数据而不是把整张表从头到尾扫一遍。这部分工作通常是在不改SQL语义的前提下通过创建合适的索引、调整索引结构来提升查询效率。它像给书加目录目录建得好翻书找内容就快。查询重写解决的是“SQL表达方式是否高效”的问题。有时候即使有索引但因为SQL写法有问题索引根本用不上这时候就要改写SQL。比如在索引列上做函数运算、隐式类型转换、前导通配符模糊匹配等都会让索引失效。改写SQL不是改变业务逻辑而是换一种等价写法让优化器能走索引。两者的边界在于如果一条慢SQL已经走到全表扫描你先看是索引缺失还是索引失效。索引缺失就建索引索引失效就改SQL。实际工作中我发现很多性能问题需要索引和改写配合解决——遇到一条复杂的慢SQL往往是先改写成更清晰的形式再设计匹配的复合索引两者缺一不可。1.3 慢SQL优化到底在优化什么很多新手容易陷入一个误区觉得优化就是把执行时间降下来。执行时间当然是最直观的指标但不是唯一的指标。我更关注的是三个层面的东西。第一是响应时间这个不用多说。第二是资源消耗包括CPU、IO、内存。有时候一条SQL虽然执行时间不长但它的执行计划导致扫描了大量磁盘页IO开销很高在高并发场景下就会拖垮整个数据库。第三是扫描行数和返回行数的比例这个比例如果严重失衡说明数据库做了大量无效工作。举个简单的例子一条SQL执行需要200毫秒对一个日活不大的系统来说好像还能接受。但如果这条SQL每秒被调用100次那每秒就有20秒的数据库处理时间被它消耗。优化一条高频SQL哪怕只减少50毫秒对系统整体压力的改善都是巨大的。所以定位慢SQL时我一般会关注两个维度单次执行耗时和执行频率。单次耗时高而频率低的可能是凌晨跑批任务、报表统计类查询这类优化往往效果不明显但也不紧急单次耗时中等但频率极高的才是系统性能的隐形杀手优先级最高。2. EXPLAIN详解看懂慢SQL优化的第一步2.1 EXPLAIN核心字段逐个拆解EXPLAIN的输出结果有很多列我刚接触的时候看得一头雾水后来总结出几个关键字段把它们的含义彻底吃透之后基本就能判断一条SQL的问题所在了。下面我用一个实际例子来说明。EXPLAIN SELECT u.name, o.order_no FROM t_user u INNER JOIN t_order o ON u.id o.user_id WHERE u.age 25 AND o.status 1;执行后返回的结果包含id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra这些列。其中最重要的几个type列这是访问类型直接反映了SQL的性能表现。性能排序从好到差依次是system const eq_ref ref range index ALL。system是表中只有一行数据const是主键或唯一索引等值查询eq_ref是联表查询中被驱动表通过主键或唯一索引等值匹配ref是普通索引等值匹配range是索引范围扫描index是遍历整个索引树ALL就是全表扫描。看到ALL基本就要注意了说明这条SQL有优化空间。key列表示实际使用的索引。如果为NULL说明没有使用任何索引这条SQL在硬扫全表。possible_keys列是优化器可以考虑的索引列表key是优化器最终选择的索引两者对比很有价值——如果possible_keys有值但key为NULL说明优化器判断走索引不如全表扫描这种情况常见于数据量小或者索引区分度不够。rows列这是优化器预估的需要扫描的行数。这个值只是个预估值不一定精确但作为参考足够。rows越大说明定位目标数据的成本越高。如果rows跟表的总行数差不多那基本就是全表扫描了。filtered列表示经过WHERE条件过滤后剩余记录占扫描行数的百分比。比如rows10000filtered10意味着最终返回1000行左右。这个值越小说明扫描的行数中浪费的比例越高越需要优化。Extra列包含很多关键信息。看到Using index说明查询所需数据全部在索引中不需要回表这是最理想的情况Using index condition说明使用了索引下推Using where说明存储引擎返回数据后还需要server层进一步过滤Using filesort说明需要额外排序操作Using temporary说明使用了临时表。这些出现时尤其filesort和temporary都意味着SQL有优化的空间。2.2 通过EXPLAIN定位全表扫描和索引失效EXPLAIN最有价值的应用场景是帮我们快速判断一条SQL到底卡在哪里。我总结了几个高频信号。信号一typeALLkeyNULL。这就是全表扫描没有任何可用索引。出现这种情况要么是表的索引设计有问题要么是WHERE条件里的列压根没建索引。处理思路是检查WHERE和JOIN关联字段给合适的列加索引。信号二typeALLkeyNULLExtra里还有Using where。这种情况更尴尬说明扫描了全表每一个记录然后逐行去匹配过滤条件。比如一张千万级的订单表按user_id筛选但没有给user_id建索引数据库就不得不把一千万行全部读出来然后过滤。信号三typeref或range但rows特别大。这种情况有时候容易被忽略因为看起来走了索引。但如果你查的是区分度很低的列比如status字段只有几个枚举值优化器走索引后发现要匹配的仍然有几十万行性能照样很差。这时候单纯的索引解决不了问题需要从查询重写角度去思考。信号四Extra出现Using filesort。SQL里有ORDER BY但排序字段没有索引或者索引顺序不对数据库就得把结果集全部加载到内存里做一次额外排序。数据量大时这非常消耗资源和时间。2.3 一个EXPLAIN实战判断流程我第一次系统梳理EXPLAIN判断流程是在一个用户中心项目里当时有个统计接口经常超时定位到一条SQL之后我建立了如下判断链路。先看type是否为ALL。是看possible_keys是否为空为空说明没有可用索引去检查WHERE和JOIN条件里的列有没有索引有值但最终没走说明索引区分度不够或优化器认为代价更高可以考虑强制索引或优化统计信息。type是range或ref的看rows大小和filtered比例如果rows几十万但filtered很低说明扫描了大量行但返回很少问题可能出在数据分布和索引顺序不匹配上。最后看Extra里是否有Using filesort和Using temporary有则处理排序和分组字段的索引覆盖问题。这套流程走下来基本上每条慢SQL的问题都能定位清楚。这也印证了一句话EXPLAIN是SQL优化的眼睛看不懂执行计划优化就是盲人摸象。3. 索引策略全解析从原理到实战3.1 B树索引到底快在哪里理解索引策略首先要理解索引的底层数据结构。MySQL的InnoDB引擎使用的默认索引结构是B树这是一种多路平衡查找树。B树和普通二叉树的区别在于每个节点可以存储多个子节点引用树的层数因此非常浅。比如一张千万级别的表主键索引的B树高度通常只有3到4层这意味着定位一行数据最多只需要进行三四次磁盘IO。为什么这点很重要因为磁盘IO是数据库性能的命脉。内存里读数据是纳秒级的磁盘上读数据是毫秒级的中间差了几个数量级。B树这种低层高的特性保证了在大数据量下查询的磁盘IO次数仍然可控。另外B树的叶子节点之间是通过指针连接的形成一个有序链表。这意味着范围查询可以顺着链表顺序扫描不需要反复从根节点开始遍历。这就是为什么对索引列做范围条件、、BETWEEN时数据库能够高效处理的原因。还有一个特性是聚簇索引。InnoDB的主键索引就是聚簇索引它的叶子节点直接存了整行数据。而二级索引普通索引的叶子节点存的是主键值。用二级索引查询时需要先在二级索引树里找到主键值再回到主键索引树里查完整行数据这个过程叫回表。理解了这个你就知道为什么我们要追求覆盖索引了。3.2 复合索引设计需要避开的几个雷区单列索引很好理解一个字段建立一个索引。但在真实业务场景里WHERE条件往往涉及多个字段于是就有了复合索引。复合索引的原理和命中规则是SQL优化里大部分人最容易搞混的地方。复合索引的核心规则是最左前缀原则。索引按照定义时的字段顺序构建一个多级排序结构因此查询条件必须从最左字段开始连续匹配索引才会生效。比如有一个复合索引idx_user_age(user_id, age, status)它能命中(user_id)、 (user_id, age)、(user_id, age, status)这几种查询组合但无法命中只查age或者只查status的查询。很多开发同事跟我抱怨“我明明建了复合索引为什么走不了”一问才发现SQL里跳过了最左字段。这就像查字典你只知道某个字有“三点水偏旁”但不知道它的总笔画数就没法快速定位到那一页。最左前缀原则要求你必须从“第一个笔画维度”开始。设计复合索引时我还总结了一条经验等值条件列放前面范围条件列放后面。因为范围条件比如age 25一旦命中后面的索引列就无法用于精确定位了只能用于排序和覆盖。把等值判断的列放在前面可以让索引最大程度地过滤数据。另一个常见错误是把区分度最高的字段放在第一位。这条原则在多数情况下是对的但有一种例外——如果查询中某个等值条件经常出现即使区分度不高也应该放在前面因为等值条件能精确定位而范围条件会打断索引的连续性。3.3 覆盖索引让SQL起飞的回表消除方案前面讲了回表的概念用二级索引找到主键再回到主键索引查整行。回表本身多一次IO数据量大时性能损耗很明显。如果查询需要的数据全部包含在二级索引的字段里数据库就不用回表了这种索引叫覆盖索引。我用一个真实优化案例来说明。有一个订单导出功能SQL大概是这样的SELECT id, order_no, create_time FROM t_order WHERE create_time 2024-01-01 ORDER BY create_time LIMIT 1000;原本表里有订单表主键索引因为create_time上有索引查询能走索引拿到id和create_time但order_no字段不在索引里所以每条记录都要回表拿order_no。在数据量大的场景下回表一千次性能就很差了。优化方式是把订单号和创建时间一起放进复合索引里ALTER TABLE t_order ADD INDEX idx_create_time_order_no(create_time, order_no);索引里包含create_time和order_no之后SEEK和扫描过程中就可以直接从索引取到全部需要的数据Extra列会显示Using index。这种优化效果非常直观尤其是统计类、列表导出类的查询收益巨大。需要注意的一点是覆盖索引不能滥用。每多一个索引写入数据时就要多做一次索引更新会拖慢INSERT、UPDATE和DELETE。对于写多读少的表加覆盖索引要谨慎衡量。3.4 索引失效的8个高频场景索引建了SQL也看着正常但执行计划就是不走索引。这种问题我在排查中遇到过太多次整理一下高频场景。第一对索引列使用函数。比如WHERE DATE(create_time) 2024-01-01为了让索引生效应该改写为create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。函数操作让优化器无法使用正常索引顺序。第二对索引列做强转等隐式类型转换。比如phone字段是varchar但查询时WHERE phone 13812345678数字会被转成字符串做比较就会使索引失效。第三模糊匹配时前导通配符。LIKE %keyword无法走索引但LIKE keyword%可以走。第四索引列参与计算。WHERE age 1 30要改写为WHERE age 29。第五使用OR连接非索引列。如果OR两边有一个条件不能用索引整个查询就可能走全表扫描可以用UNION ALL拆分。第六负向查询。WHERE status ! 1、WHERE status NOT IN (1, 2)、WHERE name IS NOT NULL这些负向条件很难走索引除非数据分布极其倾斜否则优化器通常会选择全表扫描。第七复合索引违反最左前缀原则这个前面详细说过。第八数据量太小和统计信息不准确。表里只有几百行数据优化器觉得全表扫描更快自然不走索引这其实是合理的。我在审查代码时看到这些写法基本一眼就能判断有没有问题。更重要的是除了知道这些场景还要明白失效背后的逻辑——索引是按有序排列存储的一旦对列做了加工处理原有的顺序就被破坏了数据库自然无法利用索引的有序性来加速查找。3.5 联合索引与排序ORDER BY的索引优化很多人忽略了索引对排序的加速作用。其实B树本身就按顺序存储如果ORDER BY的字段正好是索引前缀数据库就可以直接利用索引的有序性省掉filesort。比如有一个复合索引idx(user_id, create_time)那么下面的查询就可以避免排序SELECT * FROM t_order WHERE user_id 1001 ORDER BY create_time LIMIT 10;因为先按user_id定位到具体分支在这个分支里create_time已经天然有序。但如果改为ORDER BY create_time DESC且user_id是范围条件情况就不一样了。比如WHERE user_id 1000 ORDER BY create_time这时候user_id范围条件下create_time在整体上不是有序的数据库还是需要额外排序。另一个典型坑是ORDER BY字段顺序与索引定义顺序不一致。索引是(user_id, create_time)但排序是ORDER BY create_time, user_id由于字段顺序不匹配索引无法直接用于排序。所以设计复合索引时不仅要考虑WHERE条件还要把ORDER BY和GROUP BY的字段一起考虑进去尽量让一个索引满足筛选和排序双重需求。4. 查询重写实战从慢SQL到秒级响应4.1 用UNION ALL替换OR的实战收益OR导致的索引失效前面提到过。具体来说WHERE status 1 OR create_time 2024-01-01这种条件如果status和create_time分别有单列索引优化器理论上可以走索引合并但很多情况下走的是全表扫描。而且OR连接的子条件如果都走索引可能还要做索引合并索引合并在某些场景下代价也不低。我习惯的改写方式是把OR拆成两个独立的查询再用UNION ALL合并。比如SELECT * FROM t_order WHERE user_id 1001 UNION ALL SELECT * FROM t_order WHERE coupon_id 888;这里有个关键点一定要用UNION ALL而不是UNION。UNION会对结果集做去重这需要额外的排序和临时表操作在数据量大时非常消耗性能。只有两个子查询可能产生重复行时才需要UNION业务上能保证不重复的都用UNION ALL。实测过一个案例原本一条OR查询要跑4秒多改写成UNION ALL之后两个子查询各自走索引总耗时降到了300毫秒以内。这是因为每条子查询的过滤条件都能独立高效地定位避免了多个条件叠加导致优化器放弃索引。4.2 NOT IN和NOT EXISTS的改写技巧NOT IN和NOT EXISTS在语义上很接近但性能差别可能很大。核心问题在于NOT IN子查询在某些数据库版本和场景下会被优化成低效的执行计划。比如这条SQLSELECT id FROM t_user WHERE id NOT IN (SELECT user_id FROM t_order WHERE status 1);如果子查询返回的结果集很大NOT IN的语义要求检查每一个外层记录的id是否都不在子查询结果里数据库可能采用一种称为anti join的方式进行但有时会退化成低效的逐行子查询执行。一个常见的改写方案是改成LEFT JOIN加IS NULL判断SELECT u.id FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status 1 WHERE o.user_id IS NULL;这种写法把“不存在于”的语义转换为“左连接后右表为空”的语义优化器对这种join方式有丰富的优化策略通常能获得更好的执行计划。注意JOIN条件里一定要把status 1放进ON子句而不是WHERE子句否则会把LEFT JOIN结果过滤成内连接完全改变语义查询结果就错了。还有一个基础前提子查询和驱动表的关联字段都要有索引。用上面的例子t_order.user_id必须有索引否则LEFT JOIN会走全表扫描结果比NOT IN还慢。4.3 分页查询深翻页的优化方案分页慢是很多业务系统都会遇到的问题。普通LIMIT分页在页码小的时候没问题但翻到10000页时SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100000, 20;这个查询会扫描出前100020条记录然后丢掉前100000条只返回20条扫描的行数随着页码增加而线性增长。深翻页优化的经典方案有两种。第一种是延迟关联。先用索引快速定位到需要的主键范围然后再回表取完整数据SELECT o.* FROM t_order o INNER JOIN ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id;这个方案的思路是让内层查询只扫描索引覆盖索引不包含完整列确定需要返回的20个主键后再关联取整行数据大大减少了无效回表。第二种是基于游标的分页。记录上一次查询返回的最后一条记录的create_time和id下一页就直接用这两个条件往后取SELECT * FROM t_order WHERE create_time 2024-01-01 12:00:00 OR (create_time 2024-01-01 12:00:00 AND id 888) ORDER BY create_time DESC LIMIT 20;游标分页的好处是每一步都走索引范围扫描不管翻多深性能都很稳定。代价是用户不能随意跳页。实际业务中绝大多数场景用户都是顺序翻页所以游标分页的适用面非常广。4.4 COUNT查询的合理优化COUNT()在数据量上来之后会变得很慢尤其InnoDB引擎下不支持类似MyISAM的计数器缓存必须扫描所有行才能统计。我见过有人把一张千万级表的COUNT()查询直接暴露在接口里每次统计都要跑好几秒非常伤。首先要明确业务场景。如果是后台看板上需要实时精确的COUNT建议改用缓存方案在业务代码里维护计数器或者用独立的统计表。如果是列表分页需要总条数可以考虑近似值方案直接用EXPLAIN的rows预估值代替精确值很多列表场景对总数精确要求并不高。有些COUNT场景可以通过改写来优化。比如统计一个大表里符合条件的数据量原来的SQL是COUNT()加复杂条件如果条件里的列可以通过覆盖索引命中优化器就不需要回表扫描索引树即可。另外COUNT(1)和COUNT()在MySQL里没有性能差别不需要纠结这个。4.5 关联查询的优化小表驱动大表联表查询的性能问题很大程度取决于驱动表的选择。驱动表就是查询中先被访问的表然后拿驱动表的结果集去匹配另一张表。优化器通常会选择小表作为驱动表因为小表的行数少需要执行的关联次数就少。碰到复杂的多表关联时我会手动确认一下执行计划里的驱动表是否合理。如果发现驱动表不是小表可以调整SQL结构来影响优化器的选择比如使用STRAIGHT_JOIN强制指定连接顺序。不过这种做法要非常谨慎因为强制指定的顺序一旦遇到数据分布变化可能反而变差。更重要的一点是被驱动表的关联字段必须有索引。否则每拿驱动表的一行去匹配数据就要对被驱动表做一次全表扫描那代价是灾难级的。这对应了EXPLAIN里的eq_ref和ref类型用主键或唯一索引关联时走eq_ref效率最好。5. 实战案例一次慢SQL优化的完整过程5.1 问题SQL与初始EXPLAIN分析一个真实项目的案例。某订单系统的批量查询接口在高峰期频繁超时定位到一条SQLSELECT o.order_no, o.amount, u.mobile, u.nickname FROM t_order o LEFT JOIN t_user u ON o.user_id u.id WHERE o.create_time 2024-06-01 AND o.create_time 2024-07-01 AND o.status 1 ORDER BY o.create_time DESC LIMIT 200;t_order表有3000万行t_user表有500万行。初始EXPLAIN的结果是t_order表typeALLrows预估2900万Extra里还有Using where和Using filesortt_user表typeeq_refrows1。显然瓶颈在t_order表的全表扫描。5.2 优化过程与每一步的调整思路第一步给t_order表添加复合索引。WHERE条件里create_time是范围条件status是等值条件ORDER BY也用到了create_time。按照等值条件放前面的原则我把status放在前面create_time放在后面ALTER TABLE t_order ADD INDEX idx_status_create_time(status, create_time);加完索引再EXPLAINt_order表的type变成了rangerows降到了80万左右。但80万依然很大而且Extra仍然有Using filesort。第二步分析为什么还要filesort。索引顺序是(status, create_time)但查询里status 1是等值条件所以在这个索引分支下create_time确实是有序的ORDER BY create_time应该能直接用索引顺序。为什么还有filesort因为我SELECT了order_no、amount这些字段索引里没有需要回表取数据。回表之后的数据顺序不是create_time的顺序所以最终排序还是需要在内存里做一遍。这时候有两个思路一是把排序字段和查询字段都塞进索引做成覆盖索引二是想办法减少回表的数据量。第三步权衡后我决定做一个覆盖索引。因为接口的核心查询固定同时需要返回order_no和amount。我把索引扩展成ALTER TABLE t_order ADD INDEX idx_status_create_time_cover(status, create_time, order_no, amount);但这里有个问题业务表还有一个需求是按user_id查询订单列表不同查询场景对索引的需求不同一个覆盖索引并不能满足所有场景。所以这个索引是为这条高频SQL定制的同时保留了idx_status_create_time作为通用索引。第四步再把联表查询的驱动顺序确认一下。这个SQL的LEFT JOIN里t_order是驱动表t_user是被驱动表t_user表通过主键id关联走了eq_ref这部分没有问题。加上覆盖索引之后驱动表在索引上获取了所有需要的字段连回表都省了。EXPLAIN里Extra出现了Using index conditionfilesort也消失了rows降到几千行。5.3 优化前后的性能对比优化前这条SQL在测试环境的数据量下执行了约4.2秒高峰期线上要跑到10秒以上已经触发了慢查询告警。优化后同样的数据量下执行时间降到了180毫秒左右提升了超过20倍。更重要的是资源消耗的变化。优化前全表扫描需要读取将近3000万的记录磁盘IO和内存占用都非常夸张。优化后通过索引定位到约3万条记录再通过覆盖索引取列磁盘读取量几乎可以忽略不计。在高并发调用下这个提升对数据库整体负载的影响极其明显。每次查这个案例我都有几个体会。第一索引设计一定要针对实际SQL而不是对着表结构凭空想。把表里的所有SQL收集起来按WHERE、ORDER BY、GROUP BY、JOIN字段做分类再设计匹配的复合索引效率远高于凭感觉建索引。第二覆盖索引是应对大查询的杀器但要根据场景取舍不能把所有表的查询都指望一个覆盖索引解决。第三优化完必须用真实数据验证不能只看EXPLAIN结果测试环境和线上数据分布差异导致的执行计划偏差很常见。6. 常见问题与排查技巧实录6.1 为什么加了索引却不生效这个问题我几乎每周都会遇到一次。加了索引但不生效高频原因无非这几类索引列上做了函数或计算操作字符串列查询时没加引号导致隐式类型转换复合索引违反最左前缀原则使用了LIKE前导通配符OR条件里混入了非索引列区分度太低的列即使有索引优化器也会放弃走索引因为全表扫描的代价可能更低。排查方法就是把EXPLAIN打开一条条对比。如果possible_keys有值但key是NULL说明优化器权衡后决定不走索引可能是区分度问题。如果possible_keys本身就是NULL说明根本没有可用索引检查索引定义和SQL条件是否匹配。有一种情况容易被忽略就是统计信息太旧。MySQL的优化器依赖表统计信息来估算行数如果统计信息长时间没有更新优化器可能做出错误判断。执行ANALYZE TABLE刷新统计信息后有时会发现执行计划恢复正常。6.2 查看慢查询日志和定位问题SQL很多中小团队没有接入专业的数据库监控平台这时候使用慢查询日志是最直接的定位手段。MySQL里通过参数设置开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time设置为1秒意味着执行超过1秒的SQL都会被记录。还有一个容易被忽略的配置log_queries_not_using_indexes开启后会把没有走索引的SQL也记录到慢查询日志里对于发现隐藏的全表扫描问题很有帮助。拿到慢查询日志后我可以借助mysqldumpslow或pt-query-digest这类工具做聚合分析把同类SQL汇总按执行次数、总耗时排序。很多问题SQL不是单次慢而是被频繁调用累积出来的这类SQL必须按照执行频次加单次耗时的综合排序来决定优化优先级。6.3 SQL优化中不容忽视的隐式类型转换问题隐式类型转换是我见过最隐蔽的索引失效原因之一。表中user_id字段是varchar(32)SQL写成WHERE user_id 12345MySQL会在比较时把字符串转成数字一旦发生转换索引列上就相当于加了CAST函数索引自然失效。排查方式很直接——看到执行计划里key为NULL先检查所有比较条件里字段的类型和值类型是否完全一致。我用一个习惯写SQL时对字符串字段严格要求加引号哪怕是数字字符串也一律写成12345这种形式。这不仅是规范问题更是性能问题。还有一种发生在联表场景。两张表关联字段分别是int和varchar字段值相同但类型不一致JOIN时也会发生隐式转换导致索引失效。设计表结构时就把关联字段的类型统一能在源头上规避这个问题。6.4 怎么用profiling精细定位SQL耗时分布EXPLAIN能告诉我们执行计划长什么样但无法告诉我们SQL执行过程中每个阶段的真实耗时。如果想精细定位瓶颈可以用MySQL的profiling功能。SET profiling 1; -- 执行慢SQL SHOW PROFILES; -- 查看详细耗时 SHOW PROFILE FOR QUERY 1;输出的结果会列出SQL执行过程中的各个阶段耗时包括Sending data、Sorting result、Creating sort index等。我在一次奇怪的慢查询排查中发现SQL本身逻辑很简单索引也走了但总执行时间就是居高不下。用profiling一看发现大量时间花在Sending data阶段进一步排查发现是网络传输问题加上返回了大量LOB字段数据纯查索引解决不了这个问题最后通过减少返回字段和增加网络带宽解决了。这个例子说明一个道理SQL优化是系统性的不能只看执行计划这一个维度。返回数据量、网络环境、客户端处理逻辑都有可能成为瓶颈。7. 优化思路的沉淀与总结做了这么多年SQL优化我发现真正的优化高手和普通开发之间最大的差别不是背了多少优化技巧而是有没有一套清晰的排查思路和沉淀下来的习惯。我个人习惯在项目里做三件事。第一件建立慢SQL台账。每次优化过的慢SQL把SQL原文、EXPLAIN结果、问题原因、优化方案、优化前后耗时对比都记录下来。下次看到类似的SQL直接翻台账就能找到参考方案效率高很多。第二件把索引设计纳入代码评审。每次新上线一张表或者一条新查询都要求设计人员把EXPLAIN结果贴到评审文档里把索引问题扼杀在上线之前。第三件定期巡检数据库。每周把慢查询日志拿出来过一遍看看有没有新出现的性能隐患。SQL优化不是一次性的工作数据量在增长业务逻辑在变复杂今天好用的执行计划明天可能就失效了。但只要掌握方法紧跟执行计划的变化保持对慢SQL的高敏感度性能问题就不会成为系统的瓶颈。希望这篇文章里的索引策略和查询重写方法能帮你在实际项目中少走一些弯路多省一些时间。