MySQL學習點滴 --分區表_ZenDei技術網路在線

寫在前面：筆者之前也有一些MySQL方面的筆記，其中部分內容來自極客時間中丁奇老師的課程。後經園友提醒，這個做法確實不太好。之後我仍會繼續更新一下MySQL方面的學習記錄，在自己理解之後用自己的方式記錄下來。學習與記錄，也是我寫博客的初衷。概述：分區功能並不是在存儲引擎層完成的，因此很多存儲引擎 ...

　　寫在前面：筆者之前也有一些MySQL方面的筆記，其中部分內容來自極客時間中丁奇老師的課程。後經園友提醒，這個做法確實不太好。之後我仍會繼續更新一下MySQL方面的學習記錄，在自己理解之後用自己的方式記錄下來。學習與記錄，也是我寫博客的初衷。

概述：

　　分區功能並不是在存儲引擎層完成的，因此很多存儲引擎包括InnoDB, MyISAM, NDB等都支持分區功能。但也並不是所有的存儲引擎都支持分區。在使用分區前，首先要瞭解一下存儲引擎對分區的支持情況。如果不作特殊說明，預設是在InnoDB下進行說明。

　　所謂分區，指的是將一個表或索引分解為更小的部分。從物理層面來說，可能是分成了N個物理分區，每個分區都是獨立的。從邏輯上來說，這N個物理分區仍是一個表或一個索引。

　　分區可以分為兩大類：

水平分區，指的是將同一表中不同行的記錄分配到不同的物理文件中。
垂直分區，指的是將同一表中不同列的記錄分配到不同的物理文件中。

　　MySQL在5.1版本中加入了對水平分區的支持，其最新版本是否支持垂直分區筆者暫未考證。網上有一些關於MySQL垂直分區的內容，大都也是在業務層面將表進行拆分，“手動的”垂直分區。

　　可以通過命令 SHOW PLUGINS; 來查看是否開啟了分區功能。(partition的status值為ACTIVE)。

　　MySQL支持下麵幾種類型的分區：

RANGE分區：行數據基於一個給定連續區間的列值被放入分區。從5.5版本開始支持RANGE COLUMNS的分區。
LIST分區：和RANGE分區類似，只是LIST分區面向的是離散的值。從5.5版本開始支持LIST COLUMNS的分區。
HASH分區：根據用戶自定義的表達式的返回值進行分區，返回值不能為負數。
KEY分區：根據MySQL資料庫提供的哈希函數來進行分區。

　　不論創建哪種類型的分區，如果表中存在主鍵或唯一索引，分區列必須是唯一索引的一個組成部分。(這裡筆者初次接觸時還鬧了個笑話。上面提到了MySQL支持的是水平分區，也就是說把不同的行記錄分配到不同的物理文件中，那為啥還有個分區列？分區列還必須是唯一索引的一個組成部分？其實是這樣的，在分區表中如果要插入一條記錄，肯定要先確定應該插入到哪個分區里去。而確定它屬於哪個分區就是依靠了分區列的值，根據這個值再按照不同分區自身的規則，就可以確定這條記錄應該被分配到哪個分區了)。唯一索引可以是允許NULL值的，並且分區列只要是唯一索引的一個組成部分，不需要整個唯一索引分區列都是分區列。如唯一索引是 UNIQUE KEY(a,b)，分區列可以只指定a列，PARTITION BY HASH(a)；

　　如果創建表時沒有指定主鍵，唯一索引，那麼可以指定任何一個列為分區列。

分區類型：

RANGE分區：

　　這是最常用的一種分區，我們來看一個簡單的例子：

CREATE TABLE range_t(
id INT
)ENGINE = INNODB
PARTITION BY RANGE (id)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20)
);

　　由建表語句很容易看出我們把range_t分成了p0,p1兩部分。由於Range分區是給連續區間分區，因此p1的區間範圍其實[10,20)。此處需要註意的是，如果僅僅按照上面我們的分區來的話，是不能向表range_t中插入id大於20的記錄的。

　　這種情況下我們可以添加一個MAXVALUE值的分區，MAXVALUE可以理解為正無窮，因此區間可以改變為[20,MAXVALUE).

ALTER TABLE range_t ADD PARTITION(
partition p2 values less than maxvalue);

　　range分區的一個典型的應用場景是記錄與日期相關的記錄。例如要記錄某種交易記錄，可以按年份時間進行分區。如

...
PARTITION BY RANGE (YEAR(date))(
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020)      
);

　　根據date所屬的年份進行分區，year函數取得的值如果小於2019就歸檔到p2018分區。這樣做有這麼一些好處。首先是方便管理，如果我們要刪除18年的數據,可以直接對分區p2018進行刪除。 alter table t drop partition p2018; 另一方面，這樣分區也可以加快某些查詢的速度。同樣以p2018分區進行舉例，如果你確定要查詢的範圍只在2018這個區間內，可以直接對這個分區進行查詢：select * from t partition(p2018);

　　有一點需要註意，優化器可以對range分區中的部分函數（如YEAR(),TO_DAYS()...）進行優化選擇，而對形如YEAR(date)*100 + MONTH(date)這樣的分區條件是無能為力的。

LIST分區：

　　list分區和range分區很相似，區別在於list分區的值是離散的。例如建表語句：

CREATE TABLE list_t(
a INT,
b INT
)ENGINE = INNODB
PARTITION BY LIST(b)(
    PARTITION p0 values IN(1,3,5,7),
    PARTITION p1 values IN(2,4,6,8)   
);

