mysql分区

mysql分区 1 范围分区

根据列值在给定范围内将行分配给分区，示例1中的sql根据store_id列进行分区，小于6的在p0分区，[6-11)的在p1分区，以此类推；如果你添加的store_id大于20就会报错，因为mysql不知道应该将 它放在那个分区中，可以使用示例2中的sql解决这个问题

-- 示例1 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)
);

-- 示例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 (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
);

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

3 列分区

列分区其实就是范围分区和列表分区的总称，只不过它支持的分区键的数据类型更多了，仅此而已，在实际工作中，我们用到的更多的还是范围分区和哈希分区。

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,'mmm'),
     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

4 哈希分区

哈希分区顾名思义，根据分区键的hash值进行分区，sql如下,含义为:根据store_id列的hash值，自动分为4个分区

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;

5 key分区

类似与hash分区，只不过分区键只能是主键或唯一键

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

6 子分区

子分区（也称为复合分区）是针对分区表中每个分区的进一步划分；sql如下

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );