概述

先明確一點，Oracle是多進程，mysql是單進程多線程，然後我們再繼續今天學習的內容--mysql並行數據庫與分區。

1、並行數據庫

1.1、並行數據庫的體系結構

我們一般對數據庫系統性能的度量主要有兩種方式：

(1)吞吐量(Throughput)，在給定的時間段裡所能完成的任務數量；

(2)響應時間(Response time)，單個任務從提交到完成所需要的時間。對於處理大量小事務的系統，通過並行地處理許多事務可以提高它的吞吐量。對於處理大事務的系統，通過並行的執行事務的子任務，可以縮短系統響應時間。

並行機有三種基本的體系結構，相應的，並行數據庫的體系結構也可以大概分為三類：

 共享內存(share memeory)：所有處理器共享一個公共的存儲器；

 共享磁盤(share disk)：所有處理器共享公共的磁盤；這種結構有時又叫做集群(cluster)；

 無共享(share nothing)：所有處理器既不共享內存，也不共享磁盤。

如圖所示：

1.1.1、 共享內存

該結構包括多個處理器、一個全局共享的內存（主存儲器）和多個磁盤存儲，各個處理器通過高速通訊網絡（Interconnection Network）與共享內存連接，並均可直接訪問系統中的一個、多個或全部的磁盤存儲，在系統中，所有的內存和磁盤存儲均由多個處理器共享。

這種結構的優點在於，處理器之間的通信效率極高，訪問內存的速度要比消息通信機制要快很多。這種結構的缺點在於，處理器的規模不能超過32個或者64個，因為總線或互邊網絡是由所有的處理器共享，它會變成瓶頸。當處理器數量到達某一個點時，再增加處理器已經沒有什麼好處。

共享內存結構通常在每個處理器上有很大的高速緩存，從而減少對內存的訪問。但是，這些高速緩存必須保持一致，也就是緩存一致性(cache-coherency)的問題。

1.1.2、 共享磁盤

該結構由多個具有獨立內存（主存儲器）的處理器和多個磁盤存儲構成，各個處理器相互之間沒有任何直接的信息和數據的交換，多個處理器和磁盤存儲由高速通信網絡連接，每個處理器都可以讀寫全部的磁盤存儲。

共享磁盤與共享內存結構相比，有以下一些優點：(1)每個處理器都有自己的存儲器，存儲總線不再是瓶頸；(2)以一種較經濟的方式提供了容錯性(fault tolerence)，如果一個處器發生故障，其它處理器可以代替工作。

該結構的主要問題不是在於可擴展性問題，雖然存儲總線不是瓶頸，但是，與磁盤之間的連接又成了瓶頸。

1.1.3、 無共享

該結構由多個完全獨立的處理節點構成，每個處理節點具有自己獨立的處理器、獨立的內存（主存儲器）和獨立的磁盤存儲，多個處理節點在處理器級由高速通信網絡連接，系統中的各個處理器使用自己的內存獨立地處理自己的數據。

這 種結構中，每一個處理節點就是一個小型的數據庫系統，多個節點一起構成整個的分佈式的並行數據庫系統。由於每個處理器使用自己的資源處理自己的數據，不存在內存和磁盤的爭用，提高的整體性能。另外這種結構具有優良的可擴展性——只需增加額外的處理節點，就可以以接近線性的比例增加系統的處理能力。

這種結構中，由於數據是各個處理器私有的，因此係統中數據的分佈就需要特殊的處理，以儘量保證系統中各個節點的負載基本平衡，但在目前的數據庫領域，這個數據分佈問題已經有比較合理的解決方案。

由於數據是分佈在各個處理節點上的，因此，使用這種結構的並行數據庫系統，在擴展時不可避免地會導致數據在整個系統範圍內的重分佈（Re-Distribution）問題。

Shared-Nothing結構的典型代表是Teradata(並行數據庫的先驅)，值得一提的是，MySQL NDB Cluster也使用了這種結構。

1.2、I/O並行(I/O Parallelism)

I/O並行的最簡單形式是通過對關係劃分，放置到多個磁盤上來縮減從磁盤讀取關係的時間。並行數據庫中數據劃分最通用的形式是水平劃分(horizontal portioning)，一個關係中的元組被劃分到多個磁盤。