　　和range分區一樣，如果你插入的記錄的分區列的值不在list分區的範圍內，MySQL資料庫會拋出異常。另外，如果一次插入多個行的記錄，而這些記錄當中存在分區未定義的值時，MyISAM和InnoDB存儲引擎的處理方式不同。MyISAM會將之前的行資料庫都插入，但之後的不會插入。而InnoDB會將其視為一個事務，因此沒有任何事務插入。

　　MyISAM:

　　Innodb:

HASH分區：

　　Hash分區的目的是將數據均勻地分部到預先定義的各個分區中，儘量保證各分區的數據量相等。在RANGE和LIST分區中，必須明確指定一個給定的列值活列值所在的集合範圍，而HASH分區中，MySQL自動完成這些工作，用戶所要做的只是基於將要進行哈希分區的列指定一個列值或表達式，以及指定被分區的表將要被分隔成幾部分。一個Hash分區的建表語句例子如下：

CREATE TABLE hash_t(
a INT,
b INT
)ENGINE = InnoDB
PARTITION BY HASH(a+b)
PARTITIONS 4;

　　如上所述，用戶所要做的只是基於將要進行哈希分區的列指定一個列值或表達式。這裡我們使用a+b的值來作為進行hash的值，當然你也可以直接使用欄位a或b，或是別的表達式。另外，後面的PARTITIONS 4；代表了要分隔成幾個區，這裡要求是一個非負整數，預設值是1.

KEY分區：

　　Key分區和Hash分區很類似，區別在於hash分區使用用戶定義的函數進行分區，key分區使用MySQL資料庫提供的函數進行分區。對於NDB Cluster引擎，MySQL使用MD5函數來進行分區，對於其他引擎，MySQL資料庫使用其內部哈希函數，這些函數基於與Password()一樣的運算規則。

COLUMNS分區：

　　前面介紹的這幾種分區有一個共同條件，即數據必須是整型(interger)，如果不是整形則需要通過函數將其轉化為整型，如YEAR()等。從5.5版本開始，MySQL支持COLUMNS分區，可以理解成是Range分區和list分區的一種優化，它允許直接使用非整形的數據進行分區，分區根據類型直接比較而得，不需要額外的轉型處理。此外，Range COLUMNS允許對多個列的值進行分區。COLUMNS分區所支持的類型：

所有的整型類型，如INT,SMALLINT,TINYINT,BIGINT。註意，FLOAT和DECIMAL不支持。
日期類型，僅支持DATE和DATETIME。
字元串類型，如CHAR,VARCHAR,BINARYHE VARBINARY。註意，BLOB和TEXT不支持。

　　Range Columns對多個列的值進行分區的例子如下：

CREATE TABLE range_column_t(
a INT,
b INT,
c char(3)
)engine = InnoDB
PARTITION BY RANGE COLUMNS(a,b,c)(
PARTITION p0 VALUES LESS THAN (5,10,'c'),
PARTITION p1 VALUES LESS THAN (10,20,'m'),
PARTITION p2 VALUES LESS THAN (30,50,'z')
);

　　到這裡，你應該和我一樣有一個疑問，如果我三個值分別屬於不同的區間則會被插入到哪個分區呢。比如插入這麼一條記錄：insert into range_column_t values(4,9,'n'); a，b欄位的值都在p0分區範圍內，c的值p2分區範圍內，實際上也插入成功了。我們來看看結果吧：

　　如果我們再插入一條記錄：insert into range_column_t values(25,15,'a');查看結果如下：

　　看來這種方式下，是按照分區列的順序進行分區的，滿足第一個條件後就會直接被分配到對應分區。

子分區：

　　子分區是在分區的基礎上再進行分區，有時也稱這種分區為複合分區。MySQL資料庫允許在Range和List的分區上再進行Hash分區或Key的子分區。一個建立子分區的例子：

CREATE TABLE sub_t(
a INT,
b DATE
)engine = InnoDB
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE   
);

　　關於子分區，有幾個地方需要註意一下：

每個子分區的數量必須相同。
要在一個分區表的任何分區上使用SUBPARTITION來明確定義任何子分區，就必須定義所有的子分區。
每個SUBPARTITION子句必須包括子分區的一個名字
子分區的名字必須是唯一的。

NULL值：

　　MySQL資料庫允許對NULL值做分區，但處理方式可能不同於其他資料庫。在MySQL的分區中，Null值被認為總是小於任何一個非NULL值。並且對於不同的分區類型，處理方式也稍有不同。

對於Range分區，Null值會被放入最左邊的分區。因此，如果刪除最左側的分區，假設該分區是定義是 LESS THAN 10,那麼刪除的實際上是小於10的所有記錄和包含Null值的記錄。
在List分區中則必須顯示的指出哪個分區中放入Null值，否則會報錯。
而在HASH和Key分區中，任何分區函數都會將含有NULL值的記錄返回為0。

分區和性能：

　　其實有些類似於索引，並不是說無腦地添加索引，或是使用分區，資料庫的查詢就會更快。我們真正要做的是根據實際業務需求去具體的看待問題，如前面提到的按時間記錄的交易記錄的表，假設有這樣的一張大表，並且可以明確的按照時間分區，且需要頻繁訪問。那麼確實是可以使用分區來提高效率，每次查詢時儘量只訪問對應的分區即可。

　　但實際情況中也可能存在這麼一種類型的表，它的數據量也很大，訪問也很頻繁。但每次可能只是會通過索引去訪問幾條記錄，而不需要一次返回很多很多記錄。這種情況下，分區可能會帶來不好的影響。我們知道，正常情況下B+樹索引(MySQL索引採用B+樹結構)只需要2~3次IO操作即可找到對應的記錄。如果盲目地使用分區，反而可能會增加IO操作的次數。