1、1MySQL表分區MySQL表分區2一、什么是表分區二、為什么要對表進行分區三、分區的類型四、分區的管理五、表分區的局限性3 表分區通俗來講就是允許把一個數據表根據一定的規則，跨文件系統劃分成多個可以設置為任意大小的部分。 MySQL從5.1起開始支持表分區，安裝了5.1的MySQL默認是開啟表分區支持的。可以通過：show variables like %partition%;來查看你的MySQL是否支持分區。4分區的優點：1. 與單個磁盤或文件系統分區相比，可以存儲更多的數據。2. 對于那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情

2、況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區來很方便地實現。3. 一些查詢可以得到極大的優化，這主要是借助于滿足一個給定where 語句的數據可以只保存在一個或多個分區內，這樣在查找時就不用查找其他剩余的分區。因為分區可以在創建了分區表后進行修改，所以在第一次配置分區方案時還不曾這么做時，可以重新組織數據，來提高那些常用查詢的效率。54. 涉及到例如sum() 和 count()這樣聚合函數的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如 “select salesperson_id, count(orders) as order_total from sales

3、 group by salesperson_id；”。通過“并行”， 這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。* 5. 通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量。6分區的類型:1. RANGE分區2. LIST分區3. COLUMNS分區4. HASH分區5. KEY分區7range 分區：基于屬于一個給定連續區間的列值進行分配create table employees ( id int not null, fname varchar(30), lname varchar(30), hired date not null default 1

4、970-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)；8list 分區：類似range分區,它們

5、的主要區別在于，list分區中每個分區的定義和選擇是基于某列的值從屬于一個集合，而range分區是從屬于一個連續區間值的集合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 va

6、lues 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)；如果試圖插入列值（或分區表達式的返回值）不在分區值列表中的一行時，那么“insert”查詢將失敗并報錯9columns 分區： columns分區是range分區或list分區的一種變體，支持非整形字段作為分區的鍵，也可以用多個字段組合起來作為分區的鍵。 columns分區可允許使用的分區鍵類型有

