利用MySQL資料庫如何解決大資料量儲存問題？

標籤：增量   fill   批量   結束   統計   sdn   十分鐘   實驗   固定   

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

更多資訊參見：
MySQL資料庫伺服器端核心參數詳解和推薦配置
mysqlops.com/2011/10/26
您好，主要是檢索某段時間內的類比量值（select * from table where datatime between t1 and t2 ）,目前打算使用分表，分區的方式解決

不紙上談兵，說一下我的思路以及我的解決，拋磚引玉了 
我最近正在解決這個問題 
我現在的公司有三張表，是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 
6 

照你的需求來看，可以有兩種方式，一種是分表，另一種是分區
首先是分表，就像你自己所說的，可以按月分表，可以按使用者ID分表等等，至於採用哪種方式分表，要看你的商務邏輯了，分表不好的地方就是查詢有時候需要跨多個表。

然後是分區，分區可以將表分離在若干不同的資料表空間上，用分而治之的方法來支撐無限膨脹的大表，給大表在物理一級的可管理性。將大表分割成較小的分區可以改善表的維護、備份、恢複、事務及查詢效能。分區的好處是分區的優點：

1　增強可用性：如果表的一個分區由於系統故障而不能使用，表的其餘好的分區仍然可以使用；

2　減少關閉時間：如果系統故障隻影響表的一部分分區，那麼只有這部分分區需要修複，故能比整個大表修複花的時間更少；

3　維護輕鬆：如果需要重建表，獨立管理每個分區比管理單個大表要輕鬆得多；

4　均衡I/O:可以把表的不同分區分配到不同的磁碟來平衡I/O改善效能；

5　改善效能：對大表的查詢、增加、修改等操作可以分解到表的不同分區來並存執行，可使運行速度更快；

6　分區對使用者透明，終端使用者感覺不到分區的存在。

 

導致節點插入時間非常慢的原因：

      1、串連資料庫的問題：建立串連和關閉串連的次數太多，導致IO訪問次數太頻繁。

       2、應該使用批量插入和批量修改的方法，而不是有一條資料就進行插入，這樣會導致訪問資料庫的實際特別的慢。

       3、在建立庫的時候要建立適當的索引：如主鍵、外鍵、唯一等，最佳化查詢效率。

 

首先，資料量大的時候，應盡量避免全表掃描，應考慮在 where 及 order by 涉及的列上建立索引，建索引可以大大加快資料的檢索速度。 但是，有些情況索引是不會起效的：

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

2、應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如： 
     select id from t where num is null 
     可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢： 
     select id from t where num=0

3、盡量避免在 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

4、下面的查詢也將導致全表掃描：

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

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

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、如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但最佳化程式不能將訪問計劃的選擇延遲到運行時；它必須在編譯時間進行選擇。然而，如果在編譯時間建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描： 
     select id from t where [email protected] 
     可以改為強制查詢使用索引： 
     select id from t with(index(索引名)) where [email protected]

7、應盡量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描。如： 
     select id from t where num/2=100 
     應改為: 
     select id from t where num=100*2

8、應盡量避免在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′

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

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

11、不要寫一些沒有意義的查詢，如需要產生一個空表結構： 
     select col1,col2 into #t from t where 1=0 
     這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣： 
     create table #t(…)

12、很多時候用 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)

 

    建索引需要注意的地方：

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

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

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

 

    其他需要注意的地方：

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

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

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

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

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

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

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

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

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

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

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

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

