经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 程序设计 » SQL语言 » 查看文章
如何利用分析函数改写范围判断自关联查询详解
来源:jb51  时间:2018/10/15 8:59:27  对本文有异议

前言

最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:

Stat NameStatement TotalPer Execution% Snap Total
Elapsed Time (ms)363,741363,740.788 .42
CPU Time (ms)362,770362,770.008 .81
Executions1  
Buffer Gets756756.000.00
Disk Reads00.000.00
Parse Calls11.000.01
Rows50,82550,825.00 
User I/O Wait Time (ms)0 
Cluster Wait Time (ms)0  
Application Wait Time (ms)0  
Concurrency Wait Time (ms)0  
Invalidations0  
Version Count1  
Sharable Mem(KB)28  

从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:


IdOperationNameRowsBytesTempSpcCost (%CPU)Time
0SELECT STATEMENT   
1226 (100) 
1   SORT ORDER BY 493793375K3888K1226 (2)00:00:05
2     HASH JOIN ANTI 493793375K2272K401 (3)00:00:02
3       TABLE ACCESS FULLT_NUM493791687K 88 (4)00:00:01
4       TABLE ACCESS FULLT_NUM493791687K 88 (4)00:00:01

从执行计划看,Oracle选择了HASH JOIN ANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。

将原SQL进行简单脱密改写后, SQL文本类似如下:

  1. SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
  2. FROM T_NUM A
  3. WHERE NOT EXISTS (
  4. SELECT 1
  5. FROM T_NUM B
  6. WHERE B.BEGIN <= A.BEGIN
  7. AND B.END >= A.END
  8. AND B.ROWID != A.ROWID
  9. AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

如果分析SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。

简单一点说,表中的记录表示的是由BEGIN开始到END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段BEGIN的长度的比较。

显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:

  1. SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;
  2.  
  3.  
  4.  
  5. LENGTH(BEGIN) COUNT(*)
  6.  
  7. ————- ———-
  8.  
  9. 12  22096
  10.  
  11. 11  9011
  12.  
  13. 13  8999
  14.  
  15. 14  8186
  16.  
  17. 16   49
  18.  
  19. 9   45
  20.  
  21. 8   41
  22.  
  23. 7   27

大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,设置关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。

再来看一下具体的SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。

那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。

SQL改写结果如下:

  1. SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
  2. FROM (
  3. SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
  4. ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
  5. FROM
  6. (
  7. SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
  8. FROM T_NUM
  9. )
  10. )
  11. WHERE RN = 1
  12. AND CN = 1;

简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。

改写后,这个SQL避免对自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:

  1. SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
  2.  
  3. 2 FROM T_NUM A
  4.  
  5. 3 WHERE NOT EXISTS (
  6.  
  7. 4  SELECT 1
  8.  
  9. 5  FROM T_NUM B
  10.  
  11. 6  WHERE B.BEGIN <= A.BEGIN
  12.  
  13. 7  AND B.END >= A.END
  14.  
  15. 8  AND B.ROWID != A.ROWID
  16.  
  17. 9  AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))
  18.  
  19. 10 ;
  20.  
  21.  
  22.  
  23. 48344 rows selected.
  24.  
  25.  
  26.  
  27. Elapsed: 00:00:57.68
  28.  
  29.  
  30.  
  31. Execution Plan
  32.  
  33. ———————————————————-
  34.  
  35. Plan hash value: 2540751655
  36.  
  37.  
  38.  
  39. ————————————————————————————
  40.  
  41. | Id | Operation   | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |
  42.  
  43. ————————————————————————————
  44.  
  45. | 0 | SELECT STATEMENT |  | 48454 | 1703K|  | 275 (1)| 00:00:04 |
  46.  
  47. |* 1 | HASH JOIN ANTI |  | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |
  48.  
  49. | 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K|  | 68 (0)| 00:00:01 |
  50.  
  51. | 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K|  | 68 (0)| 00:00:01 |
  52.  
  53. ————————————————————————————
  54.  
  55.  
  56.  
  57. Predicate Information (identified by operation id):
  58.  
  59. —————————————————
  60.  
  61.  
  62.  
  63. 1  access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))
  64.  
  65. filter(“B”.”BEGIN”<=”A”.”BEGIN AND B”.”END”>=”A”.”END AND
  66.  
  67. B”.ROWID<>”A”.ROWID)
  68.  
  69.  
  70.  
  71.  
  72.  
  73. Statistics
  74.  
  75. ———————————————————-
  76.  
  77. 0 recursive calls
  78.  
  79. 0 db block gets
  80.  
  81. 404 consistent gets
  82.  
  83. 0 physical reads
  84.  
  85. 0 redo size
  86.  
  87. 2315794 bytes sent via SQL*Net to client
  88.  
  89. 35966 bytes received via SQL*Net from client
  90.  
  91. 3224 SQL*Net roundtrips to/from client
  92.  
  93. 0 sorts (memory)
  94.  
  95. 0 sorts (disk)
  96.  
  97. 48344 rows processed
  98.  
  99.  
  100.  
  101. SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
  102.  
  103. 2 FROM (
  104.  
  105. 3  SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
  106.  
  107. 4    ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
  108.  
  109. 5  FROM
  110.  
  111. 6  (
  112.  
  113. 7    SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
  114.  
  115. 8    FROM T_NUM
  116.  
  117. 9  )
  118.  
  119. 10 )
  120.  
  121. 11 WHERE RN = 1
  122.  
  123. 12 AND CN = 1;
  124.  
  125.  
  126.  
  127. 48344 rows selected.
  128.  
  129.  
  130.  
  131. Elapsed: 00:00:00.72
  132.  
  133.  
  134.  
  135. Execution Plan
  136.  
  137. ———————————————————-
  138.  
  139. Plan hash value: 1546715670
  140.  
  141.  
  142.  
  143. ——————————————————————————————
  144.  
  145. | Id | Operation    | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |
  146.  
  147. ——————————————————————————————
  148.  
  149. | 0 | SELECT STATEMENT   |  | 48454 | 2460K|  | 800 (1)| 00:00:10 |
  150.  
  151. |* 1 | VIEW     |  | 48454 | 2460K|  | 800 (1)| 00:00:10 |
  152.  
  153. |* 2 | WINDOW SORT PUSHED RANK|  | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |
  154.  
  155. | 3 | WINDOW BUFFER   |  | 48454 | 1845K|  | 800 (1)| 00:00:10 |
  156.  
  157. | 4 |  VIEW     |  | 48454 | 1845K|  | 311 (1)| 00:00:04 |
  158.  
  159. | 5 |  WINDOW SORT   |  | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |
  160.  
  161. | 6 |  TABLE ACCESS FULL | T_NUM | 48454 | 662K|  | 68 (0)| 00:00:01 |
  162.  
  163. ——————————————————————————————
  164.  
  165.  
  166.  
  167. Predicate Information (identified by operation id):
  168.  
  169. —————————————————
  170.  
  171.  
  172.  
  173. 1  filter(“RN”=1 AND CN”=1)
  174.  
  175. 2  filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END
  176.  
  177. ORDER BY BEGIN”)<=1)
  178.  
  179.  
  180.  
  181.  
  182.  
  183. Statistics
  184.  
  185. ———————————————————-
  186.  
  187. 0 recursive calls
  188.  
  189. 0 db block gets
  190.  
  191. 202 consistent gets
  192.  
  193. 0 physical reads
  194.  
  195. 0 redo size
  196.  
  197. 1493879 bytes sent via SQL*Net to client
  198.  
  199. 35966 bytes received via SQL*Net from client
  200.  
  201. 3224 SQL*Net roundtrips to/from client
  202.  
  203. 3 sorts (memory)
  204.  
  205. 0 sorts (disk)
  206.  
  207. 48344 rows processed

原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对w3xue的支持。

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号