MySQL 對於千萬級的大表要怎麼優化？

01-03

很多人第一反應是各種切分；我給的順序是:

第一優化你的sql和索引；

第二加緩存，memcached,redis；

第三以上都做了後，還是慢，就做主從複製或主主複製，讀寫分離，可以在應用層做，效率高，也可以用三方工具，第三方工具推薦360的atlas,其它的要麼效率不高，要麼沒人維護；

第四如果以上都做了還是慢，不要想著去做切分，mysql自帶分區表，先試試這個，對你的應用是透明的，無需更改代碼,但是sql語句是需要針對分區表做優化的，sql條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區，另外分區表還有一些坑，在這裡就不多說了；

第五如果以上都做了，那就先做垂直拆分，其實就是根據你模塊的耦合度，將一個大的系統分為多個小的系統，也就是分散式系統；

第六才是水平切分，針對數據量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的sharding key,為了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，sql中盡量帶sharding key，將數據定位到限定的表上去查，而不是掃描全部的表；

mysql資料庫一般都是按照這個步驟去演化的，成本也是由低到高；

有人也許要說第一步優化sql和索引這還用說嗎？的確，大家都知道，但是很多情況下，這一步做的並不到位，甚至有的只做了根據sql去建索引，根本沒對sql優化（中槍了沒？），除了最簡單的增刪改查外，想實現一個查詢，可以寫出很多種查詢語句，不同的語句，根據你選擇的引擎、表中數據的分布情況、索引情況、資料庫優化策略、查詢中的鎖策略等因素，最終查詢的效率相差很大；優化要從整體去考慮，有時你優化一條語句後，其它查詢反而效率被降低了，所以要取一個平衡點；即使精通mysql的話，除了純技術面優化，還要根據業務面去優化sql語句，這樣才能達到最優效果；你敢說你的sql和索引已經是最優了嗎?

再說一下不同引擎的優化，myisam讀的效果好，寫的效率差，這和它數據存儲格式，索引的指針和鎖的策略有關的，它的數據是順序存儲的（innodb數據存儲方式是聚簇索引），他的索引btree上的節點是一個指向數據物理位置的指針，所以查找起來很快，（innodb索引節點存的則是數據的主鍵，所以需要根據主鍵二次查找）；myisam鎖是表鎖，只有讀讀之間是並發的，寫寫之間和讀寫之間（讀和插入之間是可以並發的，去設置concurrent_insert參數，定期執行表優化操作，更新操作就沒有辦法了）是串列的，所以寫起來慢，並且默認的寫優先順序比讀優先順序高，高到寫操作來了後，可以馬上插入到讀操作前面去，如果批量寫，會導致讀請求餓死，所以要設置讀寫優先順序或設置多少寫操作後執行讀操作的策略;myisam不要使用查詢時間太長的sql，如果策略使用不當，也會導致寫餓死，所以盡量去拆分查詢效率低的sql,

innodb一般都是行鎖，這個一般指的是sql用到索引的時候，行鎖是加在索引上的，不是加在數據記錄上的，如果sql沒有用到索引，仍然會鎖定表,mysql的讀寫之間是可以並發的，普通的select是不需要鎖的，當查詢的記錄遇到鎖時，用的是一致性的非鎖定快照讀，也就是根據資料庫隔離級別策略，會去讀被鎖定行的快照，其它更新或加鎖讀語句用的是當前讀，讀取原始行；因為普通讀與寫不衝突，所以innodb不會出現讀寫餓死的情況，又因為在使用索引的時候用的是行鎖，鎖的粒度小，競爭相同鎖的情況就少，就增加了並發處理，所以並發讀寫的效率還是很優秀的，問題在於索引查詢後的根據主鍵的二次查找導致效率低；

ps:很奇怪，為什innodb的索引葉子節點存的是主鍵而不是像mysism一樣存數據的物理地址指針嗎？如果存的是物理地址指針不就不需要二次查找了嗎，這也是我開始的疑惑，根據mysism和innodb數據存儲方式的差異去想，你就會明白了，我就不費口舌了！

