數(shù)據(jù)庫(kù)SQL調(diào)優(yōu)

2020-02-16 21:02 作者：艾銻無(wú)限 瀏覽量：

 
        這次的肺炎疫情對(duì)中國(guó)的中小企業(yè)將會(huì)是沉重的打擊，據(jù)釘釘和微信兩個(gè)辦公平臺(tái)數(shù)據(jù)統(tǒng)計(jì)現(xiàn)有2億左右的人在家遠(yuǎn)程辦公，那么對(duì)于中小企業(yè)的員工來(lái)說(shuō)不懂IT技術(shù)將會(huì)讓他們面臨的最大挑戰(zhàn)和困難。
 
        電腦不亮了怎么辦？系統(tǒng)藍(lán)屏如何處理？辦公室的電腦在家如何連接？網(wǎng)絡(luò)應(yīng)該如何設(shè)置？VPN如何搭建？數(shù)據(jù)如何對(duì)接？服務(wù)器如何登錄？數(shù)據(jù)安全如何保證？數(shù)據(jù)如何存儲(chǔ)？視頻會(huì)議如何搭建？業(yè)務(wù)系統(tǒng)如何開啟等等一系列的問(wèn)題，都會(huì)困擾著并非技術(shù)出身的您。
   
        好消息是當(dāng)您看到這篇文章的時(shí)候，就不用再為上述的問(wèn)題而苦惱，您只需撥打艾銻無(wú)限的全國(guó)免費(fèi)熱線電話:400 650 7820，就會(huì)有我們的遠(yuǎn)程工程師為您解決遇到的問(wèn)題，他們可以遠(yuǎn)程幫您處理遇到的一些IT技術(shù)難題。
 
       如遇到免費(fèi)熱線占線，您還可以撥打我們的24小時(shí)值班經(jīng)理電話:15601064618或技術(shù)經(jīng)理的電話:13041036957，我們會(huì)在第一時(shí)間接聽(tīng)您的來(lái)電，為您提供適合的解決方案，讓您無(wú)論在家還是在企業(yè)都能無(wú)憂辦公。
 
那艾銻無(wú)限具體能為您的企業(yè)提供哪些服務(wù)呢？        艾銻無(wú)限始創(chuàng)于2005年，歷經(jīng)15年服務(wù)了5000多家中小企業(yè)并保障了幾十萬(wàn)臺(tái)設(shè)備的正常運(yùn)轉(zhuǎn)，積累了豐富的企業(yè)IT緊急問(wèn)題和特殊故障的解決經(jīng)驗(yàn)，制定了相對(duì)應(yīng)的解決方案。我們?yōu)槟钠髽I(yè)提供的IT服務(wù)分為三大版塊:
 
        第一版塊是保障性IT外包服務(wù):如電腦設(shè)備運(yùn)維，辦公設(shè)備運(yùn)維，網(wǎng)絡(luò)設(shè)備運(yùn)維，服務(wù)器運(yùn)維等綜合性企業(yè)IT設(shè)備運(yùn)維服務(wù)。
 
        第二版塊是功能性互聯(lián)網(wǎng)外包服務(wù):如網(wǎng)站開發(fā)外包，小程序開發(fā)外包，APP開發(fā)外包，電商平臺(tái)開發(fā)外包，業(yè)務(wù)系統(tǒng)的開發(fā)外包和后期的運(yùn)維外包服務(wù)。
 
        第三版塊是增值性云服務(wù)外包:如企業(yè)郵箱上云，企業(yè)網(wǎng)站上云，企業(yè)存儲(chǔ)上云，企業(yè)APP小程序上云，企業(yè)業(yè)務(wù)系統(tǒng)上云，阿里云產(chǎn)品等后續(xù)的云運(yùn)維外包服務(wù)。
 
        您要了解更多服務(wù)也可以登錄艾銻無(wú)限的官網(wǎng):www.bjitwx.com查看詳細(xì)說(shuō)明，在疫情期間，您企業(yè)遇到的任何困境只要找到艾銻無(wú)限，能免費(fèi)為您提供服務(wù)的我們絕不收一分錢，我們?nèi)w艾銻人承諾此活動(dòng)直到中國(guó)疫情結(jié)束,我們將這次活動(dòng)稱為——春雷行動(dòng)。  
       以下還有我們?yōu)槟峁┑囊恍┘夹g(shù)資訊，以便可以幫助您更好的了解相關(guān)的IT知識(shí)，幫您渡過(guò)疫情中辦公遇到的困難和挑戰(zhàn)，艾銻無(wú)限愿和中國(guó)中小企業(yè)一起共進(jìn)退，因?yàn)槲覀兿嘈湃f(wàn)物同體，能量合一，只要我們一起齊心協(xié)力，一定會(huì)成功。再一次祝福您和您的企業(yè)，戰(zhàn)勝疫情，您和您的企業(yè)一定行。

 
