分區(qū)概念

分區(qū)又把這個(gè)概念推進(jìn)了一步，它允許根據(jù)可以設(shè)置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分。實(shí)際上，表的不同部分在不同的位置被存儲為單獨(dú)的表。用戶所選擇的、實(shí)現(xiàn)數(shù)據(jù)分割的規(guī)則被稱為分區(qū)函數(shù)，這在MySQL中它可以是模數(shù)，或者是簡單的匹配一個(gè)連續(xù)的數(shù)值區(qū)間或數(shù)值列表，或者是一個(gè)內(nèi)部HASH函數(shù)，或一個(gè)線性HASH函數(shù)。函數(shù)根據(jù)用戶指定的分區(qū)類型來選擇，把用戶提供的表達(dá)式的值作為參數(shù)。該表達(dá)式可以是一個(gè)整數(shù)列值，或一個(gè)作用在一個(gè)或多個(gè)列值上并返回一個(gè)整數(shù)的函數(shù)。這個(gè)表達(dá)式的值傳遞給分區(qū)函數(shù)，分區(qū)函數(shù)返回一個(gè)表示那個(gè)特定記錄應(yīng)該保存在哪個(gè)分區(qū)的序號。這個(gè)函數(shù)不能是常數(shù)，也不能是任意數(shù)。它不能包含任何查詢，但是實(shí)際上可以使用MySQL中任何可用的SQL表達(dá)式，只要該表達(dá)式返回一個(gè)小于MAXVALUE（最大可能的正整數(shù)）的正數(shù)值。

使用以下命令查看當(dāng)前的Mysql版本是否支持分區(qū)

SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_partition_engine | YES   |
+-----------------------+-------+
1 row in set (0.00 sec)

注：如果不顯示為yes,或者沒有顯示，那就表示可能當(dāng)前版本不支持分區(qū)

Mysql分區(qū)種類

• range分區(qū)
• list分區(qū)
• hash分區(qū)
• key分區(qū)
• 子分區(qū)

分區(qū)與存儲引擎

對于分區(qū)，可以使用mysql服務(wù)器所支持的任何存儲引擎，但同一個(gè)分區(qū)表的所有分區(qū)必須使用同一個(gè)存儲引擎，

例如，不能對一個(gè)分區(qū)使用MyISAM，而對另一個(gè)使用InnoDB。

要為某個(gè)分區(qū)表配置一個(gè)存儲引擎，必須且只能使用[STORAGE] ENGINE 選項(xiàng)。。但是，必須記住[STORAGE] ENGINE（和其他的表選項(xiàng)）必須列在用在CREATE TABLE語句中的其他任何分區(qū)選項(xiàng)之前。下面的例子給出了怎樣創(chuàng)建一個(gè)通過HASH分成6個(gè)分區(qū)、使用InnoDB存儲引擎的表：

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

分區(qū)的一些優(yōu)點(diǎn)包括：

•  與單個(gè)磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。
•  對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通?？梢酝ㄟ^刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個(gè)新的分區(qū)，來很方便地實(shí)現(xiàn)。
• 一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個(gè)給定WHERE 語句的數(shù)據(jù)可以只保存在一個(gè)或多個(gè)分區(qū)內(nèi)，這樣在查找時(shí)就不用查找其他剩余的分區(qū)。因?yàn)榉謪^(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時(shí)還不曾這么做時(shí)，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。 
• 涉及到例如SUM() 和 COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。這種查詢的一個(gè)簡單例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”， 這意味著該查詢可以在每個(gè)分區(qū)上同時(shí)進(jìn)行，最終結(jié)果只需通過總計(jì)所有分區(qū)得到的結(jié)果。 
•    通過跨多個(gè)磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。

RANGE分區(qū)

按照RANGE分區(qū)的表是通過如下一種方式進(jìn)行分區(qū)的，每個(gè)分區(qū)包含那些分區(qū)表達(dá)式的值位于一個(gè)給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來進(jìn)行定義。在下面的幾個(gè)例子中，假定你創(chuàng)建了一個(gè)如下的一個(gè)表，該表保存有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
)；

