ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > 基于达梦的SQL等价改写优化案例精选第三部【顺势而为】① 列的类型转换

基于达梦的SQL等价改写优化案例精选第三部【顺势而为】① 列的类型转换

原创 数据库开发技术 作者：梁敬彬 时间：2025-05-26 06:48:37 0 删除 编辑

往期回顾

     SQL等价改写优化案例精选第一部【一统天下】① count(case when)的合并魔力

    SQL等价改写优化案例精选第一部【一统天下】②分析函数的穿透之力 

     SQL等价改写优化案例精选第一部【一统天下】③ group by (case when)的聚合艺术

     SQL等价改写优化案例精选第一部【一统天下】④ or 与 union all的优化交响

     SQL等价改写优化案例精选第二部【精兵简政】① 莫名其妙的行列转换

     SQL等价改写优化案例精选第二部【精兵简政】② 无脑复制粘贴

     SQL等价改写优化案例精选第二部【精兵简政】③ 有困难先将需求最小化

    SQL等价改写优化案例精选第二部【精兵简政】④ 多余写法


在前两部中，我们探讨了SQL优化的基本原则和各种技巧，包括索引利用、JOIN优化、子查询改写等。进入第三部分，我们将关注一个更加细致但同样重要的优化方向：顺势而为。

三．顺势而为

所谓"顺势而为"，在SQL优化中指的是遵循数据库引擎的工作原理，顺应其内部机制来编写查询语句，而不是与之对抗。这种思想体现在多个方面，今天我们首先讨论其中最基础的一点：保留列的纯洁性。

顺势而为的核心理念：请保留列的纯洁性，不要对列进行运算。

当我们在SQL查询中对列进行函数操作或类型转换时，实际上是在与数据库优化器的工作机制对抗，这会导致性能下降。下面通过两个实际案例来理解这一原则。

案例10 to_char（字段列）的改造

让我们看一段实际的SQL代码，特别关注其中的WHERE条件部分：

