MYSQL5.1表分区初探

MySQL5.1分区功能初探

编写人：胡家惠

日 期：2008-3-14

1.1.1.1.1. MySQL分区的概念

分区就是根据某种规则把一个大表划分为多个物理存放的部分(也叫子分区)，从而提高表的查询速度和方便管理的一种方法。MySQL5.1开始全面支持表分区，但是现阶段MySQL官方还没有推荐把MySQL5.1应用到生产环境，应该过不了多久，就会推出正式版的MySQL5.1。可以对MySQL所支持的所有存储引擎表进行分区，但是一个分区表中只能选择一种存储引擎。

测试你的MySQL是否支持分区方法：

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| have_partition_engine | YES |

+-----------------------+-------+

1 row in set (0.00 sec)

如果看到变量have_partition_engine的值为YES，说明你的MySQL支持分区。

1.1.1.1.2. 分区的优点

1、 与单个磁盘或文件系统分区相比，可以存储更多的数据。

2、 简化管理。

3、 缩小查询范围，提高查询性能

4、 提高查询的并行性

5、 提高磁盘吞吐量

1.1.1.1.3. 分区的创建方法

1.1.1.1.3.1. RANGE分区

假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这

20家音像店的编号从1到20（store_id字段）。

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

)

可以根据store_id字段进行范围分区:

CREATE TABLE employees_range0 (

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

)

按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，商店11到15的雇员保存在P2中，商店号大于或等于16而小于MAXVALUE的雇员记录将保存在p3中，这里MAXVALUE 表示最大的可能的整数值。

也可以基于每个雇员进入公司的年份（YEAR(hired)）来分割表，比如：

CREATE TABLE employees_range1 (

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(hired)) (

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中。

1.1.1.1.3.2. LIST分区

MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，

每个分区必须明确定义。它们的主要区别在于，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

)

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句进行LIST分区：

CREATE TABLE employees_list (

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)

)

要强调的是，如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行

时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的插入语句将失败：

INSERT INTO employees VALUES

(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21)

这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。

1.1.1.1.3.3. HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST

分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

下面的语句创建了一个使用基于“store_id”列进行哈希处理的表，该表被分成

了4个分区：

CREATE TABLE employees_hash0 (

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，如果在关键

字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。

也可以基于雇用雇员的年份来进行分区。这可以通过下面的语句来实现：

CREATE TABLE employees_hash1 (

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

…… 此处隐藏：7061字，全部文档内容请下载后查看。喜欢就下载吧 ……