MySQL表的四種分區類型(Partition)

一、什麼是表分區

通俗地講表分區是将一大表，根據條件分割成若幹個小表。mysql5.1開始支援資料表分區了。如：某使用者表的記錄超過了600萬條，那麼就可以根據入庫日期将表分區，也可以根據所在地将表分區。當然也可根據其他的條件分區。

二、為什麼要對表進行分區

為了改善大型表以及具有各種通路模式的表的可伸縮性，可管理性和提高資料庫效率。

分區的一些優點包括：

與單個磁盤或檔案系統分區相比，可以存儲更多的資料。
對于那些已經失去儲存意義的資料，通常可以通過删除與那些資料有關的分區，很容易地删除那些資料。相反地，在某些情況下，添加新資料的過程又可以通過為那些新資料專門增加一個新的分區，來很友善地實作。通常和分區有關的其他優點包括下面列出的這些。MySQL分區中的這些功能目前還沒有實作，但是在我們的優先級清單中，具有高的優先級；我們希望在5.1的生産版本中，能包括這些功能。
一些查詢可以得到極大的優化，這主要是借助于滿足一個給定WHERE語句的資料可以隻儲存在一個或多個分區内，這樣在查找時就不用查找其他剩餘的分區。因為分區可以在建立了分區表後進行修改，是以在第一次配置分區方案時還不曾這麼做時，可以重新組織資料，來提高那些常用查詢的效率。
涉及到例如SUM()和COUNT()這樣聚合函數的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味着該查詢可以在每個分區上同時進行，最終結果隻需通過總計所有分區得到的結果。
通過跨多個磁盤來分散資料查詢，來獲得更大的查詢吞吐量。

三、分區類型

RANGE分區：基于屬于一個給定連續區間的列值，把多行配置設定給分區。

LIST分區：類似于按RANGE分區，差別在于LIST分區是基于列值比對一個離散值集合中的某個值來進行選擇。

HASH分區：基于使用者定義的表達式的傳回值來進行選擇的分區，該表達式使用将要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、産生非負整數值的任何表達式。

KEY分區：類似于按HASH分區，差別在于KEY分區隻支援計算一列或多列，且MySQL伺服器提供其自身的哈希函數。必須有一列或多列包含整數值。

RANGE分區

基于屬于一個給定連續區間的列值，把多行配置設定給分區。

這些區間要連續且不能互相重疊，使用VALUES LESS THAN操作符來進行定義。以下是執行個體。

Sql代碼：

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)
);

按照這種分區方案，在商店1到5工作的雇員相對應的所有行被儲存在分區P0中，商店6到10的雇員儲存在P1中，依次類推。注意，每個分區都是按順序進行定義，從最低到最高。這是PARTITION BY RANGE 文法的要求；在這點上，它類似于C或Java中的“switch … case”語句。對于包含資料(72, ‘Michael’, ‘Widenius’, ’1998-06-25′, NULL, 13)的一個新行，可以很容易地确定它将插入到p2分區中，但是如果增加了一個編号為第21的商店，将會發生什麼呢？在這種方案下，由于沒有規則把store_id大于20的商店包含在内，伺服器将不知道把該行儲存在何處，将會導緻錯誤。要避免這種錯誤，可以通過在CREATE TABLE語句中使用一個“catchall” VALUES LESS THAN子句，該子句提供給所有大于明确指定的最高值的值：

Sql代碼：

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,等等增加新的分區。在幾乎一樣的結構中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code 列值的連續區間。例如——假定2位數字的工作代碼用來表示普通（店内的）勞工，三個數字代碼表示辦公室和支援人員，四個數字代碼表示管理層，你可以使用下面的語句建立該分區表：

