MySQL Partition

Introduction

MySQL Partition is a way of splitting or separating the rows of a table into various tables in different locations. But all these separate tables are considered as a single table. The data present in the table across the file system splits on the bases of rules set by us according to our requirements. The rule based on which partition of the data present in tables takes place is known as the partitioning function. Some of these functions are internal, linear, or modulus hashing functions. The selected function depends on the type of partitioning function you have selected. And user-supplied expression is taken by it as a parameter. It also depends on the type of partition you are using, whether the user expression is in the form of column value or a function acting on column value.

MySQL's Two Main Forms of Partitioning

Horizontal Partitioning

In horizontal partitioning, rows of a table are split into different tables according to the logic used by us. In this partition, all the tables must have the same number of columns, but there is no compulsion for the number of rows to be the same. In this, there is only a physical partition of tables, but logically all tables are considered as a single table. At present, MYSQL supports horizontal partitions.

Vertical Partitioning

Vertical partition also splits the table into different tables but takes only fewer columns from the main table. For storing the additional columns, it uses additional tables. Currently, MYSQL is not supporting vertical partitioning.

Benefits of Partitioning

• The query performance can be optimized through the implementation of partition. The MYSQL engine does not need to find the whole data to extract the intended data while using scan operation. Whereas, it can directly reach the partition where the intended data is present and scan through it. This is the whole process through which partition can optimize the process of scanning.
• Data operations can also be controlled using the process of MySQL Partition. It is also possible to add or remove data from any partition without even disturbing the other parts of the original data.
• It allows keeping extensive data in a single table
• By MySQL Partition, there is more control over the management of the data present in the database.

Types of Partitioning in MYSQL

RANGE Partitioning

For a better understanding of MYSQL partitions, let us take an example. Suppose we have created a table with the name STUDENT and it has the STUDENT_ID, STUDENT_NAME, STUDENT_DOB, and STUDENT_ADDRESS columns. After that, we will partition this table by using a range in different ways according to your requirement. And for partitioning, we have used the STUDENT_DOB column of the STUDENT table and used PARTITION BY CLAUSE for partitioning the data of the table in 4 different ways. The query for creating the table is:

Now we will add some data to the table:

Now we will fetch all the data of the table to check whether data is properly inserted in the table or not:

Now we can see the data is inserted successfully in the table.

LIST Partitioning

MYSQL LIST Partitioning is so similar to Range partitioning. It is implemented by the PARTITION BY LIST(exp) clause. The exp returns an integer value and this exp is considered an expression or column value. For defining each partition, implement the VALUES IN(value_lists) statement. A partition is created and defined based on column values matched to one of the values of discrete values lists instead of a contiguous value range.

In the example taken below, we have 10 students distributed in four different places. This can be shown more clearly with the help of the table given below:

Now we can partition the table based on STUDENT_ADDRESS and having the same location will be stored in the same partition. The below query is used to arrange the rows having locations into one partition.

COLUMNS Partitioning

In MySQL column partitioning, it is possible to use the various columns in partitioning keys. Rows can be placed in the partition with the help of these columns and also help in defining which partition will be suitable for the matching rows. There are two types of MySQL column partitioning.

• RANGE Columns Partitioning
• LIST Columns Partitioning

For defining the ranges or value lists, it allows the use of non-integer columns. The following data types are allowed in this type of partitioning.

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

Example:

RANGE Columns Partitioning: Range column partitioning is the same as range partitioning but with one difference. Partitions take place with the help of ranges and these ranges depend on the various columns considered as the partition keys. The defined ranges are defined as column types instead of integer types.

Range Columns Partitioning Syntax is given below:

list_of_column: One or more column list list_of_values: Values list supplied for every partition and it has the number of values equal to the number of columns.

LIST Columns Partitioning: List columns partitioning considers a list of one or more columns as a partition key. It is possible to use various column types instead of integer types for considering partitioning columns. This type of partitioning method can be implemented using the DATE, string data types and DATETIME columns.

HASH Partitioning

In MySQL HASH partitioning, the distribution of data is according to the predefined number of partitions. Or we can say, the partition of the table depends on the value returned by the user-defined expression. It evenly distributes data in each partition. PARTITION BY HASH(expr) CLAUSE is used to implement this partition technique. Here, we can specify a column value based on the column_name to be hashed and the number of partitions in which the table is required to be divided. Below is the query for creating the table with the name STUDENT and hashing is used on the column STUDENT_ID for dividing the table into 4 partitions.

The number of partitions will be one by default if you are not using the PARTITIONS clause. There will be an error if you do not provide the number of partitions with the keyword PARTITIONS.

KEY Partitioning

It is the same as the HASH partitioning. But in hash partitioning, a user-specified expression is used. Whereas MYSQL provides the hashing function for the key. The MYSQL can also employ its internal hashing function in case we are using other storage engines and these MYSQL functions can be implemented with the help of the PARTITION BY KEY clause. In this partition, KEY is used in place of the HASH function by which only the list of zero or multiple column names is accepted only. If the PRIMARY KEY is present in the table, and no column is specified for a partition. In this case, the primary key is considered the partitioning key.

If there is no primary key in the table but a unique key is present in the table then the unique will be used as a partition key.

Subpartitioning

It is considered a composite partitioning. As it further divides each partition present in the partition table. We can understand it in detail using the following example:

Limitations and Restrictions in Partitioning

Some of the limitations and restrictions of implementing partitioning are given below:

• Arithmetic and Logical Operators: The arithmetic operators such as +, -, *are allowed to be used in the partitioning expression. The output must be in the form of an integer or NULL. The / operator is not allowed but can use the DIV operator. The bit operators such as|, ~, >>, <<, ^,` & are not permitted in the partitioning expression.
• Subqueries: It is not possible for a partitioning key to be a subquery, even if the subquery resolves to an integer value or NULL.
• Temporary Tables: It is not allowed to implement partitioning in temporary tables.
• Spatial Columns: It is not possible to use spatial data types such as POINT or GEOMETRY in columns of partitioned tables.