MySQL partition summary, mysql Partition

MySQL partition summary, mysql Partition

MySQL supports four partitions: RANGE, LIST, HASH, and KEY. Each partition has a special type. For RANGE partitions, there are range columns partitions. LIST partitions have list columns partitions. For HASH partitions, there are linear hash partitions. For KEY partitions, there are linear key partitions. The details are as follows:

 

RANGE partitioning

RANGE refers to the RANGE partition. It determines which partition is located based on the RANGE. For example, in the following example, if store_id is smaller than 6, the newly added or modified records are allocated to p0 partitions, if the value is greater than 6 and less than 11, the record will be allocated to the p1 partition, and so on. Similar to the if... elseif... statement in programming language.

The format is as follows:

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 NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN MAXVALUE);

Note:

1. the return value of RANGE partition must be an integer.

2. PARTITION p3 values less than maxvalue is not required.

 

Range columns Partition

Range columns is a special type of RANGE partition. Its difference with RANGE partition is as follows:

1. range columns does not accept expressions and can only be column names. The RANGE partition requires that the partition object be an integer.

2. range columns allows multiple COLUMNS. At the underlying implementation level, it compares the ancestor (a list composed of multiple column values), while RANGE compares scalar values, that is, the value size.

3. range columns is not limited to integer objects, such as date, datetime, and string.

The format is as follows:

CREATE TABLE rcx (    a INT,    b INT,    c CHAR(3),    d INT)PARTITION BY RANGE COLUMNS(a,d,c) (    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),    PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),    PARTITION p2 VALUES LESS THAN (15,30,'sss'),    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));

Similar to a RANGE partition, its RANGE must be incremental. Sometimes, the column involves too many columns, so it is difficult to judge the RANGE size. You can use the following method to judge:

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);+-----------------+-----------------+-----------------+| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |+-----------------+-----------------+-----------------+|               1 |               1 |               0 |+-----------------+-----------------+-----------------+1 row in set (0.07 sec)

For more information about range columns, see the MySQL official documentation:

Http://dev.mysql.com/doc/refman/5.6/en/partitioning-columns-range.html

 

LIST Partition

LIST is the LIST partition.

The format is as follows:

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

 

List columns partitions

The list columns partition is also a special type of LIST partition. It is similar to the range columns partition and does not accept expressions. It also supports the string, date, and datetime types for multiple COLUMNS.

The format is as follows:

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

The format of multiple columns is as follows:

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

 

HASH Partition

Different from RANGE and LIST partitions, HASH partitions do not need to define partition conditions. You only need to specify the number of partitions.

The format is as follows:

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 HASH(store_id)PARTITIONS 4;

Note:

1. You do not need to specify the PARTITIONS clause for hash partitions. For example, if PARTITIONS 4 is described above, the default number of PARTITIONS is 1.

2. You are not allowed to write only PARTITIONS without specifying the number of PARTITIONS.

3. Like the range partition and list partition, the expr in the partition by hash (expr) clause must return an integer.

4. The underlying implementation of HASH partitions is actually based on MOD functions. For example

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)    PARTITION BY HASH( YEAR(col3) )    PARTITIONS 4;

If you want to insert a record with col3 as "", the partition selection is based on the following values:

MOD(YEAR('2005-09-01'),4)=  MOD(2005,4)=  1

 

Linear hash Partition

Linear hash partition is a special type of HASH partition. Unlike HASH partition, which is based on MOD functions, it is based on another algorithm.

The format is as follows:

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 LINEAR HASH( YEAR(hired) )PARTITIONS 4;

Note:

1. its advantage is that in scenarios with a large amount of data, such as TB-level, it is faster to add, delete, merge, and split partitions. Its disadvantage is that, compared with HASH partitions, it has a higher probability of uneven data distribution.

2. For specific algorithms, see the MySQL official documentation.

Http://dev.mysql.com/doc/refman/5.6/en/partitioning-linear-hash.html

 

KEY Partition

The KEY partition is similar to the HASH partition. The differences are as follows:

1. KEY partitions allow multiple columns, while HASH partitions allow only one column.

2. If there is a primary key or a unique key, the partition column in the key is not specified. The default value is the primary key or unique key. If not, the column must be explicitly specified.

3. The KEY partition object must be a column rather than a column-based expression.

4. the algorithms for KEY partitioning and HASH partitioning are different. partition by hash (expr), the MOD value object is the value returned BY expr, and partition by key (column_list ), it is based on the column's MD5 value.

The format is as follows:

CREATE TABLE k1 (    id INT NOT NULL PRIMARY KEY,    name VARCHAR(20))PARTITION BY KEY()PARTITIONS 2;

If no primary key or unique key exists, the format is as follows:

CREATE TABLE tm1 (    s1 CHAR(32))PARTITION BY KEY(s1)PARTITIONS 10;

 

Linear key Partition

Similar to the linear hash partition.

The format is as follows:

CREATE TABLE tk (    col1 INT NOT NULL,    col2 CHAR(5),    col3 DATE)PARTITION BY LINEAR KEY (col1)PARTITIONS 3;

 

Summary:

1. If a primary key or a unique key exists in a MySQL partition, the partition column must be included.

2. For native RANGE partitions, LIST partitions, HASH partitions, and partition objects, only integer values can be returned.

3. range columns, list columns, KEY, and linear key partition objects can only be COLUMNS, not column-based expressions.