What is partitioning in MySQL?

Partitioning in MySQL is used to split or partition the rows of a table into separate tables in different locations, but still, it is treated as a single table. It distributes the portions of the table's data across a file system based on the rules we have set as our requirement. The rule that we have set to accomplish the division of table data is called as a partitioning function (modulus, a linear or internal hashing function, etc.). The selected function is based on the partitioning type we have specified and takes a user-supplied expression as its parameter. The user- expression can be a column value or a function acting on column values, depending on the type of partitioning used.

MySQL 8.0 only supports partitioning in InnoDB and NDB storage engines. Other storage engines such as MyISAM, MERGE, CSV, and FEDERATED cannot have support for partitioning.

MySQL has mainly two forms of partitioning:

1. Horizontal Partitioning

This partitioning split the rows of a table into multiple tables based on our logic. In horizontal partitioning, the number of columns is the same in each table, but no need to keep the same number of rows. It physically divides the table but logically treated as a whole. Currently, MySQL supports this partitioning only.

2. Vertical Partitioning

This partitioning splits the table into multiple tables with fewer columns from the original table. It uses an additional table to store the remaining columns. Currently, MySQL does not provide supports for this partitioning.

Benefits of Partitioning

The following are the benefits of partitioning in MySQL:

• It optimizes the query performance. When we query on the table, it scans only the portion of a table that will satisfy the particular statement.
• It is possible to store extensive data in one table that can be held on a single disk or file system partition.
• It provides more control to manage the data in your database.

How can we partition the table in MySQL?

We can create a partition in MySQL using the CREATE TABLE or ALTER TABLE statement. Below is the syntax of creating partition using CREATE TABLE command:

The below is the syntax of creating partition using ALTER TABLE command:

Types of MySQL Partitioning

MySQL has mainly six types of partitioning, which are given below:

1. RANGE Partitioning
2. LIST Partitioning
3. COLUMNS Partitioning
4. HASH Partitioning
5. KEY Partitioning
6. Subpartitioning

Let us discuss each partitioning in detail.

MySQL RANGE Partitioning

This partitioning allows us to partition the rows of a table based on column values that fall within a specified range. The given range is always in a contiguous form but should not overlap each other, and also uses the VALUES LESS THAN operator to define the ranges.

In the following example, we are going to create a table named "Sales" containing the five columns cust_id, name, store_id, bill_no, bill_date, and amount. Next, we will partition this table by using a range in several ways based on our needs. Here, we will use the bill_date column for partitioning and then partition the table's data in four ways using a PARTITION BY RANGE clause:

Next, we need to insert records into the table as below statement:

To verify the record, we will execute the below statement:

We can see that the records are successfully inserted into the Sales table.

We can see the partition created by CREATE TABLE statement using the below query:

We will get the following output where four partitions are created successfully:

DROP MySQL Partition

Sometimes our table contains the data that is useless in the partition table. In that case, we can drop single or multiple partitions based on the need. The following statement is used to delete all rows from the partition p0 of table Sales:

After successful execution, we can see that the two rows are deleted from the table.

We can verify the partition table using the query below:

In the output, we can see that partition p0 does not contain any rows.

MySQL LIST Partitioning

It is the same as Range Partitioning. Here, the partition is defined and selected based on columns matching one of a set of discrete value lists rather than a set of a contiguous range of values. It is performed by the PARTITION BY LIST(exp) clause. The exp is an expression or column value that returns an integer value. The VALUES IN(value_lists) statement will be used to define each partition.

In the below example, suppose we have 12 stores distributed among four franchises based on their region. The table explains it more clearly:

We can partition the above table where rows for stores belonging to the same region and will be stored in the same partition. The following statement arranges the stores in the same region using LIST partitioning, as shown below:

After the successful execution, it will give following output;

MySQL HASH Partitioning

This partitioning is used to distribute data based on a predefined number of partitions. In other words, it splits the table as of the value returned by the user-defined expression. It is mainly used to distribute data evenly into the partition. It is performed with the PARTITION BY HASH(expr) clause. Here, we can specify a column value based on the column_name to be hashed and the number of partitions into which the table is divided.

This statement is used to create table Store using CREATE TABLE command and uses hashing on the store_id column that divided it into four partitions:

NOTE: If you do not use the PARTITIONS clause, the number of partitions will be one by default. If you do not specify the number with the PARTITIONS keyword, it will throw an error.

MySQL COLUMN Partitioning

This partitioning allows us to use the multiple columns in partitioning keys. The purpose of these columns is to place the rows in partitions and determine which partition will be validated for matching rows. It is mainly divided into two types:

• RANGE Columns Partitioning
• LIST Columns Partitioning

They provide supports for the use of non-integer columns to define the ranges or value lists. They support the following data types:

• All Integer Types: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
• String Types: CHAR, VARCHAR, BINARY, and VARBINARY.
• DATE and DATETIME data types.

Range Column Partitioning: It is similar to the range partitioning with one difference. It defines partitions using ranges based on various columns as partition keys. The defined ranges are of column types other than an integer type.

The following are the syntax for Range Columns Partitioning.

Let us understand it with the below example.

In this example, the table "test_part" contains the four columns A, B, C, and D. We have used the first three columns in partitioning in the order of A, B, C. And, each list value is used to define a partition that contains three values in the same order as INT, CHAR, and INT. After execution, we will get the output as below and verified by the SELECT statement successfully.

List Columns Partitioning: It takes a list of single or multiple columns as partition keys. It enables us to use various columns of types other than integer types as partitioning columns. In this partitioning, we can use String data types, DATE, and DATETIME columns.

The following example explains it more clearly. Suppose a company has many agents in three cities for marketing purposes. We can organize it as below:

The following statement uses a List Columns Partitioning to organize the agents:

After the successful execution, we will get the output as below:

MySQL KEY Partitioning

It is similar to the HASH partitioning where the hash partitioning uses the user-specified expression, and MySQL server supplied the hashing function for key. If we use other storage engines, the MySQL server employs its own internal hashing function that is performed by using the PARTITION BY KEY clause. Here, we will use KEY rather than HASH that can accept only a list of zero or more column names.

If the table contains a PRIMARY KEY and we have not specified any column for partition, then the primary key is used as partitioning key. The below example explains it more clearly:

If the table have unique key but not contains the primary key, then a UNIQUE KEY is used as a partition key.

SUBPARTITIONING

It is a composite partitioning that further splits each partition in a partition table. The below example helps us to understand it more clearly:

Execute the below statement to verify the sub-partitioning:

It will give the output as below: