關于SQL優化的一些思想和原則
一、優化的目的
對應用程序的優化通常可分為兩個方面：源代碼優化和SQL語句優化。由于涉及到對程序邏輯的改變，源代碼的優化在時間成本和風險上代價很高(尤其是對正在使用中的系統進行優化) 。另一方面，源代碼的優化對數據庫系統性能的提升收效有限，因為應用程序對數據庫的操作最終要表現為SQL語句對數據庫的操作。
對SQL語句優化有以下一些直接原因：
1.SQL語句是對數據庫(數據) 進行操作的惟一途徑，應用程序的執行最終要歸結為SQL語句的執行，SQL語句的效率對數據庫系統的性能起到了決定性的作用。
2.SQL語句消耗了70%～90%的數據庫資源。
3.SQL語句獨立于程序設計邏輯，對SQL語句進行優化不會影響程序邏輯，相對于對程序源代碼的優化，對SQL語句優化在時間成本和風險上的代價都很低。
4.SQL語句可以有不同的寫法，不同的寫法在性能上的差異可能很大。
5.SQL語句易學，難精通。SQL語句的性能往往同實際運行系統的數據庫結構、記錄數量等有關，不存在普遍適用的規律來提升性能。
二、優化數據庫的思想
1、關鍵字段建立索引。
2、使用存儲過程，它使SQL變得更加靈活和高效。
3、備份數據庫和清除垃圾數據。
4、SQL語句語法的優化。
5、清理刪除日志。
三、SQL語句優化的原則
不要以為只有SELECT語句是查詢。實際上，帶有任何WHERE條件的DML(INSERT、UPDATE、DELETE)語句中都包含查詢要求，在后面的文章中，當說到查詢時，不一定只是指SELECT語句，也有可能指DML語句中的查詢部分。
我們知道，SQL語句同其它語言(如C語言)的語句不一樣，它是非過程化(non-procedural)的語句，即當你要取數據時，不需要告訴數據庫通 過何種途徑去取數據，如到底是通過索引取數據，還是應該將表中的每行數據都取出來，然后再通過一一比較的方式取數據(即全表掃描)。
為了實現一個查詢，內核必須為每個查詢定制一個查詢策略，或為取出符合條件的數據生成一個執行計劃(execution plan)。典型的，對于同一個查詢，可能有幾個執行計劃都符合要求，都能得到符合條件的數據。例如，參與連接的表可以有多種不同的連接方法，這取決于連接條件和優化器采用的連接方法。為了在多個執行計劃中選擇最優的執行計劃，優化器必須使用一些實際的指標來衡量每個執行計劃使用的資源(I/0次數、 CPU等)，這些資源也就是我們所說的代價(cost)。如果一個執行計劃使用的資源多，我們就說使用執行計劃的代價大。以執行計劃的代價大小作為衡量標準，優化器選擇代價最小的執行計劃作為真正執行該查詢的執行計劃，并拋棄其它的執行計劃。
#p#
四、關于ORACLE優化器
在任何可能的時候都會對表達式進行評估，并且把特定的語法結構轉換成等價的結構，這么做的原因是 ：要么結果表達式能夠比源表達式具有更快的速度；要么源表達式只是結果表達式的一個等價語義結構。不同的SQL結構有時具有同樣的操作（例如：= ANY (subquery) and IN (subquery)），ORACLE會把他們映射到一個單一的語義結構。下面我們介紹常量優化和一些常用的操作符優化的知識。
1、常量優化：
常量的計算是在語句被優化時一次性完成，而不是在每次執行時。下面是檢索月薪大于2000的的表達式：
- sal > 24000/12
- sal > 2000
- sal*12 > 24000
如果SQL語句包括第一種情況，優化器會簡單地把它轉變成第二種。
優化器不會簡化跨越比較符的表達式，例如第三條語句，鑒于此，應盡量寫用常量跟字段比較檢索的表達式，而不要將字段置于表達式當中。否則沒有辦法優化，比如如果sal上有索引，第一和第二就可以使用，第三就難以使用。
2、操作符優化：
優化器把使用LIKE操作符和一個沒有通配符的表達式組成的檢索表達式轉換為一個“=”操作符表達式。例如：優化器會把表達式ename LIKE 'SMITH'轉換為ename = 'SMITH'。優化器只能轉換涉及到可變長數據類型的表達式，前一個例子中，如果ENAME字段的類型是CHAR(10)， 那么優化器將不做任何轉換。一般來講LIKE比較難以優化。
IN 操作符優化：
優化器把使用IN比較符的檢索表達式替換為等價的使用“=”和“OR”操作符的檢索表達式。
例如，優化器會把表達式ename IN ('SMITH','KING','JONES')替換為：ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘。
ANY和SOME 操作符優化:
優化器將跟隨值列表的ANY和SOME檢索條件用等價的同等操作符和“OR”組成的表達式替換。例如，優化器將如下所示的第一條語句用第二條語句替換：
- sal > ANY (:first_sal, :second_sal)
- sal > :first_sal OR
- sal > :second_sal
優化器將跟隨子查詢的ANY和SOME檢索條件轉換成由“EXISTS”和一個相應的子查詢組成的檢索表達式。例如，優化器將如下所示的第一條語句用第二條語句替換：
- x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')
- EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)
ALL操作符優化：
優化器將跟隨值列表的ALL操作符用等價的“=”和“AND”組成的表達式替換。例如：sal > ALL (:first_sal, :second_sal)表達式會被替換為：sal > :first_sal AND sal > :second_sal 。
對于跟隨子查詢的ALL表達式，優化器用ANY和另外一個合適的比較符組成的表達式替換。例如x > ALL (SELECT sal FROM emp WHERE deptno = 10) 替換為：NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10)) 。接下來優化器會把第二個表達式適用ANY表達式的轉換規則轉換為下面的表達式：
- NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)
BETWEEN 操作符優化：
優化器總是用“>=”和“<=”比較符來等價的代替BETWEEN操作符。
例如：優化器會把表達式sal BETWEEN 2000 AND 3000用sal >= 2000 AND sal <= 3000來代替。
NOT 操作符優化：
優化器總是試圖簡化檢索條件以消除“NOT”邏輯操作符的影響，這將涉及到“NOT”操作符的消除以及代以相應的比較運算符。例如，優化器將下面的第一條語句用第二條語句代替：
- NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
- deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
通常情況下一個含有NOT操作符的語句有很多不同的寫法，優化器的轉換原則是使“NOT”操作符后邊的子句盡可能的簡單，即使可能會使結果表達式包含了更多的“NOT”操作符。例如，優化器將如下所示的第一條語句用第二條語句代替：
- NOT (sal < 1000 OR comm IS NULL)
- NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL
關于SQL優化的知識就介紹到這里，謝謝各位的支持！
【編輯推薦】
