search

Home >Database >Mysql Tutorial >MySQL分区总结_MySQL

MySQL分区总结_MySQL

WBOY
WBOYOriginal
2016-05-27 13:46:24955browse

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,10)

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

|               1 |               1 |               0 |

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

1 row in set (0.07 sec)

 

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的官方文档

 

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分区对象只能是列，不能是基于列的表达式。

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MySQL调优－－Usingfilesort_MySQLNext article：mysqlbinlog解析binlog乱码问题解密

Related articles

See more