提問：如何設(shè)計或優(yōu)化千萬級別的大表幔崖？此外無其他信息，個人覺得這個話題有點(diǎn)范惧磺，就只好簡單說下該如何做颖对，對于一個存儲設(shè)計，必須考慮業(yè)務(wù)特點(diǎn)豺妓，收集的信息如下：

1.數(shù)據(jù)的容量：1-3年內(nèi)會大概多少條數(shù)據(jù)惜互，每條數(shù)據(jù)大概多少字節(jié)；

2.數(shù)據(jù)項：是否有大字段琳拭，那些字段的值是否經(jīng)常被更新训堆；

3.數(shù)據(jù)查詢SQL條件：哪些數(shù)據(jù)項的列名稱經(jīng)常出現(xiàn)在WHERE、GROUP BY白嘁、ORDER BY子句中等坑鱼；

4.數(shù)據(jù)更新類SQL條件：有多少列經(jīng)常出現(xiàn)UPDATE或DELETE 的WHERE子句中；

5.SQL量的統(tǒng)計比絮缅，如：SELECT：UPDATE+DELETE：INSERT=多少鲁沥？

6.預(yù)計大表及相關(guān)聯(lián)的SQL，每天總的執(zhí)行量在何數(shù)量級耕魄？

7.表中的數(shù)據(jù)：更新為主的業(yè)務(wù) 還是 查詢?yōu)橹鞯臉I(yè)務(wù)

8.打算采用什么數(shù)據(jù)庫物理服務(wù)器画恰，以及數(shù)據(jù)庫服務(wù)器架構(gòu)？

9.并發(fā)如何吸奴？

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

大致明白以上10個問題缠局，至于如何設(shè)計此類的大表，應(yīng)該什么都清楚了考润！

至于優(yōu)化若是指創(chuàng)建好的表狭园，不能變動表結(jié)構(gòu)的話，那建議InnoDB引擎糊治，多利用點(diǎn)內(nèi)存唱矛，減輕磁盤IO負(fù)載，因為IO往往是數(shù)據(jù)庫服務(wù)器的瓶頸

另外對優(yōu)化索引結(jié)構(gòu)去解決性能問題的話井辜，建議優(yōu)先考慮修改類SQL語句绎谦，使他們更快些，不得已只靠索引組織結(jié)構(gòu)的方式抑胎，當(dāng)然此話前提是燥滑， 索引已經(jīng)創(chuàng)建的非常好，若是讀為主阿逃，可以考慮打開query_cache铭拧， 以及調(diào)整一些參數(shù)值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

不紙上談兵，說一下我的思路以及我的解決恃锉，拋磚引玉了

我最近正在解決這個問題

我現(xiàn)在的公司有三張表搀菩，是5億的數(shù)據(jù)，每天張表每天的增量是100w

每張表大概在10個columns左右

下面是我做的測試和對比

1.首先看engin,在大數(shù)據(jù)量情況下破托，在沒有做分區(qū)的情況下

mysiam比innodb在只讀的情況下肪跋，效率要高13％左右

2.在做了partition之后，你可以去讀一下mysql的官方文檔土砂，其實(shí)對于partition州既，專門是對myisam做的優(yōu)化，對于innodb萝映，所有的數(shù)據(jù)是存在ibdata里面的吴叶，所以即使你可以看到schema變了，其實(shí)沒有本質(zhì)的變化

在分區(qū)出于同一個physical disk下面的情況下序臂，提升大概只有1％

在分區(qū)在不同的physical disk下蚌卤，我分到了三個不同的disks下，提升大概在3％奥秆，其實(shí)所謂的吞吐量逊彭，由很多因素決定的，比如你的explain parition時候可以看到构订，record在那一個分區(qū)侮叮，如果每個分區(qū)都有，其實(shí)本質(zhì)上沒有解決讀的問題悼瘾，這樣只會提升寫的效率签赃。

另外一個問題在于谷异，分區(qū)，你怎么分锦聊，如果一張表，有三個column都是經(jīng)常被用于做查詢條件的箩绍，其實(shí)是一件很悲慘的事情孔庭，因為你沒有辦法對所有的sql做針對性的分區(qū)，如果你只是如mysql官方文檔上說的材蛛，只對時間做一個分區(qū)圆到，而且你也只用時間查詢的話，恭喜你

3.表主要用來讀還是寫卑吭，其實(shí)這個問題是不充分的芽淡，應(yīng)該這樣問，你在寫入的時候豆赏，同時并發(fā)的查詢多么挣菲？我的問題還比較簡單，因為mongodb的shredding支持不能掷邦，在crush之后白胀，還是回到mysql，所以在通常情況下抚岗，9am－9pm或杠，寫入的情況很多，這個時候我會做一個view宣蔚，view是基于最近被插入或者經(jīng)常被查詢的向抢，通過做view來分離讀取，就是說寫是在table上的胚委，讀在進(jìn)行邏輯判斷前是在view上操作的

4做一些archive table挟鸠，比如先對這些大表做很多已有的統(tǒng)計分析，然后通過已有的分析＋增量來解決

5如果你用mysiam篷扩，還有一個問題你要注意兄猩，如果你的.configure的時候，加了一個max index length參數(shù)的時候鉴未，當(dāng)你的record數(shù)大于制定長度的時候枢冤，這個index會被disable

6 照你的需求來看，可以有兩種方式铜秆，一種是分表淹真，另一種是分區(qū)

首先是分表，就像你自己所說的连茧，可以按月分表核蘸，可以按用戶ID分表等等巍糯，至于采用哪種方式分表，要看你的業(yè)務(wù)邏輯了客扎，分表不好的地方就是查詢有時候需要跨多個表祟峦。

然后是分區(qū)，分區(qū)可以將表分離在若干不同的表空間上徙鱼，用分而治之的方法來支撐無限膨脹的大表宅楞，給大表在物理一級的可管理性。將大表分割成較小的分區(qū)可以改善表的維護(hù)袱吆、備份厌衙、恢復(fù)、事務(wù)及查詢性能绞绒。分區(qū)的好處是分區(qū)的優(yōu)點(diǎn)：

1　增強(qiáng)可用性：如果表的一個分區(qū)由于系統(tǒng)故障而不能使用婶希，表的其余好的分區(qū)仍然可以使用；

2　減少關(guān)閉時間：如果系統(tǒng)故障只影響表的一部分分區(qū)蓬衡，那么只有這部分分區(qū)需要修復(fù)喻杈，故能比整個大表修復(fù)花的時間更少；

3　維護(hù)輕松：如果需要重建表撤蟆，獨(dú)立管理每個分區(qū)比管理單個大表要輕松得多奕塑；

4　均衡I/O:可以把表的不同分區(qū)分配到不同的磁盤來平衡I/O改善性能；

5　改善性能：對大表的查詢家肯、增加龄砰、修改等操作可以分解到表的不同分區(qū)來并行執(zhí)行，可使運(yùn)行速度更快讨衣；

6　分區(qū)對用戶透明换棚，最終用戶感覺不到分區(qū)的存在。

導(dǎo)致節(jié)點(diǎn)插入時間非常慢的原因：

? ? ? 1反镇、連接數(shù)據(jù)庫的問題：建立連接和關(guān)閉連接的次數(shù)太多固蚤，導(dǎo)致IO訪問次數(shù)太頻繁。

? ? ? 2歹茶、應(yīng)該使用批量插入和批量修改的方法夕玩，而不是有一條數(shù)據(jù)就進(jìn)行插入，這樣會導(dǎo)致訪問數(shù)據(jù)庫的實(shí)際特別的慢惊豺。

? ? ? 3燎孟、在建立庫的時候要建立適當(dāng)?shù)乃饕喝缰麈I、外鍵尸昧、唯一等揩页，優(yōu)化查詢效率。

首先烹俗，數(shù)據(jù)量大的時候爆侣，應(yīng)盡量避免全表掃描萍程，應(yīng)考慮在 where 及 order by 涉及的列上建立索引，建索引可以大大加快數(shù)據(jù)的檢索速度兔仰。 但是茫负，有些情況索引是不會起效的：

1、應(yīng)盡量避免在 where 子句中使用!=或<>操作符乎赴，否則將引擎放棄使用索引而進(jìn)行全表掃描朽褪。

2、應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷无虚，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

? ? select id from t where num is null

? ? 可以在num上設(shè)置默認(rèn)值0衍锚，確保表中num列沒有null值友题，然后這樣查詢：

? ? select id from t where num=0

3、盡量避免在 where 子句中使用 or 來連接條件戴质，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描度宦，如：

? ? 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、下面的查詢也將導(dǎo)致全表掃描：

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

? ? 若要提高效率告匠，可以考慮全文檢索戈抄。

5、in 和 not in 也要慎用后专，否則會導(dǎo)致全表掃描划鸽，如：

? ? 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戚哎、如果在 where 子句中使用參數(shù)裸诽，也會導(dǎo)致全表掃描。因為SQL只有在運(yùn)行時才會解析局部變量型凳，但優(yōu)化程序不能將訪問計劃的選擇推遲到運(yùn)行時丈冬；它必須在編譯時進(jìn)行選擇。然而甘畅，如果在編譯時建立訪問計劃埂蕊，變量的值還是未知的，因而無法作為索引選擇的輸入項疏唾。如下面語句將進(jìn)行全表掃描：

? ? select id from t where num=@num

? ? 可以改為強(qiáng)制查詢使用索引：

? ? select id from t with(index(索引名)) where num=@num

7蓄氧、應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描荸实。如：

? ? select id from t where num/2=100

? ? 應(yīng)改為:

? ? select id from t where num=100*2

8匀们、應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描准给。如：

? ? 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

? ? 應(yīng)改為:

? ? select id from t where name like ‘a(chǎn)bc%’

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

9泄朴、不要在 where 子句中的“=”左邊進(jìn)行函數(shù)重抖、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引祖灰。

10钟沛、在使用索引字段作為條件時，如果該索引是復(fù)合索引局扶，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引恨统，否則該索引將不會被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致三妈。

11畜埋、不要寫一些沒有意義的查詢，如需要生成一個空表結(jié)構(gòu)：

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

? ? 這類代碼不會返回任何結(jié)果集畴蒲，但是會消耗系統(tǒng)資源的悠鞍，應(yīng)改成這樣：

? ? 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是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時蔫骂，SQL查詢可能不會去利用索引么翰，如一表中有字段 sex，male辽旋、female幾乎各一半浩嫌，那么即使在sex上建了索引也對查詢效率起不了作用。

2戴已、索引并不是越多越好固该，索引固然可以提高相應(yīng)的 select 的效率，但同時也降低了 insert 及 update 的效率糖儡，因為 insert 或 update 時有可能會重建索引伐坏，所以怎樣建索引需要慎重考慮，視具體情況而定握联。一個表的索引數(shù)最好不要超過6個桦沉，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

3金闽、應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列纯露，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整代芜，會耗費(fèi)相當(dāng)大的資源埠褪。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

其他需要注意的地方：

1钞速、盡量使用數(shù)字型字段贷掖，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能渴语，并會增加存儲開銷苹威。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了驾凶。

2牙甫、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”调违，不要返回用不到的任何字段窟哺。

3、盡量使用表變量來代替臨時表技肩。如果表變量包含大量數(shù)據(jù)脏答，請注意索引非常有限（只有主鍵索引）。

4亩鬼、避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗阿蝶。

5雳锋、臨時表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行劢啵珑韫?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是筑煮，對于一次性事件辛蚊，最好使用導(dǎo)出表。

6真仲、在新建臨時表時袋马，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table秸应，避免造成大量 log 虑凛，以提高速度；如果數(shù)據(jù)量不大软啼，為了緩和系統(tǒng)表的資源桑谍，應(yīng)先create table，然后insert祸挪。

7锣披、如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table 雹仿，然后 drop table 增热，這樣可以避免系統(tǒng)表的較長時間鎖定。

8盅粪、盡量避免使用游標(biāo)钓葫，因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行票顾，那么就應(yīng)該考慮改寫础浮。

9、使用基于游標(biāo)的方法或臨時表方法之前奠骄，應(yīng)先尋找基于集的解決方案來解決問題豆同，基于集的方法通常更有效。

10含鳞、與臨時表一樣影锈，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法蝉绷，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時鸭廷。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時間允許熔吗，基于游標(biāo)的方法和基于集的方法都可以嘗試一下辆床，看哪一種方法的效果更好。

11桅狠、在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON 讼载，在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息中跌。

12咨堤、盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大漩符，應(yīng)該考慮相應(yīng)需求是否合理一喘。

13、盡量避免大事務(wù)操作嗜暴，提高系統(tǒng)并發(fā)能力

如今隨著互聯(lián)網(wǎng)的發(fā)展津滞，數(shù)據(jù)的量級也是撐指數(shù)的增長，從GB到TB到PB灼伤。對數(shù)據(jù)的各種操作也是愈加的困難触徐，傳統(tǒng)的關(guān)系性數(shù)據(jù)庫已經(jīng)無法滿足快速查詢與插入數(shù)據(jù)的需求。這個時候NoSQL的出現(xiàn)暫時解決了這一危機(jī)狐赡。它通過降低數(shù)據(jù)的安全性撞鹉，減少對事務(wù)的支持，減少對復(fù)雜查詢的支持，來獲取性能上的提升鸟雏。但是享郊，在有些場合NoSQL一些折衷是無法滿足使用場景的，就比如有些使用場景是絕對要有事務(wù)與安全指標(biāo)的孝鹊。這個時候NoSQL肯定是無法滿足的炊琉，所以還是需要使用關(guān)系性數(shù)據(jù)庫。

? 雖然關(guān)系型數(shù)據(jù)庫在海量數(shù)據(jù)中遜色于NoSQL數(shù)據(jù)庫又活，但是如果你操作正確苔咪，它的性能還是會滿足你的需求的。針對數(shù)據(jù)的不同操作柳骄，其優(yōu)化方向也是不盡相同团赏。對于數(shù)據(jù)移植，查詢和插入等操作耐薯，可以從不同的方向去考慮舔清。而在優(yōu)化的時候還需要考慮其他相關(guān)操作是否會產(chǎn)生影響。就比如你可以通過創(chuàng)建索引提高查詢性能曲初，但是這會導(dǎo)致插入數(shù)據(jù)的時候因為要建立更新索引導(dǎo)致插入性能降低体谒，你是否可以接受這一降低那。所以臼婆，對數(shù)據(jù)庫的優(yōu)化是要考慮多個方向营密，尋找一個折衷的最佳方案。

一：查詢優(yōu)化

? 1：創(chuàng)建索引目锭。

? 最簡單也是最常用的優(yōu)化就是查詢。因為對于CRUD操作纷捞，read操作是占據(jù)了絕大部分的比例痢虹，所以read的性能基本上決定了應(yīng)用的性能。對于查詢性能最常用的就是創(chuàng)建索引主儡。經(jīng)過測試奖唯，2000萬條記錄，每條記錄200字節(jié)兩列varchar類型的糜值。當(dāng)不使用索引的時候查詢一條記錄需要一分鐘丰捷，而當(dāng)創(chuàng)建了索引的時候查詢時間可以忽略。但是寂汇，當(dāng)你在已有數(shù)據(jù)上添加索引的時候病往，則需要耗費(fèi)非常大的時間。我插入2000萬條記錄之后骄瓣，再創(chuàng)建索引大約話費(fèi)了幾十分鐘的樣子停巷。

? 創(chuàng)建索引的弊端和場合。雖然創(chuàng)建索引可以很大程度上優(yōu)化查詢的速度，但是弊端也是很明顯的畔勤。一個是在插入數(shù)據(jù)的時候蕾各，創(chuàng)建索引也需要消耗部分的時間，這就使得插入性能在一定程度上降低庆揪；另一個很明顯的是數(shù)據(jù)文件變的更大式曲。在列上創(chuàng)建索引的時候，每條索引的長度是和你創(chuàng)建列的時候制定的長度相同的缸榛。比如你創(chuàng)建varchar(100)吝羞，當(dāng)你在該列上創(chuàng)建索引，那么索引的長度則是102字節(jié)仔掸，因為長度超過64字節(jié)則會額外增加2字節(jié)記錄索引的長度脆贵。

? 從上圖可以看到我在YCSB_KEY這一列(長度100)上創(chuàng)建了一個名字為index_ycsb_key的索引，每條索引長度都為102起暮，想象一下當(dāng)數(shù)據(jù)變的巨大無比的時候卖氨，索引的大小也是不可以小覷的。而且從這也可以看出负懦，索引的長度和列類型的長度還不同筒捺，比如varchar它是變長的字符類型(請看MySQL數(shù)據(jù)類型分析)，實(shí)際存儲長度是是實(shí)際字符的大小纸厉，但是索引卻是你聲明的長度的大小系吭。你創(chuàng)建列的時候聲明100字節(jié)，那么索引長度就是這個字節(jié)再加上2颗品，它不管你實(shí)際存儲是多大肯尺。

? 除了創(chuàng)建索引需要消耗時間，索引文件體積會變的越來越大之外躯枢，創(chuàng)建索引也需要看的你存儲數(shù)據(jù)的特征则吟。當(dāng)你存儲數(shù)據(jù)很大一部分都是重復(fù)記錄，那這個時候創(chuàng)建索引是百害而無一利锄蹂。請先查看MySQL索引介紹氓仲。所以，當(dāng)很多數(shù)據(jù)重復(fù)的時候得糜，索引帶來的查詢提升的效果是可以直接忽略的敬扛，但是這個時候你還要承受插入數(shù)據(jù)的時候創(chuàng)建索引帶來的性能消耗。

2：緩存的配置朝抖。

? 在MySQL中有多種多樣的緩存啥箭，有的緩存負(fù)責(zé)緩存查詢語句，也有的負(fù)責(zé)緩存查詢數(shù)據(jù)治宣。這些緩存內(nèi)容客戶端無法操作捉蚤，是由server端來維護(hù)的抬驴。它會隨著你查詢與修改等相應(yīng)不同操作進(jìn)行不斷更新。通過其配置文件我們可以看到在MySQL中的緩存：

? 在這里主要分析query cache缆巧，它是主要用來緩存查詢數(shù)據(jù)布持。當(dāng)你想使用該cache，必須把query_cache_size大小設(shè)置為非0陕悬。當(dāng)設(shè)置大小為非0的時候题暖，server會就會緩存每次查詢返回的結(jié)果，到下次相同查詢server就直接從緩存獲取數(shù)據(jù)捉超，而不是再執(zhí)行查詢胧卤。能緩存的數(shù)據(jù)量就和你的size大小設(shè)置有關(guān)，所以當(dāng)你設(shè)置的足夠大拼岳，數(shù)據(jù)可以完全緩存到內(nèi)存枝誊，速度就會非常之快。