7、： 1. 所有的整形：tinyint, smallint, mediumint, int , bigint (和range分區和list分區相同) 不包括decimal和float這種數字類型的。 2. date 和 datetime 3. 字符型：chra, varchar, binary, varbinary 不包括text和blob型10range columns分區：create table test2 ( a int, b int, c char(3), d int )partition by range columns(a,d,c) ( partition p0 values le

8、ss than (5,10,ggg), partition p1 values less than (10,20,mmm), partition p2 values less than (15,30,sss), partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE) );11list columns分區：set names utf8;create table customers ( name varchar(25), city varchar(15)partition by list columns(city) ( partitio

9、n p0 values in(Beijing, Shanghai, Tianjin), partition p1 values in(Guanzhou, Shenzhen, Xiamen);12hash分區： 基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含mysql 中有效的、產生非負整數值的任何表達式。 hash分區主要用來確保數據在預先確定數目的分區中平均分布。在range和list分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在hash分區中，mysql 自動完成這些工作，我們所要做的只是基于將要被哈

10、希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。13 要使用hash分區來分割一個表，要在create table 語句上添加一個“partition by hash (expr)”子句，其中“expr”是一個返回一個整數的表達式。它可以僅僅是字段類型為mysql 整型的一列的名字。此外，需要在后面再添加一個“partitions num”子句，其中num 是一個非負的整數，它表示表將要被分割成分區的數量。 如果沒有包括一個PARTITIONS子句，那么分區的數量將默認為1。 如果在關鍵字“PARTITIONS”后面沒有加上分區的數量，將會出現語法錯誤。14create

11、 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；15 mysql還支持線性哈希功能，它與常規哈希的區別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規哈希使

12、用的是求哈希函數值的模數。線性哈希分區和常規哈希分區在語法上的唯一區別在于，在“partition by”子句中添加“linear”關鍵字，如下面所示：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(hir

13、ed)partitions 4；16 假設一個表達式expr, 當使用線性哈希功能時，記錄將要保存到的分區是num 個分區中的分區N，其中N是根據下面的算法得到： 1. 找到下一個大于num.的、2的冪，我們把這個值稱為V ，它可以通過下面的公式得到： 2. V = POWER(2, CEILING(LOG(2, num) 3. 設置 N = F (column_list) & (V - 1). 4. 當 N = num: 設置 V = CEIL(V / 2) 設置 N = N & (V - 1) 17例如，假設表t1，使用線性哈希分區且有4個分區，是通過下面的語句創建的：cr

14、eate table t1(col1 int not null,col2 char (5),col3 date)partition by linear key(col1)partitions 4;18 現在假設要插入兩行記錄到表t1中，其中一條記錄col3列值為2003-04-14，另一條記錄col3列值為1998-10-19。 第一條記錄將要保存到的分區確定如下：V = POWER(2, CEILING(LOG(2,5) = 8N = YEAR(2003-04-14) & (8 - 1) = 2003 & 7 = 3 (3 = 4 為假（FALSE）: 記錄將被保存到#3號分

15、區中)19第二條記錄將要保存到的分區序號計算如下： V = 8N = YEAR(1998-10-19) & (8-1) = 1998 & 7 = 6 (6 = 4 為真（TRUE）: 還需要附加的步驟) N = 6 & (CEILING(8 / 2) 1) = 6 & 3 = 2 (2 = 4 為假（FALSE）: 記錄將被保存到#2分區中)20key 分區： 按照key進行分區類似于按照hash分區，除了hash分區使用的用戶定義的表達式，而key分區的哈希函數是由mysql 服務器提供。create table tk ( col1 int not null,

16、 col2 char(5), col3 date)partition by linear key (col1)partitions 3;21也可以通過線性key分割一個表。下面是一個簡單的例子：create table tk ( col1 int not null, col2 char(5), col3 date)partition by linear key (col1)partitions 3;22 在KEY分區中使用關鍵字LINEAR和在HASH分區中使用具有同樣的作用，分區的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數算法。關于該算法及其蘊涵式的描述請參考線性

17、哈希功能。23子分區: 對于已經通過RANGE或LIST分區了的表可以再進行子分區。子分區既可以使用HASH希分區，也可以使用KEY分區。這也被稱為復合分區（composite partitioning）。例如： create table ts (id int, purchased date) partition by range(YEAR(purchased) subpartition by hash(TO_DAYS(purchased) subpartition 2 ( partition p0 values less than (1990), partition p1 values le

18、ss than (2000), partition p2 values less than MAXVALUE )；24 表ts 有3個RANGE分區。這3個分區中的每一個分區p0, p1, 和 p2又被進一步分成了2個子分區。實際上，整個表被分成了3 * 2 = 6個分區。但是，由于partition by hash子句的作用，這些分區的前2個只保存“purchased”列中值小于1990的那些記錄。25子分區可以用于特別大的表，在多個磁盤空間分配數據和索引。例子：create table ts (id int, purchased date) engine=myisam partition

19、by range(year(purchased) subpartition by hash(to_days(purchased) ( partition p0 values less than (1990) ( subpartition s0 data directory = /tmp/mysql/data index directory = /tmp/mysql/idx, subpartition s1 data directory = /tmp/mysql/data1 index directory = /tmp/mysql/idx1 ),26 partition p1 values le

20、ss than (2000) ( subpartition s2 data directory = /tmp/mysql/data2 index directory = /tmp/mysql/idx2, subpartition s3 data directory = /tmp/mysql/data3 index directory = /tmp/mysql/idx3 );執行不會報錯，但實際是不起作用。27使用子分區的注意項：1 每個分區必須有相同數量的子分區。2 每個subpartition 子句必須包括 (至少)子分區的一個名字。3 在每個分區內，子分區的名字必須是唯一的，但是在整個表中

21、，沒有必要保持唯一（5.1.7以前 是，5.1.8以后在一張表內保證全局唯一）。28分區的管理 ：range分區和list分區的管理：1. 刪除分區create table tr(id int,name varchar(50),purchased date)partition by range(year(purchased)(partition p0 values less than(1990),partition p1 values less than(1995),partition p2 values less than(2000),partition p3 values less tha

22、n(2005);29 從一個按照range或list分區的表中刪除一個分區，可以使用帶一個drop partition子句的alter table命令來實現:alter table tr drop partition p2; 如果希望從所有分區刪除所有的數據，但是又要保留表的定義和表的分區模式，請使用truncate table命令。 302. 添加分區： 要增加一個新的RANGE或LIST分區到一個前面已經分區了的表，使用“ALTER TABLE . ADD PARTITION”語句。對于使用RANGE分區的表，可以用這個語句添加新的區間到已有分區的序列的前面或后面。例如有一個組織的全體成員

23、數據的分區表，該表的定義如下：create table members(id int,fname varchar(25),lname varchar(25),dob date)partition by range(year(dob)(partition p0 values less than (1970),partition p1 values less than (1980),partition p2 values less than (1990);31 假設成員的最小年紀是16歲。隨著日歷接近2005年年底，要接納1990年（以及以后年份）出生的成員。可以按照下面的方式，修改成員表來容納出

24、生在19901999年之間的成員：alter table add partition (partition p3 values less than (2000); 對于通過RANGE分區的表，只可以使用ADD PARTITION添加新的分區到分區列表的高端。設法通過這種方式在現有分區的前面或之間增加一個新的分區，將會導致下面的一個錯誤：alter table add partition (partition p3 values less than (1960);32 類似的可以增加新的分區到已經通過list分區的表。例如，假定有如下定義的一個表：create table tt(id int,d

25、ata int)partition by list(data)(partition p0 values in (5, 10, 15),partition p1 values in (6, 12, 18);33 可以通過下面的方法添加一個新的分區，用來保存擁有數據列值7，14和21的行：alter table tt add partition (partition p2 values in (7, 14, 21)； 注意：不能添加這樣一個新的LIST分區，該分區包含有已經包含在現有分區值列表中的任意值。如果試圖這樣做，將會導致錯誤：alter table tt add partition (pa

26、rtition np values in (4, 8, 12);343. 修改分區：使用“reorganize partition”拆分或合并分區，沒有數據丟失。在執行上面的語句中，mysql 把保存在分區s0和s1中的所有數據都移到分區p0中。“reorganize partition”的基本語法是：alter table tbl_name reorganize partition partition_list into (partition_definitions)；35alter table members reorganize partition p0 into(partition s

27、0 values less than (1960),partition s1 values less than (1970);alter table members reorganize partition s0,s1 into(partition p0 values less than (1970);alter table members reorganize partition p0,p1,p2,p3 into (partition m0 values less than (1980),partition m1 values less than (2000);36alter table t

28、t reorganize partition p1,np into(partition p1 values in (6, 18),partition np values in (4, 8, 12); 當使用“ALTER TABLE . REORGANIZE PARTITION”來對已經按照RANGE和LIST分區表進行重新分區時，需要注意：1. 用來確定新分區模式的PARTITION子句使用與用在CREATE TABLE中確定分區模式的PARTITION子句相同的規則。2. 新分區模式不能有任何重疊的區間（適用于按照RANGE分區的表）或值集合（適用于重新組織按照LIST分區的表）。373.

29、partition_definitions 列表中分區的合集應該與在partition_list 中命名分區的合集占有相同的區間或值集合。 例如，分區p1和p2總共覆蓋了1980到1999的這些年。因此，對這兩個分區的重新組織都應該覆蓋相同范圍的年份。4. 對于按照range分區的表，只能重新組織相鄰的分區，不能跳過range分區。 5. 不能使用REORGANIZE PARTITION來改變表的分區類型；也就是說，例如，不能把RANGE分區變為HASH分區，反之亦然。也不能使用該命令來改變分區表達式或列。38hash分區和key分區的管理：create table clients (id i

30、nt,fname varchar(30),lname varchar(30),signed date)partition by hash(month(signed) partitions 12;39 不能使用與從按照range或list分區的表中刪除分區相同的方式來從hash或key分區的表中刪除分區。但是，可以使用“alter table . coalesce partition”命令來合并hash或key分區。 coalesce不能用來增加分區的數量,要增加顧客表的分區數量從12到18，使用“alter table . add partition”,具體如下：alter table cli

31、ents add partition partitions 18；40要減少分區的數量從12到6，執行下面的ALTER TABLE命令： alter table clients coalesce partition 6;對于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分區的表， COALESCE能起到同樣的作用。下面是一個類似于前面例子的另外一個例子，它們的區別只是在于表是按照LINEAR KEY 進行分區：create table clients_lk (id int,fname varchar(30),lname varchar(30),signed date)pa

32、rtition by linear key(signed) partitions 12;alter table clients_lk coalesce partition 6;41要改變分區的方式可以用 alter table tablename partition by hash(id) partitions 2;來實現。42分區的維護：Rebuilding partitions:alter table t1 rebuild partition p0, p1;Optimizing partitions:alter table t1 optimize partition p0, p1;43An

33、alyzing partitions:alter table t1 analyze partition p3;Repairing partitions:alter table t1 repair partition p0,p1;Check partitions:alter table t1 check partition p1; 44分區的一些局限性：一、關于Partitioning Keys, Primary Keys, and Unique Keys的限制在5.1中分區表對唯一約束有明確的規定，每一個唯一約束必須包含在分區表的分區鍵（也包括主鍵約束）。這句話也許不好理解，我們做幾個實驗：4

34、5create table t1( id int not null, uid int not null, primary key (id), unique key (uid) ) partition by range (id) (partition p0 values less than(5), partition p1 values less than(10) );ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the tables partitioning function46三、關于函數的限制 在建立分區表的語句中，只能包含例如ABS() 、CEILING() 、FLOOR() 、DAY() 、HOUR() 、MINUTE() 、MOD() 、MONTH() 、QUARTER() 、SECOND() 、TO_DAYS() 、YEAR()等返回整型的函數。 分區鍵必須是INT類型，或者通過表達式返回INT類型，可以為NULL。唯一的例外是當分區類型為KEY分區的時候，可以使用其他類型的列作為分區鍵（ BLOB or TEXT 列除外）。create table tkc (c1 char) partition by key(