所以innodb為了避免二次查找可以使用索引覆蓋技術，無法使用索引覆蓋的，再延伸一下就是基於索引覆蓋實現延遲關聯；不知道什麼是索引覆蓋的，建議你無論如何都要弄清楚它是怎麼回事！

盡你所能去優化你的sql吧！說它成本低，卻又是一項費時費力的活，需要在技術與業務都熟悉的情況下，用心去優化才能做到最優，優化後的效果也是立竿見影的！

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

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資料庫伺服器端核心參數詳解和推薦配置

http://www.mysqlops.com/2011/10/26/mysql-variables-one.html

說3點

1. 設計合適的索引,基於主鍵的查找,上億數據也是很快的;

2. 反範式化設計,以空間換時間,避免join,有些join操作可以在用代碼實現,沒必要用資料庫來實現;

3. buffer,盡量讓內存大於數據.

分享：MySQL資料庫設計總結

作者針對MySQL資料庫設計總結了19條規則，其中對於大表優化，他在規則7中提到：

大表可以考慮水平拆分。大表影響查詢效率，根據業務特性有很多拆分方式，像根據時間遞增的數據，可以根據時間來分；以id劃分的數據，可以根據id%資料庫個數的方式來拆分。

19條規則摘要如下：

規則1：一般情況可以選擇MyISAM存儲引擎，如果需要事務支持必須使用InnoDB存儲引擎。

規則2：命名規則。

規則3：資料庫欄位類型定義

經常需要計算和排序等消耗CPU的欄位,應該盡量選擇更為迅速的欄位，如用TIMESTAMP(4個位元組，最小值1970-01-01 00:00:00)代替Datetime（8個位元組，最小值1001-01-01 00:00:00）,通過整型替代浮點型和字元型
變長欄位使用varchar，不要使用char
對於二進位多媒體數據，流水隊列數據(如日誌)，超大文本數據不要放在資料庫欄位中

規則4：業務邏輯執行過程必須讀到的表中必須要有初始的值。避免業務讀出為負或無窮大的值導致程序失敗

規則5：並不需要一定遵守範式理論，適度的冗餘，讓Query盡量減少Join

規則6：訪問頻率較低的大欄位拆分出數據表。有些大欄位佔用空間多，訪問頻率較其他欄位明顯要少很多，這種情況進行拆分，頻繁的查詢中就不需要讀取大欄位，造成IO資源的浪費。

規則7：大表可以考慮水平拆分。大表影響查詢效率，根據業務特性有很多拆分方式，像根據時間遞增的數據，可以根據時間來分。以id劃分的數據，可根據id%資料庫個數的方式來拆分。

一.資料庫索引

規則8：業務需要的相關索引是根據實際的設計所構造sql語句的where條件來確定的，業務不需要的不要建索引，不允許在聯合索引（或主鍵）中存在多於的欄位。特別是該欄位根本不會在條件語句中出現。

規則9：唯一確定一條記錄的一個欄位或多個欄位要建立主鍵或者唯一索引，不能唯一確定一條記錄，為了提高查詢效率建普通索引

規則10：業務使用的表，有些記錄數很少，甚至只有一條記錄，為了約束的需要，也要建立索引或者設置主鍵。

規則11：對於取值不能重複，經常作為查詢條件的欄位，應該建唯一索引(主鍵默認唯一索引)，並且將查詢條件中該欄位的條件置於第一個位置。沒有必要再建立與該欄位有關的聯合索引。

規則12：對於經常查詢的欄位，其值不唯一，也應該考慮建立普通索引，查詢語句中該欄位條件置於第一個位置，對聯合索引處理的方法同樣。

規則13：業務通過不唯一索引訪問數據時，需要考慮通過該索引值返回的記錄稠密度，原則上可能的稠密度最大不能高於0.2，如果稠密度太大，則不合適建立索引了。

規則14：需要聯合索引(或聯合主鍵)的資料庫要注意索引的順序。SQL語句中的匹配條件也要跟索引的順序保持一致。

注意：索引的順勢不正確也可能導致嚴重的後果。

規則15：表中的多個欄位查詢作為查詢條件，不含有其他索引，並且欄位聯合值不重複，可以在這多個欄位上建唯一的聯合索引，假設索引欄位為 (a1,a2,...an),則查詢條件(a1 op val1,a2 op val2,...am op valm)m&<=n,可以用到索引，查詢條件中欄位的位置與索引中的欄位位置是一致的。

規則16：聯合索引的建立原則(以下均假設在資料庫表的欄位a,b,c上建立聯合索引(a,b,c))

規則17：重要業務訪問數據表時。但不能通過索引訪問數據時，應該確保順序訪問的記錄數目是有限的，原則上不得多於10.

二.Query語句與應用系統優化

規則18：合理構造Query語句

規則19：應用系統的優化

各規則詳細解讀，歡迎閱讀 MySQL資料庫設計總結

先讀寫分離、再垂直拆分、再水平拆分！

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

我最近正在解決這個問題

我現在的公司有三張表，是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

太多了，洗澡去先－－

當然，我還是紙上談兵了，lz把上下文給足，我可以幫你看一下

@哈哈答主答得非常好了，但是不太贊同答主給出的順序。

一個合格的技術人，應該能做出適用於未來的產品。引入分散式會帶來很多麻煩，但它會讓你走得更遠。這不正是技術人的價值所在嗎？

看到有人回復說有錢就上Oracle了。Facebook，阿里沒錢嗎？錢不是最重要的考慮。再說他們的技術實力沒有甲骨文強嗎？萬萬不要迷信IOE。

首先，任何優化，都需要你了解你的業務，了解你的數據。

QPS要到多少？- 帶寬及存儲夠的情況下，單機幾千QPS妥妥的。
讀寫比例如何？- 讀多寫少和寫多讀少，優化方法是有很大差別的。設置於只讀場景，果斷壓縮。
數據是否快速增長？- 基本就是QPS的要求。
數據及服務的SLA要到多少？- 數據需不需要強一致？HA做到什麼程度？
諸如此類。

不同的場景有不同的側重，解決方案是不同的。而對於一些典型的場景可能會有成熟的解決方案。

題主已註明「千萬級」，因此以下假設題主為最常見的場景：大量數據，QPS要求高，讀多寫少，數據快速增長，SLA要求高。

其次，說優化的方法。

主要從三個維度說：Why, How, When。

0. sql vs nosql

有些跑題，但也是很重要的一方面。

Why: nosql天生分布，而且大多針對某種類型的數據、某種使用場景做過優化。

比如大批量的監控數據，用mysql存費時費力，可以選擇mongo，甚至時間序列資料庫，存取會有量級提升。

How: 找對應解決方案。

When: 有足夠誘惑 - 針對使用場景，有成熟解決方案，效率獲得大量提升。

1. 優化shema、sql語句+索引

Why: 再好的MySQL架構也扛不住一個頻繁的垃圾查詢。不合理的schema設計也會導致數據存取慢。索引的作用不必多說，但如innodb下，錯的索引帶來的可能不只是查詢變慢而已。

How: 設計階段就需要預計QPS及數據規模，參考業務場景對數據的要求，合理設計表結構（參考mysql在線DDL問題），甚至違反設計範式做到適當冗餘。生產環境分析慢日誌，優化語句。索引的設計需要知道索引是怎麼用的，比如innodb的加鎖機制。

When: 這個不僅僅是第一個要考慮的，而應該是需要持續去優化的。特別是要參考業務。但實際環境中如果是這個的問題，那一般比較幸運了，因為一般已經優化過很多了。實際中遇到的一般是更深的問題。

2. 緩存

緩存沒有那麼簡單。

緩存對於應用不是完全透明的，除非你用Django這種成熟框架，而且緩存粒度很大，但實際。。。像python，最少也得加幾個裝飾器。

如何保證緩存裡面的數據是始終正確的？寫數據前失效緩存還是寫數據後？