13、盡量避免大事務操作，提高系統並發能力

 如今隨著互連網的發展，資料的量級也是撐指數的增長，從GB到TB到PB。對資料的各種操作也是愈加的困難，傳統的關係性資料庫已經無法滿足快速查詢與插入資料的需求。這個時候NoSQL的出現暫時解決了這一危機。它通過降低資料的安全性，減少對事務的支援，減少對複雜查詢的支援，來擷取效能上的提升。但是，在有些場合NoSQL一些折衷是無法滿足使用情境的，就比如有些使用情境是絕對要有事務與安全指標的。這個時候NoSQL肯定是無法滿足的，所以還是需要使用關係性資料庫。

  雖然關係型資料庫在海量資料中遜色於NoSQL資料庫，但是如果你操作正確，它的效能還是會滿足你的需求的。針對資料的不同操作，其最佳化方向也是不盡相同。對於資料移植，查詢和插入等操作，可以從不同的方向去考慮。而在最佳化的時候還需要考慮其他相關操作是否會產生影響。就比如你可以通過建立索引提高查詢效能，但是這會導致插入資料的時候因為要建立更新索引導致插入效能降低，你是否可以接受這一降低那。所以，對資料庫的最佳化是要考慮多個方向，尋找一個折衷的最佳方案。

 

 

  一：查詢最佳化   1：建立索引。

  最簡單也是最常用的最佳化就是查詢。因為對於CRUD操作，read操作是佔據了絕大部分的比例，所以read的效能基本上決定了應用的效能。對於查詢效能最常用的就是建立索引。經過測試，2000萬條記錄，每條記錄200位元組兩列varchar類型的。當不使用索引的時候查詢一條記錄需要一分鐘，而當建立了索引的時候查詢時間可以忽略。但是，當你在已有資料上添加索引的時候，則需要耗費非常大的時間。我插入2000萬條記錄之後，再建立索引大約話費了幾十分鐘的樣子。

  建立索引的弊端和場合。雖然建立索引可以很大程度上最佳化查詢的速度，但是弊端也是很明顯的。一個是在插入資料的時候，建立索引也需要消耗部分的時間，這就使得插入效能在一定程度上降低；另一個很明顯的是資料檔案變的更大。在列上建立索引的時候，每條索引的長度是和你建立列的時候制定的長度相同的。比如你建立varchar(100)，當你在該列上建立索引，那麼索引的長度則是102位元組，因為長度超過64位元組則會額外增加2位元組記錄索引的長度。

  從可以看到我在YCSB_KEY這一列(長度100)上建立了一個名字為index_ycsb_key的索引，每條索引長度都為102，想象一下當資料變的巨大無比的時候，索引的大小也是不可以小覷的。而且從這也可以看出，索引的長度和列類型的長度還不同，比如varchar它是變長的字元類型(請看MySQL資料類型分析)，實際儲存長度是是實際字元的大小，但是索引卻是你聲明的長度的大小。你建立列的時候聲明100位元組，那麼索引長度就是這個位元組再加上2，它不管你實際儲存是多大。

  除了建立索引需要消耗時間，索引檔案體積會變的越來越大之外，建立索引也需要看的你儲存資料的特徵。當你儲存資料很大一部分都是重複記錄，那這個時候建立索引是百害而無一利。請先查看MySQL索引介紹。所以，當很多資料重複的時候，索引帶來的查詢提升的效果是可以直接忽略的，但是這個時候你還要承受插入資料的時候建立索引帶來的效能消耗。

  2：緩衝的配置。

  在MySQL中有多種多樣的緩衝，有的緩衝負責緩衝查詢語句，也有的負責緩衝查詢資料。這些緩衝內容用戶端無法操作，是由server端來維護的。它會隨著你查詢與修改等相應不同操作進行不斷更新。通過其設定檔我們可以看到在MySQL中的緩衝：

  在這裡主要分析query cache，它是主要用來緩衝查詢資料。當你想使用該cache，必須把query_cache_size大小設定為非0。當設定大小為非0的時候，server會就會緩衝每次查詢返回的結果，到下次相同查詢server就直接從緩衝擷取資料，而不是再執行查詢。能緩衝的資料量就和你的size大小設定有關，所以當你設定的足夠大，資料可以完全緩衝到記憶體，速度就會非常之快。

  但是，query cache也有它的弊端。當你對資料表做任何的更新操作(update/insert/delete)等操作，server為了保證緩衝與資料庫的一致性，會強制重新整理快取資料，導致快取資料全部失效。所以，當一個表格的更新資料表操作非常多的話，query cache是不會起到查詢提升的效能，還會影響其他動作的效能。

  3：slow_query_log分析。

  其實對於查詢效能提升，最重要也是最根本的手段也是slow_query的設定。

  當你設定slow_query_log為on的時候，server端會對每次的查詢進行記錄，當超過你設定的慢查詢時間(long_query_time)的時候就把該條查詢記錄到日誌。而你對效能進行最佳化的時候，就可以分析慢查詢日誌，對慢查詢的查詢語句進行有目的的最佳化。可以通過建立各種索引，可以通過分表等操作。那為什麼要分庫分表那，當不分庫分表的時候那個地方是限制效能的地方啊。下面我們就簡單介紹。

  4：分庫分表

  分庫分表應該算是查詢最佳化的殺手鐧了。上述各種措施在資料量達到一定等級之後，能起到最佳化的作用已經不明顯了。這個時候就必須對資料量進行分流。分流一般有分庫與分表兩種措施。而分表又有垂直切分與水平切分兩種方式。下面我們就針對每一種方式簡單介紹。

  對於mysql，其資料檔案是以檔案形式儲存在磁碟上的。當一個資料檔案過大的時候，作業系統對大檔案的操作就會比較麻煩與耗時，而且有的作業系統就不支援大檔案，所以這個時候就必須分表了。另外對於mysql常用的儲存引擎是Innodb，它的底層資料結構是B+樹。當其資料檔案過大的時候，B+樹就會從層次和節點上比較多，當查詢一個節點的時候可能會查詢很多層次，而這必定會導致多次IO操作進行裝載進記憶體，肯定會耗時的。除此之外還有Innodb對於B+樹的鎖機制。對每個節點進行加鎖，那麼當更改表結構的時候，這時候就會樹進行加鎖，當表檔案大的時候，這可以認為是不可實現的。 

  所以綜上我們就必須進行分表與分庫的操作。

 

 二：資料轉移

  當資料量達到一定等級之後，那麼移庫將是一個非常謹慎又危險的工作。在移庫中保證前後資料的一致性，各種突發情況的處理，移庫過程中資料的變遷，每一個都是一個非常困難的問題。

  2.1：插入資料  當進行資料移轉的時候，肯定會存在大資料的重新匯入，你可以選擇直接load檔案，有的時候可能就需要代碼插入了。這個時候就需要對插入語句進行一定的最佳化了。這個時候可以使用INSERT DELAYED語句，該語句是當你發出插入請求的時候，部馬上就插入到資料庫而是放在緩衝裡面，等待時機成熟之後再進行插入。

