1. 索引失效的底层逻辑优化器的选择困境1.1 为什么明明建了索引查询却还是慢做SQL优化这几年我见过太多开发者栽在同一道坎上表里明明建了索引EXPLAIN一看却是ALL全表扫描慢查询日志里整天躺着那条“该死”的SQL。问题到底出在哪先说结论索引不是建了就能用而是优化器“愿意”用才会用。MySQL的优化器在收到一条SQL后会基于统计信息估算各种执行计划的代价——走全表扫描要读多少页、走索引要回表多少次、条件过滤能筛掉多少行——然后选一个它认为成本最小的方案。所以索引失效本质上不是数据库“坏了”而是优化器通过成本计算后认为你的索引根本不划算。这里面最常见的两类情况**第一函数操作导致索引列失去有序性。**比如WHERE DATE(create_time) 2025-01-01你看着没问题但优化器眼里DATE()已经改变了列值的原始排序B树里的有序结构完全用不上只能全表扫。正确的写法应该是WHERE create_time 2025-01-01 AND create_time 2025-01-02让索引列保持裸列状态。**第二隐式类型转换。**比如user_id是VARCHAR(32)你写WHERE user_id 10086MySQL会把字符串列转成数字去比较相当于对索引列做了一次隐形函数索引失效。这种坑在联表查询里尤其隐蔽两个表的关联字段类型不一致两边都建了索引结果一个能走一个不能走。所以当你发现索引“失效”时第一反应不该是FORCE INDEX强行指定而是去问优化器为什么觉得走索引不划算统计信息过期了还是SQL写法本身破坏了索引的有序性先把这个问题想清楚。1.2 统计信息与行数估算优化器也会“看走眼”优化器的成本估算依赖统计信息而统计信息不是实时更新的。InnoDB默认通过采样来估算索引的基数Cardinality当表数据频繁增删改时统计信息可能严重滞后。举个例子某订单表有500万行数据status字段只有3个值0待支付、1已支付、2已取消你在status上建了索引然后查WHERE status 1。优化器一看统计信息估算出status1占比40%觉得回表成本太高干脆全表扫。但实际上线上数据status1只占5%走索引完全更优。这种场景下你有两个选择执行ANALYZE TABLE 表名强制更新统计信息让优化器重新估算。使用FORCE INDEX人工干预执行计划。我的建议是优先更新统计信息。FORCE INDEX是最后的底牌因为它会锁死执行计划一旦数据分布再次变化反而会造成更严重的性能问题。你需要的不是让优化器“听你的”而是给它足够准确的信息让它做出正确的判断。2. 高性能索引设计从单列到联合索引的演进2.1 联合索引的最左前缀原则到底怎么理解联合索引可能是面试里被问烂了但实际开发中依然用不好的一个点。很多人背过“最左前缀原则”但遇到具体查询就懵(a, b, c)这个联合索引到底哪些查询能用上哪些不能我用一个最直观的方式来解释联合索引就是按照字段顺序构建的一棵排序树。先按a排序a相同再按b排序b相同再按c排序。所以在等值匹配时WHERE a 1 AND b 2能走索引WHERE b 2走不了——因为索引的全局有序性是建立在a的基础上的跳过a直接查b索引里根本没法二分定位。但有几个容易被忽略的细节第一最左前缀不一定从第一个字段开始才算“最左”。只要查询条件里包含联合索引的最左字段后续字段的等值匹配都能用上。比如WHERE a 1 AND c 3a能用索引定位c用不上因为中间隔了b但回表次数已经被a大幅缩小了。这算是部分用到索引而不是完全失效。第二ORDER BY也能利用联合索引的有序性。WHERE a 1 ORDER BY b这里b不需要排序直接按索引顺序读取就行。这也解释了为什么联合索引的字段顺序设计要同时考虑查询条件和排序需求。如果你想查WHERE a 1 ORDER BY c那c就免不了filesort因为b跳过了c在索引里的顺序是“在相同b值下才有序”跨b值的时候是乱序的。第三范围查询右边的字段会失效。WHERE a 1 AND b 2a的范围条件已经确定了索引的扫描区间b的有序性在这个区间内无法保持所以b用不上索引。这也是为什么我反复强调联合索引设计时等值条件放前面范围条件放后面。2.2 覆盖索引让查询连回表都省了回表是InnoDB二级索引查询不可避免的代价——先在二级索引B树上找到主键再拿着主键去聚簇索引里取整行数据。如果查询的列恰好都包含在索引里优化器就能直接在二级索引上拿到所有需要的数据这一步全省了。这就是覆盖索引性能提升非常可观。比如经常要查SELECT user_id, name FROM users WHERE status 1你建一个(status, user_id, name)的联合索引这个查询的所有列都在索引里Extra列会显示Using index回表次数为零。实践里我经常靠覆盖索引来优化高频查询思路是先圈定高频查询的WHERE和SELECT列然后把这些列揉进同一个索引。数据量越大覆盖索引带来的收益越明显。举个例子一个千万级的订单表统计每天订单数用的是SELECT COUNT(*) FROM orders WHERE create_time BETWEEN ...如果create_time上有索引COUNT(*)可以直接走索引统计行数不需要回表取每行数据。覆盖索引还有一个隐藏收益二级索引通常比聚簇索引小得多同样的数据量扫描索引页的数量可能只有聚簇索引的几分之一IO成本大幅降低。2.3 索引字段的顺序如何取舍区分度优先还是查询频率优先设计联合索引字段顺序时最常见的争论是区分度高的字段放前面还是查询频率高的字段放前面先说结论区分度优先但前提是等值匹配。在WHERE条件都是等值匹配的情况下区分度高的字段放前面能更快缩小扫描范围。比如(gender, user_id)和(user_id, gender)前者gender只有两个值扫描范围缩小到一半后者user_id直接定位到一行。但如果是范围查询情况就变了。前面说过范围查询右边的字段索引会失效所以应该把范围查询的字段往后放前面的字段尽量用等值匹配来缩窄扫描区间。还有一个容易忽略的点是查询频率。如果两个字段区分度接近优先把查询频率更高的字段放前面。因为联合索引本身也能覆盖到“只查最左字段”的场景高频字段放前面可以让更多查询直接复用这个索引避免额外建索引的成本。举个综合例子。假设有一个user_orders表高频查询是“查某个用户在某个时间段内的订单”SQL长这样SELECT order_id, amount FROM user_orders WHERE user_id 10086 AND create_time 2025-01-01 AND create_time 2025-02-01;这时候联合索引(user_id, create_time)是最优解user_id等值命中create_time范围命中但注意create_time右边不能再有字段了。如果要查的列order_id和amount也加进来变成(user_id, create_time, order_id, amount)还能顺便凑成覆盖索引连回表都省了。3. EXPLAIN实战看懂执行计划里的潜台词3.1 type列从ALL到const的优化路径EXPLAIN是SQL优化最重要的工具没有之一。很多人会跑EXPLAIN但只会看key列有没有值这是远远不够的。type列才是执行效率最直观的体现它描述了访问类型从好到差大概是system const eq_ref ref range index ALLconst通过主键或唯一索引等值查询最多返回一行这是最优状态。eq_ref联表查询时被驱动表通过主键或唯一索引等值匹配也是很好。ref通过普通索引等值匹配可能返回多行多数情况下可以接受。range索引范围扫描比如BETWEEN、、有索引的辅助下还算高效。index全索引扫描遍历整个索引树。比全表扫描好一点但本质还是不理想。ALL全表扫描实力劝退。优化的核心目标就是把ALL提升到至少range能到ref更好const可遇不可求只有主键和唯一索引等值查询才能到。我看执行计划时的习惯是先看type如果出现ALL立刻标记为优化对象然后看key与rows确认是否真的走了索引以及估算扫描行数最后看Extra判断有没有Using filesort、Using temporary这种隐藏的坑。3.2 Extra列里最坑的两种提示Extra列藏着很多优化器的小动作其中有两种几乎总是性能杀手一是Using filesort。这不是说在磁盘上排序而是表示MySQL需要额外执行一次排序操作而不是直接利用索引的有序性。比如WHERE status 1 ORDER BY create_time DESC如果联合索引是(status, create_time)排序用不上因为索引里create_time是按升序排列的但你要降序——这里又涉及一个MySQL 8.0的改进8.0之后支持降序索引可以真正物理存储降序排列解决这类问题。二是Using temporary。表示查询使用了临时表常见于GROUP BY、DISTINCT、子查询等场景。临时表的内存版本叫MEMORY数据量一大就会落到磁盘临时表性能断崖式下跌。优化方向通常是改写SQL或调整索引让分组和去重操作能直接利用索引的有序性。顺便说一句判断Using filesort要不要优化得看数据量。几千行的小表排个序也就几个毫秒没必要为了消除filesort大动干戈加索引。但百万行以上的表filesort就非常致命了必须想办法用索引顺序替代。3.3 如何用EXPLAIN对比索引方案实际工作中我经常需要对比不同索引方案的效果。做法很简单同一个SQL分别建不同索引跑EXPLAIN对比rows估算值。虽然rows是估算的但用来横向对比索引方案参考价值很高。比如上面那个订单统计的例子EXPLAIN SELECT COUNT(*) FROM orders WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31;方案A是仅create_time单列索引方案B是(create_time, status)联合索引。多数情况下方案B的rows会比方案A少因为联合索引覆盖了更多可能用到的条件。如果还能改成覆盖索引Extra列出现Using index那基本就是接近最优了。这里要注意一点对比rows时也要看filtered列。filtered表示满足条件的行数占比估算如果rows 10000但filtered 1说明实际命中的只有100行优化器可能低估了索引的过滤能力。4. 典型慢SQL优化实战从定位到上线4.1 定位慢SQL慢查询日志和性能分析工具怎么配合优化慢SQL的第一步是找到它们。MySQL开启慢查询日志是基础操作SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的SQL都记下来 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;线上环境建议把long_query_time设成1秒甚至0.5秒太大会漏掉很多“潜在的慢查询”——有些SQL平均执行200毫秒但调用频率极高累积的资源消耗比偶发的2秒查询更可怕。拿到慢查询日志后我先用mysqldumpslow工具做聚合统计看看哪些SQL是“又慢又频繁”的mysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at按平均执行时间排序-t 10取前10条。聚合出来的SQL往往是优化的高优先级对象。有了具体SQL之后再针对单条做EXPLAIN和EXPLAIN ANALYZEMySQL 8.0能给出实际执行时间。4.2 一个OR条件引发的血案从全表扫描到索引命中分享一个我实战中遇到的案例。有个商品表products规模约300万行线上有一条查询经常超过3秒SELECT id, name, price, stock FROM products WHERE brand_id 101 OR category_id 88 ORDER BY sales_volume DESC LIMIT 20;brand_id和category_id分别建了索引但type显示ALL。原因很经典OR条件使优化器难以同时利用两个独立索引除非用UNION拆开或者INDEX MERGE优化生效否则它倾向于全表扫描。我的优化方案是把SQL拆成两个查询后合并SELECT id, name, price, stock FROM products WHERE brand_id 101 UNION SELECT id, name, price, stock FROM products WHERE category_id 88 ORDER BY sales_volume DESC LIMIT 20;改写后两个子查询分别命中brand_id和category_id索引type从ALL变成了ref单条查询耗时从3.2秒降到0.4秒。这个改动上线后接口P95延迟直接降了一个数量级。不过这里有个小细节改写成UNION后ORDER BY和LIMIT要放在最后一个查询才生效如果放在第一个子查询里排序只会作用于第一个分支结果就错了。4.3 分页深翻页的优化延迟关联到底强在哪分页查询是另一个高频翻车现场。LIMIT 200000, 20这种深分页即使走了索引前200000条数据都得扫描后丢弃效率极其低下。我在运营后台的系统里经常处理这种需求标准的优化手段是延迟关联-- 原始写法慢 SELECT id, name, price FROM products ORDER BY id LIMIT 200000, 20; -- 延迟关联写法快 SELECT p.id, p.name, p.price FROM products p INNER JOIN ( SELECT id FROM products ORDER BY id LIMIT 200000, 20 ) t ON p.id t.id;思路很简单先用覆盖索引在主键上定位到第200001到200020条的id这一步扫描的是紧凑的索引页不回表然后再用这些id回到聚簇索引取完整行数据。相比原始写法每扫一条都要回表一次性能提升是数量级的。类似的场景还有“基于游标的分页”WHERE id 上一页最大id ORDER BY id LIMIT 20。如果业务允许这种方案比LIMIT深翻页更优雅但需要前端配合改造。4.4 优化上线前一定要做的三件事改完SQL和索引不要急着上线。我踩过的坑告诉我至少要过三关第一关EXPLAIN验证执行计划。确认type明显改善key列是预期索引rows估算合理Extra没有filesort和temporary。第二关压测环境测试。拿生产数据脱敏后恢复到预发环境模拟线上真实流量跑一遍。注意观察锁等待和IO情况——有些优化虽然单个查询快了但可能引入更频繁的锁冲突整体吞吐未必提升。第三关灰度上线。先放5%~10%的流量观察对比优化前后的慢查询数量和接口延迟。如果出现性能回退立即回滚索引或SQL版本。5. 索引维护与常见失效场景排查5.1 索引下推被低估的优化利器MySQL 5.6引入的索引下推Index Condition Pushdown, ICP很多人不知道但它对联合索引的查询效率提升非常明显。原理一句话在存储引擎层遍历索引时直接把WHERE条件中能被索引列覆盖的部分下推到存储引擎进行过滤减少回表次数。举个例子联合索引(age, city)查询WHERE age 20 AND city 上海——age是范围查询city本来到不了索引层面过滤但有了ICP存储引擎在遍历索引时就会用city 上海过滤掉不符合条件的记录只有真正满足条件的才回表。判断ICP是否生效看EXPLAIN的Extra列有没有Using index condition。触发ICP有几个条件索引包含相关列、存储引擎支持该特性InnoDB默认支持、查询不是覆盖索引如果是覆盖索引本来就无需回表ICP意义不大。5.2 一张表建立多少个索引合适索引不是越多越好。每多一个索引意味着插入、更新、删除时都要多维护一棵B树写入性能直接受损。磁盘空间也从“几乎不用考虑”变成了“真金白银的成本”。我个人的经验规则单表索引数量5个以内是比较健康的超过8个就要反思是否过度索引。单索引字段数3~4个以内再多意义就不大了而且会显著增加索引体积。高频写表索引数量要更克制优先保证写入吞吐。有个很典型的反面案例运营后台为了“响应各种筛选条件”在十几列上各建了一个单列索引结果写接口从50ms涨到800ms。最后我把所有单列索引删除设计了两个联合索引覆盖主要查询场景写入恢复查询也没降速——因为大部分查询本来就是组合条件单列索引本来就不该被用上。5.3 MySQL 8.0新增的索引能力值得升级吗如果你还在用MySQL 5.7考虑升级到8.0时索引相关的改进有几个值得关注的亮点第一个是降序索引。5.7里ORDER BY a DESC, b ASC这种混合排序经常导致filesort8.0可以在索引定义时指定每个字段的排序方向让索引顺序和查询排序完全匹配。第二个是隐藏索引Invisible Index。可以把索引设为INVISIBLE优化器会忽略它但索引依然在维护。这给索引下线提供了很好的过渡手段——先在测试环境把目标索引隐藏观察慢查询是否有变化确认不依赖后再真正删除避免误删索引导致线上事故。第三个是索引跳过扫描Skip Scan。当联合索引的最左列是低区分度字段、且查询条件跳过它时优化器可以自动扫描所有不同的最左值来“模拟”索引查找部分缓解了最左前缀的局限性。不过这个特性有前提条件效果因数据分布而异不要期望太高。5.4 索引失效场景速查表结合我日常排查的经验整理一份高频失效场景清单遇到问题可以直接对照场景示例是否失效对策索引列使用函数WHERE DATE(create_time) 2025-01-01失效改写为范围条件隐式类型转换WHERE user_id 10086user_id为字符串失效保持类型一致LIKE以通配符开头WHERE name LIKE %美食%失效改前缀匹配或全文索引OR连接非索引列WHERE a 1 OR b 2b无索引可能失效拆分为UNION!或WHERE status ! 1通常失效改写为IN (其他值)NOT INWHERE status NOT IN (1, 2)通常失效评估改LEFT JOIN联合索引跳过最左列索引(a,b)条件WHERE b 2失效调整索引或查询条件范围查询右侧字段索引(a,b)条件WHERE a 1 AND b 2b失效调整索引字段顺序表格里说的“通常失效”不一定绝对有些场景取决于优化器的成本判断和数据分布。比如OR如果两个条件都覆盖同一个索引也可能走INDEX MERGE。遇到具体问题还是以EXPLAIN的结果为准。5.5 在线DDL与索引维护的注意事项生产环境加索引最怕的是锁表导致业务中断。MySQL 8.0的INPLACE算法支持在大部分场景下在线加索引但有几个细节要留意在大表上加索引不管是不是INPLACE都会产生额外的磁盘IO和主从复制延迟。凌晨低峰期操作是标配。如果表有外键约束部分DDL还是会退化为COPY锁表时间不可控。ALGORITHMINPLACE不是万能药它只会减少锁的粒度不会消除对写入的短暂阻塞。我曾经在一张5亿行的流水表上加过索引当时预估耗时2小时我以为可以摸鱼了结果半小时后从库延迟飙到10分钟业务报警。后来我学乖了先用pt-online-schema-change这类工具在从库演练再在低峰期分批操作同时监控主从延迟。大表DDL永远要当成一次小型的运维变更来做而不是“执行一条SQL就完事”。另外索引维护不只是加索引和删索引还包括定期更新统计信息。如果表的增删改很频繁可以设置innodb_stats_auto_recalc自动重算或者周期性执行ANALYZE TABLE。否则就会回到第一章说的统计信息失真优化器做出不明智的选择。6. 独家经验我在SQL优化中用过的三板斧优化SQL这件事说难也难说简单也简单。这些年我沉淀了一套自己的排查套路每次遇到性能问题基本按这个顺序走第一板斧先看执行计划不看代码。不管业务逻辑写得再复杂最终落到数据库上就是一条SQL的执行计划。先EXPLAIN把type、key、rows、Extra看明白问题基本就定位了一半。第二板斧揪出回表和排序。90%的慢查询都能归因到两个操作回表太多、排序太慢。回表多就考虑覆盖索引排序慢就考虑联合索引的有序性。这两个点优化到位大部分SQL都能跑回百毫秒以内。第三板斧验证验证再验证。用生产数据量级做压测用EXPLAIN ANALYZE看实际执行代价灰度上线后对比监控指标。没有验证过的索引设计都是纸面优化。最后再分享一个小技巧优化完别忘了清理冗余索引。有时候为了覆盖某个新场景你加了个索引旧的索引可能就不再被任何查询利用了。用sys.schema_unused_indexes视图可以查出来哪些索引从未被使用过定期清理这些“僵尸索引”既能省空间还能提升写入性能。我每次上线索引调整后都会顺手查一下这个视图清理效果非常显著。索引优化说到底就是一场“用空间换时间”的平衡艺术。它不是堆索引的数量而是精准匹配查询模式让每一棵B树都被用在刀刃上。希望这篇实战经验能帮你少踩几个坑。
MySQL索引失效与慢SQL优化实战：从EXPLAIN到联合索引设计
1. 索引失效的底层逻辑优化器的选择困境1.1 为什么明明建了索引查询却还是慢做SQL优化这几年我见过太多开发者栽在同一道坎上表里明明建了索引EXPLAIN一看却是ALL全表扫描慢查询日志里整天躺着那条“该死”的SQL。问题到底出在哪先说结论索引不是建了就能用而是优化器“愿意”用才会用。MySQL的优化器在收到一条SQL后会基于统计信息估算各种执行计划的代价——走全表扫描要读多少页、走索引要回表多少次、条件过滤能筛掉多少行——然后选一个它认为成本最小的方案。所以索引失效本质上不是数据库“坏了”而是优化器通过成本计算后认为你的索引根本不划算。这里面最常见的两类情况**第一函数操作导致索引列失去有序性。**比如WHERE DATE(create_time) 2025-01-01你看着没问题但优化器眼里DATE()已经改变了列值的原始排序B树里的有序结构完全用不上只能全表扫。正确的写法应该是WHERE create_time 2025-01-01 AND create_time 2025-01-02让索引列保持裸列状态。**第二隐式类型转换。**比如user_id是VARCHAR(32)你写WHERE user_id 10086MySQL会把字符串列转成数字去比较相当于对索引列做了一次隐形函数索引失效。这种坑在联表查询里尤其隐蔽两个表的关联字段类型不一致两边都建了索引结果一个能走一个不能走。所以当你发现索引“失效”时第一反应不该是FORCE INDEX强行指定而是去问优化器为什么觉得走索引不划算统计信息过期了还是SQL写法本身破坏了索引的有序性先把这个问题想清楚。1.2 统计信息与行数估算优化器也会“看走眼”优化器的成本估算依赖统计信息而统计信息不是实时更新的。InnoDB默认通过采样来估算索引的基数Cardinality当表数据频繁增删改时统计信息可能严重滞后。举个例子某订单表有500万行数据status字段只有3个值0待支付、1已支付、2已取消你在status上建了索引然后查WHERE status 1。优化器一看统计信息估算出status1占比40%觉得回表成本太高干脆全表扫。但实际上线上数据status1只占5%走索引完全更优。这种场景下你有两个选择执行ANALYZE TABLE 表名强制更新统计信息让优化器重新估算。使用FORCE INDEX人工干预执行计划。我的建议是优先更新统计信息。FORCE INDEX是最后的底牌因为它会锁死执行计划一旦数据分布再次变化反而会造成更严重的性能问题。你需要的不是让优化器“听你的”而是给它足够准确的信息让它做出正确的判断。2. 高性能索引设计从单列到联合索引的演进2.1 联合索引的最左前缀原则到底怎么理解联合索引可能是面试里被问烂了但实际开发中依然用不好的一个点。很多人背过“最左前缀原则”但遇到具体查询就懵(a, b, c)这个联合索引到底哪些查询能用上哪些不能我用一个最直观的方式来解释联合索引就是按照字段顺序构建的一棵排序树。先按a排序a相同再按b排序b相同再按c排序。所以在等值匹配时WHERE a 1 AND b 2能走索引WHERE b 2走不了——因为索引的全局有序性是建立在a的基础上的跳过a直接查b索引里根本没法二分定位。但有几个容易被忽略的细节第一最左前缀不一定从第一个字段开始才算“最左”。只要查询条件里包含联合索引的最左字段后续字段的等值匹配都能用上。比如WHERE a 1 AND c 3a能用索引定位c用不上因为中间隔了b但回表次数已经被a大幅缩小了。这算是部分用到索引而不是完全失效。第二ORDER BY也能利用联合索引的有序性。WHERE a 1 ORDER BY b这里b不需要排序直接按索引顺序读取就行。这也解释了为什么联合索引的字段顺序设计要同时考虑查询条件和排序需求。如果你想查WHERE a 1 ORDER BY c那c就免不了filesort因为b跳过了c在索引里的顺序是“在相同b值下才有序”跨b值的时候是乱序的。第三范围查询右边的字段会失效。WHERE a 1 AND b 2a的范围条件已经确定了索引的扫描区间b的有序性在这个区间内无法保持所以b用不上索引。这也是为什么我反复强调联合索引设计时等值条件放前面范围条件放后面。2.2 覆盖索引让查询连回表都省了回表是InnoDB二级索引查询不可避免的代价——先在二级索引B树上找到主键再拿着主键去聚簇索引里取整行数据。如果查询的列恰好都包含在索引里优化器就能直接在二级索引上拿到所有需要的数据这一步全省了。这就是覆盖索引性能提升非常可观。比如经常要查SELECT user_id, name FROM users WHERE status 1你建一个(status, user_id, name)的联合索引这个查询的所有列都在索引里Extra列会显示Using index回表次数为零。实践里我经常靠覆盖索引来优化高频查询思路是先圈定高频查询的WHERE和SELECT列然后把这些列揉进同一个索引。数据量越大覆盖索引带来的收益越明显。举个例子一个千万级的订单表统计每天订单数用的是SELECT COUNT(*) FROM orders WHERE create_time BETWEEN ...如果create_time上有索引COUNT(*)可以直接走索引统计行数不需要回表取每行数据。覆盖索引还有一个隐藏收益二级索引通常比聚簇索引小得多同样的数据量扫描索引页的数量可能只有聚簇索引的几分之一IO成本大幅降低。2.3 索引字段的顺序如何取舍区分度优先还是查询频率优先设计联合索引字段顺序时最常见的争论是区分度高的字段放前面还是查询频率高的字段放前面先说结论区分度优先但前提是等值匹配。在WHERE条件都是等值匹配的情况下区分度高的字段放前面能更快缩小扫描范围。比如(gender, user_id)和(user_id, gender)前者gender只有两个值扫描范围缩小到一半后者user_id直接定位到一行。但如果是范围查询情况就变了。前面说过范围查询右边的字段索引会失效所以应该把范围查询的字段往后放前面的字段尽量用等值匹配来缩窄扫描区间。还有一个容易忽略的点是查询频率。如果两个字段区分度接近优先把查询频率更高的字段放前面。因为联合索引本身也能覆盖到“只查最左字段”的场景高频字段放前面可以让更多查询直接复用这个索引避免额外建索引的成本。举个综合例子。假设有一个user_orders表高频查询是“查某个用户在某个时间段内的订单”SQL长这样SELECT order_id, amount FROM user_orders WHERE user_id 10086 AND create_time 2025-01-01 AND create_time 2025-02-01;这时候联合索引(user_id, create_time)是最优解user_id等值命中create_time范围命中但注意create_time右边不能再有字段了。如果要查的列order_id和amount也加进来变成(user_id, create_time, order_id, amount)还能顺便凑成覆盖索引连回表都省了。3. EXPLAIN实战看懂执行计划里的潜台词3.1 type列从ALL到const的优化路径EXPLAIN是SQL优化最重要的工具没有之一。很多人会跑EXPLAIN但只会看key列有没有值这是远远不够的。type列才是执行效率最直观的体现它描述了访问类型从好到差大概是system const eq_ref ref range index ALLconst通过主键或唯一索引等值查询最多返回一行这是最优状态。eq_ref联表查询时被驱动表通过主键或唯一索引等值匹配也是很好。ref通过普通索引等值匹配可能返回多行多数情况下可以接受。range索引范围扫描比如BETWEEN、、有索引的辅助下还算高效。index全索引扫描遍历整个索引树。比全表扫描好一点但本质还是不理想。ALL全表扫描实力劝退。优化的核心目标就是把ALL提升到至少range能到ref更好const可遇不可求只有主键和唯一索引等值查询才能到。我看执行计划时的习惯是先看type如果出现ALL立刻标记为优化对象然后看key与rows确认是否真的走了索引以及估算扫描行数最后看Extra判断有没有Using filesort、Using temporary这种隐藏的坑。3.2 Extra列里最坑的两种提示Extra列藏着很多优化器的小动作其中有两种几乎总是性能杀手一是Using filesort。这不是说在磁盘上排序而是表示MySQL需要额外执行一次排序操作而不是直接利用索引的有序性。比如WHERE status 1 ORDER BY create_time DESC如果联合索引是(status, create_time)排序用不上因为索引里create_time是按升序排列的但你要降序——这里又涉及一个MySQL 8.0的改进8.0之后支持降序索引可以真正物理存储降序排列解决这类问题。二是Using temporary。表示查询使用了临时表常见于GROUP BY、DISTINCT、子查询等场景。临时表的内存版本叫MEMORY数据量一大就会落到磁盘临时表性能断崖式下跌。优化方向通常是改写SQL或调整索引让分组和去重操作能直接利用索引的有序性。顺便说一句判断Using filesort要不要优化得看数据量。几千行的小表排个序也就几个毫秒没必要为了消除filesort大动干戈加索引。但百万行以上的表filesort就非常致命了必须想办法用索引顺序替代。3.3 如何用EXPLAIN对比索引方案实际工作中我经常需要对比不同索引方案的效果。做法很简单同一个SQL分别建不同索引跑EXPLAIN对比rows估算值。虽然rows是估算的但用来横向对比索引方案参考价值很高。比如上面那个订单统计的例子EXPLAIN SELECT COUNT(*) FROM orders WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31;方案A是仅create_time单列索引方案B是(create_time, status)联合索引。多数情况下方案B的rows会比方案A少因为联合索引覆盖了更多可能用到的条件。如果还能改成覆盖索引Extra列出现Using index那基本就是接近最优了。这里要注意一点对比rows时也要看filtered列。filtered表示满足条件的行数占比估算如果rows 10000但filtered 1说明实际命中的只有100行优化器可能低估了索引的过滤能力。4. 典型慢SQL优化实战从定位到上线4.1 定位慢SQL慢查询日志和性能分析工具怎么配合优化慢SQL的第一步是找到它们。MySQL开启慢查询日志是基础操作SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的SQL都记下来 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;线上环境建议把long_query_time设成1秒甚至0.5秒太大会漏掉很多“潜在的慢查询”——有些SQL平均执行200毫秒但调用频率极高累积的资源消耗比偶发的2秒查询更可怕。拿到慢查询日志后我先用mysqldumpslow工具做聚合统计看看哪些SQL是“又慢又频繁”的mysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at按平均执行时间排序-t 10取前10条。聚合出来的SQL往往是优化的高优先级对象。有了具体SQL之后再针对单条做EXPLAIN和EXPLAIN ANALYZEMySQL 8.0能给出实际执行时间。4.2 一个OR条件引发的血案从全表扫描到索引命中分享一个我实战中遇到的案例。有个商品表products规模约300万行线上有一条查询经常超过3秒SELECT id, name, price, stock FROM products WHERE brand_id 101 OR category_id 88 ORDER BY sales_volume DESC LIMIT 20;brand_id和category_id分别建了索引但type显示ALL。原因很经典OR条件使优化器难以同时利用两个独立索引除非用UNION拆开或者INDEX MERGE优化生效否则它倾向于全表扫描。我的优化方案是把SQL拆成两个查询后合并SELECT id, name, price, stock FROM products WHERE brand_id 101 UNION SELECT id, name, price, stock FROM products WHERE category_id 88 ORDER BY sales_volume DESC LIMIT 20;改写后两个子查询分别命中brand_id和category_id索引type从ALL变成了ref单条查询耗时从3.2秒降到0.4秒。这个改动上线后接口P95延迟直接降了一个数量级。不过这里有个小细节改写成UNION后ORDER BY和LIMIT要放在最后一个查询才生效如果放在第一个子查询里排序只会作用于第一个分支结果就错了。4.3 分页深翻页的优化延迟关联到底强在哪分页查询是另一个高频翻车现场。LIMIT 200000, 20这种深分页即使走了索引前200000条数据都得扫描后丢弃效率极其低下。我在运营后台的系统里经常处理这种需求标准的优化手段是延迟关联-- 原始写法慢 SELECT id, name, price FROM products ORDER BY id LIMIT 200000, 20; -- 延迟关联写法快 SELECT p.id, p.name, p.price FROM products p INNER JOIN ( SELECT id FROM products ORDER BY id LIMIT 200000, 20 ) t ON p.id t.id;思路很简单先用覆盖索引在主键上定位到第200001到200020条的id这一步扫描的是紧凑的索引页不回表然后再用这些id回到聚簇索引取完整行数据。相比原始写法每扫一条都要回表一次性能提升是数量级的。类似的场景还有“基于游标的分页”WHERE id 上一页最大id ORDER BY id LIMIT 20。如果业务允许这种方案比LIMIT深翻页更优雅但需要前端配合改造。4.4 优化上线前一定要做的三件事改完SQL和索引不要急着上线。我踩过的坑告诉我至少要过三关第一关EXPLAIN验证执行计划。确认type明显改善key列是预期索引rows估算合理Extra没有filesort和temporary。第二关压测环境测试。拿生产数据脱敏后恢复到预发环境模拟线上真实流量跑一遍。注意观察锁等待和IO情况——有些优化虽然单个查询快了但可能引入更频繁的锁冲突整体吞吐未必提升。第三关灰度上线。先放5%~10%的流量观察对比优化前后的慢查询数量和接口延迟。如果出现性能回退立即回滚索引或SQL版本。5. 索引维护与常见失效场景排查5.1 索引下推被低估的优化利器MySQL 5.6引入的索引下推Index Condition Pushdown, ICP很多人不知道但它对联合索引的查询效率提升非常明显。原理一句话在存储引擎层遍历索引时直接把WHERE条件中能被索引列覆盖的部分下推到存储引擎进行过滤减少回表次数。举个例子联合索引(age, city)查询WHERE age 20 AND city 上海——age是范围查询city本来到不了索引层面过滤但有了ICP存储引擎在遍历索引时就会用city 上海过滤掉不符合条件的记录只有真正满足条件的才回表。判断ICP是否生效看EXPLAIN的Extra列有没有Using index condition。触发ICP有几个条件索引包含相关列、存储引擎支持该特性InnoDB默认支持、查询不是覆盖索引如果是覆盖索引本来就无需回表ICP意义不大。5.2 一张表建立多少个索引合适索引不是越多越好。每多一个索引意味着插入、更新、删除时都要多维护一棵B树写入性能直接受损。磁盘空间也从“几乎不用考虑”变成了“真金白银的成本”。我个人的经验规则单表索引数量5个以内是比较健康的超过8个就要反思是否过度索引。单索引字段数3~4个以内再多意义就不大了而且会显著增加索引体积。高频写表索引数量要更克制优先保证写入吞吐。有个很典型的反面案例运营后台为了“响应各种筛选条件”在十几列上各建了一个单列索引结果写接口从50ms涨到800ms。最后我把所有单列索引删除设计了两个联合索引覆盖主要查询场景写入恢复查询也没降速——因为大部分查询本来就是组合条件单列索引本来就不该被用上。5.3 MySQL 8.0新增的索引能力值得升级吗如果你还在用MySQL 5.7考虑升级到8.0时索引相关的改进有几个值得关注的亮点第一个是降序索引。5.7里ORDER BY a DESC, b ASC这种混合排序经常导致filesort8.0可以在索引定义时指定每个字段的排序方向让索引顺序和查询排序完全匹配。第二个是隐藏索引Invisible Index。可以把索引设为INVISIBLE优化器会忽略它但索引依然在维护。这给索引下线提供了很好的过渡手段——先在测试环境把目标索引隐藏观察慢查询是否有变化确认不依赖后再真正删除避免误删索引导致线上事故。第三个是索引跳过扫描Skip Scan。当联合索引的最左列是低区分度字段、且查询条件跳过它时优化器可以自动扫描所有不同的最左值来“模拟”索引查找部分缓解了最左前缀的局限性。不过这个特性有前提条件效果因数据分布而异不要期望太高。5.4 索引失效场景速查表结合我日常排查的经验整理一份高频失效场景清单遇到问题可以直接对照场景示例是否失效对策索引列使用函数WHERE DATE(create_time) 2025-01-01失效改写为范围条件隐式类型转换WHERE user_id 10086user_id为字符串失效保持类型一致LIKE以通配符开头WHERE name LIKE %美食%失效改前缀匹配或全文索引OR连接非索引列WHERE a 1 OR b 2b无索引可能失效拆分为UNION!或WHERE status ! 1通常失效改写为IN (其他值)NOT INWHERE status NOT IN (1, 2)通常失效评估改LEFT JOIN联合索引跳过最左列索引(a,b)条件WHERE b 2失效调整索引或查询条件范围查询右侧字段索引(a,b)条件WHERE a 1 AND b 2b失效调整索引字段顺序表格里说的“通常失效”不一定绝对有些场景取决于优化器的成本判断和数据分布。比如OR如果两个条件都覆盖同一个索引也可能走INDEX MERGE。遇到具体问题还是以EXPLAIN的结果为准。5.5 在线DDL与索引维护的注意事项生产环境加索引最怕的是锁表导致业务中断。MySQL 8.0的INPLACE算法支持在大部分场景下在线加索引但有几个细节要留意在大表上加索引不管是不是INPLACE都会产生额外的磁盘IO和主从复制延迟。凌晨低峰期操作是标配。如果表有外键约束部分DDL还是会退化为COPY锁表时间不可控。ALGORITHMINPLACE不是万能药它只会减少锁的粒度不会消除对写入的短暂阻塞。我曾经在一张5亿行的流水表上加过索引当时预估耗时2小时我以为可以摸鱼了结果半小时后从库延迟飙到10分钟业务报警。后来我学乖了先用pt-online-schema-change这类工具在从库演练再在低峰期分批操作同时监控主从延迟。大表DDL永远要当成一次小型的运维变更来做而不是“执行一条SQL就完事”。另外索引维护不只是加索引和删索引还包括定期更新统计信息。如果表的增删改很频繁可以设置innodb_stats_auto_recalc自动重算或者周期性执行ANALYZE TABLE。否则就会回到第一章说的统计信息失真优化器做出不明智的选择。6. 独家经验我在SQL优化中用过的三板斧优化SQL这件事说难也难说简单也简单。这些年我沉淀了一套自己的排查套路每次遇到性能问题基本按这个顺序走第一板斧先看执行计划不看代码。不管业务逻辑写得再复杂最终落到数据库上就是一条SQL的执行计划。先EXPLAIN把type、key、rows、Extra看明白问题基本就定位了一半。第二板斧揪出回表和排序。90%的慢查询都能归因到两个操作回表太多、排序太慢。回表多就考虑覆盖索引排序慢就考虑联合索引的有序性。这两个点优化到位大部分SQL都能跑回百毫秒以内。第三板斧验证验证再验证。用生产数据量级做压测用EXPLAIN ANALYZE看实际执行代价灰度上线后对比监控指标。没有验证过的索引设计都是纸面优化。最后再分享一个小技巧优化完别忘了清理冗余索引。有时候为了覆盖某个新场景你加了个索引旧的索引可能就不再被任何查询利用了。用sys.schema_unused_indexes视图可以查出来哪些索引从未被使用过定期清理这些“僵尸索引”既能省空间还能提升写入性能。我每次上线索引调整后都会顺手查一下这个视图清理效果非常显著。索引优化说到底就是一场“用空间换时间”的平衡艺术。它不是堆索引的数量而是精准匹配查询模式让每一棵B树都被用在刀刃上。希望这篇实战经验能帮你少踩几个坑。
