我们平时写SQL最多的就是查数据。不管是做个简单报表、给前端写个接口还是排查线上问题七成以上的时间都耗在SELECT语句上。可大多数人对DQL的理解停留在“能用就行”一旦遇到筛选条件复杂、多表关联、分页性能这些场景写出来的SQL要么结果不对要么慢得要命。这篇就围绕数据查询语言DQL把那些容易出细节坑的地方掰开揉碎讲一讲。这篇文章没有特别高深的理论但我会把日常开发里最常用的查询场景、最容易被忽略的执行细节、还有我实际踩过的坑都拉出来过一遍。不管你是刚学SQL的初学者还是写过两年SQL但总感觉差点意思的开发者应该都能在这里面找到对你有用的东西。1. 查询语句的基本骨架FROM和WHERE的执行次序才是关键很多人第一次学SELECT都是从“SELECT后面写列名FROM后面写表名”开始的。这没错但只理解了书写顺序没理解执行顺序。理解执行顺序是排查SQL问题的基础也是理解后面所有查询陷阱的前提。1.1 SELECT的书写顺序与真正的执行顺序我们通常写的SQL是这样的SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 分组字段 HAVING 分组后的条件 ORDER BY 排序字段 LIMIT 条数但数据库引擎真正执行的时候顺序大致是这样的FROM确定要读取哪张表可能还会先做JOIN。WHERE对来源数据做第一轮筛选把不满足条件的行直接丢掉。GROUP BY把筛选后的行按指定字段分组。HAVING对分组后的结果再做筛选。SELECT真正计算和投影出需要的列。ORDER BY对已经算出来的结果排序。LIMIT截取需要的部分。这个顺序记不住也不要紧但你一定要知道WHERE是在分组之前执行的这句话决定了“WHERE筛选”和“HAVING筛选”完全是两个时机。1.2 一个报表需求引发的次序思考举个例子一个订单表我想按月份统计“下单金额大于100元的订单数量”。如果我先分组再筛那是先算每个月有多少单再筛金额如果我先筛再分组那金额大于100的单才进入统计这两种写法结果是完全不同的。-- 正确写法先筛掉金额不足100的单再分组统计 SELECT DATE_FORMAT(order_time, %Y-%m) AS month, COUNT(*) AS order_cnt FROM orders WHERE amount 100 GROUP BY DATE_FORMAT(order_time, %Y-%m);把WHERE条件写在分组前面表面上是语法习惯实际上是对执行时序的尊重。我在工作中见到过不少“统计数字对不上”的问题查到最后都是这种执行顺序理解偏差而不是数据库算错了。2. 字段筛选的细节NULL、空字符串与LIKE模糊查询的边界SELECT的字段列表看起来是最不用动脑子的部分其实这里藏着很多让人头疼的细节。尤其是NULL的处理几乎每个写过半年以上SQL的人都至少被它坑过一次。2.1 为什么NULL总是“查不出来”很多人会写这样一个条件筛选备注不为空的记录SELECT * FROM user WHERE remark ! ;然后发现结果里少了某些看起来remark字段“什么都没有”的行。原因很简单如果remark字段的值是NULL那么表达式remark ! 的结果既不是TRUE也不是FALSE而是NULL。在WHERE条件里只有结果为TRUE的行才会保留所以那些NULL值的行就被过滤掉了。正确的写法应该是同时排除NULL和空字符串SELECT * FROM user WHERE remark IS NOT NULL AND remark ! ;这里有个容易混淆的点IS NULL和IS NOT NULL是专门用来判断NULL的不能用 NULL或者! NULL。我见过太多新手写WHERE remark NULL然后一脸疑惑问为什么查不出来。NULL不参与等值比较这是SQL里一个非常反直觉但必须接受的设计。2.2 模糊查询中很容易忽略的通配符LIKE模糊查询也是高频使用的功能但很多人只知道%表示任意多个字符却忽略了_表示单个字符。例如-- 匹配“张”开头后面跟一个任意字的姓名比如“张三”“张伟” SELECT * FROM user WHERE name LIKE 张_; -- 匹配“张”开头后面随便多少个字的姓名 SELECT * FROM user WHERE name LIKE 张%;这两个结果完全不一样。如果你业务上想搜“张”开头的所有用户写成张_就只会匹配到两个字的姓名三个字的名字就漏掉。还有一个常见的坑是LIKE里的通配符转义。如果业务数据里真的包含%或_字符比如商品名称叫“50%折扣”那直接LIKE %50%折扣%会把“50折”“150折扣”这种也匹配出来。需要用ESCAPE指定转义字符SELECT * FROM product WHERE name LIKE %50\$%折扣% ESCAPE \;这类细节平时没人提但真在数据里遇到带特殊字符的内容写不对就会产生莫名其妙的脏数据。2.3 字段列表里的运算与别名陷阱SELECT里可以做运算比如SELECT price * quantity AS total FROM orders。这里有个容易忽略的点别名是在SELECT阶段才生成的所以WHERE阶段不能直接用别名做条件。-- 这样是报错的 SELECT price * quantity AS total FROM orders WHERE total 1000; -- 改成这样 SELECT price * quantity AS total FROM orders WHERE price * quantity 1000;原因还是那个执行顺序WHERE先执行那时候别名还不存在。很多人从写Excel公式的习惯过来非常容易在这里卡住。3. 排序与分页不是简单的ORDER BY和LIMIT排序和分页看起来简单实际用起来问题最多。一个是ORDER BY的排序规则不够直观另一个是LIMIT分页在数据量大之后性能急剧下降还有深分页时数据偏移的坑。3.1 多字段排序的规则与NULL的排位ORDER BY支持多字段排序这个是大家都知道的SELECT * FROM products ORDER BY category_id ASC, price DESC;它的含义是先按category_id升序排在category_id相同的情况下再按price降序排。很多人会把这条理解成“category_id升序、price降序各排各的”这就会导致排序结果和预期完全不一致。还有一个容易被忽略的点NULL在排序里的位置。默认情况下MySQL里NULL被认为是比任何非NULL值都小的所以升序排序时NULL会排在前面。这不是所有数据库都一样Oracle就默认NULL排最后。如果你对NULL的排序位置有明确要求最好写清楚-- 把NULL强制排在最后 SELECT * FROM products ORDER BY category_id IS NULL, category_id ASC;这种写法利用了一个技巧IS NULL的表达式结果在MySQL里是0或1直接作为排序键使用。3.2 分页的经典翻车现场ORDER BY LIMIT再说一个我实际遇到过的问题。某次做分页接口第一页和第二页数据有重复排查了半天发现是ORDER BY的字段不唯一导致的。比如按创建时间排序分页如果有大量记录创建时间相同那么它们的排列顺序是不确定的数据库每次查询可能给出不同的相对顺序于是翻页时就会出现同一行出现在不同页里。解决办法是让排序组合唯一确保每一行的相对顺序稳定SELECT * FROM comments ORDER BY created_at ASC, id ASC LIMIT 20 OFFSET 0;如果排序字段本身没有唯一性必须加一个唯一字段作为第二排序键。这是分页接口必须养成的习惯不然后期数据一多必然有人反馈“翻页数据重复”或“翻页漏数据”。3.3 深分页的优化思路LIMIT 1000000, 20这个写法在数据量小的时候没有问题但一旦表里数据上了百万深分页就会让数据库扫描大量无用的行。原因是数据库得先跳过前面一百万行再取后面那20行。一种通用的优化思路是使用“延迟关联”或“书签”的方式。书签方式SELECT * FROM comments WHERE id 1000000 ORDER BY id ASC LIMIT 20;它的核心是记住上一页最后一条记录的id下一页从这个id之后继续取。这样数据库不用再从头扫描效率会提升很多。缺点是只能支持“下一页”的连续翻页不能随意跳到任意页但对大多数前端交互下拉加载更多来说完全够用。4. 聚合与分组GROUP BY是DQL里最考验逻辑的语法聚合查询是DQL里逻辑最绕的部分。很多人写聚合查询时会发现为什么我GROUP BY出来的结果跟我想的不一样为什么有的字段不能SELECT这些问题如果不搞清楚做统计数据的时候经常会被自己坑到。4.1 WHERE与HAVING的适用边界前面说执行顺序的时候已经提到了WHERE在分组前执行HAVING在分组后执行。这个差异直接影响统计口径。举个例子我要统计“每个分类下商品数量超过10个的分类”。这个条件不能用WHERE直接筛因为“超过10个”是基于分组后的聚合结果来判断的必须用HAVINGSELECT category_id, COUNT(*) AS cnt FROM products GROUP BY category_id HAVING cnt 10;但反过来如果我要统计“状态为已上架的商品每个分类下有多少个”那“状态为已上架”是对单行数据的筛选必须在WHERE里处理SELECT category_id, COUNT(*) AS cnt FROM products WHERE status 1 GROUP BY category_id;理解这两者边界最简单的方式就是问自己这个条件施加后还能不能看到原始的行能就放WHERE不能就放HAVING。能回答清楚这个问题聚合查询基本就过关了一大半。4.2 为什么SELECT后面的字段必须出现在GROUP BY里这是一个让很多新手崩溃的报错“Expression #2 of SELECT list is not in GROUP BY clause”。比如-- 错误示例 SELECT category_id, product_name, COUNT(*) FROM products GROUP BY category_id;报错的原因很直白每个分类可能对应多个不同的product_name数据库不知道该把哪个product_name放进结果里。除非你用的是MySQL的ONLY_FULL_GROUP_BY关闭状态否则会直接报错。如果你想在分组后返回一个分类下的商品名列表可以用GROUP_CONCATSELECT category_id, GROUP_CONCAT(product_name) AS names, COUNT(*) AS cnt FROM products GROUP BY category_id;想取分组后某个字段的最大值、最小值、平均值就用MAX、MIN、AVG。规则就是出现在SELECT里的非聚合字段要么出现在GROUP BY里要么被聚合函数包裹。这个规则听着简单但我见过太多人写出模棱两可的SQL还抱怨数据库“不讲道理”。4.3 COUNT(*)与COUNT(1)和COUNT(字段)的微妙差异COUNT(*)和COUNT(1)在绝大多数数据库里性能没有区别它们都是统计满足条件的行数。但COUNT(字段)就不一样了它统计的是该字段非NULL的行数。这个差异会导致统计结果不一样。我之前接过一个排查需求业务方说统计出来的用户数是10000但明细数据里明明有10300行。查了半天发现统计SQL里面写的是COUNT(user_name)而有一部分用户没有填写user_name这些行在COUNT(字段)时被自动忽略了。最后改成COUNT(*)结果才对上。所以如果你要统计的是行数优先写COUNT(*)。如果你想统计某个字段有值的行数那就要明确知道COUNT(字段)的语义再写。5. 多表连接JOIN的条件和子查询是两套思路到了多表关联环节DQL的复杂度一下子上来了。JOIN用得好可以高效地把分散在不同表里的信息拼接在一起用得不好轻则结果翻倍重则把数据库连接池打满。这一节我会把JOIN的常见坑和JOIN与子查询的取舍讲清楚。5.1 连接条件不完整的“数据翻倍”事故JOIN最典型的坑是忘记写连接条件或者写了连接条件但没写充分导致笛卡尔积。比如订单表和订单明细表关联一个订单有3条明细。正确的逻辑是每个订单对应3条明细结果总数应该是订单数乘3。但如果连接条件写错了订单表里每行都会和明细表里所有行组合数据就会爆炸性展开。-- 危险写法漏了关联条件 SELECT * FROM orders o, order_items i; -- 应该使用明确的JOIN SELECT * FROM orders o INNER JOIN order_items i ON o.id i.order_id;在输出报表字段时如果不小心把明细表里的金额字段和订单表里的金额字段同时GROUP BY非常容易得到翻倍后的统计值。排查这类问题我的经验是先分别对两张表做COUNT确认基数再对JOIN后的结果做COUNT如果结果不是两个基数的乘积那基本可以断定连接条件有问题。5.2 INNER JOIN/LEFT JOIN/RIGHT JOIN的适用场景INNER JOIN是取两张表的交集适合“两边都存在才展示”的场景。LEFT JOIN是以左表为主右表没有匹配时用NULL填充适合“主表全部展示附属信息有则补充”的场景。RIGHT JOIN在日常业务里用得少因为大部分时候把表的顺序换一下RIGHT JOIN就能改成LEFT JOIN。在实际项目里LEFT JOIN是出现频率最高的连接方式尤其是做报表的时候主表是订单列表左连用户表拿用户昵称左连区域表拿地区名称。这种情况下你要时刻保持一个警觉右表如果有多条匹配记录左表的行会被展开。所以LEFT JOIN的右表关联字段最好有唯一性否则结果行数会莫名其妙变多。5.3 子查询和JOIN的取舍子查询在可读性上很有优势尤其适合那种“先得到一个结果集合再基于这个集合去查询”的思路。比如查“下单次数超过5次的用户信息”SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) 5 );这个写法非常直观适合初学者理解。但要注意性能子查询里如果涉及大表或者IN后面的集合特别大查询效率可能会很差。这种情况下通常可以用JOIN改写SELECT u.* FROM users u INNER JOIN ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) 5 ) t ON u.id t.user_id;不过JOIN改写也不是万能药尤其是遇到关联列上没有索引的时候JOIN也可能很慢。我的习惯是先看执行计划EXPLAIN再决定方案而不是凭感觉选。对于开发者来说从“能查出结果”到“能解释为什么这个写法更快”是SQL能力的一个分水岭。5.4 EXISTS与IN的选择还有一个经典的性能取舍EXISTS和IN。对于“外层表小、内层表大”的场景EXISTS通常更高效对于“外层表大、内层表小”的场景IN往往更合适。不过这不是绝对的和数据库版本、统计信息、索引情况都有关系。实际项目中我经常用的判断方法是把这个子查询单独拿出来跑一下看它返回的数据行数。如果子查询的结果集很小我会优先IN如果子查询结果集很大优先改成EXISTS或JOIN。还有一个容易被忽视的点IN的子查询结果里不能有NULL否则可能会出现结果丢失的问题。6. 查询设计里的工程习惯与软技能写DQL不只是写对语法还要写出可维护、可排查、性能稳的查询。这一节算是我在工作里总结出的一些“软技能”未必是SQL语法本身但对实际项目长期运行非常重要。6.1 给表和字段起名的可读性SQL写多了之后你会发现表名字段名的好坏直接影响语句的阅读成本。比如一张用户表叫user字段有id、name、phone、created_at这看起来平平无奇但它比那种用user_tbl、col1、col2命名的表好维护太多。好的命名习惯是表名用单数或复数都可但同一项目里要统一字段名用snake_case时间字段要带_at或_date后缀明确粒度。我见过最痛苦的维护场景是一张表叫tab_a字段叫f1、f2、f3业务逻辑全靠程序里的注释解释。这种方式在刚开发时省了几秒取名时间但后面每次写DQL都是一次猜谜。给自己留一点写名字的时间长期看是稳赚不赔的。6.2 用EXPLAIN读懂查询是否走对了索引SQL性能出现问题第一件事不是改SQL而是看执行计划。MySQL里用EXPLAINPostgreSQL里用EXPLAINSQL Server里是显示执行计划但逻辑都是类似的看这个查询是不是做了全表扫描、有没有用到索引、扫描了多少行。EXPLAIN SELECT * FROM orders WHERE customer_id 123;如果看到type列是ALL说明是全表扫描customer_id上大概率没有索引。如果type列是ref或range说明用到了索引。参数方面我习惯重点看三个东西可能的索引possible_keys、实际使用的索引key、扫描行数rows。只要rows特别大即使type看着没问题也需要进一步优化。优化思路通常是两条路一是加索引二是在SQL层面减少扫描范围比如把时间范围收窄、把不必要的排序去掉。整体来说DQL的调试过程就是一个“从结果对不对”逐渐过渡到“从性能稳不稳”的过程。6.3 在线查询的安全意识DQL虽然只是查询语言但仍有一件事要时刻记住查询语句里不要直接拼接用户输入。典型的风险是SQL注入比如前端传入一个搜索关键词后端直接拼成SQL字符串执行。看似无害的搜索框可能被恶意传入包含破坏性语句的文本。参数化查询是基础防线在ORM里也就是预处理语句。不要把拼接SQL字符串的习惯带到生产环境。很多框架已经强制参数化但如果你手写SQL一定要留心。DQL本身没有攻击性但组合了不安全的使用方式就另说了。6.4 慢查询日志是一个很好的体检入口如果线上经常有慢SQL建议把数据库的慢查询日志打开让超过一定时间的查询自动沉淀到日志文件里。定期看一眼这些日志通常能发现不少问题某个时间段前后的查询特别多、某些查询扫描行数巨大、某些查询连表太多但结果集很少。这些都是DQL层面的优化线索。我在一次排查里碰到过一个让人印象深刻的案例一个列表页SQL本身很简单但因为它左连了四张表其中有两张表的数据量很大每次查询都要做大量临时表操作。优化它并不是改写SQL而是先思考业务是否真的需要同时展示这么多关联字段。很多时候减少一次JOIN比任何SQL调优都更有效。6.5 查询结果的验证方式DQL写完不能只看“有没有返回数据”还要看“返回的数据对不对”。我习惯用两个辅助手段来做验证一是COUNT(*)粗查总行数和数据源做交叉验证二是对关键字段做SUM或者DISTINCT统计看和业务预期是否一致。这样能及时发现自己写错了条件、写漏了关联而不是等到数据报表上线了才被人反馈“数字又不对了”。有一次做月度订单统计我查出来结果是9998单但业务系统里显示的是10000单。我第一反应是检查条件仔细看发现漏了一个退款订单的类型过滤。真是这种很不起眼的细节让统计差了那么两单。所以写完查询以后永远别急着说完成了多看一眼数据特征。7. 一个实战案例把一张订单表拆成一张业务报表前面的章节基本把DQL的语法边界和常见坑都过了一遍。最后我用一个融合了多类语法的实战案例把这些知识点串起来。假设我们现在有一张订单表orders和一张订单明细表order_items需要输出一份按月份统计的报表包含每个月的总订单数、总销售额、有退款记录的订单数、以及客单价。先看订单表结构ordersid、order_no、customer_id、amount、status、refund_flag、order_timeorder_itemsid、order_id、product_name、quantity、price第一步先把订单明细表里的销售额维度汇总到订单层级因为一个订单可能有多条明细SELECT order_id, SUM(quantity * price) AS item_total FROM order_items GROUP BY order_id;第二步和订单表做关联筛选有效订单并按月份聚合SELECT DATE_FORMAT(o.order_time, %Y-%m) AS month, COUNT(DISTINCT o.id) AS order_cnt, SUM(oi.item_total) AS sales_amount, COUNT(DISTINCT CASE WHEN o.refund_flag 1 THEN o.id END) AS refund_order_cnt, ROUND(SUM(oi.item_total) / COUNT(DISTINCT o.id), 2) AS avg_order_amount FROM orders o LEFT JOIN ( SELECT order_id, SUM(quantity * price) AS item_total FROM order_items GROUP BY order_id ) oi ON o.id oi.order_id WHERE o.status IN (paid, shipped, completed) GROUP BY DATE_FORMAT(o.order_time, %Y-%m) ORDER BY month DESC;这段SQL里用到了GROUP BY、COUNT(DISTINCT)、CASE WHEN、子查询、LEFT JOIN、ORDER BY几乎把前面讲的点都覆盖了。如果直接对order_items表GROUP BY订单ID再左连就能避免明细表行数展开导致的销售额翻倍问题。这也是前面反复提到的“先确认基数”的实践。实际跑这个语句的时候还要注意订单时间字段是否存在NULL值、退款标记字段的取值是否只有0和1。但凡这些数据质量有一点问题统计结果都可能出现偏差。做报表查询时花一点时间了解数据质量比急着写SQL更有价值。DQL这件事讲到头来就是注意力问题。语法本身并不复杂复杂的是在写之前想清楚业务想要什么写的时候理顺条件和关联写完之后再验证数据。这个过程多走几遍自然就顺手了。