SQL DQL查询全解：从执行顺序到索引优化的实践指南

1. DQL的核心价值与执行顺序1.1 为什么数据库入门必须死磕DQL如果你手头刚好有一本数据库教材翻到目录页多半会看到“数据查询语言DQL”这个章节排在靠前的位置。很多初学者容易把它当成SQL众多语法分类里普通的一支觉得背一背SELECT语法、记几个关键字就算过关了。但以我带过项目的经验来看这种认知低估了DQL的重量级。DQL是SQL四大分类里唯一一个专门负责“读数据”的部分。整个数据库系统花费大量精力去做的索引设计、缓存优化、存储引擎选择最终服务的核心目标就是让DQL跑得更快、查得更准。你可以不写INSERT、不碰UPDATE、暂时用不到事务控制但只要有数据要展示、有报表要统计、有接口要返回结果就绝对绕不开SELECT。说得更直白一点你日常在业务系统里写的所有查询逻辑95%以上都属于DQL的范畴。这篇笔记我想围绕DQL做一次系统性的拆解。我不会按教科书那样先从语法定义讲起而是从执行顺序、过滤逻辑、聚合分组、关联查询、子查询这几个核心大块入手把每个部分背后的设计思路和实际开发里的坑位都摆出来。适合刚学完SQL基础想巩固一遍的初学者也适合写过一些查询但总觉得功力不够扎实的开发新人。1.2 SQL书写顺序与执行顺序的差异刚开始接触DQL的同学最容易懵的一个点是SQL语句的书写顺序和实际执行顺序并不一致。你写的SELECT可能列在最前面但数据库执行的时候它反而是比较靠后的一步。一条完整的查询语句典型的书写顺序是这样的SELECT DISTINCT 列名或表达式 FROM 表名 JOIN 另一张表 ON 连接条件 WHERE 过滤条件 GROUP BY 分组字段 HAVING 对分组结果过滤 ORDER BY 排序字段 LIMIT 返回行数但数据库引擎内部的执行逻辑一个经典的心智模型是这样排列的先确定数据来源从哪些表取数据FROM和JOIN再按条件逐行过滤把不需要的行扔掉WHERE然后对剩余行做分组聚合GROUP BY分组之后用HAVING再筛掉不合格的组接着才计算SELECT后面写的表达式和列然后做去重DISTINCT再排序ORDER BY最后才做分页截断LIMIT如果你把这个执行顺序记熟了后面很多语法限制就不用死背了。举个例子为什么WHERE里不能直接用聚合函数做条件因为WHERE执行的时候GROUP BY还没跑数据还是逐行的状态聚合结果压根不存在。为什么HAVING可以筛选分组字段因为它排在GROUP BY之后执行分组结果已经出来了。很多人分不清WHERE和HAVING的区别其实核心就是执行位置不同。这个顺序的心智模型向后延伸到SQL调优也用得上。比如你想优化一条慢查询脑子里第一个问题就应该是这语句在执行顺序的哪一部损耗最大是FROM阶段没走索引还是WHERE阶段扫描量过大或者排序阶段内存不够用。定位到具体阶段再动手比瞎试快得多。2. 单表查询的必备细节与实操技巧2.1 SELECT的字段选择与别名使用单表查询是DQL最基本的形态。语句本身不复杂但细节决定了代码质量和后续维护成本。先说说字段选择。我见过不少新手习惯写SELECT *理由是“简单省事”。在自己练手的Demo里这么搞问题不大但在真实的业务代码里这是一个值得警惕的坏习惯。核心原因有两个。第一SELECT *会把表里所有的列都查出来如果这张表有十几二十个字段大部分可能根本用不上。多查出来的数据会白白占用网络传输和内存开销在单表数据量几百万行时尤其明显。第二代码的健壮性差。表结构一变比如某天DBA给表加了一个大字段SELECT *的结果集就会跟着变接口返回的JSON结构可能就悄悄变了排查起来费老大劲。所以除非是临时在命令行里看一眼数据正式代码里我都建议把需要的字段一个一个列出来。再说别名。给表名和列名取别名不只是为了少打几个字符。如果你的SQL涉及子查询或者多表关联结构清晰与否全靠别名撑场面。实际开发中我会给每个表都设置简短有意义的别名例如users u、orders o然后在所有字段引用的地方都带上这个别名前缀。这样做最大的好处是SQL的可读性和可维护性显著提升——哪怕一个月后回来看这条语句也能一眼看出每个字段来自哪张表。-- 一个带别名的典型查询 SELECT u.name AS 用户名, o.order_no AS 订单号, o.total_amount AS 订单金额 FROM users u JOIN orders o ON u.id o.user_id WHERE u.status 1 ORDER BY o.created_at DESC LIMIT 20;AS关键字写不写都可以。我习惯写上纯粹是为了让语义更清楚尤其在表达式运算的地方。2.2 WHERE过滤条件的几个经典陷阱WHERE子句是查询条件的主战场这里集中了最多的实际坑位。有些问题不踩一次很难真正记住。判空别用等号。WHERE name NULL永远查不到数据。NULL在SQL里代表“未知值”它不等于NULL本身任何与NULL做等值比较的表达式结果都是UNKNOWN而UNKNOWN会被当作不满足条件处理。正确写法是WHERE name IS NULL或者查非空用WHERE name IS NOT NULL。这个错误我见过不止一次出现在工作了几年的人写的代码里属于极为隐蔽又极为低级的失误。**字符串与数值的隐式转换。**如果某张表的字段类型是VARCHAR你却拿一个整数去比较比如WHERE phone 13800138000数据库会尝试做隐式类型转换。问题在于一旦字段被函数或者隐式转换包裹索引通常会失效全表扫描就来了。数据量小的时候感觉不到等表到了几百万行一条原本能秒回的查询可能一卡就是好多秒。我的建议是写条件之前先搞清楚字段的真实类型无论何时都不要依赖数据库的隐式转换。**多个条件用AND与OR连接时注意括号。**SQL里AND优先级高于OR但人的阅读习惯往往不这么认为。如果条件里同时出现AND和OR最好显式加括号否则很容易出现明明你想表达“A且B或C”实际SQL却被理解成“A且B或C”的情况。这个错误一旦在数据量大的时候发生查询结果对不上排查时相当头疼。再来一个优化向的小经验过滤条件尽量写在JOIN之前还是之后严格来说对于INNER JOIN写在WHERE还是JOIN的ON里结果一样。但性能上如果能在JOIN之前先把单表数据量降下来后续参与连接的数据量就会更小整体消耗更可控。所以我会优先把过滤条件直接放在WHERE里让数据尽早缩小范围。2.3 ORDER BY与LIMIT的搭配心得排序和分页是绝大多数业务查询的收尾动作但这里也有不少门道。ORDER BY默认是升序ASC降序要显式写DESC。排序字段如果有多个从左到右依次生效先按第一字段排遇到相同的再按第二字段排。这一块比较直白新手容易忽略的是汉字排序问题。在MySQL里汉字排序跟字符集校对规则有关默认情况下是按编码排序不是按拼音更不是按笔画。如果你要支持中文拼音排序写法是ORDER BY CONVERT(name USING gbk)但这种写法大概率会让索引失效数据量大的时候要慎重最好在设计表时额外加一个拼音排序字段。LIMIT分页有个经典性能问题页码越深查询越慢。原因是LIMIT 200000, 20这种写法实际执行时数据库仍然要先把前面20万行都找出来然后丢弃再返回后面的20行。页面翻到几百上千页时这个开销会越来越大。常见优化手段是“延迟关联”先通过索引定位到需要的主键ID范围再拿这批ID去关联取完整数据。举个例子-- 深分页的常见写法数据量大时性能不佳 SELECT * FROM orders ORDER BY id LIMIT 200000, 20; -- 延迟关联优化写法 SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 200000, 20) t ON o.id t.id;子查询里先只取主键Order By和Limit都在那条轻量的查询里完成再跟原表做一次关联。实测下来在百万级数据量的场景里这种写法往往能快上好几倍。DISTINCT去重也经常跟ORDER BY搭配出现。注意DISTINCT作用的范围是SELECT子句中所有列的组合而不是单列。SELECT DISTINCT name, age和SELECT DISTINCT name含义完全不同。如果只想去重一列但还想查其他列那DISTINCT根本满足不了你的需求得换思路。3. 分组统计与聚合函数的运用3.1 聚合函数的使用边界DQL里最出彩的一部分能力是用一组聚合函数对大量明细数据做统计汇总。常用的聚合函数无非COUNT、SUM、AVG、MAX、MIN这几个但用法细节值得说道。COUNT可能是被误解最多的一个。COUNT(*)统计的是结果集行数包括NULL行COUNT(具体字段)统计的是该字段不为NULL的行数。我见过有人统计总数时写了COUNT(remark)结果发现有几行的remark是NULL总数就少了业务上死活对不上账。所以统计总行数就老老实实写COUNT(*)别搞骚操作。另外COUNT在MyISAM和InnoDB引擎里的实现逻辑也不同InnoDB因为要支持事务没有单独存储行数所以COUNT在大表上是需要实际扫描的别指望它像计数器的表一样瞬间出结果。SUM和AVG遇见NULL值时的表现也值得留意。SUM(字段)会自动忽略NULL所以如果整列全是NULLSUM的结果不是0而是NULL这个返回类型在Java等语言里接收时容易产生空指针判断失误。AVG同理分母是“非NULL行数”而不是“总行数”。如果你希望AVG把NULL当成0参与计算得自己用IFNULL或COALESCE做预处理。聚合函数里不能混用普通字段是不少新手的知识盲区。比如你写了SELECT name, MAX(salary) FROM employees逻辑上你期望的是“拿到工资最高的那个人的名字”但SQL标准并不保证这种结果。因为分组维度不明确数据库到底取哪个name行为是不确定的。如果你想要的是每一组里的最高工资对应的人就得用到窗口函数或者子查询。MySQL的默认模式ONLY_FULL_GROUP_BY未开启下甚至不报错直接给你一个随机值这种数据拿出来就是事故。3.2 GROUP BY分组逻辑与HAVING过滤GROUP BY的本质是把“多行合并成一组”然后对每一组做聚合运算。这个逻辑里最核心的认知是**分组之后的查询结果里SELECT后面能出现的东西只有两类——分组字段本身以及由分组字段聚合计算得到的表达式。**其它原始列的数据在分组后已经失去了逐行的意义因为它们可能来自组内不同的行。举一个实际的例子。假设有一张销售流水表里面有城市、渠道、销售额。你现在想统计每个渠道的总销售额SELECT 渠道, SUM(销售额) AS 总销售额 FROM 销售流水 GROUP BY 渠道 ORDER BY 总销售额 DESC;执行顺序是先把所有行按渠道分类然后对每个渠道桶里的销售额做求和最后按总销售额降序排列。搞清楚这个流程后你就明白了为什么SELECT里写非分组字段会有问题。HAVING和WHERE的区别前面已经提过执行时机不同。实际使用中我的判断标准很简单**凡是能用WHERE过滤掉的绝不放进HAVING。**比如你想查“销售额超过1000元的渠道”如果“销售额超过1000元”是对单行数据的过滤就先在WHERE里干掉如果是对分组后的汇总结果做过滤比如“总销售额大于10万的渠道”就必须用HAVING了。把效率不高的HAVING用到极致也是一种典型的性能杀手。GROUP BY还有一个常见搭配是配合WITH ROLLUP做小计汇总MySQL和部分数据库支持这个语法。它会额外生成一行总计结果在报表场景里很方便。但要注意用了WITH ROLLUP之后返回结果里会多出NULL标志的总计行代码里要单独判断处理不然容易跟正常数据混淆。4. 多表关联查询的进阶应用4.1 内连接与外连接的选型单表查询解决的是“一张表里能回答的问题”。但业务建模为了减少数据冗余会把数据打散到多张表里用外键去关联。所以多表关联查询就成了DQL中绕不开的核心技能。最常见的关联类型有INNER JOIN、LEFT JOIN、RIGHT JOIN。从集合论的角度来理解会更清楚INNER JOIN取两张表的交集LEFT JOIN保留左表的全部行右表能匹配上就带上数据匹配不上就用NULL填充RIGHT JOIN则方向相反。不过我自己的习惯是能不用RIGHT JOIN就不用因为它可以把逻辑对称性打破阅读代码的人还得在心里做一次左右镜像不如统一用LEFT JOIN实现同样效果。写关联查询的时候有一个思路很关键首先确认你要以哪张表为“主表”。这是JOIN类型选择的锚点。比如你在做一个订单列表接口需求是“展示所有订单并带出每个订单对应的用户姓名”那你的主表显然应该是订单表即FROM orders然后LEFT JOIN users ON orders.user_id users.id。如果需求反过来是“展示所有用户并带出他们近期的订单数”主表就变成用户表用LEFT JOIN把订单表带进来。连接条件本身也讲究。ON后面写的关联字段两边的数据类型要保持一致避免隐式转换。并且关联字段上要有索引。这一点极其重要关联查询性能差十有八九是关联字段没有索引导致每一行都要去全表扫描匹配一次。4.2 子查询的应用与改写思路子查询就是嵌套在SELECT、FROM或WHERE里的一整条SQL。按照出现位置的不同可以大体分三类。WHERE子句中的子查询典型用法是配合IN、EXISTS做条件过滤。比如“找出近期下过订单的用户”先查出订单表里去重后的user_id再用IN去过滤用户表。这种写法直观易懂但要注意IN后面的结果集如果很大性能就会下降。EXISTS是另外一种写法逻辑上更接近“存在性检测”优化器处理起来往往更灵活尤其在子查询数据量比较大的场景下实测EXISTS往往比IN表现更好。-- 子查询 IN 的写法 SELECT name FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE created_at 2024-01-01); -- 子查询 EXISTS 的写法 SELECT name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.created_at 2024-01-01 );FROM子句中的子查询可以把它理解为“先查出一个临时的结果集再把它当成一张新表来查”。这种写法在处理多层聚合统计时非常好用。比如“统计每个渠道的总销售额再找出其中超过平均值的渠道”——你需要先算平均数再做比较天然适合先搞一个临时表。SELECT 渠道, 总销售额 FROM ( SELECT 渠道, SUM(销售额) AS 总销售额 FROM 销售流水 GROUP BY 渠道 ) t WHERE 总销售额 (SELECT AVG(总销售额) FROM ( SELECT 渠道, SUM(销售额) AS 总销售额 FROM 销售流水 GROUP BY 渠道 ) t2);这种SQL写多了会有个问题可读性急速下降嵌套再嵌套会让人吐血。所以现在很多新项目里遇到复杂逻辑我更推荐把子查询改写成公共表达式CTE用WITH语句分成几步走。WITH有时候也被称为“临时视图”它的最大优势是能把一坨复杂的嵌套逻辑拆成一段段有名字的步骤每一段单独阅读都很清楚调试起来也方便。WITH 渠道汇总 AS ( SELECT 渠道, SUM(销售额) AS 总销售额 FROM 销售流水 GROUP BY 渠道 ) SELECT 渠道, 总销售额 FROM 渠道汇总 WHERE 总销售额 (SELECT AVG(总销售额) FROM 渠道汇总);这一段SQL读起来就顺多了一眼能看明白意图。CTE在可读性和维护性上的优势在复杂统计场景里几乎碾压嵌套子查询。4.3 多表查询中的NULL与重复数据关联查询产生NULL值是非常正常的现象。LEFT JOIN时右表匹配不上的字段就是NULL。所以对关联查询的结果做过滤时同样要遵守之前说的NULL判断规则别用 NULL。另外在连接条件里如果右表的关联字段本身可能有NULL那这些行用ON关联时是匹配不上的INNER JOIN不会包含它们LEFT JOIN则会以NULL形态带出来。这个细节在做数据对齐时容易对不上数。重复数据也是多表查询的老朋友。当左表一行数据在右表对应多行时LEFT JOIN会把左表那行复制多份这就是行数膨胀。举一个经典场景一张订单表一张订单明细表一个订单包含多个商品。如果你SELECT * FROM orders o LEFT JOIN order_items i ON o.id i.order_id那一个订单有几件商品结果集就会有几行。这在统计“订单总数”时会闹笑话——COUNT(o.id)统计出来的不是订单数而是订单明细行数。遇到这种场景要么明确业务需求之后在统计处做去重COUNT(DISTINCT o.id)要么把维度理清楚再关联。多表关联时把表前缀写全也是一个值得坚持的习惯。如果表里有重名的字段比如用户表和订单表都有created_at不写前缀查询结果和后端取值时都会出问题。如果在SELECT里只写了字段名没写前缀数据库会报“字段不明确”。所以从一开始就养成带表别名前缀的写法不然后续排查问题会非常难受。5. 查询性能问题的初步诊断5.1 EXPLAIN执行计划基础解读DQL写得对不对有时候需要靠性能来验证。真正到了生产环境一条慢查询可能拖垮整个数据库。所以我觉得这一节值得单独记录一下尤其对初学者来说早一点接触执行计划后面写SQL的习惯会好很多。MySQL等主流关系型数据库都提供了EXPLAIN命令用法很简单在SELECT语句前加上EXPLAIN关键字。结果里会输出一张表里面关键字段有几个是必看的。type字段反映了访问类型从好到坏大致是system、const、eq_ref、ref、range、index、ALL。ALL意味着全表扫描通常是性能瓶颈的红色警报。key字段显示实际使用的索引如果是NULL说明没用上索引。rows字段是个估算值表示预计要扫描多少行。Extra字段里如果出现Using filesort或者Using temporary说明排序或分组操作使用了临时表和文件排序通常意味着大数据量下的性能问题。我见过不少开发者的操作习惯是SQL能跑出结果就行压根不看执行计划。但一个能跑出结果的SQL和能稳定扛住生产流量的SQL中间隔着一整条执行计划的鸿沟。养成用EXPLAIN检查关键查询的习惯对提升SQL功底非常有帮助。5.2 慢查询的常见原因与规避方式结合我自己的排查经验慢查询最常见的几个原因无非下面几类。类型不匹配导致索引失效。前面提过的隐式转换问题这里再强调一次。字段是字符串类型你传了整数索引就废了字段是日期类型你传了字符串同样可能废。排查这类问题EXPLAIN一看key为NULL基本就能锁定。函数包裹索引字段。WHERE DATE(created_at) 2024-06-01这种写法看起来很自然但DATE()函数会把created_at的索引破坏掉。更优的写法是用范围条件WHERE created_at 2024-06-01 AND created_at 2024-06-02。这既保留了索引的使用语义也清晰。LIKE模糊查询的前置通配符。LIKE %keyword%因为通配符在开头走不了前缀索引。如果业务真的需要全文搜索不要硬用LIKE老老实实引入全文检索或者走专门的搜索引擎别在SQL里死磕。OR条件导致索引失效。多条件过滤时OR连接的条件可能让优化器放弃索引。解决思路是把OR改写成UNION把两个查询结果合并。尤其替换成UNION ALL时性能改善往往很明显。事务中执行大查询。如果在一个长事务里跑了一条大查询哪怕SQL本身不慢也可能因为持锁时间过长引起连锁性能问题。所以查询类操作尽量在非事务环境下执行或者缩短事务生命周期也是在项目里见过的教训。5.3 分页、排序与聚合的调优案例再分享一个实战中的聚合调优思路。记得某次在项目里处理一张千万级的订单流水表需要按天统计订单数并排序。最开始的SQL写法是直接对全表做WHERE过滤再加GROUP BY结果每次查询都耗时数秒接口直接超时。后来优化的思路是分三步走。第一业务上能预判的时间范围尽量收窄减少扫描行数第二把按天分组结果预先通过定时任务算好存进一张汇总表查询直接读汇总表第三查询条件里对索引字段做范围过滤让数据库多走索引少做全表扫描。改完之后耗时从秒级直接降到了几十毫秒。这个案例想说明的其实是一个朴素的道理DQL查询一旦到了生产环境就不能只停留在“查得对”的层面必须考虑“查得快”。而查得快的基础是对数据量的敬畏以及对索引结构的基本理解。索引本质上就是一本“数据的目录”写查询的时候多想想怎么让数据库更快地翻到你要的那几页。6. DQL学习的实践建议与常见错误清单6.1 用一套练习数据覆盖全部核心语法DQL语法本身不算多但如果不实际操作看再多教程都是纸上谈兵。我给新手朋友的建议是自己动手建一套练习用的小型数据库比如模拟一个电商场景包含用户表、商品表、订单表、订单明细表。表不用太复杂每张表几十行数据就够但设计要有意识地覆盖常用的字段类型整数、字符串、日期时间、可空字段、外键关系。然后拿着这套数据把下列场景一个一个练过去查全部字段、查指定字段、条件过滤、模糊匹配、范围查询、空值判断、排序、分页、去重、聚合函数、单字段分组、多字段分组、分组后过滤、INNER JOIN、LEFT JOIN、RIGHT JOIN、WHERE子查询、FROM子查询、CTE。每一个语法点都用同一个业务场景去套比如“统计每个商品类别的总销售额”“找出购买次数超过3次的用户”“查询最近一周的订单明细并且带上商品名称”。二十来个练习做完DQL的主要语感就算建立起来了。掌握代码能力的核心从来不是看懂而是练熟。你在自己电脑上踩过的坑远比别人在文章里提前告诉你的坑记得牢固。6.2 新手最容易踩的十个错误速查表我把实际工作和带人过程中见过的典型错误汇总成一个速查表建议保存下来写SQL之前扫一眼。错误类型错误示例正确写法NULL判断用等号WHERE name NULLWHERE name IS NULL统计行数用COUNT(可空字段)COUNT(remark)COUNT(*)WHERE里用聚合函数WHERE COUNT(*) 5改用HAVINGSELECT混入非分组字段SELECT name, MAX(salary)明确分组维度或改子查询SELECT *SELECT * FROM users显式列出所需字段深分页直接LIMIT偏移LIMIT 1000000, 20延迟关联或游标分页关联查询不带表前缀SELECT id FROM users u JOIN orders oSELECT u.idLIKE前置通配符LIKE %keyword%能走索引的写法或用全文检索字符串字段传数值WHERE phone 13800138000显式写成字符串OR连接条件WHERE a 1 OR b 2改写UNION或用索引合并策略6.3 一条完整DQL语句的拆解示例最后写一个综合性的例子把前面提到的所有语法点串起来。场景是统计某电商平台上“2024年第一季度每个城市中消费总金额排名前3的会员用户”。假设有会员表membersid、用户名、城市、订单表ordersid、会员id、下单时间、订单金额。第一步先算每个会员在季度内的总消费WITH 会员消费 AS ( SELECT m.id AS 会员ID, m.用户名 AS 用户名, m.城市 AS 城市, SUM(o.订单金额) AS 总消费 FROM members m JOIN orders o ON m.id o.会员ID WHERE o.下单时间 2024-01-01 AND o.下单时间 2024-04-01 GROUP BY m.id, m.用户名, m.城市 )第二步在每座城市内部排序并取前3名现代数据库普遍支持窗口函数写法比如ROW_NUMBERSELECT 城市, 用户名, 总消费 FROM ( SELECT 城市, 用户名, 总消费, ROW_NUMBER() OVER (PARTITION BY 城市 ORDER BY 总消费 DESC) AS 排名 FROM 会员消费 ) t WHERE 排名 3 ORDER BY 城市, 总消费 DESC;整个查询用了CTE、JOIN、WHERE范围过滤、聚合函数、GROUP BY、窗口函数、子查询和排序。一套流程走下来你会发现自己对DQL的整体驾驭感已经和只会写简单SELECT时完全不同了。我个人在实际操作中的一个体会是DQL的学习曲线其实并不陡峭卡住大多数人的根本不是语法而是思维习惯——有没有想过数据是怎么存储的有没有想过索引是怎么加速的有没有想过一条SQL在数据库内部经历了什么。一旦你开始带着这几个问题去写每条查询你的SQL水平会以超出预期的速度往上涨。最后再分享一个小技巧遇到任何拿不准的写法不要只在教学库上验证尝试在EXPLAIN里看一眼执行计划它会把很多你以为懂了、其实没懂的事情照出原形。