(18) partitions in mysql (after development), 18 mysql

(18) partitions in mysql (after development), 18 mysql
Overview

Before getting started, I saw the partition, And I squeezed it. Yesterday, I finally learned what partitioning is, but that is also the case. Today, we can sum up that good memory is not as good as bad writing.
Zookeeper MySQL supports the Partition Function from 5.1. One sentence for partitioning is:Divides a table into multiple areas (pages/Files) according to certain rules (such as range, list, hash, and key) for storage.For mysql application development, partitions are no different from those without partitions (that is, they are transparent to applications ). It is like "turning to zero" in a breakout war ". MySQL supports most storage engines (such as MyISAM, InnoDB, and Memory) to create partitions, but does not support MERGE and CSV to create partitions. All partitions in the same partition table must be the same storage engine. For example:

# Create table 'test' in the myisam TABLE that creates Five hash partitions '. 'partition _ t1' ('id' int unsigned not null, 'username' VARCHAR (30) not null, 'email 'VARCHAR (30) not null, 'Birth _ date' date not null) ENGINE = myisampartition by hash (MONTH (birth_date) PARTITIONS 5;

Partition Function

• More data can be stored (the maximum size of a single file in the System)
• Optimize the query. If a where clause contains a partition condition, you only need to scan one or more partitions to improve the query efficiency. When a function such as sum () is involved, it can be processed in parallel on the partition, and the final result is summarized.
• You can delete related partitions to quickly delete expired or unnecessary data.
• Data Query is distributed across multiple disks. The concurrency of a single table is improved, and the disk I/O performance is also improved.

Partition type

There are four types:

• Range partition: data is allocated to different partitions based on a given continuous range.
• List partition: similar to range partition, the difference is that list is based on the list partition of the enumerated values, and range is based on the range.
• Hash partition: Based on the given number of partitions, data is allocated to different partitions (Modulo/linear)
• Key partition: similar to hash partition.

In MySQL5.1, range, list, and hash partitions require that the partition key be int. MySQL5.5 or above, supports non-Integer range and list partitions, namely, range columns and list columns.
Note: either partition or partition table does not have a primary key/unique key, or the partition key must have a primary key/unique key.

1. range partitioning

Range partitions use the value range (range) to divide partitions. intervals must be continuous and cannot overlap with each other.values less thanOperator to define partitions.

Example 1:

CREATE TABLE `test`.`partition_t2`(    `id` INT UNSIGNED NOT NULL,  `username` VARCHAR(30) NOT NULL,  `email` VARCHAR(30) NOT NULL,  `birth_date` DATE NOT NULL) ENGINE=MYISAMPARTITION BY RANGE(id)(   PARTITION t21 VALUES LESS THAN (10),   PARTITION t22 VALUES LESS THAN (20),   PARTITION t23 VALUES LESS THAN MAXVALUE);

In the preceding example, a range partition table containing three partitions (t21, t22, and t23) is defined,This is a bit similar to the switch statement in advanced languages.. It is explained as follows: when the id is <10, it is partitioned in t21; when 20> id> = 10, it is partitioned in t22; when id> = 20, it is partitioned in t23.

Example 2:

CREATE TABLE `test`.`partition_t3`(    `id` INT UNSIGNED NOT NULL,  `username` VARCHAR(30) NOT NULL,  `email` VARCHAR(30) NOT NULL,  `birth_date` DATE NOT NULL) ENGINE=MYISAMPARTITION BY RANGE COLUMNS(birth_date)(   PARTITION t31 VALUES LESS THAN ('1996-01-01'),   PARTITION t32 VALUES LESS THAN ('2006-01-01'),   PARTITION t33 VALUES LESS THAN ('2038-01-01'));

MySQL5.5 improves range partitions and provides range columns partitions to support non-integer partitions.

2. list partitions

List partitions create a discrete value list (similar to the enum type data in mysql) to partition, usevalues inOperator to partition. List partitions do not need to declare any specific order. List has many aspects similar to range.

CREATE TABLE `test`.`partition_t4`(    `id` INT UNSIGNED NOT NULL,  `username` VARCHAR(30) NOT NULL,  `email` VARCHAR(30) NOT NULL,  `birth_date` DATE NOT NULL) ENGINE=MYISAMPARTITION BY LIST(id)(   PARTITION t41 VALUES IN (1,2),   PARTITION t42 VALUES IN (3,6),   PARTITION t43 VALUES IN (5,4),   PARTITION t44 VALUES IN (7,8));

In the example above, when id is 1 or 2, it is in the t41 partition; When id is 3 or 6, in the t42 partition, and so on...

