SQL語句查詢性能優(yōu)化_百度文庫

1、SQl語句查詢性能優(yōu)化【摘要】本文從DBMS的查詢優(yōu)化器對SQL查詢語句進(jìn)行性能優(yōu)化的角度出發(fā)，結(jié)合數(shù)據(jù)庫理論，從查詢表達(dá)式及其多種查詢條件組合對數(shù)據(jù)庫查詢性能優(yōu)化進(jìn)行分析，總結(jié)出多種提高數(shù)據(jù)庫查詢性能優(yōu)化策略，介紹索引的合理建立和使用以及高質(zhì)量SQL查詢語句的書寫原則，從而實現(xiàn)高效的查詢，提高系統(tǒng)的可用性。【關(guān)鍵詞】SQL查詢語句，索引，性能優(yōu)化1.引言在應(yīng)用系統(tǒng)開發(fā)初期，由于開發(fā)數(shù)據(jù)庫數(shù)據(jù)比較少，對于查詢SQL語句，索引的運用與復(fù)雜視圖的編寫等體會不出SQL語句各種寫法的性能優(yōu)劣，但是應(yīng)用系統(tǒng)實際應(yīng)用后，隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就成為目前系統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)

2、化中一個很重要的方面就是SQL語句的優(yōu)化。對于海量數(shù)據(jù)，劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達(dá)到上百倍，可見對于一個系統(tǒng)不是簡單地能實現(xiàn)其功能就可，而是要寫出高質(zhì)量的SQL語句，提高系統(tǒng)的可用性。面對海量數(shù)據(jù)查詢，分時段對大批量數(shù)據(jù)進(jìn)行刪除、更新和插入操作，抓住需要優(yōu)化的主要方面，針對不同的情況從如何采用高效的SQL入手來進(jìn)行。2.索引的正確使用在海量數(shù)據(jù)表中，基本每個表都有一個或多個的索引來保證高效的查詢，索引的使用需要遵循以下使用原則：(1 當(dāng)插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量10%以上時, 首先需要刪除該表的索引來提高數(shù)據(jù)的插入效率，當(dāng)數(shù)據(jù)全部插入后再建立索引。(2 避免在索引列

3、上使用函數(shù)或計算，在WHERE子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用全表掃描。舉例: 低效： select * from table where salary * 12 > 25000; 高效： select * from table where salary > 25000/12;低效： select * from table1 where name='zhangsan' and tID > 10000高效： select * from table1 where tID > 10000 and name='zhangsan&

4、#39;如果tID是一個聚合索引，那么后一句僅僅從表的10000條以后的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name='zhangsan'的，而后再根據(jù)限制條件條件tID>10000來提出查詢結(jié)果。(3 避免在索引列上使用NOT和”!=”或<> ， 索引只能告訴什么存在于表中， 而不能告訴什么不存在于表中，當(dāng)數(shù)據(jù)庫遇到NOT和”!=”時，就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。(4 索引列上用>=替代>高效： select * from table where Deptno >=4 低效： select * from table w

5、here Deptno >3 兩者的區(qū)別在于， 前者table將直接跳到第一個Deptno等于4的記錄而后者將首先定位到Deptno=3的記錄并且向前掃描到第一個Deptno大于3的記錄。(5 函數(shù)的列啟用索引方法，如果一定要對使用函數(shù)的列啟用索引，Oracle9i以上版本新的功能：基于函數(shù)的索引(Function-Based Index是一個較好的方案，但該類型索引的缺點是只能針對某個函數(shù)來建立和使用該函數(shù)。create index EMP_I ON EMP (upper( ename; /*建立基于函數(shù)的索引*/ select * from EMP where upper( enam

6、e = BLACKSNAIL; /*將使用索引*/3. SQL語句性能優(yōu)化3.1 WHERE子句中的連接順序ORACLE采用自下而上的順序解析where子句，根據(jù)這個原理，表之間的連接必須寫在其它where條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在where子句的末尾。 低效：Select * from table where Salary > 50000 and Job = MANAGER and 25 < (Select count(* from table where Mgr=table.Empno; 高效：Select * from table where 25 &

7、lt; (select count(* from table where Mgr=table.Empno and Salary> 50000 and Job = MANAGER;3.2 用EXISTS替代IN在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件往往需要對另一個表進(jìn)行聯(lián)接，例如在ETL過程寫數(shù)據(jù)到模型時經(jīng)常需要關(guān)聯(lián)10個左右的維表，從ORACLE執(zhí)行的步驟來分析用IN的SQL與不用IN的SQL有以下區(qū)別：ORACLE試圖將其轉(zhuǎn)換成多個表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢，再查詢外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個表的連接方式查詢。由此可見用IN的SQL至少多了一個轉(zhuǎn)換

8、的過程, 在這種情況下，使用EXISTS而不用IN將提高查詢的效率。3.3 用NOT EXISTS替代NOT IN子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因為它對子查詢中的表執(zhí)行了一個全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。3.4 !=或<> 操作符（不等于）不等于操作符是永遠(yuǎn)不會用到索引的，因此對它的處理只會產(chǎn)生全表掃描。推薦方案：用其它相同功能的操作運算代替，如a<>0 改為 a>0 or a<0 。3.5 IS NULL 或IS NOT NULL操作（判斷字段是否為空）

9、判斷字段是否為空一般是不會應(yīng)用索引的，因為B樹索引是不索引空值的。推薦方案：用其它相同功能的操作運算代替，如a is not null 改為 a>0 或a>等。不允許字段為空，而用一個缺省值代替空值。3.6 > 及 < 操作符（大于或小于操作符）大于或小于操作符一般情況下是不用調(diào)整的，因為它有索引就會采用索引查找，但有的情況下可以對它進(jìn)行優(yōu)化，如一個表有100萬記錄，一個數(shù)值型字段A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因為A>2時ORACLE會先找出為2的記錄索

10、引再進(jìn)行比較，而A>=3時ORACLE則直接找到=3的記錄索引。3.7 優(yōu)化GROUP BY提高GROUP BY 語句的效率，可以通過將不需要的記錄在GROUP BY 之前過濾掉。 低效： Select Job, Avg(Salary from table group by Job having Job = PRESIDENT OR Job = MANAGER 高效： Select Job, Avg(Salary from table Where Job = PRESIDENT OR Job = MANAGER group by Job 3.8 LIKE操作符LIKE操作符可以應(yīng)用通配符

11、查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢，但是如果用得不好則會產(chǎn)生性能上的問題，如LIKE %5400% 這種查詢不會引用索引，而LIKE X5400%則會引用范圍索引。一個實際例子：用Students表中學(xué)生編號后面的標(biāo)識號可來查詢學(xué)生 Sno LIKE %5400% 這個條件會產(chǎn)生全表掃描，如果改成Sno LIKE X5400% OR Sno LIKE B5400% 則會利用Sno的索引進(jìn)行兩個范圍的查詢，性能肯定大大提高。3.9 有條件的使用UNION-ALL 替換UNION針對多表連接操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所連接的各個表中無主關(guān)鍵

12、字相同的記錄，因為UNION ALL 將重復(fù)輸出兩個結(jié)果集合中相同記錄。UNION在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運算，刪除重復(fù)的記錄再返回結(jié)果。當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以UNION-ALL的方式被合并，然后在輸出最終結(jié)果前進(jìn)行排序。如果用UNION ALL替代UNION， 這樣排序就不是必要了，效率就會因此得到提高3-5倍。3.10 避免where 子句中對字段進(jìn)行表達(dá)式操作或?qū)ψ侄芜M(jìn)行函數(shù)操作因為若where 子句中如果索引列是表達(dá)式或函數(shù)的一部分,優(yōu)化器就不能使用分布統(tǒng)計信息，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全

13、表掃描。低效：Select name from table where substring (id, 1, 1 = 'C'低效：Select name from table where salary * 10 > 400004.其它的優(yōu)化方法 數(shù)據(jù)庫的查詢優(yōu)化方法不僅僅是索引和SQL語句的優(yōu)化，其他方法的合理使用同樣也能很好的對數(shù)據(jù)庫查詢功能起到優(yōu)化作用。我們就來列舉幾種簡單實用的方法。4.1 使用臨時表加速查詢創(chuàng)建臨時表在一些情況下可以避免多重排序操作。但所創(chuàng)建的臨時表的行要比主表的行少，其物理順序就是所要求的順序，這樣就減少了輸入和輸出，降低了查詢的工作量，

14、提高了查詢效率，而且臨時表的創(chuàng)建并不會反映主表的修改。 比如：如果直接在存儲上萬條數(shù)據(jù)的永久表上重復(fù)循環(huán)進(jìn)行統(tǒng)計、查詢，其執(zhí)行效率非常低，但是，先從存儲大量數(shù)據(jù)的永久表中提取符和條件的存放到臨時表后，在臨時表上執(zhí)行操作，效率會大大提高。4.2 避免對大型表 行數(shù)據(jù)的順序存取在嵌套查詢中，對表的順序存取對查詢效率可能產(chǎn)生致命的影響。比如采用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那么這個查詢就要查詢10億行數(shù)據(jù)。避免這種情況的主要方法就是對連接的列進(jìn)行索引。兩個表：學(xué)生表（學(xué)號、姓名、年齡） 選課表（學(xué)號、課程號、成績） 如果兩個表要做連接，就要在“學(xué)號”這個連接字段上建立索

15、引。 4.3 避免或簡化排序 應(yīng)當(dāng)簡化或避免對大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時，優(yōu)化器就避免了排序的步驟。以下是一些影響因素：1.索引中不包括一個或幾個待排序的列2. group by或order by子句中列的次序與索引的次序不一樣3. 排序的列來自不同的表為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫表（盡管有時可能影響表的規(guī)范化，但相對于效率的提高是值得的）。如果排序不可避免，那么應(yīng)當(dāng)試圖簡化它，如縮小排序的列的范圍等。4.4 避免相關(guān)子查詢 如果在主查詢和where子句中的查詢中同時出現(xiàn)了一個列的標(biāo)簽，這樣就會使主查詢的列值改變后，子查詢也必須重新進(jìn)行一次查詢。因為查詢的嵌套層次越多，查詢的效率就會降低，所以我們應(yīng)當(dāng)避免子查詢。如果無法避免，就要在查詢的過程中過濾掉盡可能多的。4.5 用排序來取代非順序存取 非順序磁盤存取是最慢的操作，表現(xiàn)在磁盤存取臂的來回移動。SQL語句隱藏了這一情況，使得我們在寫應(yīng)用程序時很容易寫出要求存取大量非順序頁的查詢。 有些時候，用數(shù)據(jù)庫的排序能力來替代非順序的存取能改進(jìn)查詢。 5. 總結(jié)對