Mysql大表查詢最佳化技巧總結及案例分析

標籤：http   io   使用   ar   strong   for   資料   div   sp   

http://www.169it.com/article/3219955334.html

    sql語句使用基本原則:1.對查詢進行最佳化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如： select id...

 

sql語句使用基本原則:

1.對查詢進行最佳化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：

select id from t where num=0

3.應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

4.應盡量避免在 where 子句中使用 or 來串連條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

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

5.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查詢也將導致全表掃描：

select id from t where name like ‘%abc%‘

若要提高效率，可以考慮全文檢索索引。

7.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但最佳化程式不能將訪問計劃的選擇延遲到運行時；它必須在編譯時間進行選擇。然而，如果在編譯時間建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where [email protected]

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where [email protected]

8.應盡量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

9.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)=‘abc‘--name以abc開頭的id

select id from t where datediff(day,createdate,‘2005-11-30‘)=0--‘2005-11-30’產生的id

應改為:

select id from t where name like ‘abc%‘

select id from t where createdate>=‘2005-11-30‘ and createdate<‘2005-12-1‘

10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。

11.在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。

12.不要寫一些沒有意義的查詢，如需要產生一個空表結構：

select col1,col2 into #t from t where 1=0

這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：

create table #t(...)

13.很多時候用 exists 代替 in 是一個好的選擇：

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)

14.並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢最佳化的，當索引列有大量資料重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

15.索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要謹慎考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

16.應儘可能的避免更新 clustered 索引資料列，因為 clustered 索引資料列的順序就是表記錄的實體儲存體順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引資料列，那麼需要考慮是否應將該索引建為 clustered 索引。

17.盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字元型，這會降低查詢和串連的效能，並會增加儲存開銷。這是因為引擎在處理查詢和串連時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。

18.儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。

19.任何地方都不要使用 select * from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。

20.盡量使用表變數來代替暫存資料表。如果表變數包含大量資料，請注意索引非常有限（只有主鍵索引）。

21.避免頻繁建立和刪除暫存資料表，以減少系統資料表資源的消耗。

22.暫存資料表並不是不可使用，適當地使用它們可以使某些常式更有效，例如，當需要重複引用大型表或常用表中的某個資料集時。但是，對於一次性事件，最好使用匯出表。

23.在建立暫存資料表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統資料表的資源，應先create table，然後insert。

24.如果使用到了暫存資料表，在預存程序的最後務必將所有的暫存資料表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統資料表的較長時間鎖定。

25.盡量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。

26.使用基於遊標的方法或暫存資料表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。

27.與暫存資料表一樣，遊標並不是不可使用。對小型資料集使用 FAST_FORWARD 遊標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的資料時。在結果集中包括“合計”的常式通常要比使用遊標執行的速度快。如果開發時間允許，基於遊標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的預存程序和觸發器的開始處設定 SET NOCOUNT ON ，在結束時設定 SET NOCOUNT OFF 。無需在執行預存程序和觸發器的每個語句後向用戶端發送 DONE_IN_PROC 訊息。

29.盡量避免大事務操作，提高系統並發能力。

30.盡量避免向用戶端返回大資料量，若資料量過大，應該考慮相應需求是否合理。

綜合最佳化案例參考1:

提問：如何設計或最佳化千萬層級的大表？此外無其他資訊，個人覺得這個話題有點範，就只好簡單說下該如何做，對於一個儲存設計，必須考慮業務特點，收集的資訊如下：

1.資料的容量：1-3年內會大概多少條資料，每條資料大概多少位元組；

2.資料項目：是否有大欄位，那些欄位的值是否經常被更新；

3.資料查詢SQL條件：哪些資料項目的列名稱經常出現在WHERE、GROUP BY、ORDER BY子句中等；

4.資料更新類SQL條件：有多少列經常出現UPDATE或DELETE 的WHERE子句中；

5.SQL量的統計比，如：SELECT：UPDATE+DELETE：INSERT=多少？

6.預計大表及相關聯的SQL，每天總的執行量在何數量級？

7.表中的資料：更新為主的業務 還是 查詢為主的業務

8.打算採用什麼資料庫物理伺服器，以及資料庫伺服器架構？

9.並發如何？

10.儲存引擎選擇InnoDB還是MyISAM？

大致明白以上10個問題，至於如何設計此類的大表，應該什麼都清楚了！

至於最佳化若是指建立好的表，不能變動表結構的話，那建議InnoDB引擎，多利用點記憶體，減輕磁碟IO負載，因為IO往往是資料庫伺服器的瓶頸另外對最佳化索引結構去解決效能問題的話，建議優先考慮修改類SQL語句，使他們更快些，不得已只靠索引組織圖的方式，當然此話前提是，索引已經建立的非常好，若是讀為主，可以考慮開啟query_cache，以及調整一些參數值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

綜合最佳化案例參考2:

我現在的公司有三張表，是5億的資料，每天張表每天的增量是100w,每張表大概在10個columns左右,下面是我做的測試和對比:

1.首先看engine,在大資料量情況下，在沒有做分區的情況下 mysiam比innodb在唯讀情況下，效率要高13％左右 。

2.在做了partition之後，你可以去讀一下mysql的官方文檔，其實對於partition，專門是對myisam做的最佳化，對於innodb，所有的資料是存在ibdata裡面的，所以即使你可以看到schema變了，其實沒有本質的變化 在分區出於同一個physical disk下面的情況下，提升大概只有1％ 。在分區在不同的physical disk下，我分到了三個不同的disks下，提升大概在3％，其實所謂的輸送量，由很多因素決定的，比如你的explain parition時候可以看到，record在那一個分區，如果每個分區都有，其實本質上沒有解決讀的問題，這樣只會提升寫的效率。另外一個問題在於，分區，你怎麼分，如果一張表，有三個column都是經常被用於做查詢條件的，其實是一件很悲慘的事情，因為你沒有辦法對所有的sql做針對性的分區，如果你只是如mysql官方文檔上說的，只對時間做一個分區，而且你也只用時間查詢的話，恭喜你 。

3.表主要用來讀還是寫，其實這個問題是不充分的，應該這樣問，你在寫入的時候，同時並發的查詢多嗎？我的問題還比較簡單，因為mongodb的shredding支援不能，在crush之後，還是回到mysql，所以在通常情況下，9am－9pm，寫入的情況很多，這個時候我會做一個view，view是基於最近被插入或者經常被查詢的，通過做view來分離讀取，就是說寫是在table上的，讀在進行邏輯判斷前是在view上操作的。

4.做一些archive table，比如先對這些大表做很多已有的統計分析，然後通過已有的分析＋增量來解決。

5.如果你用mysiam，還有一個問題你要注意，如果你的.configure的時候，加了一個max index length參數的時候，當你的record數大於制定長度的時候，這個index會被disable。

 

Mysql大表查詢最佳化技巧總結及案例分析