如何利用分析函數(shù)改寫范圍判斷自關(guān)聯(lián)查詢?cè)斀?/h1>

 更新時(shí)間：2018年10月13日 11:28:58   作者：yangtingkun  

這篇文章主要給大家介紹了關(guān)于如何利用分析函數(shù)改寫范圍判斷自關(guān)聯(lián)查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用sql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

前言

最近碰到一個(gè)單條SQL運(yùn)行效率不佳導(dǎo)致數(shù)據(jù)庫(kù)整體運(yùn)行負(fù)載較高的問題。

分析、定位數(shù)據(jù)庫(kù)的主要負(fù)載是這條語句引起的過程相對(duì)簡(jiǎn)單，通過AWR報(bào)告就可以比較容易的完成定位，這里就不贅述了。

現(xiàn)在直接看一下這個(gè)導(dǎo)致性能問題的SQL語句，其對(duì)應(yīng)的SQL REPORT統(tǒng)計(jì)如下：

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	363,741	363,740.78	8 .42
CPU Time (ms)	362,770	362,770.00	8 .81
Executions	1
Buffer Gets	756	756.00	0.00
Disk Reads	0	0.00	0.00
Parse Calls	1	1.00	0.01
Rows	50,825	50,825.00
User I/O Wait Time (ms)	0
Cluster Wait Time (ms)	0
Application Wait Time (ms)	0
Concurrency Wait Time (ms)	0
Invalidations	0
Version Count	1
Sharable Mem(KB)	28

從SQL的性能指標(biāo)上看，其單次執(zhí)行需要6分鐘左右，處理5萬多條記錄，邏輯度只有756，主要消耗時(shí)間在CPU上。而這里就存在疑點(diǎn)，邏輯讀如此之低，而CPU時(shí)間花費(fèi)又如此之高，那么這些CPU都消耗在哪里呢？當(dāng)然這個(gè)問通過SQL的統(tǒng)計(jì)信息中是找不到答案的，我們下面關(guān)注SQL的執(zhí)行計(jì)劃：

Id	Operation	Name	Rows	Bytes	TempSpc	Cost (%CPU)	Time
0	SELECT STATEMENT					1226 (100)
1	SORT ORDER BY		49379	3375K	3888K	1226 (2)	00:00:05
2	HASH JOIN ANTI		49379	3375K	2272K	401 (3)	00:00:02
3	TABLE ACCESS FULL	T_NUM	49379	1687K		88 (4)	00:00:01
4	TABLE ACCESS FULL	T_NUM	49379	1687K		88 (4)	00:00:01

從執(zhí)行計(jì)劃看，Oracle選擇了HASH JOIN ANTI，JOIN的兩張表都是T_NUM，且都采用了全表掃描，并未選擇索引。僅靠執(zhí)行計(jì)劃也只等得到上面的結(jié)論，至于為什么不選擇索引，以及為什么執(zhí)行時(shí)間過長(zhǎng)，還需要進(jìn)一步的分析。

將原SQL進(jìn)行簡(jiǎn)單脫密改寫后， SQL文本類似如下：

SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
FROM T_NUM A
WHERE NOT EXISTS (
SELECT 1
FROM T_NUM B
WHERE B.BEGIN <= A.BEGIN
AND B.END >= A.END
AND B.ROWID != A.ROWID
AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

如果分析SQL語句，會(huì)發(fā)現(xiàn)這是一個(gè)自關(guān)聯(lián)語句，在BEGIN字段長(zhǎng)度相等的前提下，想要找到哪些不存在BEGIN比當(dāng)前記錄BEGIN小且END比當(dāng)前記錄END大的記錄。

簡(jiǎn)單一點(diǎn)說，表中的記錄表示的是由BEGIN開始到END截至的范圍，那么當(dāng)前想要獲取的結(jié)果是找出哪些沒有范圍所包含的范圍。需要注意的是，對(duì)于當(dāng)前的SQL邏輯，如果存在兩條范圍完全相同的記錄，那么最終這兩條記錄都會(huì)被舍棄。

業(yè)務(wù)的邏輯并不是特別復(fù)雜，但是要解決一條記錄與其他記錄進(jìn)行比較，多半采用的方法是自關(guān)聯(lián)，而在這個(gè)自關(guān)聯(lián)中，既有大于等于又有小于等于，還有不等于，僅有的一個(gè)等于的關(guān)聯(lián)條件，來自范圍段BEGIN的長(zhǎng)度的比較。

顯而易見的是，如果是范圍段本身的比較，其選擇度一般還是不錯(cuò)的，但是如果只是比較其長(zhǎng)度，那么無疑容易產(chǎn)生大量的重復(fù)，比如在這個(gè)例子中：

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;

 

LENGTH(BEGIN) COUNT(*)

————- ———-

12  22096

11  9011

13  8999

14  8186

16   49

9   45

8   41

7   27

大量重復(fù)的數(shù)據(jù)出現(xiàn)在長(zhǎng)度為11到14的范圍上，在這種情況下，僅有的一個(gè)等值判斷條件LENGTH(BEGIN)是非常低效的，這時(shí)一條記錄根據(jù)這個(gè)等值條件會(huì)關(guān)聯(lián)到近萬條記錄，設(shè)置關(guān)聯(lián)到兩萬多條記錄，顯然大量的實(shí)踐消耗在低效的連接過程中。

再來看一下具體的SQL語句，會(huì)發(fā)現(xiàn)幾乎沒有辦法建立索引，因?yàn)長(zhǎng)ENGTH(BEGIN)的選擇度非常查，而其他的條件都是不等查詢，選擇度也不會(huì)好，即使建立索引，強(qiáng)制執(zhí)行選擇索引，效率也不會(huì)好。