? 但是惜纸，query cache也有它的弊端叶撒。當(dāng)你對數(shù)據(jù)表做任何的更新操作(update/insert/delete)等操作，server為了保證緩存與數(shù)據(jù)庫的一致性耐版，會強(qiáng)制刷新緩存數(shù)據(jù)祠够，導(dǎo)致緩存數(shù)據(jù)全部失效。所以粪牲，當(dāng)一個表格的更新數(shù)據(jù)表操作非常多的話古瓤，query cache是不會起到查詢提升的性能，還會影響其他操作的性能腺阳。

? 3：slow_query_log分析落君。

? 其實(shí)對于查詢性能提升，最重要也是最根本的手段也是slow_query的設(shè)置亭引。

? 當(dāng)你設(shè)置slow_query_log為on的時候绎速，server端會對每次的查詢進(jìn)行記錄，當(dāng)超過你設(shè)置的慢查詢時間(long_query_time)的時候就把該條查詢記錄到日志痛侍。而你對性能進(jìn)行優(yōu)化的時候，就可以分析慢查詢?nèi)罩灸校瑢β樵兊牟樵冋Z句進(jìn)行有目的的優(yōu)化主届。可以通過創(chuàng)建各種索引待德，可以通過分表等操作君丁。那為什么要分庫分表那，當(dāng)不分庫分表的時候那個地方是限制性能的地方啊将宪。下面我們就簡單介紹绘闷。

4：分庫分表

? 分庫分表應(yīng)該算是查詢優(yōu)化的殺手锏了橡庞。上述各種措施在數(shù)據(jù)量達(dá)到一定等級之后，能起到優(yōu)化的作用已經(jīng)不明顯了印蔗。這個時候就必須對數(shù)據(jù)量進(jìn)行分流扒最。分流一般有分庫與分表兩種措施。而分表又有垂直切分與水平切分兩種方式华嘹。下面我們就針對每一種方式簡單介紹吧趣。

? 對于mysql，其數(shù)據(jù)文件是以文件形式存儲在磁盤上的耙厚。當(dāng)一個數(shù)據(jù)文件過大的時候强挫，操作系統(tǒng)對大文件的操作就會比較麻煩與耗時，而且有的操作系統(tǒng)就不支持大文件薛躬，所以這個時候就必須分表了俯渤。另外對于mysql常用的存儲引擎是Innodb，它的底層數(shù)據(jù)結(jié)構(gòu)是B+樹型宝。當(dāng)其數(shù)據(jù)文件過大的時候八匠，B+樹就會從層次和節(jié)點(diǎn)上比較多，當(dāng)查詢一個節(jié)點(diǎn)的時候可能會查詢很多層次诡曙，而這必定會導(dǎo)致多次IO操作進(jìn)行裝載進(jìn)內(nèi)存臀叙，肯定會耗時的。除此之外還有Innodb對于B+樹的鎖機(jī)制价卤。對每個節(jié)點(diǎn)進(jìn)行加鎖劝萤，那么當(dāng)更改表結(jié)構(gòu)的時候，這時候就會樹進(jìn)行加鎖慎璧，當(dāng)表文件大的時候床嫌，這可以認(rèn)為是不可實(shí)現(xiàn)的。

? 所以綜上我們就必須進(jìn)行分表與分庫的操作胸私。

二：數(shù)據(jù)轉(zhuǎn)移

? 當(dāng)數(shù)據(jù)量達(dá)到一定等級之后厌处，那么移庫將是一個非常慎重又危險的工作。在移庫中保證前后數(shù)據(jù)的一致性岁疼，各種突發(fā)情況的處理阔涉，移庫過程中數(shù)據(jù)的變遷，每一個都是一個非常困難的問題捷绒。

? 2.1：插入數(shù)據(jù)

? 當(dāng)進(jìn)行數(shù)據(jù)遷移的時候瑰排，肯定會存在大數(shù)據(jù)的重新導(dǎo)入，你可以選擇直接load文件暖侨，有的時候可能就需要代碼插入了椭住。這個時候就需要對插入語句進(jìn)行一定的優(yōu)化了。這個時候可以使用INSERT DELAYED語句字逗，該語句是當(dāng)你發(fā)出插入請求的時候京郑，部馬上就插入到數(shù)據(jù)庫而是放在緩存里面涌矢，等待時機(jī)成熟之后再進(jìn)行插入氛改。

待補(bǔ)充拓哺。寺晌。。

mysql大數(shù)據(jù)量處理

一金拒、概述

分表是個目前算是比較炒的比較流行的概念兽肤，特別是在大負(fù)載的情況下，分表是一個良好分散數(shù)據(jù)庫壓力的好方法绪抛。

首先要了解為什么要分表资铡，分表的好處是什么。我們先來大概了解以下一個數(shù)據(jù)庫執(zhí)行SQL的過程：

