資料庫性能優化

關系型資料庫性能優化總結

對于web應用開發，多數性能瓶頸均出現在資料庫上，除了采用分布式架構或雲處理（大公司基本上都是），更重要的是平時程式設計時要遵照一些規則，從根本上提高系統的性能，以下總結了一些常用的規則方法，

1、 把資料、日志、索引放到不同的I/O裝置上，增加讀取速度。資料量（尺寸）越大，提高I/O越重要。

2、 縱向、橫向分割表，減少表的尺寸，如：可以把大資料量的字段拆分表。

3、 根據查詢條件，建立索引，優化索引、優化通路方式，限制結果集的資料量。注意填充因子要适當（最好是使用預設值0）。索引應該盡量小，盡量使用位元組數小的列建索引，不要對有限的幾個值的列建單一索引。

4、 用OR的字句可以分解成多個查詢，并且通過UNION連結多個查詢。它們的速度隻與是否使用索引有關，如果查詢需要用到聯合索引，用UNION all執行的效率更高。

5、 在查詢SELECT語句中用WHERE子句限制傳回的行數，避免表掃描。如果傳回不必要的資料，則浪費了伺服器的I/O資源，加重了網絡的負擔，降低了性能。如果表很大，在表掃描期間将表鎖住，禁止其他的聯結通路表，後果很嚴重。

6、 注意使用DISTINCT，在沒有必要時不要用，它同UNION一樣會使查詢變慢。

7、 在IN後面值的清單中，将出現最頻繁的值放在最前面，出現最少的放在最後面，減少判斷的次數。

8、 一般在GROUP BY和HAVING子句之前就能剔除多餘的行，是以盡量不要用它們來做剔除行的工作，也就是說盡可能在WHERE中過濾資料。

9、 盡量将資料的處理工作放在伺服器上，減少網絡的開銷，如使用存儲過程。存儲過程是編譯、優化過，并且被組織到一個執行規劃裡，且存儲在資料庫中的SQL語句（存儲過程是資料庫伺服器端的一段程式），是控制流語言的集合，速度當然快。

10、 不要在一句話裡再三地使用相同的函數，浪費資源，将結果放在變量裡再調用更快。

11、針對大量隻讀查詢操作進行優化的方法：

1)  資料量小的資料，可以考慮不存儲在資料庫中，而是通過程式常量的方式解決。

當然也可以放在memcache或者redis等緩存中。還有就是hibernate,spring的緩存。

2)    需要存儲在資料庫中的資料，可以考慮采用物化視圖（索引視圖）。當DBA在視圖上建立索引時，這個視圖就被物化（執行）了，并且結果集被永久地儲存在唯一索引中，儲存方式與一個有聚簇索引的表的儲存方式相同。物化視圖減除了為引用視圖的查詢動态建立結果集的開銷，優化人員可以在查詢中使用視圖索引，而不需要在FROM子句中直接指定視圖。

3)    資料存儲時可以考慮适當的資料備援，以減少資料庫表之間的連結操作，提高查詢效率。

4)  針對資料的特點，采取特定的索引類型。例如，位圖索引等。

索引注意事項：

<1>.索引是建立在常用的列上，比如你經常根據某一列（使用者名，密碼）去查資料

<2>.索引相當于字典前面的目錄。

<3>.加不加索引要權衡一下是查詢多，還是update,insert,delete多！！！

 雖然使用索引能得到查詢效率的提高，但是我們也必須注意到他的代價。索引需要空間來存儲，也需要定期維護， 每當有記錄在表中增減或索引列被修改時， 索引本身也會被修改。這意味着每條記錄的INSERT，DELETE ，UPDATE将為此多付出4、5次的磁盤I/O 。因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。

《4》并不是所有的表都适合加索引的！！

①記錄少是不要加索引！

②每次查詢的資料占總的數量很大時，不要加索引。

③多列索引：對查詢頻率高的列放在前面。

④資料庫量很大，并且每次查詢的資料占總量比較少時，加索引誰體會到明顯的效率！

12、對于SQL語句書寫時的一些建議：

1)         寫語句時能夠确定資料庫對象所有者的，盡可能把所有者帶上，如：

