JAVA(SQL語句的優(yōu)化)

《JAVA(SQL語句的優(yōu)化)》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫。

一、SQL編寫注意事項（標(biāo)準(zhǔn)）在多在應(yīng)用系統(tǒng)開發(fā)初期，由于開發(fā)數(shù)據(jù)庫數(shù)據(jù)比較少，對于查詢SQL語句的編寫等體會不出SQL語句各種寫法的性能優(yōu)劣，但是如果將應(yīng)用系統(tǒng)提交實際應(yīng)用后，隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就成為目前系統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)化中一個很重要的方面就是SQL語句的優(yōu)化。對于大量數(shù)據(jù)，劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達到上百倍，可見對于一個系統(tǒng)不是簡單地能實現(xiàn)其功能就可，而是要寫出高質(zhì)量的SQL語句，提高系統(tǒng)的可用性。在多數(shù)情況下，Oracle使用索引來更快地遍歷表，優(yōu)化器主要根據(jù)定義的索引來提高性能。但是，如果在SQL語句的where子句中寫的SQL代碼不合理，就會造成優(yōu)化器刪去索引而使用全表掃描，一般就這種SQL語句就是所謂的劣質(zhì)SQL語句。在編寫SQL語句時我們應(yīng)清楚優(yōu)化器根據(jù)何種原則來使用索引，這有助于寫出高性能的SQL語句。改成下面：10 二、SQL語句的編寫原則：●選擇一個好的表聯(lián)接順序（這是一個比較重要的原則）當(dāng)在WHERE子句中有多個表聯(lián)接時，WHERE子句中排在最后的表應(yīng)當(dāng)是返回行數(shù)可能最少的表，有過濾條件的子句應(yīng)放在WHERE子句中的最后。如：設(shè)從emp表查到的數(shù)據(jù)比較少或該表的過濾條件比較確定，能大大縮小查詢范圍，則將最具有選擇性部分放在WHERE子句中的最后：select?*?from?emp?e,dept?d?where?d.deptno?>10?and?e.deptno?=30;?如果dept表返回的記錄數(shù)較多的話，上面的查詢語句會比下面的查詢語句響應(yīng)快得多。select?*?from?emp?e,dept?d?where?e.deptno?=30?and?d.deptno?>10;●最好不要在WHERE子句中使用函數(shù)或表達式，如果要使用的話，最好統(tǒng)一使用相同的表達式或函數(shù)，這樣便于以后使用合理的索引。SELECT?*FROM?T1WHERE?F1*2=100?改成?SELECT?*FROM?T1WHEREF1=100/2●?使用WHERE?（NOT）EXISTS?來代替（NOT）IN子句，使用NOT?EXISTS?子句可以有效地利用索引。盡可能使用NOT?EXISTS來代替NOT?IN，盡管二者都使用了NOT（但NOTIN不能使用索引而降低速度），NOT?EXISTS要比NOT?IN查詢效率更高。例子1：?10 SELECT?dname,?deptno?FROM?dept?WHERE?deptno?NOT?IN?(SELECT?deptno?FROM?emp);?例子2：SELECT?dname,?deptno?FROM?dept?WHERE?NOT?EXISTS?(SELECT?deptno?FROM?emp?WHERE?dept.deptno?=?emp.deptno);?明顯的，2要比1的執(zhí)行性能好很多。因為1中對emp進行了全表掃描,這是很浪費時間的操作。而且1中沒有用到emp的索引，?因為沒有where子句。而2中的語句對emp進行的是縮小范圍的查詢?！?通過使用>=、<=等，避免使用NOT命令如這個例子：select?*?from?employee?where?salary<>3000;?對這個查詢，可以改寫為不使用NOT：select?*?from?employee?where?salary<3000?or?salary>3000;?雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引?！裢獠柯?lián)接+的用法外部聯(lián)接+按其在=的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶+運算符的表中的一個行不直接匹配于帶+預(yù)算符的表中的任何行，則前者的行與后者中的一個空行相匹配并被返回。利用外部聯(lián)接+，可以替代效率十分低下的?not?in?運算，大大提高運行速度。例如，下面這條命令執(zhí)行起來很慢：select?a.empno?from?emp?a?where?a.empno?not?in(select?empno?from?emp1?where?job=‘SALE’);索引倘若利用外部聯(lián)接，改寫命令如下:?select?a.empno?from?emp?aleftjoinemp1?b?on?a.empno=b.empno?where?b.empno?is?null?and?b.job=‘SALE’;例如表少，但情況復(fù)雜的時候應(yīng)該寫如下語句：selecta.empnoASempno,(selectemp2.addressfromemp2wherea.id=emp2.id)ASaddress,(selectemp3.address1fromemp3whereb.id=emp3.id)ASaddress1fromempaleftjoinemp1bona.empno=b.empnowhereb.empnoisnullandb.job=‘SALE’這樣運行速度明顯提高.●在查詢時盡量少用格式轉(zhuǎn)換如用?WHERE?a.order_no?=?b.order_no?而不用?WHERE?TO_NUMBER?(substr(a.order_no,?instr(b.order_no,?’.’)-1)=?TO_NUMBER?(substr(a.order_no,?instr(b.order_no,?.’)?-?1)10 ●Order?by語句索引ORDER?BY語句決定了Oracle如何將返回的查詢結(jié)果排序。Order?by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order?by語句的非索引項或者有計算表達式都將降低查詢速度。仔細(xì)檢查order?by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order?by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在order?by子句中使用表達式。如必須使用排序操作，請遵循如下規(guī)則：如結(jié)果集不需唯一，使用unionall代替union?！馡S?NULL?與?IS?NOT?NULL不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。任何在where子句中使用is?null或is?not?null的語句優(yōu)化器是不允許使用索引的?！馭ELECT子句中避免使用‘*‘SELECT*FROM　EMP應(yīng)改為：SELECTCOLUMNFROM　EMP●當(dāng)在SQL語句中連接多個表時,最好使用表別名并把別名加在每個列上●其他使用count(*)而不要使用count(column_name)。避免困難的正規(guī)表達式：LIKE通配符匹配，技術(shù)上叫正規(guī)表達式。但這種匹配特別耗費時間。例如：SELECT＊FROMcustomerWHEREzipcodeLIKE“98___”即使在zipcode字段上建立了索引，也還是采用順序掃描的方式。如果把語句改為SELECT＊FROMcustomerWHEREzipcode>98000在執(zhí)行查詢時就會利用索引來查詢，顯然會大大提高速度。另外，以下語句也不會使用索引：SELECT＊FROMcustomerWHEREzipcodeLIKE'[C-P]arsen'SELECT＊FROMcustomerWHEREzipcodeLIKE'parsen[^L]%'SELECT＊FROMcustomerWHEREzipcodeLIKE'%parsen'10 三、其他經(jīng)驗性規(guī)則?　1、用多表連接代替EXISTS子句。如例（1）　?2、少用DISTINCT，用EXISTS代替如例（2），where條件滿足后，可以一次　性返回結(jié)果集3、如果有兩張表聯(lián)接利用COUNT的時候最好將急紀(jì)錄少的表放放在后后面　表TAB116,384條記錄　　　表TAB25000條記錄selectcount(tab2.*)fromtab1,tab2wheretab2.id=tab1.id這個速度要快些。selectcount(tab2.*)fromtab2,tab1wheretab2.id=tab1.id如果還有tab3表，而且tab3表是tab1和tab2表的聯(lián)協(xié)表，也叫交集表，此時，將tab3放在最后效率要高很多。selectcount(tab2.*)fromtab2,tab1,tab3wheretab2.name=tab1.nameandtab3.sex=tab1.sexandtab3.age=tab2.age示例：?例1：??XSELECT*FROMempWHEREEXISTS(SELECT'X'FROMdept??WHEREdept_no=e.dept_noANDdept_cat='A');??OSELECT*FROMempe,deptdWHEREe.dept_no=d.dept_no??ANDdept_cat='A';?例2：??XSELECTDISTINCTd.dept_code,d.dept_nameFROMdeptd,empe??WHEREe.dept_code=d.dept_code;??OSELECTdept_code,dept_nameFROMdeptd??WHEREEXISTS(SELECT'X'FROMempe??WHEREe.dept_code=d.dept_code);10 SQL性能調(diào)整原則以下就某些SQL語句的where子句編寫及性能優(yōu)化中需要注意的問題作詳細(xì)介紹。在這些where子句中，雖然某些列存在索引，但是由于編寫了劣質(zhì)的SQL，系統(tǒng)在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應(yīng)速度的極大降低。1.ISNULL與ISNOTNULL在應(yīng)不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。任何在where子句中使用isnull或isnotnull的語句優(yōu)化器是不允許使用索引的。例：dropindextemp_zhao.aaaindexselect*fromtemp_zhaowhereaulnameisnotnullcreateclusteredindexaaaindexontemp_zhao(aulname)select*fromtemp_zhaowhereaulnameisnotnull2.聯(lián)接列在應(yīng)對于有聯(lián)接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器是不會使用索引的。我們一起來看一個例子，假定有一個職工表（employee），對于一個職工的姓和名分成兩列存放（FIRST_NAME和LAST_NAME），現(xiàn)在要查詢一個叫比爾.克林頓（BillCliton）的職工。下面是一個采用聯(lián)接查詢的SQL語句：select*fromemploysswherefirst_name||''||last_name='BeillCliton';上面這條語句完全可以查詢出是否有BillCliton這個員工，但是這里需要注意，系統(tǒng)優(yōu)化器對基于last_name創(chuàng)建的索引沒有使用。應(yīng)當(dāng)采用下面這種SQL語句的編寫，Oracle系統(tǒng)就可以采用基于last_name創(chuàng)建的索引。Select*fromemployeewherefirst_name='Beill'andlast_name='Cliton';例：dropindextemp_zhao.aaaindexselect*fromtemp_zhaowhereaulname+''+au_fname='WhiteJohnson'select*fromtemp_zhaowhereaulname='White'andau_fname='Johnson'createclusteredindexaaaindexontemp_zhao(au_fname)10 select*fromtemp_zhaowhereaulname+''+au_fname='WhiteJohnson'select*fromtemp_zhaowhereaulname='White'andau_fname='Johnson'3.帶通配符（%）的like語句同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含cliton的人。可以采用如下的查詢SQL語句：select*fromemployeewherelast_namelike'%cliton%';這里由于通配符（%）在搜尋詞首出現(xiàn)，所以O(shè)racle系統(tǒng)不使用last_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用：select*fromemployeewherelast_namelike'cliton%';例：dropindextemp_zhao.aaaindexselect*fromtemp_zhaowhereaulnamelike'G%'oraulnamelike'W%'oraulnamelike'b%'createclusteredindexaaaindexontemp_zhao(aulname)select*fromtemp_zhaowhereaulnamelike'G%'oraulnamelike'W%'oraulnamelike'b%'4.Orderby語句ORDERBY語句決定了Oracle如何將返回的查詢結(jié)果排序。Orderby語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Orderby語句的非索引項或者有計算表達式都將降低查詢速度。仔細(xì)檢查orderby語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫orderby語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在orderby子句中使用表達式。例：Orderby語句dropindextemp_zhao.aaaindexselect*fromtemp_zhaoorderbyau_idcreateclusteredindexaaaindexontemp_zhao(au_id)select*fromtemp_zhaoorderbyau_idGroupby語句dropindextemp_zhao.aaaindexselectaulnamefromtemp_zhaogroupbyaulnamecreateclusteredindexaaaindexontemp_zhao(aulname)selectaulnamefromtemp_zhaogroupbyaulname10 5.NOT我們在查詢時經(jīng)常在where子句使用一些邏輯表達式，如大于、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）。NOT可用來對任何邏輯運算符號取反。下面是一個NOT子句的例子：...wherenot(status='VALID')如果要使用NOT，則應(yīng)在取反的短語前面加上括號，并在短語前面加上NOT運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等于（<>）運算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運算符中，見下例：...wherestatus<>　'INVALID';再看下面這個例子：select*fromemployeewhere?salary<>3000;對這個查詢，可以改寫為不使用NOT：select*fromemployeewhere?salary<3000orsalary>3000;例：dropindextemp_zhao.aaaindexselect*fromtemp_zhaowherezip<>94609createclusteredindexaaaindexontemp_zhao(zip)select*fromtemp_zhaowherezip<>94609select*fromtemp_zhaowherezip>94609orzip<94609雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。6.IN和EXISTS有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種格式的子查詢。第一種格式是使用IN操作符：...wherecolumnin(select*from...where...);第二種格式是使用EXIST操作符：...whereexists(select'X'from...where...);相信絕大多數(shù)人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠(yuǎn)比第一種格式的效率高。在Oracle中可以幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。在第二種格式中，子查詢以‘select10 'X'開始。運用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作（這里假定在where語句中使用的列存在索引）。相對于IN子句來說，EXISTS使用相連子查詢，構(gòu)造起來要比IN子查詢困難一些。通過使用EXIST，Oracle系統(tǒng)會首先檢查主查詢，然后運行子查詢直到它找到第一個匹配項，這就節(jié)省了時間。Oracle系統(tǒng)在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。同時應(yīng)盡可能使用NOTEXISTS來代替NOTIN，盡管二者都使用了NOT（不能使用索引而降低速度），NOTEXISTS要比NOTIN查詢效率更高。例：createclusteredindexaaaindexontemp_zhao(au_id)select*fromtemp_zhaowheretemp_zhao.au_idin(selectau_idfromtemp_zhao1)select*fromtemp_zhaowhereexists(select*fromtemp_zhao1wheretemp_zhao.au_id=temp_zhao1.au_id)7、其他1、SELECT子句中避免使用‘*‘selectzipfromtemp_zhaoselect*fromtemp_zhao2、使用count(*)而不要使用count(column_name)。selectcount(zip)fromtemp_zhaoselectcount(*)fromtemp_zhao10 培訓(xùn)注意點總結(jié)1、語句中出現(xiàn)的所有表名、字段名全部小寫，系統(tǒng)保留字、內(nèi)置函數(shù)名、SQL保留字大寫。2、連接符or、in、and、以及＝、<=、>=等前后加上一個空格。3、對較為復(fù)雜的sql語句加上注釋，說明算法、功能。4、查詢多個列時，盡量使每列查詢占一行，且加上注釋來標(biāo)明該列名稱，以方便其他人以后的修改。5、where子句書寫時，每個條件占一行，語句令起一行時，以保留字或者連接符開始，連接符右對齊。6、多表連接時，使用表的別名來引用列。7、變量命名不能超出ORACLE的限制（30個字符），命名要規(guī)范，要用英文命名。8、查找數(shù)據(jù)庫表或視圖時，只取需要字段，不要使用*來代替所有列名。9、當(dāng)在WHERE子句中有多個表聯(lián)接時，WHERE子句中排在最后的表應(yīng)當(dāng)是返回行數(shù)可能最少的表。10、過濾條件最有效的子句應(yīng)放在WHERE子句中的最后。11、最好不要在WHERE子句中使用函數(shù)或表達式。12、盡可能使用NOT?EXISTS來代替NOT?IN，使用EXISTS來代替IN。13、通過使用>...OR<...來避免使用<>...。14、在查詢時盡量少用格式轉(zhuǎn)換。15、在不必要的情況下盡量避免使用ORDERBY和GROUPBY（必要時注意對索引列排序、分組）。16、盡量避免使用IS?NULL?和?IS?NOT?NULL（尤其對具有索引的列使用）。17、在使用count時，盡量使用count(*)而不要使用count(column_name)。18、在不必要的情況下盡量避免使用DISTINCT。19、盡量避免使用‘%...‘。20、盡可能多地使用主鍵列（默認(rèn)的會在主鍵上加上索引）作為條件。21、盡量避免使用UNIONALL，而用UNION代替。10