表分區(qū)

MySQL在5.1版引入的分區(qū)是一種簡單的水平拆分，用戶需要在建表的時候加上分區(qū)參數(shù)，對應(yīng)用是透明的無需修改代碼

對用戶來說，分區(qū)表是一個獨(dú)立的邏輯表，但是底層由多個物理子表組成，實現(xiàn)分區(qū)的代碼實際上是通過對一組底層表的對象封裝，但對SQL層來說是一個完全封裝底層的黑盒子。MySQL實現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)的子表定義，沒有全局索引。

用戶的SQL語句是需要針對分區(qū)表做優(yōu)化，SQL條件中要帶上分區(qū)條件的列，從而使查詢定位到少量的分區(qū)上，否則就會掃描全部分區(qū)，可以通過 EXPLAIN PARTITIONS來查看某條SQL語句會落在那些分區(qū)上，從而進(jìn)行SQL優(yōu)化，如下圖5條記錄落在兩個分區(qū)上：

1. mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
2. +----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
3. | id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
4. +----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
5. |  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
6. +----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
7. 1 row in set (0.00 sec)

分區(qū)的好處是：

• 可以讓單表存儲更多的數(shù)據(jù)

• 分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過清楚整個分區(qū)批量刪除大量數(shù)據(jù)，也可以增加新的分區(qū)來支持新插入的數(shù)據(jù)。另外，還可以對一個獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作

• 部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上，速度會很快

• 分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而搞笑利用多個硬件設(shè)備

• 可以使用分區(qū)表賴避免某些特殊瓶頸，例如InnoDB單個索引的互斥訪問、ext3文件系統(tǒng)的inode鎖競爭

• 可以備份和恢復(fù)單個分區(qū)

分區(qū)的限制和缺點：

• 一個表最多只能有1024個分區(qū)

• 如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來

• 分區(qū)表無法使用外鍵約束

• NULL值會使分區(qū)過濾無效

• 所有分區(qū)必須使用相同的存儲引擎

分區(qū)的類型：

• RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)

• LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇

• HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式

• KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值

分區(qū)適合的場景有：

最適合的場景數(shù)據(jù)的時間序列性比較強(qiáng)，則可以按時間來分區(qū)，如下所示：

1. CREATE TABLE members (
2.    firstname VARCHAR(25) NOT NULL,
3.    lastname VARCHAR(25) NOT NULL,/li>
4.    username VARCHAR(16) NOT NULL,
5.    email VARCHAR(35),
6.    joined DATE NOT NULL
7. )
8. PARTITION BY RANGE( YEAR(joined) ) (
9.    PARTITION p0 VALUES LESS THAN (1960),
10.    PARTITION p1 VALUES LESS THAN (1970),
11.    PARTITION p2 VALUES LESS THAN (1980),
12.    PARTITION p3 VALUES LESS THAN (1990),
13.    PARTITION p4 VALUES LESS THAN MAXVALUE
14. );

查詢時加上時間范圍條件效率會非常高，同時對于不需要的歷史數(shù)據(jù)能很容的批量刪除。

如果數(shù)據(jù)有明顯的熱點，而且除了這部分?jǐn)?shù)據(jù)，其他數(shù)據(jù)很少被訪問到，那么可以將熱點數(shù)據(jù)單獨(dú)放在一個分區(qū)，讓這個分區(qū)的數(shù)據(jù)能夠有機(jī)會都緩存在內(nèi)存中，查詢時只訪問一個很小的分區(qū)表，能夠有效使用索引和緩存

另外MySQL有一種早期的簡單的分區(qū)實現(xiàn) - 合并表（merge table），限制較多且缺乏優(yōu)化，不建議使用，應(yīng)該用新的分區(qū)機(jī)制來替代

垂直拆分

垂直分庫是根據(jù)數(shù)據(jù)庫里面的數(shù)據(jù)表的相關(guān)性進(jìn)行拆分，比如：一個數(shù)據(jù)庫里面既存在用戶數(shù)據(jù)，又存在訂單數(shù)據(jù)，那么垂直拆分可以把用戶數(shù)據(jù)放到用戶庫、把訂單數(shù)據(jù)放到訂單庫。垂直分表是對數(shù)據(jù)表進(jìn)行垂直拆分的一種方式，常見的是把一個多字段的大表按常用字段和非常用字段進(jìn)行拆分，每個表里面的數(shù)據(jù)記錄數(shù)一般情況下是相同的，只是字段不一樣，使用主鍵關(guān)聯(lián)

比如原始的用戶表是：

垂直拆分后是：

垂直拆分的優(yōu)點是：