1.2.1、常用劃分技術

假定將數據劃分到n個磁盤D0，D1，…，Dn中。

(1) 輪轉法(round-bin)。對關係順序掃描，將第i個元組存儲到標號為Di%n的磁盤上；該方式保證了元組在多個磁盤上均勻分佈。

(2) 散列劃分(hash partion)。選定一個值域為{0, 1, …,n-1}的散列函數，對關係中的元組基於劃分屬性進行散列。如果散列函數返回i，則將其存儲到第i個磁盤。

(3) 範圍劃分(range partion)。

由於將關係存儲到多個磁盤，讀寫時能同時進行，劃分(partion)能大大提高系統的讀寫性能。數據的存取可以分為以下幾類：

• 掃描整個關係；
• 點查詢(point query)，如name = “hustcat”；
• 範圍查詢(range query)，如 20 < age < 30。

不同的劃分技術，對這些存取類型的效率是不同的：

 輪轉法適合順序掃描關係，對點查詢和範圍查詢的處理較複雜。

 散列劃分特別適合點查詢，速度最快。

 範圍劃分對點查詢、範圍查詢以及順序掃描都支持較好，所以適用性很廣。但是，這種方式存在一個問題——執行偏斜(execution skew)，也就是說某些範圍的元組較多，使得大量的I/O出現在某幾個磁盤。

1.3、查詢間並行(interquery parallism)

查詢間並行指的是不同的查詢或事務間並行的執行。這種形式的並行可以提高事務的吞吐量，然而，單個事務並不能執行得更快(即響應時間不能減少)。查詢間的並行主要用於擴展事務處理系統，在單位時間內能夠處理更多的事務。

查詢間並行是數據庫系統最易實現的一種並行，在共享內存的並行系統(如SMP)中尤其這樣。為單處理器設計的數據庫系統可以不用修改，或者很少修改就能用到共享內存的體系結構。

在共享磁盤和無共享的體系結構中，實現查詢間並行要更復雜一些。各個處理需要協調來進行封鎖、日誌操作等等，這就需要處理器之間的傳遞消息。並行數據庫系統必須保證兩個處理器不會同時更新同一數據。而且，處理器訪問數據時，系統必須保證處理器緩存的數據是最新的數據，即緩存一致性問題。

1.4、查詢內並行(intraquery parallism)

查詢內並行是指單個查詢要在多個處理器和磁盤上同時進行。為了理解，來考慮一個對某關係進行排序的查詢。假設關係已經基於某個屬性進行了範圍劃分，存儲於多個磁盤上，並且劃分是基於劃分屬性的。則排序操作可以如下進行：對每個分區並行的排序，然後將各個已經有序的分區合併到一起。

單個查詢的執行可以有兩種並行方式：

(1) 操作內並行(Intraoperation parallism)：通過並行的執行每一個運算，如排序、選擇、連接等，來加快一個查詢的處理速度。

(2) 操作間並行(Interoperation parallism)：通過並行的執行一個查詢中的多個不同的運算，來加速度一個查詢的處理速度。

注意兩者間的區別，前者可以認為多個處理器同時執行一個運算，而後者是多個處理器同時執行不同的運算。

這兩種形式之間的並行是互相補充的，並且可以同時存在於一個查詢中。通常由於一個查詢中的運算數目相對於元組數目是較小的，所以當並行度增加時，第一種方式取得的效果更顯著。

2、MySQL的分區(partion)

2.1、MySQL分區概述

在MySQL中，InnoDB存儲引擎長期支持表空間的概念，並且MySQL服務器甚至在分區引入之前，就能配置為存儲不同的數據庫使用不同的物理路徑。分區(partion)更進一步，它允許你通過設置各種規則將一個表的各個分區跨文件系統存儲。實際上，不同位置的不同表分區是作為一個單獨的表來存儲的。用戶所選擇的、實現數據分割的規則被稱為分區函數(partioning function)，這在MySQL中它可以是模數，或者是簡單的匹配一個連續的數值區間或數值列表，或者是一個內部HASH函數，或一個線性HASH函數。

最常見是的水平分區(horizontal partitioning)，也就是將表的不同的元組分配到不同的物理分區上。

