搞javaWeb开发的兄弟应该都有同感一个项目能不能跑起来看框架和配置但业务能不能做得下去看的是数据这一层。而数据层里面DQLData Query Language数据查询语言又是日常开发中占了八成以上工作量的一块。我最早学javaWeb的时候Servlet、JSP、三层架构这些学得都挺顺结果一接手实际项目发现业务需求落到代码里卡点全在一条SQL写不出来。后来专门把MySQL的DQL啃了一遍再回头看那些订单列表、统计报表、条件筛选的功能思路一下子就通了。这篇文章就围绕javaWeb后端开发中最常用的MySQL DQL展开从查询语句的骨架讲起覆盖条件过滤、排序、聚合分组、多表连接、子查询、分页这些高频场景。中间会穿插我自己在项目里踩过的坑以及一些排查问题的方法。不管你是刚学到javaWeb的新手还是写了一些CRUD但总觉得SQL不够扎实的开发者这篇文章都能帮你在DQL上补一块短板。1. 内容整体设计与思路拆解1.1 为什么javaWeb学习一定要跨过DQL这道坎很多初学javaWeb的人有个误区觉得后端开发的核心是框架Spring、SpringMVC、MyBatis搭起来能跑通就完了。但实际开发里框架只是骨架真正的血肉是业务逻辑而业务逻辑落到最后基本都是对数据库的查询和计算。举个例子一个最简单的后台用户列表前端传过来一组条件用户名模糊搜索、注册时间段、用户状态、分页页码。你以为这是Java代码的遍历筛选不是。生产环境的数据量动不动几十万上百万条不可能把所有数据查出来再在内存里过滤。正确的做法是直接在SQL层面把这些条件拼进WHERE子句让数据库在最底层完成过滤只把需要的几十条数据返回给Java层。这就是DQL的意义所在。我见过不少新手写项目功能都实现了但打开慢查询日志一看一条简单的列表接口触发了好几次全表扫描数据量上了十万就卡成PPT。本质原因就是DQL基本功不扎实不会用索引、不会合理组织查询条件、不清楚join该不该用。在javaWeb的学习路线里数据库查询能力往往决定了你能在这个行业走多深。1.2 DQL在javaWeb开发中的业务场景前面提到了用户列表这里再展开说几个javaWeb项目里特别典型、特别常见的DQL场景你会发现它们其实都是同一个核心能力的变体。第一个是登录认证。你提供一个账号或手机号后端要用DQL去用户表里精确匹配一条记录再比对密码。这个查询看起来简单但涉及唯一索引的设计、条件列的正确使用以及防SQL注入的处理都属于DQL在真实业务里的细节。第二个是后台管理系统的列表页。几乎所有管理后台都是由“条件筛选排序分页”构成的比如订单列表按状态筛选、按金额排序、下拉选择时间范围。这些需求翻译成SQL就是一个带有动态条件的DQL查询。第三个是统计报表。比如电商项目要统计每天销售额、每个商品的销量排行、每个用户的消费总额。这要用到聚合函数、GROUP BY分组、HAVING筛选分组后的数据。写这种SQL时对分组逻辑的理解稍有偏差结果就会差之千里。第四个是关联数据查询。订单表关联用户表拿用户名商品表关联分类表拿分类名称这些跨表取数据的需求在javaWeb里几乎每个模块都会遇到。多表连接JOIN就是面试必问、工作必用的核心技能点。你会发现以上这些场景没有一个是靠死记硬背SQL语法能搞定的而是需要你把业务需求翻译成查询逻辑。DQL不是一条条孤立的SQL语句它是一套“把数据需求表达给数据库”的思维方式。这个东西通了后面学MyBatis的resultMap映射、动态SQL拼接都会顺理成章。2. 核心细节解析与实操要点2.1 查询语句骨架顺序即思路一条完整的DQL语句长这样SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段 HAVING 分组后的过滤条件 ORDER BY 排序字段 LIMIT 分页参数;我刚开始学的时候觉得这只是语法的排列顺序背下来就行。后来才意识到这个顺序其实也是数据库执行查询的思考顺序。理解执行顺序比背语法更重要因为它决定了你能不能用正确的方式写WHERE、GROUP BY、HAVING。MySQL执行一条查询时实际的大致顺序是先通过FROM定位到要操作的表再根据WHERE条件过滤行数据接着按GROUP BY指定的字段分组分组后若还有条件则用HAVING过滤分组然后才轮到SELECT投影出想要的列最后是ORDER BY排序和LIMIT截取条数。这里有一个非常关键的推论WHERE是在分组之前执行的所以它是用来过滤原始行的HAVING是在分组之后执行的所以它是用来过滤分组的。如果你在HAVING里写一个不涉及聚合函数、完全可以用WHERE完成的条件不仅逻辑绕性能也会打折扣。还有一点新手容易踩坑SELECT子句里定义的别名在GROUP BY和ORDER BY中通常可以使用但在WHERE中不能使用。因为执行顺序里WHERE先于SELECT别名还没有生成。你在where里写where alias 10MySQL会直接报错说字段不存在。这个细节虽然小但项目里排查报错时能帮你省不少时间。2.2 条件过滤、排序与去重WHERE子句是DQL里出现频率最高的部分核心就是各种条件的组合和运算符的使用。常用的运算符包括、!、、、、、、BETWEEN AND、IN、LIKE、IS NULL。以及逻辑运算符AND、OR、NOT。组合使用时有一个优先级问题AND的优先级高于OR。比如SELECT * FROM user WHERE status 1 OR status 2 AND age 18;这会被解析成status 1 OR (status 2 AND age 18)因为AND先执行。如果你想让OR先生效就必须加括号。这是一个面试和实际开发中都容易忽视的细节我只能说任何复杂的条件组合该加括号就加括号别指望读代码的人去猜你的优先级。LIKE模糊查询也是高频坑区。%代表任意多个字符_代表单个字符。比如要查所有姓张的用户SELECT * FROM user WHERE name LIKE 张%;但如果你写成LIKE %张%虽然能查到数据可这种以%开头的模糊查询无法走索引数据量大时性能会急剧下降。这一点放到后面索引章节再展开这里先记住结论能用前缀匹配就不要用包含匹配。去重也是DQL里容易理解偏差的操作。SELECT DISTINCT是对查询结果的完全去重也就是说只有当两条记录的所有查询列都相同才会被合并为一条。比如你想查所有用户所在的城市SELECT DISTINCT city FROM user没问题但如果你写SELECT DISTINCT city, name FROM user去重粒度变成了“城市姓名”的组合结果跟你想的就不是一回事了。ORDER BY排序则有两种形态单字段排序和多字段排序。多字段排序的规则是“先按第一个字段排字段值相同时再按第二个字段排”。比如SELECT * FROM order_list ORDER BY status ASC, create_time DESC;意思是先按状态升序排相同状态下再按创建时间降序排。这个逻辑在列表展示时非常常用比如“展示待处理订单时先按紧急程度分档档内按时间倒序”。补充一个容易出问题的点排序字段如果是字符串类型的数字那么排序会按字典序进行而不是数值序。比如“10”会排在“9”的前面因为字符1比字符9小。如果你在设计表结构时把本该是整数的字段用成了varchar后期排序就会出现这种反直觉的结果。这类问题在DQL运行不出来时往往要到表结构设计上找原因。2.3 聚合、分组与having聚合函数是DQL从“查数据”升级到“算数据”的分水岭。常用的五个聚合函数是COUNT(*), COUNT(字段), SUM(字段), AVG(字段), MAX(字段), MIN(字段)先讲一个我印象很深的坑COUNT(*)和COUNT(字段)的结果可能不一样。COUNT(*)统计的是行数不管这一行的其他字段是不是NULL而COUNT(字段)统计的是该字段非NULL的记录数。举个例子订单表里有100条记录其中50条的remark字段是NULL。COUNT(*)是100COUNT(remark)是50。你在写统计接口时如果没想清楚到底要数什么很容易在数据校验阶段被打回。SUM、AVG、MAX、MIN这些函数都会自动忽略NULL值。这在大多数情况下是好事比如AVG计算平均分时NULL的缺考记录不会被当成0分拉低平均值。但反过来想如果你期望把NULL当成0来计算聚合结果就会和预期不符。遇到这种情况可以用IFNULL(字段, 0)来先把NULL替换成0再做聚合。GROUP BY是配合聚合函数使用的核心。它的逻辑是按指定字段把记录分成若干个组然后对每个组分别做聚合计算。写GROUP BY时有一个非常经典的问题查询列中出现了既不在GROUP BY里也不在聚合函数里的字段。比如SELECT name, city, COUNT(*) FROM user GROUP BY city;在MySQL的某些版本和配置下主要是5.7之前的版本或者关闭了ONLY_FULL_GROUP_BY模式这条SQL能跑但返回的name是每组里随机挑出来的没有任何意义。而在开启了ONLY_FULL_GROUP_BY模式的MySQL 5.7及以上版本中这种SQL会直接报错。所以我的建议是从一开始就养成好习惯SELECT的字段要么在GROUP BY里出现要么被聚合函数包裹。一旦违反这个规则结果就是“能跑但不对”或者“直接报错”在项目里都是很被动的。HAVING和WHERE的区别前面提过这里用一个直观的例子收尾你要统计下单次数超过5次的用户。先WHERE过滤掉无效订单再GROUP BY user_id分组统计下单次数最后HAVING COUNT(*) 5过滤分组。整个过程“先过滤行、再分组、再过滤组”逻辑清晰也符合执行顺序。SELECT user_id, COUNT(*) AS cnt FROM order_list WHERE status 1 GROUP BY user_id HAVING cnt 5;注意这里的cnt是SELECT里定义的别名在HAVING中可以使用因为HAVING的执行顺序在SELECT投影之后。这个细节和WHERE不能用别名形成了鲜明对比理解执行顺序后就不会记混了。2.4 多表连接join的正确打开方式实际javaWeb项目里几乎没有哪张业务表能独立支撑起一个完整页面。订单要关联用户表拿买家昵称商品要关联分类表拿分类名权限要关联角色表、菜单表。多表连接查询是DQL里最重要也最容易出错的部分。先理解JOIN的本质它是把两张表按一定的匹配条件“拼接”成一张虚拟表然后你在这张虚拟表上进行查询。根据拼接方式的不同主要分三种INNER JOIN只返回两边都匹配上的记录LEFT JOIN返回左表全部记录右表没有匹配的就补NULLRIGHT JOIN返回右表全部记录左表没有匹配的就补NULL实际工作里LEFT JOIN用得最多因为它能保证左表的记录不丢。比如查询所有用户及其订单数量哪怕某用户一单都没下你希望结果里还保留这个用户数量显示为0这就必须用LEFT JOIN。ON和WHERE的区别是另一个高频坑。ON在连接阶段指定两张表的匹配条件而WHERE在连接完成之后对结果做过滤。对INNER JOIN来说把过滤条件写在ON和WHERE里结果一样但对LEFT JOIN来说两者有本质区别。这里我踩过实打实的坑。当时写一个报表查询要求“查所有客户及其订单金额”但只需要已支付订单的金额。我一开始把支付状态写进了WHERESELECT c.id, c.name, SUM(o.amount) FROM customer c LEFT JOIN orders o ON c.id o.customer_id WHERE o.status paid GROUP BY c.id, c.name;结果本来应该保留未下单客户但因为WHERE把右表匹配不到产生的NULL行全过滤掉了未下单客户全没了。正确的写法是把支付状态作为ON条件的一部分SELECT c.id, c.name, SUM(o.amount) FROM customer c LEFT JOIN orders o ON c.id o.customer_id AND o.status paid GROUP BY c.id, c.name;这样左表记录全保留未下单客户虽然o.status是NULL但行还在恰好满足了需求。本质上ON决定“怎么拼”WHERE决定“拼完之后要哪些”。这个区别理解了LEFT JOIN就算真正入门了。还有一个常见错误JOIN时漏写ON条件或者ON条件写错了。两张表一旦缺少连接条件就会做笛卡尔积结果行数是两表行数相乘。比如10万用户表连接5万订单表且没有ON结果会是50亿行数据库直接卡死。我排查过不少“数据库CPU突然飙满”的问题最后发现就是有人在测试环境写了一条没带ON的JOIN。多对多关系也是JOIN的经典场景。典型的如用户和角色中间用一个关联表user_role来维护关系。查询用户及其角色名通常需要两次JOINSELECT u.name, r.role_name FROM user u LEFT JOIN user_role ur ON u.id ur.user_id LEFT JOIN role r ON ur.role_id r.id;这个模式在javaWeb的权限管理模块里非常常见。理解JOIN的本质之后你会发现这种“链式拼接”其实很直观。2.5 子查询与派生表子查询就是嵌套在另一个查询里的查询。根据返回结果的不同可以分为标量子查询返回单行单列、列子查询返回单列多行、行子查询返回单行多列和表子查询返回多行多列。标量子查询最简单也是最常用的。比如要查询“下单金额最高的用户信息”SELECT * FROM user WHERE id (SELECT user_id FROM orders ORDER BY amount DESC LIMIT 1);这里子查询返回一个值然后外层查询用它做条件。注意子查询的结果必须是单行单列否则会报“Subquery returns more than 1 row”的错误。列子查询通常配合IN使用。比如查“购买过某个分类商品的用户”SELECT * FROM user WHERE id IN ( SELECT DISTINCT user_id FROM orders WHERE goods_category 数码 );这类查询里有一个存在已久的争论IN和EXISTS哪个快。简单记忆法是小表驱动大表原则如果外层表数据量小用IN更直接如果子查询返回的结果集小而外层表很大用EXISTS可能更好。不过MySQL的优化器对IN子查询有半连接优化实际性能差距在多数场景下并不像老博客说的那么玄乎真正要关注的是有没有走索引。表子查询的典型用法是把子查询结果当作一张“派生表”来查询。比如先查出每个用户的订单总额再从这个结果里筛出总额大于10000的用户SELECT user_id, total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) AS t WHERE total_amount 10000;这种写法在逻辑上很好理解先“算中间结果”再“从中间结果里筛选”。在MySQL里派生表会被物化或合并优化具体执行计划要看版本和数据量但作为书写习惯它能让复杂需求拆解成清晰的步骤。子查询还有一个用途是关联子查询也就是子查询里引用外层查询的字段。经典的例子是查“每个分类下价格最高的商品”SELECT * FROM goods g WHERE price ( SELECT MAX(price) FROM goods WHERE category_id g.category_id );这种查询的思考方式有点像“对每一条外层记录去内层找最值”逻辑上很自然但性能要小心因为每一行都可能触发一次子查询执行。实际项目里如果数据量大这类SQL很多时候会被改写为JOIN与窗口函数组合这涉及到DQL进阶层面的优化了。窗口函数比如ROW_NUMBER() OVER (PARTITION BY ...)在MySQL 8.0中正式支持解决“分组内取TopN”这类问题比子查询优雅得多。如果你用的是MySQL 8.0及以上版本建议学DQL的同时把窗口函数的基础用法过一遍很多复杂的查询会变得异常简单。2.6 分页查询的公式与深坑分页查询是javaWeb项目里绕不开的话题几乎所有列表接口都要做分页。MySQL的分页靠LIMIT实现两种写法等价SELECT * FROM order_list LIMIT 20, 10; SELECT * FROM order_list LIMIT 10 OFFSET 20;第一种写法逗号前面是偏移量后面是返回条数第二种写法从语义上更好理解返回10条跳过20条。我习惯用第二种因为阅读时更清楚。分页查询有个通用公式假设每页条数为pageSize请求的是第pageNum页那么偏移量就是(pageNum - 1) * pageSize。比如每页10条第1页就是LIMIT 0,10第2页就是LIMIT 10,10。在javaWeb的接口里pageNum和pageSize通常由前端传入后端在做一层参数校验后拼进SQL。分页最深的坑是深分页问题。当你查第100000页的时候LIMIT偏移量是999990MySQL仍然需要先扫描前999990条记录然后全部丢弃再取后面的数据。随着页码越翻越深查询耗时越来越高这在后台报表系统里尤其明显。我处理过的一个实际案例一张订单表有两百万条数据管理后台的分页查询在翻到几百页之后接口耗时从几十毫秒飙升到四五秒。排查后发现就是典型的深分页。当时的优化方案有两个一是前端加了筛选条件把翻页深度控制在合理范围内二是后端改成了“基于游标”的分页思路不是用页码跳转而是记录上一页最后一条订单的id查询时用WHERE id 上一页最大id LIMIT pageSize。第二种方式在逻辑上同样实现“下一页”效果但利用了主键索引的范围扫描性能稳定不会越翻越慢。当然它牺牲了“任意页码跳转”的能力不是所有场景都适用但对于瀑布流或“加载更多”类交互游标分页是更优解。作为DQL学习者理解这两种分页思路的差异和适用场景是进阶必须过的一关。3. 实操过程与核心环节实现3.1 场景建模三张表搭一个订单统计需求理论知识说了一堆不如动手过一遍。这里打算用一个贴近真实业务的场景把前面提到的DQL知识点串起来。假设我们在开发一个电商管理系统的后台需要实现以下三个查询需求。为了方便演示先建三张简化版业务表用户表、订单表、订单明细表。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, city VARCHAR(20), reg_time DATETIME ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2), status TINYINT COMMENT 1-已支付 0-未支付, create_time DATETIME ); CREATE TABLE order_item ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, goods_name VARCHAR(100), price DECIMAL(10,2), quantity INT );这三张表的关系很清晰用户一对多订单订单一对多明细。实际项目里表结构比这复杂得多但用来练习DQL正好够。3.2 从业务需求到SQL完整推导过程需求一查询注册时间在最近30天内的用户列表按注册时间倒序排列分页展示。这个需求最直接涉及WHERE时间条件、ORDER BY、LIMIT。注意时间的边界处理一般用闭区间比较稳妥SELECT id, name, city, reg_time FROM user WHERE reg_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY reg_time DESC LIMIT 20 OFFSET 0;需求二统计每个城市已支付订单的总金额筛选出总金额大于5000元的城市按金额从高到低排序。这是一个典型的“聚合分组HAVING排序”组合需求。分析过程是先关联用户表和订单表再通过WHERE过滤已支付订单接着按城市分组计算总金额用HAVING过滤分组最后排序SELECT u.city, SUM(o.amount) AS total_amount FROM user u INNER JOIN orders o ON u.id o.user_id WHERE o.status 1 GROUP BY u.city HAVING total_amount 5000 ORDER BY total_amount DESC;这里有个小细节HAVING里可以直接用别名total_amount但如果你为了兼容性考虑也可以写成HAVING SUM(o.amount) 5000结果一致。需求三查询每个商品的销售总量和销售总额并按销售总额降序取前10。这个需求涉及订单明细表用了SUM和GROUP BY并做ORDER BY LIMITSELECT goods_name, SUM(quantity) AS total_qty, SUM(price * quantity) AS total_sales FROM order_item GROUP BY goods_name ORDER BY total_sales DESC LIMIT 10;需求四查出每个用户最近一单的支付金额。这个稍微复杂可以用关联子查询实现。先找出每个用户最近的订单时间再关联订单表取金额SELECT u.id, u.name, o.amount AS last_paid_amount FROM user u LEFT JOIN orders o ON o.user_id u.id AND o.create_time ( SELECT MAX(create_time) FROM orders WHERE user_id u.id AND status 1 );这里如果用户没有已支付订单LEFT JOIN保证用户仍会出现amount为NULL。如果用户有两单在同一时间创建查询可能返回多行但作为示例已经足够说明关联子查询的用法。这四个需求把DQL的主要知识点全部覆盖了条件过滤、排序、聚合、分组、HAVING、连接、子查询、分页。你在实际项目里遇到的大多数查询基本都能拆解成这些基础块的组合。3.3 在javaWeb项目中落地JDBC与MyBatis两种姿势SQL写出来了接下来关键的一步是把它接进javaWeb项目。这涉及一个非常重要的原则SQL应该以参数化的方式传给数据库而不是靠拼接字符串。先看JDBC时代的正确写法。PreparedStatement是预编译的它在数据库端先完成SQL模板的编译然后每次执行只绑定参数值既提高了执行效率也从根上避免了SQL注入。String sql SELECT id, name, city, reg_time FROM user WHERE city ? ORDER BY reg_time DESC LIMIT ?, ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, city); ps.setInt(2, offset); ps.setInt(3, pageSize); ResultSet rs ps.executeQuery();这里的?占位符会在数据库端被转义处理传入的参数无论内容多特殊都只会被当作字面量不会被拼进SQL结构里。反过来说如果你图省事用字符串拼接String sql SELECT * FROM user WHERE city city ;一旦city的内容是 OR 11整个查询条件就会被改写这就是SQL注入的根源。我见过不止一个新手项目这么写过安全测试一抓一个准。在Spring Boot项目里MyBatis是更主流的方案。MyBatis的#{}占位符就相当于JDBC的PreparedStatement参数绑定而${}是字符串拼接。只要能用#{}就绝不碰${}除非那个位置确实需要动态拼表名或排序字段且你做了严格的白名单校验。select idselectUserPage resultTypeUser SELECT id, name, city, reg_time FROM user where if testcity ! null and city ! AND city #{city} /if /where ORDER BY reg_time DESC LIMIT #{offset}, #{pageSize} /selectMyBatis的动态SQL标签if、where、foreach本质上是帮你按条件拼SQL但底层执行时用的还是预编译绑定。你需要明白一点动态SQL拼的是“SQL的结构”而#{}绑定的是“数据值”两者不冲突。但如果把数据值用${}拼进SQL结构里就出大问题了。还有一个小细节LIMIT的参数在MyBatis里建议用#{}传参比如上面的offset和pageSizeMySQL的LIMIT支持预编译参数不会影响执行计划。有人习惯写成${}这是没必要的反而引入了注入风险。4. 常见问题与排查技巧实录4.1 NULL值引发的怀疑人生NULL是DQL里最阴间的存在没有之一。它既不等于任何值也不不等于任何值。你用WHERE name NULL查不到任何记录因为正确写法是IS NULL。聚合函数对这一块的规则前面提过SUM、AVG、MAX、MIN自动忽略NULLCOUNT(字段)只统计非NULL行。我在实际项目中遇到最迷惑的问题是统计用户性别分布时有一部分用户性别字段为NULL然后按性别分组会单独出现一个NULL组页面显示成“空字符串”运营同学来问这是哪来的数据。解决办法是把NULL先映射成“未知”SELECT IFNULL(gender, 未知) AS gender, COUNT(*) FROM user GROUP BY IFNULL(gender, 未知);还有一个容易出错的场景是NULL参与算术运算。在MySQL里NULL加任何数还是NULL。比如订单表有个优惠金额字段你计算实际支付金额写amount - discount只要discount是NULL结果就是NULL而不是amount本身。这时候必须用IFNULL兜底SELECT amount - IFNULL(discount, 0) AS real_amount FROM orders;这个坑在财务计算里非常致命我调试过整整一下午才发现是NULL在作怪。4.2 索引失效写了查询却慢如蜗牛很多DQL问题不是“查不出来”而是“查得特别慢”。排查时第一反应永远是看SQL有没有走索引。用EXPLAIN关键字可以查看一条SQL的执行计划EXPLAIN SELECT * FROM user WHERE name 张三;重点看type列和rows列。type从好到差依次是const、eq_ref、ref、range、index、ALL。见到ALL就说明是全表扫描数据量大时性能堪忧rows列则是预估扫描行数数字越大越危险。以下几类我踩过的索引失效场景值得记在小本子上第一对索引列使用函数。比如WHERE DATE(create_time) 2024-01-01即使create_time有索引也走不了。正确做法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二LIKE以通配符开头。LIKE %张%无法走索引LIKE 张%可以。实在需要模糊包含搜索可以考虑全文索引或者借助搜索引擎中间件。第三隐式类型转换。索引列是varchar查询时传了数值MySQL会把varchar列转成数值再比较导致索引失效。比如WHERE phone 13800138000phone是varchar这里即使有索引也用不上。正确写法是WHERE phone 13800138000。第四使用OR连接多个条件其中只有一部分列有索引。这种情况下优化器可能放弃索引改用全表扫描。解决办法是把OR拆成两个查询后用UNION ALL合并或者改成IN条件。每次写完一条看起来要经常执行、数据量又不小的查询我建议先跑一下EXPLAIN看一眼执行计划这个习惯能在早期拦截掉大多数性能问题而不是等线上卡顿了再来救火。4.3 连接查询结果翻倍的坑有时候你会发现一条JOIN查询返回的行数比预期多了很多而且看似重复。这种情况十有八九是ON条件写得不完整。举一个我处理过的真实错误查“订单信息及所包含的商品明细”本意是一个订单对应一条明细表记录但漏了明细表里的一个软删除标记字段SELECT o.id, o.amount, i.goods_name FROM orders o LEFT JOIN order_item i ON o.id i.order_id;如果同一个订单在明细表里有两条记录结果当然返回两行。这不算“错”但如果在聚合场景下比如你再SUM明细表的amount金额就会翻倍。这类问题在做统计报表时尤其隐蔽因为数据看起来数量级是合理的但一和财务对账就露馅。排查思路是检查JOIN之后的行数是否等于左表的行数如果不等于把JOIN条件里补上所有应该参与匹配的维度。还有一种常见情况是“一对多连接后再连接另一个一对多”导致笛卡尔积式爆炸这种SQL通常需要重构成先聚合子查询再JOIN。4.4 分页深翻页的性能优化前面提到过深分页问题这里再补充一些具体的优化实践。深分页优化有一个很好用的手法叫“延迟关联”思路是先快速定位出需要的那一批主键再通过主键回表查询完整数据。比如SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user ORDER BY reg_time DESC LIMIT 990000, 20 ) t ON u.id t.id;这个写法的关键点在于子查询里的LIMIT虽然也带大偏移量但查询只需扫描主键列数据量小拿到20个主键后再回到主表按主键取值成本远低于一次性扫描全行数据。对于宽表字段很多的表这种优化尤其明显。再提一个思路是结合业务限制翻页深度。很多后台系统的真实使用场景中用户很少会翻到几百页之后。与其优化深分页不如在前端限制最多翻到某一页引导用户通过筛选条件缩小范围。分页的核心是“控制数据返回量”不是“让用户无限翻”业务上想明白了技术压力会小很多。4.5 SQL注入拼接字符串要命这个问题前面已经提到这里专门拎出来说是因为它的严重程度值得单独占一节。javaWeb项目里最容易出现SQL注入的位置就是动态条件查询。搜索关键字、排序字段、表名这几类位置如果处理不当就会变成攻击入口。安全的底线就一条数据值一律走预编译绑定永远不要拼进SQL字符串。在JDBC里用PreparedStatement的?占位在MyBatis里用#{}这两个机制是一样的。需要拼SQL结构和表名的极少数场景先做白名单校验。比如排序字段只允许是预定义列表里的值String[] allowedOrder {create_time, amount, status}; if (!Arrays.asList(allowedOrder).contains(orderBy)) { orderBy create_time; }有了这道白名单判断即使使用了${orderBy}也不会有注入风险因为用户无法把任意值塞进去。这是我见过最稳妥的兜底方案。4.6 问题速查表现象可能原因优先排查方向查询结果比预期多JOIN条件不全或一对多膨胀检查ON条件、使用DISTINCT验证聚合结果偏小或偏大NULL被忽略或GROUP BY逻辑不对检查字段NULL情况、SELECT字段合法性带有WHERE的LEFT JOIN丢数据过滤条件写在了WHERE而非ON把右表条件移到ON里数据量不大但查询很慢索引失效或隐式类型转换EXPLAIN看type/rows分页翻到后面越来越慢深分页偏移量过大游标分页或延迟关联查询报错列不存在WHERE中使用了SELECT别名重新写原表达式LIKE查询性能差通配符放在开头改为前缀匹配或全文索引登录接口被恶意攻击SQL注入全面改用预编译绑定这张表我挑的是日常最常踩的几条更多的问题要靠你自己去EXPLAIN里面找答案。5. 进阶扩展从DQL到企业级查询能力5.1 用EXPLAIN看懂你的查询对于想要进阶的开发者DQL的下一步不是背更复杂的语法而是学会“看穿”一条查询在数据库内部是怎么执行的。EXPLAIN是每个javaWeb开发者都应该熟练掌握的工具。EXPLAIN输出的关键字段除了前面提到的type和rows还有key实际用到的索引、possible_keys可能用到的索引、extra额外的执行信息。extra里如果出现Using filesort说明排序没能用索引需要在临时文件里排序数据量大时很慢出现Using temporary说明查询使用了临时表多半是GROUP BY或DISTINCT导致的这两个都是优化的信号。举个例子EXPLAIN SELECT u.city, COUNT(*) FROM user u GROUP BY city;如果extra里出现Using temporary说明分组操作需要临时表可以考虑在city字段上建索引来消除。这个只有通过EXPLAIN才能发现单看SQL本身是看不出来的。5.2 视图与存储过程封装复杂查询当一条DQL语句又长又复杂时javaWeb项目里有一种常见做法是把它封装成视图方便多个接口复用。比如前面提到的“每个用户的订单总额统计”可以创建成视图CREATE VIEW v_user_order_summary AS SELECT u.id, u.name, SUM(o.amount) AS total_amount FROM user u LEFT JOIN orders o ON u.id o.user_id AND o.status 1 GROUP BY u.id, u.name;之后在javaWeb里就可以把它当成一张普通表来查可以继续做WHERE、ORDER BY、分页。视图的本质是一次查询的“固化”对开发体验的提升很明显。存储过程则是把一段逻辑固化在数据库端。DQL相关的存储过程实际写起来会涉及动态SQL比如传入一个条件字符串拼SQL这需要用到预处理语句来保证安全。存储过程适合那些确实需要在数据库端完成、并且被多端调用的复杂逻辑。但在javaWeb项目里存储过程的使用要克制一是调试困难二是数据库压力过高。大部分业务逻辑我更推荐放在Java代码层控制事务SQL层只做数据读写。5.3 面试高频题自查清单学DQL的最终目的除了干活还有就是面试。下面这几个问题是面试里反复出现的你可以拿来检验自己是否真的理解到位WHERE和HAVING的区别答执行顺序不同一个过滤行一个过滤分组。INNER JOIN和LEFT JOIN的区别答连接方式不同左连接保留左表全部记录。COUNT(*)、COUNT(1)、COUNT(字段)的区别答前两个是计数行COUNT(字段)忽略NULL。如何优化一个慢查询答先EXPLAIN定位再看索引命中、查询重写、分页策略。UNION和UNION ALL的区别答UNION去重UNION ALL不去重UNION性能差一点。这些题目都不算难难的是在项目里真的遇到对应问题时有意识去用它。我面试过不少候选人SQL背得滚瓜烂熟但一问EXPLAIN有没有用过、有没有排查过慢查询就答不上来了。所以如果你是想好好走javaWeb这条路建议把DQL当成一项“工程能力”而不是“语法知识”来学每写一条查询都问自己一句这条SQL在真实数据量下能不能扛住能不能再优化一点。我个人在实际项目里的体会是DQL的进阶没有捷径就是多写、多排查、多看执行计划。那些复杂的业务报表最开始的版本往往又长又乱是后来基于EXPLAIN一行行调整、拆分才变成最终那个高效的模样的。最后再分享一个小技巧写完一条查询后习惯性先用一条带LIMIT 1的版本验证表结构和字段名是否合法再放开结果集跑完整查询能省下不少排查语法报错的时间。DQL这个技能理论上限不高但工程下限却可以很高值得花心思好好打磨。
JavaWeb开发必备：MySQL DQL查询实战与性能优化指南
搞javaWeb开发的兄弟应该都有同感一个项目能不能跑起来看框架和配置但业务能不能做得下去看的是数据这一层。而数据层里面DQLData Query Language数据查询语言又是日常开发中占了八成以上工作量的一块。我最早学javaWeb的时候Servlet、JSP、三层架构这些学得都挺顺结果一接手实际项目发现业务需求落到代码里卡点全在一条SQL写不出来。后来专门把MySQL的DQL啃了一遍再回头看那些订单列表、统计报表、条件筛选的功能思路一下子就通了。这篇文章就围绕javaWeb后端开发中最常用的MySQL DQL展开从查询语句的骨架讲起覆盖条件过滤、排序、聚合分组、多表连接、子查询、分页这些高频场景。中间会穿插我自己在项目里踩过的坑以及一些排查问题的方法。不管你是刚学到javaWeb的新手还是写了一些CRUD但总觉得SQL不够扎实的开发者这篇文章都能帮你在DQL上补一块短板。1. 内容整体设计与思路拆解1.1 为什么javaWeb学习一定要跨过DQL这道坎很多初学javaWeb的人有个误区觉得后端开发的核心是框架Spring、SpringMVC、MyBatis搭起来能跑通就完了。但实际开发里框架只是骨架真正的血肉是业务逻辑而业务逻辑落到最后基本都是对数据库的查询和计算。举个例子一个最简单的后台用户列表前端传过来一组条件用户名模糊搜索、注册时间段、用户状态、分页页码。你以为这是Java代码的遍历筛选不是。生产环境的数据量动不动几十万上百万条不可能把所有数据查出来再在内存里过滤。正确的做法是直接在SQL层面把这些条件拼进WHERE子句让数据库在最底层完成过滤只把需要的几十条数据返回给Java层。这就是DQL的意义所在。我见过不少新手写项目功能都实现了但打开慢查询日志一看一条简单的列表接口触发了好几次全表扫描数据量上了十万就卡成PPT。本质原因就是DQL基本功不扎实不会用索引、不会合理组织查询条件、不清楚join该不该用。在javaWeb的学习路线里数据库查询能力往往决定了你能在这个行业走多深。1.2 DQL在javaWeb开发中的业务场景前面提到了用户列表这里再展开说几个javaWeb项目里特别典型、特别常见的DQL场景你会发现它们其实都是同一个核心能力的变体。第一个是登录认证。你提供一个账号或手机号后端要用DQL去用户表里精确匹配一条记录再比对密码。这个查询看起来简单但涉及唯一索引的设计、条件列的正确使用以及防SQL注入的处理都属于DQL在真实业务里的细节。第二个是后台管理系统的列表页。几乎所有管理后台都是由“条件筛选排序分页”构成的比如订单列表按状态筛选、按金额排序、下拉选择时间范围。这些需求翻译成SQL就是一个带有动态条件的DQL查询。第三个是统计报表。比如电商项目要统计每天销售额、每个商品的销量排行、每个用户的消费总额。这要用到聚合函数、GROUP BY分组、HAVING筛选分组后的数据。写这种SQL时对分组逻辑的理解稍有偏差结果就会差之千里。第四个是关联数据查询。订单表关联用户表拿用户名商品表关联分类表拿分类名称这些跨表取数据的需求在javaWeb里几乎每个模块都会遇到。多表连接JOIN就是面试必问、工作必用的核心技能点。你会发现以上这些场景没有一个是靠死记硬背SQL语法能搞定的而是需要你把业务需求翻译成查询逻辑。DQL不是一条条孤立的SQL语句它是一套“把数据需求表达给数据库”的思维方式。这个东西通了后面学MyBatis的resultMap映射、动态SQL拼接都会顺理成章。2. 核心细节解析与实操要点2.1 查询语句骨架顺序即思路一条完整的DQL语句长这样SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段 HAVING 分组后的过滤条件 ORDER BY 排序字段 LIMIT 分页参数;我刚开始学的时候觉得这只是语法的排列顺序背下来就行。后来才意识到这个顺序其实也是数据库执行查询的思考顺序。理解执行顺序比背语法更重要因为它决定了你能不能用正确的方式写WHERE、GROUP BY、HAVING。MySQL执行一条查询时实际的大致顺序是先通过FROM定位到要操作的表再根据WHERE条件过滤行数据接着按GROUP BY指定的字段分组分组后若还有条件则用HAVING过滤分组然后才轮到SELECT投影出想要的列最后是ORDER BY排序和LIMIT截取条数。这里有一个非常关键的推论WHERE是在分组之前执行的所以它是用来过滤原始行的HAVING是在分组之后执行的所以它是用来过滤分组的。如果你在HAVING里写一个不涉及聚合函数、完全可以用WHERE完成的条件不仅逻辑绕性能也会打折扣。还有一点新手容易踩坑SELECT子句里定义的别名在GROUP BY和ORDER BY中通常可以使用但在WHERE中不能使用。因为执行顺序里WHERE先于SELECT别名还没有生成。你在where里写where alias 10MySQL会直接报错说字段不存在。这个细节虽然小但项目里排查报错时能帮你省不少时间。2.2 条件过滤、排序与去重WHERE子句是DQL里出现频率最高的部分核心就是各种条件的组合和运算符的使用。常用的运算符包括、!、、、、、、BETWEEN AND、IN、LIKE、IS NULL。以及逻辑运算符AND、OR、NOT。组合使用时有一个优先级问题AND的优先级高于OR。比如SELECT * FROM user WHERE status 1 OR status 2 AND age 18;这会被解析成status 1 OR (status 2 AND age 18)因为AND先执行。如果你想让OR先生效就必须加括号。这是一个面试和实际开发中都容易忽视的细节我只能说任何复杂的条件组合该加括号就加括号别指望读代码的人去猜你的优先级。LIKE模糊查询也是高频坑区。%代表任意多个字符_代表单个字符。比如要查所有姓张的用户SELECT * FROM user WHERE name LIKE 张%;但如果你写成LIKE %张%虽然能查到数据可这种以%开头的模糊查询无法走索引数据量大时性能会急剧下降。这一点放到后面索引章节再展开这里先记住结论能用前缀匹配就不要用包含匹配。去重也是DQL里容易理解偏差的操作。SELECT DISTINCT是对查询结果的完全去重也就是说只有当两条记录的所有查询列都相同才会被合并为一条。比如你想查所有用户所在的城市SELECT DISTINCT city FROM user没问题但如果你写SELECT DISTINCT city, name FROM user去重粒度变成了“城市姓名”的组合结果跟你想的就不是一回事了。ORDER BY排序则有两种形态单字段排序和多字段排序。多字段排序的规则是“先按第一个字段排字段值相同时再按第二个字段排”。比如SELECT * FROM order_list ORDER BY status ASC, create_time DESC;意思是先按状态升序排相同状态下再按创建时间降序排。这个逻辑在列表展示时非常常用比如“展示待处理订单时先按紧急程度分档档内按时间倒序”。补充一个容易出问题的点排序字段如果是字符串类型的数字那么排序会按字典序进行而不是数值序。比如“10”会排在“9”的前面因为字符1比字符9小。如果你在设计表结构时把本该是整数的字段用成了varchar后期排序就会出现这种反直觉的结果。这类问题在DQL运行不出来时往往要到表结构设计上找原因。2.3 聚合、分组与having聚合函数是DQL从“查数据”升级到“算数据”的分水岭。常用的五个聚合函数是COUNT(*), COUNT(字段), SUM(字段), AVG(字段), MAX(字段), MIN(字段)先讲一个我印象很深的坑COUNT(*)和COUNT(字段)的结果可能不一样。COUNT(*)统计的是行数不管这一行的其他字段是不是NULL而COUNT(字段)统计的是该字段非NULL的记录数。举个例子订单表里有100条记录其中50条的remark字段是NULL。COUNT(*)是100COUNT(remark)是50。你在写统计接口时如果没想清楚到底要数什么很容易在数据校验阶段被打回。SUM、AVG、MAX、MIN这些函数都会自动忽略NULL值。这在大多数情况下是好事比如AVG计算平均分时NULL的缺考记录不会被当成0分拉低平均值。但反过来想如果你期望把NULL当成0来计算聚合结果就会和预期不符。遇到这种情况可以用IFNULL(字段, 0)来先把NULL替换成0再做聚合。GROUP BY是配合聚合函数使用的核心。它的逻辑是按指定字段把记录分成若干个组然后对每个组分别做聚合计算。写GROUP BY时有一个非常经典的问题查询列中出现了既不在GROUP BY里也不在聚合函数里的字段。比如SELECT name, city, COUNT(*) FROM user GROUP BY city;在MySQL的某些版本和配置下主要是5.7之前的版本或者关闭了ONLY_FULL_GROUP_BY模式这条SQL能跑但返回的name是每组里随机挑出来的没有任何意义。而在开启了ONLY_FULL_GROUP_BY模式的MySQL 5.7及以上版本中这种SQL会直接报错。所以我的建议是从一开始就养成好习惯SELECT的字段要么在GROUP BY里出现要么被聚合函数包裹。一旦违反这个规则结果就是“能跑但不对”或者“直接报错”在项目里都是很被动的。HAVING和WHERE的区别前面提过这里用一个直观的例子收尾你要统计下单次数超过5次的用户。先WHERE过滤掉无效订单再GROUP BY user_id分组统计下单次数最后HAVING COUNT(*) 5过滤分组。整个过程“先过滤行、再分组、再过滤组”逻辑清晰也符合执行顺序。SELECT user_id, COUNT(*) AS cnt FROM order_list WHERE status 1 GROUP BY user_id HAVING cnt 5;注意这里的cnt是SELECT里定义的别名在HAVING中可以使用因为HAVING的执行顺序在SELECT投影之后。这个细节和WHERE不能用别名形成了鲜明对比理解执行顺序后就不会记混了。2.4 多表连接join的正确打开方式实际javaWeb项目里几乎没有哪张业务表能独立支撑起一个完整页面。订单要关联用户表拿买家昵称商品要关联分类表拿分类名权限要关联角色表、菜单表。多表连接查询是DQL里最重要也最容易出错的部分。先理解JOIN的本质它是把两张表按一定的匹配条件“拼接”成一张虚拟表然后你在这张虚拟表上进行查询。根据拼接方式的不同主要分三种INNER JOIN只返回两边都匹配上的记录LEFT JOIN返回左表全部记录右表没有匹配的就补NULLRIGHT JOIN返回右表全部记录左表没有匹配的就补NULL实际工作里LEFT JOIN用得最多因为它能保证左表的记录不丢。比如查询所有用户及其订单数量哪怕某用户一单都没下你希望结果里还保留这个用户数量显示为0这就必须用LEFT JOIN。ON和WHERE的区别是另一个高频坑。ON在连接阶段指定两张表的匹配条件而WHERE在连接完成之后对结果做过滤。对INNER JOIN来说把过滤条件写在ON和WHERE里结果一样但对LEFT JOIN来说两者有本质区别。这里我踩过实打实的坑。当时写一个报表查询要求“查所有客户及其订单金额”但只需要已支付订单的金额。我一开始把支付状态写进了WHERESELECT c.id, c.name, SUM(o.amount) FROM customer c LEFT JOIN orders o ON c.id o.customer_id WHERE o.status paid GROUP BY c.id, c.name;结果本来应该保留未下单客户但因为WHERE把右表匹配不到产生的NULL行全过滤掉了未下单客户全没了。正确的写法是把支付状态作为ON条件的一部分SELECT c.id, c.name, SUM(o.amount) FROM customer c LEFT JOIN orders o ON c.id o.customer_id AND o.status paid GROUP BY c.id, c.name;这样左表记录全保留未下单客户虽然o.status是NULL但行还在恰好满足了需求。本质上ON决定“怎么拼”WHERE决定“拼完之后要哪些”。这个区别理解了LEFT JOIN就算真正入门了。还有一个常见错误JOIN时漏写ON条件或者ON条件写错了。两张表一旦缺少连接条件就会做笛卡尔积结果行数是两表行数相乘。比如10万用户表连接5万订单表且没有ON结果会是50亿行数据库直接卡死。我排查过不少“数据库CPU突然飙满”的问题最后发现就是有人在测试环境写了一条没带ON的JOIN。多对多关系也是JOIN的经典场景。典型的如用户和角色中间用一个关联表user_role来维护关系。查询用户及其角色名通常需要两次JOINSELECT u.name, r.role_name FROM user u LEFT JOIN user_role ur ON u.id ur.user_id LEFT JOIN role r ON ur.role_id r.id;这个模式在javaWeb的权限管理模块里非常常见。理解JOIN的本质之后你会发现这种“链式拼接”其实很直观。2.5 子查询与派生表子查询就是嵌套在另一个查询里的查询。根据返回结果的不同可以分为标量子查询返回单行单列、列子查询返回单列多行、行子查询返回单行多列和表子查询返回多行多列。标量子查询最简单也是最常用的。比如要查询“下单金额最高的用户信息”SELECT * FROM user WHERE id (SELECT user_id FROM orders ORDER BY amount DESC LIMIT 1);这里子查询返回一个值然后外层查询用它做条件。注意子查询的结果必须是单行单列否则会报“Subquery returns more than 1 row”的错误。列子查询通常配合IN使用。比如查“购买过某个分类商品的用户”SELECT * FROM user WHERE id IN ( SELECT DISTINCT user_id FROM orders WHERE goods_category 数码 );这类查询里有一个存在已久的争论IN和EXISTS哪个快。简单记忆法是小表驱动大表原则如果外层表数据量小用IN更直接如果子查询返回的结果集小而外层表很大用EXISTS可能更好。不过MySQL的优化器对IN子查询有半连接优化实际性能差距在多数场景下并不像老博客说的那么玄乎真正要关注的是有没有走索引。表子查询的典型用法是把子查询结果当作一张“派生表”来查询。比如先查出每个用户的订单总额再从这个结果里筛出总额大于10000的用户SELECT user_id, total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) AS t WHERE total_amount 10000;这种写法在逻辑上很好理解先“算中间结果”再“从中间结果里筛选”。在MySQL里派生表会被物化或合并优化具体执行计划要看版本和数据量但作为书写习惯它能让复杂需求拆解成清晰的步骤。子查询还有一个用途是关联子查询也就是子查询里引用外层查询的字段。经典的例子是查“每个分类下价格最高的商品”SELECT * FROM goods g WHERE price ( SELECT MAX(price) FROM goods WHERE category_id g.category_id );这种查询的思考方式有点像“对每一条外层记录去内层找最值”逻辑上很自然但性能要小心因为每一行都可能触发一次子查询执行。实际项目里如果数据量大这类SQL很多时候会被改写为JOIN与窗口函数组合这涉及到DQL进阶层面的优化了。窗口函数比如ROW_NUMBER() OVER (PARTITION BY ...)在MySQL 8.0中正式支持解决“分组内取TopN”这类问题比子查询优雅得多。如果你用的是MySQL 8.0及以上版本建议学DQL的同时把窗口函数的基础用法过一遍很多复杂的查询会变得异常简单。2.6 分页查询的公式与深坑分页查询是javaWeb项目里绕不开的话题几乎所有列表接口都要做分页。MySQL的分页靠LIMIT实现两种写法等价SELECT * FROM order_list LIMIT 20, 10; SELECT * FROM order_list LIMIT 10 OFFSET 20;第一种写法逗号前面是偏移量后面是返回条数第二种写法从语义上更好理解返回10条跳过20条。我习惯用第二种因为阅读时更清楚。分页查询有个通用公式假设每页条数为pageSize请求的是第pageNum页那么偏移量就是(pageNum - 1) * pageSize。比如每页10条第1页就是LIMIT 0,10第2页就是LIMIT 10,10。在javaWeb的接口里pageNum和pageSize通常由前端传入后端在做一层参数校验后拼进SQL。分页最深的坑是深分页问题。当你查第100000页的时候LIMIT偏移量是999990MySQL仍然需要先扫描前999990条记录然后全部丢弃再取后面的数据。随着页码越翻越深查询耗时越来越高这在后台报表系统里尤其明显。我处理过的一个实际案例一张订单表有两百万条数据管理后台的分页查询在翻到几百页之后接口耗时从几十毫秒飙升到四五秒。排查后发现就是典型的深分页。当时的优化方案有两个一是前端加了筛选条件把翻页深度控制在合理范围内二是后端改成了“基于游标”的分页思路不是用页码跳转而是记录上一页最后一条订单的id查询时用WHERE id 上一页最大id LIMIT pageSize。第二种方式在逻辑上同样实现“下一页”效果但利用了主键索引的范围扫描性能稳定不会越翻越慢。当然它牺牲了“任意页码跳转”的能力不是所有场景都适用但对于瀑布流或“加载更多”类交互游标分页是更优解。作为DQL学习者理解这两种分页思路的差异和适用场景是进阶必须过的一关。3. 实操过程与核心环节实现3.1 场景建模三张表搭一个订单统计需求理论知识说了一堆不如动手过一遍。这里打算用一个贴近真实业务的场景把前面提到的DQL知识点串起来。假设我们在开发一个电商管理系统的后台需要实现以下三个查询需求。为了方便演示先建三张简化版业务表用户表、订单表、订单明细表。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, city VARCHAR(20), reg_time DATETIME ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2), status TINYINT COMMENT 1-已支付 0-未支付, create_time DATETIME ); CREATE TABLE order_item ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, goods_name VARCHAR(100), price DECIMAL(10,2), quantity INT );这三张表的关系很清晰用户一对多订单订单一对多明细。实际项目里表结构比这复杂得多但用来练习DQL正好够。3.2 从业务需求到SQL完整推导过程需求一查询注册时间在最近30天内的用户列表按注册时间倒序排列分页展示。这个需求最直接涉及WHERE时间条件、ORDER BY、LIMIT。注意时间的边界处理一般用闭区间比较稳妥SELECT id, name, city, reg_time FROM user WHERE reg_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY reg_time DESC LIMIT 20 OFFSET 0;需求二统计每个城市已支付订单的总金额筛选出总金额大于5000元的城市按金额从高到低排序。这是一个典型的“聚合分组HAVING排序”组合需求。分析过程是先关联用户表和订单表再通过WHERE过滤已支付订单接着按城市分组计算总金额用HAVING过滤分组最后排序SELECT u.city, SUM(o.amount) AS total_amount FROM user u INNER JOIN orders o ON u.id o.user_id WHERE o.status 1 GROUP BY u.city HAVING total_amount 5000 ORDER BY total_amount DESC;这里有个小细节HAVING里可以直接用别名total_amount但如果你为了兼容性考虑也可以写成HAVING SUM(o.amount) 5000结果一致。需求三查询每个商品的销售总量和销售总额并按销售总额降序取前10。这个需求涉及订单明细表用了SUM和GROUP BY并做ORDER BY LIMITSELECT goods_name, SUM(quantity) AS total_qty, SUM(price * quantity) AS total_sales FROM order_item GROUP BY goods_name ORDER BY total_sales DESC LIMIT 10;需求四查出每个用户最近一单的支付金额。这个稍微复杂可以用关联子查询实现。先找出每个用户最近的订单时间再关联订单表取金额SELECT u.id, u.name, o.amount AS last_paid_amount FROM user u LEFT JOIN orders o ON o.user_id u.id AND o.create_time ( SELECT MAX(create_time) FROM orders WHERE user_id u.id AND status 1 );这里如果用户没有已支付订单LEFT JOIN保证用户仍会出现amount为NULL。如果用户有两单在同一时间创建查询可能返回多行但作为示例已经足够说明关联子查询的用法。这四个需求把DQL的主要知识点全部覆盖了条件过滤、排序、聚合、分组、HAVING、连接、子查询、分页。你在实际项目里遇到的大多数查询基本都能拆解成这些基础块的组合。3.3 在javaWeb项目中落地JDBC与MyBatis两种姿势SQL写出来了接下来关键的一步是把它接进javaWeb项目。这涉及一个非常重要的原则SQL应该以参数化的方式传给数据库而不是靠拼接字符串。先看JDBC时代的正确写法。PreparedStatement是预编译的它在数据库端先完成SQL模板的编译然后每次执行只绑定参数值既提高了执行效率也从根上避免了SQL注入。String sql SELECT id, name, city, reg_time FROM user WHERE city ? ORDER BY reg_time DESC LIMIT ?, ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, city); ps.setInt(2, offset); ps.setInt(3, pageSize); ResultSet rs ps.executeQuery();这里的?占位符会在数据库端被转义处理传入的参数无论内容多特殊都只会被当作字面量不会被拼进SQL结构里。反过来说如果你图省事用字符串拼接String sql SELECT * FROM user WHERE city city ;一旦city的内容是 OR 11整个查询条件就会被改写这就是SQL注入的根源。我见过不止一个新手项目这么写过安全测试一抓一个准。在Spring Boot项目里MyBatis是更主流的方案。MyBatis的#{}占位符就相当于JDBC的PreparedStatement参数绑定而${}是字符串拼接。只要能用#{}就绝不碰${}除非那个位置确实需要动态拼表名或排序字段且你做了严格的白名单校验。select idselectUserPage resultTypeUser SELECT id, name, city, reg_time FROM user where if testcity ! null and city ! AND city #{city} /if /where ORDER BY reg_time DESC LIMIT #{offset}, #{pageSize} /selectMyBatis的动态SQL标签if、where、foreach本质上是帮你按条件拼SQL但底层执行时用的还是预编译绑定。你需要明白一点动态SQL拼的是“SQL的结构”而#{}绑定的是“数据值”两者不冲突。但如果把数据值用${}拼进SQL结构里就出大问题了。还有一个小细节LIMIT的参数在MyBatis里建议用#{}传参比如上面的offset和pageSizeMySQL的LIMIT支持预编译参数不会影响执行计划。有人习惯写成${}这是没必要的反而引入了注入风险。4. 常见问题与排查技巧实录4.1 NULL值引发的怀疑人生NULL是DQL里最阴间的存在没有之一。它既不等于任何值也不不等于任何值。你用WHERE name NULL查不到任何记录因为正确写法是IS NULL。聚合函数对这一块的规则前面提过SUM、AVG、MAX、MIN自动忽略NULLCOUNT(字段)只统计非NULL行。我在实际项目中遇到最迷惑的问题是统计用户性别分布时有一部分用户性别字段为NULL然后按性别分组会单独出现一个NULL组页面显示成“空字符串”运营同学来问这是哪来的数据。解决办法是把NULL先映射成“未知”SELECT IFNULL(gender, 未知) AS gender, COUNT(*) FROM user GROUP BY IFNULL(gender, 未知);还有一个容易出错的场景是NULL参与算术运算。在MySQL里NULL加任何数还是NULL。比如订单表有个优惠金额字段你计算实际支付金额写amount - discount只要discount是NULL结果就是NULL而不是amount本身。这时候必须用IFNULL兜底SELECT amount - IFNULL(discount, 0) AS real_amount FROM orders;这个坑在财务计算里非常致命我调试过整整一下午才发现是NULL在作怪。4.2 索引失效写了查询却慢如蜗牛很多DQL问题不是“查不出来”而是“查得特别慢”。排查时第一反应永远是看SQL有没有走索引。用EXPLAIN关键字可以查看一条SQL的执行计划EXPLAIN SELECT * FROM user WHERE name 张三;重点看type列和rows列。type从好到差依次是const、eq_ref、ref、range、index、ALL。见到ALL就说明是全表扫描数据量大时性能堪忧rows列则是预估扫描行数数字越大越危险。以下几类我踩过的索引失效场景值得记在小本子上第一对索引列使用函数。比如WHERE DATE(create_time) 2024-01-01即使create_time有索引也走不了。正确做法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二LIKE以通配符开头。LIKE %张%无法走索引LIKE 张%可以。实在需要模糊包含搜索可以考虑全文索引或者借助搜索引擎中间件。第三隐式类型转换。索引列是varchar查询时传了数值MySQL会把varchar列转成数值再比较导致索引失效。比如WHERE phone 13800138000phone是varchar这里即使有索引也用不上。正确写法是WHERE phone 13800138000。第四使用OR连接多个条件其中只有一部分列有索引。这种情况下优化器可能放弃索引改用全表扫描。解决办法是把OR拆成两个查询后用UNION ALL合并或者改成IN条件。每次写完一条看起来要经常执行、数据量又不小的查询我建议先跑一下EXPLAIN看一眼执行计划这个习惯能在早期拦截掉大多数性能问题而不是等线上卡顿了再来救火。4.3 连接查询结果翻倍的坑有时候你会发现一条JOIN查询返回的行数比预期多了很多而且看似重复。这种情况十有八九是ON条件写得不完整。举一个我处理过的真实错误查“订单信息及所包含的商品明细”本意是一个订单对应一条明细表记录但漏了明细表里的一个软删除标记字段SELECT o.id, o.amount, i.goods_name FROM orders o LEFT JOIN order_item i ON o.id i.order_id;如果同一个订单在明细表里有两条记录结果当然返回两行。这不算“错”但如果在聚合场景下比如你再SUM明细表的amount金额就会翻倍。这类问题在做统计报表时尤其隐蔽因为数据看起来数量级是合理的但一和财务对账就露馅。排查思路是检查JOIN之后的行数是否等于左表的行数如果不等于把JOIN条件里补上所有应该参与匹配的维度。还有一种常见情况是“一对多连接后再连接另一个一对多”导致笛卡尔积式爆炸这种SQL通常需要重构成先聚合子查询再JOIN。4.4 分页深翻页的性能优化前面提到过深分页问题这里再补充一些具体的优化实践。深分页优化有一个很好用的手法叫“延迟关联”思路是先快速定位出需要的那一批主键再通过主键回表查询完整数据。比如SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user ORDER BY reg_time DESC LIMIT 990000, 20 ) t ON u.id t.id;这个写法的关键点在于子查询里的LIMIT虽然也带大偏移量但查询只需扫描主键列数据量小拿到20个主键后再回到主表按主键取值成本远低于一次性扫描全行数据。对于宽表字段很多的表这种优化尤其明显。再提一个思路是结合业务限制翻页深度。很多后台系统的真实使用场景中用户很少会翻到几百页之后。与其优化深分页不如在前端限制最多翻到某一页引导用户通过筛选条件缩小范围。分页的核心是“控制数据返回量”不是“让用户无限翻”业务上想明白了技术压力会小很多。4.5 SQL注入拼接字符串要命这个问题前面已经提到这里专门拎出来说是因为它的严重程度值得单独占一节。javaWeb项目里最容易出现SQL注入的位置就是动态条件查询。搜索关键字、排序字段、表名这几类位置如果处理不当就会变成攻击入口。安全的底线就一条数据值一律走预编译绑定永远不要拼进SQL字符串。在JDBC里用PreparedStatement的?占位在MyBatis里用#{}这两个机制是一样的。需要拼SQL结构和表名的极少数场景先做白名单校验。比如排序字段只允许是预定义列表里的值String[] allowedOrder {create_time, amount, status}; if (!Arrays.asList(allowedOrder).contains(orderBy)) { orderBy create_time; }有了这道白名单判断即使使用了${orderBy}也不会有注入风险因为用户无法把任意值塞进去。这是我见过最稳妥的兜底方案。4.6 问题速查表现象可能原因优先排查方向查询结果比预期多JOIN条件不全或一对多膨胀检查ON条件、使用DISTINCT验证聚合结果偏小或偏大NULL被忽略或GROUP BY逻辑不对检查字段NULL情况、SELECT字段合法性带有WHERE的LEFT JOIN丢数据过滤条件写在了WHERE而非ON把右表条件移到ON里数据量不大但查询很慢索引失效或隐式类型转换EXPLAIN看type/rows分页翻到后面越来越慢深分页偏移量过大游标分页或延迟关联查询报错列不存在WHERE中使用了SELECT别名重新写原表达式LIKE查询性能差通配符放在开头改为前缀匹配或全文索引登录接口被恶意攻击SQL注入全面改用预编译绑定这张表我挑的是日常最常踩的几条更多的问题要靠你自己去EXPLAIN里面找答案。5. 进阶扩展从DQL到企业级查询能力5.1 用EXPLAIN看懂你的查询对于想要进阶的开发者DQL的下一步不是背更复杂的语法而是学会“看穿”一条查询在数据库内部是怎么执行的。EXPLAIN是每个javaWeb开发者都应该熟练掌握的工具。EXPLAIN输出的关键字段除了前面提到的type和rows还有key实际用到的索引、possible_keys可能用到的索引、extra额外的执行信息。extra里如果出现Using filesort说明排序没能用索引需要在临时文件里排序数据量大时很慢出现Using temporary说明查询使用了临时表多半是GROUP BY或DISTINCT导致的这两个都是优化的信号。举个例子EXPLAIN SELECT u.city, COUNT(*) FROM user u GROUP BY city;如果extra里出现Using temporary说明分组操作需要临时表可以考虑在city字段上建索引来消除。这个只有通过EXPLAIN才能发现单看SQL本身是看不出来的。5.2 视图与存储过程封装复杂查询当一条DQL语句又长又复杂时javaWeb项目里有一种常见做法是把它封装成视图方便多个接口复用。比如前面提到的“每个用户的订单总额统计”可以创建成视图CREATE VIEW v_user_order_summary AS SELECT u.id, u.name, SUM(o.amount) AS total_amount FROM user u LEFT JOIN orders o ON u.id o.user_id AND o.status 1 GROUP BY u.id, u.name;之后在javaWeb里就可以把它当成一张普通表来查可以继续做WHERE、ORDER BY、分页。视图的本质是一次查询的“固化”对开发体验的提升很明显。存储过程则是把一段逻辑固化在数据库端。DQL相关的存储过程实际写起来会涉及动态SQL比如传入一个条件字符串拼SQL这需要用到预处理语句来保证安全。存储过程适合那些确实需要在数据库端完成、并且被多端调用的复杂逻辑。但在javaWeb项目里存储过程的使用要克制一是调试困难二是数据库压力过高。大部分业务逻辑我更推荐放在Java代码层控制事务SQL层只做数据读写。5.3 面试高频题自查清单学DQL的最终目的除了干活还有就是面试。下面这几个问题是面试里反复出现的你可以拿来检验自己是否真的理解到位WHERE和HAVING的区别答执行顺序不同一个过滤行一个过滤分组。INNER JOIN和LEFT JOIN的区别答连接方式不同左连接保留左表全部记录。COUNT(*)、COUNT(1)、COUNT(字段)的区别答前两个是计数行COUNT(字段)忽略NULL。如何优化一个慢查询答先EXPLAIN定位再看索引命中、查询重写、分页策略。UNION和UNION ALL的区别答UNION去重UNION ALL不去重UNION性能差一点。这些题目都不算难难的是在项目里真的遇到对应问题时有意识去用它。我面试过不少候选人SQL背得滚瓜烂熟但一问EXPLAIN有没有用过、有没有排查过慢查询就答不上来了。所以如果你是想好好走javaWeb这条路建议把DQL当成一项“工程能力”而不是“语法知识”来学每写一条查询都问自己一句这条SQL在真实数据量下能不能扛住能不能再优化一点。我个人在实际项目里的体会是DQL的进阶没有捷径就是多写、多排查、多看执行计划。那些复杂的业务报表最开始的版本往往又长又乱是后来基于EXPLAIN一行行调整、拆分才变成最终那个高效的模样的。最后再分享一个小技巧写完一条查询后习惯性先用一条带LIMIT 1的版本验证表结构和字段名是否合法再放开结果集跑完整查询能省下不少排查语法报错的时间。DQL这个技能理论上限不高但工程下限却可以很高值得花心思好好打磨。
