生产SQL隐藏风险与编码规范-这些 SQL 就在你生产库里头埋着呢，随时会炸

这些 SQL 就在你生产库里头埋着呢随时会炸凌晨两点多。一条告警短信直接把 DBA 给弄醒了。生产系统那边有个权限查询开始大批量返回空集。受影响的用户登不进去了。排查了整整三个小时。最后定位到一条 SQL。这条 SQL 在系统里跑了两年了。没改过。数据也没什么异常。那为什么会出事呢是因为之前做了一次数据迁移。某张关联表里面多出来了一行 NULL 记录。就因为这一行什么都查不出来了。有这么一类代码审查的问题。在 Code Review 里面是根本挑不出来的。为什么呢因为它能跑通。有输出。也不报错。但是到了某个特定的时间点。或者说某种特定的数据状态下面。它就会突然表现出完全错误的行为。这你上哪说理去。这类问题大家一般叫它隐性逻辑风险。在数据库这一层它其实特别危险。为什么呢因为它失效的时候往往是没有声音的。没有报错。也没有什么异常堆栈给你看。就只有一个结果。这个结果跟你预期的不一样。或者是一条被错误改掉的数据。然后它就放在那。等着用户去发现。或者等着监控去抓。这篇文章我整理了一下。都是生产环境里面真实出现过的几类不规范的 SQL 写法。还有对应的风险分析。以及规范建议。希望能帮到你。在它们发作之前把这些雷给排掉。文章目录这些 SQL 就在你生产库里头埋着呢随时会炸一、在 WHERE 子句里面去依赖函数的执行顺序场景还原一下风险一执行顺序这东西是没有什么契约保证的风险二会话状态被污染了——这种 Bug 最难找正确的做法DBA 排查的指引二、NOT IN 子查询里面的 NULL 陷阱问题场景根本原因三值逻辑搞出来的连锁反应修复方案三、在批处理里面用跨事务的时间函数先把 NOW() 的真实行为搞清楚问题场景跨库迁移时候的风险维度正确的做法四、隐式去依赖列顺序的 INSERT问题场景正确的做法五、在事务里面混着用 DDL 导致事务边界乱套了Oracle 里面的 DDL 隐式提交KES 的事务内 DDL 情况六、聚合函数里面的 NULL 传播不同的聚合函数怎么处理 NULL正确的处理方式通用的 SQL 编码规范建议一、在 WHERE 子句里面去依赖函数的执行顺序这种不规范的写法是我见过的风险等级最高的几个里面的一个。而且它藏得特别深。测试环境里面你去跑几乎肯定是能跑通的。只有在特定的会话状态下面它才会出问题。场景还原一下有个系统。它用 Package 的全局变量来传递上下文。开发人员当时可能是为了省事。把设置变量还有使用变量这两个逻辑。给混写在同一条 SQL 的 WHERE 子句里面了。就像下面这样。SELECT*FROMbusiness_tableWHERErecord_idpkg_context.get_id()-- 函数1读取上下文变量ANDpkg_context.set_id(current_id)1;-- 函数2设置上下文变量成功返回1开发人员是怎么想的呢他以为set_id会先去执行。把变量设置好之后。get_id再去读这个变量。也就是说这两个函数会按照书写顺序从右到左去执行。或者按某个特定的顺序去执行。风险一执行顺序这东西是没有什么契约保证的SQL 它是一门声明式的语言。WHERE 子句里面的条件求值顺序其实属于执行引擎的实现细节。不同的数据库。不同的版本。不同的执行计划下面。这个顺序可能都是不一样的。KES 在现在的这个版本里面。对 WHERE 子句里的函数条件。它是按照从左到右的顺序去依次执行的。按这个顺序来的话get_id就会比set_id先执行。这个时候变量还没被设置呢。get_id拿到的就是旧值。或者是 NULL。但是呢这个从左到右仅仅只是 KES 当前版本的一个实现行为。它不是 SQL 语义给你保证的东西。万一以后优化器增强了函数代价评估的能力。它完全是有可能把条件的求值顺序给调整掉的。你去依赖这个行为的代码。那就跟走钢丝没什么区别了。风险二会话状态被污染了——这种 Bug 最难找Package 全局变量的生命周期是什么呢是整个会话也就是数据库连接。只要这个连接还在它就一直有效。这要是放在连接池的场景下面。就会产生非常诡异的情况。我们来看一下。应用连接池里面有个连接 A。某次请求执行了set_id(10)。变量就被设置成 10 了。请求处理完了。连接 A 就还给连接池了。接着另一个请求从连接池里面把连接 A 拿出来了。这个时候变量里面还是 10 啊。然后它去执行带get_id()的查询。get_id()读到的是什么是上次那个请求留下来的 10。根本不是当前请求想要的那个值。查询结果就莫名其妙地错了。而且它完全不报错。这种 Bug 它有几个很典型的特征。这就导致它特别难被发现。测试环境里面根本复现不出来为什么复现不出来呢因为测试的时候通常用的是长连接。或者就一个连接。历史状态把这个问题给掩盖掉了。并发低的时候几乎不触发只有特定的请求顺序凑到一起了它才会冒出来。表现出来就是偶发性的数据错乱没什么规律。很难去定位根因在哪。有时候甚至会被误认成是网络问题。或者是数据本身的问题。正确的做法-- ❌ 错误去依赖 WHERE 子句里面的函数执行顺序SELECT*FROMbusiness_tableWHERErecord_idpkg_context.get_id()ANDpkg_context.set_id(current_id)1;-- ✅ 正确状态设置跟查询要严格分开-- 第一步单独去调用设置函数CALLpkg_context.set_id(current_id);-- 第二步再去执行纯查询SELECT*FROMbusiness_tableWHERErecord_idpkg_context.get_id();规范原则绝对不要在 WHERE 子句里面放那种有副作用的函数。什么叫有副作用呢就是修改数据或者修改状态的函数。查询就是查询。状态设置是状态设置。这两件事必须要在不同的执行步骤里面去做完。DBA 排查的指引如果你发现某个查询的结果跟会话有关系。比如换个连接它就不对了。那你得立刻去查这几个东西。存储过程或者函数有没有去改 Package 级别的全局变量。应用连接池的连接被复用的时候有没有去重置状态。在 KES 里面的话。你可以通过EXPLAIN ANALYZE去看一下 WHERE 子句里各个条件实际的执行顺序。还有它花了多少时间。这样能帮你定位问题。二、NOT IN 子查询里面的 NULL 陷阱文章一开头讲的那个案子。就是这个问题。问题场景-- 权限系统查询不在禁用列表中的用户SELECTuser_id,user_name,roleFROMactive_usersWHEREuser_idNOTIN(SELECTdisabled_idFROMdisabled_list);这条 SQL 看着是不是没什么毛病但是如果disabled_list.disabled_id里面混进了一行 NULL 值。整个查询返回的就会是空结果集。结果就是没有任何用户能登录进去。根本原因三值逻辑搞出来的连锁反应我们把 NOT IN 的语义等价展开来看一下。WHEREuser_iddisabled_id_1ANDuser_iddisabled_id_2AND...ANDuser_idNULL-- 这里user_id NULL这个结果是什么是 Unknown。它不是 False。也不是 True。Unknown AND 任何东西。结果要么是 Unknown。要么是 False。那么最后整个条件的结果就变成了 Unknown。没有任何一行数据能通过 WHERE 的过滤。更诡异的情况在于什么呢。这个问题在disabled_list表是空的时候它不触发。空集的 NOT IN 总是会返回 True 的。但是在表里面只有 NULL 的时候它就触发了。所有用户都被过滤掉了。在表里有正常数据但是中间夹了一条 NULL 的时候它也会触发。实际在生产里面。那一行 NULL 到底是哪来的呢往往只是因为下面这几种情况。某次 ETL 导入数据的时候没有做非空校验。某次做数据修复操作手法不太规范。业务上面允许有未知这种状态的记录存在。某个代码 Bug 写进去了不合法的数据。修复方案-- ✅ 方案一NOT EXISTS推荐天然 NULL 安全SELECTuser_id,user_name,roleFROMactive_users uWHERENOTEXISTS(SELECT1FROMdisabled_list dWHEREd.disabled_idu.user_id);-- ✅ 方案二子查询中显式过滤 NULLSELECTuser_id,user_name,roleFROMactive_usersWHEREuser_idNOTIN(SELECTdisabled_idFROMdisabled_listWHEREdisabled_idISNOTNULL);其实 NOT EXISTS 在底层的实现上通常来说也会更高效一点。为什么呢对于active_users里面的每一行数据。它只需要在disabled_list里面找到第一条匹配的。就可以停下来了。但是 NOT IN 往往仅仅只是需要先去把整个子查询的结果给物化出来。这就有开销了。团队规范的建议在你们的代码规范文档里面最好明确写上这一条。子查询来源的那个字段如果你不能百分之百确定它里面没有 NULL。那就禁止用 NOT IN。直接改用 NOT EXISTS。三、在批处理里面用跨事务的时间函数这个问题在批量处理数据的脚本还有存储过程里面其实特别常见。它触发的条件也很特殊。只有在批处理被切分成了好几个独立事务的时候它才会出问题。你要是放在单事务里面去执行那是完全正常的。先把 NOW() 的真实行为搞清楚在 KES 里面。NOW()还有CURRENT_TIMESTAMP这俩东西。它们返回的是当前事务开始时的时间戳。也就是说在同一个事务里面。不管你调用了多少次。它返回的时间值全都是同一个。这是 SQL 标准里面对于事务时间的一个规范要求。那什么函数会在每次调用的时候都返回不同的时间呢也就是系统的实时时钟。是CLOCK_TIMESTAMP()这个函数。它不依赖事务。每次调用它都去取实时时钟。它属于 VOLATILE 函数。问题场景-- 存储过程归档30天前的订单在循环里每批单独 COMMITCREATEORREPLACEPROCEDUREarchive_old_ordersASBEGINFORiIN1..10LOOPUPDATEordersSETstatus已归档WHEREcreate_timeNOW()-INTERVAL30 days-- 每个事务开始时重新取时间ANDstatus已完成ANDbatch_idi;COMMIT;-- 每批提交一次开启新事务ENDLOOP;END;这个问题的根源在哪呢其实不是NOW()这个函数本身不稳定。而是因为每次COMMIT以后它就开启了一个新事务。NOW()在这个新事务里面返回的是新事务的开始时间。这就跟上一批次用的时间基准不一样了。假如说这个存储过程是在 23:55 开始跑的。跑到 00:05 结束。中间刚好跨了零点。那会怎么样呢前面几个 batch每次 COMMIT 完了以后新事务的NOW()拿到的是 23:5x。后面几个 batch新事务的NOW()就变成 00:0x 了。这两批用的就不是同一个时间基准了。那么算30天前的结果就会差个几分钟到十几分钟。这就可能导致处在边界上的订单。要么被漏掉了。要么被重复处理了。跨库迁移时候的风险维度不同的数据库它们批处理的事务模型可能是不一样的。某些数据库的存储过程里面。每一个语句会自动当成一个独立事务去提交。也就是 Auto-commit 模式。但是在 KES 里面呢。你得显式地去写 COMMIT。它才会把当前事务给结束掉。如果你的旧系统。它的存储过程逻辑依赖了一个假设。就是NOW() 在整个过程中是固定不变的。比如在单事务的长批处理里面这确实是成立的。但是迁到 KES 以后呢。一旦你的批处理逻辑被改成了多事务分批提交。这个时间基准就会在事务之间漂移了。正确的做法-- ✅ 在批处理开始时固定时间基准存入变量后续统一使用CREATEORREPLACEPROCEDUREarchive_old_ordersASv_cutoffTIMESTAMP;BEGINv_cutoff :NOW()-INTERVAL30 days;-- 只在第一个事务里取一次固定基准FORiIN1..10LOOPUPDATEordersSETstatus已归档WHEREcreate_timev_cutoff-- 所有批次使用同一个时间点ANDstatus已完成ANDbatch_idi;COMMIT;ENDLOOP;END;把时间基准提取出来放到一个变量里面。这样的话整个批处理的过程不管你跨了多少个事务。用的都是这同一个固定的时间快照。跨事务时间基准漂移的风险就这么给彻底消除了。四、隐式去依赖列顺序的 INSERT这个问题在那些老系统里面特别常见。它本身其实不复杂。但是它带来的后果可能会非常严重。问题场景-- 没有指定列名的 INSERTINSERTINTOuser_profileVALUES(1001,张三,男,28,上海,研发部);这条 SQL 完全就是靠着user_profile表现在的列顺序在跑的。一旦这个表的结构发生了变更。通常来说会出现下面这两种情况里面的一个。报错如果新增了字段。实际列数跟 VALUES 里面的数量对不上了。那就会报错。这种情况相对还好处理一点。数据写错了如果是调整了列的顺序。INSERT 的时候它不报错。但是数据写到错误的列里面去了。后面这种情况要危险得多。怎么说呢男这个字可能写到city列里面去了。上海写到gender列里面去了。程序这边呢没有任何报错。数据就这么悄悄地写错了。你要去排查的话会非常费劲。为什么因为数据本身没有 NULL。也没有类型错误。只是它的语义错掉了。正确的做法-- ✅ 永远显式指定列名INSERTINTOuser_profile(user_id,name,gender,age,city,dept)VALUES(1001,张三,男,28,上海,研发部);你把列名显式指定上以后。不管表结构后面怎么去变。加列也好。改列名也好。调整顺序也好。INSERT 的语义都不会受影响。后面要是出了问题排查起来也容易得多。团队规范所有的 INSERT 语句必须把列名显式写出来。在代码 Review 的时候。遇到没有写列名的 INSERT。直接当成 Blocker 级别的问题给打回去。五、在事务里面混着用 DDL 导致事务边界乱套了不同的数据库。它们对 DDL 语句的事务处理方式其实是存在根本差异的。这个差异在你做存储过程迁移的时候。特别容易把问题给引发出来。Oracle 里面的 DDL 隐式提交在 Oracle 里面。DDL 语句像 CREATE、DROP、ALTER、TRUNCATE 这些会自动把当前事务给提交了。这意味着什么呢意味着在 DDL 之前所有还没提交的 DML 操作。在 DDL 执行的那一下就全被自动提交了。你后面再写 ROLLBACK。是回滚不了这些操作的。-- Oracle 存储过程BEGININSERTINTOaudit_logVALUES(...);-- 未提交ALTERTABLEconfigADDCOLUMNnew_col...;-- DDL自动提交前面的 INSERT-- 后续处理EXCEPTIONWHENOTHERSTHENROLLBACK;-- 只能回滚 DDL 之后的操作END;KES 的事务内 DDL 情况KES 这边呢。它是支持事务内 DDL 回滚的。也就是说DDL 语句可以参与到事务里面去。它不会去触发隐式提交。这是符合 ANSI 标准的一个行为。比 Oracle 那种隐式提交要严格得多。也更好预期。但是呢对于那些迁移过来的旧代码。如果它的逻辑是依赖了 Oracle 的隐式提交行为的。比如有的人会故意用一个 DDL 来强制落盘前面的 DML。迁到 KES 以后呢。事务边界就变得完全不一样了。修复的原则-- ✅ 用显式 COMMIT 代替依赖 DDL 隐式提交的行为BEGININSERTINTOaudit_logVALUES(...);COMMIT;-- 显式提交明确语义-- DDL 在 COMMIT 之后执行EXECUTEALTER TABLE config ADD COLUMN new_col INTEGER;EXCEPTIONWHENOTHERSTHENROLLBACK;END;迁移的建议把所有里面带了 DDL 的存储过程都拿出来。做一遍事务边界的审计。要搞清楚每一个 COMMIT 还有 ROLLBACK 预期的范围到底是哪。用显式的事务控制语句。去替换掉那些依赖隐式行为的写法。六、聚合函数里面的 NULL 传播这个问题藏得非常隐蔽。为什么呢因为不同的聚合函数它对 NULL 的处理方式是不一样的。你要是混着用的话特别容易搞出意外的结果来。不同的聚合函数怎么处理 NULL-- 假设 score 列有 NULL 值未参加考试的学生SELECTCOUNT(*)AStotal_rows,-- 计所有行包括 score 为 NULL 的COUNT(score)ASscored_students,-- 只计非 NULL 的行SUM(score)AStotal_score,-- NULL 被忽略只加非 NULL 值AVG(score)ASavg_score,-- 平均分只算有成绩的学生忽略 NULLMAX(score)ASmax_scoreFROMexam_resultsWHEREexam_id1001;这里要特别注意的是AVG(score)。它的分母是什么呢是非 NULL 的行数。它不是总行数。假如说有 5 个学生。3 个有成绩。2 个是 NULL。AVG(score)算出来的是什么是这 3 个有成绩学生的平均分。它不是 5 个学生的平均分。如果你要把另外 2 个人算成 0 分的话那这个结果就是错的。在算全班平均分这种统计场景里面。如果业务上规定没参考的学生得算 0 分。那你还用 AVG 的话结果就是错的。正确的处理方式-- ✅ 业务上要将 NULL 视为 0 时显式转换SELECTAVG(COALESCE(score,0))ASavg_score_with_zeroFROMexam_resultsWHEREexam_id1001;-- ✅ 或者先处理 NULL再聚合SELECTAVG(score_val)FROM(SELECTCOALESCE(score,0)ASscore_valFROMexam_resultsWHEREexam_id1001)t;编码规范在做聚合计算的时候。如果字段有可能是 NULL。而且这个 NULL 在业务上是有特定含义的比如缺考算 0 分这种情况。那你必须用 COALESCE 或者 NVL 去做一下显式的转换。千万别去依赖聚合函数对 NULL 的那种默认忽略行为。通用的 SQL 编码规范建议把上面说的这些问题综合起来。我整理了下面这些 SQL 编码规范。你们可以拿来当团队日常 Code Review 的检查清单。做迁移审计的时候也能用。规范项具体要求风险等级禁止 WHERE 里面的副作用函数状态设置跟查询逻辑要严格分开分步去执行 高NOT IN 必须把 NULL 排除掉子查询来源字段不能保证没 NULL 的话改用 NOT EXISTS 高INSERT 必须把列名显式写出来别去依赖表结构的列顺序 高批处理的时间基准要变量化多事务批处理里第一次执行的时候就把时间变量固定下来别让跨事务时间基准不一致 中把事务边界显式声明出来别去依赖任何数据库的隐式提交行为 中ON 跟 WHERE 的语义要分清楚连接条件放 ON 里面结果过滤放 WHERE 里面 高NULL 值的处理要显式化聚合函数里如果 NULL 有特定业务含义用 COALESCE 显式转换一下 中函数属性要正确声明纯查询的函数声明成 STABLE 或者 IMMUTABLE有副作用的声明成 VOLATILE 中数据库问题有个很特别的地方。就是它失效的时候往往不是立刻就发生的。而是要等到某个特定的数据状态出现了。或者是特定的并发条件凑齐了。又或者是某次配置变更之后。它才会突然从潜伏的风险变成一个真实的生产事故。那些已经在你系统里跑了一两年的 SQL。只要触发它的条件没凑齐。它就会一直安安静静地待在那。跟没事人一样。所以你得先建立起对这些隐性风险的认知。在写 SQL 的时候。多留意一下语义正确性的问题。在 Code Review 的时候。把上面这个清单拿出来过一遍。这么做的话。比你去打任何补丁都要管用得多。