下面的例子給出了怎樣創建一個通過HASH分成6個分區、使用InnoDB存儲引擎的表：

CREATE TABLE t1 (id INT, amount DECIMAL(7,2), tr_date DATE)
 ENGINE=INNODB
 PARTITION BY HASH( MONTH(tr_date) )
 PARTITIONS 6;

注：分區必須對一個表的所有數據和索引；不能只對數據分區而不對索引分區，反之亦然，同時也不能只對表的一部分進行分區。

MySQL的分區，會給系統帶來以下一些優點：

 與單個磁盤或文件系統分區相比，單個表可以存儲更多的數據。

 對於那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區，來很方便地實現。

 對於帶Where的條件查詢語句，可以得到更大的優化；只需要查詢某些分區，而不用掃描全部分區。

注：實際上，分區不論是對I/O並行，還是查詢內並行，都有著重要的影響。只不過MySQL在這方面做得還不夠多(不過，正在改進)，而Oracle對於查詢內並行，做了很多工作。

2.2、分區類型

MySQL 可用的分區類型包括：

• RANGE分區(portioning)：根據列值所屬的範圍區間，將元組分配到各個分區。
• LIST分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。
• HASH分區：根據用戶定義的函數的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。
• KEY分區：類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。

2.2.1、範圍分區

範圍分區是通過計算表達式的值所屬的範圍區間，對元組進行分區。這些區間要求連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。在下面的幾個例子中，假定你創建了一個如下的一個表，該表保存有20家音像店的職員記錄，這20家音像店的編號從1到20。

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
);

你可以根據需要對該表進行各種分區，比如，你可以通過store_id來進行分區：

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
 PARTITION p0 VALUES LESS THAN (6),
 PARTITION p1 VALUES LESS THAN (11),
 PARTITION p2 VALUES LESS THAN (16),
 PARTITION p3 VALUES LESS THAN (21)
);

很容易確定數據(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)被插入分區p2；但是，如果一條數據的store_id = 21，會怎麼樣呢？由於沒有規則處理大於20的情況，所以服務器會報錯。你可以通過如下方式來處理這種情況：

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
 PARTITION p0 VALUES LESS THAN (6),
 PARTITION p1 VALUES LESS THAN (11),
 PARTITION p2 VALUES LESS THAN (16),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE 表示最大的可能的整數值。現在，store_id 列值大於或等於16（定義了的最高值）的所有行都將保存在分區p3中。當商店數已經增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區

RANGE分區在如下場合特別有用：

(1) 當需要刪除“舊的”數據時。 在上面的例子中，只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經停止工作的僱員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個DELETE查詢要有效得多。

(2) 經常依賴於分區屬性進行查詢。例如，當執行一個如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時，MySQL可以很迅速地確定只有分區p2需要掃描，這是因為餘下的分區不可能包含有符合該WHERE子句的任何記錄。

範圍分區的缺點就是容易出現執行偏斜，這會影響系統性能。

2.2.2、HASH分區

HASH分區主要用來確保數據在預先確定數目的分區中平均分佈。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。

比如：

如果沒有PARTITIONS語句，默認分區數為1。但是，PARTITIONS後面沒有數字，系統會報錯。相對於範圍分區，HASH分區更可能保證數據均衡分佈。

2.2.3、子分區(Subpartitioning)

子分區，也叫做複合分區(composite partitioning)，是對分區表的每個分區的進一步分割。例如：

CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) )
 SUBPARTITIONS 2 (
 PARTITION p0 VALUES LESS THAN (1990),
 PARTITION p1 VALUES LESS THAN (2000),
 PARTITION p2 VALUES LESS THAN MAXVALUE
 );

表ts 有3個RANGE分區。這3個分區中的每一個分區——p0, p1, 和 p2 ——又被進一步分成了2個子分區。實際上，整個表被分成了3 * 2 = 6個分區。但是，由於PARTITION BY RANGE子句的作用，這些分區的頭2個只保存“purchased”列中值小於1990的那些記錄。

為了對個別的子分區指定選項，使用SUBPARTITION 子句來明確定義子分區也是可能的。例如，創建在前面例子中給出的同一個表的、一個更加詳細的方式如下：

CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
 PARTITION p0 VALUES LESS THAN (1990) (
 SUBPARTITION s0,
 SUBPARTITION s1
 ),
 PARTITION p1 VALUES LESS THAN (2000) (
 SUBPARTITION s2,
 SUBPARTITION s3
 ),
 PARTITION p2 VALUES LESS THAN MAXVALUE (
 SUBPARTITION s4,
 SUBPARTITION s5
 )
 );

一些注意點：

• 每個分區的子分區數必須相同；
• 如果在一個分區表上的任何分區上使用SUBPARTITION 來明確定義任何子分區，那麼就必須定義所有的子分區；
• 每個SUBPARTITION子句必須包含一個子分區的名稱；
• 從MySQL 5.1.8開始，子分區的名稱在整個表中都必須唯一。

子分區可以用於特別大的表，在多個磁盤間分配數據和索引。假設有6個磁盤，分別為/disk0， /disk1， /disk2等，對於如下例子：

CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
 PARTITION p0 VALUES LESS THAN (1990) (
 SUBPARTITION s0
 DATA DIRECTORY = '/disk0/data'
 INDEX DIRECTORY = '/disk0/idx',
 SUBPARTITION s1
 DATA DIRECTORY = '/disk1/data'
 INDEX DIRECTORY = '/disk1/idx'
 ),
 PARTITION p1 VALUES LESS THAN (2000) (
 SUBPARTITION s2
 DATA DIRECTORY = '/disk2/data'
 INDEX DIRECTORY = '/disk2/idx',
 SUBPARTITION s3
 DATA DIRECTORY = '/disk3/data'
 INDEX DIRECTORY = '/disk3/idx'
 ),
 PARTITION p2 VALUES LESS THAN MAXVALUE (
 SUBPARTITION s4
 DATA DIRECTORY = '/disk4/data'
 INDEX DIRECTORY = '/disk4/idx',
 SUBPARTITION s5
 DATA DIRECTORY = '/disk5/data'
 INDEX DIRECTORY = '/disk5/idx'
 )
 );

後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注一下~

"

概述

先明確一點，Oracle是多進程，mysql是單進程多線程，然後我們再繼續今天學習的內容--mysql並行數據庫與分區。

---

1、並行數據庫

1.1、並行數據庫的體系結構

我們一般對數據庫系統性能的度量主要有兩種方式：

(1)吞吐量(Throughput)，在給定的時間段裡所能完成的任務數量；

(2)響應時間(Response time)，單個任務從提交到完成所需要的時間。對於處理大量小事務的系統，通過並行地處理許多事務可以提高它的吞吐量。對於處理大事務的系統，通過並行的執行事務的子任務，可以縮短系統響應時間。

並行機有三種基本的體系結構，相應的，並行數據庫的體系結構也可以大概分為三類：

 共享內存(share memeory)：所有處理器共享一個公共的存儲器；

 共享磁盤(share disk)：所有處理器共享公共的磁盤；這種結構有時又叫做集群(cluster)；

 無共享(share nothing)：所有處理器既不共享內存，也不共享磁盤。

如圖所示：

1.1.1、 共享內存

該結構包括多個處理器、一個全局共享的內存（主存儲器）和多個磁盤存儲，各個處理器通過高速通訊網絡（Interconnection Network）與共享內存連接，並均可直接訪問系統中的一個、多個或全部的磁盤存儲，在系統中，所有的內存和磁盤存儲均由多個處理器共享。

這種結構的優點在於，處理器之間的通信效率極高，訪問內存的速度要比消息通信機制要快很多。這種結構的缺點在於，處理器的規模不能超過32個或者64個，因為總線或互邊網絡是由所有的處理器共享，它會變成瓶頸。當處理器數量到達某一個點時，再增加處理器已經沒有什麼好處。

共享內存結構通常在每個處理器上有很大的高速緩存，從而減少對內存的訪問。但是，這些高速緩存必須保持一致，也就是緩存一致性(cache-coherency)的問題。

1.1.2、 共享磁盤

該結構由多個具有獨立內存（主存儲器）的處理器和多個磁盤存儲構成，各個處理器相互之間沒有任何直接的信息和數據的交換，多個處理器和磁盤存儲由高速通信網絡連接，每個處理器都可以讀寫全部的磁盤存儲。

