不少团队都经历过这样的时刻线上数据库CPU突然飙高慢查询日志里刷出来一条跑了七八秒的 SQL表面看功能完全正常返回的数据也挑不出毛病。你把它复制到本地数据量小执行计划还算体面可一上生产就原形毕露全表扫描、临时文件排序、嵌套连接全都冒出来了。数据库硬件升级了连接池也调大了问题还是照样存在——这时候你才意识到问题不是数据库不行而是写那条 SQL 的人从一开始就把优化器的嘴给堵死了。这类写法我见得太多也替别人排查过太多。它们不会语法报错不会让功能失败甚至在数据量只有几万行的时候非常体面但只要数据量涨到千万或者接口并发一上来立刻变成整个团队的定时炸弹。我把它们归纳成 8 种常见的“专坑同事”写法逐一拆解背后的原理、触发条件以及正确的替代方案。如果你是刚接触数据库优化的人这篇文章能帮你建立一种“条件反射”如果你已经是团队里的老手这份清单也能作为代码评审时的参考依据。1. 慢 SQL 的雷到底埋在哪优化器的取舍逻辑1.1 一条查询提交之后数据库到底在纠结什么很多开发把 SQL 理解成“我说要什么数据库就去取什么”但真实流程完全不是这样。数据库拿到一条 SQL 之后会先把它解析成语法树再做逻辑优化和物理优化最后根据统计信息计算每条执行路径的代价选一个它认为最便宜的计划去执行。代价估算的核心依据有几个表的行数、字段的区分度、索引的分布、是否需要回表以及排序和临时表的开销。举个例子一条查询可以用主键定位也可以全表扫描优化器会估算“主键定位要读几个索引页、几个数据页”再估算“全表扫描要读多少页、过滤多少行”哪个便宜就走哪个。这就是为什么统计信息过期时执行计划会突然变差的原因——它判断的事依据是真的不准了。这个机制里有个特别关键的点优化器只有在条件“能被表达成索引上的边界”时才会考虑走索引。如果条件本身无法确定边界就只能把符合条件的每一行都拉出来验证一遍。这种“能作为边界”的特性业内叫 SARGable也就是查询条件能否被看成搜索参数。一条 SQL 是快是慢很多事都取决于条件是不是 SARGable。1.2 写法是怎么改变执行计划的理解优化器之后再看写法的差别就非常清楚了。WHERE age 25可以直接在age索引上定位WHERE age 1 26就必须把每一行的 age 取出来做一次加法再判断结果。多了一个表达式代价可能就从索引查找退化成全表扫描。就算表里只有 10 万行这两条 SQL 的实际表现也能有几十倍的差距。所以后面要拆解的 8 种写法本质上都违反了同一个核心原则让查询条件里的索引列保持裸用。换句话说索引列不要被函数包着不要发生类型转换不要被通配符截断前缀不要被奇怪的子查询塞进连接逻辑里。很多开发写 SQL 时只考虑“结果对不对”完全忽略了“这个写法会让数据库付出什么样的代价”。本章先把底层逻辑讲透后面的具体坑就都好理解了。2. 三个基础坑函数包裹列、类型不匹配、左侧模糊2.1 坑一索引列上套函数优化器直接失去“边界感”这类写法最常出现在统计报表里比如想查一年内的文章数据随手就写SELECT id, title, created_at FROM article WHERE YEAR(created_at) 2024;从结果看没错确实能把 2024 年的数据捞出来。但YEAR(created_at)是一个表达式created_at列本身被函数包住了。B 树索引节点里存的是完整的时间值比如2024-06-15 10:30:00而不是年份 2024。优化器无法通过索引直接定位“所有年为 2024 的记录”因为它没有一个明确的上下边界只能把索引或表里的每一行都取出来依次执行YEAR()函数再比较结果是不是 2024。这个操作一旦落到千万级的大表上就是几千万行数据的函数调用CPU 直接被打满。正确的写法是改成范围条件SELECT id, title, created_at FROM article WHERE created_at 2024-01-01 AND created_at 2025-01-01;这里的逻辑是让created_at保持裸列把“年”这个语义转换成起始和结束两个边界。优化器就能在索引上做一次范围扫描只读取属于 2024 年的那部分索引节点回表也只有几十万行甚至更少。我每次评审代码时都会提醒一句索引列就站在比较符左边不动函数、运算都去处理右边的常量。只要条件里出现DATE()、YEAR()、MONTH()、LEFT()包住索引列的情况基本就是一个隐藏的全表扫描。还有一种变体是按时间段做统计时写的WHERE DATE(create_time) CURDATE()同样的问题。改成WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY才有救。这类写法在 BI 报表场景里出现频率极高一条报表慢查询就能拖垮整个报表平台的数据库连接池。2.2 坑二隐式类型转换最隐蔽的索引失效手机号、身份证、订单号这类字段建表时几乎都会用varchar存储。开发在写查询时容易忽略引号SELECT id, name, phone FROM user WHERE phone 13800138000;这里phone是字符串列右边的13800138000是数值类型。主流数据库处理字符串列和数值常量比较时有自己的一套类型优先级约定通常会把字符串转成数值再比较。结果就是phone列本身被隐式转换了一次等同于CAST(phone AS unsigned)索引列又成了表达式的一部分。优化器无法在这个条件下直接走索引定位只能回落到全表扫描。尤其坑人的是这种写法如果数据量只有几万行执行一次也要不了几毫秒测试环境根本感知不到异常。等用户量涨到百万、千万级它就会在接口里变成一个稳定出现的慢查询而且因为查询结果一直是正确的很难有人想到是“少了一对引号”导致的。正确写法是SELECT id, name, phone FROM user WHERE phone 13800138000;反过来也有一种场景id是int列代码里把参数拼成字符串于是写成WHERE id 10086。部分数据库优化器会尝试把右侧常量转成数值类型匹配左列的类型这种情况下影响还不算大可一旦优化器选择把id列转成字符串再比较那问题就和上面一模一样。最稳妥的规则是列的类型是数值型参数就传数值列的类型是字符串型参数就一定加上引号。不要去赌优化器的隐式转换方向类型匹配才是最简单可靠的方式。顺带说一句字符集不同造成的隐式转换也会让关联查询的索引失效比如utf8mb4和latin1的两张表做 JOIN这种坑在正常排查里更容易漏掉。2.3 坑三左侧带通配符的 LIKE索引扫描变成全表裸奔模糊查询是 LIKE 语句的应用场景但 LIKE 不等于一定慢。问题只出在通配符的位置SELECT id, item_name, price FROM product WHERE item_name LIKE %保温杯%;LIKE 保温杯%是前缀匹配B 树的排序是按字符串前缀进行的优化器能算出匹配的起点和终点从而走范围扫描。但%保温杯%把通配符放在最前面等于告诉优化器“记录的开头部分我不确定请把每一行都拿出来匹配一遍”。于是执行计划从索引范围扫描直接变成全表扫描或全索引扫描每一行都要做一次字符串模式匹配CPU 和 IO 双双飙升。如果业务确实需要中间匹配比如在商品名里搜“保温杯”三个字不能在开头指定那就要面对一个现实数据库的 B 树索引没法直接支持这种检索。可以考虑的方案有几种。数据量小、查询频率低的配合覆盖索引能缓解回表压力但扫描本身仍然是全表级的数据量大、查询频率高的建议把搜索功能交给全文检索引擎专门负责分词和倒排索引数据库只负责按主键回查。还有一个不太常见但确有其效的做法如果只是后缀匹配比如日志文件名是test_20240601.log而你只想查20240601.log结尾的记录可以在存储时额外冗余一个反向字符串字段对反向字段做前缀匹配索引。这属于“伪前缀”的变通思路适合那些不改架构又必须用 LIKE 的场景。总之见到%xxx%这类写法默认先标记为全表扫描候选。2.4 顺手清一个同族问题OR 条件的索引困局本节再补充一个经常和索引失效伴生的写法WHERE status 1 OR status 2。如果status列有索引理论上优化器可以走索引合并把两个等值条件各自定位之后再合并结果。但现实并不总是如此尤其当 OR 连接的是不同索引列或者其中一侧是范围条件时优化器算来算去发现合并成本太高干脆选择全表扫描于是一个简单的等值查询就变成了全表操作。更简洁也更稳妥的表达是WHERE status IN (1, 2)它比 OR 更容易被优化器处理成一次索引范围定位。如果 OR 连接的两侧本身是截然不同的业务条件适合拆成两条分成 UNION ALL 再合并而不是用一条 OR 硬扛。很多开发觉得 OR 很直观但它在数据库优化器眼里的分量并不轻。3. 子查询和 JOIN 里的性能黑洞三个老熟人3.1 坑四NOT IN 子查询撞上 NULL结果直接给“清零”不少人在写“排除黑名单用户”时会顺手写成SELECT id, name FROM user WHERE id NOT IN ( SELECT user_id FROM blacklist );这条 SQL 从语义上看简单直接但它既可能产生性能风暴还可能产生严重的数据正确性问题。先从正确性说只要blacklist.user_id里有任何一条是 NULL这个查询返回的结果集会直接变成空。原因在于 SQL 的三值逻辑——NULL 既不算真也不算假。NOT IN的含义是“当前值不等于列表中的每一个值”可一旦列表里出现未知值整个判断结果也跟着变成未知“不等于所有值”的整体结果无法判定为真每一行就都被过滤掉了。这也意味着如果你在维护一套用户排除功能只要黑名单表里误入一条user_id IS NULL的数据所有用户都会被隐藏线上业务直接表现为“列表空白”。性能层面的问题也一样明显优化器倾向于把子查询结果物化成一张临时表再做逐行判断消耗大量内存和临时表 IO和索引定位完全无关。更安全的替代方案是 NOT EXISTS 或 LEFT JOINSELECT u.id, u.name FROM user u WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id u.id );这条写法在语义上等价但在 NULL 面前不会崩而且优化器可以走 semijoin 或依赖关联索引查到一个匹配后就停止。想用 LEFT JOIN 也同样成立只要保证最后过滤条件是b.user_id IS NULL。我在团队里定过一条约定凡遇到“排除若干记录”的需求默认用 NOT EXISTS而不是 NOT IN。3.2 坑五SELECT 字段里塞相关子查询外层多少行它就查多少次给用户列表带上“每个用户的最大消费金额”这个需求太常见了于是也容易写出这种代码SELECT u.id, u.name, (SELECT MAX(p.amount) FROM payment p WHERE p.user_id u.id) AS max_amount FROM user u WHERE u.status 1;功能上完全正确但执行方式极其恐怖外层用户表先过滤出一批结果然后每返回一行数据库就要执行一次内部的子查询。如果u.status 1过滤出来 10 万行这条子查询就会执行 10 万次。哪怕每一次只有 0.1 毫秒加在一起也要 10 秒。你去看执行计划经常能看到“DEPENDENT SUBQUERY”之类的标记意思就是这个子查询和外部查询逐行关联。优化器不是没能耐做自动改写而是这种逐行关联的写法在复杂场景下经常无法被直接改写成一次聚合。正确的思路是把聚合计算先单独做成一张派生表让数据库只聚合一次再和主表关联SELECT u.id, u.name, t.max_amount FROM user u LEFT JOIN ( SELECT user_id, MAX(amount) AS max_amount FROM payment GROUP BY user_id ) t ON t.user_id u.id WHERE u.status 1;这个版本的执行过程是先把 payment 表按 user_id 分组算出每个用户的最大金额生成一份临时结果再用这份临时结果和 user 表做连接。子查询只执行一次而不是外层每一行执行一次。逻辑上同样能得到每个用户的最大消费金额但代价完全变了。如果数据量再往上走还能给这个聚合临时表加索引或者缩小范围可读性和维护性也更好。3.3 坑六用 DISTINCT 掩盖 JOIN 膨胀数据翻倍全靠去重找平第三种关联层的老熟人是“先 JOIN 出重复行再用 DISTINCT 把多出来的行抹掉”SELECT DISTINCT u.id, u.name, u.phone FROM user u LEFT JOIN orders o ON o.user_id u.id WHERE o.status 1;假设一个用户有 20 个符合条件的订单LEFT JOIN 会先产生 20 行重复结果DISTINCT 再把其中 19 行去掉。数据库在这期间做的事情包括生成一个 20 倍的临时结果集把每列数据复制一遍然后做一次排序去重或哈希去重。用户数越多订单数越多这个膨胀就越离谱。数据量到百万级时这种查询极其容易触发磁盘临时表数据库性能直接被打穿。如果 JOIN 条件再写得宽一点生成的重复行还会更多慢的效果会来得更早。更重要的是这种写法往往掩盖了一个事实开发真正想要的可能只是“存在下单记录的用户”而不是“订单和用户的所有组合”。换个表达方式用 EXISTS 既能准确表达业务意图又能避免展开重复行SELECT u.id, u.name, u.phone FROM user u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status 1 );EXISTS 的语义是“只要存在一条满足条件的记录即可”优化器可以按驱动表的每一行去关联子表找到第一条匹配记录后立即返回不会把所有订单都展开。如果业务确实需要返回订单明细那就老老实实按订单维度去查明确指定“每个用户取最新一单”之类的规则而不是让结果先膨胀再靠 DISTINCT 找平。要是在代码评审里看到高频出现的SELECT DISTINCT x.*几乎可以直接问一句你是真的需要去重还是在用去重掩盖 JOIN 产生了多余数据的事实4. 排序、随机和深分页三个“伪需求”拖垮数据库的写法4.1 坑七ORDER BY RAND()把随机数当排序键全表陪你“摇号”活动页要随机展示一个 banner这是最标准的随机取数需求。很多人的第一反应是SELECT id, title, image_url FROM banner ORDER BY RAND() LIMIT 1;这条 SQL 的执行过程是先为 banner 表的每一行生成一个随机数再把整个结果集按照随机数排序最后取第一行。如果表里只有几十行这没有任何问题可一旦表里有百万行数据库就要生成一百万个随机数然后对一百万个值做排序。内存排序放不下就会落到临时文件一次简单的“随机取一条”直接变成全表扫描加文件排序耗时可能从 10 毫秒涨到好几个数量级。这类需求的关键问题是数据库真的有必要承担这个随机过程吗如果随机结果不需要绝对均匀可以在后端维护一个“随机池”定期把所有候选主键加载到应用缓存每次请求时在应用层随机挑一个主键再回到数据库按主键查询一行。这个方案的响应时间无比稳定完全不会因为表变大而变慢而且还能顺便做频次控制和去重。如果就想着数据库里一步搞定也可以用主键范围去猜SELECT id, title, image_url FROM banner WHERE id ( SELECT FLOOR(RAND() * (SELECT MAX(id) - MIN(id) 1)) MIN(id) FROM banner ) LIMIT 1;但这方案在主键稀疏时可能返回空行需要再做一层兜底复杂度反而上去了。所以我的排序思路永远是先确认业务到底需要多“随机”如果只是“每次看到的不一样”完全可以把随机这件事从数据库里挪走。业务需求的表象是“随机”本质可能是“变化”把伪需求修正了SQL 自然就轻了。4.2 坑八OFFSET 深分页越往后翻越像一场灾难分页接口最常见的写法是SELECT id, title, content FROM article ORDER BY id DESC LIMIT 20 OFFSET 999980;看起来数据库好像“跳过”了前 999980 行只返回最后 20 行。实际上它做不到。为了拿到这 20 行数据库必须按照ORDER BY id DESC的顺序把前 999980 行的主键扫描出来然后一页一页地数到第 999981 行才开始返回。意味着页码越深扫描的行数越多接口响应时间越长而且这个劣化是线性的——第 50 万页比第 10 万页慢 5 倍这不是固定开销能兜住的。更好的方案是游标分页也就是 keyset pagination客户端把上一页最后一条记录的排序值传回来下一页的条件写成WHERE id last_id ORDER BY id DESC LIMIT 20。数据库直接从上一页的位置开始读取 20 条页码的深浅彻底和查询代价解耦。如果排序字段不是主键比如按sort_time排序那就用排序字段加主键共同构造游标条件WHERE (sort_time last_sort_time) OR (sort_time last_sort_time AND id last_id) ORDER BY sort_time DESC, id DESC LIMIT 20;同时要给(sort_time, id)建联合索引这个条件才能直接用上 B 树定位。需要说明的是游标分页不支持用户直接跳转第 50 万页这种需求更像是一个“伪分页”。产品上真要支持深渊跳转更合理的做法是限制可查询的最大深度或者干脆换一种交互方式比如按日期分段翻页。数据库不是用来数一百万行再扔掉的它应该只负责自己真正需要的数据。4.3 这三种需求的共同特征把不必要的压力交给数据库排序、随机、深分页看起来是三个独立问题但它们有一个共性业务对结果精度的要求没那么高代码却把数据库当成了大型计算中心。很多人天然觉得数据库反正有排序能力那就让它排数据库支持RAND()那我就用它分页参数传过来多少 offset就让它翻多少。这些逻辑放到应用层用缓存、预生成列表、游标指针能做得很漂亮放到数据库里就成了每一条请求都要承担重型计算。我每次做技术方案时会多问自己几个问题这里的随机真的每次都要变吗这里的排序能事前排好吗这里真的允许用户翻到几十万页吗多问几句以后会发现很多看似合理的 SQL 其实来自一个未经验证的“伪需求”。优化 SQL 是治标修正需求才是治本。5. 把这些坑挡在代码之外团队自查的五个动作5.1 用 EXPLAIN 建立“慢 SQL 雷达”看执行计划是识别这些写法最直观的手段。你不需要精通优化器内部实现只要掌握几个关键信号就行。type列从好到坏大致是const、eq_ref、ref、range、index、all看到all就说明是全表扫描要格外警惕rows列是优化器估算的扫描行数如果和表总行数接近说明这条 SQL 已经退化成了全表操作Extra列出现Using filesort或Using temporary时就要思考排序和去重是不是可以避免。如果执行计划里同时出现Using index condition说明索引下推被启用通常不算坏信号但也不能因此忽视最前面的type。我习惯让团队成员在提交 SQL 前跑一次EXPLAIN哪怕只是看一眼 type 和 rows。时间久了大家就会形成一种直觉哪些写法会导致扫描多少行、哪种执行计划才算正常。不用看整篇调优文档执行计划本身就是最好的老师。5.2 慢查询日志是同事写法的“照妖镜”真等到生产环境出事故再排查代价就太大了。更好的做法是把数据库慢查询日志打开阈值设成 200 毫秒或者更保守的数值每周固定拉一次 TOP 20 慢 SQL逐个分析。你会发现这些慢 SQL 经常出自同一种风格高度集中在少数几个反模式里。我实际处理过一条查询同事用 LEFT JOIN 做一对多关联又用 DISTINCT 去重数据量到 200 万行以后接口从 400 毫秒直接飙到 6 秒多我改成 EXISTS 版本后耗时稳定在 40 毫秒左右将近上百倍的提升。慢查询日志不一定是为了追责它最核心的价值是帮你验证团队里到底谁还在用聚合方式掩盖模型问题。5.3 把它沉淀成一份“SQL 代码评审清单”个人经验代替不了团队规范。与其让每个新人都重新趟一遍这些坑不如把前面说的反模式整理成一份评审清单挂在代码评审流程里。例如查询条件里的索引列是否保持裸用字段类型和参数类型是否一致LIKE 是否使用了前置通配符SELECT 字段中是否嵌套了相关子查询排除逻辑是否使用了 NOT IN关联查询是否依赖 DISTINCT 来找平是否存在ORDER BY RAND()分页是否允许深 OFFSET这些检查项每一条都可以用“是/否”回答新人照着打钩很快就能适应老手看到类似写法也会形成条件反射。5.4 给容易踩坑的查询建“示例库”规范文档容易写得抽象真正有说服力的是反面样例和正面方案的对照。可以整理一个内部文档把每一种反模式都配上坏写法、好写法、执行计划对比、在某个数据量下的实际耗时。新人入职不用先去啃整本数据库优化书先看这份示例库基本上就能避开 90% 的低级问题。这些样例也是很好的培训素材比讲抽象理论直接得多。SQL 性能问题大多数时候不是“不会写”而是“没意识到写法对优化器的影响”示例库的作用就是把这个意识提前种下去。5.5 设计层面的提醒别忘了对应的表结构写法再标准如果表结构缺了合适的索引一样会慢。自查清单的最后一步永远要回到索引设计上WHERE、ORDER BY、GROUP BY 里用到的条件字段有没有设计对应的联合索引例如查询条件是WHERE status 1 AND created_at 2024-01-01 ORDER BY created_at那就应该考虑(status, created_at)这个联合索引让它既能过滤又能排序。优化写法和建索引是双管齐下的事只改 SQL 不改索引或者只加索引不改写法都很难拿到理想的效果。这些坑我自己全踩过也帮别人擦过好多次屁股。SQL 性能优化的核心真不复杂写完一条查询先别急着跑花十秒钟想一下这个条件会不会让索引失效这次排序是不是真的需要数据库来做这个分页是不是实际会翻到那么深。功能正确只是第一步写法经得起执行计划推敲才算完整。以后要是再看到同事写出类似的代码把这篇拆解放给他也算是一种很温柔的提醒了。
