身边经常有同事跑过来问我“某条SQL查了十几秒线上都报警了有没有什么速效救心丸”说实话大部分时候我不用看具体业务代码光看那几条SQL的写法就能猜出七八分问题在哪。MySQL的查询优化本质上是让优化器“走对路”、让索引“用得上”、让数据“少折腾”。这篇文章我想把平时排查和优化SQL的经验完整梳理一遍不绕弯子直接讲怎么定位慢查询、怎么读懂执行计划、怎么改索引和写法以及几个从几秒优化到几十毫秒的实战案例。适合刚接触数据库优化的开发同学也适合那些写了好几年SQL但没系统整理过优化口诀的同行。1. 定位慢查询别凭感觉先让数据库告诉你哪里慢很多人在优化SQL时有个坏习惯拿一条看起来“可疑”的SQL就开始猜改来改去浪费半天时间。我自己的做法永远是先做两件事——开慢查询日志跑EXPLAIN。没有这两步后面的一切优化都是盲人摸象。1.1 慢查询日志怎么开阈值怎么定慢查询日志是MySQL自带的“行车记录仪”它会记录所有执行时间超过阈值的SQL。先说最简单的开启方式。在配置文件里加上slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1这几个参数里long_query_time 1表示超过1秒的SQL会记录生产环境我一般先设为1秒如果慢SQL特别多再逐步收紧到0.5秒甚至0.2秒。注意long_query_time的单位是秒支持小数。还有一个容易忽略的参数是log_queries_not_using_indexes它会额外记录那些即使执行很快但没走索引的SQL。这类SQL在数据量小时感受不到问题等表到了千万级就是一颗定时炸弹。所以我建议把它一并打开。我见过不少团队开了慢查询日志却没人看日志文件滚了好几个GB等于没开。正确做法是配合mysqldumpslow工具做聚合统计。比如mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log-s c表示按SQL出现的次数排序-t 10表示只看前10条。这样能把那些“单次不快但高频执行”的SQL也揪出来。要知道查询效率优化不能只盯单次执行时间还要看总体的资源消耗。一条SQL执行30毫秒但每秒被调用上千次比一条执行3秒但只在凌晨跑一次的SQL更值得优化。1.2 看懂EXPLAIN执行计划先抓住这几个字段拿到慢SQL之后下一步就是执行计划分析。用法很简单在SQL前面加EXPLAIN关键字EXPLAIN SELECT user_id, order_amount FROM orders WHERE status 1 AND created_at 2025-01-01;执行计划会输出一张表字段很多但真正需要反复盯住的其实只有四个type、key、rows、Extra。type是访问类型从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL意味着全表扫描通常这就是性能瓶颈的核心信号。key表示实际用到的索引名如果显示为NULL说明这条SQL没走任何索引rows是MySQL估算需要扫描的行数这个数字越大查询成本越高Extra里如果出现Using filesort或Using temporary说明排序或者去重、分组操作在硬盘临时表里完成这也是常见的性能杀手。EXPLAIN是“估算”而不是“实际执行”所以它是用来判断查询路径的。路径决定了90%的性能剩下10%要靠实际运行时间验证。1.3 type级别的晋级之路从ALL到const意味着什么我习惯把type级别看成一条“晋级路线”。最差的是ALL全表扫描相当于在图书馆里一本书一本书翻翻完整个书架才能找到想要的那本。index是全索引扫描虽然不翻整本书了但要把整个目录页从头看到尾比ALL略好但在大表上依然不可接受。range指的是索引范围扫描比如、、BETWEEN、IN这类查询已经能用上索引快速锁定一个区间。ref是非唯一索引等值匹配比如WHERE status 1可能匹配出多行但总比全表扫好太多。eq_ref是唯一索引等值匹配多发生在主键或唯一索引作为JOIN关联条件的场景。const是最高级别之一表示通过主键或唯一索引查一行MySQL直接把它当作常量处理速度快到极致。在日常优化里我们的目标很简单让SQL至少从ALL和index提升到range以上能把ref和eq_ref用上就是性价比最高的操作。我之前遇到过一个查询光是加上一个联合索引type直接从ALL变成ref查询时间从2.8秒降到80毫秒这个收益是立竿见影的。2. 索引优化让查询走上正确的路可以说SQL优化有80%的工作是在跟索引打交道。索引选对了SQL怎么写都很快索引选错了SQL怎么写都慢。这一节我把最常踩的坑和最有效的策略聊透。2.1 最左前缀法则联合索引的灵魂联合索引可以理解成一本按字母顺序排列的字典查询时只能从第一个字母开始按顺序匹配。也就是说对于(a, b, c)这样的联合索引以下规则必须牢记查询条件里没有a那么b和c都白搭索引不会完整生效。查询条件里有a但没有b那c也无法充分利用。如果查询条件里有a和c而跳过b那么c仍然无法走完整索引只能做部分过滤。举个实际例子假设表里有联合索引(user_id, status, created_at)下面这些SQL的索引命中情况完全不同-- 走索引且三个字段都用上了 SELECT * FROM orders WHERE user_id 1001 AND status 1 AND created_at 2025-01-01; -- 走索引但只用到 user_id 和 statuscreated_at用不上 SELECT * FROM orders WHERE user_id 1001 AND status 1; -- 走索引但只用到了 user_id SELECT * FROM orders WHERE user_id 1001 AND created_at 2025-01-01; -- 不走完整索引因为跳过了第一列 user_id SELECT * FROM orders WHERE status 1 AND created_at 2025-01-01;最后一个查询我见过太多次了。开发同事觉得“建了联合索引怎么查还是慢”一看索引定义才发现查询条件根本就没带着联合索引最左边的字段。优化方向要么给单列status建索引要么调整原来的联合索引顺序把高频查询条件往前放。2.2 六个经典索引失效场景一个比一个隐蔽我总结过一张“索引失效避坑清单”这六类场景占了实际工作中90%的问题第一个是LIKE模糊查询以通配符开头比如LIKE %关键词。这种写法走不了索引因为B树的索引结构是按前缀排序的你从中间开始匹配它没法利用有序性。如果业务确实需要可以考虑用全文索引或者换一种查询设计。第二个是在索引列上做函数运算比如WHERE DATE(created_at) 2025-01-01。MySQL优化器一般不会自动把函数表达式改写为可索引的等价形式索引列被函数包裹后就会失效。正确写法是WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00把范围换算成裸列比较。第三个是隐式类型转换。比如索引字段是字符串类型你写WHERE phone 13800138000MySQL会把字段隐式转换成数值再比较索引照样失效。解法很简单写SQL时保证参数类型和字段类型一致。第四个是OR连接的条件里有一部分没索引。比如WHERE user_id 1 OR status 2如果只有user_id上有索引而status上没有优化器干脆放弃索引改走全表扫描。解法是给所有OR条件都建好索引或者用UNION ALL拆开。第五个是NOT IN和!。这类负向查询在多数情况下不走索引因为B树更适合做正向的范围查找。业务上要排除少量数据时可以反着查把要的数据用IN查出来再在应用层做差集。第六个是联合索引跳过中间列。前面已经说过跳过中间列会导致后续列失效但在实际排序和分组场景里这个规则会变复杂需要具体分析。2.3 覆盖索引与回表少一次磁盘IO就快一倍InnoDB的表是聚簇表主键索引的叶子节点直接存整行数据而普通索引二级索引的叶子节点只存索引列和主键值。也就是说当你用普通索引查非索引列时MySQL会先查到主键再拿着主键去主键索引里“回表”一次才能拿到完整行。回表不是每次都能避免但如果你的查询只需要少数几个列完全可以把这几个列塞进同一个联合索引里让索引的叶子节点直接包含需要的所有列这就是覆盖索引。举个例子SELECT user_id, status FROM orders WHERE user_id 1001 AND status 1;如果表上有联合索引(user_id, status)那么这条SQL的查询结果全部能从索引里拿到Extra字段里会显示Using index表示不需要回表。这种优化在数据量大时效果非常明显因为InnoDB的页大小是16KB索引项比整行数据小得多同样的IO能读入更多索引条目扫描效率翻倍。我自己在做覆盖索引优化时还有个小技巧把经常一起查询的字段按“查询过滤条件优先、排序字段次之、返回列最后”的顺序组织联合索引。这样既保证过滤高效又尽量让排序和返回全部命中索引。3. SQL写法优化别让数据库做无用功索引不是万能药SQL本身的写法同样决定了优化器能不能做出聪明的决策。很多时候改几条写法习惯比加索引更快见效而且不需要动表结构。3.1 少用SELECT *不只是节省流量这么简单很多开发者写SELECT *是图省事但它的代价其实不小。首先MySQL要把所有列的数据都读出来传回应用层网络传输和内存占用都会放大。其次SELECT *会导致覆盖索引失效——因为你查了索引里没有的列只能回表。我之前优化过一个报表查询把SELECT *改成只查需要的两列后配合索引调整整体耗时直接降了60%。我知道有些团队会说“我表里字段不多无所谓”。可业务表是会长的今天只有五六列明年就可能二十多列。写查询的时候明确列出需要的列既能让优化器有更多选择空间也方便后期通过覆盖索引提效这是一个低成本高回报的习惯。3.2 IN、EXISTS和JOIN没有银弹要看数据分布IN和EXISTS的争论在社区里从来不缺。我个人的经验是不要死记“小表驱动大表”这句话而是要理解它们各自的执行逻辑。旧版MySQL对IN子查询的优化不够好现在的版本已经做了很多改写。但有一点始终没变当子查询结果集很大时IN的代价会上升。反过来EXISTS更适合外层表数据少、内层表大的场景。不过如果你追求稳妥我更推荐直接写成JOIN让优化器通过表关联的方式统一处理。举个例子-- 用 EXISTS SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id AND o.amount 1000); -- 改成 JOIN 语义更清晰 SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON o.user_id u.id AND o.amount 1000;哪种更快取决于users和orders的数据分布以及orders上的索引情况。如果orders.user_id有索引JOIN通常不会差。关键是执行计划出来以后重点看有没有走索引、有没有创建临时表而不必纠结某一种写法“一定比另一种好”。3.3 深分页的噩梦LIMIT 100000, 10为何特別慢分页查询人人都写过但深分页的坑很少有人提前意识到。LIMIT 100000, 10的意思是跳过前面100000行再取10行。MySQL的实现方式是先扫描100010行然后丢掉前100000行只返回最后的10行。你想想随着页码变大MySQL要扫描的无效行数越来越多查询自然越来越慢。这个问题的解法主要有三种第一种是“延迟关联”先用覆盖索引找到目标主键再把原表关联回来SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE user_id 1001 ORDER BY id LIMIT 100000, 10 ) tmp ON t.id tmp.id;子查询里只查id列能走索引覆盖扫描速度快很多。拿到10个目标主键后再通过主键关联回原表取整行。实测中这个写法的深分页性能往往提升一个数量级。第二种是“记录上次位置”也就是游标分页。如果业务允许用WHERE id last_id ORDER BY id LIMIT 10替代页码这是最优雅的深分页方案。第三种是限制可翻页深度比如业务上只允许用户看前100页超过后直接提示。这个更像产品策略但很多场景下是可行且合理的。4. 实战优化案例从3秒到30毫秒我都做了什么理论讲再多不如来看两个真实案例。下面都是我在实际工作中处理过的模拟场景数据结构做了简化但优化思路是完整的。4.1 订单列表慢查询一条索引的收益有多大某系统的订单列表接口线上反馈打开要好几秒。原SQL大概是这样的SELECT * FROM orders WHERE status 1 AND created_at 2025-03-01 ORDER BY created_at DESC LIMIT 20;订单表当时已经有几百万行。第一眼看这个查询也不算复杂为什么不快直接执行EXPLAINtype显示为ALLrows扫描了快200万行。原因很明确status和created_at都没有合适的索引排序还要另起炉灶。我没有急着盲目建索引而是先分析这个接口的核心访问模式按状态过滤、按时间倒序、取最近一页。于是设计联合索引(status, created_at)让等值条件走前缀范围条件和排序都落在同一棵索引树上。加完索引后再跑EXPLAINtype变成了refrows降到了几百行接口从2.8秒降到了40毫秒左右。这里有个细节值得说联合索引里我把status放在前面因为它是一个等值筛选条件如果反过来把created_at放前面那么status的过滤就无法在索引里直接完成了。这正是“等值条件在前、范围条件在后”的设计原则。4.2 JOIN查询优化让驱动表先瘦身另一个案例是统计报表模块里的一张临时大表关联查询经常跑到5秒以上。原始SQL经过简化是这样SELECT a.order_no, a.order_amount, b.user_name FROM order_his a LEFT JOIN user_info b ON a.user_id b.id WHERE a.pay_time 2025-01-01 AND a.status IN (1, 3, 5);执行计划里能看到第一行和第二行的type分别是ALL和rangeExtra里出现了Using join bufferBlock Nested Loop说明MySQL在拿驱动表的一批结果去关联被驱动表时没法高效用上索引只能走内存里的嵌套循环。优化思路分两步。第一步给被驱动表user_info的id加主键索引实际主键本来就存在问题是出在驱动表这边第二步给order_his表加联合索引(pay_time, status, user_id)让第一步过滤出来的结果集足够小。驱动表小了关联次数才会少这是JOIN优化的核心。修改后执行计划的Extra里不再出现Using join buffer两表关联时都走了索引总耗时降到了400毫秒以内。这个案例告诉大家JOIN慢的时候先别急着怼SQL写法回头看看两张表各自的过滤条件是否有索引支撑。4.3 深分页优化实战从数据到接口一次改完还有个典型的深分页案例某个管理后台的“流水列表”支持翻到几千页。用户翻到第1000页的时候接口直接超时。原始SQL类似SELECT * FROM payment_logs WHERE user_id 2001 ORDER BY id LIMIT 99900, 100;user_id上有索引但LIMIT 99900, 100意味着MySQL要先找到99900行再跳过整个查询要扫过的行数接近10万。优化方案就是前面说的延迟关联SELECT p.* FROM payment_logs p INNER JOIN ( SELECT id FROM payment_logs WHERE user_id 2001 ORDER BY id LIMIT 99900, 100 ) tmp ON p.id tmp.id;优化后内层查询只扫描索引列id和user_id效率比取整行高出很多。整个接口响应从8秒降到了300毫秒以内。不过我也要说明这属于“治标”真正的长期方案还是改成游标分页让前端每次都传最后一条记录的ID从业务底层消除深翻页问题。5. 常见问题与排查技巧实录这一节我想把平时高频遇到的问题浓缩成一张速查表再分享几个不太容易从文档里学到的“野路子”心得。这些内容很多是我踩过坑之后才慢慢悟出来的。5.1 SQL优化自查清单从日志到执行计划的一路排查我在帮团队做SQL Review的时候习惯按下面这个顺序逐条检查你也可以直接拿来用。检查项关键观察点常见解法慢查询日志高频慢SQL列表、平均耗时打开log_queries_not_using_indexesEXPLAIN的typeALL、index、range、ref针对性调整索引或写法EXPLAIN的key是否为NULL检查查询条件与索引是否匹配EXPLAIN的rows扫描行数是否过大优化过滤条件缩小扫描范围Extra出现Using filesort排序未用索引将排序字段加入合适的联合索引Extra出现Using temporary分组去重建了临时表重写SQL或调整索引避免临时表字段与查询条件类型隐式类型转换统一参数和字段类型深分页SQLLIMIT偏移量过大延迟关联或游标分页这套清单基本能覆盖日常80%的优化需求。每次拿到一条慢SQL按表格逐项排查大多数瓶颈都能浮出水面。5.2 我踩过的坑和总结的技巧第一个坑联合索引不是建得越多越好。有一次我给某张表一口气加了五六个索引查询是变快了可写入和更新变慢了因为每次修改都要同步维护索引表空间也膨胀得厉害。后来我删除冗余索引仅保留两三个关键联合索引整体表现反而更好。记住索引是“空间换时间”的典型也是要付出维护成本的。第二个坑EXPLAIN只是估算不是真相。它在数据量小、统计信息不准的情况下可能会给出误导性的结果。碰到EXPLAIN看起来很漂亮但实际执行很慢的情况我会先在真实数据量下重新ANALYZE TABLE更新统计信息然后必要时用FORCE INDEX来做对照实验找出真正的性能瓶颈。第三个技巧合理利用index condition pushdown索引条件下推。在MySQL 5.6及以上版本当二级索引无法完全覆盖WHERE条件时优化器可以把部分条件“下推”到索引层去过滤减少回表次数。这意味着即使某些查询不能走完全覆盖索引也别轻易放弃联合索引设计因为下推能弥补一部分缺口。第四个技巧优化不只看单条SQL更要看SQL模式。有些慢SQL是临时爆出来的更多是业务长期积累的坏写法导致的。我建议团队把优化经验固化为代码规范的检查规则比如禁止SELECT *、禁止在索引字段上做函数运算、禁止LIKE %xxx这种写法。每一次SQL Review都按清单过一遍问题才会越来越少。5.3 写给新手的最后一句经验优化SQL这件事说到底是“理解数据的存储方式再顺应它的脾气去查询”。B树擅长按顺序找你偏要从中间开始匹配那肯定慢聚簇表的叶子里存着整行数据二级索引都要回表你偏要查一堆没必要的列那自然快不了。关键是养成两个习惯拿到慢SQL先看执行计划设计索引先想清楚查询模式。我把这套方法反复用在不同项目里每次都管用希望也能帮到你少走一些弯路。