在項(xiàng)目中,SQL的調(diào)優(yōu)對(duì)項(xiàng)目的性能來(lái)講至關(guān)重要,所有掌握常見(jiàn)的SQL調(diào)優(yōu)方式是必不可少的,下面介紹幾種常見(jiàn)的SQL的調(diào)優(yōu)方式,供借鑒.
 
一.創(chuàng)建索引
1.要盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引
 2.(1)在經(jīng)常需要進(jìn)行檢索的字段上創(chuàng)建索引，比如要按照表字段username進(jìn)行檢索，那么就應(yīng)該在姓名字段上創(chuàng)建索引，如果經(jīng)常要按照員工部門和員工崗位級(jí)別進(jìn)行檢索，那么就應(yīng)該在員工部門和員工崗位級(jí)別這兩個(gè)字段上創(chuàng)建索引。
 (2)創(chuàng)建索引給檢索帶來(lái)的性能提升往往是巨大的，因此在發(fā)現(xiàn)檢索速度過(guò)慢的時(shí)候應(yīng)該首先想到的就是創(chuàng)建索引。
(3)一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。
 
二.避免在索引上使用計(jì)算
在where字句中，如果索引列是計(jì)算或者函數(shù)的一部分，DBMS的優(yōu)化器將不會(huì)使用索引而使用全表查詢,函數(shù) 屬于計(jì)算的一種,同時(shí)在in和exists中通常情況下使用EXISTS，因?yàn)閕n不走索引
效率低：
 select * from user where salary*22>11000(salary是索引列)
 
效率高：
 select * from user where salary>11000/22(salary是索引列)
 
三.使用預(yù)編譯查詢
程序中通常是根據(jù)用戶的輸入來(lái)動(dòng)態(tài)執(zhí)行SQL，這時(shí)應(yīng)該盡量使用參數(shù)化SQL,這樣不僅可以避免SQL注入漏洞 攻擊，最重要數(shù)據(jù)庫(kù)會(huì)對(duì)這些參數(shù)化SQL進(jìn)行預(yù)編譯，這樣第一次執(zhí)行的時(shí)候DBMS會(huì)為這個(gè)SQL語(yǔ)句進(jìn)行查詢優(yōu)化 并且執(zhí)行預(yù)編譯，這樣以后再執(zhí)行這個(gè)SQL的時(shí)候就直接使用預(yù)編譯的結(jié)果，這樣可以大大提高執(zhí)行的速度。
 
四.調(diào)整Where字句中的連接順序
DBMS一般采用自下而上的順序解析where字句，根據(jù)這個(gè)原理表連接最好寫在其他where條件之前，那些可以 過(guò)濾掉最大數(shù)量記錄。
 
五.盡量將多條SQL語(yǔ)句壓縮到一句SQL中
每次執(zhí)行SQL的時(shí)候都要建立網(wǎng)絡(luò)連接、進(jìn)行權(quán)限校驗(yàn)、進(jìn)行SQL語(yǔ)句的查詢優(yōu)化、發(fā)送執(zhí)行結(jié)果，這個(gè)過(guò)程 是非常耗時(shí)的，因此應(yīng)該盡量避免過(guò)多的執(zhí)行SQL語(yǔ)句，能夠壓縮到一句SQL執(zhí)行的語(yǔ)句就不要用多條來(lái)執(zhí)行。
六.用where字句替換HAVING字句
避免使用HAVING字句，因?yàn)镠AVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾，而where則是在聚合前 刷選記錄，如果能通過(guò)where字句限制記錄的數(shù)目，那就能減少這方面的開銷。HAVING中的條件一般用于聚合函數(shù) 的過(guò)濾，除此之外，應(yīng)該將條件寫在where字句中。
 