緩存掛了或者過冷，流量壓到後端mysql了怎麼辦？

緩存也不是萬能的。寫多讀少，命中率會很低。

How: memcache用做緩存，redis用於需要持久化的場景。（redis能不能完全取代memcache？呵呵。。）

還可以使用mysql自帶的query cache，對應用基本完全透明。但會受限於本機。而且只緩存查詢結果，mc和redis可以緩存一些加工後的數據。

而且數據量大、QPS大的情況下，也需要考慮分片及HA的問題。如果有一個數據過熱，把一個節點壓垮了怎麼辦？

When: 基本上大多數讀多寫少的場景都能用，寫多的情況下可能需要考慮考慮。

3. 複製及讀寫分離

Why: 這個其實是大多數場景下都必須的。因為複製可以實現備份、高可用、負載均衡。就算嫌麻煩不做負載均衡，那備份下總是要的吧？既然已經備份了，何不加個LVS+HAProxy做下HA？順便稍微修改下應用，讀寫分離也就成了。

How: 節點少的情況下，主備。前面加Keepalived+HAProxy等組件，失效自動切換。讀寫分離可能需要修改下應用。

節點多的情況下，一是考慮多級備份，減輕主的壓力。其次可以引入第三方組件，接管主節點的備份工作。

主主不是很推薦。一是需要考慮數據衝突的情況，比如錯開id，同時操作數據後衝突解決。其次如果強一致會導致延遲增加，如果有節點掛了，需要等到超時才返回。

When: 主備幾乎大多數場景。甚至不論數據大小。高可用對應用透明，為啥不用?主主麻煩，建議先用切分。

4. 切分

包括垂直切分和水平切分，實現方式上又包括分庫、分表。

雖然有些難度，但還是推薦常用的。

Why: 垂直切分保證業務的獨立性，防止不同業務爭搶資源，畢竟業務是有優先順序的。

水平切分主要用於突破單機瓶頸。除了主主外，只有切分能真正做到將負載分配下去。

切分後也可對不同片數據進行不同優化。如按時間切分，超過一定時間數據不允許修改，就可以引入壓縮了，數據傳輸及讀取減少很多。

How: 根據業務垂直切分。業務內部分庫、分表。一般都需要修改應用。除分表外，其餘實現不是很複雜。有第三方組件可用，但通用高效又靈活的方式，還是自己寫client。

When: 垂直切分一般都要做，只不過業務粒度大小而已。

分庫有是經常用的，就算當前壓力小，也盡量分出幾個邏輯庫出來。等規模上去了，很方便就遷移擴展。

水平拆分有一定難度，但如果將來一定會到這個規模，又可能用到，建議越早做越好。因為對應用的改動較大，而且遷移成本高。

綜上，資料庫設計要面向現代化，面向世界，面向未來。。。

1. 千萬隻是小case而已，千萬對程序員來說好大啊，人家一天幾千萬的，那DBA不是不要活了？

現在5T,10T 的單個資料庫都很多。程序猿能兼職幹了嗎？？

2. 你需要一個專業的DBA，專業的人干專業的事，DBA手裡有自己的武器

3.程序員來操DBA的心，不是傻，就是楞，在不就是逗比。

我想強調的是「專業性」，資料庫架構、管理、優化，在IT的始祖，美國是一個專門的行業和要求很嚴格的崗位，在我們這裡是什麼情況，一個編代碼能力還不到3-5年的初中級程序猿總要試圖，找到解決海量數據問題的捷徑，還總一種企圖找到「魔術師式的秘訣」的心態，很顯然，並不存在，一個合格的DBA，是經歷各種問題處理、碰壁、深思的成長過程，經歷早已覆蓋了這些初中級程序猿能碰到的你們認為比較難的問題，對他們來說，這些都是很基本的問題， IT本身並沒有捷徑。資料庫的運行本質和一切程序代碼都是一個原理，DBA不過對於資料庫運行的各個環節更加清晰，更有量化，碰到問題的廣度和深度都比寫代碼的程序猿多一些。相反要DBA去調試java代碼可能同樣吃力、難受，還做不出效果。