Sql代碼：

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 (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

在這個例子中, 店内勞工相關的所有行将儲存在分區p0中，辦公室和支援人員相關的所有行儲存在分區p1中，管理層相關的所有行儲存在分區p2中。在VALUES LESS THAN 子句中使用一個表達式也是可能的。這裡最值得注意的限制是MySQL 必須能夠計算表達式的傳回值作為LESS THAN (<)比較的一部分；是以，表達式的值不能為NULL 。由于這個原因，雇員表的hired, separated, job_code,和store_id列已經被定義為非空（NOT NULL）。除了可以根據商店編号分割表資料外，你還可以使用一個基于兩個DATE （日期）中的一個的表達式來分割表資料。例如，假定你想基于每個雇員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實作這種分區模式的CREATE TABLE 語句的一個例子如下所示：

Sql代碼：

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,
    store_id INT
)

PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

在這個方案中，在1991年前雇傭的所有雇員的記錄儲存在分區p0中，1991年到1995年期間雇傭的所有雇員的記錄儲存在分區p1中， 1996年到2000年期間雇傭的所有雇員的記錄儲存在分區p2中，2000年後雇傭的所有勞工的資訊儲存在p3中。

Range分區函數，查詢優化器隻能對YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()這些函數進行優化選擇

常見錯誤：

錯誤提示：#1503 内容來自www.itxxz.com

A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION

MySQL主鍵的限制，每一個分區表中的公式中的列，必須在主鍵/unique key 中包括

即：主鍵和唯一索引中都必須包含分區的列才可以。

RANGE分區在如下場合特别有用：1）、當需要删除一個分區上的“舊的”資料時,隻删除分區即可。如果你使用上面最近的那個例子給出的分區方案，你隻需簡單地使用”ALTER TABLE employees DROP PARTITION p0;”來删除所有在1991年前就已經停止工作的雇員相對應的所有行。對于有大量行的表，這比運作一個如”DELETE FROM employees WHERE YEAR (separated) <= 1990;”這樣的一個DELETE查詢要有效得多。 2）、想要使用一個包含有日期或時間值，或包含有從一些其他級數開始增長的值的列。3）、經常運作直接依賴于用于分割表的列的查詢。例如，當執行一個如”SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”這樣的查詢時，MySQL可以很迅速地确定隻有分區p2需要掃描，這是因為餘下的分區不可能包含有符合該WHERE子句的任何記錄。

注釋：這種優化還沒有在MySQL 5.1源程式中啟用，但是，有關工作正在進行中。

LIST分區

類似于按RANGE分區，差別在于LIST分區是基于列值比對一個離散值集合中的某個值來進行選擇。

LIST分區通過使用“PARTITION BY LIST(expr)”來實作，其中“expr”是某列值或一個基于某個列值、并傳回一個整數值的表達式，然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗号分隔的整數清單。注釋：在MySQL 5.1中，當使用LIST分區時，有可能隻能比對整數清單。

Sql代碼：

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,
    store_id INT
)；

假定有20個音像店，分布在4個有經銷權的地區，如下表所示：

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

地區商店ID 号

北區 3, 5, 6, 9, 17

東區 1, 2, 10, 11, 19, 20

西區 4, 12, 13, 14, 18

中心區 7, 8, 15, 16

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

要按照屬于同一個地區商店的行儲存在同一個分區中的方式來分割表，可以使用下面的“CREATE TABLE”語句：

Sql代碼：

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,
    store_id INT
)

PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

這使得在表中增加或删除指定地區的雇員記錄變得容易起來。例如，假定西區的所有音像店都賣給了其他公司。那麼與在西區音像店工作雇員相關的所有記錄（行）可以使用查詢“ALTER TABLE employees DROP PARTITION pWest；”來進行删除，它與具有同樣作用的DELETE（删除）查詢“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。【要點】：如果試圖插入列值（或分區表達式的傳回值）不在分區值清單中的一行時，那麼“INSERT”查詢将失敗并報錯。例如，假定LIST分區的采用上面的方案，下面的查詢将失敗：

Sql代碼：

INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

