Shulou(Shulou.com)06/01 Report--

Introduction

COLUMN partition is a partition feature introduced since 5.5.There are only RANGE COLUMN and LIST COLUMN partitions; * *, date and string are supported; RANGE and LIST partition are very similar.

Differences between COLUMNS and RANGE and LIST partitions

1. Partitions for date fields no longer need to be converted by functions, for example, partitions for date fields no longer need to be converted using the YEAR () expression.

2.COLUMN partitions support multiple fields as partition keys but do not support expressions as partition keys.

Types supported by COLUMNS

Support: tinyint,smallint,mediumint,int,bigint; does not support decimal and float

Time type support: date,datetime

Character type support: char,varchar,binary,varbinary; does not support text,blob

I. RANGE COLUMNS Partition

1. Date field partition

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), (1) (1), (1), (1), (1), (1), (1), (1)

two。 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 with only 4 records inserted, of which partition C has no record, and the result is the same as the actual.

3. Analysis and 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 scan all partitions instead of specific partitions, while the second query execution statement looks for specific partitions.

two。 Multiple fields combined partition

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

Note: multi-field partition key comparisons are based on array comparisons. 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, then there is no 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 comparing the second value and 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 _ r _ b) values (1m 20), (10 _ r 15), (10 _ re 30)

The first set of values: (1, 10, 20); 15, 10, 10 and 1520, so it does not belong to p1, it satisfies 10

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.