今天看到這個問題，看到了優秀的回答已經不少了，我再補充幾句，對於千萬級別的大表，如果是mysql資料庫，確實表數量已經算大了，mysql 的合理上限不應該超過500萬。達到千萬級，起碼要進行歷史歸檔，否則會嚴重拖累資料庫性能。雖然有很多answers說千萬級是小case,但是如果這樣的表再不進行優化，結果會不堪設想。
再來說一下我的優化方案，依我看任何偏離業務場景的優化都是耍流氓，如果是訂單表，主要通過訂單id來查詢訂單信息，則可以對這樣的表進行垂直分庫，每個庫表容量500萬條，按訂單號維度給拆分到多個庫，而在查詢的時候，使用訂單號查詢，通過某個業務規則，直接定位到要查詢的目標庫。或者通過用戶ID 、日期維度進行分庫，但是千萬要注意，查詢時攜帶分庫的條件。如果是CRM系統，不直接使用訂單號直接查詢，而是一個範圍查詢，返回一個列表集合，而你還繼續執著於分庫分表就能解決你的性能問題，這樣你要對各個庫的查詢結果集進行union，資料庫的性能非但不能提高反而會適得其反！
表設計，看欄位設計是否合理，是否符合資料庫的設計規範，這個我就不細說了。
SQL優化，慢SQL監控，檢查是否有大量的的子查詢和關聯查詢嵌套查詢等，盡量避免使用這些查詢。可以結合redis，memcache等緩存服務，把這些複雜的sql進行拆分，充分利用二級緩存，減少資料庫IO操作。對資料庫連接池，mybatis，hiberante二級緩存充分利用上。盡量使用順序IO代替隨機IO。合理使用索引，盡量避免全表掃描。
索引優化，減少無效索引，提高索引的使用效率，眾所周知的是，索引的維護成本通常是表維護成本的幾倍，所以一定要避免建立無效的索引。建立的索引就要充分利用上。
有人說 水平分表，這個我還是建議三思，搞不好非但不能提升性能反而多了很多的join和磁碟IO，開發起來也麻煩，有很多的業務就是要求一次查詢大部分的欄位看你業務場景了。

拋磚引玉，有問題大家一起討論吧

都千萬數據了，居然沒有具體細節，提問者肯定是新手。

首先這就是個偽問題，千萬級也叫大表？

沒看錯吧，千萬級也叫大表？這個規模的，只要沒把索引用錯，幾乎不用考慮優化。

具體遇到什麼問題了？可以列出來，大家一起診斷一下。

關係資料庫本來是一套嚴密的理論，現在越來越少的人願意去花時間捉摸集合運算，很多時候是沒有用對集合運算導致性能急劇下降。這其實不屬於優化範圍

讀：對MySQL最好的優化，就是不讀MySQL。

不要笑。

是去讀cache。

在cache為王的時代，如果所有請求都落到後端DB，什麼架構都扛不住。

去讀redis和mc吧。

寫：別一個勁什麼東西都直接扔給DB，扔到隊列里，控制好速度去消費，就寫不死。

備份：都千萬級別了，如果被注入或者出現人為失誤丟數據了，沒有備份，估計就完蛋了。

親身經歷過，某大型app，單表366G，被注入，恢復了一天。如果那天沒有備份，估計就得辭職白白了。

單表 7億筆記錄, 80G 大小, 無分區, 每秒可以支持400次增刪查改.

還有一個 2.8億記錄, 大約40Gb 同一個庫的兩個大表.

出現問題了,多半是你設計出問題了, 跟資料庫關係不大.

千萬級，MySQL實際上確實不是什麼壓力，InnoDB的存儲引擎，使用的是B+樹存儲結構，千萬級的數據量，基本也就是三到四層的搜索，如果有合適的索引，性能基本也不是問題。