3. hash Partition

Hash partitions are mainly used to distribute hotspot reads to ensure that data is evenly distributed as much as possible in a predetermined number of partitions. When a table executes a hash partition, mysql will apply a hash function to the partition key to determine which partition of the data should be placed in n partitions. Hash partitions support two hash functions (partition mode ):Modulo algorithm (default hash partition method)AndLinear 2-power algorithm (liner hash partition).

Regular hash Partition

# The example at the top is a regular hash partition.

• Mysql does not recommend using hash Expressions Involving multiple columns.
• Regular hash brings too much cost to partition management, and it is not suitable for flexibly changing partitions. See consistent hash algorithm.
• Due to the management of conventional hash partitions, all mysql instances introduce linear hash partitions.

Linear hash partitioning

CREATE TABLE `test`.`partition_t5`(    `id` INT UNSIGNED NOT NULL,  `username` VARCHAR(30) NOT NULL,  `email` VARCHAR(30) NOT NULL,  `birth_date` DATE NOT NULL) ENGINE=MYISAMPARTITION BY LINEAR HASH(id)PARTITIONS 5;

In the preceding example, create a linear hash partition with five partitions.

• Advantages of linear hash partitioning: mysql can process partitions more quickly during partition maintenance;
• Disadvantages of linear hash partitioning: data distribution between partitions is not balanced.

4. key Partition

• Hash partitions allow user-defined expressions, while key partitions do not allow user-defined expressions.
• Hash partitions only support integer partitions, and key partitions support data types other than blob or text.
• Unlike hash partitions, you can choose not to specify the partition key when creating a key/partition table. By default, the primary key/unique key is used as the partition key without a primary key/unique key. You must specify the partition key.

CREATE TABLE `test`.`partition_t6`(    `id` INT UNSIGNED NOT NULL,  `username` VARCHAR(30) NOT NULL,  `email` VARCHAR(30) NOT NULL,  `birth_date` DATE NOT NULL) ENGINE=MYISAMPARTITION BY LINEAR KEY(email)PARTITIONS 5;

Columns and subpartitions1. columns Partition

Columns includes range columns and list columns that support non-integer partition keys.Columns partitions support multi-column partitions.

CREATE TABLE `test`.`partition_t7`(    `a` INT UNSIGNED NOT NULL,  `b` INT UNSIGNED NOT NULL)PARTITION BY RANGE COLUMNS(a,b)(    PARTITION p0 VALUES LESS THAN (0,10),    PARTITION p1 VALUES LESS THAN (10,10),    PARTITION p2 VALUES LESS THAN (10,20),    PARTITION p3 VALUES LESS THAN (10,35),    PARTITION p4 VALUES LESS THAN (10,MAXVALUE),    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE));

Judgment basis: (a <10) or (1 = 10) and (10 <10 )).

2. subpartition

Sub-partitions are the re-segmentation of each partition in the Partition Table, also known as composite partitions. MySQL supports subpartitions for tables through range and list from MySQL5.1. subpartitions can be hash partitions or key partitions. Subpartitions are suitable for storing a large number of data records.

CREATE TABLE partition_t8(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased))SUBPARTITION BY HASH(TO_DAYS(purchased))SUBPARTITIONS 2(    PARTITION p0 VALUES LESS THAN (1990),    PARTITION p1 VALUES LESS THAN (2000),    PARTITION p2 VALUES LESS THAN MAXVALUE);

Partition Management

MySQL5.1 provides commands for adding, deleting, redefinition, merging, and splitting partitions.

1. range or list partitions

# Delete the partition alter table partition_t8 drop partition p2; # add a partition alter table partition_t8 add partition (partition p4 values less than (2030 )) # redefinition of a partition alter table partition_t8 reorganize partition p3 into (partition p2 values less than (2005), partition p3 values less than (2015 ));

• Only partitions can be added from the maximum end of the range partition list.
• To add a list partition, you cannot add a partition containing any value in the list of existing partition values. That is to say, you must specify a fixed partition key value and only one unique partition can be specified.
• Re-define the range partition. Only adjacent partitions can be redefined. At the same time, the re-defined partition range must overwrite the same range as the original partition range.

2. hash or key partitioning

# Reduce the number of partitions (for example, reduce the number of partitions to 2) alter table partition_t8 coalesce partition 2; # increase the number of partitions (for example, increase the number of partitions by 8) alter table partiton_t8 add partition partitions 8;

• Coalesce cannot be used to increase the number of partitions.

Table sharding reference

Mysql table sharding Methods
Mysql-Table sharding (1)