接收到SQL --> 放入SQL執(zhí)行隊列 --> 使用分析器分解SQL --> 按照分析結(jié)果進(jìn)行數(shù)據(jù)的提取或者修改 --> 返回處理結(jié)果

當(dāng)然幢码，這個流程圖不一定正確笤休，這只是我自己主觀意識上這么我認(rèn)為。那么這個處理過程當(dāng)中症副，最容易出現(xiàn)問題的是什么店雅？就是說，如果前一個SQL沒有執(zhí)行完畢的話贞铣，后面的SQL是不會執(zhí)行的闹啦，因為為了保證數(shù)據(jù)的完整性，必須對數(shù)據(jù)表文件進(jìn)行鎖定辕坝，包括共享鎖和獨(dú)享鎖兩種鎖定窍奋。共享鎖是在鎖定的期間，其它線程也可以訪問這個數(shù)據(jù)文件酱畅，但是不允許修改操作，相應(yīng)的纺酸，獨(dú)享鎖就是整個文件就是歸一個線程所有窖逗，其它線程無法訪問這個數(shù)據(jù)文件痪寻。一般MySQL中最快的存儲引擎MyISAM，它是基于表鎖定的虽惭，就是說如果一鎖定的話橡类，那么整個數(shù)據(jù)文件外部都無法訪問，必須等前一個操作完成后芽唇，才能接收下一個操作顾画，那么在這個前一個操作沒有執(zhí)行完成，后一個操作等待在隊列里無法執(zhí)行的情況叫做阻塞匆笤，一般我們通俗意義上叫做“鎖表”研侣。

鎖表直接導(dǎo)致的后果是什么？就是大量的SQL無法立即執(zhí)行炮捧，必須等隊列前面的SQL全部執(zhí)行完畢才能繼續(xù)執(zhí)行庶诡。這個無法執(zhí)行的SQL就會導(dǎo)致沒有結(jié)果，或者延遲嚴(yán)重咆课，影響用戶體驗末誓。

特別是對于一些使用比較頻繁的表，比如SNS系統(tǒng)中的用戶信息表书蚪、論壇系統(tǒng)中的帖子表等等喇澡，都是訪問量大很大的表，為了保證數(shù)據(jù)的快速提取返回給用戶殊校，必須使用一些處理方式來解決這個問題晴玖，這個就是我今天要聊到的分表技術(shù)。

分表技術(shù)顧名思義箩艺，就是把若干個存儲相同類型數(shù)據(jù)的表分成幾個表分表存儲窜醉，在提取數(shù)據(jù)的時候，不同的用戶訪問不同的表艺谆，互不沖突榨惰，減少鎖表的幾率。比如静汤，目前保存用戶分表有兩個表琅催，一個是user_1表，還有一個是 user_2 表虫给，兩個表保存了不同的用戶信息藤抡，user_1 保存了前10萬的用戶信息，user_2保存了后10萬名用戶的信息抹估，現(xiàn)在如果同時查詢用戶 heiyeluren1 和 heiyeluren2 這個兩個用戶缠黍，那么就是分表從不同的表提取出來，減少鎖表的可能药蜻。

我下面要講述的兩種分表方法我自己都沒有實(shí)驗過瓷式，不保證準(zhǔn)確能用替饿，只是提供一個設(shè)計思路。下面關(guān)于分表的例子我假設(shè)是在一個貼吧系統(tǒng)的基礎(chǔ)上來進(jìn)行處理和構(gòu)建的贸典。（如果沒有用過貼吧的用戶趕緊Google一下）

二视卢、基于基礎(chǔ)表的分表處理

這個基于基礎(chǔ)表的分表處理方式大致的思想就是：一個主要表，保存了所有的基本信息廊驼，如果某個項目需要找到它所存儲的表据过，那么必須從這個基礎(chǔ)表中查找出對應(yīng)的表名等項目，好直接訪問這個表妒挎。如果覺得這個基礎(chǔ)表速度不夠快绳锅，可以完全把整個基礎(chǔ)表保存在緩存或者內(nèi)存中，方便有效的查詢酝掩。

我們基于貼吧的情況榨呆，構(gòu)建假設(shè)如下的3張表：

1. 貼吧版塊表: 保存貼吧中版塊的信息

2. 貼吧主題表：保存貼吧中版塊中的主題信息，用于瀏覽

3. 貼吧回復(fù)表：保存主題的原始內(nèi)容和回復(fù)內(nèi)容

“貼吧版塊表”包含如下字段：

版塊ID? ? ? board_id? ? ? ? ? int(10)

版塊名稱? ? board_name? ? ? char(50)

子表ID? ? ? table_id? ? ? ? ? ? smallint(5)

產(chǎn)生時間? ? created? ? ? ? ? ? datetime