根據(jù)你的需要，這個(gè)表可以有多種方式來按照區(qū)間進(jìn)行分區(qū)。一種方式是使用store_id 列。例如，你可能決定通過添加一個(gè)PARTITION BY RANGE子句把這個(gè)表分割成4個(gè)區(qū)間，如下所示:

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

按照這種分區(qū)方案，在商店1到5工作的雇員相對應(yīng)的所有行被保存在分區(qū)P0中，商店6到10的雇員保存在P1中，依次類推。注意，每個(gè)分區(qū)都是按順序進(jìn)行定義，從最低到最高。這是PARTITION BY RANGE語法的要求；在這點(diǎn)上，它類似于C或Java中的“switch ... case”語句。

對于包含數(shù)據(jù)(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的一個(gè)新行，可以很容易地確定它將插入到p2分區(qū)中，但是如果增加了一個(gè)編號為第21的商店，將會發(fā)生什么呢？在這種方案下，由于沒有規(guī)則把store_id大于20的商店包含在內(nèi)，服務(wù)器將不知道把該行保存在何處，將會導(dǎo)致錯(cuò)誤。 要避免這種錯(cuò)誤，可以通過在CREATE TABLE語句中使用一個(gè)“catchall” VALUES LESS THAN子句，該子句提供給所有大于明確指定的最高值的值：

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 表示最大的可能的整數(shù)值?，F(xiàn)在，store_id 列值大于或等于16（定義了的最高值）的所有行都將保存在分區(qū)p3中。在將來的某個(gè)時(shí)候，當(dāng)商店數(shù)已經(jīng)增長到25, 30,或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區(qū)

在幾乎一樣的結(jié)構(gòu)中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code 列值的連續(xù)區(qū)間。例如——假定2位數(shù)字的工作代碼用來表示普通（店內(nèi)的）工人，三個(gè)數(shù)字代碼表示辦公室和支持人員，四個(gè)數(shù)字代碼表示管理層，你可以使用下面的語句創(chuàng)建該分區(qū)表：

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

在這個(gè)例子中, 店內(nèi)工人相關(guān)的所有行將保存在分區(qū)p0中，辦公室和支持人員相關(guān)的所有行保存在分區(qū)p1中，管理層相關(guān)的所有行保存在分區(qū)p2中。

在VALUES LESS THAN 子句中使用一個(gè)表達(dá)式也是可能的。這里最值得注意的限制是MySQL必須能夠計(jì)算表達(dá)式的返回值作為LESS THAN (<)比較的一部分；因此，表達(dá)式的值不能為NULL。由于這個(gè)原因，雇員表的hired, separated, job_code,和store_id列已經(jīng)被定義為非空（NOT NULL）。

除了可以根據(jù)商店編號分割表數(shù)據(jù)外，你還可以使用一個(gè)基于兩個(gè)DATE （日期）中的一個(gè)的表達(dá)式來分割表數(shù)據(jù)。例如，假定你想基于每個(gè)雇員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實(shí)現(xiàn)這種分區(qū)模式的CREATE TABLE語句的一個(gè)例子如下所示：

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

在這個(gè)方案中，在1991年前雇傭的所有雇員的記錄保存在分區(qū)p0中，1991年到1995年期間雇傭的所有雇員的記錄保存在分區(qū)p1中，1996年到2000年期間雇傭的所有雇員的記錄保存在分區(qū)p2中，2000年后雇傭的所有工人的信息保存在p3中。

RANGE分區(qū)在如下場合特別有用：

• 當(dāng)需要?jiǎng)h除“舊的”數(shù)據(jù)時(shí)。如果你使用上面最近的那個(gè)例子給出的分區(qū)方案，你只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經(jīng)停止工作的雇員相對應(yīng)的所有行。對于有大量行的表，這比運(yùn)行一個(gè)如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個(gè)DELETE查詢要有效得多。

