SQL等价改写优化案例精选第三部【顺势而为】(大结局）② 等价改写中的陷阱

梁敬彬梁敬弘兄弟出品

往期回顾

SQL等价改写优化案例精选第一部【一统天下】① count(case when)的合并魔力
SQL等价改写优化案例精选第一部【一统天下】②分析函数的穿透之力
SQL等价改写优化案例精选第一部【一统天下】③ group by (case when)的聚合艺术
SQL等价改写优化案例精选第一部【一统天下】④ or 与 union all的优化交响
SQL等价改写优化案例精选第二部【精兵简政】① 莫名其妙的行列转换
SQL等价改写优化案例精选第二部【精兵简政】 ② 无脑复制粘贴
SQL等价改写优化案例精选第二部【精兵简政】③ 有困难先将需求最小化
SQL等价改写优化案例精选第二部【精兵简政】④ 多余写法
SQL等价改写优化案例精选第三部【顺势而为】① 列的类型转换

在上一节中，我们讨论了保持列纯洁性的重要性，避免在查询条件中对列进行函数操作。继续"顺势而为"的主题，本节我们将深入探讨列的数据类型问题，以及在改写SQL时如何确保真正的等价。

案例12. 为什么一定要改变列的类型

select to_char(p_type) 产品类别,
       to_char(p_id) 产品编码,
       to_char(tag_id) 目标数据ID,
       to_char(p_name) 产品名称,
       to_char(ih1.ip) 源IP,
       to_char(ih1.port) 源端口,
       to_char(id1.store_dir) 源目录,
       to_char(ih2.ip) 目标IP,
       to_char(ih2.port) 目标端口,
       to_char(id2.store_dir) 目标目录,
       to_char(p_cycle) 周期,
       to_char(p_state) 状态
  from idep_data id1,
       idep_host ih1,
       idep_data id2,
       idep_host ih2,
       (select iss.sched_id sch_id,
               prod_type p_type,
               ip.prod_id p_id,
               src_ds_id src_id,
               tag_ds_id tag_id,
               prod_name p_name,
               iss.interval p_cycle,
               decode(iss.state, '0SA', 1, '0SX', 0) p_state
          from idep_schedule       iss,
               idep_sched_param    isp,
               idep_prod_flow_rela ipfr,
               idep_product        ip
         where iss.sched_id = isp.sched_id
           and isp.prod_flow_id = ipfr.prod_flow_id
           and ipfr.prod_id = ip.prod_id) i_sched
 where i_sched.src_id = id1.data_id
   and id1.host_id = ih1.host_id
   and i_sched.tag_id = id2.data_id
   and id2.host_id = ih2.host_id;

问题分析

这段SQL的主要问题是：将所有的列都使用TO_CHAR函数转换为字符串类型。这种做法存在以下严重问题：

1. 性能损失：每一个TO_CHAR转换都需要CPU计算资源，特别是在大结果集中
2. 类型安全丧失：原始数据类型（如数字、日期）的所有优势都丢失了
3. 内存浪费：字符串类型通常比原始数据类型占用更多存储空间
4. 排序问题：如果需要排序，字符串排序和数字排序的结果可能不同，例如：数字排序：1, 2, 10, 20；字符串排序：1, 10, 2, 20
5. 后续处理困难：应用程序收到全是字符串类型的数据，可能需要再次转换回原始类型

优化方案

正确的做法应该是：保留每个列的原始数据类型，只在必要时进行转换。

SELECT TF.FLOW_ID 流程ID,
       TF.FLOW_NAME 流程名称,
       TFM.FLOW_NAME 流程模板名称,
       STAFF1.STAFF_NAME  流程创建人名称,
       STAFF2.STAFF_NAME   流程状态员工,
       TF.CREATE_DATE 创建日期,
       TF.STATE 流程状态名称,
       TF.STATE_DATE 流程状态日期,
       TT.TCH_ID 环节ID,
       TT.TCH_NAME 环节名称,
       TT.TCH_MOD 环节类型名称,
       TT.PERSON 接收任务员工,
       TT.TCH_CONT_DESC 环节内容,
       TT.RESULT_ID 环节执行结果,
       TT.LATE_REASON 影响本环节原因,
       STAFF3.STAFF_NAME   环节执行人,
       TT.SLA_DATE 考核时间点,
       TT.SLA_TIME 考核时间,
       TT.CREATED_DATE 环节创建日期,
       TT.BEGIN_DATE 环节启动日期,
       TT.FINISH_DATE 环节结束日期,
       TT.STATE 环节状态名称,
       NVL2(TT.SUB_FLOW_ID,
            (SELECT TF.FLOW_NAME
               FROM FLOW TF
              WHERE TF.FLOW_ID = TT.SUB_FLOW_ID
                AND TF.FLOW_ID = TT.FLOW_ID),
            0) 子流程名称
  FROM FLOW TF,TACHE TT,FLOW_MODEL TFM,STAFF STAFF1 ,STAFF STAFF2,STAFF STAFF3
 WHERE TF.FLOW_ID = TT.FLOW_ID
   AND TF.BEGIN_TCH_ID = TT.TCH_ID
   AND TF.FLOW_MOD = TFM.FLOW_MOD
   AND TF.STAFF_ID=STAFF1.STAFF_ID
   AND TF.STATE_STAFF=STAFF2.STAFF_ID
   AND TT.STAFF_ID=STAFF3.STAFF_ID

如果前端应用确实需要字符串格式，更好的做法是在应用层进行转换，而不是在数据库层。这样既保留了数据库操作的效率，又满足了应用的需求。

从类型一致到语义一致：等价改写中的陷阱

保持类型一致性只是SQL优化的一个方面。在进行等价改写时，我们不仅要关注类型的一致，还需要确保语义的一致，特别是在处理NULL值等特殊情况时。以下是一个真实案例，说明等价改写中常见的陷阱。

总结说明，读懂需求，等价改写，确实是优化SQL最常用的手段，但是我们也要注意，等价改写是有技巧的，等价改写时，我们要小心不等价的情况，举个案例如下：

开发人员完成如下SQL：

SELECT TF.FLOW_ID 流程ID,
       TF.FLOW_NAME 流程名称,
       TFM.FLOW_NAME 流程模板名称,
       STAFF1.STAFF_NAME  流程创建人名称,
       STAFF2.STAFF_NAME   流程状态员工,
       TF.CREATE_DATE 创建日期,
       TF.STATE 流程状态名称,
       TF.STATE_DATE 流程状态日期,
       TT.TCH_ID 环节ID,
       TT.TCH_NAME 环节名称,
       TT.TCH_MOD 环节类型名称,
       TT.PERSON 接收任务员工,
       TT.TCH_CONT_DESC 环节内容,
       TT.RESULT_ID 环节执行结果,
       TT.LATE_REASON 影响本环节原因,
       STAFF3.STAFF_NAME   环节执行人,
       TT.SLA_DATE 考核时间点,
       TT.SLA_TIME 考核时间,
       TT.CREATED_DATE 环节创建日期,
       TT.BEGIN_DATE 环节启动日期,
       TT.FINISH_DATE 环节结束日期,
       TT.STATE 环节状态名称,
       NVL2(TT.SUB_FLOW_ID,
            (SELECT TF.FLOW_NAME
               FROM FLOW TF
              WHERE TF.FLOW_ID = TT.SUB_FLOW_ID
                AND TF.FLOW_ID = TT.FLOW_ID),
            0) 子流程名称
  FROM FLOW TF,TACHE TT,FLOW_MODEL TFM,STAFF STAFF1 ,STAFF STAFF2,STAFF STAFF3
 WHERE TF.FLOW_ID = TT.FLOW_ID
   AND TF.BEGIN_TCH_ID = TT.TCH_ID
   AND TF.FLOW_MOD = TFM.FLOW_MOD
   AND TF.STAFF_ID=STAFF1.STAFF_ID
   AND TF.STATE_STAFF=STAFF2.STAFF_ID
   AND TT.STAFF_ID=STAFF3.STAFF_ID

后来，开发人员将上述语句修改为标量子查询，具体如下：

SELECT TF.FLOW_ID 流程ID,
       TF.FLOW_NAME 流程名称,
       TFM.FLOW_NAME 流程模板名称,
       (SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TF.STAFF_ID) 流程创建人名称,
       (SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TF.STATE_STAFF) 流程状态员工,
       TF.CREATE_DATE 创建日期,
       TF.STATE 流程状态名称,
       TF.STATE_DATE 流程状态日期,
       TT.TCH_ID 环节ID,
       TT.TCH_NAME 环节名称,
       TT.TCH_MOD 环节类型名称,
       TT.PERSON 接收任务员工,
       TT.TCH_CONT_DESC 环节内容,
       TT.RESULT_ID 环节执行结果,
       TT.LATE_REASON 影响本环节原因,
       (SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TT.STAFF_ID) 环节执行人,
       TT.SLA_DATE 考核时间点,
       TT.SLA_TIME 考核时间,
       TT.CREATED_DATE 环节创建日期,
       TT.BEGIN_DATE 环节启动日期,
       TT.FINISH_DATE 环节结束日期,
       TT.STATE 环节状态名称,
       NVL2(TT.SUB_FLOW_ID,
            (SELECT TF.FLOW_NAME
               FROM FLOW TF
              WHERE TF.FLOW_ID = TT.SUB_FLOW_ID
                AND TF.FLOW_ID = TT.FLOW_ID),
            0) 子流程名称
  FROM FLOW TF,TACHE TT,FLOW_MODEL TFM
 WHERE TF.FLOW_ID = TT.FLOW_ID
   AND TF.BEGIN_TCH_ID = TT.TCH_ID
   AND TF.FLOW_MOD = TFM.FLOW_MOD

关键差异

乍看之下，两个SQL语句似乎只是写法不同，功能相同。但实际上存在一个关键的差异：

版本1使用了六表连接，包括三个STAFF表的内连接：

FROM FLOW TF,TACHE TT,FLOW_MODEL TFM,STAFF STAFF1 ,STAFF STAFF2,STAFF STAFF3
WHERE ...
   AND TF.STAFF_ID=STAFF1.STAFF_ID
   AND TF.STATE_STAFF=STAFF2.STAFF_ID
   AND TT.STAFF_ID=STAFF3.STAFF_ID

版本2只使用了三表连接，而员工信息通过子查询获取：

FROM FLOW TF,TACHE TT,FLOW_MODEL TFM
WHERE ...

– 员工信息通过子查询获取:
(SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TF.STAFF_ID) 流程创建人名称,
问题分析：这两种写法等价吗？

问题来了，这两种写法等价吗？其实是不等价的！如果TF.STAFF_ID可能为空，必须该用外连接才等价，另一个TT.STAF_ID 也是如此!

验证实验

我们可以试验如下：

DROP TABLE TEST111;
CREATE TABLE TEST111 (ID INT );
INSERT  INTO TEST111 VALUES (1);
INSERT  INTO TEST111 VALUES (2);
INSERT  INTO TEST111 VALUES (3);
COMMIT;
DROP TABLE TEST222;
CREATE TABLE TEST222 (ID INT );
INSERT  INTO TEST222 VALUES (1);
INSERT  INTO TEST222 VALUES (2);
INSERT  INTO TEST222 VALUES (NULL);
COMMIT;

如下两种写法等价：

SELECT T1.ID ,(SELECT T2.ID FROM TEST222 T2 WHERE ID=T1.ID) FROM TEST111 T1;

结果：

ID (SELECTT2.IDFROMTEST222T2WHERE
-- ----------------------------------
 1                               1
 2                               2
 3       

SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID(+);

结果：

ID ID
-- --
 1  1
 2  2
 3   

如果不写成外连接，将不等价：

SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID;

结果：

ID ID
-- --
 1  1
 2  2

从测试结果可以明确看出：

使用标量子查询的SQL与使用外连接(+)的SQL结果相同，都包含了ID=3的记录，虽然对应的值为空
而使用内连接的SQL则只返回了ID为1和2的记录，ID=3的记录被排除了
这证明了我们的论点：标量子查询的等价写法是外连接，而不是内连接。在进行SQL改写时，如果不注意这一点，很可能会导致查询结果的不一致。

总结：SQL等价改写的关键原则

从以上案例，我们可以总结出SQL等价改写时需要注意的几个关键原则：

类型一致性：

• 保留列的原始数据类型
• 避免不必要的类型转换，特别是对大量数据的全列转换
• 如需转换，优先在应用层而非数据库层进行

语义一致性：

• 内连接与标量子查询在NULL值处理上有根本区别
• 标量子查询等价于外连接，而非内连接
• 改写前后必须确保在NULL值处理上的行为一致

测试验证：

• 对改写的SQL进行充分测试，特别是边界情况
• 使用测试数据验证NULL值、特殊值的处理
• 不要仅凭直觉判断两个SQL是否等价

在SQL优化中，"顺势而为"不仅要求我们遵循数据库引擎的工作原理，还需要尊重数据的本质特性，包括类型特征和NULL值语义。只有全面理解这些概念，才能进行既正确又高效的SQL等价改写。记住：改写不仅要追求性能，更要确保语义的真正等价。

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列