待補充。。。

   1：建立索引。

  最簡單也是最常用的最佳化就是查詢。因為對於CRUD操作，read操作是佔據了絕大部分的比例，所以read的效能基本上決定了應用的效能。對於查詢效能最常用的就是建立索引。經過測試，2000萬條記錄，每條記錄200位元組兩列varchar類型的。當不使用索引的時候查詢一條記錄需要一分鐘，而當建立了索引的時候查詢時間可以忽略。但是，當你在已有資料上添加索引的時候，則需要耗費非常大的時間。我插入2000萬條記錄之後，再建立索引大約話費了幾十分鐘的樣子。

  建立索引的弊端和場合。雖然建立索引可以很大程度上最佳化查詢的速度，但是弊端也是很明顯的。一個是在插入資料的時候，建立索引也需要消耗部分的時間，這就使得插入效能在一定程度上降低；另一個很明顯的是資料檔案變的更大。在列上建立索引的時候，每條索引的長度是和你建立列的時候制定的長度相同的。比如你建立varchar(100)，當你在該列上建立索引，那麼索引的長度則是102位元組，因為長度超過64位元組則會額外增加2位元組記錄索引的長度。

  從可以看到我在YCSB_KEY這一列(長度100)上建立了一個名字為index_ycsb_key的索引，每條索引長度都為102，想象一下當資料變的巨大無比的時候，索引的大小也是不可以小覷的。而且從這也可以看出，索引的長度和列類型的長度還不同，比如varchar它是變長的字元類型(請看MySQL資料類型分析)，實際儲存長度是是實際字元的大小，但是索引卻是你聲明的長度的大小。你建立列的時候聲明100位元組，那麼索引長度就是這個位元組再加上2，它不管你實際儲存是多大。

  除了建立索引需要消耗時間，索引檔案體積會變的越來越大之外，建立索引也需要看的你儲存資料的特徵。當你儲存資料很大一部分都是重複記錄，那這個時候建立索引是百害而無一利。請先查看MySQL索引介紹。所以，當很多資料重複的時候，索引帶來的查詢提升的效果是可以直接忽略的，但是這個時候你還要承受插入資料的時候建立索引帶來的效能消耗。

  2：緩衝的配置。

  在MySQL中有多種多樣的緩衝，有的緩衝負責緩衝查詢語句，也有的負責緩衝查詢資料。這些緩衝內容用戶端無法操作，是由server端來維護的。它會隨著你查詢與修改等相應不同操作進行不斷更新。通過其設定檔我們可以看到在MySQL中的緩衝：

  在這裡主要分析query cache，它是主要用來緩衝查詢資料。當你想使用該cache，必須把query_cache_size大小設定為非0。當設定大小為非0的時候，server會就會緩衝每次查詢返回的結果，到下次相同查詢server就直接從緩衝擷取資料，而不是再執行查詢。能緩衝的資料量就和你的size大小設定有關，所以當你設定的足夠大，資料可以完全緩衝到記憶體，速度就會非常之快。

  但是，query cache也有它的弊端。當你對資料表做任何的更新操作(update/insert/delete)等操作，server為了保證緩衝與資料庫的一致性，會強制重新整理快取資料，導致快取資料全部失效。所以，當一個表格的更新資料表操作非常多的話，query cache是不會起到查詢提升的效能，還會影響其他動作的效能。

  3：slow_query_log分析。

  其實對於查詢效能提升，最重要也是最根本的手段也是slow_query的設定。

  當你設定slow_query_log為on的時候，server端會對每次的查詢進行記錄，當超過你設定的慢查詢時間(long_query_time)的時候就把該條查詢記錄到日誌。而你對效能進行最佳化的時候，就可以分析慢查詢日誌，對慢查詢的查詢語句進行有目的的最佳化。可以通過建立各種索引，可以通過分表等操作。那為什麼要分庫分表那，當不分庫分表的時候那個地方是限制效能的地方啊。下面我們就簡單介紹。

  4：分庫分表

  分庫分表應該算是查詢最佳化的殺手鐧了。上述各種措施在資料量達到一定等級之後，能起到最佳化的作用已經不明顯了。這個時候就必須對資料量進行分流。分流一般有分庫與分表兩種措施。而分表又有垂直切分與水平切分兩種方式。下面我們就針對每一種方式簡單介紹。

  對於mysql，其資料檔案是以檔案形式儲存在磁碟上的。當一個資料檔案過大的時候，作業系統對大檔案的操作就會比較麻煩與耗時，而且有的作業系統就不支援大檔案，所以這個時候就必須分表了。另外對於mysql常用的儲存引擎是Innodb，它的底層資料結構是B+樹。當其資料檔案過大的時候，B+樹就會從層次和節點上比較多，當查詢一個節點的時候可能會查詢很多層次，而這必定會導致多次IO操作進行裝載進記憶體，肯定會耗時的。除此之外還有Innodb對於B+樹的鎖機制。對每個節點進行加鎖，那麼當更改表結構的時候，這時候就會樹進行加鎖，當表檔案大的時候，這可以認為是不可實現的。 

  所以綜上我們就必須進行分表與分庫的操作。

 

 二：資料轉移

  當資料量達到一定等級之後，那麼移庫將是一個非常謹慎又危險的工作。在移庫中保證前後資料的一致性，各種突發情況的處理，移庫過程中資料的變遷，每一個都是一個非常困難的問題。

  2.1：插入資料  當進行資料移轉的時候，肯定會存在大資料的重新匯入，你可以選擇直接load檔案，有的時候可能就需要代碼插入了。這個時候就需要對插入語句進行一定的最佳化了。這個時候可以使用INSERT DELAYED語句，該語句是當你發出插入請求的時候，部馬上就插入到資料庫而是放在緩衝裡面，等待時機成熟之後再進行插入。

