先说明一下这篇不是给纯新手的什么是SQL科普而是假定你已经会写基本的SELECT、JOIN、GROUP BY但是在实际项目里遇到过数据量一上来查询就卡成PPT这种问题。我梳理的是自己这几年在几个偏重读写混合的业务系统里从索引设计、执行计划解读到慢查询治理的一整套实战打法。文章里不会出现某个具体生产库的真实表名和脱敏数据但所有思路、套路、坑点都是可以直接搬到你自己项目里用的。1. 先搞清楚你的查询到底慢在哪里很多朋友一上来就喜欢说这个SQL太慢了帮我加个索引。这话听着没毛病但真要动手优化第一步绝不是写CREATE INDEX而是先把慢这件事拆开看清楚。SQL慢有两类完全不同的原因处理方式是天差地别的。一类是它本来可以不慢但因为写法烂导致慢。比如在索引列上做了函数运算或者把OR条件写得让优化器直接放弃索引再或者一次性把所有历史数据都拖出来再ORDER BY。这类问题属于白给的性能——你什么都不用加光改改SQL写法性能就能提升几十倍甚至几百倍。另一类是它确实要做那么多事只是没有趁手的工具。比如你要在几千万行里做范围筛选、要频繁按某个非主键字段做分组统计或者表之间关联的字段压根没有索引。这时候才轮到加索引上场。判断到底是哪一类标准动作是看执行计划。以MySQL为例最简单的做法是EXPLAIN SELECT ...你的查询...;重点看三列type、key、rows。type是从const到ref再到ALL的一条性能降级链路如果出现ALL说明是全表扫描key是实际用到的索引rows是预估扫描行数。当然EXPLAIN里的rows是估算值不是精确值但它能告诉你一个量级——如果预估扫800万行哪怕只用了200毫秒也算不上健康因为它会随数据量继续恶化。我还习惯再加一步EXPLAIN ANALYZEMySQL 8.0Percona和MariaDB也有类似能力。这个是真去执行一遍把实际耗时、实际扫描行数、每一层操作的耗时都给你打出来。比普通EXPLAIN多一层信息也能验证优化器的估算到底准不准。遇到EXPLAIN说走索引但实际还是很慢的情况基本都得靠它来定位。提示日常排查慢查询先开慢查询日志把long_query_time设置为1秒甚至0.5秒收集真实的慢SQL样本。别凭感觉觉得这条SQL好像慢先让日志告诉你哪些SQL才是真正需要治理的钉子户。2. 索引策略不是加得越多越好而是每一列都用在刀刃上索引这块我最想强调的一句话是索引不是银弹你设计索引的时候想的不应该是这个表哪些列会被查而应该是这个表最常用的查询路径是哪几条。我之前接手过一个订单相关的业务模块表里有几百万行数据某天运营同事反馈后台筛选订单越来越慢。我翻了一下建表语句发现除了主键之外竟然只给status字段建了一个单列索引。所有筛选条件动辄就是status create_time user_id混合出现导致每次查询都要拿status索引捞出一大堆中间结果再回表过滤。这种时候单列索引形同虚设——你通过status只能把范围缩小到比如已退款订单但已退款订单可能还是几十万行然后还要在几十万行里逐条比对create_time和user_id不慢才怪。正确的做法是先分析业务里最频繁的查询组合。假如80%的查询都带着status和create_time那在该查询路径下一个(status, create_time)的联合索引就非常合适。而user_id如果只是偶尔出现在筛选里那可以再评估一个单独的(user_id)索引或者干脆把它放进联合索引的末尾看具体情况。设计联合索引时有一个几乎所有人都会踩的坑把区分度最高的列放前面。这句话不假但更重要的是理解联合索引的最左前缀规则。MySQL的联合索引是按索引定义的列顺序先按第一列排序再按第二列排序。你建了(a, b, c)索引那么查询条件里只有a的时候能走索引只有a, b也能走但只有b或只有b, c就不行。所以你要保证的是高频查询条件的列顺序必须与联合索引定义的列顺序匹配且不能跳过中间的列。不过区分度高的列放前面这个经验也不是万能的。举个例子如果一张表里status只有3种值create_time却是个高基数的时间列按区分度优先的直觉可能你会把create_time放前面。但你的业务查询如果90%都强制带status 已完成这个条件那从执行计划上看把status放前面反而能更快地裁剪数据。好的方案是拿真实业务数据做一次成本验证而不是拍脑袋。再补充一个点如果某列在查询里经常做范围查询比如create_time BETWEEN ...那这个字段放在联合索引里要注意位置。MySQL对联合索引中第一个范围条件的后续列是无法继续用于索引中进一步过滤的。比如索引(status, create_time, user_id)如果create_time用了范围匹配那么user_id这一列只会被用来回表后过滤索引本身帮不上忙。所以设计时尽量把精确匹配的列放前面范围列放后面把每一次索引查找的性价比最大化。2.1 回表、覆盖索引和索引下推搞清楚这仨就能少踩一半坑回表是索引优化里绕不开的概念。简单说走二级索引查到主键值后再拿着主键去聚簇索引里取整行数据这个过程就是回表。回表本身不是问题问题是回表次数太多。如果一个二级索引把几十万行都筛出来了那就要回表几十万次性能自然崩。覆盖索引的思路就是让查询需要的所有列都包含在索引本身里。这样查询走到索引就完事了不需要回表。举个常见的例子统计某状态下当天的订单数如果只查order_id和status那一个(status, create_time, order_id)的联合索引就能做到索引覆盖。索引下推Index Condition PushdownICP是MySQL 5.6引入的一个优化原理是使用联合索引查询时对于索引中包含但不在最左前缀范围内的列存储引擎层可以先对这些列做条件过滤减少回表次数。以前只能把索引中所有匹配的行全部捞出来再回表后过滤现在可以先在索引层面就把不满足条件的行干掉。很多情况下你不需要把它背下来只要知道联合索引里多塞一个查询条件引用的列哪怕不是严格的最左前缀匹配也可能因为ICP而显著减少回表量所以别急着否定一个看似没用到的索引列。2.2 索引的代价写入变慢、占用空间大、优化器选择困难我说不是加得越多越好不是空话。每个索引都是B树写入数据的时候除了更新聚簇索引所有二级索引也要同步更新。大事务里每多维护一个索引插入、更新、删除的开销就肉眼可见地涨。特别是那种并发写密集的表索引多了之后反而可能拖垮TPS。另外索引太多还会让优化器犯难。SQL里条件复杂时可用的索引组合变多优化器要评估的路径也变多有时它会选错索引。我之前就遇到过一次明明给(status, user_id)建了联合索引某条查询却走了status单列索引导致扫描量暴涨。也不是优化器傻而是它的统计信息有偏差。解决办法一般是ANALYZE TABLE刷新统计信息或者用FORCE INDEX临时指定但根治还是要让索引设计更精简减少优化器的选择噪音。所以我的建议是先按核心查询路径建索引然后每个季度做一次索引使用统计。MySQL的performance_schema或sys.schema_unused_indexes可以直接查哪些索引从未被使用该删就删别心疼。留着那些一年都用不上一次的索引纯粹是给写入和存储添堵。3. 写完SQL先别急着跑这些写法问题比缺索引更致命很多时候应用慢在SQL写法上而不是索引上。我下面列几个生产环境里高频踩中、又非常好修的写法问题。修完你会发现有时候根本不用增加任何索引。第一个高频坑在索引列上使用函数。SELECT * FROM orders WHERE DATE(create_time) 2024-11-11;这段SQL看着很直观但它意味着MySQL对每一行的create_time都得先执行一次DATE()函数再把结果拿去和字符串比较。函数处理完的结果显然不可能直接走create_time的索引。结果就是哪怕create_time有索引这个查询也会全表扫描。改成下面这样就能完美命中索引SELECT * FROM orders WHERE create_time 2024-11-11 00:00:00 AND create_time 2024-11-12 00:00:00;第二个高频坑隐式类型转换。比如user_id字段是varchar类型查询时却传了整型WHERE user_id 123456MySQL会尝试把字段转成数字导致索引失效。这类问题在代码里尤其隐蔽因为很多ORM框架传参时并不会强制类型。排查方法很直接EXPLAIN里看到key是NULL但条件列明明有索引十有八九是类型对不上。第三个高频坑OR条件。如果OR连接的每个子条件都能独立走索引那问题不大但实际情况是只要其中一个条件不走索引整个查询都可能退化成全表扫描。稳妥的做法是把OR改写成UNION ALL或者确认所有分支都命中了索引。另外MySQL 8.0的优化器对OR的处理比老版本聪明了但遇到复杂组合时我仍然会谨慎。第四个高频坑大偏移量的LIMIT。比如后台翻页翻到第10000页LIMIT 100000, 20MySQL会先把前面100000行全部扫出来丢掉再取20行。数据量一大你会看到越往后翻越慢的经典现象。解决方案不算高深要么记录上一页的最大ID用WHERE id last_id ORDER BY id LIMIT 20来做键集分页要么建立延迟关联先只查主键再用主键关联回原表取其他列。3.1 排序和分组别让临时文件跑出来ORDER BY和GROUP BY也是常见的性能黑洞。如果排序字段没有索引MySQL就不得不在内存或磁盘上建临时表做排序。数据量小的时候无所谓数据量一大filesort咔咔往磁盘写性能直接跌到冰点。举个例子查询条件是WHERE status 1 ORDER BY create_time DESC如果只有status的单列索引排序无法利用索引就会触发filesort。但如果你有(status, create_time)联合索引因为索引本身就是按statuscreate_time有序存储的所以查出来的结果天然有序排序直接省掉。同样的道理也适用于GROUP BY。很多做报表的SQL喜欢GROUP BY user_id后接COUNT(*)如果user_id有索引分组操作就能走索引扫描效率高很多。但要注意GROUP BY后面如果还跟着ORDER BY两者字段不一致也可能引发额外的排序。另外还要留意filesort是内存排序还是磁盘排序。可以通过EXPLAIN的Extra列看到Using filesort字样也可以用SHOW STATUS LIKE Sort_merge_passes;看是否有多次merge pass。出现Sort_merge_passes大于0说明排序量大到内存放不下这时候你要么优化排序字段减少排序涉及的字节数要么在排序字段上做文章让它走索引序。3.2 子查询和JOIN写法不同结果差好几倍在MySQL老版本里子查询的优化做得不太好IN (SELECT ...)经常被当成逐行相关子查询执行性能惨不忍睹。MySQL 5.6之后做了优化改进但也不是所有场景都能完美改写。我有一次排查一个报表SQL结构大致是SELECT ... FROM orders o WHERE o.user_id IN ( SELECT user_id FROM vip_users WHERE level 3 );执行计划显示子查询先全表扫了vip_users再去驱动外层orders效果很差。后来我改写成JOIN形式SELECT ... FROM orders o JOIN vip_users vu ON o.user_id vu.user_id WHERE vu.level 3;因为vip_users.user_id是主键orders.user_id也有索引JOIN成本明显更低。顺便一提改写时要注意去重问题——IN自带去重语义JOIN不去重如果外层表关联后有重复行需要加DISTINCT或先聚合子查询。关于JOIN本身还有一个老生常谈但极其重要的原则小表驱动大表。优化器一般会自己选但很多时候它选得并不完美。你可以在EXPLAIN里看第一行谁是被驱动表谁是驱动表手动通过STRAIGHT_JOIN来强制驱动顺序。但我不建议上来就上STRAIGHT_JOIN要基于执行计划和实际数据量做判断否则容易适得其反。4. 从一个真实案例出发手把手走一遍SQL优化全流程前面讲了不少理论可能有朋友觉得信息量有点大但不知道从哪入手。这里我拿一个脱敏后的模拟场景串一遍。假设有一个业务叫某跨平台内容系统的用户行为日志明细表缩写叫user_events核心字段包括id主键自增user_id用户IDevent_type事件类型channel渠道来源created_at事件发生时间ext_infoJSON扩展信息表里有约1200万行数据每天新增几万到十几万行。业务方反馈说后台查询某用户某时间段内的事件列表非常慢一条SQL要跑好几秒。原始SQL大概是SELECT id, user_id, event_type, channel, created_at, ext_info FROM user_events WHERE user_id 123456 AND created_at 2024-10-01 00:00:00 AND created_at 2024-11-01 00:00:00 ORDER BY created_at DESC LIMIT 20;我先拿EXPLAIN看了一眼结果是typeALL、rows1200万、keyNULL。以前只给event_type建过索引这条查询既用不上event_type也没有user_id和created_at的索引所以全表扫。这个场景里user_id的区分度远高于created_at而且查询条件里user_id是等值条件created_at是范围条件。按照前面说过的等值列放前面、范围列放后面原则我建了一个联合索引ALTER TABLE user_events ADD INDEX idx_user_created (user_id, created_at);建完索引再看执行计划typeref、keyidx_user_created、rows预估降到了几千。单次查询从原来的3000毫秒左右降到了80毫秒以内。这已经是量级上的变化。但还不算完。注意这条SQL查询了ext_info这个JSON大字段而索引里只有user_id和created_at。MySQL中途必然要回表把id、event_type、channel、ext_info都捞出来。回表发生在索引筛选之后结果集只是某个用户一个月内的事件量级大概是几百条回表成本不算高所以这里不强行追求覆盖索引了。如果有一天你想把这个列表页做成只显示摘要不显示ext_info那可以考虑把查询字段缩减成id, event_type, created_at并把联合索引扩成(user_id, created_at, id, event_type)实现覆盖索引但既然业务上要展示详情那就别过度设计。4.1 加完索引后再对SQL做一次瘦身索引加上去之后性能已经达标了但我又顺手做了一次SQL瘦身。原查询里SELECT *或者把.ext_info整个JSON拖回去在列表页只显示前20条时没啥问题但如果是导出场景一次性导出几万条JSON大字段会把IO拖爆。这里有一个非常实用的经验把重字段拆出去。比如这个业务场景里ext_info平时用得少可以考虑拆分到另一张扩展表按主键关联查询。列表页只查主表核心字段详情页再按需读取扩展字段。这个动作在OLTP系统里效果尤为明显因为行变短了每页数据页能容纳更多行全表扫描的IO成本显著下降。4.2 别忽略统计信息和慢查询日志优化完成后我又嘱咐了维护该项目的同事两件事一是每次大批量导入历史数据之后记得执行ANALYZE TABLE user_events;让优化器拿到最新的统计信息否则索引可能有但不用二是持续观察慢查询日志看这条SQL有没有稳定在100毫秒以内。我见过很多团队加完索引跑了一次觉得快了就再也不管了。过了俩月数据翻倍统计信息过期优化器选错索引又慢了然后开始怀疑索引失效了。其实EXPLAIN SELECT ...跑一遍就全明白了大多数索引失效并不是真失效只是优化器基于错误的统计信息做了错误选择。5. 常见问题排查实录与避坑速查我把这几年大家问得最多、同时也是我自己踩过的坑整理成一个速查表每一条都对应一个真实发生过的场景。现象可能原因排查方法解决手段明明有索引EXPLAIN却显示keyNULL索引列上用了函数或隐式类型转换查看WHERE条件中字段的类型和写法改成 ... AND ...的区间写法保证传给数据库的参数类型和字段一致走索引了rows依旧很大联合索引顺序设计不合理范围列挡住后续等值列分析索引列的实际使用顺序重新设计联合索引等值列优先查询偶尔快、偶尔慢统计信息过期优化器选择了非最优索引执行ANALYZE TABLE后复测优化索引数量必要时使用FORCE INDEX临时救场翻页越深越慢大偏移量LIMIT导致扫描大量无效行查看SQL中的LIMIT偏移量改成键集分页或延迟关联多表关联查询很慢关联字段缺索引驱动表选错EXPLAIN查看type和rows补齐关联字段索引分析驱动顺序ORDER BY触发Using filesort排序字段不在索引中或索引列顺序与排序方向不匹配查看Extra列让排序字段参与索引注意ASC/DESC与索引列顺序的匹配数据量不大但查询还是很慢表行宽太大随机IO次数多查看行平均长度拆大字段冷热数据分离写入变慢TPS下降二级索引过多写入维护成本高查sys.schema_unused_indexes删除长期未使用的索引这个表格不能覆盖所有场景但它提供了一个统一的思路遇到性能问题先看EXPLAIN再查rows和Extra最后才动索引或动SQL。不要调参一时爽事后火葬场。5.1 关于索引失效说一个最容易被误解的点很多文章动不动就说OR导致索引失效LIKE以%开头导致索引失效函数导致索引失效听起来像条件反射。实际上逻辑是数据库并不是讨厌OR而是因为OR分支里有一个分支无法走索引导致整个查询的路径选择被迫降级。同理LIKE %abc本身意味着你要的是以abc结尾的字符串这种查找在普通B树索引上就没有意义因为B树的叶子节点是按前缀有序的后缀内容无序可循。所以别死记失效这个词你该做的是去理解索引为什么能加速。B树加速的本质是有序——你可以用二分查找快速缩小范围。任何破坏有序性、或者使得查找目标无法利用有序性的写法都无法走索引。想通这一点遇到新场景就能自己判断了。5.2 那些看似高级的技巧索引跳跃扫描、函数索引MySQL 8.0支持索引跳跃扫描Skip Scan意思是当联合索引第一列的可区分值很少而查询条件没有包含第一列时优化器可以自动把第一列的每个不同值都尝试扫描一遍。这个功能听起来很强但它适合的场景其实是第一列枚举值非常少第二列选择性高的情况。如果第一列有几千个不同值跳跃扫描要跳几千次反而更慢。所以别指望它力挽狂澜还是要把索引设计做对。函数索引也值得提一嘴。如果你确实需要WHERE DATE(create_time) ...这种写法MySQL 8.0支持创建函数索引ALTER TABLE user_events ADD INDEX idx_func_date ((DATE(created_at)));这个可以解决函数导致索引失效的问题。但函数索引会额外存储计算后的值占空间。更推荐的做法还是把查询改成区间条件。函数索引是一种兜底手段不是首选。6. 关于执行计划我建议你多盯这三个字段做SQL优化这么久我认为最值得花时间啃的不是某个具体语法而是执行计划的阅读能力。每次有同事拿着一条慢SQL来问我基本都会让他做三步第一步看SELECT里的字段是不是都被用到了。如果经常多查出用不到的字段尤其是大文本列那就是在给数据库加无谓的负担。第二步看type。从好到坏大致是systemconsteq_refrefrangeindexALL。如果出现index和ALL你就要警惕了。index虽然也是在扫索引但它扫了整个索引并不代表高效。第三步看Extra里有没有Using temporary、Using filesort、Using where。这几个关键词分别对应临时表、文件排序、存储引擎返回后过滤。出现Using temporary尤其值得注意它往往意味着性能瓶颈在分组或去重逻辑上。举个例子我在排查一个统计类SQL时EXPLAIN显示Extra是Using temporary; Using filesorttype是ALL。原因就是GROUP BY字段没索引MySQL先建了临时表再做了排序。在GROUP BY字段上建了索引之后Using temporary和Using filesort都消失了整个查询快了近10倍。优化这事真不是靠背命令而是靠读懂数据库在干什么。你读懂了很多选择自然就清晰了。6.1 高性能SQL的共同特征最后总结一下我见过的高性能SQL无论是什么业务几乎都有这几个共同点查询字段精确能用索引覆盖就覆盖不拖无用字段。条件列类型干净字段类型不再发生隐式转换。等值优先、范围靠后联合索引的列顺序和查询条件匹配最左前缀完全命中。排序走索引ORDER BY的字段要么就是索引列要么有专门设计的索引支撑。小结果集驱动大结果集JOIN的驱动顺序合理内层表关联字段有索引。你拿这些特征去对照自己的慢SQL通常能发现一两个可改的点。很多时候不是数据库不行而是我们没把它的脾性摸透。7. 写在最后优化工作流的个人习惯做久了SQL优化我养成一个习惯每次上线任何SQL之前都必须过一遍EXPLAIN。哪怕只是改一个WHERE条件也花不了十秒钟但这个习惯救过我很多次防止了一些运行时才暴雷的问题。另外我会在代码评审阶段就让研发同事把SQL贴出来用EXPLAIN看一眼。遇到像SELECT *、大表OR、无索引JOIN这类明显问题当场就拦下来。虽然一开始同事会觉得管得太宽但后面线上事故变少了大家也就接受了。优化没有一招鲜的手段关键是形成自己的工作流先收集慢SQL再读执行计划找到真正的瓶颈点然后针对性改写SQL或调整索引。每一步做完都要用数据说话——优化前后对比执行时间、扫描行数、响应耗时而不是感觉快了一点。我见过太多朋友一上来就加了一堆索引反而把写入拖垮了或者把ORDER BY强行改成应用程序内排序结果代码逻辑变得更复杂。真正高效的方式是让每个索引、每条SQL都服务于清晰的业务路径让数据库在它擅长的场景下做它擅长的事。这条经验无论你用的是MySQL还是其他关系型数据库我觉得都通用。