SELECT * FROM dbo.Users

2)         存儲過程中，參數定義最好放在最前面，盡可能一次定義，如：

DECLARE @USER_ID INT        ,@USER_NAME   VARCHAR(50)        ,@PASSWORD VARCHAR(50)

3)         為參數指派時，盡可能一次指派，如：

SELECT @USER_ID = 1001        ,@USER_NAME = 'xiaojun.liu'

4)         盡量少用遊标

5)      不使用*，少使用連接配接查詢，少用like操作等

二：其他的參考http://blog.sina.com.cn/s/blog_76c0a1c50101d0gq.html

常見的資料庫優化方法：索引（資料庫），緩存，分表，分庫，sql優化。

索引：建立索引一般有以下兩個目的：維護被索引列的唯一性和提供快速通路表中資料的政策。95% 的資料庫能 問題都可以采用索引技術得到解決。索引有助于提高檢索性能，但過多或不當的索引也會導緻系統低 效。因為使用者在表中每加進一個索引，資料庫就要做更多的工作。過多的索引甚至會導緻索引碎片。 

緩存：hibernate，spring3有緩存子產品

分表：針對每個時間周期産生大量的資料，可以考慮采用一定的政策将資料存到多個資料表中。

分庫：就是将系統按照子產品相關的特征分布到不同的資料中，以提高系統整體負載能力。

sql優化：

 1.in 和 not in 也要慎用，因為IN會使系統無法使用索引,而隻能直接搜尋表中的資料。如：

select id from t where num in(1,2,3)對于連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

2.當判斷真假是，如果帶and 或者 or :

（當存在 “where 條件1 and 條件2” 時，資料庫先執行右邊的語句）

and盡量把假的放到右邊（一個為假就為假）  Or盡量把為真的放到右邊（一個為真就為真）

3.應盡量避免在 where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。 如：

SELECT * FROM T1 WHERE F1/2=100 

應改為: 

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’ 

SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members 

WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 

WHERE dateofbirth < DATEADD(yy,-21,GETDATE()) 

即：任何對列的操作都将導緻表掃描，它包括資料庫函數、計算表達式等等，查詢時 要盡可能将操作移至等号右邊。

4.很多時候用 exists是一個好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

SELECT SUM(T1.C1)FROM T1 WHERE( 

(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0) 

SELECT SUM(T1.C1) FROM T1 WHERE EXISTS( 

SELECT * FROM T2 WHERE T2.C2=T1.C2) 

兩者産生相同的結果，但是後者的效率顯然要高于前者。因為後者不會産生大量鎖定 的表掃描或是索引掃描。

如果你想校驗表裡是否存在某條紀錄，不要用count(*)那樣效率很低，而且浪費服務 器資源。可以用EXISTS代替。如： 

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') 

可以寫成： 

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

5.充分利用連接配接條件，在某種情況下，兩個表之間可能不隻一個的連接配接條件，這時在 WHERE 子句中将 連接配接條件完整的寫上，有可能大大提高查詢速度。 

例： 

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO 

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO 

第二句将比第一句執行快得多。

6.使用視圖加速查詢 

把表的一個子集進行排序并建立視圖，有時能加速查詢。它有助于避免多重排序 操 作，而且在其他方面還能簡化優化器的工作。例如：

SELECT cust.name，rcvbles.balance，……other columns 

FROM cust，rcvbles 

WHERE cust.customer_id = rcvlbes.customer_id 

AND rcvblls.balance>0 

AND cust.postcode>“98000” 

ORDER BY cust.name

如果這個查詢要被執行多次而不止一次，可以把所有未付款的客戶找出來放在一個視 圖中，并按客戶的名字進行排序： 

CREATE VIEW DBO.V_CUST_RCVLBES 

AS 

ORDER BY cust.name 

然後以下面的方式在視圖中查詢： 

SELECT ＊ FROM V_CUST_RCVLBES 

WHERE postcode>“98000” 

視圖中的行要比主表中的行少，而且實體順序就是所要求的順序，減少了磁盤I/O，是以查詢工作量可以得到大幅減少。