select *
  from (select rownum num, h.*
          from (select f.gather_batch_id,
                       a.logic_analyze_type,
                       a.MAIN_GATHER_ID,
                       a.ne_Id,
                       a.LOGIC_ANALYZE_RULE_ID,
                       f.ANALYZE_TASK_BATCH_ID,
                       b.REGION_NAME,
                       c.ne_Name,
                       a.SUBJECT,
                       (select list_label
                          from TP_DOMAIN_LISTVALUES
                         where domain_code = 'DOMAIN_TA_TASK_TYPE'
                           and list_value = d.TASK_TYPE) TASK_TYPE,
                       '<div style=''line-height:20px;''>' ||
                       to_char(f.SOURCE_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '<BR/>至<BR/>' ||
                       to_char(f.SOURCE_END_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '</div>' SOURCE_BEGIN_DATE,
                       to_char(f.ANALYZE_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss') ANALYZE_BEGIN_DATE,
                       a.analyze_Desc,
                       (case
                         when f.REC_CNT = null then
                          '程序出错'
                         when f.REC_CNT = -2 then
                          '程序出错'
                         when f.REC_CNT = -1 then
                          '配置脚本出错'
                         when f.REC_CNT = -8 then
                          '人工审核'
                         when f.REC_CNT = -9 then
                          '审核屏蔽'
                         when f.REC_CNT = 0 then
                          '审核通过'
                         when f.REC_CNT >= 0 then
                          '审核出错' 　else '程序出错'
                       end) as rec_cnt,
                       decode(a.DR_ID,
                              0,
                              ' ',
                              (select list_label
                                 from TP_DOMAIN_LISTVALUES
                                where domain_code = 'DOMAIN_DR_ID_FLAG'
                                  and list_value = a.DR_ID || '')) DR_ID
                  from (select A.*, B.path, B.ne_name last_ne_name
                          from LOGIC_ANALYZE_RULE A, net_element B
                         where A.ne_id = B.ne_id) a,
                       MANAGE_REGION b,
                       NET_ELEMENT c,
                       NET_ELEMENT g,
                       LOGIC_GATHER_TASK d,
                       ANALYZE_TASK_BATCH f
                 where 1 = 1
                   and a.region_Id = b.REGION_ID
                   and a.ne_Id = c.NE_ID
                   and a.LOGIC_DATA_GATHER_ID = d.LOGIC_DATA_GATHER_ID
                   and a.LOGIC_ANALYZE_RULE_ID = f.LOGIC_ANALYZE_RULE_ID
                   and a.path like g.path || '%'
                   and g.ne_id = '4'
                   and ((to_char(f.SOURCE_BEGIN_DATE, 'yyyy-mm-dd') >=
                       '2024-01-04') or
                       (to_char(f.SOURCE_END_DATE, 'yyyy-mm-dd') >=
                       '2024-01-04'))
                   and ((to_char(f.SOURCE_BEGIN_DATE, 'yyyy-mm-dd') <=
                       '2024-01-06') OR
                       (to_char(f.SOURCE_END_DATE, 'yyyy-mm-dd') <=
                       '2024-01-06'))
                   and (to_char(f.ANALYZE_BEGIN_DATE, 'yyyy-mm-dd') >=
                       '2024-01-04')
                   and (to_char(f.ANALYZE_BEGIN_DATE, 'yyyy-mm-dd') <=
                       '2024-01-06')
                   and a.report_Type = '数据处理'
                 order by ANALYZE_BEGIN_DATE desc,
                          ne_Id,
                          REC_CNT desc,
                          SUBJECT) h)
 where num >= 1
   and num <= 10

问题分析

在WHERE子句中，我们发现了大量对日期字段使用TO_CHAR函数的情况：

```sql
 and ((to_char(f.SOURCE_BEGIN_DATE, 'yyyy-mm-dd') >=
                       '2024-01-04') or
                       (to_char(f.SOURCE_END_DATE, 'yyyy-mm-dd') >=
                       '2024-01-04'))
                   and ((to_char(f.SOURCE_BEGIN_DATE, 'yyyy-mm-dd') <=
                       '2024-01-06') OR
                       (to_char(f.SOURCE_END_DATE, 'yyyy-mm-dd') <=
                       '2024-01-06'))
                   and (to_char(f.ANALYZE_BEGIN_DATE, 'yyyy-mm-dd') >=
                       '2024-01-04')
                   and (to_char(f.ANALYZE_BEGIN_DATE, 'yyyy-mm-dd') <=
                       '2024-01-06')

这种写法存在严重问题：

 1. 无法利用索引：当对列应用函数（如TO_CHAR）时，数据库无法使用该列的索引，导致全表扫描

 2. 类型不匹配：将日期类型转为字符串后进行比较，产生额外的转换开销

 3. 语义不清晰：日期操作应该使用日期类型的比较方式，而不是字符串比较

优化方案

正确的做法是：不对列进行函数操作，而是对比较值进行转换。

and (f.SOURCE_BEGIN_DATE>=to_date('2024-01-04','yyyy-mm-dd') or
                f.SOURCE_END_DATE >=to_date('2024-01-04','yyyy-mm-dd'))
               and (f.SOURCE_BEGIN_DATE <to_date('2024-01-06','yyyy-mm-dd')+1 OR
                   f.SOURCE_END_DATE <to_date('2024-01-06','yyyy-mm-dd')+1)
               and f.ANALYZE_BEGIN_DATE <to_date('2024-01-04','yyyy-mm-dd') +1

这样修改的好处是：

 1. 可以使用索引：列没有被函数操作，保持了原始形态，可以使用索引

 2. 减少转换开销：常量TO_DATE只需要执行一次，而不是对每一行数据都执行TO_CHAR

 3. 语义更清晰：日期比较使用日期类型，逻辑更自然

同时，需要创建相应的索引来支持查询：

 CREATE INDEX IDX_AN_TASK_BAT_BEGTIM ON ANALYZE_TASK_BATCH (SOURCE_BEGIN_DATE);
 CREATE INDEX IDX_AN_TASK_BAT_ENDTIM ON ANALYZE_TASK_BATCH (SOURCE_END_DATE);
 CREATE INDEX IDX_AN_TASK_BAT_ANY_BEGTIM ON ANALYZE_TASK_BATCH (ANALYZE_BEGIN_DATE);

优化后的完整SQL

select *
  from (select rownum num, h.*
          from (select f.gather_batch_id,
                       a.logic_analyze_type,
                       a.MAIN_GATHER_ID,
                       a.ne_Id,
                       a.LOGIC_ANALYZE_RULE_ID,
                       f.ANALYZE_TASK_BATCH_ID,
                       b.REGION_NAME,
                       c.ne_Name,
                       a.SUBJECT,
                       (select list_label
                          from TP_DOMAIN_LISTVALUES
                         where domain_code = 'DOMAIN_TA_TASK_TYPE'
                           and list_value = d.TASK_TYPE) TASK_TYPE,
                       '<div style=''line-height:20px;''>' ||
                       to_char(f.SOURCE_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '<BR/>至<BR/>' ||
                       to_char(f.SOURCE_END_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '</div>' SOURCE_BEGIN_DATE,
                       to_char(f.ANALYZE_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss') ANALYZE_BEGIN_DATE,
                       a.analyze_Desc,
                       (case
                         when f.REC_CNT = null then
                          '程序出错'
                         when f.REC_CNT = -2 then
                          '程序出错'
                         when f.REC_CNT = -1 then
                          '配置脚本出错'
                         when f.REC_CNT = -8 then
                          '人工审核'
                         when f.REC_CNT = -9 then
                          '审核屏蔽'
                         when f.REC_CNT = 0 then
                          '审核通过'
                         when f.REC_CNT >= 0 then
                          '审核出错' 　else '程序出错'
                       end) as rec_cnt,
                       decode(a.DR_ID,
                              0,
                              ' ',
                              (select list_label
                                 from TP_DOMAIN_LISTVALUES
                                where domain_code = 'DOMAIN_DR_ID_FLAG'
                                  and list_value = a.DR_ID || '')) DR_ID
                  from (select A.*, B.path, B.ne_name last_ne_name
                          from LOGIC_ANALYZE_RULE A, net_element B
                         where A.ne_id = B.ne_id) a,
                       MANAGE_REGION b,
                       NET_ELEMENT c,
                       NET_ELEMENT g,
                       LOGIC_GATHER_TASK d,
                       ANALYZE_TASK_BATCH f
                 where 1 = 1
                   and a.region_Id = b.REGION_ID
                   and a.ne_Id = c.NE_ID
                   and a.LOGIC_DATA_GATHER_ID = d.LOGIC_DATA_GATHER_ID
                   and a.LOGIC_ANALYZE_RULE_ID = f.LOGIC_ANALYZE_RULE_ID
                   and a.path like g.path || '%'
                   and g.ne_id = '4'
                      and (f.SOURCE_BEGIN_DATE>=to_date('2024-01-04','yyyy-mm-dd') or
                    f.SOURCE_END_DATE >=to_date('2024-01-04','yyyy-mm-dd'))
                   and (f.SOURCE_BEGIN_DATE <to_date('2024-01-06','yyyy-mm-dd')+1 OR
                       f.SOURCE_END_DATE <to_date('2024-01-06','yyyy-mm-dd')+1)
                   and f.ANALYZE_BEGIN_DATE <to_date('2024-01-04','yyyy-mm-dd') +1
                   and f.ANALYZE_BEGIN_DATE >=to_date('2024-01-04','yyyy-mm-dd') 
                   and a.report_Type = '数据处理'
                 order by ANALYZE_BEGIN_DATE desc,
                          ne_Id,
                          REC_CNT desc,
                          SUBJECT) h)
 where num >= 1
   and num <= 10


优化后的查询执行时间从原来的长时间等待降低到了约2秒钟，效率显著提升。

案例11 trunc(字段列)的改造

看一个使用TRUNC函数的案例：

select sum(a.store_size), TRUNC(a.generate_time) generateTime
  from idep_goods_log a, idep_goods b, idep_product c
 where (a.goods_id = b.goods_id(+) and b.goods_id is not null)
   and (b.prod_id = c.prod_id(+) and c.prod_id is not null)
   and c.prod_id = 123
   and TRUNC(a.generate_time) >= TO_DATE('2024-08-02', 'YYYY-MM-DD')
   AND TRUNC(a.generate_time) <= TO_DATE('2024-08-03', 'YYYY-MM-DD')
 group by TRUNC(a.generate_time)
 order by TRUNC(a.generate_time)

问题分析

同样，我们在WHERE条件中看到了对列的函数操作：

and TRUNC(a.generate_time) >= TO_DATE('2024-08-02', 'YYYY-MM-DD')
AND TRUNC(a.generate_time) <= TO_DATE('20240-08-03', 'YYYY-MM-DD')

问题与前一个案例类似：

 1. 对a.generate_time应用TRUNC函数使得无法使用该列的索引

 2. 每一行数据都需要执行TRUNC函数，增加计算开销

优化方案

正确的做法是将条件改写为不对列进行函数操作的形式：

and a.generate_time >= TO_DATE('2024-08-02', 'YYYY-MM-DD')
AND a.generate_time < TO_DATE('2024-08-03', 'YYYY-MM-DD')+1

这样修改后：

 1. 可以使用a.generate_time列的索引

 2. 减少了对每行数据执行TRUNC函数的开销

 3. 语义上等价，因为: 

     a.generate_time >= '2024-08-02 00:00:00' 等价于 TRUNC(a.generate_time)>= '2024-08-02'

     a.generate_time < '2024-08-04 00:00:00' 等价于 TRUNC(a.generate_time) <=   '2024-08-03' 优化后的SQL

select count(*) num1, TRUNC(a.generate_time) generateTime
  from idep_goods_log a, idep_goods b, idep_product c
 where (a.goods_id = b.goods_id(+) and b.goods_id is not null)
   and (b.prod_id = c.prod_id(+) and c.prod_id is not null)
   and c.prod_id = 123
   and a.generate_time >= TO_DATE('2024-08-02', 'YYYY-MM-DD')
   AND a.generate_time < TO_DATE('2024-08-03', 'YYYY-MM-DD')+1
 group by TRUNC(a.generate_time)
 order by TRUNC(a.generate_time)



未完待续...

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



公众号：收获不止数据库

微信：ljbyxl1213

系列回顾


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9091385/viewspace-3085690/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 数据处理进化史之小明历险记①：从Excel困局到集中式数据库的飞跃
下一篇： 基于达梦的SQL等价改写优化案例精选第三部【顺势而为】(大结局）② 等价改写中的陷阱
请登录后发表评论 登录
全部评论
梁敬彬
专家姓名：梁敬彬
历任中电福富研究院副理事长、中盾安信研究院副院长、宁德时代技术顾问等职务，受聘电信集团专家和国家十四五课题负责人，著有《收获，不止Oracle》《收获，不止SQL优化》等多本畅销数据库技术书籍。

注册时间：2024-02-06

  • 博文量
    162
  • 访问量
    198194

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593