Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to create table partitions in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1.RANGE partition:

CREATE TABLE T5 (id INT,dt DATETIME NOT NULL

)

PARTITION BY RANGE (TO_DAYS (dt)) (

PARTITION p0 VALUES LESS THAN (TO_DAYS ('2014-01-01'))

PARTITION p1 VALUES LESS THAN (TO_DAYS ('2014-02-01'))

PARTITION p2 VALUES LESS THAN (TO_DAYS ('2014-03-01'))

PARTITION p3 VALUES LESS THAN MAXVALUE)

Note that the range partition cannot partition date data directly, so if you want to partition the date, you need to modify it to convert the date to int.

CREATE TABLE T5 (id INT,dt DATETIME NOT NULL

)

PARTITION BY RANGE (dt) (

PARTITION p0 VALUES LESS THAN ('2014-01-02')

PARTITION p1 VALUES LESS THAN ('2014-01-03')

PARTITION p2 VALUES LESS THAN ('2014-01-04')

PARTITION p3 VALUES LESS THAN MAXVALUE)

If you use the above statement and partition directly with the date column, the following error occurs:

Error code: 1697

VALUES value for partition 'p0' must have type INT

2.LIST partition

CREATE TABLE t6

(userid INT NOT NULL

Usertype INT NOT NULL

)

PARTITION BY LIST (usertype)

(

PARTITION p1 VALUES IN (1)

PARTITION p2 VALUES IN (2)

PARTITION p3 VALUES IN (3)

);

3.HASH partition

HASH partitions are mainly used to ensure that data is evenly distributed among a predetermined number of partitions. Simply specify a column value or expression as the hash column, and then specify the partition data.

CREATE TABLE t8

(userid INT NOT NULL

Dt DATE

)

PARTITION BY HASH (MONTH (dt))

PARTITIONS 6

The problem with HASH partitions is that if the where condition is a specified range, all partitions will be scanned, which does not achieve the purpose of using partition tables to reduce the scan range and achieve performance improvement. Especially when you use da as a partition keyword, you must pay attention to the type, preferably numeric.

EXPLAIN PARTITIONS

SELECT COUNT (*) FROM t8 WHERE dt > '2013-08-01'

The above statement is to scan all partitions.

4.key partition

The Key partition type is the HASH partition, the HASH partition uses a user-defined expression, and the hash function for the KEY partition is provided by the MySQL server, which is based on the same algorithm as PASSWORD (). The main difference between it and the Hash partition is that the partition can only be based on a list of one or more column names.

CREATE TABLE t9

(userid INT NOT NULL

Dt DATE

)

PARTITION BY HASH (dt)

PARTITIONS 6

Key partitions have the same problem with range scanning as HASH partitions.

5. Compound partition

1) RANGE-HASH (range hash) compound partition

CREATE TABLE t11

(id INT NOT NULL

Dt DATE

)

PARTITION BY RANGE (id)

SUBPARTITION BY HASH (YEAR (dt))

SUBPARTITIONS 4

(

PARTITION p1 VALUES LESS THAN (100000)

PARTITION p2 VALUES LESS THAN maxvalue

);

2) RANGE- KEY compound partition

CREATE TABLE t12

(id INT NOT NULL

Dt DATE

)

PARTITION BY RANGE (id)

SUBPARTITION BY KEY (dt)

SUBPARTITIONS 4

(

PARTITION p1 VALUES LESS THAN (100000)

PARTITION p2 VALUES LESS THAN maxvalue

);

3) LIST-HASH compound partition

CREATE TABLE T13 (

Userid INT

Usertype INT

)

PARTITION BY LIST (usertype)

SUBPARTITION BY HASH (userid)

SUBPARTITIONS 3

(

PARTITION p1 VALUES IN (1)

PARTITION p2 VALUES IN (2)

PARTITION p3 VALUES IN (3)

)

4) LIST-KEY compound partition

CREATE TABLE T14 (

Userid INT

Usertype INT

)

PARTITION BY LIST (usertype)

SUBPARTITION BY KEY (userid)

SUBPARTITIONS 3

(

PARTITION p1 VALUES IN (1)

PARTITION p2 VALUES IN (2)

PARTITION p3 VALUES IN (3)

)

After reading the above, do you have any further understanding of how to create table partitions in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.