這是因為“store_id”列值21不能在用于定義分區pNorth, pEast, pWest,或pCentral的值清單中找到。要重點注意的是，LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在内的定義。将要比對的任何值都必須在值清單中找到。

LIST分區除了能和RANGE分區結合起來生成一個複合的子分區，與HASH和KEY分區結合起來生成複合的子分區也是可能的。

HASH分區

基于使用者定義的表達式的傳回值來進行選擇的分區，該表達式使用将要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、産生非負整數值的任何表達式。

要使用HASH分區來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個傳回一個整數的表達式。它可以僅僅是字段類型為MySQL整型的一列的名字。此外，你很可能需要在後面再添加一個“PARTITIONS num”子句，其中num是一個非負的整數，它表示表将要被分割成分區的數量。

Sql代碼：

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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

如果沒有包括一個PARTITIONS子句，那麼分區的數量将預設為1。例外：對于NDB Cluster（簇）表，預設的分區數量将與簇資料節點的數量相同，這種修正可能是考慮任何MAX_ROWS設定，以便確定所有的行都能合适地插入到分區中。

LINER HASH

MySQL還支援線性哈希功能，它與正常哈希的差別在于，線性哈希功能使用的一個線性的2的幂（powers-of-two）運算法則，而正常哈希使用的是求哈希函數值的模數。線性哈希分區和正常哈希分區在文法上的唯一差別在于，在“PARTITION BY”子句中添加“LINEAR”關鍵字。

Sql代碼：

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,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

假設一個表達式expr，當使用線性哈希功能時，記錄将要儲存到的分區是num 個分區中的分區N，其中N是根據下面的算法得到： 1. 找到下一個大于num.的、2的幂，我們把這個值稱為V ，它可以通過下面的公式得到： 2. V = POWER(2, CEILING(LOG(2, num))) （例如，假定num是13。那麼LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，則V = POWER(2,4), 即等于16）。 3. 設定 N = F(column_list) & (V – 1). 4. 當 N >= num: · 設定 V = CEIL(V / 2) · 設定 N = N & (V – 1) 例如，假設表t1，使用線性哈希分區且有4個分區，是通過下面的語句建立的： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6; 現在假設要插入兩行記錄到表t1中，其中一條記錄col3列值為’2003-04-14′，另一條記錄col3列值為’1998-10-19′。第一條記錄将要儲存到的分區确定如下： V = POWER(2, CEILING(LOG(2,7))) = 8 N = YEAR(’2003-04-14′) & (8 – 1) = 2003 & 7 = 3 (3 >= 6 為假（FALSE）: 記錄将被儲存到#3号分區中) 第二條記錄将要儲存到的分區序号計算如下： V = 8 N = YEAR(’1998-10-19′) & (8-1) = 1998 & 7 = 6 (6 >= 4 為真（TRUE）: 還需要附加的步驟) N = 6 & CEILING(5 / 2) = 6 & 3 = 2 (2 >= 4 為假（FALSE）: 記錄将被儲存到#2分區中) 按照線性哈希分區的優點在于增加、删除、合并和拆分分區将變得更加快捷，有利于處理含有極其大量（1000吉）資料的表。它的缺點在于，與使用正常HASH分區得到的資料分布相比，各個分區間資料的分布不大可能均衡。

KSY分區

類似于按HASH分區，差別在于KEY分區隻支援計算一列或多列，且MySQL伺服器提供其自身的哈希函數。必須有一列或多列包含整數值。

Sql代碼：

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

在KEY分區中使用關鍵字LINEAR和在HASH分區中使用具有同樣的作用，分區的編号是通過2的幂（powers-of-two）算法得到，而不是通過模數算法。

源位址：http://www.xprogrammer.com/1653.html

MySQL表的四種分區類型(Partition)

一、什麼是表分區

二、為什麼要對表進行分區

三、分區類型

RANGE分區

LIST分區

HASH分區

LINER HASH

KSY分區

繼續閱讀

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

雲計算面試題——檔案/權限/分區/軟體包管理

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案