慢SQL优化实战：从索引原理到并行查询的完整指南

1. 先说清楚慢SQL到底难在哪我干了十来年数据开发见过的慢SQL比很多人写过的SQL都多。说句实在话大部分人面对慢SQL时的第一反应就是加索引但这个思路往往只对了一半甚至有时候加了索引反而更慢。如果你在面试中被问到SQL优化常用的几种方法或者在线上环境被一条跑了十几秒的查询折腾得焦头烂额那你来对地方了。这篇内容不整虚的我把这些年踩过的坑、验证过有效的优化套路从最基础的索引原理一路讲到并行SQL优化尽量用大白话讲清楚。先给新手打个底SQL优化本质上就三件事——减少扫描的数据量、减少返回的数据量、减少计算的次数。所有花里胡哨的技巧归根结底都是这三句话的变体。你把这个底层逻辑记在脑子里面试时说出来的话和网上抄的八股文完全不是一个档次。同时我也得提醒你SQL优化不是一个做完就结束的动作而是一个需要持续监控、反复调整的过程。今天优化好的SQL明天数据量翻一倍可能又慢了。所以这篇文章不只是教你怎么改一条SQL更希望帮你建立一套系统性的分析和判断方法让你以后遇到任何慢查询都能有条不紊地拆解。2. 索引优化90%的慢SQL都栽在索引上2.1 索引为什么能提速B树和数据页的快递分拣逻辑要理解索引先用一个生活化的类比。你所在的小区有几千个快递柜每一个格子都对应一个门牌号。如果没有快递柜索引快递员要找一个包裹只能把所有包裹都翻一遍这就是全表扫描。有了索引之后相当于每个快递柜侧面都贴了门牌号标签快递员看一眼标签就知道包裹在哪个区域甚至哪个格子里。数据库用的索引结构大多是B树。B树和普通二叉树最大的区别是它的每个节点可以装很多个键值树的高度通常只有3到4层。这意味着哪怕表里有上亿条数据你要定位到某一条记录最多只需要走3到4次磁盘IO速度当然快。MySQL的InnoDB存储引擎默认主键索引就是聚簇索引非主键索引叫做二级索引二级索引的叶子节点存的是主键值所以通过二级索引查数据往往还要回表拿主键再查一次。这就是为什么我经常说索引不是越多越好。每加一个索引写入数据时都要多维护一棵B树写入性能会打折。你要做的是让索引精准命中你的查询模式而不是给所有字段都建上索引。一个常见的经验值是一张表的索引数量不要超过5到6个否则写入性能很可能出问题。2.2 最左前缀原则你建了复合索引但SQL可能没走复合索引是SQL优化里最大的坑很多开发都栽在我明明建了索引为什么没生效这个问题上。复合索引遵循最左前缀原则。你建立了一个(a, b, c)的复合索引相当于建了(a)、(a, b)、(a, b, c)三个索引。查询条件里没有a这个索引基本就废了数据库优化器会认为走全表扫描比走这个索引更划算。这不是数据库有毛病而是B树的排序决定的——复合索引先按a排a相同再按b排如果条件里直接跳过a去查b或c索引的有序性就不存在了。举个例子订单表有一个复合索引(user_id, status, create_time)下面三条SQL的走索引情况完全不同-- 走了索引 SELECT * FROM order_info WHERE user_id 1001 AND status 1; -- 走了索引 SELECT * FROM order_info WHERE user_id 1001 ORDER BY create_time DESC; -- 没走索引http跳过了user_id SELECT * FROM order_info WHERE status 1 AND create_time 2024-01-01;第三条SQL就是典型的最左前缀失效。碰到这种情况不能怪数据库笨你要么调整查询条件要么单独为(status, create_time)建一个索引。另外还有一个很多人忽略的点复合索引中范围的字段放在最后。如果你要在user_id和create_time上建索引查询条件既有等值又有范围应该把等值字段放前面范围字段放后面。原因是B树在等值匹配时可以精确定位范围匹配只能扫一段连续区间如果范围字段放在前面后面的字段就没办法走索引排序了。2.3 覆盖索引连回表都省了的极致优化覆盖索引是我在所有SQL优化方法里最偏爱的一个因为它提效非常明显而且不需要改SQL只需要调整索引结构。什么叫覆盖索引就是索引里的字段已经包含了你要查询的所有字段数据库不需要回表再去数据页里捞数据。举个实际例子我们业务上有个统计报表经常要查某个用户在某个时间段内的订单金额SELECT user_id, SUM(amount) FROM order_info WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY user_id;如果只有主键索引这条SQL要先把所有符合时间条件的订单数据加载出来再分组求和。但如果我们建一个(create_time, user_id, amount)的复合索引那数据全在索引页里InnoDB直接扫描索引就能算出结果根本不用回表。我实测过在千万级数据量的表上这个改动能把查询时间从2秒降到100毫秒以内。所以你在设计索引时可以先看SELECT后面到底要哪些字段尽量让索引包住这些字段。特别是统计类、报表类的查询覆盖索引往往是性价比最高的优化手段。2.4 索引失效的常见场景这些坑我一个个都踩过做过SQL优化的人大概率见过索引明明在查询却慢得像蜗牛的情况。总结下来最常见的索引失效场景有这几类对索引列做计算或函数操作。比如WHERE DATE(create_time) 2024-01-01这就让索引失效了因为数据库要先对每一行做函数计算才知道结果。正确的写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02这样既走索引语义还更准。隐式类型转换。比如表的手机号字段是varchar类型你写WHERE phone 13800138000数据库会把字段隐式转成数字再比较索引同样会失效。正确做法是让查询条件和字段类型保持完全一致。前导模糊查询。LIKE %abc这种写法因为不知道开头是什么优化器没法利用索引的有序性只能全表扫。如果业务上确实需要前导模糊查询可以考虑用全文索引或者搜索引擎不要硬怼数据库。OR连接的非索引条件。如果OR两边只有一个字段有索引那整体查询还是会全表扫。解决办法是把OR改成UNION或者给两边都加上索引。反向查询。NOT IN、NOT EXISTS、!这些操作优化器大多数情况下会放弃索引。能改成正向查询就尽量改比如把status ! 0改成status IN (1, 2, 3)前提是业务语义允许。3. SQL语句层面的优化不建索引也能快几倍3.1 SELECT * 的问题不只是多返回几个字段那么简单很多新手不理解为什么大家都反对SELECT *。表面上是多返回了字段网络传输变慢了但更深层的问题是它破坏了覆盖索引的可能性。还是拿订单表来说假如你写SELECT * FROM order_info WHERE user_id 1001就算user_id有索引查到的数据是全字段索引里没存所有字段必须回表。但如果你的业务只需要order_no和amount两个字段写成SELECT order_no, amount FROM order_info WHERE user_id 1001配合覆盖索引就能避免回表。我之前帮一个业务部门优化过一个慢查询就是把SELECT *改成了只查必需的字段配合覆盖索引把响应时间从800毫秒压到了50毫秒。人家问我做了什么高深的操作其实就是这么朴素。当然SELECT *不是绝对不能碰。如果你确实需要所有字段而且表字段本来就不多写SELECT *也无可厚非。最怕的是明明只需要两个字段却习惯性地写个*上去白白浪费性能。3.2 JOIN和子查询的选择用小表驱动大表是铁律JOIN优化是老生常谈但真正做到位的人不多。我见过太多人写SQL时完全不关心表的连接顺序觉得反正优化器会调但实际上优化器的选择不一定是最优的尤其是表数据量有明显差异的时候。在MySQL里JOIN执行时驱动表的选择很关键。所谓小表驱动大表就是用小表作为外层循环逐条去大表里匹配数据。这样做的原因是外层表每扫一条都要去内层表做一次索引查找把小表放外层可以减少查找次数。不过MySQL 8.0之后引入了hash join在某些场景下不靠索引也能高效完成连接这对优化器来说是个好消息也让JOIN优化的容错率变高了。但老项目里如果是MySQL 5.7及以下版本连接的优化还是要重视起来。说回业务场景如果你的SQL里出现了子查询特别是IN子查询要注意MySQL的版本。5.6之前的版本对IN子查询的优化很不友好会重复执行子查询导致性能雪崩。到了5.6之后引入了半连接优化情况好了很多但依然比不上直接JOIN来得干脆。我的建议是能用JOIN解决的问题尽量别套子查询逻辑上写到明面上来优化器也好干活人也好维护。3.3 ORDER BY和GROUP BY排序和分组其实很烧钱排序和分组是SQL里最吃内存和CPU的操作。不带索引的ORDER BY数据库会把数据先放到临时表里排一遍如果数据量太大临时表还会落到磁盘上那个慢是肉眼可见的。要让排序走索引核心思路是让ORDER BY的字段顺序和复合索引的顺序保持一致。比如索引是(a, b, c)那么ORDER BY a, b, c可以走索引但ORDER BY b, a, c就不行因为顺序颠倒了。如果排序字段的方向不一致比如一个升序一个降序也可能导致无法走索引这需要你在MySQL 8.0以上版本才能得到更好的处理。GROUP BY其实和ORDER BY有相似之处因为分组本质上是先排序再聚拢。在数据量大的表上做GROUP BY如果发现慢可以先看执行计划里有没有Using temporary发现临时表了就是排序没有走索引的典型特征。另外如果你只需要对分组结果做过滤用HAVING还是WHERE要想清楚——WHERE是在分组前过滤行HAVING是在分组后过滤组能用WHERE先缩小范围就别等到HAVING再去过滤数据量会差很多。3.4 分页查询的深坑LIMIT 100000, 20 为什么那么慢如果你做过后台管理系统肯定写过这样的分页SQLSELECT * FROM operation_log ORDER BY create_time DESC LIMIT 100000, 20;这条SQL的逻辑是跳过前面十万条取接下来的20条。问题在于数据库并不知道你要跳过十万条它只能把前十万零二十条全部查出来然后丢掉前十万条。越往后翻页查询越慢这就是深分页问题。解决办法有不少最常用的是延迟关联或游标分页。延迟关联的思路是先用索引覆盖快速定位到需要的ID再用ID回表取数据SELECT o.* FROM operation_log o INNER JOIN ( SELECT id FROM operation_log ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;这种写法先用二级索引找出20个ID——因为二级索引小扫描十万条ID比扫描十万条全行数据快得多——然后再回表取完整记录性能能提升一个数量级。不过更推荐的方式是业务上改用游标分页也就是下一页这种形式记录上一页最后一条的ID查询时直接WHERE id 上一页最大ID ORDER BY id DESC LIMIT 20。这种方案不管翻多少页性能都是稳定的非常适合海量数据的场景。4. 进阶读懂执行计划才算真正入门SQL优化4.1 EXPLAIN不是看了就完事要会抓关键字段如果你只会改SQL却不会看执行计划那你的SQL优化能力就还停留在碰运气阶段。看执行计划用MySQL就是EXPLAIN命令它会告诉你SQL在数据库里到底是怎么执行的。EXPLAIN SELECT user_id, SUM(amount) FROM order_info WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY user_id;执行计划里有几个字段特别关键type字段从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描该优化了看到index也别高兴太早说明是扫描了整棵索引树只有在索引覆盖的情况下才勉强能接受。key字段实际用到的索引名。如果一个索引都没用上这里显示为NULL那就要警惕了。rows字段预估需要扫描的行数。这数字越大SQL执行越久。优化前后对比执行计划最直观的变化就是rows大幅下降。Extra字段这个字段信息量很大。看到Using filesort说明排序没有走索引性能会打折扣看到Using temporary说明用了临时表如果数据量大很容易出问题看到Using index说明是覆盖索引扫描这是好现象。我面试工程师时经常让候选人现场分析一条慢SQL的执行计划如果能说出rows从几万降到几十意味着什么、Using filesort背后的代价是什么那基本可以判断这个人真的有实战经验。4.2 慢查询日志线上环境的第一现场平时开发环境很难模拟线上的数据量和并发情况很多慢SQL在开发环境跑得飞快上了线就现原形。所以线上定位慢SQL最直接的工具是慢查询日志。MySQL通过long_query_time参数设置阈值比如设置为1秒那么执行时间超过1秒的SQL都会被记到慢查询日志里。你可以用下面这条命令看一下当前配置SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time%;如果没开启可以在MySQL配置文件my.cnf的[mysqld]段里加slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1这里log_queries_not_using_indexes也建议打开可以把那些没走索引的SQL也记录下来方便你主动发现隐患不用等用户先体验变差。拿到慢日志之后建议大家做一个归档分析找出哪些SQL出现频率最高、总执行时间最长。优先优化这些高频慢SQL比盯着一条偶尔慢一次的SQL死磕要划算得多。工具方面pt-query-digest是很多DBA常用的慢日志分析工具会按总耗时和出现次数帮你排序输出结果很直观。4.3 优化器选错索引怎么办force index 和直方图有时候索引建得好好的执行计划也看了发现优化器就是不走最优索引。原因通常是统计信息不准确或者选择性估算偏差。MySQL的InnoDB通过随机采样来估算索引的选择性数据分布不均匀时估算就很容易翻车。遇到这种情况最直接的办法是用FORCE INDEX来强制走指定索引SELECT user_id, amount FROM order_info FORCE INDEX (idx_create_time) WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY user_id;不过这招是双刃剑如果强制指定的索引后续因为数据分布变化不再适用了SQL可能会更慢所以不建议长期写在代码里一般是临时手段。MySQL 8.0引入了直方图可以给优化器提供更准确的字段分布信息帮助优化器做出更好的选择。在数据分布不均匀的字段上建直方图有时候比调SQL还管用。创建方式也很简单ANALYZE TABLE order_info UPDATE HISTOGRAM ON amount WITH 1024 BUCKETS;这个我之前在优化一个订单金额分布极不均匀的查询时试过效果确实不错。注意直方图不是索引它不占太多存储空间只是给优化器多了一份参考数据。5. 并行SQL优化当单线程真的扛不住了5.1 什么是并行SQL为什么它能让大查询飞起来很多人在开发时用的数据库是MySQL但到了数据仓库或者分析型数据库时会接触到并行SQL的概念。说实话如果你面试时能聊几句并行SQL优化会显得你比其他候选人高一个段位因为这说明你对数据库执行引擎的底层原理有理解。所谓并行SQL简单说就是数据库把一个大的查询任务拆成多个小的子任务分配到多个CPU核上同时执行最后把结果汇总。就像搬家时一个人搬一整卡车的东西和十个人同时搬速度完全不是一个量级。以PostgreSQL为例它从9.6版本开始支持并行查询到现在已经比较成熟了。并行执行的场景主要有三类一是全表扫描或大范围索引扫描二是大表之间的JOIN三是复杂的聚合操作比如GROUP BY和COUNT。MySQL 8.0在并行方面动作慢一些InnoDB层面的并行扫描还在不断演进。如果你主要用MySQL也不用太失望——对绝大多数业务系统来说单条SQL需要跑几秒钟的场景本来就不多并行SQL更大的舞台是在数据分析领域。5.2 并行度怎么设置不是越大越好并行SQL优化里最核心的一个参数就是并行度。很多人以为并行度越大越好其实不是。并行度过高会导致两个问题一是CPU资源竞争激烈数据库所在机器上的其他服务都会被拖垮二是任务拆分的开销和结果合并的开销可能超过并行带来的收益尤其是对本来就不大的查询。在PostgreSQL里相关参数有这样几个-- 设置最大并行worker数 SET max_parallel_workers_per_gather 4; -- 只有当表大小超过1GB时才走并行 SET parallel_setup_cost 1000; SET parallel_tuple_cost 0.1; -- 查看当前并行相关的配置 SHOW max_parallel_workers_per_gather;parallel_setup_cost是启动并行任务的固定开销parallel_tuple_cost是每个元组在并行worker之间传输的成本。这两个成本参数设得越大优化器越不愿意走并行。如果你的服务器配置很好、CPU核数多可以适当调低这两个成本值让更多查询有机会走并行。我个人的经验是对于OLTP场景的并发查询并行度设置2到4就足够了对于OLAP场景的分析查询可以考虑8甚至更高但要先确认机器上还有其他业务在跑。一把梭把并行度拉到系统CPU核数很容易出大事我见过生产环境因为并行度过高导致CPU打满的案例最后只能重启数据库代价相当大。5.3 并行SQL的局限什么时候不要用并行并行SQL不是银弹它也有很明显的适用边界。我总结了几种不适合用并行的场景小查询不要并行。如果一个查询只需要扫几百条记录并行任务拆分和合并的开销比直接查询还大性能反而下降。写入操作一般不支持并行。目前大多数数据库的并行优化主要集中在读操作上并行DML在很多数据库里限制很多不要指望并行解决写入慢的问题。热点小表不需要并行。如果表的数据量不大数据基本都在缓冲池里单线程扫描已经很快了加了并行只是徒增CPU消耗。复杂子查询并行效果不稳定。有些子查询本身没法并行执行最终整个SQL的并行度会被这个子查询拖累看起来执行计划里有并行实际上瓶颈还在串行部分。做并行SQL优化核心思路是先判断这个查询是不是真的需要并行。如果你的单条SQL只跑了200毫秒并行的意义就不大但如果一条报表SQL要跑30秒而且每天都要跑那并行绝对是性价比极高的优化手段。6. 常见问题排查与避坑速查表6.1 为什么加了索引还是慢三个必须检查的盲区我经常收到类似的求助我给表加了索引为什么查询还是慢这时候我会让同事按下面三个方向逐一排查大部分问题很快就能定位。盲区一索引对结果集超过阈值的选择性失效。索引的价值在于快速定位少量数据。如果某个字段的值分布很均匀比如性别只有男和女那就算这个字段上了索引优化器也不会走——因为它算出来全表扫描更快。这不算索引失效而是优化器做出了正确判断。遇到这种情况不要硬刚单选字段尝试用复合索引或者调整查询逻辑更靠谱。盲区二回表次数过多。走二级索引查到一堆ID然后每条ID都要回表去主键索引里捞数据。如果回表次数以万为单位那速度还不如全表扫描。排查方法还是看执行计划里的rows和Extra字段如果发现问题优先考虑覆盖索引。盲区三查询条件里的隐式转换。这一点我在前面已经提过但它实在太高频了。尤其是代码里传参时应用层传过来的是字符串数据库字段是数字类型或者反过来你的查询在不知不觉中做了类型转换索引就这么被悄悄绕过了。我建议把核心大表的字段类型和代码里的参数类型严格对齐甚至在测试环境造些数据去验证一下不要凭感觉。6.2 慢SQL优化踩坑实录三个真实案例挑三个我印象特别深的案例分享给你排除掉公司和业务敏感信息留最核心的内容。案例一索引没问题是缓存没命中。有个运营后台的报表每次打开都要等4到5秒开发同事查了表和索引都没毛病。我上去一看SQL里用了SELECT COUNT(*)配合多张千万级大表的JOIN每次都是实时计算缓存完全没起作用。后来我们在中间层加了一层异步预聚合的结果表报表直接查结果表响应时间从4秒降到100毫秒。这个案例告诉我们SQL写得再优化有些统计指标的实时计算成本就是高这时候要换思路用空间换时间。案例二OR条件让索引整个没用上。一个用户查询界面条件里有会员等级和注册渠道这两个字段分别有索引但SQL里用的是OR连接。执行计划显示全表扫描。解决办法是把OR改成了UNION ALL两个子查询各自走索引最后合并结果。改动很小查询时间从2秒降到300毫秒。后来我反思了一下如果当初建的是复合索引SQL甚至不用改但也只能等下次重构时优化了。案例三并行设置太激进CPU打满。有一次我把Oracle数据库的并行度设置调得过高同一时间有几个分析任务同时跑CPU直接打满连正常的业务查询都受到了影响值班电话都被打爆了。后来把并行度限定在4并通过资源组限制了同时并行的任务数量才恢复稳定。这个教训让我深刻认识到任何优化都要考虑全局资源不能只顾一条SQL的局部最优。6.3 面试中SQL优化的高频考点整理最近很多读者问我SQL优化面试该怎么准备这里正好整理一个速查表都是我自己面试别人时经常问的点也是这些年做优化真正会用到的核心知识。面试问题核心回答要点SQL优化常用方法有哪些索引优化、SQL改写、执行计划分析、分页优化、表结构设计、读写分离、缓存、并行查询为什么索引能提高查询速度B树结构、降低磁盘IO次数、索引覆盖避免回表什么情况下索引会失效函数操作、隐式类型转换、前导模糊查询、OR非索引列、反向查询如何优化慢查询先开启慢日志定位再EXPLAIN分析执行计划对症下药调整索引或改写SQL分页查询慢怎么办延迟关联、游标分页、覆盖索引JOIN如何优化小表驱动大表、连接字段加索引、避免笛卡尔积、合理使用hash join8.0并行SQL是什么将大查询拆分到多核并行执行优化器通过成本模型决定是否并行面试时回答这些问题最忌讳背概念。面试官想听的是你实际怎么做的、遇到了什么问题、怎么排查解决的。比如问到索引失效与其背那几条场景不如讲一个你真实遇到过的案例说说当时执行计划长什么样做了哪些尝试效果如何。这种回答比八股文强太多了。6.4 日常开发中的SQL编写习惯建议最后分享几个我这些年慢慢养成的习惯帮你在源头减少慢SQL的产生写SQL前先想清楚字段和过滤条件。需要哪些字段就查哪些字段条件能往前放就往前放能缩小范围就缩小范围。这句废话一样的建议真做到的开发没几个。核心表变更索引时要谨慎。生产环境的表加索引不能随便加要评估写入性能的影响尽量在低峰期操作。大表加索引用ALGORITHMINPLACE方式减少锁表时间。定期查看慢查询日志。我会每个月固定过一遍慢日志即使线上没告警也要看看是不是有长期潜伏的慢SQL在慢慢消耗资源。等用户投诉了再查就已经晚了一步。用数据说话不靠感觉。优化完一条SQL记录一下优化前后的执行时间、扫描行数、执行计划差异。量化的结果既能证明你的优化是有效的也能在汇报时更有说服力。SQL优化这件事越做到后面越会意识到它不是靠一两个绝招就能解决所有问题的而是要你对数据库原理、业务特点和系统架构都有足够的理解然后针对性地组合各种手段。我在实际查看执行计划时哪怕是一条已经跑得不错的SQL也习惯再想想是否有更好的索引方案踩过几次坑之后我更加笃定先把慢查询日志和SQL改写方法论吃透再谈并行SQL这些进阶方案这条路对大多数团队成员来说是成长最快、也最容易形成团队共识的路径。希望这篇从基础到进阶的整理能帮你少走一些弯路。