MySQL的表分區(qū)

一、什么是表分區(qū) 通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個小表。 mysql 5.1開始支持 數(shù)據(jù) 表分區(qū)了。 如：某用戶表的記錄超過了600萬條，那么就可以根據(jù)入庫日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當然也可根據(jù)其他的條件分區(qū)。 二、為什么要對

一、什么是表分區(qū)
通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個小表。mysql5.1開始支持數(shù)據(jù)表分區(qū)了。
如：某用戶表的記錄超過了600萬條，那么就可以根據(jù)入庫日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當然也可根據(jù)其他的條件分區(qū)。

二、為什么要對表進行分區(qū)
為了改善大型表以及具有各種訪問模式的表的可伸縮性，可管理性和提高數(shù)據(jù)庫效率。

分區(qū)的一些優(yōu)點包括：
1）、與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。
2）、對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過刪除與那些數(shù)據(jù)有關的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。通常和分區(qū)有關的其他優(yōu)點包括下面列出的這些。MySQL分區(qū)中的這些功能目前還沒有實現(xiàn)，但是在我們的優(yōu)先級列表中，具有高的優(yōu)先級；我們希望在5.1的生產(chǎn)版本中，能包括這些功能。
3）、一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定WHERE語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進行修改，所以在第一次配置分區(qū)方案時還不曾這么做時，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。
4）、涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區(qū)上同時進行，最終結果只需通過總計所有分區(qū)得到的結果。
5）、通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。
三、分區(qū)類型

· RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
· LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。
· HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負整數(shù)值的任何表達式。
· KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

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

這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。以下是實例。

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT?NOT?NULL,
8. ????store_id?INT?NOT?NULL
9. )
10. partition?BY?RANGE?(store_id)?(
11. ????partition?p0?VALUES?LESS?THAN?(6),
12. ????partition?p1?VALUES?LESS?THAN?(11),
13. ????partition?p2?VALUES?LESS?THAN?(16),
14. ????partition?p3?VALUES?LESS?THAN?(21)
15. )；

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

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT?NOT?NULL,
8. ????store_id?INT?NOT?NULL
9. )
10. PARTITION?BY?RANGE?(store_id)?(
11. ????PARTITION?p0?VALUES?LESS?THAN?(6),
12. ????PARTITION?p1?VALUES?LESS?THAN?(11),
13. ????PARTITION?p2?VALUES?LESS?THAN?(16),
14. ????PARTITION?p3?VALUES?LESS?THAN?MAXVALUE
15. )；

MAXVALUE 表示最大的可能的整數(shù)值?，F(xiàn)在，store_id 列值大于或等于16（定義了的最高值）的所有行都將保存在分區(qū)p3中。在將來的某個時候，當商店數(shù)已經(jīng)增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區(qū)。
在幾乎一樣的結構中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code 列值的連續(xù)區(qū)間。例如——假定2位數(shù)字的工作代碼用來表示普通（店內(nèi)的）工人，三個數(shù)字代碼表示辦公室和支持人員，四個數(shù)字代碼表示管理層，你可以使用下面的語句創(chuàng)建該分區(qū)表：

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT?NOT?NULL,
8. ????store_id?INT?NOT?NULL
9. )
10. PARTITION?BY?RANGE?(job_code)?(
11. ????PARTITION?p0?VALUES?LESS?THAN?(100),
12. ????PARTITION?p1?VALUES?LESS?THAN?(1000),
13. ????PARTITION?p2?VALUES?LESS?THAN?(10000)
14. )；

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

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT,
8. ????store_id?INT
9. )
10. PARTITION?BY?RANGE?(YEAR(separated))?(
11. ????PARTITION?p0?VALUES?LESS?THAN?(1991),
12. ????PARTITION?p1?VALUES?LESS?THAN?(1996),
13. ????PARTITION?p2?VALUES?LESS?THAN?(2001),
14. ????PARTITION?p3?VALUES?LESS?THAN?MAXVALUE
15. )；

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

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

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

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

LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實現(xiàn)，其中“expr” 是某列值或一個基于某個列值、并返回一個整數(shù)值的表達式，然后通過“VALUES IN (value_list)”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(shù)列表。
注釋：在MySQL 5.1中，當使用LIST分區(qū)時，有可能只能匹配整數(shù)列表。

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT,
8. ????store_id?INT
9. )；