• 想要使用一個(gè)包含有日期或時(shí)間值，或包含有從一些其他級數(shù)開始增長的值的列。

•   經(jīng)常運(yùn)行直接依賴于用于分割表的列的查詢。例如，當(dāng)執(zhí)行一個(gè)如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時(shí)，MySQL可以很迅速地確定只有分區(qū)p2需要掃描，這是因?yàn)橛嘞碌姆謪^(qū)不可能包含有符合該WHERE子句的任何記錄。

LIST分區(qū)

MySQL中的LIST分區(qū)在很多方面類似于RANGE分區(qū)。和按照RANGE分區(qū)一樣，每個(gè)分區(qū)必須明確定義。它們的主要區(qū)別在于，LIST分區(qū)中每個(gè)分區(qū)的定義和選擇是基于某列的值從屬于一個(gè)值列表集中的一個(gè)值，而RANGE分區(qū)是從屬于一個(gè)連續(xù)區(qū)間值的集合。LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實(shí)現(xiàn)，其中“expr” 是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式，然后通過“VALUES IN (value_list)”的方式來定義每個(gè)分區(qū)，其中“value_list”是一個(gè)通過逗號分隔的整數(shù)列表。

對于下面給出的例子，我們假定將要被分區(qū)的表的基本定義是通過下面的“CREATE TABLE”語句提供的：

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個(gè)音像店，分布在4個(gè)有經(jīng)銷權(quán)的地區(qū)，如下表所示：

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

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

這使得在表中增加或刪除指定地區(qū)的雇員記錄變得容易起來。例如，假定西區(qū)的所有音像店都賣給了其他公司。那么與在西區(qū)音像店工作雇員相關(guān)的所有記錄（行）可以使用查詢“ALTER TABLE employees DROP PARTITION pWest；”來進(jìn)行刪除，它與具有同樣作用的DELETE（刪除）查詢“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。

要點(diǎn)：如果試圖插入列值（或分區(qū)表達(dá)式的返回值）不在分區(qū)值列表中的一行時(shí)，那么“INSERT”查詢將失敗并報(bào)錯(cuò)。例如，假定LIST分區(qū)的采用上面的方案，下面的查詢將失敗：

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

這是因?yàn)?ldquo;store_id”列值21不能在用于定義分區(qū)pNorth,pEast, pWest,或pCentral的值列表中找到。要重點(diǎn)注意的是，LIST分區(qū)沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。

HASH分區(qū)

HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中；而在HASH分區(qū)中，MySQL 自動(dòng)完成這些工作，你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。

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

例如，下面的語句創(chuàng)建了一個(gè)使用基于“store_id”列進(jìn)行 哈希處理的表，該表被分成了4個(gè)分區(qū)：

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；

如果沒有包括一個(gè) PARTITIONS子句，那么分區(qū)的數(shù)量將默認(rèn)為1。 例外： 對于 NDB Cluster（簇）表，默認(rèn)的分區(qū)數(shù)量將與 簇?cái)?shù)據(jù)節(jié)點(diǎn)的數(shù)量相同，這種修正可能是考慮任何MAX_ROWS 設(shè)置，以便確保所有的行都能合適地插入到分區(qū)中。

如果在關(guān)鍵字“PARTITIONS”后面沒有加上分區(qū)的數(shù)量，將會出現(xiàn)語法錯(cuò)誤。

“expr”還可以是一個(gè)返回一個(gè)整數(shù)的SQL表達(dá)式。例如，也許你想基于雇用雇員的年份來進(jìn)行分區(qū)。這可以通過下面的語句來實(shí)現(xiàn)：

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(YEAR(hired))
PARTITIONS 4；

