DQL查询优化：从执行顺序到JOIN分页，JavaWeb必会SQL内功

1. DQL在JavaWeb里的真实位置先说实话很多同学学JavaWeb的时候视图、控制器、前后端交互这些东西一上来就把人绕晕了。但如果你去问任何一个干了三五年后端的人他大概率会告诉你写业务代码最耗时、最容易出问题的不是Java那部分而是数据库查询那部分。DQL就是Data Query Language数据查询语言。MySQL的增删改查四种操作里查是最频繁的。用户登录要查商品列表要查订单详情要查报表聚合还是查。页面上的每一个数字、每一行记录背后都是一条或几条DQL在支撑。所谓“JavaWeb从入门到进阶”本质上就是两条线一条线是Java和前端框架这些表层功夫另一条线就是SQL尤其是DQL这条内功。内功不行外表再花哨也没用。这篇内容适合正在学JavaWeb的在校生也适合刚入职不久、写SQL总靠百度拼凑的初级开发。我会把DQL从最简单的单表查询讲到多表关联、子查询、分组聚合这一层。每个环节都配真实场景最后再分享几个实际工作中踩过的SQL坑。很多人觉得DQL就是“SELECT * FROM 表 WHERE 条件”没别的了。等到了公司接手一个几十万行数据、十来张关联表的业务模块才发现完全不是那么回事。SQL写得好不好直接决定接口响应是200毫秒还是3秒决定你写的报表能不能跑完决定你在review的时候是被夸还是被怼。2. 别急着写SELECT先搞懂执行逻辑学DQL最忌讳的一件事就是上来就背关键字语法。SELECT、FROM、WHERE、GROUP BY、ORDER BY、LIMIT这些都知道但一旦组合起来顺序就乱套了。2.1 书写顺序和执行顺序是两码事很多新手会背“SELECT FROM WHERE GROUP BY ORDER BY”这个书写顺序但数据库真正干活的时候顺序完全不一样先确定数据从哪张表来然后过滤行再分组再过滤组再计算最后排序和分页。底层执行顺序是这样的FROM —— 确定数据源加载表WHERE —— 对FROM的结果做行级过滤GROUP BY —— 按照指定列分组HAVING —— 对分组后的结果做过滤SELECT —— 投影需要的列计算表达式ORDER BY —— 排序LIMIT —— 分页这个顺序知道以后很多困惑就解开了。比如为什么WHERE后面不能使用SELECT里定义的别名因为WHERE执行的时候SELECT都还没执行别名自然不存在。再比如为什么HAVING能用聚合函数而WHERE不能用因为WHERE是先过滤行而HAVING是分组之后过滤组聚合函数必须等到分组后才能计算。我当年第一次在面试里被问到“WHERE和HAVING的区别”只答了一句“WHERE过滤行HAVING过滤组”考官点了点头。但如果只答到这个程度还是不够的最好把执行顺序也说出来。2.2 用一组数据把逻辑跑通先建一张学生表来演示。不用太复杂但对于理解整条主线的帮助很大。CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), class_id INT, score DECIMAL(5,2), create_time DATETIME DEFAULT CURRENT_TIMESTAMP );随便造几条测试数据INSERT INTO student (name, class_id, score) VALUES (张三, 1, 88.5), (李四, 1, 76.0), (王五, 2, 92.0), (赵六, 2, 65.5), (孙七, 3, 81.0), (周八, 3, 59.5);现在问一个问题每个班级的平均分是多少并且只要平均分大于70的班级SELECT class_id, AVG(score) AS avg_score FROM student GROUP BY class_id HAVING AVG(score) 70;执行逻辑先加载student表然后没有WHERE所以全部行保留接着按class_id分组三组数据然后对每组计算AVG(score)再把平均值小于等于70的组干掉了最后投影出class_id和avg_score两列。这个例子看起来很简单但它把GROUP BY和HAVING各自的位置说得清清楚楚。如果项目里写错了位置比如在WHERE里写AVG(score)立刻就是语法错误。3. 基础查询部分最常用的几种操作3.1 SELECT的投影操作写SELECT的时候第一条原则是能用具体列名就不要用星号。星号虽然省事但在生产环境里问题很多。一个是网络传输量表里有40个字段你只需要3个SELECT *一下子把40个都传回来了一张表数据量上来以后这个差异会被放大。另一个问题是一旦表结构改了比如新增列SELECT *的结果集也会跟着变代码里按列序号取数的地方可能就悄悄崩了。在JavaWeb开发里查询结果往往要映射成实体对象或者Map。如果返回了多余的字段轻则多消耗内存重则出现字段名冲突。比如两个表JOIN之后都有同名的id字段用ResultSet按列名取值的时候就容易拿错。我自己写查询有个习惯无论是简单查询还是多表JOINSELECT后面永远是明确列出的字段。哪怕多敲几个字也值。3.2 WHERE条件别小看运算符细节WHERE里最常见的就是比较运算符和逻辑运算符。、、、、、不等于、LIKE模糊匹配、BETWEEN AND范围、IN集合匹配这些都很基础。但有两个坑值得单独说。第一个是NULL的坑。在MySQL里NULL和任何值比较结果都是UNKNOWN既不是TRUE也不是FALSE。所以WHERE score NULL永远查不到数据必须写成WHERE score IS NULL。实际工作中经常出现这样的场景用户表的注册时间字段是NULL表示未完善资料。你想找出所有未完善资料的记录写成WHERE register_time NULL查了半天一条都没有。这种错在新手里极其常见。第二个坑是LIKE的索引失效。LIKE %关键词%这种前后都带通配符的写法会导致索引无法使用。如果表里有几百万行这条查询基本就是全表扫描。实际业务里搜索功能很难完全避免这种写法但至少你心里要有数这个查询是慢的后续要考虑搜索引擎或者前缀匹配之类的方案。3.3 DISTINCT去重DISTINCT这个关键字在JavaWeb里用得不多但在做数据统计的时候很实用。比如查一下一共有多少个不同的班级SELECT DISTINCT class_id FROM student;注意DISTINCT是对整个行去重不是对单列去重。也就是说DISTINCT class_id, name会把两个字段拼起来判断是否相同。如果业务上想“查出去重后的班级数量”要配合COUNT使用COUNT(DISTINCT class_id)。这两个写法的区别在Excel透视表里也有对应只是SQL写错了不像Excel那样好发现。4. 排序加分页其实藏着性能大学问4.1 ORDER BY的两面性排序本身不难ORDER BY score DESC或者ORDER BY class_id ASC, score DESC。但排序是数据库里比较贵的操作之一。数据量小的时候无所谓数据量大起来排序经常让查询慢得离谱。排序的优化思路最直接的就是让排序字段走索引。MySQL在BTree索引上如果本身就是有序的那排序就不需要额外做filesort了。比如你经常按create_time倒序查那给create_time建个索引排序性能就是质的飞跃。还有一个多字段排序的细节。ORDER BY class_id ASC, score DESC的意思是先按班级升序排班级相同的再按分数降序排。这个顺序的理解很多人会搞反尤其是后面还要加分页的时候一旦顺序不稳分页就会翻车。4.2 LIMIT分页的深坑JavaWeb里的列表页几乎都是分页的。最常见的分页写法SELECT * FROM student ORDER BY id LIMIT 0, 10;第一页传0第二页传10第三页传20。语法简单但有一个深层问题LIMIT的偏移量越大查询越慢。原因在于LIMIT 100000, 10实际执行的时候MySQL会把前100010条都查出来然后丢掉前100000条只返回最后的10条。前面那些脏活累活全白干了。这个用生活类比来解释就像你要从一摞文件里找第100001页到第100010页只能从第一页开始一页一页翻前面基本都翻废了。解决思路有几个如果排序字段是自增主键可以用WHERE id 上一页最大id的方式翻页。这个叫“键集分页”翻到越深优势越明显。用子查询先查出起始位置的id再关联查询数据。如果业务允许限制最大翻页深度超过就提示用户缩小筛选范围。实际项目中深分页的问题基本都出现在后台管理系统的列表里。用户翻到几百页以后接口响应时间就会飙升。我当时排查过一个2000多页的列表接口没优化之前响应3秒多改成键集分页以后直接掉到200毫秒以内。这里有一点要注意键集分页要求排序字段唯一且有序。如果只按score排序而score有大量重复值单纯用id比大小就不够严谨了排序条件里最好加上id作为次级排序。5. 聚合查询统计报表绕不开的核心5.1 五个聚合函数常用的聚合函数是五个COUNT、SUM、AVG、MAX、MIN。COUNT和SUM经常被搞混。COUNT是数行数的SUM是求和的。比如统计学生人数COUNT(*)统计总分SUM(score)。语义上很清楚但在复杂SQL里经常写错。AVG有一个细节如果被统计的列里有NULLAVG会自动忽略。这通常是我们想要的行为但如果你希望NULL被当作0算进去就得先处理NULL比如AVG(IFNULL(score, 0))。MAX和MIN的用途比想象中多很多不只是求最大最小值。比如你可以用MAX(create_time)来找最近一条记录的时间。配合GROUP BY还可以实现“每组里最新的一条记录”这种需求。5.2 GROUP BY分组的本质是什么GROUP BY的本质是把相同的值合并成一组然后对每一组做聚合运算。要注意的是SELECT后面出现的非聚合列理论上必须出现在GROUP BY里。比如SELECT name, class_id, AVG(score) FROM student GROUP BY class_id;这条SQL在MySQL的默认设置下可能不会报错但name取的是每组里的哪一条是随机的结果不可控。这是一个非常危险的隐患。到了MySQL 5.7及以上版本默认开启了ONLY_FULL_GROUP_BY模式这种SQL直接报错。如果哪天你发现自己的SQL在这台机器上好好跑换了一台就开始报错多半就是这个模式的问题。5.3 HAVING和WHERE别混用前面已经在执行顺序里讲过WHERE在分组前过滤行HAVING在分组后过滤组。记住一句话能写在WHERE里的条件不要写在HAVING里。为什么因为WHERE的执行时机早可以先缩小数据范围减少分组时的计算量HAVING是在所有分组都算完之后再筛一遍效率上差了很多。比如查询平均分大于70的班级先加个WHERE把及格线以下的人排除掉计算量就小了很多逻辑上也说得通。有些初级开发习惯把所有过滤条件都堆在HAVING里数据量小的时候没感觉数据量大了这个差距就是几倍甚至十几倍。6. 多表JOINJavaWeb业务里真正的主角6.1 什么时候需要JOIN一张表永远搞不定业务。用户表存了用户基本信息订单表存了订单记录订单里存的user_id只是用户的ID。前端页面要显示订单详情并带上用户名你得把订单表和用户表关联起来才能查出来。JOIN就是把两张或多张表接起来按关联条件拼成一张大表再去做筛选和投影。6.2 INNER JOIN、LEFT JOIN、RIGHT JOIN怎么选这个选择我在实际开发中见过无数人搞混。INNER JOIN只查出两表都匹配的行也就是交集。LEFT JOIN以左表为主左表的行全部保留右表没有匹配的填NULL。RIGHT JOIN反过来。实际的JavaWeb项目里95%的场景只会用到INNER JOIN和LEFT JOINRIGHT JOIN我几乎没用过。因为把右表当主表的逻辑写的时候把表顺序调一下用LEFT JOIN表达读起来更顺手。下面这个例子是把学生表和班级表关联用LEFT JOIN查询每个学生的班级信息SELECT s.name, s.score, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.id;注意JOIN的ON条件只是关联条件不是筛选条件。很多人把WHERE条件塞在ON后面用AND接有时候效果一样但有时候完全不同。ON的AND是在JOIN的过程中做条件匹配WHERE是在JOIN完成以后对结果集过滤。如果关联条件里写了过滤逻辑查出来的数据量可能会跟你预期的不一样。6.3 JOIN带来的重复数据问题JOIN很容易把数据弄重复。比如订单表和订单明细表关联一个订单有三条明细JOIN之后这个订单就变成三行了。如果你在统计订单数量的时候直接COUNT(*)数字就错了。这是个非常经典的坑。解决方案是统计订单数量的时候用COUNT(DISTINCT o.id)或者在关联之前先聚合明细表再关联订单表这样就能避免重复计数。实际的项目里我见过因为一个JOIN重复没处理好导致管理后台的销售统计数字跟财务对不上账的情况。查了两天才定位到问题。所以记住JOIN完以后先看一眼结果的行数有没有变大再往下走。6.4 一个完整的JOIN查询示例三个表的使用也非常普遍。用户表、订单表、订单状态表查所有订单并附带用户名、订单状态名称和订单金额金额大于100的还要按金额倒序排SELECT u.username, o.order_no, st.status_name, o.amount FROM orders o INNER JOIN users u ON o.user_id u.id LEFT JOIN order_status st ON o.status st.status_code WHERE o.amount 100 ORDER BY o.amount DESC;这个例子串起了前面讲的绝大部分知识点明确列投影、JOIN类型选择、ON关联条件、WHERE行过滤、ORDER BY排序。能独立写出这条SQL并且能解释每行是干什么的DQL就算入门了。7. 子查询进阶逻辑拆分与综合表达7.1 子查询的用法子查询就是套在查询里的查询。很多用JOIN能解决的问题用子查询也能解决两种方案在效率上可能不同要看执行计划才能确定。比较常见的子查询场景是用IN或NOT IN找出所有没有订单的用户SELECT * FROM users WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);还有标量子查询比如在SELECT后面查一个单值。查出每个学生的分数和他所在班级的平均分SELECT name, score, (SELECT AVG(score) FROM student WHERE class_id s.class_id) AS class_avg FROM student s;这个写法在报表场景里很有用。一行数据旁边跟着一个相关的平均值一眼就能看出这个学生跟班级平均水平的关系。7.2 子查询的性能问题子查询不是万能的。尤其是IN后面跟一个大数据量的子查询时效率经常不好。MySQL的优化器对某些子查询的处理方式不太优雅说不准什么时候就把子查询整个物化一遍内存和时间都吃了。所以遇到IN子查询我的习惯是先用JION or EXISTS试一试再对比一下执行时间。比如“找出有订单的用户”这个需求SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id o.user_id;和SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE u.id o.user_id);两条结果一样写法不同。在数据量大的时候EXISTS往往更快因为它不是把所有子查询结果都算出来而是逐行去判断满足条件就返回。但这个东西跟数据分布和索引有关没有绝对的快慢关键是要去执行EXPLAIN只看不测就是瞎猜。7.3 EXISTS和NOT EXISTS的补集思路NOT EXISTS比NOT IN在语义上更清晰。注意NOT IN有一个大坑如果子查询的结果里有NULLNOT IN的结果集会是空。因为NULL打断了整个判断链。SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);这样写没问题但如果你不确定子查询里有没有NULL直接写NOT IN查出来空结果都不知道哪里错了。用NOT EXISTS写就不需要考虑这个问题。这个细节在面试里也经常被问到。能答出“NOT IN遇到NULL会失效”的人至少在SQL这个方向是有过深层思考的。8. 进阶实用技巧把DQL用得更顺手8.1 常用函数字符串、日期、流程控制JavaWeb开发的报表接口里DQL经常会用到函数。最常用的是这几类字符串CONCAT拼接、SUBSTRING截取、IFNULL空值替换、REPLACE替换日期DATE_FORMAT格式化、NOW当前时间、DATEDIFF日期差、DATE_ADD日期加减聚合配合DATE_FORMAT(create_time, %Y-%m) 可以把时间按月份分组举个例子统计每个月的订单数SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);这里要提醒一下在GROUP BY和ORDER BY里使用时间函数的时候因为函数处理过列之后就不是普通索引命中的情况了数据量大时会形成临时表做分组性能会差不少。8.2 CASE WHEN的妙用CASE WHEN相当于SQL里的if-else。做数据统计的时候它可以实现在一条SQL里做条件计数。比如统计每个班级的及格人数和不及格人数SELECT class_id, SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) AS pass_count, SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) AS fail_count FROM student GROUP BY class_id;这种写法比查两次再合并要高效得多。实际工作中做报表统计、接口返回状态汇总这些场景CASE WHEN几乎天天用。8.3 LIKE查询优化的替代方案前面提到LIKE %abc%会让索引失效。但实际业务中比如用户搜索商品名称、按照订单号模糊查询这类需求很难完全避免。常见的替代思路是前缀匹配LIKE abc%如果通配符不在开头索引还是能用的。用LOCATE或INSTR函数判断字符是否存在于字段中但有时候效率也不会更好。面对真正大规模模糊搜索的需求与其硬扛SQL不如老实做全文索引或者下沉到搜索引擎。字段本身数据量小的话直接模糊匹配就完了没啥性能压力。核心还是那句话先搞清楚数据量再谈优化。8.4 EXPLAIN怎么说EXPLAIN是定位SQL性能最重要的工具没有之一。在SQL前面加个EXPLAINMySQL就会告诉你它的执行计划重点看这四列type访问级别all代表全表扫描ref/range比较常见index代表索引扫描const是主键或唯一索引的等值匹配性能从好到差的排序大概是const ref range index all。key实际用的索引名字。如果这一列是NULL说明压根没走索引。rows预估扫描的行数。这个数字越大越危险。Extra会出现Using filesort、Using temporary这样的关键信息暗示排序用了临时文件或者分组产生了临时表优先去优化。排查慢SQL的时候先跑EXPLAIN再决定是加索引还是改写法方向上就对了。9. 工作中最常见的SQL排查清单9.1 查询莫名变慢先做这几件事遇到一条查询以前快现在慢或者数据量大了以后越来越慢我一般按下面的顺序排查先看是不是没走索引。EXPLAIN一下key为NULL就直接加索引。看关联字段有没有建索引。JOIN的ON条件字段上没有索引两个大表关联就是灾难效果就像两边都扫一遍然后互相匹配。看是不是用了函数包裹了索引列。WHERE里写DATE(create_time) 2024-01-01索引直接失效。改成范围比较create_time 2024-01-01 AND create_time 2024-01-02就能用上索引了。看是不是LIMIT偏移量太大考虑键集分页。看是不是查询了太多不需要的字段。SELECT * 在列少的表上还好列多的表就是白白的开销。9.2 多表JOIN把结果搞乱了怎么快速定位JOIN结果比预期多了几行大多原因是关联字段不是唯一的。比如用user_id关联如果右表里某个user_id有多行结果就膨胀了。排查步骤先分别看两个表的数据量确认哪边出现了重复。用GROUP BY 关联字段 HAVING COUNT(*) 1查一下重复值。确认重复后要么在关联前先对右表去重聚合要么调整JOIN逻辑。这个排查流程本身就是一个加索引、执行EXPLAIN、看重复数据的组合拳工作量不大但价值很高。9.3 一个错误的常见场景WHERE和ORDER BY字段不一样如果WHERE里用了字段A过滤ORDER BY用字段B排序而字段B没有索引排序就要把整批结果拉出来做filesort。为了排序单独建索引有时候也有点讲究因为MySQL只支持一棵树多条件排序时索引列顺序有讲究。所以如果业务上经常出现 “按状态过滤按时间倒序排序” 的场景给 (status, create_time) 建一个复合索引一条SQL就能同时覆盖过滤和排序。10. 一点个人经验带过不少新同事写SQL发现他们最常犯的错并不是语法记不住而是根本不知道SQL是怎么执行的。一个WHERE、GROUP BY、HAVING、ORDER BY的位置无所谓执行顺序完全不同导致了大量难以排查的逻辑问题。我的建议是找几份真实业务的数据表自己写查询把每个关键字的执行顺序背到滚瓜烂熟。平时写完一条DQL顺手EXPLAIN一下长期下来对SQL的理解会扎实很多。DQL这块内功练好了后面学MyBatis、学JPA、学各种ORM框架都会轻松很多因为那些框架最终还是把Java代码翻译成DQL去跑。框架只是工具SQL才是一直要用的核。这个内容往后还可以继续延伸索引底层原理BTree、MySQL事务隔离级别、MVCC、SQL优化实战。先把DQL这块地基打牢再往那边走就顺了。