MySQL 5.1 Partition Table learning notes

MySQL supports Partitioned Tables from version 5.1.x to the latest version 5.1.56, which is also a stable MySQL version. In addition, MySQL 5.5 supports range columns and list columns partitions. That is to say, non-integer COLUMNS no longer need to be converted to integer COLUMNS by using functions, and multiple COLUMNS can also be partitioned.

Because the partition function is not completed in the storage engine, most common engines are supported, such as InnoDB, MyISAM, and NDB, but CSV, FEDERATED, and MERGE are not supported. Only horizontal partitions are supported, but vertical partitions are not supported.

The advantages of partition tables can be imagined. As mentioned in the official reference manual, compared with a single disk or file system partition, more data can be stored, and some queries can be greatly optimized, this is mainly because the data that satisfies a given WHERE statement can be saved in only one or more partitions, so that no other partitions need to be searched during the search; for example, SUM () aggregate Function queries such as COUNT () can easily be processed in parallel. Data Queries are distributed across multiple disks to obtain larger query throughput.

MySQL supports four types of partitions:
1. RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval;
2. LIST partitioning: similar to partitioning by RANGE. The difference is that LIST partitioning is based on column values matching a value in a discrete value set;
3. HASH partition: select a partition based on the return value of the User-Defined expression. This expression uses the column values of the rows to be inserted into the table for calculation;
4. KEY partitioning: similar to HASH partitioning, the difference is that KEY partitioning only supports computing one or more columns, and the MySQL server provides its own HASH function.

MySQL 5.1 provides many ways to modify partition tables. It is possible to add, delete, redefine, merge, or split existing partitions. All these operations can be achieved through the alter table command partition extension. For how to add and delete partitions, RANGE and LIST partitions are very similar, and HASH and KEY partitions are very similar. For this reason, we will first introduce the management of the RANGE and HASH partitions.

The following describes the operations supported by the partition table through the instance operations of the RANGE partition. We will introduce the instance operations of the HASH partition later:
First, you can use the show variables command to determine whether MySQL supports partitioning (Note: mysql> is a prompt)
Mysql> show variables like '% partition % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Have_partition_engine | YES |
+ ----------------------- + ------- +
If the value is YES, the following operations can be continued.

Follow the examples provided in the official manual (slightly changed) to create a RANGE-type partition table:
Create table employees (
Id int not null,
Fname VARCHAR (30 ),
Lname VARCHAR (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-12-31 ',
Job_code int not null,
Store_id INT NOT NULL
)
Partition by range (store_id )(
PARTITION p0 values less than (1 ),
PARTITION p1 values less than (11 ),
PARTITION p2 values less than (21)
)

Add a partition named p3
Mysql> alter table employees add partition (partition p3 values less than (31 ));

Delete partition p3
Mysql> alter table employees drop partition p3;

The split name is p2 and the partition is p2 p3. Note that the split partition can only be the last partition of the partition table.
Mysql> alter table employees reorganize partition p2 into (partition p2 values less than (21), partition p3 values less than (31 ));

Merge the two partitions named p2 p3 into one partition p2. Note that the merged partition p2 value cannot be smaller than the original p3 partition value.
Mysql> alter table employees reorganize partition p2, p3 into (partition p2 values less than (31 ));

Note:
1. If you do not have the problem of manually expanding partitions, you can use "values less than maxvalue" to define partitions.
2. LIST partitions do not have definitions such as "values less than maxvalue" that contain other VALUES. Any VALUES to be matched must be found in the Value LIST.
3. If the value is NULL, MySQL will put the value in the leftmost partition if the RANGE partition is used, because the NULL value is considered to be less than any non-NULL value, this is the opposite of Oracle. For LIST partitions, you must specify which partition has a NULL value.

Create a HASH Partition Table
Create table employees2 (
Id int not null,
Fname VARCHAR (30 ),
Lname VARCHAR (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-12-31 ',
Job_code INT,
Store_id INT
)
Partition by hash (store_id)
PARTITIONS 4

Increase the number of partitions
Mysql> alter table employees2 add partition partitions 1;

Reduce the number of partitions
Mysql> alter table employees2 coalesce partition 1;

Note: "alter table... reorganize partition" cannot be used for tables partitioned by HASH or HASH.

You can also optimize the two tables mentioned above.
Mysql> alter table employees rebuild partition p0, p1;

Note: "alter table... reorganize partition" can also reconstruct the PARTITION data file.

View SQL Execution plans
Mysql> explain partitions select * from employees;

MySQL also supports sub-partitions. You can also use a separate disk for data and indexes in each RANGE partition.
Create table employees3 (
Id int not null,
Fname VARCHAR (30 ),
Lname VARCHAR (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-12-31 ',
Job_code int not null,
Store_id INT NOT NULL
)
Partition by range (YEAR (hired ))
Subpartition by hash (TO_DAYS (hired ))(
PARTITION p0 values less than (2010 )(
SUBPARTITION s0 data directory = '/disk0/data' index directory ='/disk0/idx ',
SUBPARTITION s1 data directory = '/disk1/data' index directory ='/disk1/idx'
),
PARTITION p1 values less than (2011 )(
SUBPARTITION s2 data directory = '/disk2/data' index directory ='/disk2/idx ',
SUBPARTITION s3 data directory = '/disk3/data' index directory ='/disk3/idx'
),
PARTITION p2 values less than (2012 )(
SUBPARTITION s4 data directory = '/disk4/data' index directory ='/disk4/idx ',
SUBPARTITION s5 data directory = '/disk5/data' index directory ='/disk5/idx'
)
);
Note that the InnoDB Storage engine ignores the data directory And index directory syntax. Therefore, it is invalid to separate the DATA and INDEX files of the preceding partition tables.

For details, refer to the official manual.