SQL最佳化--邏輯最佳化--視圖重寫與等價謂詞重寫,sql謂詞

來源:互聯網
上載者:User

SQL最佳化--邏輯最佳化--視圖重寫與等價謂詞重寫,sql謂詞

1)視圖重寫

視圖的類型:

a)用SPJ格式構造的視圖,稱為簡單視圖。

CREATE VIEW v1 AS SELECT x, y, z FROM t;

b)用非SPJ格式構造的視圖(帶有GROUPBY等操作),稱為複雜視圖。

CREATE VIEW v2 AS SELECT x, y, z FROM t ORDER BY x;

視圖重寫:

a)查詢語句中出現視圖對象

b)查詢最佳化後,視圖對象消失

c)消失的視圖對象的查詢語句, 融合到初始查詢語句中

MySQL視圖重寫準則:

a)MySQL支援對視圖進行最佳化。

b)最佳化方法是把視圖轉為對基表的查詢,然後進行類似子查詢的最佳化。

c)MySQL通常只能重寫簡單視圖,複雜視圖不能重寫。

2)等價謂詞重寫:把邏輯運算式重寫成等價的且效率更高的形式。

a)LIKE規則

LIKE謂詞,是SQL標準支援的一種模式比對比較操作;LIKE規則,是對LIKE謂詞的等價重寫,即改寫LIKE謂詞為其他等價的謂詞,以更好地利用索引進行最佳化。如:

name LIKE 'Abc%'  重寫為:  name >='Abc' AND name <'Abd'

應用LIKE規則的好處:轉換前針對LIKE謂詞,只能進行全表掃描,如果name列上存在索引,則轉換後可以進行索引掃描。

LIKE匹配的運算式中,沒有萬用字元(%或_),則與“=”等價,如:

name LIKE 'Abc'  重寫為:  name ='Abc'

如果name列上存在索引,則可以利用索引提高查詢效率

b)BETWEEN-AND規則

BETWEEN-AND謂詞,是SQL標準支援的一種範圍比較操作;

BETWEEN-AND規則,是BETWEEN-AND謂詞的等價重寫,即改寫BETWEEN-AND謂詞為其他等價的謂詞,以更好地利用索引進行最佳化。如:

sno BETWEEN 10 AND 20  重寫為:  sno>=10 AND sno <=20

BETWEEN-AND規則的好處是:如果sno上建立了索引,則可以用索引掃描代替原來BETWEEN-AND謂詞限定的全表掃描,從而提高了查詢的效率。

c)IN轉換OR規則

IN是只IN操作符操作,不是IN子查詢。IN轉換OR規則,就是IN謂詞的OR等價重寫,即改寫IN謂詞為等價的OR謂詞,以更好地利用索引進行最佳化。將IN謂詞等價重寫為若干個OR謂詞,可能會提高執行效率。如:

age IN (8,12,21)  重寫為:  age=8 OR age=12 OR age=21

應用IN轉換OR規則後效率是否能夠提高,需要看資料庫對IN謂詞是否只支援全表掃描。如果資料庫對IN謂詞只支援全表掃描且OR謂詞中表的age列上存在索引,則轉換後查詢效率會提高。

d)IN轉換ANY規則

IN轉換ANY規則,就是IN謂詞的ANY等價重寫,即改寫IN謂詞為等價的ANY謂詞。IN可以轉換為OR,OR可以轉為ANY,所以可以直接把IN轉換為ANY。將IN謂詞等價重寫為ANY謂詞,可能會提高執行效率。如:

age IN (8,12,21)  重寫為:  age ANY(8, 12, 21)

應用IN轉換ANY規則後效率是否能夠提高,依賴於資料庫對於ANY操作的支援情況。

e)OR轉換ANY規則

OR轉換ANY規則,就是OR謂詞的ANY等價重寫,即改寫OR謂詞為等價的ANY謂詞,以更好地利用MIN/MAX操作進行最佳化。如:

sal>1000 OR 

dno=3 AND (sal>1100 OR sal>base_sal+100) OR

sal>base_sal+200 OR 

sal>base_sal×2

重寫為:

dno=3 AND (sal>1100 OR sal>base_sal+100) OR 

sal> ANY (1000,base_sal+200,base_sal×2)

OR轉換ANY規則,依賴於資料庫對於ANY操作的支援情況。(PostgreSQL V9.2.3和MySQL V5.6.10目前都不支援本條規則。)

f)ALL/ANY轉換集合函式規則

ALL/ANY轉換集合函式規則,就是ALL/ANY謂詞改寫為等價的聚集合函式MIN/MAX謂詞操作,以更好地利用MIN/MAX操作進行最佳化。如:

sno>ANY(10, 2*5+3,sqrt(9))  重寫為:  sno>sqrt(9)

上面這個ALL/ANY轉換集合函式規則的樣本,有兩點需要注意:

①樣本中存在“>”和“ANY”,其意是在找出“(10, 2*5+3,sqrt(9))”中的最小值,所以可以重寫為“sno>sqrt(9)”。通常,聚集合函式MAX()、MIN()等的執行效率一般都比ANY、ALL謂詞的執行效率高,因此在這種情況下對其進行重寫,可以起到比較好的效果。

②如果有索引存在,求解MAX/MIN的效率更高。

g)NOT規則

NOT謂詞的等價重寫,如下:

NOT (col_1 !=2)    重寫為  col_1=2

NOT (col_1 !=col_2)重寫為  col_1=col_2

NOT (col_1 =col_2) 重寫為  col_1!=col_2

NOT (col_1 <col_2) 重寫為  col_1>=col_2

NOT (col_1 >col_2) 重寫為  col_1<=col_2

NOT規則重寫的好處:如果col_1上建立了索引,則可以用索引掃描代替原來的全表掃描,從而提高查詢的效率。

h)OR重寫並集規則

OR條件重寫為並集操作,形如下SQL樣本:

SELECT * FROM student

WHERE(sex=’f’ AND age>15) OR age>18;

假設所有條件運算式的列上都有索引(即sex列和age列上都存在索引),資料庫可能對於樣本中的WHERE語句強迫查詢最佳化工具使用順序掃描,因為這個語句要檢索的是OR操作的集合。為了能利用索引處理上面的查詢,可以將語句改成如下形式:

SELECT * FROM student

WHERE sex=’f’ and age>15

UNION

SELECT * FROM student

WHERE age>18;

改寫後的形式,可以分別利用列sex和age上的索引,進行索引掃描,然後再提供執行UNION操作獲得最終結果。


摘自《資料庫查詢最佳化工具的藝術》一書

A

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

Tags Index: