★ FEATURED ARTICLE 先讲个真实场景。上周系统一个列表接口突然从 50ms 飙到 2 秒多数据库 CPU 居高不下。慢查询日志里排第一的 SQL 看起来人畜无害只是多了一个 create_time 条件。我点开执行计划typeALLrows 从几千直接跳到 300 万。加在 created_at 列上的 DATE() 函数让索引彻底失效数据库只能一路扫全表。这种“专坑同事的 SQL 写法”特点就是小数据量时完全无感上线一跑大表性能直接掉两个数量级。我整理了实际踩过、也帮同事收拾过的 8 种坑爹写法适合后端开发、DBA、写数据报表的同学对照自查。不绕弯子直接按“为什么慢、怎么改、有什么注意事项”的顺序讲看完至少能在 Code Review 里少放几句狠话。1. 先搞清楚一条SQL是怎么被拖慢的1.1 扫描行数才是成本核心数据库的查询成本核心不是“查得难不难”而是“需要摸多少行数据”。让数据库按主键查一条记录它顺着B树索引往下走几十次节点跳转就能定位处理的行数就一行。可一旦写成全表扫描数据表有300万行它就得实打实地访问300万行行数差了几个数量级耗时自然翻几十上百倍。这跟查电话号码簿是一样的道理按姓名索引翻几页就能找到和从第一页一直翻到最后一页去一个个找工作量根本不是一个量级。很多时候同事改了一行看似“等价”的写法查询结果没变执行计划却从“索引定位”变成了“全量遍历”。在一张几十万行的小表上差距可能只有几十毫秒大家都不觉得有问题等到生产环境千万行延迟就从秒级变成分钟级接口超时告警马上铺天盖地。“性能降低100倍”不是夸张修辞扫描行数每多两个数量级耗时基本成比例上涨IO、CPU、内存全部一起扛。这种差距在慢查询日志里表现得很直观。我真见过同一张表、同一套代码数据量从 80 万涨到 800 万一条本来 10ms 的查询变成 8 秒正好百倍。所以判断SQL好坏第一个要问的问题永远是它到底扫描了多少行。1.2 用EXPLAIN提前嗅出危险信号判断一条SQL是不是坑不用靠猜EXPLAIN 一眼就能看出信号。EXPLAIN 输出里的信息很多但大多数人只盯着 key 列看有没有用上索引其实另外三个字段更关键type 字段从 const/ref/range 变成 ALL 或 index意味着索引没有起作用。rows 字段如果比合理值大出几个数量级说明优化器预估要扫描的行数失控。Extra 里出现 Using filesort 或 Using temporary大概率有排序、去重或中间结果集的问题。我每次都让同事先跑一句 EXPLAIN再看这几列。比如EXPLAIN SELECT * FROM orders WHERE DATE(created_at) 2024-11-20;结果大概率是 typeALL、keyNULL、rows几百万、ExtraUsing where。就这么一句输出问题就摆在台面上了。这里要强调一个原则判断SQL好坏永远先看执行计划而不是看查询结果对不对。功能正确只代表逻辑没崩性能问题往往藏在执行路径里。养成“写完SQL顺手EXPLAIN”的习惯能过滤掉80%的坑。2. 三种最经典的索引杀手函数、隐式转换、前导通配2.1 在索引列上套函数索引瞬间失效函数包裹索引列是生产事故率最高的写法之一。典型长这样SELECT * FROM orders WHERE DATE(created_at) 2024-11-20;需求很明确查某一天创建的订单。可问题在于 created_at 上明明有索引DATE(created_at) 一包索引的有序性就失效了。数据库的B树索引存储的是原始字段值的排列不是函数结果的排列。优化器没法利用“原始值有序”去跳转定位只能把每一行的 created_at 都取出来做一次 DATE() 计算看满不满足条件本质就是全表扫描。这也是几乎所有函数套索引列的通用悲剧UPPER(name) TOM、YEAR(publish_time) 2024、SUBSTR(phone, 1, 3) 138写法一个比一个自然慢起来一个比一个不讲道理。正确做法是把函数从列上挪走改造成范围条件SELECT * FROM orders WHERE created_at 2024-11-20 00:00:00 AND created_at 2024-11-21 00:00:00;用半开区间 [当天零点, 次日零点) 去匹配既能吃到索引也不会漏掉当月最后一天、跨年这种边界数据。这个改法比“把DATE格式化成字符串再比较”更稳因为字符串比较一样会导致索引失效。有人说新版数据库支持函数索引那确实能缓解但函数索引需要额外维护成本不是默认免费的日常代码规范里还是先要求别这么写。2.2 隐式类型转换类型没对上就开始全表扫隐式类型转换是另一种“看起来完全没问题”的坑SELECT * FROM user WHERE phone 13800138000;假设 phone 字段是 varchar 类型你传进去的是一个数字。数据库比较时会把字符串转成数字再和数字常量比。这个转换动作落在索引列上相当于又给列套了一层函数索引一样失效。更麻烦的是这类SQL用功能测试根本测不出来运行结果和正确写法一模一样只是执行计划从索引扫描变成了全表扫。等到生产环境数据量大了才爆发回溯成本就高了。正确写法非常简单字符串列就用字符串常量别偷懒SELECT * FROM user WHERE phone 13800138000;关联查询里也一样两个表关联字段类型不一致或者字符集不一致都可能触发隐式转换导致本该走索引的JOIN变成逐行匹配。我的习惯是在设计表结构时就统一编码和字段类型ID全用同一类型手机号、订单号这类纯字符串字段就统一 varchar并且所有代码里的查询常量保持同名同型。实现层面没有黑魔法全是规范的事。2.3 LIKE前导通配符越模糊越慢LIKE 前导通配符几乎每个搜索需求都会遇到SELECT * FROM article WHERE title LIKE %性能优化%;这条SQL的问题在于百分号在开头。B树索引只能按前缀匹配定位记录’性能优化%‘ 这种前缀写法的确能走索引但 ’%性能优化%‘ 意味着数据库不知道要匹配的内容从哪里开始它只能扫描整棵索引树或者直接全表扫。越模糊越慢一点也不含糊。如果业务场景只要前缀匹配改成LIKE 性能优化%就能激活索引。但如果产品要的是“标题中含某某词”的任意匹配那就别硬写 LIKE 了。小表数据量可控时全表扫描还能接受百万行以上建议直接评估全文索引或者独立的搜索引擎方案把分词、匹配、排序都交给更专业的东西。我这个观点可能有人觉得重但线上模糊搜索把数据库拖垮的例子我见过太多次与其等事故不如早做选型。3. 三种隐藏在查询设计里的坑OR、NOT IN、SELECT *3.1 OR条件优化器看到就头疼OR 是优化器最头疼的语法之一。写起来很直观性能却很任性SELECT * FROM payment WHERE state 1 OR state 2;表面看两个条件都能命中索引实际上优化器更难处理多个索引的交并集。有些版本会启用 index_merge先分别扫两个索引再把结果合并去重更常见的是只要OR两边有一个条件没法快速定位优化器就会干脆放弃所有索引直接全表扫。你永远不知道它这次是不是心情好。最简单稳妥的改法是换成INSELECT * FROM payment WHERE state IN (1, 2);IN 对优化器来说友好得多可以直接走索引范围扫描语义也完全等价。如果OR牵扯到不同列、各自都有独立索引拆成UNION ALL往往比OR更可控但每个分支都要用EXPLAIN确认能走上索引。我的经验是以后在代码里看到OR第一反应就是先改成IN改不了再琢磨拆分支。这个习惯能让一大波慢SQL在出生前就被掐死。3.2 NOT IN与NULL的三值逻辑陷阱NOT IN 的坑分两层一层是逻辑一层是性能。先看逻辑SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);看着人畜无害但SQL是三值逻辑NULL 参与比较的结果不是真也不是假而是未知。如果 b.id 里出现了 NULLNOT IN (1, 2, NULL) 对任何一行都不会返回真最终结果就是空集合。也就是“明明有数据查询结果却啥也没有”而且测试数据少的时候根本发现不了。性能层面NOT IN 子查询也经常导致全表扫描。我推荐用NOT EXISTS或者LEFT JOIN IS NULL来改写SELECT a.* FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id a.id); SELECT a.* FROM a LEFT JOIN b ON a.id b.id WHERE b.id IS NULL;NOT EXISTS 在数据库优化器眼里是半连接处理起来比NOT IN聪明LEFT JOIN 写法配合右表主键也能走索引。一句话总结代码里看到NOT IN先查右边有没有NULL再想怎么改成NOT EXISTS。这个坑要不是踩过一次我也不会每次都条件反射式地检查。3.3 SELECT * 加无索引排序COGS全开SELECT * 加无索引排序是列表页慢查询的另一大源头SELECT * FROM logs ORDER BY created_at DESC LIMIT 20;如果 created_at 上没有索引数据库只能把符合条件的所有行先捞出来在内存或磁盘上做一个 filesort 排序再取最后20条。行数少时没问题几百上千万行时这个排序临时结果集的成本和IO开销会直接暴涨。就算排序列有索引SELECT * 也会把每条记录的所有字段都读一遍大字段更是雪上加霜最后只需要20条中间却捞了海量无用数据。优化方式有两种。第一是给排序列建合适的组合索引让排序直接复用索引的有序性第二是延迟关联先只查主键再回表取整行SELECT l.* FROM logs l JOIN (SELECT id FROM logs ORDER BY created_at DESC LIMIT 20) t ON l.id t.id;子查询走的覆盖索引只取id扫描量小得多最后再按20个id回表查询。团队规范里我一般直接要求列表页禁止SELECT *用多少字段写多少字段为高频列表页单独设计索引。这样既省IO又省网络包查询快很多。4. 深分页和JOIN时机两个容易爆发的隐藏地雷4.1 深分页LIMIT offset越翻越慢深分页是那种“迟早会出事”的写法SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;LIMIT 100000,20 看起来只取20条但数据库为了知道第100000条在哪必须把前面的100000行都扫描一遍再丢弃。如果排序列没索引连前面的行数都没法快速定位就只能全表扫。访问越深的页浪费越多整体响应时间基本线性增长。用户狂点下一页数据库就开始告警。我用的优化方案是键集分页也叫游标分页核心思路是“用上一页的最后一条记录作为下一页起点”SELECT * FROM orders WHERE id 100000 ORDER BY id DESC LIMIT 20;这样每一页都只扫描20行页码大了速度也不会退化。如果排序字段不是主键就选一个稳定唯一有索引的字段来当游标。有些场景产品一定要支持“跳到第100页”这时可以退而求其次先用覆盖索引查出这一页的id集合再JOIN回原表取整行至少比直接SELECT * 深翻页快得多。我实验过同样的列表接口从LIMIT百万级offset改成游标分页响应时间能降两个数量级真实不虚。4.2 ON和WHERE的过滤时机一个动作两种结果JOIN 的过滤时机新手和老手经常写出完全相反的写法。先看这段SELECT u.*, o.* FROM user u LEFT JOIN orders o ON u.id o.user_id WHERE o.status 1;这个SQL的WHERE条件出现在LEFT JOIN之后会把右表为NULL的行全部过滤掉最终效果等价于INNER JOIN。如果业务想保留没有订单的用户这里已经在悄悄埋雷。反过来的坑是SELECT u.*, o.* FROM user u LEFT JOIN orders o ON u.id o.user_id AND o.status 1;条件放进ONLEFT JOIN的语义保住了但结果里会出现大量订单为NULL的用户行能不能接受要结合业务定。更重要的是ON和WHERE的差异会影响中间结果集一个动作两种结果查错一次就是一次返工。性能和JOIN时机也有关系。多表关联时如果过滤条件全部堆在WHERE阶段优化器可能会先生成大得吓人的中间结果再过滤尤其是主表大、副表更大的场景。我会先把需要过滤的副表用子查询缩小范围例如SELECT u.*, o.* FROM user u LEFT JOIN ( SELECT * FROM orders WHERE created_at 2024-11-01 ) o ON u.id o.user_id;子查询先过滤再关联中间结果集小得多。优化器不一定会完全按这个顺序执行但在可读性和结果可控性上明确写出意图总比让优化器猜更合理。5. 慢SQL排查实录与团队避坑指南5.1 五分钟定位一条坑同事SQL如果怀疑同事写了一条坑SQL最快的定位路径是开慢查询日志。以MySQL为例SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;超过1秒的查询会被记进日志再用日志聚合工具做排序开头几分钟就能看到排在最前面的犯罪现场。拿到具体SQL之后不要急着改先拿EXPLAIN看执行计划重点看type和rows对照上面提到的信号判断问题出在哪个环节。如果环境允许EXPLAIN ANALYZE 还能给出实际执行时间和各步骤返回行数比预估更准确但这个会真实执行SQL千万别在生产大表上随便跑最好在测试环境或只读从库上验证。我的排查习惯是慢日志定位SQLEXPLAIN定位路径最后再决定是加索引还是改写法三步走完基本十分钟内能给出结论。5.2 “看着没毛病其实很坑”的SQL速查表把前面提到的8种坑做成一张速查表贴在工位上正合适坑写法后果推荐写法SELECT * 查列表全字段回表IO与网络放大按需字段或延迟关联WHERE DATE(col)...函数包列索引失效半开区间范围比较WHERE phone数字隐式转换索引失效字符串常量加引号LIKE %关键词%前导通配全表扫描前缀匹配或全文检索WHERE a1 OR a2优化器头疼索引失效改写成 INNOT IN 子查询全表扫 NULL空结果NOT EXISTS 或 LEFT JOIN IS NULLLIMIT 100000,20深分页扫描海量行游标分页或延迟关联LEFT JOINWHERE右表条件LEFT JOIN退化成INNER JOIN条件按语义放ON或明确INNER JOIN这张表我每次Code Review都会带一份。看到对应的影子直接贴执行计划截图用rows数据说话比反复劝“这样写会慢”管用得多。5.3 让团队少写坏SQL的三条约定最后说三条能落地的团队约定。第一高并发、大数据量的核心查询在代码评审时必须附EXPLAIN输出rows增长超过预期阈值就得讲清楚理由。第二各种ORM框架生成的SQL也别因为是“框架生成的”就跳过审查这类工具一样会生成上面这些坑写法也许还更难发现。第三每个季度把线上慢查询日志里新出现的SQL模式整理成案例补进团队知识库。踩过的坑不写下来下次换个人照样会掉进去。我个人的体会是坏SQL很少是故意挖的坑更多是“当时没看执行计划”的疏忽。只要团队里形成“写SQL先EXPLAIN、Review看执行计划、出问题先查慢日志”这三步习惯大部分坑在源头上就会被拦下来。这8种写法我建议直接保存成清单下次排查慢查询的时候挨个对照一遍多半能命中。
阅读完成 · 觉得有帮助？
