MySQL之LIST和LIST COLUMNS分区初探

阅读本文时，请先阅读《MySQL之RANGE和RANGE COLUMNS分区初见》。

LIST分区

        MySQL的LIST分区在许多方面与RANGE分区有相似之处。例如与RANGE分区一样，必须显式地定义每个分区。两者也有如下主要的不同之处：

（1）在LIST分区中，每个分区都是基于列值（column value） 与一组值列表（ a set of value lists）的成员关系来定义和选择的；而在RANGE分区中，每个分区是基于列值与一组连续的值范围的成员关系来定义的；

（2）在LIST分区中，使用PARTITION BY LIST(expr)来定义，expr是expression的缩写，表示一个列的值，或者是一个基于列值的表达式，要求expr是整数值。然后再通过VALUES IN (value_list)定义每一个分区，value_list是逗号分隔的整数值列表。而在RANGE分区中，使用PARTITION BY RANGE(expr)，然后再通过VALUES LESS THAN(value)定义每个分区，当然，在LIST分区中，可以只匹配一个值，VALUES IN (value)，value可以是NULL；

（3）在LIST分区中，各个分区之间不需要特殊的顺序，而在RANGE分区中是需要顺序的。

下面看如何创建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
);

根据store_id的值，可以将数据行分为如下4个区域：

Region	Store ID Numbers
North	3, 5, 6, 9, 17
East	1, 2, 10, 11, 19, 20
West	4, 12, 13, 14, 18
Central	7, 8, 15, 16

 可以如下建立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)
);

注：store_id列类型是INT。 

仅删除数据，不删除分区：

 ALTER TABLE employees TRUNCATE PARTITION pWest;

 删除分区（数据行同时被删除）：

ALTER TABLE employees DROP PARTITION pWest;

效率高于

DELETE FROM employees WHERE store_id IN (4,12,13,14,18);

当插入一条不属于任何分区的数据会怎样呢？我们举个例子：

CREATE TABLE h2 (
   c1 INT,
   c2 INT
 )
PARTITION BY LIST(c1) (
PARTITION p0 VALUES IN (1, 4, 7),
PARTITION p1 VALUES IN (2, 5, 8)
);

 我们使用DBeaver创建LIST分区表h2，成功后如图：

 向h2中插入如下数据：

 c1=3不在任何分区中，插入失败！

当使用一个INSERT语句插入多行时，行为取决于表是否使用事务存储引擎。对于InnoDB表，该语句被认为是一个单独的事务，因此任何不匹配的值都会导致语句完全失败，并且不会插入行。对于使用非事务性存储引擎(如MyISAM)的表，在包含不匹配值的行之前的任何行都会被插入，但在该行之后的任何行不会被插入。

 LIST COLUMNS分区

        LIST COLUMNS分区就像RANGE COLUMNS分区是RANGE分区的变种一样，它是LIST分区的变种。和LIST分区不同，

（1）在LIST分区中，PARTITION BY LIST(expr)，expr是整数型的列或者返回值为整数的列的表达式，而在 LIST COLUMNS分区中，使用PARTITION BY LIST COLUMNS(column_list)，column_list是单个或者多个列名，不再是表达式，事实上，COLUMNS()不允许使用列名以外的表达式；

（2）LIST COLUMNS分区不再局限于整数列，如下类型均可以：

• 所有整数类型: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT。

  其他数值类型 ，比如 DECIMAL 和 FLOAT是不支持的。

• DATE 和 DATETIME；

• 字符串类型: CHAR, VARCHAR, BINARY, 和 VARBINARY，不支持TEXT 和 BLOB。

举一些例子

基于字符串类型的列，创建LIST COLUMS分区，如下：

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(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

基于DATE类型列，创建LIST COLUMNS分区：

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(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_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(city,renewal) (
    PARTITION pRegion_1 VALUES IN(('Oskarshamn','2010-02-01'), ('Högsby','2010-02-01'), ('Mönsterås','2010-02-01')),
    PARTITION pRegion_2 VALUES IN(('Vimmerby','2010-02-01'), ('Hultsfred','2010-02-01'), ('Västervik','2010-02-01')),
    PARTITION pRegion_3 VALUES IN(('Nässjö','2010-02-01'), ('Eksjö','2010-02-01'), ('Vetlanda','2010-02-01')),
    PARTITION pRegion_4 VALUES IN(('Uppvidinge','2010-02-01'), ('Alvesta','2010-02-01'), ('Växjo','2010-02-01'))
);

下一篇：MySQL之HASH分区和LINEAR HASH分区初识