但經常出現的情況是，業務上面的增長，導致數據量還會繼續增長，為了應對這方面的問題而必須要做擴展了此時可能首先需要考慮的就是分表策略了。

當然分表，可能還有其它幾個原因，比如表變大了，千萬級的資料庫，為了減少運維成本，降低風險，就想到了通過分表來解決問題，這都是比較合適的。

分表，還有另一個方面的意思，就是在數據量更大的情況下，為了分擔業務壓力，將數據表分到不同的實例中去，這樣有兩方面的好處：1. 降低業務風險，如果一套資料庫集群出問題了，那至少還有其它的可以服務，這樣被影響的業務可能只是一部分。2. 降低運維成本，如果資料庫想要做遷移，或者正常維護等操作了，那涉及到的數據量小，下線時間短，操作快，從而對業務影響也就小了。這種方式，我們稱之為「分實例」。

分表的話，還是要根據具體的業務邏輯等方面來做，這方面有更精彩的回答，我這裡貼一下：

========================================

分庫分表是MySQL永遠的話題，一般情況下認為MySQL是個簡單的資料庫，在數據量大到一定程度之後處理查詢的效率降低，如果需要繼續保持高性能運轉的話，必須分庫或者分表了。關於數據量達到多少大是個極限這個事兒，本文先不討論，研究源碼的同學已經證實MySQL或者Innodb內部的鎖粒度太大的問題大大限制了MySQL提供QPS的能力或者處理大規模數據的能力。在這點上，一般的使用者只好坐等官方不斷推出的優化版本了。

在一般運維的角度來看，我們什麼情況下需要考慮分庫分表？

首先說明，這裡所說的分庫分表是指把資料庫數據的物理拆分到多個實例或者多台機器上去，而不是類似分區表的原地切分。

原則零：能不分就不分。

是的，MySQL 是關係資料庫，資料庫表之間的關係從一定的角度上映射了業務邏輯。任何分庫分表的行為都會在某種程度上提升業務邏輯的複雜度，資料庫除了承載數據的存儲和訪問外，協助業務更好的實現需求和邏輯也是其重要工作之一。分庫分表會帶來數據的合併，查詢或者更新條件的分離，事務的分離等等多種後果，業務實現的複雜程度往往會翻倍或者指數級上升。所以，在分庫分表之前，不要為分而分，去做其他力所能及的事情吧，例如升級硬體，升級，升級網路，升級資料庫版本，讀寫分離，負載均衡等等。所有分庫分表的前提是，這些你已經儘力了。

原則一：數據量太大，正常的運維影響正常業務訪問。

這裡說的運維，例如：

（1）對資料庫的備份。如果單表或者單個實例太大，在做備份的時候需要大量的磁碟IO或者網路IO資源。例如1T的數據，網路傳輸佔用50MB的時候，需要20000秒才能傳輸完畢，在此整個過程中的維護風險都是高於平時的。我們在Qunar的做法是給所有的資料庫機器添加第二塊網卡，用來做備份，或者SST，Group Communication等等各種內部的數據傳輸。1T的數據的備份，也會佔用大量的磁碟IO，如果是SSD還好，當然這裡忽略某些廠商的產品在集中IO的時候會出一些BUG的問題。如果是普通的物理磁碟，則在不限流的情況下去執行xtrabackup，該實例基本不可用。

（2）對數據表的修改。如果某個表過大，對此表做DDL的時候，MySQL會鎖住全表，這個時間可能很長，在這段時間業務不能訪問此表，影響甚大。解決的辦法有類似騰訊遊戲DBA自己改造的可以在線秒改表，不過他們目前也只是能添加欄位而已，對別的DDL還是無效；或者使用pt-online-schema-change，當然在使用過程中，它需要建立觸發器和影子表，同時也需要很長很長的時間，在此操作過程中的所有時間，都可以看做是風險時間。把數據表切分，總量減小，有助於改善這種風險。

（3）整個表熱點，數據訪問和更新頻繁，經常有鎖等待，你又沒有能力去修改源碼，降低鎖的粒度，那麼只會把其中的數據物理拆開，用空間換時間，變相降低訪問壓力。

