[[238028]]

思考

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

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

以及調整一些參數值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

案例一

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

案例二

任何偏離業務場景的優化都是耍流氓，如果是訂單表，主要通過訂單id來查詢訂單信息，則可以對這樣的表 進行垂直分庫，每個庫表容量500萬條，按訂單號維度 給拆分到多個庫，而在查詢的時候，使用訂單號查詢，通過某個業務規則，直接定位到要查詢的目標庫。或者通過用戶ID 、日期維度 進行分庫，但是千萬要注意，查詢時攜帶 分庫的條件。 如果是CRM系統 ，不直接使用訂單號直接查詢，而是一個范圍查詢，返回一個列表集合，而你還繼續執著于分庫分表就能解決你的性能問題，這樣你要對各個庫的查詢結果集進行union，數據庫的性能非但不能提高反而會適得其反！

解決方案

• 首先，任何優化，都需要你了解你的業務，了解你的數據。
• 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. 復制及讀寫分離（做主從復制或主主復制，讀寫分離，可以在應用層做，效率高，也可以用三方工具，第三方工具推薦360的atlas,其它的要么效率不高，要么沒人維護）

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

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

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

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

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

4. 切分

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

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

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

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

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

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

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

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

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

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

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

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

原則零：能不分就不分。

是的，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 或者開發人員水平很差，動不動給你出一些幺蛾子，直接把數據庫搞掛。這個時候，火車票業務的人員雖然技術很優秀，工作也很努力，照樣被老板打屁股。解決的辦法很簡單:惹不起，躲得起。

20條規則摘要如下：

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

規則2：命名規則。

規則3：數據庫字段類型定義

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

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

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

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

規則7： 水平分表，這個我還是建議 三思，搞不好非但不能提升性能反而多了很多的join和磁盤IO，開發起來也麻煩，有很多的業務就是要求一次查詢大部分的字段 看你業務場景了。大表可以考慮水平拆分。大表影響查詢效率，根據業務特性有很多拆分方式，像根據時間遞增的數據，可以根據時間來分。以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。

規則18：合理構造Query語句，慢SQL監控，檢查是否有大量的的子查詢和關聯查詢 嵌套查詢等，盡量避免使用這些查詢，使用連接（JOIN）來代替子查詢(Sub-Queries)，使用聯合(UNION)來代替手動創建的臨時表。

規則19：應用系統的優化。

規則20：可以結合redis，memcache等緩存服務，把這些復雜的sql進行拆分，充分利用二級緩存，減少數據庫IO操作。對數據庫連接池，mybatis，hiberante二級緩存充分利用上。盡量使用順序IO代替隨機IO。合理使用索引，盡量避免全表掃描。