• 可以使得行數(shù)據(jù)變小，一個數(shù)據(jù)塊(Block)就能存放更多的數(shù)據(jù)，在查詢時就會減少I/O次數(shù)(每次查詢時讀取的Block 就少)

• 可以達(dá)到最大化利用Cache的目的，具體在垂直拆分的時候可以將不常變的字段放一起，將經(jīng)常改變的放一起

• 數(shù)據(jù)維護(hù)簡單

缺點是：

• 主鍵出現(xiàn)冗余，需要管理冗余列

• 會引起表連接JOIN操作（增加CPU開銷）可以通過在業(yè)務(wù)服務(wù)器上進(jìn)行join來減少數(shù)據(jù)庫壓力

• 依然存在單表數(shù)據(jù)量過大的問題（需要水平拆分）

• 事務(wù)處理復(fù)雜

水平拆分

概述

水平拆分是通過某種策略將數(shù)據(jù)分片來存儲，分庫內(nèi)分表和分庫兩部分，每片數(shù)據(jù)會分散到不同的MySQL表或庫，達(dá)到分布式的效果，能夠支持非常大的數(shù)據(jù)量。前面的表分區(qū)本質(zhì)上也是一種特殊的庫內(nèi)分表

庫內(nèi)分表，僅僅是單純的解決了單一表數(shù)據(jù)過大的問題，由于沒有把表的數(shù)據(jù)分布到不同的機(jī)器上，因此對于減輕MySQL服務(wù)器的壓力來說，并沒有太大的作用，大家還是競爭同一個物理機(jī)上的IO、CPU、網(wǎng)絡(luò)，這個就要通過分庫來解決

前面垂直拆分的用戶表如果進(jìn)行水平拆分，結(jié)果是：

實際情況中往往會是垂直拆分和水平拆分的結(jié)合，即將 Users_A_M和 Users_N_Z再拆成 Users和 UserExtras，這樣一共四張表

水平拆分的優(yōu)點是:

• 不存在單庫大數(shù)據(jù)和高并發(fā)的性能瓶頸

• 應(yīng)用端改造較少

• 提高了系統(tǒng)的穩(wěn)定性和負(fù)載能力

缺點是：

• 分片事務(wù)一致性難以解決

• 跨節(jié)點Join性能差，邏輯復(fù)雜

• 數(shù)據(jù)多次擴(kuò)展難度跟維護(hù)量極大

分片原則

• 能不分就不分，參考單表優(yōu)化

• 分片數(shù)量盡量少，分片盡量均勻分布在多個數(shù)據(jù)結(jié)點上，因為一個查詢SQL跨分片越多，則總體性能越差，雖然要好于所有數(shù)據(jù)在一個分片的結(jié)果，只在必要的時候進(jìn)行擴(kuò)容，增加分片數(shù)量

• 分片規(guī)則需要慎重選擇做好提前規(guī)劃，分片規(guī)則的選擇，需要考慮數(shù)據(jù)的增長模式，數(shù)據(jù)的訪問模式，分片關(guān)聯(lián)性問題，以及分片擴(kuò)容問題，最近的分片策略為范圍分片，枚舉分片，一致性Hash分片，這幾種分片都有利于擴(kuò)容

• 盡量不要在一個事務(wù)中的SQL跨越多個分片，分布式事務(wù)一直是個不好處理的問題

• 查詢條件盡量優(yōu)化，盡量避免Select * 的方式，大量數(shù)據(jù)結(jié)果集下，會消耗大量帶寬和CPU資源，查詢盡量避免返回大量結(jié)果集，并且盡量為頻繁使用的查詢語句建立索引。

• 通過數(shù)據(jù)冗余和表分區(qū)賴降低跨庫Join的可能

這里特別強(qiáng)調(diào)一下分片規(guī)則的選擇問題，如果某個表的數(shù)據(jù)有明顯的時間特征，比如訂單、交易記錄等，則他們通常比較合適用時間范圍分片，因為具有時效性的數(shù)據(jù)，我們往往關(guān)注其近期的數(shù)據(jù)，查詢條件中往往帶有時間字段進(jìn)行過濾，比較好的方案是，當(dāng)前活躍的數(shù)據(jù)，采用跨度比較短的時間段進(jìn)行分片，而歷史性的數(shù)據(jù)，則采用比較長的跨度存儲。

總體上來說，分片的選擇是取決于最頻繁的查詢SQL的條件，因為不帶任何Where語句的查詢SQL，會遍歷所有的分片，性能相對最差，因此這種SQL越多，對系統(tǒng)的影響越大，所以我們要盡量避免這種SQL的產(chǎn)生。