SQL调优实战：从慢查询定位到索引优化，实现10倍提速

数据库工程做了这么多年SQL调优的目标无非就是让查询更快、让数据库更扛压。但“提升10倍查询速度”这件事很多人一听就觉得夸张觉得是不是要上什么高端硬件、搞什么分布式架构。实际上在我经手的绝大多数项目里SQL从慢到快根本不需要动架构就是一条SQL、一个索引、一次执行计划的调整就能从几秒级别直接压到几百毫秒甚至几十毫秒。这10倍的差距往往就藏在那些看起来“没毛病”的写法里。这篇文章我就围绕数据库工程里的SQL调优实战讲一套我常用的提速方法论。这套方法论不是理论推演都是我在实际业务里压过线上慢查询、处理过生产事故之后沉淀下来的。适合谁看适合那些每天跟MySQL、PostgreSQL这类关系型数据库打交道被慢查询拖得头疼的后端开发、DBA、数据工程师。你看完可以直接对照自己的业务把里面的方法套上去试试。1. 慢查询的第一步先定位瓶颈到底在哪很多人一上来就改SQL这是最大的误区。SQL只是表象瓶颈可能出在索引失效、表结构设计不合理、数据量增长导致的执行计划变化甚至可能是连接数打满、锁等待。不先定位你改的每一行代码都是在碰运气。1.1 用慢查询日志锁定目标SQLMySQL里可以通过slow_query_log把执行时间超过阈值的SQL记录下来。我一般会把阈值先设得激进一点比如long_query_time 1这样线上超过1秒的SQL都会被抓到。不要一开始就设成5秒、10秒那样很多潜在的问题SQL会被漏掉。拿到慢查询日志之后肯定不能只看SQL文本还得看它的执行频次。一条SQL执行10次、每次2秒和一条SQL执行100万次、每次50毫秒哪个更值得先优化显然是后者因为后者的总耗时占了资源大头。我会先把慢查询日志里的SQL按“平均耗时×执行次数”排序算出每个SQL的总开销优先处理排名靠前的。1.2 通过EXPLAIN读懂执行计划别靠猜定位到目标SQL之后下一步就是用EXPLAIN看执行计划。这一步非常关键因为执行计划会告诉你MySQL到底是怎么执行这条SQL的是全表扫描还是走索引预估扫描多少行有没有用到临时表、文件排序。我习惯重点看这几个字段字段关注点type从const到eq_ref、ref、range再到ALL顺序越靠后越危险。看到ALL基本意味着全表扫描key实际用到的索引如果是 NULL说明创建了索引但没用上rowsMySQL预估的扫描行数这个数字越大查询成本越高Extra看到filesort、temporary就要警惕这些通常是排序、分组慢的根源这里有个我踩过很多次的坑EXPLAIN查出来的 rows 是估算值不是精确值有时候偏差会很大。特别是当你用了复杂的 JOIN 和子查询时MySQL 可能基于错误的基数估算选择一个错误的执行计划。所以我会在EXPLAIN之后再用ANALYZE TABLE更新表的统计信息然后重新看执行计划。1.3 用profile定位是CPU耗时还是IO耗时如果执行计划看起来没什么大问题但SQL还是很慢那就需要更深一层了。我会用SET profiling 1;打开profiling功能然后执行目标SQL再查询SHOW PROFILE来查看整个执行过程各阶段的耗时分布。这个阶段非常关键因为它能区分到底是CPU烧在计算上还是磁盘I/O拖了后腿。如果I/O耗时占比高通常意味着读取的数据块太多这时候优先考虑优化索引以减少访问的页数如果CPU耗时高则大概率是排序操作、临时表操作太重或者做了大量无意义的字符串处理。看到这里你就知道调优不是无脑加索引而是“对症下药”。2. 索引让数据查找从“翻书搜”变成“查目录”索引是SQL提速的最大杠杆也是背锅最严重的环节。很多项目里索引建了一堆查询还是慢原因是索引建得不对或者查询写法让索引根本没法生效。2.1 覆盖索引的威力让查询在索引里完成先讲一个最容易被低估的策略覆盖索引Covering Index。所谓覆盖索引就是你查询的所有字段都包含在同一个索引里查询时MySQL只需要扫描索引树的叶子节点不需要回表去主键索引查找完整行记录。这个操作可以省掉大量的随机I/O。打个比方你要找一个作者的书普通索引相当于只告诉你“这个作者在第几排书架”你还得亲自走过去找覆盖索引相当于直接告诉你“书就在你手边的这个抽屉里翻页就行”。节省的就是来回走路的成本。实际落地时我之前优化过一条订单列表查询原来SQL长这样SELECT order_id, order_status, amount, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;这条SQL虽然也走了user_id索引但每一行都要回表去拿amount和created_at而且ORDER BY还需要文件排序。我改成了ALTER TABLE orders ADD INDEX idx_user_created_amount (user_id, created_at, amount);加了联合索引之后查询只需要从索引里直接取数回表全部省掉排序也能走索引顺序连filesort都省了。性能直接提升了一个量级。2.2 最左前缀原则以及建联合索引时的字段顺序联合索引很多人会用但怎么排字段顺序大多数人是凭感觉的。实际上有一条核心原则必须记牢把区分度高的字段放前面。区分度即字段中不同值的比例值越分散过滤效果越好放前面能让索引树更快地收敛。另外还有一个很实际的经验把等值查询的字段放在范围查询字段的前面。举个例子如果你经常查WHERE status 1 AND created_at 2024-01-01那么索引应该建成(status, created_at)而不是反过来。因为等值条件可以精确定位到索引树的某个节点范围条件只是在这个节点内部继续扫这样能最大化利用索引的定位能力。这里插一句覆盖“视图可以加快查询速度吗”这个话题。视图本质上只是一条存储起来的SQL语句的命名封装所以直接把一个慢SQL做成视图查询速度不会变快一丝一毫。你每次查视图底层还是执行那条SQL。真正能让查询变快的是物化视图——把查询结果变成物理存储的表。但MySQL原生并不支持物化视图所以如果你是在MySQL上听到“建视图能让查询变快”的说法可以直接判定为误导。后面我会专门用一整节展开讲这个容易混淆的问题。2.3 索引失效的几种典型场景索引失效是实战中最容易踩的坑而且经常是“看起来明明走了索引但还是慢”。常见的失效场景我列一下都是我被坑过或者排查过无数次的对索引列使用了函数或运算WHERE DATE(created_at) 2024-01-01会让created_at索引失效。正确写法是WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00。隐式类型转换字段是varchar但你传了数字进去或者反过来。MySQL会做隐式转换索引直接废掉。我见过太多WHERE phone 13800138000导致全表扫描的案例手机上应该带引号。前模糊匹配LIKE %关键字这种写法索引无法倒着匹配必然失效。但是LIKE 关键字%是可以用上索引的这个要注意区分。OR连接的两个条件中有非索引列只要其中一个条件没有索引整个查询就会退化成全表扫描。这种情况需要改造为UNION ALL把带索引的部分分开走。上面这些失效场景你只要在写SQL的时候多留个心眼大部分都能避开。但我还要强调一下索引不是建得越多越好。每一条索引都会拖慢INSERT、UPDATE、DELETE的速度因为写操作需要同步维护索引树。那些“给每个查询都建一个索引”的做法短期查得快长期写库会越来越慢属于饮鸩止渴。3. SQL写法的细节决定了10倍速能不能落地这一节讲的是纯粹的SQL写法层面不需要动静多改几行字性能差距就是天壤之别。这些细节单看都很小但叠加起来效果惊人。3.1 SELECT * 不只是多查了几列那么简单很多人觉得SELECT *方便省略了写列名的麻烦。但在高并发场景下这是性能杀手。原因有三层第一*会无条件把整行所有字段都查出来哪怕你只需要其中两三个字段。多出来的字段白白占用了网络带宽和数据库的内存临时区。第二*无法使用覆盖索引因为你不可能把整行的所有列都塞进一个索引里这就强制了回表操作。第三*让优化器在处理嵌套循环连接Nested Loop Join时无法使用索引覆盖优化可能会导致驱动表变大。我在优化一个数据报表接口时原SQL查出50多个字段实际业务只用其中8个。改完之后查询耗时降了40%因为传输的数据量少了80%。看起来离谱但在大结果集情况下就是这么显著。3.2 分页查询深度翻页的痛OFFSET陷阱分页是查询优化里最容易被忽视的痛点。业务上常见的方案是LIMIT offset, page_size但当页码很深时比如查第100000页SQL会写成LIMIT 1000000, 20此时MySQL需要先把前100万行全部定位出来再丢弃扫描代价随着页码线性增长。我举个自己优化过的真实例子。一条社区帖子列表的分页SQLSELECT post_id, title, author, created_at FROM posts ORDER BY created_at DESC LIMIT 200000, 20;跑一次需要600多毫秒用户翻到很后面的时候接口响应就会超时。我的优化思路是“先快速定位分页起点再向后取数”改成基于上一次查询的最大ID或者时间戳SELECT post_id, title, author, created_at FROM posts WHERE created_at 2024-05-01 10:00:00 ORDER BY created_at DESC LIMIT 20;配合(created_at)索引这条SQL只需要走索引树定位到游标位置再往后精确取20行耗时瞬间降到了40毫秒以内。这样的优化思路叫“游标分页”或“keyset pagination”非常实用。不过要注意这种方案要求排序字段必须有唯一性约束否则两条记录的时间完全相同时翻页容易出现重复。最稳妥的是在ORDER BY里加一个唯一字段作为第二排序项比如ORDER BY created_at DESC, id DESC。3.3 子查询和JOIN的改写把关联条件想透MySQL对子查询的处理能力在5.7版本之后有了很大提升会把一部分子查询自动改写为半连接semi-join但并非所有场景都能正确优化。特别是当你用了IN子查询且子查询的返回集很大时很容易造成性能陷阱。我见过太多类似这样写法的SQLSELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE last_login_at 2024-01-01 );这条SQL在数据量小的时候没事数据量一上来users表的筛选结果如果是几十万行那IN子查询就不再高效。我一般会把它改写为INNER JOINSELECT o.* FROM orders o INNER JOIN users u ON u.user_id o.user_id WHERE u.last_login_at 2024-01-01;这样MySQL就能明确两张表的关联关系选择小表作为驱动表通过索引去关联大表。注意改写之后如果你发现驱动表选错了可以在 JOIN 时使用STRAIGHT_JOIN强制指定驱动顺序但这招要谨慎只在你确定驱动顺序更优时使用。另外关于EXISTS和IN的取舍简单记一个经验法则外层表数据量小、内层表数据量大用EXISTS更合适外层表数据量大、内层表数据量小用IN更好。核心逻辑是“谁小谁作为外层”让外层循环的次数尽量少。3.4 聚合查询里HAVING和WHERE的分工这是个老生常谈的话题但我发现还是有不少人写错了。WHERE是在分组之前过滤行记录HAVING是在分组之后过滤聚合结果。能写在WHERE里的条件绝对不要放到HAVING里。因为HAVING要等全部数据分组聚合完之后才执行过滤相当于把所有脏数据都算了一遍再丢白白浪费计算资源。看个例子SELECT user_id, COUNT(*) FROM orders HAVING order_status 1 GROUP BY user_id;这个写法会让数据库先把所有状态的订单都分组统计一遍然后再把不满足条件的组丢掉。正确写法应该是SELECT user_id, COUNT(*) FROM orders WHERE order_status 1 GROUP BY user_id;表面上看只是多了个WHERE实际过滤了大量无需参与分组的数据聚合的计算量直接下降。这种小改动在百万行级别的表上能拉开几十倍的差距。4. 视图到底能不能加速查询把这个热词讲透前面提到了热词“视图可以加快查询速度吗”这一节我专门展开讲。因为这不只是对新手友好的科普也是很多经验不足的工程师会搞混淆的点值得在生产层面掰扯清楚。4.1 视图的本质一个命名的查询封装先明确概念。在MySQL、PostgreSQL这类传统关系型数据库里普通视图Non-materialized View本身不存储任何数据。你可以把它理解成一个“SQL片段”或者“查询的快捷方式”。当你CREATE VIEW v_user_orders AS SELECT ...之后这张视图只是保存了这个SELECT语句的文本定义。每次你在查询里引用这个视图数据库做的第一件事就是把视图替换回原始的SELECT语句再和你的外层查询合并生成最终的执行计划。也就是说它的执行成本和直接写那条等价的SQL完全一样。加一层视图甚至还有一点点额外的解析开销虽然微乎其微但绝不可能“提速”。真实场景里视图的价值是别的方面安全性只暴露部分字段给下游应用、可维护性复杂查询统一封装、兼容性改动底层表结构时保持视图对外不变。这些是工程层面的好处不是性能层面的。4.2 物化视图才是真正的“加速器”如果非要说“视图能不能加速查询”唯一能给出肯定答案的就是物化视图Materialized View。它把查询结果真正落盘存储成一张物理表查询时直接扫物化视图里的数据不再去聚合底层明细表。这相当于把一次动态计算变成一次静态读取性能自然是质的飞跃特别适合那种“底层表频繁写入、查询条件固定、结果集远小于源数据”的报表场景。PostgreSQL原生支持物化视图CREATE MATERIALIZED VIEW mv_order_summary AS SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id;之后手动刷新REFRESH MATERIALIZED VIEW mv_order_summary;MySQL则不支持原生物化视图但可以自己用表来模拟写一个定时任务把聚合结果查出来物理落到一张结果表里应用层直接查询结果表。这也是很多BI报表系统的底层实现方式。说白了核心思路就是“空间换时间”。4.3 生产环境里“视图”替代不了“索引改写”所以回到实战链路如果有人来问我“我把慢查询包成一个视图会变快吗”我会直接回答不会顶多让你写SQL方便一点。真正的加速来自我们前面讲的索引优化和执行计划调整或者从架构层面引入物化视图等预处理机制。这里也提醒一个很容易被忽视的坑视图的嵌套层级如果过深比如视图套视图套三层以上MySQL在解析的时候可能会产生膨胀的合并SQL反而让优化器算错代价、选错执行计划。我之前排查过一条线上查询直接写底层表时执行计划正常改成查三层嵌套视图后居然出现了全表扫描加临时表。当时查了很久最后发现就是嵌套视图导致的优化器误判。所以我会一直强调视图不是拿来叠罗汉的能不用嵌套就别用嵌套。5. 完整案例复盘一个报表查询从6秒到0.3秒的调优全过程这一节我完整复盘一个优化案例把前面讲的方法串起来让大家看到一个真实的调优流程是怎么走的。这个案例来自我之前处理过的一个商家后台销售报表模块业务需求是按日期范围列出每个商品的销售数量和销售额汇总并按销售额降序排列。初始SQL是这样的SELECT p.product_name, SUM(oi.quantity) AS total_quantity, SUM(oi.price * oi.quantity) AS total_sales FROM order_items oi LEFT JOIN products p ON oi.product_id p.product_id LEFT JOIN orders o ON oi.order_id o.order_id WHERE o.paid_at 2024-05-01 AND o.paid_at 2024-06-01 GROUP BY p.product_name ORDER BY total_sales DESC LIMIT 100;当时线上订单表大概800万行order_items表有1200万行products表只有2万行。这条SQL跑一次要6秒多报表页面直接超时。我一步步处理第一步EXPLAIN看执行计划。发现的问题是order_items 走了typeALL的全表扫描orders 表走了主键查找products 表走了主键查找。MySQL选择 order_items 作为驱动表对它的1200万行全量扫了一遍再逐行回表关联其他表。这个逻辑本身就是灾难级的。第二步检查关联字段的索引。发现问题出在order_items.order_id和order_items.product_id上都没有索引。虽然它们是外键字段但建表时居然漏了。这就是典型的“外键字段需要索引”教训。我直接补上了两个索引ALTER TABLE order_items ADD INDEX idx_order_id (order_id); ALTER TABLE order_items ADD INDEX idx_product_id (product_id);因为驱动表还是很大我继续优化。把LEFT JOIN改为INNER JOIN因为业务上订单项一定关联着有效订单和有效商品不会因为连接方式不同而减少结果集。这个改动看起来小但它让优化器可以把过滤条件更早地下推到外层驱动表减少参与连接的数据量。此时再跑EXPLAIN执行计划显示 order_items 已经可以从全表扫描变成走idx_order_id去匹配 orders 表的时间范围条件。但还有一个问题没法直接用索引解决SUM(oi.quantity)和SUM(oi.price * oi.quantity)这两列的计算需要把命中的每一行数据都取出来聚合。第三步我考虑能否用覆盖索引把这一步也优化掉。我给 order_items 建立了一个覆盖索引把 order_id、quantity、price 三个字段都装进去ALTER TABLE order_items ADD INDEX idx_order_quantity_price (order_id, quantity, price);因为 order_items 表本身有一个id主键这个联合索引能直接覆盖order_id等值连接、quantity和price聚合取数这两个步骤避免回表。到这里整个查询链路的I/O开销已经大幅下降。最终执行计划变成了先通过orders表的时间条件过滤出时间范围内的订单ID集合这个集合很小假设是几千行然后拿着这些 ID 去 order_items 的idx_order_quantity_price索引里精确匹配并聚合最后对聚合结果排序。SQL耗时从6秒多降到了0.3秒左右优化了将近20倍远超刚开始预期的10倍目标。调完之后我又做了一件事把这条SQL固化成了每日定时预热任务。因为商家后台的日销售报表过去某天的数据不会变化我可以每天凌晨把前一天的数据汇总结果写入一张 day_sales_summary 表。应用层查日报时直接读汇总表查询耗时进一步降到几十毫秒。这就是我们前面说的物化思路在实际业务里的应用。这个案例最值得借鉴的不是某一条SQL的改法而是整个调优思路从定位瓶颈到补索引到改连接方式到用覆盖索引再到引入预处理。每一步都是有依据的不是拍脑袋乱改的。6. 调优路上常见的心态陷阱以及我的一点建议最后这部分不说技术说点实际踩坑后的心得。毕竟做SQL调优技术只是一部分很多性能问题拖到最后解决不了其实是分析思路出了问题。我最常看到的现象是“没有数据证明就瞎猜”。比如有人说“这个SQL慢是因为表数据量太大了”然后就开始琢磨分区表、分库分表。但实际上这条SQL可能只是索引没建对加个索引就行。数据库分区、分库分表是重武器带来的是架构复杂度、运维复杂度的大幅提升绝对不是第一优先级该做的事。先做实测、看执行计划、拿数据说话永远是最稳的路。另外一个陷阱是“优化一次就完事”。数据量是持续增长的今天800万行的表可能有合适的执行计划明天涨到2000万行优化器的选择就变了。我一般会在表结构变更、数据量翻倍这两个节点定期重点观察慢查询日志。必要的时候用ANALYZE TABLE更新统计信息这是一个成本极低、收益很快的操作但太多团队完全没这个习惯。还有一点是关于团队协作的。SQL调优的成果要固化下来不能只存在个别人的脑子里。我通常会把每一次调优的过程和结论整理成简要文档里面包含原始SQL、执行计划截图、改动点、耗时对比。这样下次有人遇到类似问题直接能照着排查不用重新走一遍弯路。做这行越久越觉得SQL调优不是炫技而是一套严谨的工程方法。你每写一条SQL的时候想一下它要走哪个索引、大概扫多少行数据、会不会回表、要不要排序、能不能用覆盖索引。想清楚这几个问题绝大多数性能问题都能在设计阶段就规避掉。剩下的就是用慢查询日志和EXPLAIN把这些直觉验证一遍即可。按这个思路去做10倍速不是目标而是一个很自然的副产品。