那么如果想要繼續(xù)優(yōu)化這個(gè)SQL，就只剩下一個(gè)辦法，那就是SQL的改寫。對(duì)于自關(guān)聯(lián)查詢而言，最佳的改寫方法是利用分析函數(shù)，其強(qiáng)大的行級(jí)處理能力，可以在一次掃描過程中獲得一條記錄與其他記錄的關(guān)系，從而消除了自關(guān)聯(lián)的必要性。

SQL改寫結(jié)果如下：

SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
FROM (
SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
FROM
(
SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
FROM T_NUM
)
)
WHERE RN = 1
AND CN = 1;

簡(jiǎn)單的說，內(nèi)層的分析函數(shù)MAX用來根據(jù)BEGIN從小到大，END從大到小的條件，確定每個(gè)范圍對(duì)應(yīng)的最大的END的值。而外層的兩個(gè)分析函數(shù)，COUNT用來去掉完全重復(fù)的記錄，而ROW_NUMBER用來獲取范圍最大的記錄（也就是沒有被其他記錄的范圍所涵蓋）。

改寫后，這個(gè)SQL避免對(duì)自關(guān)聯(lián)，也就不存在關(guān)聯(lián)條件重復(fù)值過高的性能隱患了。在模擬環(huán)境中，性能對(duì)比如下：

SQL> 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))

10 ;

 

48344 rows selected.

 

Elapsed: 00:00:57.68

 

Execution Plan

———————————————————-

Plan hash value: 2540751655

 

————————————————————————————

| Id | Operation   | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |

————————————————————————————

| 0 | SELECT STATEMENT |  | 48454 | 1703K|  | 275 (1)| 00:00:04 |

|* 1 | HASH JOIN ANTI |  | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |

| 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K|  | 68 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K|  | 68 (0)| 00:00:01 |

————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))

filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND

“B”.ROWID<>”A”.ROWID)

 

 

Statistics

———————————————————-

0 recursive calls

0 db block gets

404 consistent gets

0 physical reads

0 redo size

2315794 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

48344 rows processed

 

SQL> 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;

 

48344 rows selected.

 

Elapsed: 00:00:00.72

 

Execution Plan

———————————————————-

Plan hash value: 1546715670

 

——————————————————————————————

| Id | Operation    | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |

——————————————————————————————

| 0 | SELECT STATEMENT   |  | 48454 | 2460K|  | 800 (1)| 00:00:10 |

|* 1 | VIEW     |  | 48454 | 2460K|  | 800 (1)| 00:00:10 |

|* 2 | WINDOW SORT PUSHED RANK|  | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |

| 3 | WINDOW BUFFER   |  | 48454 | 1845K|  | 800 (1)| 00:00:10 |

| 4 |  VIEW     |  | 48454 | 1845K|  | 311 (1)| 00:00:04 |

| 5 |  WINDOW SORT   |  | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |

| 6 |  TABLE ACCESS FULL | T_NUM | 48454 | 662K|  | 68 (0)| 00:00:01 |

——————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – filter(“RN”=1 AND “CN”=1)

2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”

ORDER BY “BEGIN”)<=1)

 

 

Statistics

———————————————————-

0 recursive calls

0 db block gets

202 consistent gets

0 physical reads

0 redo size

1493879 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

48344 rows processed

原SQL運(yùn)行時(shí)間接近1分鐘，而改寫后的SQL語句只需要0.72秒，執(zhí)行時(shí)間變?yōu)樵镜?/80，邏輯讀減少一半。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:

• MySQL中三種關(guān)聯(lián)查詢方式的簡(jiǎn)單比較
• mysql中的跨庫(kù)關(guān)聯(lián)查詢方法
• 淺談mysql中多表不關(guān)聯(lián)查詢的實(shí)現(xiàn)方法
• MySQL中表子查詢與關(guān)聯(lián)子查詢的基礎(chǔ)學(xué)習(xí)教程
• 實(shí)例講解Java的MyBatis框架對(duì)MySQL中數(shù)據(jù)的關(guān)聯(lián)查詢
• MyBatis實(shí)踐之動(dòng)態(tài)SQL及關(guān)聯(lián)查詢
• mysql關(guān)聯(lián)子查詢的一種優(yōu)化方法分析
• Yii2實(shí)現(xiàn)跨mysql數(shù)據(jù)庫(kù)關(guān)聯(lián)查詢排序功能代碼
• mysql一對(duì)多關(guān)聯(lián)查詢分頁錯(cuò)誤問題的解決方法

• sql
• 分析函數(shù)
• 自關(guān)聯(lián)查詢

相關(guān)文章

• 

  介紹PostgreSQL中的jsonb數(shù)據(jù)類型

  這篇文章主要介紹了介紹PostgreSQL中的jsonb數(shù)據(jù)類型,jsonb是PostgreSQL9.4中開始內(nèi)置的類型,能夠支持GIN索引,需要的朋友可以參考下

  2015-04-04
• 

  DeveloperSharp?高效分頁使用詳解

  這篇文章主要為大家介紹了DeveloperSharp?高效分頁使用詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

  2022-12-12
• 

  influxdb數(shù)據(jù)庫(kù)常用命令及SpringBoot整合

  這篇文章主要介紹了influxdb數(shù)據(jù)庫(kù)常用命令及SpringBoot整合,Influxdb是一個(gè)開源的分布式時(shí)序、時(shí)間和指標(biāo)數(shù)據(jù)庫(kù)，使用go語言編寫，無需外部依賴,需要的朋友可以參考下

  2023-07-07
• 

  詳細(xì)聊聊關(guān)于sql注入的一些零散知識(shí)點(diǎn)

  SQL注入攻擊是通過將惡意的SQL查詢或添加語句插入到應(yīng)用的輸入?yún)?shù)中,再在后臺(tái)SQL服務(wù)器上解析執(zhí)行進(jìn)行的攻擊,它目前是黑客對(duì)數(shù)據(jù)庫(kù)進(jìn)行攻擊的最常用的手段之一,這篇文章主要給大家介紹了關(guān)于sql注入的一些零散知識(shí)點(diǎn),需要的朋友可以參考下

  2021-10-10
• 

  從Bak文件中恢復(fù)SQL數(shù)據(jù)庫(kù)的三種方法

  在數(shù)據(jù)庫(kù)管理和維護(hù)過程中,數(shù)據(jù)的安全性和完整性至關(guān)重要,備份文件（.bak 文件）是 SQL Server 中常用的數(shù)據(jù)庫(kù)備份格式,本文將介紹從 .bak 文件恢復(fù) SQL 數(shù)據(jù)庫(kù)的基本步驟和最佳實(shí)踐,需要的朋友可以參考下

  2024-09-09
• 

  SQL中過濾條件放on和where中的區(qū)別詳解

  這篇文章主要給大家介紹了關(guān)于SQL中過濾條件放on和where中的區(qū)別，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起看看吧

  2019-01-01
• 

  navicat導(dǎo)入excel文件的步驟以及可能碰到的問題

  本文介紹將excel導(dǎo)入到mysql數(shù)據(jù)庫(kù)的方法,相對(duì)來說比較簡(jiǎn)單,但也可能會(huì)碰到一些小問題,在這里做一個(gè)小的總結(jié),這里使用到的工具包括navicat,mysql數(shù)據(jù)庫(kù)以及excel,需要的朋友可以參考下

  2024-07-07
• 

  數(shù)據(jù)庫(kù) 左連接 右連接 全連接用法小結(jié)

  在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中，表建立時(shí)各數(shù)據(jù)之間的關(guān)系不必確定，常把一個(gè)實(shí)體的所有信息存放在一個(gè)表中。

  2008-08-08
• 

  DeepinV20快速安裝Mariadb的詳細(xì)步驟

  本文給大家介紹DeepinV20安裝Mariadb的詳細(xì)步驟，MariaDB數(shù)據(jù)庫(kù)管理系統(tǒng)是MySQL的一個(gè)分支，添加一些功能支持本地的非阻塞操作和進(jìn)度報(bào)告，具體內(nèi)容詳情跟隨小編一起看看吧

  2021-07-07
• 

  Lakehouse數(shù)據(jù)湖并發(fā)控制陷阱分析

  這篇文章主要為大家介紹了Lakehouse數(shù)據(jù)湖并發(fā)控制陷阱分析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪

  2022-03-03

最新評(píng)論