原則二：表設計不合理，需要對某些欄位垂直拆分

這裡舉一個例子，如果你有一個用戶表，在最初設計的時候可能是這樣：

table :users

id bigint 用戶的ID

name varchar 用戶的名字

last_login_time datetime 最近登錄時間

personal_info text 私人信息

xxxxx 其他信息欄位。

一般的users表會有很多欄位，我就不列舉了。如上所示，在一個簡單的應用中，這種設計是很常見的。但是：

設想情況一：你的業務中彩了，用戶數從100w飆升到10個億。你為了統計活躍用戶，在每個人登錄的時候都會記錄一下他的最近登錄時間。並且的用戶活躍得很，不斷的去更新這個login_time，搞的你的這個表不斷的被update，壓力非常大。那麼，在這個時候，只要考慮對它進行拆分，站在業務的角度，最好的辦法是先把last_login_time拆分出去，我們叫它 user_time。這樣做，業務的代碼只有在用到這個欄位的時候修改一下就行了。如果你不這麼做，直接把users表水平切分了，那麼，所有訪問users表的地方，都要修改。或許你會說，我有proxy，能夠動態merge數據。到目前為止我還從沒看到誰家的proxy不影響性能的。

設想情況二：personal_info這個欄位本來沒啥用，你就是讓用戶註冊的時候填一些個人愛好而已，基本不查詢。一開始的時候有它沒它無所謂。但是到後來發現兩個問題，一，這個欄位佔用了大量的空間，因為是text嘛，有很多人喜歡長篇大論地介紹自己。更糟糕的是二，不知道哪天哪個產品經理心血來潮，說允許個人信息公開吧，以方便讓大家更好的相互了解。那麼在所有人獵奇窺私心理的影響下，對此欄位的訪問大幅度增加。資料庫壓力瞬間抗不住了，這個時候，只好考慮對這個表的垂直拆分了。

原則三：某些數據表出現了無窮增長

例子很好舉，各種的評論，消息，日誌記錄。這個增長不是跟人口成比例的，而是不可控的，例如微博的feed的廣播，我發一條消息，會擴散給很多很多人。雖然主體可能只存一份，但不排除一些索引或者路由有這種存儲需求。這個時候，增加存儲，提升機器配置已經蒼白無力了，水平切分是最佳實踐。拆分的標準很多，按用戶的，按時間的，按用途的，不在一一舉例。

原則四：安全性和可用性的考慮

這個很容易理解，雞蛋不要放在一個籃子里，我不希望我的資料庫出問題，但我希望在出問題的時候不要影響到100%的用戶，這個影響的比例越少越好，那麼，水平切分可以解決這個問題，把用戶，庫存，訂單等等本來同統一的資源切分掉，每個小的資料庫實例承擔一小部分業務，這樣整體的可用性就會提升。這對Qunar這樣的業務還是比較合適的，人與人之間，某些庫存與庫存之間，關聯不太大，可以做一些這樣的切分。

原則五：業務耦合性考慮

這個跟上面有點類似，主要是站在業務的層面上，我們的火車票業務和烤羊腿業務是完全無關的業務，雖然每個業務的數據量可能不太大，放在一個MySQL實例中完全沒問題，但是很可能烤羊腿業務的DBA 或者開發人員水平很差，動不動給你出一些幺蛾子，直接把資料庫搞掛。這個時候，火車票業務的人員雖然技術很優秀，工作也很努力，照樣被老闆打屁股。解決的辦法很簡單:惹不起，躲得起。

《三國演義》第一回：「話說天下大勢，分久必合，合久必分。」其實在實踐中，有時候可能你原本要分，後來又發現分了還得合，分分合合，完全是現實的需求，隨需而變才是王道，而DBA的價值也能在此體現。或分或合的情況太多，不能窮舉，歡迎繼續交流這個話題，如果以上有錯誤之後，也請批評指正。

給生活加點料。

================================

文章摘自微信公眾號formysql。