共享磁盤與共享內存結構相比，有以下一些優點：(1)每個處理器都有自己的存儲器，存儲總線不再是瓶頸；(2)以一種較經濟的方式提供了容錯性(fault tolerence)，如果一個處器發生故障，其它處理器可以代替工作。

該結構的主要問題不是在於可擴展性問題，雖然存儲總線不是瓶頸，但是，與磁盤之間的連接又成了瓶頸。

1.1.3、 無共享

該結構由多個完全獨立的處理節點構成，每個處理節點具有自己獨立的處理器、獨立的內存（主存儲器）和獨立的磁盤存儲，多個處理節點在處理器級由高速通信網絡連接，系統中的各個處理器使用自己的內存獨立地處理自己的數據。

這 種結構中，每一個處理節點就是一個小型的數據庫系統，多個節點一起構成整個的分佈式的並行數據庫系統。由於每個處理器使用自己的資源處理自己的數據，不存在內存和磁盤的爭用，提高的整體性能。另外這種結構具有優良的可擴展性——只需增加額外的處理節點，就可以以接近線性的比例增加系統的處理能力。

這種結構中，由於數據是各個處理器私有的，因此係統中數據的分佈就需要特殊的處理，以儘量保證系統中各個節點的負載基本平衡，但在目前的數據庫領域，這個數據分佈問題已經有比較合理的解決方案。

由於數據是分佈在各個處理節點上的，因此，使用這種結構的並行數據庫系統，在擴展時不可避免地會導致數據在整個系統範圍內的重分佈（Re-Distribution）問題。

Shared-Nothing結構的典型代表是Teradata(並行數據庫的先驅)，值得一提的是，MySQL NDB Cluster也使用了這種結構。

1.2、I/O並行(I/O Parallelism)

I/O並行的最簡單形式是通過對關係劃分，放置到多個磁盤上來縮減從磁盤讀取關係的時間。並行數據庫中數據劃分最通用的形式是水平劃分(horizontal portioning)，一個關係中的元組被劃分到多個磁盤。

1.2.1、常用劃分技術

假定將數據劃分到n個磁盤D0，D1，…，Dn中。

(1) 輪轉法(round-bin)。對關係順序掃描，將第i個元組存儲到標號為Di%n的磁盤上；該方式保證了元組在多個磁盤上均勻分佈。

(2) 散列劃分(hash partion)。選定一個值域為{0, 1, …,n-1}的散列函數，對關係中的元組基於劃分屬性進行散列。如果散列函數返回i，則將其存儲到第i個磁盤。

(3) 範圍劃分(range partion)。

由於將關係存儲到多個磁盤，讀寫時能同時進行，劃分(partion)能大大提高系統的讀寫性能。數據的存取可以分為以下幾類：

• 掃描整個關係；
• 點查詢(point query)，如name = “hustcat”；
• 範圍查詢(range query)，如 20 < age < 30。

不同的劃分技術，對這些存取類型的效率是不同的：

 輪轉法適合順序掃描關係，對點查詢和範圍查詢的處理較複雜。

 散列劃分特別適合點查詢，速度最快。

 範圍劃分對點查詢、範圍查詢以及順序掃描都支持較好，所以適用性很廣。但是，這種方式存在一個問題——執行偏斜(execution skew)，也就是說某些範圍的元組較多，使得大量的I/O出現在某幾個磁盤。

1.3、查詢間並行(interquery parallism)

查詢間並行指的是不同的查詢或事務間並行的執行。這種形式的並行可以提高事務的吞吐量，然而，單個事務並不能執行得更快(即響應時間不能減少)。查詢間的並行主要用於擴展事務處理系統，在單位時間內能夠處理更多的事務。

查詢間並行是數據庫系統最易實現的一種並行，在共享內存的並行系統(如SMP)中尤其這樣。為單處理器設計的數據庫系統可以不用修改，或者很少修改就能用到共享內存的體系結構。

