前几天review同事提交的一条取数SQL差点没把眼睛看花。WHERE条件里密密麻麻全是“||”拼出来的字符串AND create_time TO_DATE(p_date || 00:00:00, YYYY-MM-DD HH24:MI:SS)、AND name LIKE % || p_keyword || %、AND dept_no || p_dept。同事说这样写省事前端传什么参数直接拼进去就行空值也不用单独判断。我听完第一反应是省事是省事慢SQL估计也快来了。打开执行计划一看果然三个过滤条件里两个没走索引还有一个在谓词信息里出现了隐式转换。借这个项目标题我把“||拼接条件”这种写法的常见问题、底层原理和优化方案整体梳理一遍。主要面向写SQL、写存储过程、写Java/MyBatis的同行尤其是刚接手复杂报表的人。内容偏Oracle多一些但MySQL、PostgreSQL里的原理也都通用看的时候把语句对应过去就行。1. 场景还原这些“||”用法最容易埋雷1.1 存储过程与报表SQL里的“万金油”条件我在实际项目里见到最多的就是有人为了让一个查询条件“可传可不传”把参数直接拼进条件里。典型长这样SELECT * FROM t_user_history WHERE 1 1 AND (p_dept IS NULL OR dept_no p_dept) AND create_time TO_DATE(p_date || 00:00:00, YYYY-MM-DD HH24:MI:SS) AND user_name LIKE % || p_keyword || %;这段SQL里有两处“||”拼接。p_date || 00:00:00是把日期参数补成完整字符串再转DATE% || p_keyword || %是做模糊查询。这两个写法都很常见但如果p_date传的是NULL拼接结果还是NULLTO_DATE(NULL)直接报错LIKE % || NULL || %在Oracle里等价于LIKE NULL一条数据都查不出来。换句话说“拼接”并没有帮你兜住空值反而制造了新的边界问题。更隐蔽的问题是索引。create_time TO_DATE(p_date || 00:00:00, ...)右边虽然是函数但如果左边列没套函数且右边是常量/绑定变量表达式Oracle理论上还能用上索引可一旦日期格式写错或者参数类型是VARCHAR2条件就常常演变成TO_CHAR(create_time)和字符串比较索引直接废掉。这类SQL在报表系统里出现频率极高往往一张表几百万行跑一次就是全表扫。1.2 应用层代码里的拼接危害更直接数据库层的“||”再怎么说也还是SQL语法层面的操作应用层手工拼接字符串就更危险了。我接手过一个老系统Java代码里全是这种写法String sql SELECT * FROM t_user_history WHERE 1 1; if (deptNo ! null !deptNo.trim().isEmpty()) { sql AND dept_no deptNo ; } if (keyword ! null !keyword.trim().isEmpty()) { sql AND user_name LIKE % keyword %; } Statement stmt conn.createStatement(); ResultSet rs stmt.executeQuery(sql);这个代码有两个问题。第一dept_no 10这种写法列是VARCHAR2时还没事如果列是NUMBER数据库就要把dept_no列做隐式转换去和字符串比较索引失效。第二直接把用户输入拼进SQL如果keyword里带一个单引号SQL语法直接崩带一段 OR 11就是教科书级别的注入。即使不考虑安全问题每次查询生成的SQL文本都不同数据库执行计划缓存基本形同虚设。1.3 先给结论拼接条件共性和影响面“||”拼接条件的共性可以概括成三句话空值语义容易被误解索引容易被搞丢动态SQL文本不稳定导致硬解析。这三件事单独拎出来都能写篇文章合在一起就是慢SQL制造机。影响面也很大。只要系统里有手工拼SQL的场景无论是PL/SQL存储过程、Java JDBC还是MyBatis里的${}都跑不掉。后台管理系统的多条件筛选、报表系统的日期区间查询、接口里的模糊搜索——这些业务往往会写“可传可不传”的查询条件而“可传可不传”恰恰是拼接写法最泛滥的地方。2. 为什么拼接条件会成为性能黑洞2.1 列被函数或拼接包裹索引为什么失效先说索引失效的原理。B-tree索引存储的是原始列值的有序排列优化器走索引的前提是条件里能直接拿列值和查询值做比较。一旦条件写成dept_no || p_dept数据库就得先把每一行的dept_no取出来拼接一下再和p_dept比较。这个拼接动作对优化器来说相当于对列施加了一个“计算过程”它无法直接根据索引键做范围定位只能老老实实把整张表扫一遍然后逐行计算。打个比方索引就像书的目录目录是按原样排好的。你要查“张三”直接翻目录找“张”开头就行但如果要求你“在所有名字后面加一个逗号再找‘张’”目录就废了只能一页一页翻正文。这就是为什么在所有数据库优化规范里第一条永远是“不要在列上做任何运算”。更坑的是很多开发者写dept_no || p_dept的初衷是不想在应用层处理空串以为加个空字符串就能和NULL“中和”一下。但在Oracle里本身就被当成NULLdept_no || 的结果有时候优化器能识别成dept_no有时候识别不了完全看优化器版本的等价改写能力。这种依赖“优化器心情”的写法上线环境稍微变一下就翻车根本不值得赌。2.2 前导通配符的LIKE普通索引为什么用不上模糊查询里的LIKE % || ? || %是另一个高频问题。普通B-tree索引支持的是“左前缀匹配”也就是能根据开头的几个字符快速定位。abc%这种方式可以走索引因为索引键本身就是有序的从头开始扫一段区间就行。但%abc%要求“字符串里任意位置包含abc”B-tree索引的有序性就完全帮不上忙数据库只能全表扫描逐行做字符串匹配。很多人不理解说索引里不也存了字符串吗为什么不能先找到abc再扩展因为索引的有序前缀是“第一个字符”“第二个字符”这样排下来的而不是“中间某个字符”。除非建全文索引或者倒排索引否则普通B-tree对前后模糊就是无能为力。这里要注意MySQL默认情况下||是逻辑或运算符不是字符串拼接所以MySQL里写LIKE % || keyword || %会得到莫名其妙的结果必须用CONCAT(%, keyword, %)。这也是“||拼接”跨数据库时的一个隐藏坑。2.3 Oracle里特别容易误判的NULL与空串语义Oracle有个让新手抓狂的特性空字符串等价于NULL。所以你写p_dept || 的时候如果p_dept是NULL整个表达式结果还是NULL而不是空字符串。很多开发者原本想表达的是“参数为空时匹配所有”结果写出来变成“参数为空时什么都匹配不到”。标准SQL里NULL与任何值比较结果都是UNKNOWN只有IS NULL能判断。所以dept_no NULL不会返回任何行这不是数据库bug是NULL语义本身的问题。在PostgreSQL里NULL || abc的结果是NULL和Oracle行为不同但p_dept || 的坑是一样的。只要参数是NULL拼接结果就是NULL兜底逻辑完全失效。要正确处理“可选条件”应该写成显式逻辑(:p_dept IS NULL OR dept_no :p_dept)。这个写法SQL文本稳定语义一目了然也不会让优化器因为拼接表达式而放弃索引虽然OR条件在极端场景下也可能影响执行计划但至少不会出现NULL比较的硬伤。2.4 SQL文本每次都不同硬解析怎么来的动态SQL拼接最直接的问题是执行计划缓存失效。以Oracle为例共享池里的游标按SQL文本精确匹配差一个空格都算不同SQL。v_sql : v_sql || AND dept_no || v_dept这种写法v_dept传10时SQL是...dept_no 10传20时变成...dept_no 20两条文本完全不同数据库只能一条条硬解析。硬解析的成本包括语法解析、语义检查、权限检查、生成执行计划、申请共享池内存等一系列动作。QPS一上来共享池里塞满“结构相同只是值不同”的SQLLibrary Cache的锁竞争加剧CPU飙升最后整个库都变慢。用一个词总结就是数据库把大量资源浪费在了重复解析上。绑定变量就是为了解决这个问题。写成WHERE dept_no :dept_no不管传10还是20SQL文本始终相同数据库解析一次后面直接复用执行计划。这个差别在量小的时候不明显一到压测或者线上大流量就是几百毫秒和几十毫秒的差距。3. 优化方案与落地写法3.1 等值条件先把无意义的“||”拿掉最基础的一步是把SQL里那些为了“防止NULL”而加的|| 统统去掉。比如-- 反例 SELECT * FROM t_user_history WHERE dept_no (p_dept || ); -- 正例 SELECT * FROM t_user_history WHERE dept_no p_dept;如果需求是“参数为空时返回全部”不要试图用拼接解决直接用显式逻辑SELECT * FROM t_user_history WHERE (:p_dept IS NULL OR dept_no :p_dept);这里的:p_dept在Oracle里可以是绑定变量在MyBatis里对应#{deptNo}。虽然OR :p_dept IS NULL在数据分布不均时也可能被优化器选成全表扫描但它的语义是对的至少不会因为NULL拼接导致一条都查不出来。如果表特别大、这个OR写法实测下来全表扫太久可以用UNION ALL把分支拆开后面单独讲。日期条件的优化也一样。传参时直接传DATE类型或者写create_time TO_DATE(:p_date, YYYY-MM-DD HH24:MI:SS)不要用|| 00:00:00拼字符串再转。把参数类型和格式控制在应用层数据库端只做单纯比较。3.2 动态SQL里的值必须绑定变量存储过程里拼动态SQL最忌讳把变量值直接拼进SQL文本。反例和正例对比如下-- 反例每次v_dept不同都会生成新的SQL文本 DECLARE v_dept VARCHAR2(20) : 10; v_sql VARCHAR2(4000); v_cnt NUMBER; BEGIN v_sql : SELECT COUNT(*) FROM t_user_history WHERE dept_no || v_dept; EXECUTE IMMEDIATE v_sql INTO v_cnt; END; / -- 正例SQL文本固定绑定变量传值 DECLARE v_dept VARCHAR2(20) : 10; v_sql VARCHAR2(4000); v_cnt NUMBER; BEGIN v_sql : SELECT COUNT(*) FROM t_user_history WHERE dept_no :dept_no; EXECUTE IMMEDIATE v_sql INTO v_cnt USING v_dept; END; /正例里的SQL文本永远是那一句数据库可以反复复用执行计划。这也符合SQL优化的第一原则让相似的SQL长得一模一样让数据库尽量走软解析。如果动态SQL里只有部分条件需要动态拼接比如“有传dept_no就拼dept_no条件没传就不拼”这种场景建议用静态SQL加OR条件替代而不是真去拼SQL文本SELECT * FROM t_user_history WHERE (:p_dept IS NULL OR dept_no :p_dept) AND (:p_keyword IS NULL OR user_name :p_keyword);这样只有一条SQL通用执行计划一次成型。缺点前面也提了OR可能导致优化器判断不够精准但纯动态拼接的SQL文本碎片化问题更严重。3.3 复杂动态排序白名单方式处理真正没法用绑定变量的是排序字段和表名字段这类“对象名”。比如ORDER BY后面不能写:order_col只能拼字符串。这块尤其适合用白名单方式处理-- MyBatis XML里的写法 choose when testorderBy createTime ORDER BY create_time /when when testorderBy userName ORDER BY user_name /when otherwise ORDER BY id /otherwise /choose宁可写一堆when分支做映射也不要直接ORDER BY ${orderBy}。白名单的好处是值域已经固定了SQL注入进不来执行计划也不会因为排序字段变化而反复硬解析。如果是存储过程里拼动态SQL同样在代码里对排序字段做if判断拼成固定字符串。3.4 模糊搜索前后模糊和索引的取舍LIKE % || ? || %在数据量小的时候没什么感受但过了几十万行就开始疼。优化方向取决于业务能不能接受“右模糊”。如果业务上只需要按前缀搜索比如根据姓名首字搜用户LIKE :kw || %能走索引性能提升非常明显-- 右模糊可以走INDEX RANGE SCAN SELECT * FROM t_user_history WHERE user_name LIKE :kw || %;如果业务强制要求前后模糊普通索引确实无用武之地。这时候有几个选择数据量中等、并发不高全表扫描硬扛但要做好SQL超时的心理准备数据量大、搜索频繁建议上全文索引Oracle Text、PostgreSQL的GIN/tsvector、MySQL FULLTEXT再大一些就是独立搜索引擎的范畴了。我个人的习惯是后台管理列表这种低频查询允许前后模糊核心交易链路的查询必须避免前后模糊。3.5 Java端和MyBatis的正确姿势Java端拼SQL核心就一句话永远用PreparedStatement永远不要用Statement拼字符串。正确写法是把?占位符放进去再用setString/setInt传参String sql SELECT * FROM t_user_history WHERE dept_no ? AND create_time ?; try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, deptNo); ps.setTimestamp(2, new Timestamp(createTime.getTime())); ResultSet rs ps.executeQuery(); }这样SQL文本固定数据库能复用执行计划也彻底杜绝了注入。MyBatis里对应的就是#{}不要用${}。#{}会被解析成预编译占位符${}是直接拼接文本。唯一允许${}的场景就是上一节说的ORDER BY、表名等对象名而且必须做白名单。模糊查询在MyBatis里可以写成select idsearchUser resultTypemap SELECT * FROM t_user_history where if testkeyword ! null and keyword ! AND user_name LIKE % || #{keyword} || % /if /where /select这里有个容易被误会的点% || #{keyword} || %里的“||”是数据库端执行的拼接#{keyword}本身是绑定变量安全上没有注入问题和前文的“手工拼接SQL字符串”是两码事。但前导通配符导致的索引问题依然存在所以该考虑的还是得考虑。4. 实测对比改写前后执行计划与耗时4.1 测试环境与准备为了让大家对“||”拼接的影响有直观认识我在本地搭了一组简单测试。环境是Oracle 19c一张t_user_history表数据量约120万行字段包括id、user_name、dept_no、create_timeuser_name和dept_no上建了普通B-tree索引。所有测试SQL用绑定变量排除重复硬解析的干扰。先说结论同样的查询意图因为写法不同执行计划可以天差地别。这里我把几种典型场景贴出来SQL文本和实测观察都列出来方便对照。4.2 等值条件与“列被拼接”的对比写法ASELECT COUNT(*) FROM t_user_history WHERE dept_no || :dept_no;写法BSELECT COUNT(*) FROM t_user_history WHERE dept_no :dept_no;用DBMS_XPLAN.DISPLAY_CURSOR看执行计划写法A在我的环境里走了TABLE ACCESS FULLCost大约1100多写法B走INDEX RANGE SCANCost只有个位数。dept_no || 虽然看起来“加了个空串等于没加”但优化器并没有把这个表达式自动折回索引键结果就是几万倍的逻辑读差异。这里补充一点Oracle某些版本可能会对|| 做constant folding但这不是可控行为。我见过同一套代码从11g升到19c后执行计划发生变化的案例所以最稳妥的方式还是从源头上不写这种表达式。4.3 模糊查询的对比写法ASELECT COUNT(*) FROM t_user_history WHERE user_name LIKE % || :kw || %;写法BSELECT COUNT(*) FROM t_user_history WHERE user_name LIKE :kw || %;120万行数据下写法A全表扫描实测单次查询大约320毫秒写法B走INDEX RANGE SCAN单次查询大约2毫秒。这个差异在处理列表接口翻页、Excel导出这种高频查询时基本上就是“能用”和“不能用”的区别。如果业务真的需要前后模糊又没法换搜索引擎可以在Oracle里考虑建CONTEXT全文索引用CONTAINS(user_name, :kw) 0查询。但全文索引的维护、分区、同步策略都需要额外成本不是银弹。4.4 动态SQL硬解析的对比我用PL/SQL循环模拟一个高频查询场景循环10000次每次传一个不同的dept_no值。反例是每次把v_dept拼进SQL文本正例是绑定变量。反例循环结束后查v$SQL能看到大量SQL_TEXT不同但结构相同的记录每条EXECUTIONS都是1LOADS也高正例循环结束后v$SQL里只有一条SQL_TEXTEXECUTIONS是10000说明游标被稳定复用。整体耗时上反例大约2.6秒正例大约0.18秒差距超过十倍主要就是硬解析的代价。硬解析的隐性成本远不止等待时间还有共享池内存占用、Library Cache锁竞争、CPU消耗。一个系统里如果这种动态拼接SQL铺开最终表现就是数据库CPU莫名高企v$SQL里塞满相似的孤儿SQL。5. 常见问题排查与避坑指南5.1 怎么快速判断一条SQL有没有隐式类型转换或函数包裹列最直接的办法是看执行计划里的Predicate Information。Oracle里执行EXPLAIN PLAN FOR SELECT * FROM t_user_history WHERE dept_no 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果输出里出现TO_NUMBER(DEPT_NO)这种信息说明dept_no列被隐式转换了索引大概率走不上。同理如果看到TO_CHAR、TO_DATE包住一个列名也是在提醒你“列上被动了手脚”。MySQL里可以用EXPLAIN EXTENDED后跟SHOW WARNINGS查看改写后的SQL能直观看到隐式转换。SQL Server则看执行计划里有没有CONVERT_IMPLICIT。5.2 怎么判断动态SQL是不是在重复硬解析Oracle执行这个查询SELECT sql_text, executions, loads, invalidations, cpu_time FROM v$sql WHERE sql_text LIKE %t_user_history% ORDER BY loads DESC;如果发现同一张表的SQL_TEXT有几十条相似记录每条EXECUTIONS都差不多LOAD次数也高基本可以断定有人在拼条件。反过来如果有一条SQL的EXECUTIONS特别高、LOAD次数很低说明执行计划复用得很好。MySQL里对应看performance_schema.events_statements_summary_by_digest按DIGEST_TEXT聚合如果同一个digest下SQL_TEXT变化很多也能看出问题。5.3 常见写法对照速查表原写法主要问题优化写法WHERE dept_no (p_dept || )NULL语义错误查不到数据WHERE p_dept IS NULL OR dept_no p_deptWHERE col || :val列参与拼接索引不可用WHERE col :valWHERE name LIKE % || :kw || %前导通配符普通索引失效WHERE name LIKE :kw || %或全文索引/搜索组件v_sql || AND dept_no || v_dept每次SQL文本不同硬解析注入风险v_sql : ... AND dept_no :dept_noUSING v_deptAND create_time TO_DATE(p_date || 00:00:00, ...)参数为NULL时报错格式解析不稳定应用层传DATE类型create_time :p_dateJava里用字符串拼接SQLSQL注入、硬解析PreparedStatement 绑定变量占位符MyBatis里用${}文本注入、执行计划不稳定用#{}对象名/排序字段必须白名单WHERE 1 1没有性能问题但可读性一般可以用where动态生成保留也可优化器会忽略5.4 几条值得记住的实操原则第一列上不要出现任何函数、拼接、计算这是铁律。只要出现优化器就没法正常使用索引。第二动态SQL里所有值都用绑定变量/PreparedStatement占位符。不是为了好看是为了让数据库认得出“这是同一条SQL”。第三空值条件用显式逻辑不要用拼接去“猜”。Oracle里和NULL等价这件事背十遍都不为过。第四排序字段、表名等对象名没法绑定就用白名单映射千万别放开参数直接拼。第五遇到慢SQL别只盯着耗时先看执行计划看Predicate Information里有没有隐式转换再看v$SQL里有没有堆积的相似SQL文本。定位到根因再动手改往往几行SQL就能把性能拉回来。我个人在实际排查中最大的体会是||拼接条件不是大事但它能牵出一整串数据库层面的连锁反应——索引失效、硬解析、空值陷阱、注入风险。很多团队写的SQL工具类、报表模板里都有这类写法每次优化完看着执行计划从全表扫描变成索引扫描还是挺有成就感的。如果你手头也有一堆历史SQL要review不妨按上面的速查表过一遍多半能找到一两处可以立刻改掉的“拼接毒瘤”。