如何分表的方案，其實這個不能一概而論，與業務邏輯有關係，與數據性質有關係，比如訂單類型的，那就非常容易了，通過時間這個特性，可以通過一個路由表，把數據分散到多個實例上面，或者多個表上面，擴展性非常強，但是如果是用戶關係等類似的表，他的唯一可以做HASH的值就是用戶ID，做HASH時，涉及到不均勻、可擴展能力，遷移麻煩等問題，所以還是不太容易的，所以只能是具體問題具體分析了。

上面說的是分表的優化方案，當然還有其它方案，那就是要儘可能的寫好SQL語句，不要留坑，MySQL就是適合那種快進快出的語句，儘可能的別把業務邏輯放到MySQL中去處理，要保持MySQL的高效運行才是最正確的選擇。

mysql只做簡單的事情，千萬級的表，不論如何優化，同樣的SQL都沒有十萬級的表訪問快。

如果設計千萬級的大表，要問自己幾個問題：

1.數據是否存在明顯的冷熱（考慮舊數據歸檔）

2.是否可以按照時間，區域拆分表

3.如果欄位過多是否可以欄位的關聯性進行拆分

說白了就是：歸檔，垂直拆分，水平拆分

周松，http://ChinaDBA.com
先讀寫分離、再垂直拆分、再水平拆分！

提到優化，先要確定出現的問題，是存儲引擎選擇問題，還是sql語句使用問題（如：索引）亦或者是單一存儲伺服器對於千萬級別的數據力不從心。

解決方法：

1、根據不同業務選用不同存儲引擎，雖然一般情況下都優先選擇InnoDB。

2、分析sql語句的影響結果集。查慢查詢日誌，定位慢查詢的sql語句，查看是否有鎖定的sql操作。

3、分庫，分表，分區（慎用分區，往往OLTP操作不適用分區，分區反倒會拖慢原有查詢）

4、利用緩存或NoSql代替現有一些熱點查詢操作，減輕Mysql壓力。

分庫：

可以基於業務邏輯拆分，不同業務分布在不同伺服器中，減少單一伺服器壓力

分表：

垂直分表和水平分表兩種方式

垂直分表：將表中的一些頻繁更新和非頻繁更新的欄位分開存放

水平分表，三種分表方式：

均等分表，哈希方式分表，優點是負載平均分布，缺點是當容量持續增加時擴榮不方便，需要重新分表，主鍵就很不好處理。
遞增分表，比如每一千萬數據開一個新表，優點是自適應強，缺點是數據負載不均衡，需要代碼層額外處理。
時間分表，根據不同的創建時間分表，適用於OLAP應用。

推薦一本書，《MySQL高性能優化》

先看看網上一個牛表：關係型資料庫——單表60億

單表60億記錄等大數據場景的MySQL優化和運維之道 | 高可用架構

純查詢的操作可以是這樣：

1、查詢語句上，只寫必要的欄位，建好索引，注意一下查詢條件的使用，多表查詢不要用框架（自己寫sql）

ref 淺談MySQL中優化sql語句查詢常用的30種方法_Mysql_腳本之家

2、一定要分頁（一些小表也養成習慣，哪怕你在前台設置多一點可選記錄數選項（如，10，50，100，500，1000(區域網環境)，不能再多了孩子））

3、在一定數量的基礎上，做好表分區

4、拆表

5、拆庫

其他操作：

1、表設計很關鍵，不要老是去改表（mysql 5.6以上版本支持online ddl之後，改動表結構對數據的影響少了）

2、使用事務操作表數據（減少操作時間）

3、使用緩存（減少資料庫連接次數）

4、讀寫分離

5、使用集群（有難同當，把單機的壓力分到多機，還可以避免一個伺服器掛掉了）

硬體方面：

1、硬碟上ssd，我猜的（手動滑稽）

2、內存搞大點是很必要的

先寫這麼點吧，除了硬體方面容易搞定，其他的地方都是要花點功夫的。

你總得說說場景吧，不提場景就談優化的，都是耍流氓。