在共享磁盤和無共享的體系結構中，實現查詢間並行要更復雜一些。各個處理需要協調來進行封鎖、日誌操作等等，這就需要處理器之間的傳遞消息。並行數據庫系統必須保證兩個處理器不會同時更新同一數據。而且，處理器訪問數據時，系統必須保證處理器緩存的數據是最新的數據，即緩存一致性問題。

1.4、查詢內並行(intraquery parallism)

查詢內並行是指單個查詢要在多個處理器和磁盤上同時進行。為了理解，來考慮一個對某關係進行排序的查詢。假設關係已經基於某個屬性進行了範圍劃分，存儲於多個磁盤上，並且劃分是基於劃分屬性的。則排序操作可以如下進行：對每個分區並行的排序，然後將各個已經有序的分區合併到一起。

單個查詢的執行可以有兩種並行方式：

(1) 操作內並行(Intraoperation parallism)：通過並行的執行每一個運算，如排序、選擇、連接等，來加快一個查詢的處理速度。

(2) 操作間並行(Interoperation parallism)：通過並行的執行一個查詢中的多個不同的運算，來加速度一個查詢的處理速度。

注意兩者間的區別，前者可以認為多個處理器同時執行一個運算，而後者是多個處理器同時執行不同的運算。

這兩種形式之間的並行是互相補充的，並且可以同時存在於一個查詢中。通常由於一個查詢中的運算數目相對於元組數目是較小的，所以當並行度增加時，第一種方式取得的效果更顯著。

---

2、MySQL的分區(partion)

2.1、MySQL分區概述

在MySQL中，InnoDB存儲引擎長期支持表空間的概念，並且MySQL服務器甚至在分區引入之前，就能配置為存儲不同的數據庫使用不同的物理路徑。分區(partion)更進一步，它允許你通過設置各種規則將一個表的各個分區跨文件系統存儲。實際上，不同位置的不同表分區是作為一個單獨的表來存儲的。用戶所選擇的、實現數據分割的規則被稱為分區函數(partioning function)，這在MySQL中它可以是模數，或者是簡單的匹配一個連續的數值區間或數值列表，或者是一個內部HASH函數，或一個線性HASH函數。

最常見是的水平分區(horizontal partitioning)，也就是將表的不同的元組分配到不同的物理分區上。

下面的例子給出了怎樣創建一個通過HASH分成6個分區、使用InnoDB存儲引擎的表：

CREATE TABLE t1 (id INT, amount DECIMAL(7,2), tr_date DATE)
 ENGINE=INNODB
 PARTITION BY HASH( MONTH(tr_date) )
 PARTITIONS 6;

注：分區必須對一個表的所有數據和索引；不能只對數據分區而不對索引分區，反之亦然，同時也不能只對表的一部分進行分區。

MySQL的分區，會給系統帶來以下一些優點：

 與單個磁盤或文件系統分區相比，單個表可以存儲更多的數據。

 對於那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區，來很方便地實現。

 對於帶Where的條件查詢語句，可以得到更大的優化；只需要查詢某些分區，而不用掃描全部分區。

注：實際上，分區不論是對I/O並行，還是查詢內並行，都有著重要的影響。只不過MySQL在這方面做得還不夠多(不過，正在改進)，而Oracle對於查詢內並行，做了很多工作。

2.2、分區類型

MySQL 可用的分區類型包括：

• RANGE分區(portioning)：根據列值所屬的範圍區間，將元組分配到各個分區。
• LIST分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。
• HASH分區：根據用戶定義的函數的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。
• KEY分區：類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。

2.2.1、範圍分區

範圍分區是通過計算表達式的值所屬的範圍區間，對元組進行分區。這些區間要求連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。在下面的幾個例子中，假定你創建了一個如下的一個表，該表保存有20家音像店的職員記錄，這20家音像店的編號從1到20。

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
);

你可以根據需要對該表進行各種分區，比如，你可以通過store_id來進行分區：

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
 PARTITION p0 VALUES LESS THAN (6),
 PARTITION p1 VALUES LESS THAN (11),
 PARTITION p2 VALUES LESS THAN (16),
 PARTITION p3 VALUES LESS THAN (21)
);

很容易確定數據(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)被插入分區p2；但是，如果一條數據的store_id = 21，會怎麼樣呢？由於沒有規則處理大於20的情況，所以服務器會報錯。你可以通過如下方式來處理這種情況：

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
 PARTITION p0 VALUES LESS THAN (6),
 PARTITION p1 VALUES LESS THAN (11),
 PARTITION p2 VALUES LESS THAN (16),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE 表示最大的可能的整數值。現在，store_id 列值大於或等於16（定義了的最高值）的所有行都將保存在分區p3中。當商店數已經增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區

RANGE分區在如下場合特別有用：

(1) 當需要刪除“舊的”數據時。 在上面的例子中，只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經停止工作的僱員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個DELETE查詢要有效得多。

(2) 經常依賴於分區屬性進行查詢。例如，當執行一個如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時，MySQL可以很迅速地確定只有分區p2需要掃描，這是因為餘下的分區不可能包含有符合該WHERE子句的任何記錄。

範圍分區的缺點就是容易出現執行偏斜，這會影響系統性能。

2.2.2、HASH分區

HASH分區主要用來確保數據在預先確定數目的分區中平均分佈。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。

比如：

如果沒有PARTITIONS語句，默認分區數為1。但是，PARTITIONS後面沒有數字，系統會報錯。相對於範圍分區，HASH分區更可能保證數據均衡分佈。

2.2.3、子分區(Subpartitioning)

子分區，也叫做複合分區(composite partitioning)，是對分區表的每個分區的進一步分割。例如：

CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) )
 SUBPARTITIONS 2 (
 PARTITION p0 VALUES LESS THAN (1990),
 PARTITION p1 VALUES LESS THAN (2000),
 PARTITION p2 VALUES LESS THAN MAXVALUE
 );

表ts 有3個RANGE分區。這3個分區中的每一個分區——p0, p1, 和 p2 ——又被進一步分成了2個子分區。實際上，整個表被分成了3 * 2 = 6個分區。但是，由於PARTITION BY RANGE子句的作用，這些分區的頭2個只保存“purchased”列中值小於1990的那些記錄。

為了對個別的子分區指定選項，使用SUBPARTITION 子句來明確定義子分區也是可能的。例如，創建在前面例子中給出的同一個表的、一個更加詳細的方式如下：

CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
 PARTITION p0 VALUES LESS THAN (1990) (
 SUBPARTITION s0,
 SUBPARTITION s1
 ),
 PARTITION p1 VALUES LESS THAN (2000) (
 SUBPARTITION s2,
 SUBPARTITION s3
 ),
 PARTITION p2 VALUES LESS THAN MAXVALUE (
 SUBPARTITION s4,
 SUBPARTITION s5
 )
 );

一些注意點：

• 每個分區的子分區數必須相同；
• 如果在一個分區表上的任何分區上使用SUBPARTITION 來明確定義任何子分區，那麼就必須定義所有的子分區；
• 每個SUBPARTITION子句必須包含一個子分區的名稱；
• 從MySQL 5.1.8開始，子分區的名稱在整個表中都必須唯一。

子分區可以用於特別大的表，在多個磁盤間分配數據和索引。假設有6個磁盤，分別為/disk0， /disk1， /disk2等，對於如下例子：

CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
 PARTITION p0 VALUES LESS THAN (1990) (
 SUBPARTITION s0
 DATA DIRECTORY = '/disk0/data'
 INDEX DIRECTORY = '/disk0/idx',
 SUBPARTITION s1
 DATA DIRECTORY = '/disk1/data'
 INDEX DIRECTORY = '/disk1/idx'
 ),
 PARTITION p1 VALUES LESS THAN (2000) (
 SUBPARTITION s2
 DATA DIRECTORY = '/disk2/data'
 INDEX DIRECTORY = '/disk2/idx',
 SUBPARTITION s3
 DATA DIRECTORY = '/disk3/data'
 INDEX DIRECTORY = '/disk3/idx'
 ),
 PARTITION p2 VALUES LESS THAN MAXVALUE (
 SUBPARTITION s4
 DATA DIRECTORY = '/disk4/data'
 INDEX DIRECTORY = '/disk4/idx',
 SUBPARTITION s5
 DATA DIRECTORY = '/disk5/data'
 INDEX DIRECTORY = '/disk5/idx'
 )
 );

---

後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注一下~

"