MySQL分区的类型一 range分区

2015-04-18 22:33:00
hainuo
来源:
CSDN
转贴 1876

三、分区类型

 

· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
· LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

  • RANGE分区

       基于属于一个给定连续区间的列值,把多行分配给分区。

       这些区间要连续且不能相互重叠,使用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 (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子句,该子句提供给所有大于明确指定的最高值的值:


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_i d 列值 大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某 个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。
     在几乎一样的结构中,你还可以基于雇员的工作代码来分割表,也就是说,基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下面的语句创建该分区表:


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 (<)比较的一部分;因此, 表达式的值不能为NULL  。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经 被定义为非空 (NOT NULL)。
       除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE (日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分区模式的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 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   
);  

在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中, 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。

RANGE分区在如下场合特别有用:
      1)、  当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。
      2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
      3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

注释:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。

发表评论
贰 减 玖 =
评论通过审核后显示。