A deep analysis of Mysql columns partition _mysql

Introduced

The column partition is the partitioning feature introduced in 5.5, with only the range column and the list column, which support reshaping, dates, strings, and range and list partitioning in a very similar way.

Differences between columns and range and list partitions

1. Partitions for date fields do not need to be converted using functions, such as partitioning against the Date field, which does not require the use of the year () expression for conversion.

A 2.COLUMN partition supports multiple fields as a partitioning key but does not support an expression as a partitioning key.

Types supported by columns

Cosmetic support: tinyint,smallint,mediumint,int,bigint; decimal and float are not supported

Time Type support: Date,datetime

Character type support: char,varchar,binary,varbinary; Text,blob not supported

A, RANGE columns partition

1. Date field partitions

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

1. Insert test data

INSERT into the 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, there are 5 partitions inserted only 4 records, where C partition is not recorded, the result is the same as the actual.

3. Analysis of the Implementation 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 that causes the query to not take a specific partition but all of the partitions scanned, while the second query executes the statement to find the specific partition.

2. Multiple field combination partitions

CREATE TABLE RCX (
a int,
b int
)
PARTITION by RANGE COLUMNS (a,b)
PARTITION p0 VALUES less THAN (5,1 0),
PARTITION p1 values less THAN (10,20), PARTITION p2 values less THAN
(15,30), PARTITION P3
values Less THAN (Maxvalue,maxvalue)
);

Note: A multiple-field partitioning key comparison is based on an array comparison. It first compares the first field value of the inserted data with the first value of the partition, and if the first value inserted is less than the first value of the partition, you do not need to compare the second value to the partition; If the first value equals the first value of the partition, Start comparing the second value if the second value is less than the second value of the partition, then it belongs to the partition.

For example:

Insert into RCX (a,b) VALUES (1,20), (10,15), (10,30);

First set of values: (1,20); 1<5 so there is no need to compare 20, the record belongs to the P0 partition.

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

Third set of values: (10,30), 10=10 but 30>20, so it does not belong to P1, it satisfies 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 value of the first column of the multiple-column partition in the RANGE column must be sequential, not cross, and the value of the second column is random, for example, the following partitions will complain

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 the partition P2 is smaller than the first column of P1, the error is correct, and the value of the first column in the subsequent partition must be larger than the previous partition value, and the second column is not specified.

Ii. LIST Columns Partition

1. Non-reshaping field partitions

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

The LIST columns partition eliminates the need to use a function to reshape a field, so partitioning the non-reshaping field is recommended to select the columns partition.

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, ' 19 92-01-01 10:00:00 ')),
PARTITION C values in ((3, ' 1993-01-01 10:00:00 ')),
PARTITION D values in (4, ' 1994-01-0 1 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 ';

Because partitions are grouped fields, filtered only 50%, for the combined partitioning index is also best to build a composite index, in fact, if you can select the data by the ID field brush, the Independent ID field index is also effective, but the effect of composite index is the best, in fact, and the concept of the non-partitioning key index almost.

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

Note: the example in the article is excerpted from the official MySQL reference manual

Third, remove the partition of the table

ALTER TABLE tablename
REMOVE partitioning;

Note: Removing a partition using remove removes the definition of the partition and does not delete the data and drop partition, which are deleted along with the data

Summarize

The RANGE columns and list columns partitions are actually upgrades to the rang and list partitions, so you can use the column partition directly. Note the columns partition does not support timestamp field types.

The above is a small set to introduce the MySQL columns division, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!