当前位置: 代码迷 >> MySQL >> MySQL分区小结
  详细解决方案

MySQL分区小结

热度:177   发布时间:2016-05-05 16:32:13.0
MySQL分区总结

MySQL支持RANGE,LIST,HASH和KEY四种分区。其中,每个分区又都有一种特殊的类型。对于RANGE分区,有RANGE COLUMNS分区。对于LIST分区,有LIST COLUMNS分区。对于HASH分区,有LINEAR HASH分区。对于KEY分区,有LINEAR KEY分区。具体如下:

 

RANGE分区

RANGE即范围分区,根据区间来判断位于哪个分区,譬如,在下例中,如果store_id小于6,则新增或修改的记录会被分配到p0分区,如果大于6小于11,则记录会被分配到p1分区,依次类推。类似于编程语言中的if ... elseif ...语句。

格式如下:

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);

注意:

1. RANGE分区的返回值必须为整数。

2. PARTITION p3 VALUES LESS THAN MAXVALUE 是非必需的。

 

RANGE COLUMNS分区

RANGE COLUMNS是RANGE分区的一种特殊类型,它与RANGE分区的区别如下:

1. RANGE COLUMNS不接受表达式,只能是列名。而RANGE分区则要求分区的对象是整数。

2. RANGE COLUMNS允许多个列,在底层实现上,它比较的是元祖(多个列值组成的列表),而RANGE比较的是标量,即数值的大小。

3. RANGE COLUMNS不限于整数对象,date,datetime,string都可作为分区列。

格式如下:

CREATE TABLE rcx (    a INT,    b INT,    c CHAR(3),    d INT)PARTITION BY RANGE COLUMNS(a,d,c) (    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),    PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),    PARTITION p2 VALUES LESS THAN (15,30,'sss'),    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));

同RANGE分区类似,它的区间范围必须是递增的,有时候,列涉及的太多,不好判断区间的大小,可采用下面的方式进行判断:

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);+-----------------+-----------------+-----------------+| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |+-----------------+-----------------+-----------------+|               1 |               1 |               0 |+-----------------+-----------------+-----------------+1 row in set (0.07 sec)

关于RANGE COLUMNS的更多说明,可参考MySQL官方文档:

http://dev.mysql.com/doc/refman/5.6/en/partitioning-columns-range.html

 

LIST分区

LIST即列表分区。

格式如下:

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 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));

 

LIST COLUMNS分区

LIST COLUMNS分区同样是LIST分区的一种特殊类型,它和RANGE COLUMNS分区较为相似,同样不接受表达式,同样支持多个列支持string,date和datetime类型。

格式如下:

CREATE TABLE customers_1 (    first_name VARCHAR(25),    last_name VARCHAR(25),    street_1 VARCHAR(30),    street_2 VARCHAR(30),    city VARCHAR(15),    renewal DATE)PARTITION BY LIST COLUMNS(renewal) (    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28'));

多列格式如下:

CREATE TABLE customers_2 (    first_name VARCHAR(25),    last_name VARCHAR(25),    street_1 VARCHAR(30),    street_2 VARCHAR(30),    city VARCHAR(15),    renewal DATE)PARTITION BY LIST COLUMNS(city,last_name,first_name) (    PARTITION pRegion_1 VALUES IN (('Oskarshamn', 'Högsby', 'Mönsterås'),('Nässjö', 'Eksjö', 'Vetlanda')),    PARTITION pRegion_2 VALUES IN(('Vimmerby', 'Hultsfred', 'Västervik'),('Uppvidinge', 'Alvesta', 'Växjo')));

 

HASH分区

和RANGE,LIST分区不同的是,HASH分区无需定义分区的条件。只需要指明分区数即可。

格式如下:

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 HASH(store_id)PARTITIONS 4;

注意:

1. HASH分区可以不用指定PARTITIONS子句,如上文中的PARTITIONS 4,则默认分区数为1。

2. 不允许只写PARTITIONS,而不指定分区数。

3. 同RANGE分区和LIST分区一样,PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

4. HASH分区的底层实现其实是基于MOD函数。譬如,对于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)    PARTITION BY HASH( YEAR(col3) )    PARTITIONS 4;

如果你要插入一个col3为“2005-09-15”的记录,则分区的选择是根据以下值决定的:

MOD(YEAR('2005-09-01'),4)=  MOD(2005,4)=  1

 

LINEAR HASH分区

LINEAR HASH分区是HASH分区的一种特殊类型,与HASH分区是基于MOD函数不同的是,它基于的是另外一种算法。

格式如下:

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(hired) )PARTITIONS 4;

说明:

1. 它的优点是在数据量大的场景,譬如TB级,增加、删除、合并和拆分分区会更快,缺点是,相对于HASH分区,它数据分布不均匀的概率更大。

2. 具体算法,可参考MySQL的官方文档

http://dev.mysql.com/doc/refman/5.6/en/partitioning-linear-hash.html

 

KEY分区

KEY分区其实跟HASH分区差不多,不同点如下:

1. KEY分区允许多列,而HASH分区只允许一列。

2. 如果在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。

3. KEY分区对象必须为列,而不能是基于列的表达式。

4. KEY分区和HASH分区的算法不一样,PARTITION BY HASH (expr),MOD取值的对象是expr返回的值,而PARTITION BY KEY (column_list),基于的是列的MD5值。

格式如下:

CREATE TABLE k1 (    id INT NOT NULL PRIMARY KEY,    name VARCHAR(20))PARTITION BY KEY()PARTITIONS 2;

在没有主键或者唯一键的情况下,格式如下:

CREATE TABLE tm1 (    s1 CHAR(32))PARTITION BY KEY(s1)PARTITIONS 10;

 

LINEAR KEY分区

LINEAR HASH分区类似。

格式如下:

CREATE TABLE tk (    col1 INT NOT NULL,    col2 CHAR(5),    col3 DATE)PARTITION BY LINEAR KEY (col1)PARTITIONS 3;

 

总结:

1. MySQL分区中如果存在主键或唯一键,则分区列必须包含在其中。

2. 对于原生的RANGE分区,LIST分区,HASH分区,分区对象返回的只能是整数值。

3. RANGE COLUMNS,LIST COLUMNS,KEY,LINEAR KEY分区对象只能是列,不能是基于列的表达式。

 

  相关解决方案