接手过不少线上数据库的锅十次里有八次最后都落在一条SQL头上。页面卡、接口超时、凌晨的告警短信追根溯源大概率是一条没走索引的大查询或者一个排序排到磁盘上的order by。MySQL的SQL优化说白了就是跟引擎商量着来让它少翻数据、少做无谓计算、把该走的索引走起来。这篇是我自己整理的学习笔记第4篇专门聊SQL优化这块。适合刚接触索引概念、写SQL但没深究过执行计划的同学也适合被慢查询困扰、想系统梳理一遍优化思路的人。我会从思路到实操把索引设计、执行计划、写法细节这些硬骨头一个个啃下来。1. 优化前的思路梳理1.1 先搞清楚慢在哪一步很多人一上来就急着改SQL看到慢查询日志里有条查询耗时2秒立刻给它加个索引。结果加完发现一点用没有为什么因为你根本没搞清楚这2秒花在哪。MySQL执行一条查询时间主要消耗在几个环节网络传输、语法解析、查询优化器生成执行计划、存储引擎扫描数据、排序分组、返回结果集。大多数情况下瓶颈都在“扫描数据”和“排序”这两步。所以我的习惯是先看执行计划再决定改哪里。用EXPLAIN把SQL过一遍看它走的什么索引、扫了多少行、有没有filesort、有没有回表。这些信息都摆在明面上比瞎猜靠谱得多。还有一种情况是线上环境不方便直接EXPLAIN那就开慢查询日志把超过阈值比如1秒的SQL抓出来再逐条分析。慢查询日志默认是关的需要手动打开具体参数我后面会提到。还有一个很容易被忽略的点应用层的慢。有时候SQL本身一秒都不到但接口响应花了3秒问题出在连接池不够、网络往返太多、或者反复查询同一份数据。这种情况你再怎么优化SQL也白搭。所以定位慢要先分清是“SQL慢”还是“整个调用链慢”别一股脑把锅扣给数据库。1.2 优化原则先定位、再分析、后改动我给自己定了个三条铁律这几年靠它们少踩了很多坑。第一只优化真正慢的SQL。别逮着一条执行只要5毫秒的查询使劲折腾收益为零还容易引入新问题。优化的前提是有明确的性能痛点最好有慢查询日志或者监控数据作支撑。第二用数据说话。优化前记录耗时、扫描行数优化后再对比一次让效果看得见。我习惯把EXPLAIN的结果和实际执行时间一起截图存档方便后来复盘。第三小步快跑一次只改一个变量。加了索引就别同时改SQL写法否则出了问题你根本不知道是哪一步导致的。在这三条基础上大部分SQL优化都可以按一个固定套路走先用慢查询日志定位目标SQL再用EXPLAIN看执行计划接着判断是索引问题、写法问题还是表结构问题然后针对性调整最后回归测试。这套流程说起来简单但每一步都有细节后面几个章节我会逐个展开。记住SQL优化不是玄学它是一套有依据、可验证的方法论。2. 索引SQL优化的一等功臣2.1 主键索引和唯一索引到底啥区别面试的时候这个问题高频到不行实际工作中也经常有人搞混。先明确一点主键索引和唯一索引都是“唯一性约束索引”的结合体但两者有本质区别。主键索引是聚集索引也就是说InnoDB表的数据行本身就是按主键顺序物理存储的。一张表只能有一个主键主键列不允许为NULL。你建了主键整张表的数据就按这个键的物理顺序排布查询用主键找数据是最快的路径直接定位到对应数据页不需要额外跳转。唯一索引则是非聚集索引它只是保证列值不重复允许有一个或多个NULL值MySQL里唯一索引对NULL的处理是“多个NULL是允许的”因为NULL ! NULL。唯一索引对应的数据是独立的索引结构叶子节点存的是主键值查询时先查索引树再通过主键回表去拿完整行数据多一步回表开销。简单总结一张表对比项主键索引唯一索引聚集索引是决定数据物理存储顺序否独立索引结构每表数量最多一个可以有多个NULL值不允许允许多个NULL查询路径直接定位索引查找回表实操中要注意如果业务上确实需要一个唯一键比如用户表的手机号我建议“主键用自增id手机号建唯一索引”。这种设计的好处是主键短小、索引树紧凑写入性能好手机号作为唯一索引在查询时虽然多一次回表但业务上有唯一性校验需求这个代价是值得的。反过来如果用手机号直接做主键数据页物理排序会被随机字符串打乱插入时频繁页分裂写性能会明显下降。2.2 联合索引的最左前缀原则联合索引是SQL优化里最需要花心思的地方。很多人建索引很随性看到一个查询条件就建一个单列索引结果一张表上挂了六七个索引写入慢、占用空间大查询还不一定走。真正的做法是分析业务查询模式用联合索引覆盖多个查询条件。联合索引遵循最左前缀原则查询条件必须命中索引的最左列或最左列的连续组合索引才会生效。比如我建了一个(idx_a, idx_b, idx_c)的联合索引那查询条件里用了idx_a能走索引用了idx_a和idx_b能走用了idx_a、idx_b和idx_c也能走但只用idx_b或者只用idx_c索引就用不上。这个原则背后的逻辑其实不难理解。联合索引的B树排序规则是先按第一列排序第一列相同再按第二列排以此类推。就像查字典先按拼音首字母排首字母相同再按第二个字母排。你直接要查第二个字母开头的单词字典根本无从下手只能从头翻到尾。所以在设计联合索引时列的排列顺序非常重要。我常用的判断标准是区分度高的列放前面等值查询的列放前面范围查询的列放后面。举个例子一个订单表经常按“用户id 下单时间范围”查询那就建(user_id, order_time)联合索引user_id等值匹配放前order_time做范围过滤放后。这样既能利用索引快速定位到某个用户的数据又能在索引内部完成时间范围的筛选效率是最高的。2.3 索引失效的典型场景这部分基本是SQL优化里老生常谈但最容易踩的坑我把自己踩过和帮别人排查过的场景都列一下。第一对索引列使用函数或者表达式。比如WHERE DATE(order_time) 2025-01-01只要对索引列套了函数MySQL就无法用它去匹配B树只能全表扫描。正确写法是WHERE order_time 2025-01-01 AND order_time 2025-01-02。这个坑非常隐蔽因为DATE()这种函数看起来人畜无害但引擎确实用不上索引。第二隐式类型转换。比如phone字段是varchar类型查询写成WHERE phone 13800138000MySQL会把字段值转成数字去比较这就相当于在索引列上做了函数转换索引失效。同样字符串和数字的比较、字符集不一致的关联都可能触发这个问题。排查思路很简单确认字段类型和查询参数类型一致。第三LIKE以通配符开头。WHERE name LIKE %张%走不了索引但WHERE name LIKE 张%可以。原因还是老一套前缀开头才能按B树的顺序去匹配通配符在开头就破坏了前缀匹配能力。第四使用OR连接多个条件且其中一个条件没有索引。比如WHERE user_id 123 OR status 1如果status没有索引MySQL只能把两个条件都全表扫一遍再合并索引就废了。这种情况可以改成UNION ALL让每个分支各自走索引。第五范围查询右侧的列失效。还是那个联合索引(idx_a, idx_b, idx_c)如果WHERE idx_a 1 AND idx_b 100 AND idx_c 2那idx_c的等值条件就用不上索引了因为idx_b的范围条件后面的列无法保持有序匹配。这也是为什么范围列要放后面的原因。遇到这些情况我的建议是不要死记硬背理解B树的匹配原理比背书管用只要索引列的有序性在匹配过程中被破坏索引就会失效。你可以把索引想象成一排按顺序摆好的书架任何让你直接跳到中间某个位置去找的行为都必须从头翻。3. 用EXPLAIN把执行计划翻个底朝天3.1 慢查询日志怎么配置定位慢SQL最直接的手段就是开慢查询日志。MySQL的慢查询日志有几个关键参数我用8.0版本举例-- 查看当前设置 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的记录线上建议先设0.5测试 SET GLOBAL log_queries_not_using_indexes ON; -- 记录不走索引的SQL注意两点long_query_time的最小粒度是0.001秒单位为秒设置之后新开启的连接才生效已经存在的连接不会用新配置要重连一下。慢查询日志默认输出到数据目录下的hostname-slow.log文件也可以用SET GLOBAL slow_query_log_file指定路径。还有一个工具叫mysqldumpslow用来汇总分析慢查询日志按执行次数、耗时排序能快速找出“最值得优化”的SQL。命令大概是mysqldumpslow -s t -t 10 /var/log/mysql/slow.log意思是最耗时的前10条。这个工具对日志的汇总能力很强能把同模板SQL聚合到一起比如where条件不同的相同查询会被归为一类。我接手一个新系统时第一件事就是把慢查询日志开起来跑几天再用mysqldumpslow拉个排行对整个数据库的健康状况就有底了。3.2 EXPLAIN关键字段逐个看慢查询定位到了就可以用EXPLAIN解析。EXPLAIN SELECT ... 不需要真的执行查询它只输出优化器预估的执行计划。虽然MySQL 8.0还提供了EXPLAIN ANALYZE可以真正执行并输出实际耗时但日常分析先用EXPLAIN就够了。重点看这几个字段type是访问类型从好到差大致是system const eq_ref ref range index all。const和eq_ref意味着用主键或唯一索引精确定位最快ref是普通索引等值匹配也不错range是范围扫描可以接受index是全索引扫描通常不太好all是全表扫描最差。我深更半夜排查线上问题时最先瞄的就是这个字段type一旦出现all基本就是索引没走对。key表示实际用到的索引possible_keys是可能用到的索引。有时候possible_keys里有索引但key是NULL说明优化器评估后认为索引不如全表扫这时候要检查是不是数据量分布问题或者是索引失效了。rows是预估扫描行数这个数字越小越好。但不是绝对有时候rows预估有误差最终看EXPLAIN ANALYZE的实际行数更准。Extra字段信息量巨大看到Using filesort说明有额外排序看到Using temporary说明用了临时表这两项出现SQL基本慢成定局。看到Using index覆盖索引是最理想的意味着查询的数据直接从索引拿到连表都不用回。3.3 一个真实慢SQL的拆解拿一个我曾经排查过的例子来说。业务反馈某报表页面加载要十几秒从慢查询日志里捞出来一条SELECT order_id, user_id, amount, create_time FROM orders WHERE status 1 AND create_time 2025-01-01 ORDER BY amount DESC LIMIT 20;EXPLAIN的结果大概长这样type是allkey是NULLrows显示80万Extra里面有Using where和Using filesort。问题一目了然status和create_time都没有可用索引80万行全表扫描且按amount排序走了磁盘排序。我的优化动作分两步。第一步创建联合索引(status, create_time)让条件能走索引把扫描范围压缩到目标数据。第二步排序问题单纯靠(status, create_time)索引解决不了amount的排序因为索引列里没有amount排序还是在临时表做。又想了一招如果业务能接受排序条件改成create_time和索引顺序一致或者把联合索引建成(status, create_time, amount)这种形式让amount也进索引。最后方案是建了(status, create_time, amount)联合索引查询条件走索引amount直接按索引顺序取Extra里的filesort消失执行时间从12秒降到0.3秒。这个案例其实很典型优化不仅仅是“加索引”而是让索引结构同时满足过滤条件和排序条件一步到位。这种思维方式比背几个优化口诀重要得多。4. SQL写法优化那些容易忽略的小地方4.1 ORDER BY排序到底慢在哪排序是SQL优化里特别容易翻车的一环。MySQL排序有两种实现方式如果用到的索引天然有序Extra里不会出现filesort反之引擎会把数据先捞出来再在内存或磁盘上排序。当排序数据量超过sort_buffer_size默认256KB8.0设置时会落到磁盘上用临时文件排序那性能会断崖式下降。排序优化最有效的思路就是让排序走索引。比如业务常用“按用户查最近订单并按时间倒序”如果表上有(user_id, create_time)联合索引那么ORDER BY create_time DESC就能直接利用索引有序性因为同一个user_id下的create_time是天然有序的。没有这个索引MySQL就得把用户的所有订单捞出来再内存排序甚至磁盘排序。另外SELECT的字段如果太多、太大排序时也会受影响。因为MySQL排序时可能要把整行数据放进sort buffer字段越多占空间越多缓冲更容易被撑爆。优化手法是只SELECT需要的字段或者用“先查主键再回表取详情”的延迟关联方式。这些都算是排序优化的常见操作。4.2 深分页优化limit offset为何越翻越慢分页查询人人都写过但深分页的坑未必人人知道。LIMIT 100000, 20这种写法MySQL不是只取20条而是先把前100020条全部扫出来然后丢掉前10万条只保留最后20条。数据量大的时候前几次翻页可能还没感觉翻到几十页之后耗时是肉眼可见地上涨。为什么会这样因为limit offset在索引上的定位方式是从头开始数offset越大扫描的行数越多。即使走了索引这10万次回表操作也够数据库喝一壶的。我常用的优化方案有几种。第一种是延迟关联先通过索引查出主键再join原表取完整行。因为主键查询时可以先不碰真实数据行只扫索引树等确定好20条主键后再统一回表大大减少随机I/O。SELECT o.order_id, o.user_id, o.amount FROM orders o INNER JOIN (SELECT order_id FROM orders ORDER BY create_time LIMIT 100000, 20) t ON o.order_id t.order_id;第二种是记住上一页的位置用条件过滤代替offset。比如按order_id排序上一页最后一条是id99999下一页直接查WHERE order_id 99999 ORDER BY order_id LIMIT 20。这种方案要求排序字段连续且唯一通常用主键最合适。深分页问题越到数据量大越明显这两招能解燃眉之急但要想根治产品层面限制用户翻页深度才是关键。4.3 写法层面的坑习惯比优化更重要有一类SQL问题不是引擎不行是写的人太随意。这里面最常见的就是SELECT *。你以为少打几个字段省事代价是MySQL要把整行所有列都读出来即使业务根本不需要。更要命的是SELECT *加上ORDER BY时很可能因为包含了超长字段而导致排序缓冲紧张。我见到SELECT *的慢SQL第一反应就是先把它改成明确字段列表。还有函数运算的滥用。WHERE year(create_time) 2025这种写法在前面提过索引会失效其实即使没索引对每行做函数计算也是额外开销。应该改写为范围条件。这个习惯要尽早养成能直接写范围就别在字段上套函数。还有一个容易忽略的点多表JOIN时的驱动顺序。小表驱动大表是一条基本经验MySQL优化器一般会自动选择但在复杂查询里它也可能判断失误。遇到这种情况可以用STRAIGHT_JOIN强制驱动顺序或者改写查询结构让过滤条件更明确。不过这个操作要对执行计划很有把握才建议做否则容易适得其反。最后说一个很有意思的场景OR和去重的问题。有热词问“MySQL的or能去重吗”答案是OR本身不会去重去重要靠DISTINCT或GROUP BY而且OR的写法在索引利用上往往也差于UNION或IN。这也是我在实践中特别注意的地方能用IN代替OR就用IN能改UNION就不OR。细节养成习惯日积月累能省下很多事。5. 优化实践中的问题排查与经验沉淀5.1 常见问题速查表把日常运维和开发里高频踩坑整理成一张速查表遇到类似问题可以对照着看。问题现象常见原因推荐排查/解决方向查询越来越慢数据量增长索引没跟上检查执行计划重建或新增索引加了索引没效果索引列上用了函数/隐式转换改写SQL去掉函数运算排序特别慢没有可利用的有序索引设计联合索引覆盖order by深分页翻页卡offset过大回表过多延迟关联或基于游标的分页OR条件慢部分条件没有索引改写为UNION ALL或IN联表查询慢驱动表选择不佳调整sql优化器构造更好的过滤条件写入变慢索引过多或表结构不合理精简索引评估冗余索引间歇性慢缓存失效/连接池问题观察监控分析慢查询日志趋势这张表只是索引真正遇到问题还是要回到EXPLAIN和慢查询日志这两条线上去拿证据。5.2 几条实战经验希望你能少走弯路做SQL优化这几年我有个很深的体会优化永远不要追求炫技而要在可控和可维护之间找平衡。比如频繁更新的列不适合加太多索引因为每次UPDATE都要同步维护索引树写入成本很高。还有不要为了某一条低频SQL去建冗余索引一张表的索引数量控制在5个以内比较健康多了就是负担。还有一个建议是优化完必须做回归验证。有一次我加了个索引让查询变快了结果发现更新那个高频表的其他语句开始变慢因为索引维护成本上去了。所以每次优化除了对比目标SQL的耗时还要关注周边SQL会不会受影响。线上环境改索引我一般选低峰期执行用gh-ost这类工具做在线变更不影响业务读写。我也强烈建议大家把慢查询日志作为常态监控的一部分别等出了故障才去看。平时可以每天看一眼慢查询排行提前发现那些“正在变慢”的SQL在它还没变成事故之前把它处理掉。这种预防性的成本远比事后救火要小得多。SQL优化这件事没有终点表在长数据在变SQL也要持续迭代。把它当成一个持续改进的过程你的数据库会稳很多。