“貼吧主題表”包含如下字段：

主題ID? ? ? ? ? topic_id? ? ? ? int(10)

主題名稱? ? ? ? topic_name? ? char(255)

版塊ID? ? ? ? ? board_id? ? ? ? ? int(10)

創(chuàng)建時間? ? ? created? ? ? ? ? datetime

“貼吧回復(fù)表”的字段如下：

回復(fù)ID? ? ? ? reply_id? ? ? ? ? int(10)

回復(fù)內(nèi)容? ? ? reply_text? ? ? ? text

主題ID? ? ? ? topic_id? ? ? ? ? int(10)

版塊ID? ? ? ? board_id? ? ? ? int(10)

創(chuàng)建時間? ? ? created? ? ? ? ? ? datetime

那么上面保存了我們整個貼吧中的表結(jié)構(gòu)信息庸队，三個表對應(yīng)的關(guān)系是：

版塊 --> 多個主題

主題 --> 多個回復(fù)

那么就是說积蜻，表文件大小的關(guān)系是：

版塊表文件 < 主題表文件 < 回復(fù)表文件

所以基本可以確定需要對主題表和回復(fù)表進(jìn)行分表，已增加我們數(shù)據(jù)檢索查詢更改時候的速度和性能彻消。

看了上面的表結(jié)構(gòu)竿拆，會明顯發(fā)現(xiàn)，在“版塊表”中保存了一個"table_id"字段宾尚，這個字段就是用于保存一個版塊對應(yīng)的主題和回復(fù)都是分表保存在什么表里的丙笋。

比如我們有一個叫做“PHP”的貼吧，board_id是1煌贴，子表ID也是1御板，那么這條記錄就是：

board_id | board_name | table_id | created

1 | PHP | 1 | 2007-01-19 00:30:12

相應(yīng)的，如果我需要提取“PHP”吧里的所有主題牛郑，那么就必須按照表里保存的table_id來組合一個存儲了主題的表名稱怠肋，比如我們主題表的前綴是“topic_”，那么組合出來“PHP”吧對應(yīng)的主題表應(yīng)該是：“topic_1”淹朋，那么我們執(zhí)行：

SELECT * FROM topic_1 WHERE board_id = 1 ORDER BY topic_id DESC LIMIT 10

這樣就能夠獲取這個主題下面回復(fù)列表笙各，方便我們進(jìn)行查看，如果需要查看某個主題下面的回復(fù)础芍，我們可以繼續(xù)使用版塊表中保存的“table_id”來進(jìn)行查詢杈抢。比如我們回復(fù)表的前綴是“reply_”，那么就可以組合出“PHP”吧的ID為1的主題的回復(fù)：

SELECT * FROM reply_1 WHERE topic_id = 1 ORDER BY reply_id DESC LIMIT 10

這里仑性，我們能夠清晰的看到惶楼，其實(shí)我們這里使用了基礎(chǔ)表，基礎(chǔ)表就是我們的版塊表。那么相應(yīng)的歼捐，肯定會說：基礎(chǔ)表的數(shù)據(jù)量大了以后如何保證它的速度和效率嫩实？

當(dāng)然，我們就必須使得這個基礎(chǔ)表保持最好的速度和性能窥岩，比如，可以采用MySQL的內(nèi)存表來存儲宰缤，或者保存在內(nèi)存當(dāng)中颂翼，比如Memcache之類的內(nèi)存緩存等等，可以按照實(shí)際情況來進(jìn)行調(diào)整慨灭。

一般基于基礎(chǔ)表的分表機(jī)制在SNS朦乏、交友、論壇等Web2.0網(wǎng)站中是個比較不錯的解決方案氧骤，在這些網(wǎng)站中呻疹，完全可以單獨(dú)使用一個表來來保存基本標(biāo)識和目標(biāo)表之間的關(guān)系。使用表保存對應(yīng)關(guān)系的好處是以后擴(kuò)展非常方便筹陵，只需要增加一個表記錄刽锤。

【優(yōu)勢】增加刪除節(jié)點(diǎn)非常方便，為后期升級維護(hù)帶來很大便利

【劣勢】需要增加表或者對某一個表進(jìn)行操作朦佩，還是無法離開數(shù)據(jù)庫并思，會產(chǎn)生瓶頸

三、基于Hash算法的分表處理

我們知道Hash表就是通過某個特殊的Hash算法計算出的一個值语稠，這個值必須是惟一的宋彼，并且能夠使用這個計算出來的值查找到需要的值，這個叫做哈希表仙畦。

我們在分表里的hash算法跟這個思想類似：通過一個原始目標(biāo)的ID或者名稱通過一定的hash算法計算出數(shù)據(jù)存儲表的表名输涕，然后訪問相應(yīng)的表。

