MySQL表的四種分割區類型的程式碼詳解

以下四種資料庫的分區我只實現了下RANGE（範圍）分區還有三種之後都會嘗試去實現有實現的朋友可以分享下自己覺得寫的不錯的相關文章

MySQL表的四個分區類型

一、什麼是表分區
通俗地講表分區是將一大表，根據條件分割成若干個小表。 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 (ad79d2732f294f8b8c01b7007dba89e0= 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）算法得到，而不是通过模数算法。

以上是MySQL表的四種分割區類型的程式碼詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！