MySQL COLUMNS partition, mysqlcolumns Partition

MySQL COLUMNS partition, mysqlcolumns Partition
Introduction

COLUMN partition is a partition function introduced at the beginning of 5.5. Only the range column and list column partitions are supported. integer, date, and string partitions are supported. The RANGE and LIST partitions are very similar.

 

 

COLUMNS, RANGE, and LIST partitions

1. You do not need to use the function to convert the partition of the date field. For example, you do not need to use the YEAR () expression to convert the partition of the date field.

2. COLUMN partitioning supports multiple fields as partition keys, but does not support expressions as partition keys.

Types supported by COLUMNS

Integer: tinyint, smallint, mediumint, int, bigint; decimal and float are not supported.

Supported time types: date and datetime

Supported character types: char, varchar, binary, and varbinary. text and blob are not supported.

1. range columns Partition

1. Date Field partitioning

CREATE TABLE members (    id INT,    joined DATE NOT NULL)PARTITION BY RANGE COLUMNS(joined) (    PARTITION a VALUES LESS THAN ('1960-01-01'),    PARTITION b VALUES LESS THAN ('1970-01-01'),    PARTITION c VALUES LESS THAN ('1980-01-01'),    PARTITION d VALUES LESS THAN ('1990-01-01'),    PARTITION e VALUES LESS THAN MAXVALUE);

1. Insert Test Data

insert into members(id,joined) values(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');

2. query partition data distribution

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='members';

Currently, only four records are inserted in five partitions, and the c partition has no records, and the result is the same as the actual one.

3. Analyze the execution plan

explain select id,joined from tb_partition.members where joined=YEAR(now());explain select id,joined from tb_partition.members where joined='1963-01-01';

The first query uses a function, so that the query does not go through a specific partition, but all the partitions are scanned. The second query executes a statement to find the specific partition.

2. Multi-field combined partitioning

CREATE TABLE rcx (    a INT,    b INT    )PARTITION BY RANGE COLUMNS(a,b) (     PARTITION p0 VALUES LESS THAN (5,10),     PARTITION p1 VALUES LESS THAN (10,20),     PARTITION p2 VALUES LESS THAN (15,30),     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));

Note: Multi-field partition key comparison is based on Array comparison. It first compares the first field value of the inserted data with the first value of the partition, if the first value inserted is smaller than the first value of the partition, you do not need to compare the second value to belong to the partition. If the first value is equal to the first value of the partition, start to compare the second value. Similarly, if the second value is smaller than the second value of the partition, it belongs to the partition.

For example:

insert into rcx(a,b)values(1,20),(10,15),(10,30);

The first group of values: (); 1 <5 so you do not need to compare 20, the record belongs to the p0 partition.

The second group of values: (), 10> 5, 10 = 10 and 15 <20, so the record belongs to the P1 partition.

The third group of values: (), 10 = 10 but 30> 20, so it does not belong to p1, it meets 10 <15 so it belongs to p2

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='rcx';

Note: The partition values of the first COLUMN of Multi-COLUMN partitions in the range column must increase sequentially and do not contain cross values. The values of the second COLUMN are random. For example, an error is returned for the following partitions.

PARTITION BY RANGE COLUMNS(a,b) (     PARTITION p0 VALUES LESS THAN (5,10),     PARTITION p1 VALUES LESS THAN (10,20),     PARTITION p2 VALUES LESS THAN (8,30),     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));

Because the first column of partition P2 is smaller than the first column of P1, an error is reported. The value of the first column of the subsequent partition must be greater than the value of the previous partition. The second column is not specified.

Ii. list columns partitions

1. partitions of non-integer Fields

CREATE TABLE listvar (    id INT NOT NULL,    hired DATETIME NOT NULL)PARTITION BY LIST COLUMNS(hired) (    PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),    PARTITION b VALUES IN ('1992-01-01 10:00:00'),    PARTITION c VALUES IN ('1993-01-01 10:00:00'),    PARTITION d VALUES IN ('1994-01-01 10:00:00'));ALTER TABLE listvar ADD INDEX ix_hired(hired);INSERT INTO listvar() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00'),(1,'1993-01-01 10:00:00');

List columns partitions partition integer fields without the need to use a function to process the fields into an integer. Therefore, we recommend that you select COLUMNS partitions for non-integer fields.

EXPLAIN SELECT * FROM listvar WHERE hired='1990-01-01 10:00:00';

2. Multi-field partitioning

CREATE TABLE listvardou (    id INT NOT NULL,    hired DATETIME NOT NULL)PARTITION BY LIST COLUMNS(id,hired) (    PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') ),    PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),    PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),    PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') ));ALTER TABLE listvardou ADD INDEX ix_hired(hired);INSERT INTO listvardou() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(2,'1992-01-01 10:00:00'),(3,'1993-01-01 10:00:00');SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='listvardou';

EXPLAIN SELECT * FROM listvardou WHERE id=1 and hired='1990-01-01 10:00:00';

Since the partition is a combination field, the filtered is only 50%. It is best to create a combination index for the combination partition index. In fact, if you can use the id field to select data, the index of the id field is also effective, but the combination index is the best. In fact, the non-partition key index concept is similar.

ALTER TABLE listvardou ADD INDEX ix_hired1(id,hired);

Note: Examples in this article are taken from the mysql official reference manual.

 

3. Remove table partitions

ALTER TABLE tablenameREMOVE PARTITIONING ;

Note: using remove to remove a PARTITION is to only remove the definition of the PARTITION, and the data will not be deleted differently from the drop PARTITION. The latter will be deleted together with the data.

 

Refer:

RANGE partitioning: http://www.cnblogs.com/chenmh/p/5627912.html

LIST partition: http://www.cnblogs.com/chenmh/p/5643174.html

HASH partition: http://www.cnblogs.com/chenmh/p/5644496.html

KEY partition: http://www.cnblogs.com/chenmh/p/5647210.html

Subpartition: http://www.cnblogs.com/chenmh/p/5649447.html

Specify partition paths: http://www.cnblogs.com/chenmh/p/5644713.html

Partitioning index: http://www.cnblogs.com/chenmh/p/5761995.html

Partition Summary: http://www.cnblogs.com/chenmh/p/5623474.html

Summary

The range columns and list columns partitions are actually upgrades of RANG and LIST partitions, so you can directly use COLUMN partitions. Note that COLUMNS partitions do not support the timestamp field type.

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion