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

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