七.使用表的別名
當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)列名上。這樣就可以減少解析的時(shí)間并減 少哪些友列名歧義引起的語(yǔ)法錯(cuò)誤。
 
八.用union all替換union
當(dāng)SQL語(yǔ)句需要union兩個(gè)查詢結(jié)果集合時(shí)，即使檢索結(jié)果中不會(huì)有重復(fù)的記錄，如果使用union這兩個(gè)結(jié)果集 同樣會(huì)嘗試進(jìn)行合并，然后在輸出最終結(jié)果前進(jìn)行排序，因此如果可以判斷檢索結(jié)果中不會(huì)有重復(fù)的記錄時(shí)候，應(yīng) 該用union all，這樣效率就會(huì)因此得到提高。
 
九.考慮使用“臨時(shí)表”暫存中間結(jié)果
簡(jiǎn)化SQL語(yǔ)句的重要方法就是采用臨時(shí)表暫存中間結(jié)果，但是，臨時(shí)表的好處遠(yuǎn)遠(yuǎn)不止這些，將臨時(shí)結(jié)果暫存在臨時(shí)表，后面的查詢就在tempdb中了，這可以避免程序中多次掃描主表，也大大減少了程序執(zhí)行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發(fā)性能。
但是也得避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。
 
十.只在必要的情況下才使用事務(wù)begin translation
SQL Server中一句SQL語(yǔ)句默認(rèn)就是一個(gè)事務(wù)，在該語(yǔ)句執(zhí)行完成后也是默認(rèn)commit的。其實(shí)，這就是begin tran的一個(gè)最小化的形式，好比在每句語(yǔ)句開頭隱含了一個(gè)begin tran，結(jié)束時(shí)隱含了一個(gè)commit。 有些情況下，我們需要顯式聲明begin tran，比如做“插、刪、改”操作需要同時(shí)修改幾個(gè)表，要求要么幾個(gè)表都修改成功，要么都不成功。begin tran 可以起到這樣的作用，它可以把若干SQL語(yǔ)句套在一起執(zhí)行，最后再一起commit。 好處是保證了數(shù)據(jù)的一致性，但任何事情都不是完美無(wú)缺的。Begin tran付出的代價(jià)是在提交之前，所有SQL語(yǔ)句鎖住的資源都不能釋放，直到commit掉。 可見(jiàn)，如果Begin tran套住的SQL語(yǔ)句太多，那數(shù)據(jù)庫(kù)的性能就糟糕了。在該大事務(wù)提交之前，必然會(huì)阻塞別的語(yǔ)句，造成block很多。 Begin tran使用的原則是，在保證數(shù)據(jù)一致性的前提下，begin tran 套住的SQL語(yǔ)句越少越好！有些情況下可以采用觸發(fā)器同步數(shù)據(jù)，不一定要用begin tran。
 
十一.盡量避免使用游標(biāo)
盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過(guò)大，應(yīng)該考慮相應(yīng)需求是否合理。因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫。
 
十二.用varchar/nvarchar 代替 char/nchar
盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。 不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時(shí)，空間就固定了， 不管是否插入值（NULL也包含在內(nèi)），都是占用 100個(gè)字符的空間的，如果是varchar這樣的變長(zhǎng)字段， null 不占用空間。
十三.查詢select語(yǔ)句優(yōu)化
1.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段
2.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，
如：
     select id from t where num is null          
可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，
然后這樣查詢：
      select id from t where num=0
      select id from t where num=10 or num=20
可以這樣查詢：
      select id from t where num=10
       union all
      select id from t where num=20
4.不能前置百分
select id from t where name like ‘%abc%’
若要提高效率，可以考慮全文檢索。         
     select id from t where num in(1,2,3)
對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：
    select id from t where num between 1 and 3
6.如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。
      例如：表A（小表），表B（大表）
 select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；    
 select * from A where exists(select cc from B where cc=A.cc)   效率高，用到了B表上cc列的索引。  
相反的
 select * from B where cc in (select cc from A)  效率高，用到了B表上cc列的索引；
 select * from B where exists(select cc from A where cc=B.cc)  效率低，用到了A表上cc列的索引。        
 
十四.更新Update語(yǔ)句優(yōu)化
1.如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來(lái)大量日志
 
十五. 刪除Delete語(yǔ)句優(yōu)化語(yǔ)句
1.最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
 
十六.插入Insert語(yǔ)句優(yōu)化
1.在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。