待補充。。。

mysql大資料量處理 一、概述 分表是個目前算是比較炒的比較流行的概念，特別是在大負載的情況下，分表是一個良好分散資料庫壓力的好方法。 首先要瞭解為什麼要分表，分表的好處是什麼。我們先來大概瞭解以下一個資料庫執行SQL的過程：接收到SQL --> 放入SQL執行隊列 --> 流量分析器分解SQL --> 按照分析結果進行資料的提取或者修改 --> 返回處理結果 當然，這個流程圖不一定正確，這隻是我自己主觀意識上這麼我認為。那麼這個處理過程當中，最容易出現問題的是什嗎？就是說，如果前一個SQL沒有執行完畢的話，後面的SQL是不會執行的，因為為了保證資料的完整性，必須對資料表檔案進行鎖定，包括共用鎖定和獨享鎖兩種鎖定。共用鎖定是在鎖定的期間，其它線程也可以訪問這個資料檔案，但是不允許修改操作，相應的，獨享鎖就是整個檔案就是歸一個線程所有，其它線程無法訪問這個資料檔案。一般MySQL中最快的儲存引擎MyISAM，它是基於表鎖定的，就是說如果一鎖定的話，那麼整個資料檔案外部都無法訪問，必須等前一個操作完成後，才能接收下一個操作，那麼在這個前一個操作沒有執行完成，後一個操作等待在隊列裡無法執行的情況叫做阻塞，一般我們通俗意義上叫做“鎖表”。 鎖表直接導致的後果是什嗎？就是大量的SQL無法立即執行，必須等隊列前面的SQL全部執行完畢才能繼續執行。這個無法執行的SQL就會導致沒有結果，或者延遲嚴重，影響使用者體驗。 特別是對於一些使用比較頻繁的表，比如SNS系統中的使用者資訊表、論壇系統中的文章表等等，都是訪問量大很大的表，為了保證資料的快速提取返回給使用者，必須使用一些處理方式來解決這個問題，這個就是我今天要聊到的分表技術。  分表技術顧名思義，就是把若干個儲存相同類型資料的表分成幾個表分表格儲存體，在提取資料的時候，不同的使用者訪問不同的表，互不衝突，減少鎖表的幾率。比如，目前儲存使用者分表有兩個表，一個是user_1表，還有一個是 user_2 表，兩個表儲存了不同的使用者資訊，user_1 儲存了前10萬的使用者資訊，user_2儲存了後10萬名使用者的資訊，現在如果同時查詢使用者 heiyeluren1 和 heiyeluren2 這個兩個使用者，那麼就是分表從不同的表提取出來，減少鎖表的可能。  我下面要講述的兩種分表方法我自己都沒有實驗過，不保證準確能用，只是提供一個設計思路。下面關於分表的例子我假設是在一個貼吧系統的基礎上來進行處理和構建的。（如果沒有用過貼吧的使用者趕緊Google一下）   二、基於基礎資料表的分表處理 這個基於基礎資料表的分表處理方式大致的思想就是：一個主要表，儲存了所有的基本資料，如果某個項目需要找到它所儲存的表，那麼必須從這個基礎資料表中尋找出對應的表名等項目，好直接存取這個表。如果覺得這個基礎資料表速度不夠快，可以完全把整個基礎資料表儲存在緩衝或者記憶體中，方便有效查詢。 我們基於貼吧的情況，構建假設如下的3張表： 1. 貼吧版塊表: 儲存貼吧中版塊的資訊2. 貼吧主題表：儲存貼吧中版塊中的主題資訊，用於瀏覽3. 貼吧回複表：儲存主題的原始內容和回複內容  “貼吧版塊表”包含如下欄位：版塊ID       board_id          int(10)版塊名稱    board_name      char(50)子表ID       table_id            smallint(5)產生時間    created             datetime “貼吧主題表”包含如下欄位：主題ID          topic_id        int(10)主題名稱        topic_name     char(255)版塊ID          board_id          int(10)建立時間       created           datetime “貼吧回複表”的欄位如下：回複ID        reply_id           int(10)回複內容      reply_text        text主題ID        topic_id           int(10)版塊ID        board_id         int(10)建立時間      created            datetime 那麼上面儲存了我們整個貼吧中的表結構資訊，三個表對應的關係是： 版塊 --> 多個主題主題 --> 多個回複 那麼就是說，表檔案大小的關係是：版塊表檔案 < 主題表檔案 < 回複表檔案 所以基本可以確定需要對主題表和回複表進行分表，已增加我們資料檢索查詢更改時候的速度和效能。 看了上面的表結構，會明顯發現，在“版塊表”中儲存了一個"table_id"欄位，這個欄位就是用於儲存一個版塊對應的主題和回複都是分表儲存在什麼表裡的。 比如我們有一個叫做“PHP”的貼吧，board_id是1，子表ID也是1，那麼這條記錄就是： board_id | board_name | table_id | created1 | PHP | 1 | 2007-01-19 00:30:12 相應的，如果我需要提取“PHP”吧裡的所有主題，那麼就必須按照表裡儲存的table_id來組合一個儲存了主題的表名稱，比如我們主題表的首碼是“topic_”，那麼組合出來“PHP”吧對應的主題表應該是：“topic_1”，那麼我們執行： SELECT * FROM topic_1 WHERE board_id = 1 ORDER BY topic_id DESC LIMIT 10 這樣就能夠擷取這個主題下面回複列表，方便我們進行查看，如果需要查看某個主題下面的回複，我們可以繼續使用版塊表中儲存的“table_id”來進行查詢。比如我們回複表的首碼是“reply_”，那麼就可以組合出“PHP”吧的ID為1的主題的回複： SELECT * FROM reply_1 WHERE topic_id = 1 ORDER BY reply_id DESC LIMIT 10 這裡，我們能夠清晰的看到，其實我們這裡使用了基礎資料表，基礎資料表就是我們的版塊表。那麼相應的，肯定會說：基礎資料表的資料量大了以後如何保證它的速度和效率？ 當然，我們就必須使得這個基礎資料表保持最好的速度和效能，比如，可以採用MySQL的記憶體表來儲存，或者儲存在記憶體當中，比如Memcache之類的記憶體緩衝等等，可以按照實際情況來進行調整。 一般基於基礎資料表的分表機制在SNS、交友、論壇等Web2.0網站中是個比較不錯的解決方案，在這些網站中，完全可以單獨使用一個表來來儲存基本標識和目標表之間的關係。使用表儲存對應關係的好處是以後擴充非常方便，只需要增加一個表記錄。  【優勢】增加刪除節點非常方便，為後期升級維護帶來很大便利【劣勢】需要增加表或者對某一個表進行操作，還是無法離開資料庫，會產生瓶頸   三、基於Hash演算法的分表處理 我們知道Hash表就是通過某個特殊的Hash演算法計算出的一個值，這個值必須是惟一的，並且能夠使用這個計算出來的值尋找到需要的值，這個叫做雜湊表。 我們在分表裡的hash演算法跟這個思想類似：通過一個原始目標的ID或者名稱通過一定的hash演算法計算出資料存放區表的表名，然後訪問相應的表。 繼續拿上面的貼吧來說，每個貼吧有版塊名稱和版塊ID，那麼這兩項值是固定的，並且是惟一的，那麼我們就可以考慮通過對這兩項值中的一項進行一些運算得出一個目標表的名稱。 現在假如我們針對我們這個貼吧系統，假設系統最大允許1億條資料，考慮每個表儲存100萬條記錄，那麼整個系統就不超過100個表就能夠容納。按照這個標準，我們假設在貼吧的版塊ID上進行hash，獲得一個key值，這個值就是我們的表名，然後訪問相應的表。 我們構造一個簡單的hash演算法： function get_hash($id){     $str = bin2hex($id);     $hash = substr($str, 0, 4);     if (strlen($hash)<4){         $hash = str_pad($hash, 4, "0");     }     return $hash;} 演算法大致就是傳入一個版塊ID值，然後函數返回一個4位的字串，如果字串長度不夠，使用0進行補全。 比如：get_hash(1)，輸出的結果是“3100”，輸入：get_hash(23819)，得到的結果是：3233，那麼我們經過簡單的跟表首碼組合，就能夠訪問這個表了。那麼我們需要訪問ID為1的內容時候哦，組合的表將是：topic_3100、reply_3100，那麼就可以直接對目標表進行訪問了。 當然，使用hash演算法後，有部分資料是可能在同一個表的，這一點跟hash表不同，hash表是盡量解決衝突，我們這裡不需要，當然同樣需要預測和分析表資料可能儲存的表名。  如果需要儲存的資料更多，同樣的，可以對版塊的名字進行hash操作，比如也是上面的二進位轉換成十六進位，因為漢字比數字和字母要多很多，那麼重複幾率更小，但是可能組合成的表就更多了，相應就必須考慮一些其它的問題。 歸根結底，使用hash方式的話必須選擇一個好的hash演算法，才能產生更多的表，然資料查詢的更迅速。  【優點hash演算法直接得出目標表名稱，效率很高】通過【劣勢】擴充性比較差，選擇了一個hash演算法，定義了多少資料量，以後只能在這個資料量上跑，不能超過過這個資料量，可擴充性稍差   四、其它問題 1. 搜尋問題現在我們已經進行分表了，那麼就無法直接對錶進行搜尋，因為你無法對可能系統中已經存在的幾十或者幾百個表進行檢索，所以搜尋必須藉助第三方的組件來進行，比如Lucene作為站內搜尋引擎是個不錯的選擇。 2. 表檔案問題我們知道MySQL的MyISAM引擎每個表都會產生三個檔案，*.frm、*.MYD、*.MYI 三個檔案，分表用來儲存表結構、表資料和表索引。Linux下面每個目錄下的檔案數量最好不要超過1000個，不然檢索資料將更慢，那麼每個表都會產生三個檔案，相應的如果分表超過300個表，那麼將檢索非常慢，所以這時候就必須再進行分，比如在進行資料庫的分離。 使用基礎資料表，我們可以新增加一個欄位，用來儲存這個表儲存在什麼資料。使用Hash的方式，我們必須截取hash值中第幾位來作為資料庫的名字。這樣，完好的解決這個問題。  五、總結 在大負載應用當中，資料庫一直是個很重要的瓶頸，必須要突破，本文講解了兩種分表的方式，希望對很多人能夠有啟發的作用。當然，本文代碼和設想沒有經過任何代碼測試，所以無法保證設計的完全準確實用，具體還是需要讀者在使用過程當中認真分析實施。  文章寫的比較匆忙，品質可能無法保證，遇到錯誤，不要見怪，歡迎提出批評指教，謝謝~~~~！ 文章來源：http://blog.csdn.net/likika2012/article/details/38816037

利用MySQL資料庫如何解決大資料量儲存問題？