數據庫方面總結

2019-11-03 08:34:55

字體：大中小

來源：轉載

供稿：網友

SQL性能優化對于程序員來說是一個很大的挑戰，因為我們往往會遇到這樣的問題：當我們開發完一個項目，感覺功能上自己測試的體驗真的很不錯，但真正項目上線后，隨著數據的海量增加，系統的客戶體驗越來越差。當然除過框架，代碼不夠合理外，主要還是SQL沒能優化，而導致系統越來越慢。

       因為我在一個小公司，所以什么都干，我覺得有時候治標得治本！有以下幾個問題得注意：

      1、數據庫表的設計要合理，特別是主鍵的設計，如果表的數據量非常大，主鍵的設計不要讓有實際意義，就像ROWID一樣，比如SQLServer的GUID，Hibernate的UUID等。當然一些數據字典表可以靈活處理了，不必考慮非得是物理主鍵了。主鍵設計中，一般也不要用復合主鍵。

      2、合理的加索引。索引是加快我們數據查詢速度的一把利器，是一種很好的手段。但不要見什么字段都加吧。索引的原理就像書的目錄一樣，如果你書的目錄幾乎全是一個名字，你可以想象以下，你自己根據目錄找具體內容時能快嗎？索引不一定要力求必須唯一，但也不要相同的記錄太多了。再就是，索引加多了，TEMP表空間會增大，將表導出在導入另一個庫時，由于索引也會降低導入的效率，此時你也會發現UNDOTBS01表空間異常的大。所以索引是一把雙刃劍，一定要合理的應用。

     3、我曾經在網上看到過好過關于SQL優化的很專業的文章，但我感覺并沒有因此而使自己能夠在項目中用到這些。反而是自己在項目進行的過程中，不斷地實驗發現一些基本的原理。我自己認為SQL優化的原理只有一個那就是盡可能的縮小查詢范圍，這樣效率肯定會有提高，而且Oracle本身對我們所寫的SQL能夠優化，所以我們要做的就是盡可能的縮小查詢范圍，說到這我想大家一定會想到我說的加索引是提高查詢速度的一把利器，的確它只是一種手段，它也源于縮小查詢范圍的原理。

      大多數需要優化的SQL都是多表連接的查詢，而多表連接也包括橫向連接，縱向連接，我們使用最多的就是縱向連接。橫向連接一般是指兩個表的字段結構基本一樣，要把一個表的數據某些記錄變成另一個表的一些記錄，即Rows+Rows?？v向連接即我們從A表取一些要查詢的字段，從B表取一些要查詢的字段，然后將A、B表取出來的表用公共的部分縱向連接起來，即Columns+Columns。

      橫向連接語句： select a.column1,a.column2 from tableA a union all select b.column1,b.column2 from tableB b

注意，橫向連接時，列個數必須相同，而且相對應的字段列數據類型必須相同。其實你就可以把要union的表認為是一個是另一個復制，完全相同。有人可能會問了，要是我要合并的列的的確有不同的列啊，或者有一列根本就沒有，那你可以采用一下方式

      select d.dname,d.loc from dept1 d  union all select '' dname, e.loc  from dept e，看“'' dname”，我們不難發現，你可以找個替代品啊，用空字符串代替沒有的字段，這樣就可以合并了。

      縱向連接語句： select a.column1,a.column2 from tableA a full outer join select b.column3,b.column4 from tableB b on a.aid=b.bid where ......，這是全外連接格式。這個速度的確挺快，但查詢可能你不喜歡，因為有些結果行可能你根本不想看到。一般情況下我們left outer join,right outer join用的比較多，這兩個的區別就是left outer join以on后面處在左邊的連接字段所對應表為主，right outer join剛好相對。當然你也可以使用left join, right join。在使用過程中還是發現外連接相對快些。

      要加快縱向連接查詢效率，辦法就是嵌套查詢。以下是項目中實際的一個例子：

select c.customerid,c.receivedmoney,c.tollcollector,c.receiveddate,c.yearmonth,c.receivedlatefee,

c.receivedfee,c.receivedappend,c.jmman,c.jmmoney,c.name,d.chargeint from

(select  a.customerid,a.receivedmoney,a.tollcollector,a.receiveddate,a.yearmonth,a.receivedlatefee,

a.receivedfee,a.receivedappend,a.jmman,a.jmmoney,b.name from

(select rf.customerid,rf.receivedmoney,rf.tollcollector,rf.receiveddate,rf.yearmonth,rf.receivedlatefee,

rf.receivedfee,rf.receivedappend,rf.jmman,rf.jmmoney from sf_receivedfee rf where

rf.electriccompanyid='1000000001' and rf.dealsign=0 and rf.yearmonth in(200811,200901,200903,200804,200805,200806,200807)

and rf.customerid=1000052545) a left outer join (select xe.employeeid,xe.name from xt_employee xe) b on a.tollcollector=b.employeeid)

c left outer join (select cp.chargeint,cp.customerid from sf_chargePRotocol cp where cp.customerid=1000052545) d

on c.customerid=d.customerid

你可以看到，這個例子中，我們先從每個表用幾乎同樣的條件過濾出我們需要的記錄，再將記錄合并，我實際使用中發現這比直接做鏈接查詢要快將近60多倍。雖然很丑，而且很難讀懂，但的確解決了SQL性能問題。它運用的原理依然是先縮小范圍，然后再進行連接查詢，如果我們連接再過濾，那么這其中相當于兩個表合并，然后再根據條件取數據，速度可想而知。

上一篇：SQL語法總結

下一篇：sqlserver 存儲過程例子