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