數(shù)據(jù)庫SQL調優(yōu)

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

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

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