前阵子带一个转行的新人做课程设计功能全写完了跑到数据量稍微大一点的测试环境翻页接口稳定在3秒以上。我帮他把SQL捞出来一看典型的SELECT *加全表扫描加无索引ORDER BY。这种事我在工作里见过太多次了。JavaWeb学到一定阶段很多人以为自己卡在框架上实际上卡在MySQL的DQL上。数据查询语言DQL就是日常开发里最常用的那部分SQL——SELECT查询别小看它一个JavaWeb项目里70%以上的业务逻辑最终落地的形式都是查询语句。这篇文章我不会讲太多虚的直接按我自己的学习路线来环境怎么避坑、DQL核心语法怎么理解、进阶查询怎么落地、索引和调优怎么上手最后怎么把查询从Java代码里正确地发出去。适合刚学完Java基础、准备进军JavaWeb的读者也适合那些SQL能写出来但总被同事说慢的朋友。1. 从项目实战反推JavaWeb开发里DQL到底占多大比重1.1 一个完整JavaWeb项目中的数据操作清单很多新手学JavaWeb时有个错觉觉得数据库操作就是增删改查四件事各占四分之一。真到项目里你会发现完全不是这么回事。拿最常见的图书管理系统来说我随手列一下真实会写的查询语句登录校验SELECT id, username, password FROM t_user WHERE username ? AND status 1图书分页列表SELECT id, title, category_id, price FROM t_book WHERE status 1 ORDER BY create_time DESC LIMIT ?, ?查询列表总条数SELECT COUNT(*) FROM t_book WHERE category_id ?关键字搜索SELECT ... WHERE title LIKE CONCAT(%, ?, %)按分类统计库存SELECT category_id, COUNT(*), SUM(stock) FROM t_book GROUP BY category_id查询最新上架的几本书SELECT ... ORDER BY create_time DESC LIMIT 8关联查作者和出版社SELECT ... FROM t_book b JOIN t_author a ON b.author_id a.id看到没有增删改的操作模式相对固定写一次就完了。但查询语句会随着业务场景千变万化——今天要多一个筛选条件明天要加一个统计维度后天要调整排序规则。可以说在JavaWeb日常开发里你花在写SELECT上的时间远远超过其他任何SQL语句。这就是为什么我把DQL单独拎出来当作从入门到进阶的一整条学习线。1.2 为什么很多新手卡在会写SQL但写不好SQL我带过的新人里几乎没有谁是完全不会写SELECT的但真正能写好的人少之又少。典型的问题我总结就三类。第一类是语法对但逻辑不对。比如分页查总数有人会把列表数据查出来后在Java里list.size()数据量小没事数据量一大直接拖垮接口。第二类是逻辑对但性能不对。SELECT *、无条件全表扫描、在索引列上套函数、OR连接两个不同字段这些写法在测试环境跑得飞快上线后被用户一打就现原形。第三类更隐蔽是不知道查询结果怎么和Java代码高效对接。MyBatis里默认一条条查导致N1问题分页查询每次都要重新COUNT这些坑几乎每个项目都会遇到。所以这篇文章的学习路径不是认识SELECT语法而是按真实项目的使用频率和踩坑概率来组织内容。你要知道一条查询从发出去到结果返回MySQL内部是按什么顺序处理的你要会看EXPLAIN执行计划来证明一条SQL为什么慢你要能在JavaWeb的代码层面用正确的姿势把DQL发出去。这三件事打通了才叫真正的进阶。2. 环境准备阶段的高频坑MySQL安装、Navicat连接与字符集2.1 MySQL 8在Windows和Linux下安装的差异很多人第一步就倒在安装上。MySQL 8和5.7的安装体验差别不小最典型的就是默认密码插件改成了caching_sha2_password后面Navicat连不上、JDBC报SSL错误十有八九都和这个有关。Windows环境下我建议直接去官网下载MySQL Installer选择Server only一路下一步就行。需要注意两点一是安装过程中会让你设root密码别随手按个回车后面改起来麻烦二是装完后建议顺手把MySQL注册成Windows服务命令行里执行net start mysql能启停。如果你遇到net start mysql 服务无法启动大概率是三种情况my.ini里配置的路径不对、data目录没初始化、3306端口被占用。处理方式是先看错误日志Windows下日志一般在MySQL安装目录的data文件夹里把日志翻出来看比瞎猜快得多。Linux环境略微麻烦一点尤其是内网离线安装的场景。我自己的习惯顺序是这样先检查系统自带的MariaDB是否冲突rpm -qa | grep mariadb有的话先卸载然后下载对应的MySQL Community RPM包用rpm -ivh按顺序安装启动服务后第一时间去拿初始密码grep temporary password /var/log/mysqld.log拿到初始密码后立刻登录并修改密码因为MySQL 8默认开了密码强度校验你设的密码必须包含大小写字母、数字和特殊字符否则会提示不满足策略。这一步经常有人卡住其实如果真的不想用高强度密码可以先在my.cnf里临时关掉validate_password插件改完密码再开回来。顺带提一个容易混淆的错误码。有个很常见的报错mysql e0434352看起来像是MySQL的错误实际上这是Windows系统的.NET运行时异常代码通常出现在Navicat或其他GUI工具启动崩溃的时候跟MySQL服务本身没关系。遇到这个优先检查.NET Framework和VC运行库是否完整重装一下运行库基本能解决。2.2 Navicat连接报错与密码插件的关系环境装好了接下来就是用什么工具连数据库。Navicat是很多人首选但连MySQL 8时经常会报Authentication plugin caching_sha2_password cannot be loaded这就是我前面说的密码插件不兼容。旧版Navicat只认识mysql_native_password而MySQL 8默认用的是新插件。临时解法是把用户的密码插件改回去ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码; FLUSH PRIVILEGES;但我要提醒一句这只是兼容旧客户端的临时手段mysql_native_password毕竟是老插件从安全角度讲我不建议生产环境做这个降级。更好的做法是升级你的数据库客户端新版Navicat、DBeaver、IDEA自带的数据库工具都已经支持caching_sha2_password了。你要是懒得折腾用DBeaver也行遇到驱动问题时手动下载对应版本的MySQL JDBC驱动放进去就能识别。连接时的SSL报错也经常有人问。报SSL connection error通常是因为连接串里没有正确配置SSL参数解决方式是在JDBC连接串后面加上useSSLfalse和allowPublicKeyRetrievaltrue这个我会在第六章讲JavaWeb对接时详细展开。2.3 字符集与排序规则对查询结果的影响字符集看起来和DQL没什么关系但真到排序和比较的时候就会冒出各种诡异问题。建库时我强烈建议直接用utf8mb4而不是老的utf8。原因很简单utf8在MySQL里最多支持3字节存不了emoji和一些生僻字而utf8mb4才是真正完整的UTF-8编码。中文在两种字符集下都能存但为了兼容性和未来扩展直接上utf8mb4是最省心的选择。CREATE DATABASE javaweb_demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;排序规则同样影响DQL的执行结果。utf8mb4_general_ci和utf8mb4_unicode_ci都不区分大小写后者在排序准确性上更好一点。如果你需要区分大小写就选utf8mb4_bin或_cs结尾的规则。这里有个很实际的经验中文按拼音排序在老版本MySQL里是个老大难问题直接ORDER BY name出来的顺序往往不是你想要的。一个常用技巧是转成GBK再排序SELECT name FROM t_user ORDER BY CONVERT(name USING gbk);这种写法在需要按中文拼音首字母排序的场景下非常好使前提是字段本身是中文。理解了字符集和排序规则你才明白为什么同样的ORDER BY在别人库上顺序正常在你库上就乱套——先查一下库的collation八成是这里出了问题。3. DQL核心语法拆解SELECT执行顺序与多表关联的底层逻辑3.1 书写顺序和执行顺序为什么经常被忽略我在面试时最爱问一个问题SELECT语句里各子句的执行顺序是什么能答对的人不超过三成。你写的时候确实是按SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT这个顺序写的但MySQL执行的时候是倒着来的。SELECT dept_id, COUNT(*) AS cnt FROM t_employee WHERE status 1 GROUP BY dept_id HAVING COUNT(*) 5 ORDER BY cnt DESC LIMIT 10;这条SQL真实的执行顺序是先FROM确定从哪张表取数然后WHERE把符合条件的行筛选出来接着GROUP BY分组再HAVING过滤分组后的结果然后才轮到SELECT计算表达式和别名最后ORDER BY排序、LIMIT截取。理解这个顺序后很多为什么这么写报错的问题就迎刃而解了。最典型的就是WHERE子句里不能用SELECT里的别名。比如WHERE cnt 5直接报错因为WHERE执行时cnt这个别名还不存在。而HAVING和ORDER BY在MySQL里可以用别名因为它们执行在SELECT之后。你可以把整个执行过程想象成一条流水线一张表的数据先经过漏斗逐层过滤最后才进入加工车间生成最终结果集。搞懂这个顺序你写复杂查询时就能预判每条语句的语义。3.2 WHERE过滤中容易踩的运算符地雷WHERE是DQL里最常用也最容易踩坑的地方。先说LIKE模糊匹配。LIKE abc%这种前缀匹配是可以走索引的但LIKE %abc和LIKE %abc%因为前面的百分号导致无法确定起始位置索引直接失效。所以搜索功能里如果要模糊查询尽量用CONCAT拼出前缀匹配的形式或者干脆引入全文检索。再说NOT IN和NULL这个组合坑非常深。当NOT IN的列表里包含NULL时整个查询结果会是空集。原因在于NOT IN本质上是多个!条件的AND组合而NULL参与比较时结果是UNKNOWN最终所有行都被过滤掉了。这个坑在很多老项目里出现过排查起来极其隐蔽。安全的写法是用NOT EXISTS替代SELECT id FROM t_user u WHERE NOT EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.id );还有一个容易忽略的点是隐式类型转换。WHERE phone 18812345678如果phone字段是varchar类型MySQL会把字段转换成数字再比较一旦对字段做转换索引就失效了。检查方式是对SQL执行SHOW WARNINGS看有没有出现Converting之类的提示。日期处理也一样字段是日期类型就直接用STR_TO_DATE(2024-11-01, %Y-%m-%d)转成日期再比较而不是把日期转成字符串否则索引照样失效。3.3 JOIN内连接与左连接的选择逻辑说到多表查询JOIN是绕不开的主题。很多人会用INNER JOIN和LEFT JOIN但说不清楚区别的本质。INNER JOIN取的是两个表的交集只返回能匹配上的行LEFT JOIN是以左表为基准左表所有行都返回匹配不上的右表字段填NULL。实际开发里有一个非常隐蔽的坑就是LEFT JOIN的过滤条件到底放在ON还是WHERE里。看下面这两条SQL-- 查所有用户及其订单且订单金额大于100 SELECT u.id, u.name, o.id AS order_id FROM t_user u LEFT JOIN t_order o ON o.user_id u.id AND o.amount 100; -- 同样目的但条件在WHERE里 SELECT u.id, u.name, o.id AS order_id FROM t_user u LEFT JOIN t_order o ON o.user_id u.id WHERE o.amount 100;第一条SQL会返回所有用户没有满足条件订单的用户订单字段显示NULL。第二条SQL因为WHERE是对连接后的结果做过滤所以没有满足条件订单的用户整行都被过滤掉了相当于把LEFT JOIN降级成了INNER JOIN。我在代码评审里见过太多次这个错误都是因为没理解ON和WHERE的执行时机。记住一个口诀ON是在连接时决定哪些行匹配WHERE是在连接完成后决定哪些行保留。关于RIGHT JOIN我建议能不用就不用。它和LEFT JOIN完全对称但日常阅读习惯都是从左往右看用RIGHT JOIN会加大理解成本。需要右表全量数据时把两表顺序调换一下改成LEFT JOIN可读性会好很多。3.4 GROUP BY分组统计的四个细节GROUP BY是DQL从查数据走向统计的分水岭。这里我不讲基础语法直接说四个容易出问题的地方。第一个是COUNT(*)、COUNT(字段)和COUNT(DISTINCT 字段)的区别。COUNT(*)统计行数COUNT(字段)只统计该字段不为NULL的行COUNT(DISTINCT 字段)统计去重后的非NULL值个数。很多人以为它们一样实际上统计口径不同结果可能差很多。第二个是HAVING和WHERE的分工。WHERE在分组之前过滤行HAVING在分组之后过滤组。如果你是想先删掉部分记录再分组统计条件放WHERE如果是分组后只保留满足条件的组条件放HAVING。把WHERE能干的活放到HAVING里性能会变差因为分组已经发生了。第三个是ONLY_FULL_GROUP_BY模式。MySQL 5.7以后默认开启这个SQL模式SELECT列表里的非聚合字段必须出现在GROUP BY里否则直接报错。这个约束看着烦实际上是帮你避免错误如果你GROUP BY category_id那SELECT里就只能出现category_id和聚合函数其他字段的值在组内并不唯一取出来也是随机的。第四个是GROUP_CONCAT这个实用函数能把组内的值拼成一个字符串。我经常用它来查每个分类下的商品名称列表SELECT category_id, COUNT(*) AS total, SUM(stock) AS stock_sum, GROUP_CONCAT(name ORDER BY id SEPARATOR 、) AS names FROM t_product GROUP BY category_id HAVING total 3 ORDER BY total DESC;这条SQL里同时出现了聚合统计、分组过滤和排序算是把这一节的知识点串起来了。4. 进阶查询能力子查询、窗口函数与分页排序4.1 子查询的三种使用位置子查询就是嵌套在查询里的查询按出现位置可以分为三种。第一种在WHERE里结果作为过滤条件。这里又分标量子查询返回单个值、IN子查询和EXISTS子查询。比如查下单过的用户用IN的写法是把子查询结果集缓存下来再比较用EXISTS的写法是逐行判断有没有匹配记录。大表场景下如果子查询的结果集很大EXISTS通常比IN更高效反过来子查询结果集很小而外层表很大时IN有时更优。第二种子查询在FROM里也就是把子查询结果当临时表用必须起别名。比如查询每个分类的平均价格再找平均价格最高的分类SELECT category_id, avg_price FROM ( SELECT category_id, AVG(price) AS avg_price FROM t_product GROUP BY category_id ) t ORDER BY avg_price DESC LIMIT 1;第三种在SELECT列表里作为标量子查询给每一行补充一个值。这种写法通常伴随关联条件比如返回每个用户最近一单的金额。注意关联子查询是对外层表的每一行都执行一次内层查询数据量大时性能压力很大能用JOIN或者窗口函数替代就尽量别用。写子查询时有个小习惯EXISTS里通常写SELECT 1而不是SELECT *因为存在性判断只关心有没有记录不关心列内容。这个习惯在MySQL里性能差异不明显但对阅读代码的人而言SELECT 1传递的语义更清晰。4.2 MySQL 8窗口函数解决排名类需求MySQL从8.0版本开始支持窗口函数这绝对是个里程碑。以前要实现每组内排名得靠各种拐弯抹角的写法现在一行ROW_NUMBER() OVER()就解决了。先看三个容易混淆的函数ROW_NUMBER()给每组内每行一个连续不重复的序号RANK()同分同号但会留下空位DENSE_RANK()同分同号不留空位。具体差异我用成绩来举例三个学生并列第一ROW_NUMBER()会返回1、2、3RANK()返回1、1、1DENSE_RANK()返回1、1、1但接下来的第二名位置两者就不同了。实际业务里最常用的场景是每个分类下销量前三的商品SELECT category_id, name, sales FROM ( SELECT category_id, name, sales, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM t_product ) t WHERE rn 3;这里PARTITION BY相当于分组ORDER BY决定组内排序。以前用GROUP BY加ORDER BY加LIMIT只能拿到全表排序后的前几名拿不到每组前几名。窗口函数把这个需求从几十行复杂SQL简化到了几行。窗口函数还常用于计算移动平均、同期对比这类统计需求。如果你还在用5.7或更老的版本看到别人用窗口函数确实会羡慕但升级MySQL 8这件事本身也值得提上日程毕竟8.0都发布这么多年了。4.3 分页查询LIMIT的应用与深分页优化分页是在JavaWeb项目里出现频率极高的需求。基础写法就是LIMIT offset, size第一页LIMIT 0, 20第十页就是LIMIT 180, 20。这个逻辑本身没有任何问题问题出在offset很大的时候。LIMIT 100000, 20意味着MySQL要扫描并丢弃前10万条记录只返回最后的20条代价非常大。一个有效的优化手段是延迟关联也就是先在索引上定位到目标行的主键再回表取完整数据SELECT t.* FROM ( SELECT id FROM t_order ORDER BY id LIMIT 100000, 20 ) tmp JOIN t_order t ON t.id tmp.id;内层子查询只查询主键列可以走索引覆盖扫描10万条主键的开销远小于扫描10万条完整行。拿到20个主键后再关联回原表取数据整体性能提升明显。另一个思路是用WHERE id ?的键值分页记住上一页最后一条记录的ID下一页就从它之后开始取。这种方式没有offset数据量再大性能也稳定缺点是只能按主键顺序翻页不能随意跳到任意页适合加载更多这种交互模式。排序和分页经常一起出现要特别注意ORDER BY的字段如果不能走索引MySQL就得做filesort排序排序发生在分页之前那代价就更大了。所以分页查询里ORDER BY字段和索引的配合是性能调优的重点。4.4 条件判断与字符串处理表达式这一节讲几个实战中高频使用的表达式函数学会了能让你的DQL干净很多。CASE WHEN是条件分支的通用解法。比如统计不同状态下任务的数量传统做法是查出来后在Java代码里循环累加其实一条SQL就能搞定SELECT SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS done_cnt, SUM(CASE WHEN status 0 THEN 1 ELSE 0 END) AS doing_cnt, COUNT(*) AS total_cnt FROM t_task WHERE create_time STR_TO_DATE(2024-11-01, %Y-%m-%d);这种条件聚合的写法在做统计报表时特别有用把多行数据里的不同状态拆成多个统计列直接在SQL层面完成不用在Java代码里写循环。注意我这里的日期比较用了STR_TO_DATE把字符串转成日期这也是处理mysql将字符串转为日期这类需求的标准做法。反过来要在结果里格式化日期就使用DATE_FORMAT比如DATE_FORMAT(create_time, %Y-%m-%d)。IFNULL和COALESCE用于处理NULL值。统计金额总和时如果某组没有数据SUM会返回NULL而不是0用IFNULL(SUM(amount), 0)就能安全展示。业务上有存JSON或拼接字符串的需求就少不了CONCAT和CONCAT_WS后者可以指定分隔符省去手工拼逗号的麻烦。关于排序还有一个冷门但实用的函数ORDER BY FIELD。有时候业务要求按指定顺序排列比如状态字段按1、0、2这种自定义优先级排序SELECT * FROM t_product ORDER BY FIELD(status, 1, 0, 2);FIELD函数返回第一个参数在后续参数列表中的位置排序时按这个位置升序排列正好实现了自定义排序。这个技巧虽然不是标准SQL但在MySQL项目里非常实用。5. 查询性能实战索引原理与EXPLAIN排查链路5.1 索引是怎么让查询快几个数量级的聊性能必须先聊索引。很多初学者把索引起名叫索引但没想过它为什么能加快查询。我常打的比方是电话簿如果不排序你想找一个姓张的人得从头翻到尾如果按姓氏排好了翻到拼音Z那一块就能快速定位。MySQL里最常用的B树索引干的就是这件事——把字段值排好序组织成树形结构查找时从根节点一路定位到叶子节点复杂度从全表扫描的O(n)降到O(log n)。索引并非加得越多越好。每多一个索引写入和更新时的维护成本就多一分。关于索引创建最核心的几条原则高区分度的列适合建索引频繁出现在WHERE、ORDER BY、GROUP BY里的字段适合建索引联合索引要遵循最左前缀原则也就是索引(a, b, c)可以匹配a、a,b、a,b,c三种组合但跳过a直接用b作为条件就不行。比建索引更重要的是知道什么情况索引会失效。我在项目评审里最常见的几种对索引列使用函数WHERE YEAR(create_time) 2024、LIKE以%开头、隐式类型转换、用OR连接两个不同的索引字段。这些写法都会让优化器放弃走索引老老实实退化成全表扫描。5.2 EXPLAIN执行计划怎么看写SQL的时候随手执行一句EXPLAIN看着没啥实际上是在和MySQL的优化器对话。你不需要看懂所有字段抓几个关键的就够用。第一看type列这是访问类型从好到差大致是const主键或唯一索引等值查询、eq_ref唯一索引关联、ref普通索引等值查询、range范围查询、index扫描整个索引、ALL全表扫描。如果你的SQL是ALL说明在扫全表这是性能警报。第二看key列确认实际命中的索引名。第三看rows列这是MySQL预估要扫描的行数这个数字越小越好。第四看Extra列看到Using filesort说明排序没用上索引需要在ORDER BY字段上建索引看到Using temporary说明用了临时表通常和GROUP BY、DISTINCT有关看到Using index是好消息说明索引覆盖了查询所需的所有字段不需要回表。EXPLAIN SELECT * FROM t_order WHERE user_id 10 ORDER BY create_time DESC LIMIT 10;这条SQL在user_id和create_time都没有索引的时候通常显示typeALL, rows全表, ExtraUsing filesort。给表加上联合索引(user_id, create_time)后type变成refExtra里Using filesort消失因为联合索引已经让create_time在同一user_id内天然有序。这个例子就是典型的索引设计服务于SQL语义。5.3 一次慢查询的完整排查过程理论说再多不如走一遍真实的排查过程。发生在我自己项目里的一次经历订单列表接口在测试环境一切正常上到预发环境数据量到了几十万行接口响应时间从200毫秒飙到3秒。我的排查链路是这样的。第一步确认慢查询确实发生在数据库侧打开慢查询日志看看有没有捕捉到SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SHOW VARIABLES LIKE slow_query%;日志里果然躺着那条列表SQL执行时间2.8秒。第二步拿到具体SQL后执行EXPLAIN结果typeALL全表扫描rows显示几十万Extra里还有Using filesort。这一步其实已经把问题定位了缺索引排序还走了文件排序。第三步建索引。根据业务场景这个查询的条件是user_id等值过滤排序是create_time倒序所以联合索引建(user_id, create_time)ALTER TABLE t_order ADD INDEX idx_user_time (user_id, create_time);第四步重新执行EXPLAIN验证type从ALL变成refrows从几十万降到几十Extra里的Using filesort消失。第五步回到业务侧重新压测接口响应时间恢复正常。这个排查链路我建议每个人都完整走一遍因为它培养了最核心的调优思维方式先确认问题在不在数据库再用EXPLAIN定位具体瓶颈最后用索引或改写SQL解决每一步都用数据说话不靠猜。面试中常问的一条SQL执行慢怎么排查其实就是这一套标准流程。6. 把DQL接进JavaWebJDBC、MyBatis与连接池的配合6.1 JDBC执行查询的标准姿势DQL学得再好最终还是要从Java代码里发出去。JavaWeb入门阶段绕不开JDBC虽然现在项目里直接用裸JDBC的很少了但它是一切框架的地基。一个规范的查询流程是获取连接、预编译SQL、绑定参数、执行查询、遍历结果集、关闭资源。String sql SELECT id, title, price FROM t_book WHERE category_id ? AND status 1 ORDER BY create_time DESC LIMIT ?, ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setInt(1, categoryId); ps.setInt(2, offset); ps.setInt(3, pageSize); try (ResultSet rs ps.executeQuery()) { ListBook list new ArrayList(); while (rs.next()) { Book book new Book(); book.setId(rs.getInt(id)); book.setTitle(rs.getString(title)); book.setPrice(rs.getBigDecimal(price)); list.add(book); } } }注意这里我用了PreparedStatement而不是Statement这是从入门就要养成的习惯。PreparedStatement支持预编译和参数绑定SQL结构在编译期就固定了用户输入只能作为参数传入天然防范SQL注入。而用字符串拼接SQL的方式一旦把用户输入直接拼进DQL里等于是把数据库操作权限向攻击者敞开。面试常问的SQL注入原理本质上就是拼SQL时没有做参数化。try-with-resources也是必须的写法。Connection、PreparedStatement、ResultSet都实现了AutoCloseable用try块包住会自动关闭避免连接泄漏。连接泄漏这个问题在JavaWeb项目里很隐蔽连接没归还连接池池子耗尽后整个应用就瘫了。6.2 MyBatis Mapper XML里的DQL写法规范现在的主流JavaWeb项目基本都用MyBatis核心就是把DQL写进Mapper XML文件里。这里有几个规范直接决定项目质量。首先是#{}和${}的区别这个坑我们必须讲清楚。#{}是预编译占位符MyBatis会把它转成?参数通过setObject绑定安全可靠${}是字符串拼接直接把参数内容替换进SQL里存在SQL注入风险。所以正常情况下全部用#{}。${}只有在动态指定表名、ORDER BY字段这类无法用占位符的场景才会用到而且使用前必须做白名单校验绝对不能让用户输入直接拼进来。其次是动态SQL的写法。多条件查询是JavaWeb最典型的DQL场景条件有就有、没有就跳过。用where配合if可以自动处理多余的逻辑运算符select idpageBooks resultTypecom.example.Book SELECT id, title, price, stock FROM t_book where if testkeyword ! null and keyword ! AND title LIKE CONCAT(%, #{keyword}, %) /if if testcategoryId ! null AND category_id #{categoryId} /if /where ORDER BY create_time DESC LIMIT #{offset}, #{limit} /selectwhere标签会自动去掉条件块开头的AND/OR避免第一个条件不成立时SQL变成WHERE AND这种语法错误。LIKE模糊查询这里我又用了CONCAT拼接百分号而不是写LIKE %${keyword}%这样既安全又不破坏索引。第三个容易踩坑的是集合查询。参数是一个List要用IN条件时MyBatis里用foreach展开select idselectByCategoryIds resultTypecom.example.Book SELECT * FROM t_book WHERE category_id IN foreach collectioncategoryIds itemcid open( separator, close) #{cid} /foreach /select最后说N1问题。查询订单列表时如果先查订单再循环去查每个订单的用户就会产生1N条SQL数据量一大性能急剧下降。正确做法是用一条LEFT JOIN把关联数据一次性查出来配合resultMap里的collection映射。这个问题在代码评审中出现频率极高尤其容易出现在刚用MyBatis的团队里。6.3 连接池参数为什么DQL也依赖它JavaWeb项目里你不会直接DriverManager.getConnection()拿连接而是通过数据库连接池。连接池的原因本质上和性能有关每次建立MySQL连接都要走TCP握手、认证等流程非常昂贵连接池提前创建好一批连接查询时借用、用完归还极大降低单次查询的开销。我自己常用的配置是HikariCP或Druid核心参数如下参数推荐值含义initialSize5启动时初始连接数minIdle5最小空闲连接数maxActive20最大活跃连接数maxWait60000获取连接超时时间毫秒testWhileIdletrue空闲时检测连接有效性testOnBorrowfalse借出时不检测省一次查询validationQuerySELECT 1检测连接是否存活这里有个细节和一个DQL相关SELECT 1不查任何表只是一次最简单的连通性测试所有连接池都用它做心跳校验。如果连接长期空闲被MySQL服务端断开连接池靠定期检测把失效连接剔除避免应用拿到坏连接后报错。如果是Spring Boot项目连接池配置就是几行application.properties。我这里特别提醒MySQL 8连接串上的两个参数spring.datasource.urljdbc:mysql://127.0.0.1:3306/javaweb_demo?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/ShanghaiuseSSLfalseallowPublicKeyRetrievaltrue spring.datasource.usernameroot spring.datasource.password你的密码 spring.datasource.driver-class-namecom.mysql.cj.jdbc.DriveruseSSLfalse是关闭不必要的SSL握手allowPublicKeyRetrievaltrue是配合caching_sha2_password插件解决公钥获取问题。很多人在IDEA里运行JavaWeb项目时报SSL连接错误基本就是连接串缺了这两个参数。7. 进阶避坑存储过程、事务与锁在查询场景中的实践7.1 存储过程什么时候该用什么时候别碰存储过程在JavaWeb项目里的地位比较微妙。技术上讲它可以把一段复杂的数据库操作打包成一个名字调用比如批量汇总、定时统计。但把核心业务逻辑写进存储过程会带来几个实际麻烦逻辑分散在数据库层和代码层不方便版本管理存储过程很难写单元测试数据库层承担了本可以在应用层用并发和中间件解决的负载水平扩展变困难。所以我的建议是核心业务查询和更新尽量用SQL加应用层逻辑来实现存储过程只用在数据迁移、定时报表汇总这类相对固定的场景。给你看一个典型的报表汇总存储过程长什么样DELIMITER $$ CREATE PROCEDURE proc_daily_summary(IN p_date DATE) BEGIN INSERT INTO t_daily_sales(stat_date, total_amount) SELECT p_date, IFNULL(SUM(amount), 0) FROM t_order WHERE order_date p_date AND status 1; END$$ DELIMITER ; CALL proc_daily_summary(2024-11-01);注意DELIMITER $$的作用是临时把语句分隔符从分号改成$$否则MySQL客户端会在存储过程内部的每个分号处提前结束语句。很多新手第一次写存储过程就卡在这里。7.2 事务隔离级别与查询看到的数据DQL不只是查出结果在并发环境下同一个查询在不同事务隔离级别下看到的数据可能不一样。MySQL默认的事务隔离级别是REPEATABLE READ也就是可重复读。这意味着在同一个事务里连续执行同一条SELECT得到的结果是一致的不受其他事务提交的影响。四个隔离级别从宽松到严格分别是隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不可能可能可能REPEATABLE READ不可能不可能可能InnoDB下基本避免SERIALIZABLE不可能不可能不可能JavaWeb里改事务级别的情况非常少默认的REPEATABLE READ配合InnoDB的多版本并发控制已经能保证大多数业务场景的一致性。真正需要关注的是事务范围的控制Service层加Transactional时事务范围应该尽量短只包住真正需要保证原子性的写操作。如果你在事务里执行一串无关紧要的查询会让事务持有连接的时间变长在高并发下连接池很快被占满。我还见过一种反模式在循环里逐条查询再逐条更新每条语句都单独开事务。这种写法性能极差正确做法是用一个事务包住循环或者改成批量SQL。记住一个原则事务是给多个操作要么全成功要么全失败提供保障的不是为了给查询做包装的。7.3 锁表死锁排查别被查询卡住DQL也会遇到锁问题这个认知很多人没有。虽然普通SELECT在InnoDB下是快照读不加锁但一旦查询语句出现在事务里或者和更新操作搅在一起就可能触发锁等待甚至死锁。常见的一个场景应用卡住接口一直转圈看数据库里一堆连接堆积。这时候第一步就是用两板斧定位SHOW PROCESSLIST; SHOW ENGINE INNODB STATUS\G;SHOW PROCESSLIST能看到当前所有连接在干什么如果发现很多连接的State是updating或locked基本就是锁等待。接着看SHOW ENGINE INNODB STATUS里的事务和锁信息那里会列出正在等待锁的事务以及持有锁的事务ID。死锁最常见的来源是多个事务以不同顺序更新同一组数据。比如事务A先更新订单再更新用户事务B先更新用户再更新订单两边互相等待对方持有的锁就死锁了。解决办法是在代码层统一更新顺序所有事务都按同样顺序操作破坏循环等待条件死锁自然消失。MySQL默认的锁等待时间是50秒超过就报Lock wait timeout exceeded。这个参数可以调但不建议盲目调大更重要的是减少持锁时间。批量更新时用WHERE条件尽量精确业务上分批处理避免一次更新锁住大片行。记住锁是用来保护数据一致性的不是用来惩罚慢查询的把查询和更新之间的锁范围控制好并发能力自然就上来了。最后说一个我自己带人时反复强调的习惯写任何一条DQL之前先花十秒钟想清楚三个问题——要不要所有列、能不能命中索引、最终要排序还是分组。写完之后随手EXPLAIN一眼这个习惯花不了半分钟但能帮你省下无数个线上接口突然慢了的凌晨。MySQL DQL这门手艺说穿了就是语法、原理、实践三件事反复打磨没有太多高大上的东西。希望这条从入门到进阶的路线能让你少走我当年踩过的那些弯路。