假定有20個音像店，分布在4個有經(jīng)銷權的地區(qū)，如下表所示：

====================
地區(qū)????? 商店ID 號

————————————

北區(qū)????? 3, 5, 6, 9, 17
東區(qū)????? 1, 2, 10, 11, 19, 20
西區(qū)????? 4, 12, 13, 14, 18
中心區(qū)?? 7, 8, 15, 16

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

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT,
8. ????store_id?INT
9. )
10. PARTITION?BY?LIST(store_id)
11. ????PARTITION?pNorth?VALUES?IN?(3,5,6,9,17),
12. ????PARTITION?pEast?VALUES?IN?(1,2,10,11,19,20),
13. ????PARTITION?pWest?VALUES?IN?(4,12,13,14,18),
14. ????PARTITION?pCentral?VALUES?IN?(7,8,15,16)
15. )；

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

Sql代碼 ?

1. INSERT?INTO?employees?VALUES(224,?‘Linus’,?‘Torvalds’,?‘2002-05-01′,?‘2004-10-12′,?42,?21);

這是因為“store_id”列值21不能在用于定義分區(qū)pNorth, pEast, pWest,或pCentral的值列表中找到。要重點注意的是，LIST分區(qū)沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。
LIST分區(qū)除了能和RANGE分區(qū)結合起來生成一個復合的子分區(qū)，與HASH和KEY分區(qū)結合起來生成復合的子分區(qū)也是可能的。

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

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

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT,
8. ????store_id?INT
9. )
10. PARTITION?BY?HASH(store_id)
11. PARTITIONS?4；

如果沒有包括一個PARTITIONS子句，那么分區(qū)的數(shù)量將默認為1。 例外： 對于NDB Cluster（簇）表，默認的分區(qū)數(shù)量將與簇數(shù)據(jù)節(jié)點的數(shù)量相同，

這種修正可能是考慮任何MAX_ROWS 設置，以便確保所有的行都能合適地插入到分區(qū)中。

MySQL還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規(guī) 哈希使用的是求哈希函數(shù)值的模數(shù)。
線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于，在“PARTITION BY” 子句中添加“LINEAR”關鍵字。

Sql代碼 ?

1. CREATE?TABLE?employees?(
2. ????id?INT?NOT?NULL,
3. ????fname?VARCHAR(30),
4. ????lname?VARCHAR(30),
5. ????hired?DATE?NOT?NULL?DEFAULT?‘1970-01-01′,
6. ????separated?DATE?NOT?NULL?DEFAULT?‘9999-12-31′,
7. ????job_code?INT,
8. ????store_id?INT
9. )
10. PARTITION?BY?LINEAR?HASH(YEAR(hired))
11. PARTITIONS?4；

假設一個表達式expr, 當使用線性哈希功能時，記錄將要保存到的分區(qū)是num 個分區(qū)中的分區(qū)N，其中N是根據(jù)下面的算法得到：
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，使用線性哈希分區(qū)且有4個分區(qū)，是通過下面的語句創(chuàng)建的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
現(xiàn)在假設要插入兩行記錄到表t1中，其中一條記錄col3列值為’2003-04-14’，另一條記錄col3列值為’1998-10-19’。第一條記錄將要保存到的分區(qū)確定如下：
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR(‘2003-04-14′) & (8 – 1)
= 2003 & 7
= 3
(3 >= 6 為假（FALSE）: 記錄將被保存到#3號分區(qū)中)
第二條記錄將要保存到的分區(qū)序號計算如下：
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分區(qū)中)
按照線性哈希分區(qū)的優(yōu)點在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有極其大量（1000吉）數(shù)據(jù)的表。它的缺點在于，與使用

常規(guī)HASH分區(qū)得到的數(shù)據(jù)分布相比，各個分區(qū)間數(shù)據(jù)的分布不大可能均衡。

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

Sql代碼 ?

1. CREATE?TABLE?tk?(
2. ????col1?INT?NOT?NULL,
3. ????col2?CHAR(5),
4. ????col3?DATE
5. )
6. PARTITION?BY?LINEAR?KEY?(col1)
7. PARTITIONS?3;

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

本文出自：http://blog.chedushi.com, 原文地址：http://blog.chedushi.com/archives/9644, 感謝原作者分享。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com