聊到数据库操作DQLData Query Language数据查询语言是绝大多数开发者的第一站也是日常开发中最容易出细节问题的一块。很多人能写出成百上千行的SELECT语句却不一定会去琢磨一条查询在数据库内部是怎么被拆解执行的更不一定说得清DQL与DDL、DML之间的边界。这篇文章不打算堆高深理论而是从实际工作场景出发把DQL涉及的核心语法、执行顺序、常见写法、踩过的坑和优化思路完整梳理一遍。刚入门数据库的同学可以用它建立整体认识写过几年SQL的开发者也能拿来做一次查漏补缺。1. 先搞清楚DQL到底是什么1.1 从SQL家族认识DQLSQL这个大家庭里按职责可以分成几类DQL就是其中之一。DDL负责定义表、索引这些结构DML负责增删改数据DCL负责权限管理TCL负责事务控制。DQL的核心工作只有一个字查。在绝大多数关系型数据库里DQL的实现就是SELECT语句以及围绕它衍生出来的各种子句比如WHERE、GROUP BY、JOIN、ORDER BY之类。既然查询本身就是读数据不修改任何内容那为什么要单独把DQL拎出来说因为它太重要了。生产环境里你可以把某张表的写权限收回但很难把读权限也收掉所有报表、列表、统计都得靠查询。把查询语言独立成一个语言子集权限模型才能做到精细化。给一个账户只开SELECT权限它就永远不会改到数据这一点对数据安全至关重要。实际工作中DQL的使用频率远高于其他SQL子语言尤其是在后端接口开发、数据分析、运维排查这些场景。几乎每个接口背后都是一条或一组SELECT几乎每张报表背后都是复杂的WHERE和GROUP BY组合。所以DQL写得熟不熟直接影响开发效率也直接影响线上系统的稳定性。很多人以为写SQL只要结果对就行但结果对只完成了三分之一能不能快速返回、能不能稳定执行才是真正考验功力的地方。1.2 DQL能解决什么问题一个查询可以简单到只取一张表的两列也可以复杂到从五六个表里关联出上百个字段。它可以回答“昨天新增了多少订单”也可以回答“每个地区销量排名前三的商品分别是什么”。这就是DQL的能力边界只要数据在库里它就能按你给的规则取出来并且做过滤、排序、分组、连接和计算。如果你是后端开发DQL是从数据库取数的基本技能比如根据用户ID查资料、按条件翻页查列表、开接口给前端做数据展示。如果你是数据分析师或运营DQL是从数据仓库里取数做报表的手艺比在Excel里手动筛选高效得多也更容易保障数据口径一致。即使你现在主要写ORM用框架自带的查询构造器底层翻译出来的仍然是一段DQL搞清楚它只有好处没有坏处。2. 查询的核心骨架SELECT的底层逻辑2.1 最简单的SELECT FROM先确定数据源先看最基础的写法。假设有一张员工表employees里面存了emp_id、emp_name、dept_id、salary、hire_date这些字段。要查某个部门所有员工的编号、姓名和薪资代码长这样SELECT emp_id, emp_name, salary FROM employees WHERE dept_id 10;这段SQL在很多人眼里是“先SELECT再FROM”认为数据库会先挑列再定位表。但实际上数据库执行查询的物理顺序恰好相反它先定位FROM找到employees这个数据源然后应用WHERE把dept_id为10的行筛出来最后才执行SELECT投影只保留需要的列。为什么会这样因为SELECT阶段要处理的东西依赖FROM和WHERE的结果只有知道了表里有哪些行、哪些行满足条件才能确定最终输出什么字段。理解这个顺序最大的价值在于写SQL时会意识到过滤条件写在WHERE里是最直接的因为数据在最源头就被缩小了范围如果你把过滤逻辑写在SELECT表达式中比如用CASE WHEN在输出阶段才判断数据库仍然可能把大量无关行读出来性能就无从谈起。另外查询的列名最好显式写出来不要一上来就SELECT *。星号会让数据库多做一次字段解析也会把不需要的列和数据传给客户端。虽然有些数据库对SELECT *做了一些优化但线上查询仍然建议明确列出字段这既能让执行计划更稳定也方便别人看懂这条查询到底用到了哪些数据。2.2 WHERE过滤条件不是越多越好WHERE是DQL里最常用的子句很多人习惯把所有约束条件堆在一起但条件堆得越多不一定带来好的执行计划。一个典型问题是函数包裹列。比如要查2024年1月入职的员工新手可能会这么写SELECT emp_id, emp_name FROM employees WHERE DATE(hire_date) 2024-01-01;这个写法在业务上没错但DATE(hire_date)这个函数操作会让索引失效数据库只能全表扫描后对每一行做计算数据量大时会非常难受。更推荐的做法是把它改成范围条件SELECT emp_id, emp_name FROM employees WHERE hire_date 2024-01-01 AND hire_date 2024-02-01;原因很简单索引是按照原始值的顺序存储的当你对列做函数操作后索引里根本没有“函数结果”这个顺序数据库无法直接利用索引定位只能放弃它。这属于索引失效最常见的一种场景。还有一个新手经常踩的坑是NULL判断。表里如果某个字段允许为空想查“还没有分配部门”的员工应该用 WHERE dept_id IS NULL而不是 WHERE dept_id NULL。NULL不等于任何值也不是空字符串。在WHERE里NULL参与的等值比较永远返回UNKNOWN行会被当成不匹配而过滤掉。如果没意识到这点很可能查出来的结果永远是空而且不报错。WHERE条件的个数也不是越多越好。条件越多优化器需要评估的组合越复杂。当然实际业务里该加的过滤条件必须加但要尽量让每个条件保持简单尽量能用到索引。一个经验是能把条件写成等值或范围形式就不要写成函数能把多个OR改写成IN或者UNION ALL就尽量别让优化器因为OR而放弃索引。2.3 ORDER BY与LIMIT排序与分页的常见陷阱ORDER BY负责给结果集排序LIMIT负责截取前N条两者经常配合使用。比如查薪资最高的10个员工SELECT emp_id, emp_name, salary FROM employees ORDER BY salary DESC LIMIT 10;这段SQL看起来没问题但有个细节值得留意数据库并不是把所有结果全部排序后再取10条绝大多数数据库可以利用索引对salary做逆序扫描提前停止拿到前10条就走。这就是索引有序性的价值。没有索引时数据库才不得不做一次临时排序如果表很大这个排序可能直接变成性能瓶颈。ORDER BY还有个容易被忽略的坑就是NULL值的排序位置。不同数据库处理方式不一样有的默认把NULL放在最前有的放在最后。如果业务对空值有特殊要求建议在查询里明确写出 NULLS FIRST 或 NULLS LAST部分数据库支持不要依赖默认行为否则切换数据库版本后排序结果可能悄悄变化。LIMIT配合OFFSET做分页是最常见也最容易出大问题的写法。典型的深分页SQLSELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;这个查询真正的问题在于LIMIT的偏移量。数据库需要先扫描并丢弃前100000行然后才返回后面的20行。偏移量越大查询越慢。我见过线上系统翻到几千页后一个分页接口直接超时的案例。常见的优化思路有两种一种是键集分页不以页码为基准而是以上一页最后一条记录的排序字段作为过滤条件比如 WHERE create_time 上一页最大create_time ORDER BY create_time DESC LIMIT 20另一种是业务上不需要深翻页时直接限制最大页码比如最多只能访问前100页防止用户疯狂拖到最后。3. 聚合与分组把数据变成结论3.1 聚合函数和NULL的纠缠DQL不只是取数更重要的是把数据变成更有意义的结论。聚合函数就是这套计算能力的基础常用的有COUNT、SUM、AVG、MAX、MIN。它们都能对一列或多列做统计但有一个共同点绝大部分聚合函数会自动忽略NULL值。这会产生一个经典陷阱。统计“部门里有多少员工有奖金”时如果在奖金字段bonus上执行COUNT(bonus)得到的是非NULL的行数而不是员工总人数。如果本意是想数员工总数却被COUNT(bonus)的结果误导就会得出错误结论。正确做法是用COUNT(*)或COUNT(1)统计行数用COUNT(bonus)统计非空奖金数两者一对比反而能算出缺失数据的行数。SUM和AVG也有类似问题。AVG(salary)会先把NULL忽略再除以非空值的个数。如果你希望NULL按0参与计算需要通过COALESCE处理比如 AVG(COALESCE(salary, 0))。这种细节在报表开发里经常埋雷如果字段口径没有提前对齐很容易算出一版想当然的结果。3.2 GROUP BY与HAVING的完整执行顺序要按部门统计总人数和平均薪资需要用到分组。一个标准的分组查询长这样SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id;理解GROUP BY的关键是执行顺序。WHERE在分组之前执行它只能过滤分组前的基础行不能过滤分组后的聚合结果。如果你想筛掉“平均薪资低于5000”的部门条件必须写在HAVING里因为AVG(salary)是分组之后才算出来的。于是完整的执行顺序是FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT。特别要注意SELECT列表和GROUP BY的关系。在主流关系型数据库的严格模式下SELECT里出现的非聚合列必须同时出现在GROUP BY中。为什么因为GROUP BY把多行压缩成一行之后数据库不知道你要从哪一行里取那个非聚合列的值。这种约束能帮我们尽早发现逻辑混乱的查询。如果确实要保留多个行的细节就不应该分组而是用子查询或窗口函数。HAVING里也可以写复杂的聚合条件比如这样SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id HAVING COUNT(*) 5 AND AVG(salary) 8000;注意HAVING虽然长得像WHERE但它是在分组后对聚合结果做过滤所以它往往没有办法走索引代价比WHERE高。能在WHERE里过滤掉的条件绝对不要留到HAVING里。3.3 用CASE WHEN按条件统计除了聚合函数DQL里一个非常实用但常被低估的语法就是CASE WHEN。它本质上是条件表达式可以在SELECT里做判断也可以在GROUP BY和ORDER BY里使用。最常见的是做条件统计。比如要统计每个部门的薪资分布SELECT dept_id, SUM(CASE WHEN salary 8000 THEN 1 ELSE 0 END) AS low_sal_cnt, SUM(CASE WHEN salary BETWEEN 8000 AND 15000 THEN 1 ELSE 0 END) AS mid_sal_cnt, SUM(CASE WHEN salary 15000 THEN 1 ELSE 0 END) AS high_sal_cnt FROM employees GROUP BY dept_id;这个技巧的本质是对一行数据做多次分流再用SUM把满足条件的行计数。如果没有CASE WHEN可能要写多条查询再在应用层拼结果既慢又容易错。CASE WHEN的另一个用途是做行列转换的基础不少把长表变宽表的操作都靠它。写CASE WHEN时有个细节ELSE分支不要省略。数据库对未匹配的CASE会返回NULLNULL参与SUM时会被忽略结果看起来可能没变化但如果你在代码里假设它是0就可能被误导。显式加上ELSE 0能让自己明确知道未匹配行如何处理。4. 多表查询连接与子查询4.1 JOIN类型与驱动表真实业务数据几乎不会只放在一张表里员工表关联部门表、订单表关联商品表是常态。JOIN就是把这些表拼在一起的工具。最常用的连接类型有INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。它们的区别可以用一个例子说明A表是员工B表是部门INNER JOIN只返回两边都匹配上的员工和部门LEFT JOIN返回A表全部记录匹配不到部门的员工部门信息为NULL。写JOIN时最容易犯的错误是漏掉连接条件导致笛卡尔积。比如员工表100行、部门表10行不带ON条件的JOIN会返回1000行数据量瞬间放大。这通常不是业务想要的我排查线上慢查询时不止一次看到这种问题。解决办法很简单写JOIN时先明确ON条件如果几个表之间没有天然关联字段就要停下来确认连接逻辑是否合理。另一个值得关注的概念是驱动表。多表连接时数据库会选择一个表作为驱动表先从中取数再逐行去其他表匹配。一般来说优化器会倾向用小表作为驱动表因为外层循环的次数更少。但在某些复杂过滤条件下优化器选错驱动表也会造成性能问题。此时可以通过调整SQL结构或使用连接顺序提示来干预但不要一上来就人工指定最好先用执行计划确认问题到底在哪。实际开发中一个常见误区是试图用JOIN把很多张表全拼在一起。比如一次查询关联8张表每张表都加LEFT JOIN导致记录被重叠放大标出来的数字翻了好几倍。我的习惯是先分清粒度哪张表是一行一条业务事实哪些表是维度补充。先对事实表过滤再关联维度表能大大减少中间结果集。4.2 子查询与EXISTS的取舍当条件本身依赖另一个查询的结果时子查询是最直观的写法。比如查“工资高于平均工资的员工”SELECT emp_id, emp_name, salary FROM employees WHERE salary (SELECT AVG(salary) FROM employees);这个例子里的子查询是标量子查询只返回一个值数据库通常先执行子查询再执行外层查询。另一个常见写法是IN加子查询SELECT ... FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE ...);IN子查询大多数时候没问题但在子查询结果集很大、外层表也很大时效率可能不如EXISTS。因为在相关子查询中EXISTS往往能利用到外层行的索引只要找到一行匹配就可以短路返回。反过来如果外层表小、内层表大IN又可能更快。这里没有绝对答案最好的办法是实际跑一下执行计划看优化器选了什么。子查询还有一个容易踩的坑从子查询中只能取一个字段时别不小心取了多个字段。很多数据库不支持多列子查询直接用在IN后面SQL会直接报错。遇到这种情况要么把子查询改成临时表再JOIN要么拆成多个等值条件。在写复杂逻辑时我更推荐使用派生表也就是FROM子句里的子查询先把中间结果算好。比如先按部门聚合出平均工资再和员工表JOIN逻辑会清晰很多SELECT e.emp_id, e.emp_name, d.avg_sal FROM employees e JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d ON e.dept_id d.dept_id;这种写法看起来长一些但每层都有明确意图调试时能单独跑内层查询验证比一步到位的大JOIN好维护得多。4.3 窗口函数分组内的计算利器传统的分组查询会把多行压成一行但有些场景需要在保留所有行细节的同时在组内做排名、做累计这时候GROUP BY就压不了行。窗口函数就是为此设计的。比如给每个部门的员工按薪资排名SELECT emp_id, emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees;窗口函数的执行时机在GROUP BY之后、SELECT之前。PARTITION BY相当于给数据开了一个“组”ORDER BY决定组内排序ROW_NUMBER()给每行一个连续编号。如果要找每个部门薪资最高的前3名可以把上面的查询作为子查询再过滤rn 3。窗口函数家族很庞大除了ROW_NUMBER还有RANK、DENSE_RANK、SUM() OVER()、LAG/LEAD等。它们的共同价值是不用自关联也不用GROUP BY就能在行级别做组内计算写出的SQL既简洁又容易理解。现代数据库对窗口函数的支持已经很成熟遇到分组TopN、同比环比这类报表需求优先考虑它别再绕自连接。5. 性能优化与排查从能查到查得快5.1 一条查询的执行顺序与索引利用很多人写SQL只关注结果对不对不关心数据库怎么执行。但查询能不能跑得快恰恰取决于执行计划是否合理。这里再把标准SQL的完整执行顺序列出来FROM→JOIN→ON→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT虽然不同数据库会做不同程度的优化但逻辑顺序大致如此。理解这个顺序后你至少能回答两个问题为什么WHERE里不能直接用SELECT列表里的别名因为SELECT还没执行别名在WHERE阶段还不存在。为什么HAVING里的条件通常不容易走索引因为HAVING在分组之后才执行此时数据已经不是原始行的顺序。索引是DQL性能的核心。WHERE里的等值条件、JOIN里的连接字段、ORDER BY里的排序字段如果都能被索引覆盖查询速度会非常可观。比如根据dept_id查员工没有索引就只能扫全表有索引的话数据库会沿着索引树快速定位到目标行再通过主键回表取其他字段。如果查询要的字段恰好都在索引里还能形成覆盖索引连回表都省了。所以我每次优化慢查询第一步一定是看执行计划。看访问类型、看有没有命中索引、看估算扫描行数。这个习惯比背一百条优化建议都管用。真正理解了执行顺序再去看执行计划很多“为什么慢”的问题就一目了然了。5.2 常见拖慢查询的写法有些写法看起来顺手实际上坑很大。我把线上常见的高频问题列一下。第一条SELECT *。它会取回所有列包括超长的文本字段甚至二进制字段网络传输和内存开销都会被放大。只取需要的列是成本最低的优化。第二条对索引列做函数运算。前面提过的DATE(hire_date)就是典型例子它会直接让索引失效。解决办法是把函数运算改成范围条件让查询能直接利用索引的有序性。第三条隐式类型转换。比如字符串字段和整数比较数据库为了比较不得不把每行的值进行一次类型转换索引自然就失效了。写条件时最好让字段类型和右侧值的类型保持一致。第四条LIKE %关键词%。这种写法不以固定前缀开头B树索引无处发力。如果确实需要全文检索应该用专门的检索能力或倒排索引而不是在SQL里硬模糊匹配。第五条OR条件连接多个非索引列。这种情况容易让优化器放弃索引。把它改写成UNION ALL通常会更可控也更容易被优化器拆成多个独立查询。还有一条很容易被忽略分页LIMIT的偏移量过大。前面已经提到过这里再强调一次。如果业务上确实需要深翻页务必换成键集分页或者让用户只能访问有限深度否则页面越翻越慢直到把数据库拖垮。5.3 常见问题排查速查表下面这张表是我把平时遇到比较多的DQL问题整理出来的方便照方抓药。现象可能原因排查与解决思路查询越来越慢数据量增长、索引缺失、统计信息过期用执行计划看扫描行数必要时重建或新增索引加了条件还是全表扫描对列做了函数、隐式类型转换、OR乱用改成范围条件显式类型转换拆成UNION ALL分页翻到后面就超时LIMIT偏移量太大改用键集分页或限制最大页码COUNT(*)很慢大表全量统计且无可复用计数使用预估统计值或物化计数避免业务实时精确大表COUNTJOIN结果比预期多很多连接条件缺失或粒度不匹配检查JOIN ON条件先对事实表过滤再关联维度表这张表只覆盖了入门级问题但足够解决大部分日常慢查询。真要排查更深的问题还是要回到执行计划看统计信息看IO开销。注意在优化DQL之前先确认业务上是否真的需要这条查询。很多时候问题不是SQL写得差而是接口逻辑让SQL承担了太多不该由数据库做的事。最后再分享一个小技巧。我写DQL时从来不会一上来就写最终版本而是先用最小查询验证数据比如先查一条记录看看字段是否正确再逐步加过滤、加聚合、加连接。每加一层都跑一遍确保结果变化符合预期。这样看起来笨实际上能省下非常多调试时间。有一次排查一个报表数字翻倍的问题就是因为JOIN时没注意粒度部门表一行关联了员工多行数据被放大。后来把关联条件改成先聚合再连接问题立刻解决。DQL这个领域技巧很多但最重要的永远是理解数据、理解执行计划然后保持谨慎。 
长河君 · 2026/10/19 10:46:05 · 阅读 1.2k DQL查询优化：从执行顺序到索引利用的实战指南
聊到数据库操作DQLData Query Language数据查询语言是绝大多数开发者的第一站也是日常开发中最容易出细节问题的一块。很多人能写出成百上千行的SELECT语句却不一定会去琢磨一条查询在数据库内部是怎么被拆解执行的更不一定说得清DQL与DDL、DML之间的边界。这篇文章不打算堆高深理论而是从实际工作场景出发把DQL涉及的核心语法、执行顺序、常见写法、踩过的坑和优化思路完整梳理一遍。刚入门数据库的同学可以用它建立整体认识写过几年SQL的开发者也能拿来做一次查漏补缺。1. 先搞清楚DQL到底是什么1.1 从SQL家族认识DQLSQL这个大家庭里按职责可以分成几类DQL就是其中之一。DDL负责定义表、索引这些结构DML负责增删改数据DCL负责权限管理TCL负责事务控制。DQL的核心工作只有一个字查。在绝大多数关系型数据库里DQL的实现就是SELECT语句以及围绕它衍生出来的各种子句比如WHERE、GROUP BY、JOIN、ORDER BY之类。既然查询本身就是读数据不修改任何内容那为什么要单独把DQL拎出来说因为它太重要了。生产环境里你可以把某张表的写权限收回但很难把读权限也收掉所有报表、列表、统计都得靠查询。把查询语言独立成一个语言子集权限模型才能做到精细化。给一个账户只开SELECT权限它就永远不会改到数据这一点对数据安全至关重要。实际工作中DQL的使用频率远高于其他SQL子语言尤其是在后端接口开发、数据分析、运维排查这些场景。几乎每个接口背后都是一条或一组SELECT几乎每张报表背后都是复杂的WHERE和GROUP BY组合。所以DQL写得熟不熟直接影响开发效率也直接影响线上系统的稳定性。很多人以为写SQL只要结果对就行但结果对只完成了三分之一能不能快速返回、能不能稳定执行才是真正考验功力的地方。1.2 DQL能解决什么问题一个查询可以简单到只取一张表的两列也可以复杂到从五六个表里关联出上百个字段。它可以回答“昨天新增了多少订单”也可以回答“每个地区销量排名前三的商品分别是什么”。这就是DQL的能力边界只要数据在库里它就能按你给的规则取出来并且做过滤、排序、分组、连接和计算。如果你是后端开发DQL是从数据库取数的基本技能比如根据用户ID查资料、按条件翻页查列表、开接口给前端做数据展示。如果你是数据分析师或运营DQL是从数据仓库里取数做报表的手艺比在Excel里手动筛选高效得多也更容易保障数据口径一致。即使你现在主要写ORM用框架自带的查询构造器底层翻译出来的仍然是一段DQL搞清楚它只有好处没有坏处。2. 查询的核心骨架SELECT的底层逻辑2.1 最简单的SELECT FROM先确定数据源先看最基础的写法。假设有一张员工表employees里面存了emp_id、emp_name、dept_id、salary、hire_date这些字段。要查某个部门所有员工的编号、姓名和薪资代码长这样SELECT emp_id, emp_name, salary FROM employees WHERE dept_id 10;这段SQL在很多人眼里是“先SELECT再FROM”认为数据库会先挑列再定位表。但实际上数据库执行查询的物理顺序恰好相反它先定位FROM找到employees这个数据源然后应用WHERE把dept_id为10的行筛出来最后才执行SELECT投影只保留需要的列。为什么会这样因为SELECT阶段要处理的东西依赖FROM和WHERE的结果只有知道了表里有哪些行、哪些行满足条件才能确定最终输出什么字段。理解这个顺序最大的价值在于写SQL时会意识到过滤条件写在WHERE里是最直接的因为数据在最源头就被缩小了范围如果你把过滤逻辑写在SELECT表达式中比如用CASE WHEN在输出阶段才判断数据库仍然可能把大量无关行读出来性能就无从谈起。另外查询的列名最好显式写出来不要一上来就SELECT *。星号会让数据库多做一次字段解析也会把不需要的列和数据传给客户端。虽然有些数据库对SELECT *做了一些优化但线上查询仍然建议明确列出字段这既能让执行计划更稳定也方便别人看懂这条查询到底用到了哪些数据。2.2 WHERE过滤条件不是越多越好WHERE是DQL里最常用的子句很多人习惯把所有约束条件堆在一起但条件堆得越多不一定带来好的执行计划。一个典型问题是函数包裹列。比如要查2024年1月入职的员工新手可能会这么写SELECT emp_id, emp_name FROM employees WHERE DATE(hire_date) 2024-01-01;这个写法在业务上没错但DATE(hire_date)这个函数操作会让索引失效数据库只能全表扫描后对每一行做计算数据量大时会非常难受。更推荐的做法是把它改成范围条件SELECT emp_id, emp_name FROM employees WHERE hire_date 2024-01-01 AND hire_date 2024-02-01;原因很简单索引是按照原始值的顺序存储的当你对列做函数操作后索引里根本没有“函数结果”这个顺序数据库无法直接利用索引定位只能放弃它。这属于索引失效最常见的一种场景。还有一个新手经常踩的坑是NULL判断。表里如果某个字段允许为空想查“还没有分配部门”的员工应该用 WHERE dept_id IS NULL而不是 WHERE dept_id NULL。NULL不等于任何值也不是空字符串。在WHERE里NULL参与的等值比较永远返回UNKNOWN行会被当成不匹配而过滤掉。如果没意识到这点很可能查出来的结果永远是空而且不报错。WHERE条件的个数也不是越多越好。条件越多优化器需要评估的组合越复杂。当然实际业务里该加的过滤条件必须加但要尽量让每个条件保持简单尽量能用到索引。一个经验是能把条件写成等值或范围形式就不要写成函数能把多个OR改写成IN或者UNION ALL就尽量别让优化器因为OR而放弃索引。2.3 ORDER BY与LIMIT排序与分页的常见陷阱ORDER BY负责给结果集排序LIMIT负责截取前N条两者经常配合使用。比如查薪资最高的10个员工SELECT emp_id, emp_name, salary FROM employees ORDER BY salary DESC LIMIT 10;这段SQL看起来没问题但有个细节值得留意数据库并不是把所有结果全部排序后再取10条绝大多数数据库可以利用索引对salary做逆序扫描提前停止拿到前10条就走。这就是索引有序性的价值。没有索引时数据库才不得不做一次临时排序如果表很大这个排序可能直接变成性能瓶颈。ORDER BY还有个容易被忽略的坑就是NULL值的排序位置。不同数据库处理方式不一样有的默认把NULL放在最前有的放在最后。如果业务对空值有特殊要求建议在查询里明确写出 NULLS FIRST 或 NULLS LAST部分数据库支持不要依赖默认行为否则切换数据库版本后排序结果可能悄悄变化。LIMIT配合OFFSET做分页是最常见也最容易出大问题的写法。典型的深分页SQLSELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;这个查询真正的问题在于LIMIT的偏移量。数据库需要先扫描并丢弃前100000行然后才返回后面的20行。偏移量越大查询越慢。我见过线上系统翻到几千页后一个分页接口直接超时的案例。常见的优化思路有两种一种是键集分页不以页码为基准而是以上一页最后一条记录的排序字段作为过滤条件比如 WHERE create_time 上一页最大create_time ORDER BY create_time DESC LIMIT 20另一种是业务上不需要深翻页时直接限制最大页码比如最多只能访问前100页防止用户疯狂拖到最后。3. 聚合与分组把数据变成结论3.1 聚合函数和NULL的纠缠DQL不只是取数更重要的是把数据变成更有意义的结论。聚合函数就是这套计算能力的基础常用的有COUNT、SUM、AVG、MAX、MIN。它们都能对一列或多列做统计但有一个共同点绝大部分聚合函数会自动忽略NULL值。这会产生一个经典陷阱。统计“部门里有多少员工有奖金”时如果在奖金字段bonus上执行COUNT(bonus)得到的是非NULL的行数而不是员工总人数。如果本意是想数员工总数却被COUNT(bonus)的结果误导就会得出错误结论。正确做法是用COUNT(*)或COUNT(1)统计行数用COUNT(bonus)统计非空奖金数两者一对比反而能算出缺失数据的行数。SUM和AVG也有类似问题。AVG(salary)会先把NULL忽略再除以非空值的个数。如果你希望NULL按0参与计算需要通过COALESCE处理比如 AVG(COALESCE(salary, 0))。这种细节在报表开发里经常埋雷如果字段口径没有提前对齐很容易算出一版想当然的结果。3.2 GROUP BY与HAVING的完整执行顺序要按部门统计总人数和平均薪资需要用到分组。一个标准的分组查询长这样SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id;理解GROUP BY的关键是执行顺序。WHERE在分组之前执行它只能过滤分组前的基础行不能过滤分组后的聚合结果。如果你想筛掉“平均薪资低于5000”的部门条件必须写在HAVING里因为AVG(salary)是分组之后才算出来的。于是完整的执行顺序是FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT。特别要注意SELECT列表和GROUP BY的关系。在主流关系型数据库的严格模式下SELECT里出现的非聚合列必须同时出现在GROUP BY中。为什么因为GROUP BY把多行压缩成一行之后数据库不知道你要从哪一行里取那个非聚合列的值。这种约束能帮我们尽早发现逻辑混乱的查询。如果确实要保留多个行的细节就不应该分组而是用子查询或窗口函数。HAVING里也可以写复杂的聚合条件比如这样SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id HAVING COUNT(*) 5 AND AVG(salary) 8000;注意HAVING虽然长得像WHERE但它是在分组后对聚合结果做过滤所以它往往没有办法走索引代价比WHERE高。能在WHERE里过滤掉的条件绝对不要留到HAVING里。3.3 用CASE WHEN按条件统计除了聚合函数DQL里一个非常实用但常被低估的语法就是CASE WHEN。它本质上是条件表达式可以在SELECT里做判断也可以在GROUP BY和ORDER BY里使用。最常见的是做条件统计。比如要统计每个部门的薪资分布SELECT dept_id, SUM(CASE WHEN salary 8000 THEN 1 ELSE 0 END) AS low_sal_cnt, SUM(CASE WHEN salary BETWEEN 8000 AND 15000 THEN 1 ELSE 0 END) AS mid_sal_cnt, SUM(CASE WHEN salary 15000 THEN 1 ELSE 0 END) AS high_sal_cnt FROM employees GROUP BY dept_id;这个技巧的本质是对一行数据做多次分流再用SUM把满足条件的行计数。如果没有CASE WHEN可能要写多条查询再在应用层拼结果既慢又容易错。CASE WHEN的另一个用途是做行列转换的基础不少把长表变宽表的操作都靠它。写CASE WHEN时有个细节ELSE分支不要省略。数据库对未匹配的CASE会返回NULLNULL参与SUM时会被忽略结果看起来可能没变化但如果你在代码里假设它是0就可能被误导。显式加上ELSE 0能让自己明确知道未匹配行如何处理。4. 多表查询连接与子查询4.1 JOIN类型与驱动表真实业务数据几乎不会只放在一张表里员工表关联部门表、订单表关联商品表是常态。JOIN就是把这些表拼在一起的工具。最常用的连接类型有INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。它们的区别可以用一个例子说明A表是员工B表是部门INNER JOIN只返回两边都匹配上的员工和部门LEFT JOIN返回A表全部记录匹配不到部门的员工部门信息为NULL。写JOIN时最容易犯的错误是漏掉连接条件导致笛卡尔积。比如员工表100行、部门表10行不带ON条件的JOIN会返回1000行数据量瞬间放大。这通常不是业务想要的我排查线上慢查询时不止一次看到这种问题。解决办法很简单写JOIN时先明确ON条件如果几个表之间没有天然关联字段就要停下来确认连接逻辑是否合理。另一个值得关注的概念是驱动表。多表连接时数据库会选择一个表作为驱动表先从中取数再逐行去其他表匹配。一般来说优化器会倾向用小表作为驱动表因为外层循环的次数更少。但在某些复杂过滤条件下优化器选错驱动表也会造成性能问题。此时可以通过调整SQL结构或使用连接顺序提示来干预但不要一上来就人工指定最好先用执行计划确认问题到底在哪。实际开发中一个常见误区是试图用JOIN把很多张表全拼在一起。比如一次查询关联8张表每张表都加LEFT JOIN导致记录被重叠放大标出来的数字翻了好几倍。我的习惯是先分清粒度哪张表是一行一条业务事实哪些表是维度补充。先对事实表过滤再关联维度表能大大减少中间结果集。4.2 子查询与EXISTS的取舍当条件本身依赖另一个查询的结果时子查询是最直观的写法。比如查“工资高于平均工资的员工”SELECT emp_id, emp_name, salary FROM employees WHERE salary (SELECT AVG(salary) FROM employees);这个例子里的子查询是标量子查询只返回一个值数据库通常先执行子查询再执行外层查询。另一个常见写法是IN加子查询SELECT ... FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE ...);IN子查询大多数时候没问题但在子查询结果集很大、外层表也很大时效率可能不如EXISTS。因为在相关子查询中EXISTS往往能利用到外层行的索引只要找到一行匹配就可以短路返回。反过来如果外层表小、内层表大IN又可能更快。这里没有绝对答案最好的办法是实际跑一下执行计划看优化器选了什么。子查询还有一个容易踩的坑从子查询中只能取一个字段时别不小心取了多个字段。很多数据库不支持多列子查询直接用在IN后面SQL会直接报错。遇到这种情况要么把子查询改成临时表再JOIN要么拆成多个等值条件。在写复杂逻辑时我更推荐使用派生表也就是FROM子句里的子查询先把中间结果算好。比如先按部门聚合出平均工资再和员工表JOIN逻辑会清晰很多SELECT e.emp_id, e.emp_name, d.avg_sal FROM employees e JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d ON e.dept_id d.dept_id;这种写法看起来长一些但每层都有明确意图调试时能单独跑内层查询验证比一步到位的大JOIN好维护得多。4.3 窗口函数分组内的计算利器传统的分组查询会把多行压成一行但有些场景需要在保留所有行细节的同时在组内做排名、做累计这时候GROUP BY就压不了行。窗口函数就是为此设计的。比如给每个部门的员工按薪资排名SELECT emp_id, emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees;窗口函数的执行时机在GROUP BY之后、SELECT之前。PARTITION BY相当于给数据开了一个“组”ORDER BY决定组内排序ROW_NUMBER()给每行一个连续编号。如果要找每个部门薪资最高的前3名可以把上面的查询作为子查询再过滤rn 3。窗口函数家族很庞大除了ROW_NUMBER还有RANK、DENSE_RANK、SUM() OVER()、LAG/LEAD等。它们的共同价值是不用自关联也不用GROUP BY就能在行级别做组内计算写出的SQL既简洁又容易理解。现代数据库对窗口函数的支持已经很成熟遇到分组TopN、同比环比这类报表需求优先考虑它别再绕自连接。5. 性能优化与排查从能查到查得快5.1 一条查询的执行顺序与索引利用很多人写SQL只关注结果对不对不关心数据库怎么执行。但查询能不能跑得快恰恰取决于执行计划是否合理。这里再把标准SQL的完整执行顺序列出来FROM→JOIN→ON→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT虽然不同数据库会做不同程度的优化但逻辑顺序大致如此。理解这个顺序后你至少能回答两个问题为什么WHERE里不能直接用SELECT列表里的别名因为SELECT还没执行别名在WHERE阶段还不存在。为什么HAVING里的条件通常不容易走索引因为HAVING在分组之后才执行此时数据已经不是原始行的顺序。索引是DQL性能的核心。WHERE里的等值条件、JOIN里的连接字段、ORDER BY里的排序字段如果都能被索引覆盖查询速度会非常可观。比如根据dept_id查员工没有索引就只能扫全表有索引的话数据库会沿着索引树快速定位到目标行再通过主键回表取其他字段。如果查询要的字段恰好都在索引里还能形成覆盖索引连回表都省了。所以我每次优化慢查询第一步一定是看执行计划。看访问类型、看有没有命中索引、看估算扫描行数。这个习惯比背一百条优化建议都管用。真正理解了执行顺序再去看执行计划很多“为什么慢”的问题就一目了然了。5.2 常见拖慢查询的写法有些写法看起来顺手实际上坑很大。我把线上常见的高频问题列一下。第一条SELECT *。它会取回所有列包括超长的文本字段甚至二进制字段网络传输和内存开销都会被放大。只取需要的列是成本最低的优化。第二条对索引列做函数运算。前面提过的DATE(hire_date)就是典型例子它会直接让索引失效。解决办法是把函数运算改成范围条件让查询能直接利用索引的有序性。第三条隐式类型转换。比如字符串字段和整数比较数据库为了比较不得不把每行的值进行一次类型转换索引自然就失效了。写条件时最好让字段类型和右侧值的类型保持一致。第四条LIKE %关键词%。这种写法不以固定前缀开头B树索引无处发力。如果确实需要全文检索应该用专门的检索能力或倒排索引而不是在SQL里硬模糊匹配。第五条OR条件连接多个非索引列。这种情况容易让优化器放弃索引。把它改写成UNION ALL通常会更可控也更容易被优化器拆成多个独立查询。还有一条很容易被忽略分页LIMIT的偏移量过大。前面已经提到过这里再强调一次。如果业务上确实需要深翻页务必换成键集分页或者让用户只能访问有限深度否则页面越翻越慢直到把数据库拖垮。5.3 常见问题排查速查表下面这张表是我把平时遇到比较多的DQL问题整理出来的方便照方抓药。现象可能原因排查与解决思路查询越来越慢数据量增长、索引缺失、统计信息过期用执行计划看扫描行数必要时重建或新增索引加了条件还是全表扫描对列做了函数、隐式类型转换、OR乱用改成范围条件显式类型转换拆成UNION ALL分页翻到后面就超时LIMIT偏移量太大改用键集分页或限制最大页码COUNT(*)很慢大表全量统计且无可复用计数使用预估统计值或物化计数避免业务实时精确大表COUNTJOIN结果比预期多很多连接条件缺失或粒度不匹配检查JOIN ON条件先对事实表过滤再关联维度表这张表只覆盖了入门级问题但足够解决大部分日常慢查询。真要排查更深的问题还是要回到执行计划看统计信息看IO开销。注意在优化DQL之前先确认业务上是否真的需要这条查询。很多时候问题不是SQL写得差而是接口逻辑让SQL承担了太多不该由数据库做的事。最后再分享一个小技巧。我写DQL时从来不会一上来就写最终版本而是先用最小查询验证数据比如先查一条记录看看字段是否正确再逐步加过滤、加聚合、加连接。每加一层都跑一遍确保结果变化符合预期。这样看起来笨实际上能省下非常多调试时间。有一次排查一个报表数字翻倍的问题就是因为JOIN时没注意粒度部门表一行关联了员工多行数据被放大。后来把关联条件改成先聚合再连接问题立刻解决。DQL这个领域技巧很多但最重要的永远是理解数据、理解执行计划然后保持谨慎。