繼續(xù)拿上面的貼吧來說慨畸，每個貼吧有版塊名稱和版塊ID莱坎，那么這兩項值是固定的，并且是惟一的寸士，那么我們就可以考慮通過對這兩項值中的一項進(jìn)行一些運(yùn)算得出一個目標(biāo)表的名稱型奥。

現(xiàn)在假如我們針對我們這個貼吧系統(tǒng)，假設(shè)系統(tǒng)最大允許1億條數(shù)據(jù)碉京，考慮每個表保存100萬條記錄厢汹，那么整個系統(tǒng)就不超過100個表就能夠容納。按照這個標(biāo)準(zhǔn)谐宙，我們假設(shè)在貼吧的版塊ID上進(jìn)行hash烫葬，獲得一個key值，這個值就是我們的表名，然后訪問相應(yīng)的表搭综。

我們構(gòu)造一個簡單的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值垢箕，然后函數(shù)返回一個4位的字符串，如果字符串長度不夠兑巾，使用0進(jìn)行補(bǔ)全条获。

比如：get_hash(1)，輸出的結(jié)果是“3100”蒋歌，輸入：get_hash(23819)帅掘，得到的結(jié)果是：3233，那么我們經(jīng)過簡單的跟表前綴組合堂油，就能夠訪問這個表了修档。那么我們需要訪問ID為1的內(nèi)容時候哦，組合的表將是：topic_3100府框、reply_3100吱窝，那么就可以直接對目標(biāo)表進(jìn)行訪問了。

當(dāng)然迫靖，使用hash算法后院峡，有部分?jǐn)?shù)據(jù)是可能在同一個表的，這一點(diǎn)跟hash表不同系宜，hash表是盡量解決沖突撕予，我們這里不需要，當(dāng)然同樣需要預(yù)測和分析表數(shù)據(jù)可能保存的表名蜈首。

如果需要存儲的數(shù)據(jù)更多实抡，同樣的，可以對版塊的名字進(jìn)行hash操作欢策，比如也是上面的二進(jìn)制轉(zhuǎn)換成十六進(jìn)制吆寨，因為漢字比數(shù)字和字母要多很多，那么重復(fù)幾率更小踩寇，但是可能組合成的表就更多了啄清，相應(yīng)就必須考慮一些其它的問題。

歸根結(jié)底俺孙，使用hash方式的話必須選擇一個好的hash算法辣卒，才能生成更多的表，然數(shù)據(jù)查詢的更迅速睛榄。

【優(yōu)點(diǎn)hash算法直接得出目標(biāo)表名稱荣茫，效率很高】通過

【劣勢】擴(kuò)展性比較差，選擇了一個hash算法场靴，定義了多少數(shù)據(jù)量啡莉，以后只能在這個數(shù)據(jù)量上跑港准，不能超過過這個數(shù)據(jù)量，可擴(kuò)展性稍差

四咧欣、其它問題

1. 搜索問題

現(xiàn)在我們已經(jīng)進(jìn)行分表了浅缸，那么就無法直接對表進(jìn)行搜索，因為你無法對可能系統(tǒng)中已經(jīng)存在的幾十或者幾百個表進(jìn)行檢索魄咕，所以搜索必須借助第三方的組件來進(jìn)行衩椒，比如Lucene作為站內(nèi)搜索引擎是個不錯的選擇。

2. 表文件問題

我們知道MySQL的MyISAM引擎每個表都會生成三個文件哮兰，*.frm毛萌、*.MYD、*.MYI 三個文件奠蹬，分表用來保存表結(jié)構(gòu)、表數(shù)據(jù)和表索引嗡午。Linux下面每個目錄下的文件數(shù)量最好不要超過1000個囤躁，不然檢索數(shù)據(jù)將更慢，那么每個表都會生成三個文件荔睹，相應(yīng)的如果分表超過300個表狸演，那么將檢索非常慢，所以這時候就必須再進(jìn)行分僻他，比如在進(jìn)行數(shù)據(jù)庫的分離宵距。

使用基礎(chǔ)表，我們可以新增加一個字段吨拗，用來保存這個表保存在什么數(shù)據(jù)满哪。使用Hash的方式，我們必須截取hash值中第幾位來作為數(shù)據(jù)庫的名字劝篷。這樣哨鸭，完好的解決這個問題。

五娇妓、總結(jié)

在大負(fù)載應(yīng)用當(dāng)中像鸡，數(shù)據(jù)庫一直是個很重要的瓶頸，必須要突破哈恰，本文講解了兩種分表的方式只估，希望對很多人能夠有啟發(fā)的作用。當(dāng)然着绷，本文代碼和設(shè)想沒有經(jīng)過任何代碼測試蛔钙，所以無法保證設(shè)計的完全準(zhǔn)確實(shí)用，具體還是需要讀者在使用過程當(dāng)中認(rèn)真分析實(shí)施荠医。