“expr”還可以是MySQL 中有效的任何函數(shù)或其他表達(dá)式，只要它們返回一個(gè)既非常數(shù)、也非隨機(jī)數(shù)的整數(shù)。（換句話說，它既是變化的但又是確定的）。但是應(yīng)當(dāng)記住，每當(dāng)插入或更新（或者可能刪除）一行，這個(gè)表達(dá)式都要計(jì)算一次；這意味著非常復(fù)雜的表達(dá)式可能會引起性能問題，尤其是在執(zhí)行同時(shí)影響大量行的運(yùn)算（例如批量插入）的時(shí)候。

最有效率的哈希函數(shù)是只對單個(gè)表列進(jìn)行計(jì)算，并且它的值隨列值進(jìn)行一致地增大或減小，因?yàn)檫@考慮了在分區(qū)范圍上的“修剪”。也就是說，表達(dá)式值和它所基于的列的值變化越接近，MySQL就可以越有效地使用該表達(dá)式來進(jìn)行HASH分區(qū)。

LINEAR HASH分區(qū)

MySQL還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個(gè)線性的2的冪（powers-of-two）運(yùn)算法則，而常規(guī) 哈希使用的是求哈希函數(shù)值的模數(shù)。

線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于，在“PARTITION BY” 子句中添加“LINEAR”關(guān)鍵字，如下面所示：

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；

KEY分區(qū)

按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)，除了HASH分區(qū)使用的用戶定義的表達(dá)式，而KEY分區(qū)的 哈希函數(shù)是由MySQL 服務(wù)器提供。MySQL 簇（Cluster）使用函數(shù)MD5()來實(shí)現(xiàn)KEY分區(qū)；對于使用其他存儲引擎的表，服務(wù)器使用其自己內(nèi)部的 哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運(yùn)算法則。

“CREATE TABLE ... PARTITION BY KEY”的語法規(guī)則類似于創(chuàng)建一個(gè)通過HASH分區(qū)的表的規(guī)則。它們唯一的區(qū)別在于使用的關(guān)鍵字是KEY而不是HASH，并且KEY分區(qū)只采用一個(gè)或多個(gè)列名的一個(gè)列表。

通過線性KEY分割一個(gè)表也是可能的。下面是一個(gè)簡單的例子：

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

在 KEY分區(qū)中使用關(guān)鍵字 LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號是通過 2的冪（ powers-of-two）算法得到，而不是通過模數(shù)算法。

子分區(qū)

子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割。例如，考慮下面的CREATE TABLE 語句：

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個(gè) RANGE分區(qū)。這3個(gè)分區(qū)中的每一個(gè)分區(qū)—— p0 , p1, 和 p2 ——又被進(jìn)一步分成了 2個(gè)子分區(qū)。實(shí)際上，整個(gè)表被分成了 3 * 2 = 6個(gè)分區(qū)。但是，由于PARTITION BY RANGE子句的作用，這些分區(qū)的頭2個(gè)只保存“purchased”列中值小于1990的那些記錄。

MySQL分區(qū)處理NULL值的方式

MySQL 中的分區(qū)在禁止空值（NULL）上沒有進(jìn)行處理，無論它是一個(gè)列值還是一個(gè)用戶定義表達(dá)式的值。一般而言，在這種情況下MySQL 把NULL視為0。如果你希望回避這種做法，你應(yīng)該在設(shè)計(jì)表時(shí)不允許空值；最可能的方法是，通過聲明列“NOT NULL”來實(shí)現(xiàn)這一點(diǎn)。

在本節(jié)中，我們提供了一些例子，來說明當(dāng)決定一個(gè)行應(yīng)該保存到哪個(gè)分區(qū)時(shí)，MySQL 是如何處理NULL值的。

如果插入一行到按照RANGE或LIST分區(qū)的表，該行用來確定分區(qū)的列值為NULL，分區(qū)將把該NULL值視為0。

到此這篇關(guān)于一文了解Mysql分區(qū)的使用的文章就介紹到這了,更多相關(guān)Mysql分區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論