MySQL Partitioning: 4 Best Practices

In most modern data solutions, huge datasets are split into partitions that are accessible & manageable individually. This division of data is called Partitioning and it facilitates scalability, reduces contention, and enhances query processing speed. Moreover, it provides the user with a mechanism to partition data according to business requirements. MySQL Partitioning divides or partitions a table’s rows into separate tables in different locations, but the table is still treated as a single entity.

This article will introduce you to MySQL along with its key features. It will then discuss the MySQL Partitions and explain their various types along with their syntax. The article will also dive into the advantages of using MySQL Partitions and the best practices that you must follow while implementing them. Read along to understand the importance and usage of MySQL Partition in detail

What is MySQL Partition?

MySQL Partitions splits up the data of a table into individual smaller tables stored at different locations. This process involves distributing portions of the original table’s data to separate locations in a file system using certain rules. This way, the storage space is used optimally. Moreover, even after the partitions, from the user’s point of view, the data will reside in a single table. 

The rule that you will use to divide the data is known as Partitioning Function. Modulus, Linear (internal hashing function), etc. are some popular Partitioning Functions that you can use to split the data according to your requirement. The selected function will take a user-supplied parameter as its input. This method helps in reducing the Query Processing time. Now, if you will request data access via an SQL query, the engine will have to search through only a fraction of table data.

Keep in mind that using MySQL Partitions is beneficial if you are dealing with huge data sets. This implies, Partitioning will have no considerable effect if your rows are in the order of thousands only.

How to Check If Your Engine Supports Partitioning?

Partitioning is supported by default in community binaries. By using the SHOW PLUGINS statement, you can see if it’s supported in your current instance. The output should look similar to the following row:

| Name       | Status   | Type           | Library | License |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

Another option is to look into INFORMATION SCHEMA.PLUGINS:

SELECT
    PLUGIN_NAME as Name,
    PLUGIN_VERSION as Version,
    PLUGIN_STATUS as Status
    FROM INFORMATION_SCHEMA.PLUGINS
    WHERE PLUGIN_TYPE='STORAGE ENGINE';

You should obtain something like this:

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

The plugin you’re looking for is named “partition” and is set to ACTIVE in both outputs. If neither of these methods shows the partition, your MySQL version was probably installed without partitioning support. In this case, a new MySQL compilation with the –DWITH PARTITION STORAGE ENGINE option is required. The documentation for “INSTALLING MYSQL FROM SOURCE” will show you how to do this.

What are the Types of MySQL Partitions?

The Horizontal Partitions will split the rows of your table into multiple smaller tables using the input partitioning function. The number of columns, however, will be the same in each smaller table. So, the original table will be physically divided into a set of smaller tables but logically, the entire data will be considered as a whole. 

The Vertical Partitions, on the other hand, will divide the data into numerous tables that will contain fewer columns than the original table. Moreover, it will deploy an additional table that will store the remaining columns.

The MySQL Partitions currently only offer the Horizontal form of data division. The MySQL Partitions are of the following types:

• RANGE MySQL Partitions
• HASH MySQL Partitions
• KEY MySQL Partitions
• LIST MySQL Partitions
• Column MySQL Partitions
• MySQL Subpartitioning

1) RANGE MySQL Partitions

The RANGE Partitions splits up rows and store them in different partitions on the basis of column values. It uses the VALUES LESS THAN operator to define the column ranges from lowest to highest value. A constraint of this Partitioning is that the range values should be contiguous and should not overlap. The below example shows the syntax of RANGE Partitions and the image represents its output.

CREATE TABLE Sales ( cust_id INT NOT NULL, name VARCHAR(40),   
store_id VARCHAR(20) NOT NULL, bill_no INT NOT NULL,   
bill_date DATE PRIMARY KEY NOT NULL, amount DECIMAL(8,2) NOT NULL)   
PARTITION BY RANGE (year(bill_date))(   
PARTITION p0 VALUES LESS THAN (2016),   
PARTITION p1 VALUES LESS THAN (2017),   
PARTITION p2 VALUES LESS THAN (2018),   
PARTITION p3 VALUES LESS THAN (2020));  

Lets first add values to the Sales table using the INSERT INTO command.

INSERT INTO Sales VALUES   
(1, 'Mike', 'S001', 101, '2015-01-02', 125.56),   
(2, 'Robert', 'S003', 103, '2015-01-25', 476.50),   
(3, 'Peter', 'S012', 122, '2016-02-15', 335.00),   
(4, 'Joseph', 'S345', 121, '2016-03-26', 787.00),   
(5, 'Harry', 'S234', 132, '2017-04-19', 678.00),   
(6, 'Stephen', 'S743', 111, '2017-05-31', 864.00),   
(7, 'Jacson', 'S234', 115, '2018-06-11', 762.00),   
(8, 'Smith', 'S012', 125, '2019-07-24', 300.00),   
(9, 'Adam', 'S456', 119, '2019-08-02', 492.20);  

To see the RANGE partition created by CREATE TABLE statement, you run the following below query:

SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH  
FROM INFORMATION_SCHEMA.PARTITIONS  
WHERE TABLE_SCHEMA = 'myemployeedb' AND TABLE_NAME = 'Sales';  

The output for the above query on a table with RANGE partitions is as follows:

2) HASH MySQL Partitions

In HASH Partitions, MySQL separates data using the value that a user-defined expression returns. The user-defined expression can contain any valid MySQL expression that can generate a nonnegative integer value as output. The HASH Partitions is used when you wish to evenly distribute the data among multiple partitions. Moreover, the HASH Partitions automates the process of defining partition location using the INT value of selected columns. The below example shows the syntax of HASH Partitions.

CREATE TABLE Stores (   
    cust_name VARCHAR(40),   
    bill_no VARCHAR(20) NOT NULL,   
    store_id INT PRIMARY KEY NOT NULL,   
    bill_date DATE NOT NULL,   
    amount DECIMAL(8,2) NOT NULL  
)  
PARTITION BY HASH(store_id)  
PARTITIONS 4;  

3) KEY MySQL Partitions

The KEY Partitions operate similar to HASH Partitions, however, the partition function here is supplied by MySQL itself. A KEY Partition also allows columns, containing non-integer values. It will always return an integer value, regardless of the column data type.

However, if the table has a primary key and the user hasn’t mentioned any column for partition, then the primary key is treated as the partition key. The following example shows the syntax of KEY Partitions:

CREATE TABLE AgentDetail (   
    agent_id INT NOT NULL PRIMARY KEY,  
    agent_name VARCHAR(40)  
)  
PARTITION BY KEY()  
PARTITIONS 2;  

If the table has a unique key and not a primary key. And also, the user hasn’t mentioned any column for partition, then the unique key is treated as the partition key. The following sxample shows the syntax of KEY partition.

CREATE TABLE AgentDetail (   
    agent_id INT NOT NULL UNIQUE KEY,  
    agent_name VARCHAR(40)  
)  
PARTITION BY KEY()  
PARTITIONS 2;  

4) LIST MySQL Partitions

LIST Partitions operate like RANGE Partitions however, they use column matching with a set of discrete values to decide how to slit up the data. The VALUES IN statement is used to define the matching criteria. However, you need to manually define the location where the partitioned data is to be stored.

Suppose you have 12 stores distributed across different locations. This table gives a clear view of the distribution.

Region	Store ID Number
East	101, 103, 105
West	102, 104, 106
North	107, 109, 111
South	108, 110, 112

The following example shows the syntax of LIST Partitions.

CREATE TABLE Stores (   
    cust_name VARCHAR(40),   
    bill_no VARCHAR(20) NOT NULL,   
    store_id INT PRIMARY KEY NOT NULL,   
    bill_date DATE NOT NULL,   
    amount DECIMAL(8,2) NOT NULL 
)
PARTITION BY LIST(store_id) (   
PARTITION pEast VALUES IN (101, 103, 105),   
PARTITION pWest VALUES IN (102, 104, 106),   
PARTITION pNorth VALUES IN (107, 109, 111),   
PARTITION pSouth VALUES IN (108, 110, 112));  

5)COLUMNS MySQL Partitions

Multiple columns can be used as partitioning keys in COLUMNS partitioning. In addition, non-integer columns can be used to define value ranges or list members in both RANGE COLUMNS and LIST COLUMNS partitioning.

• RANGE COLUMNS partitioning is similar to range partitioning but differs in some ways. As partition keys, RANGE COLUMNS accepts a list of one or more columns. Other than integer types, you can define ranges using various columns of types (mentioned above).

The following is the basic syntax for partitioning a table by RANGE COLUMNS:

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
) 

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

A list of one or more columns is called a “column_list.“
Each partition definition requires a “value_list,” which is a list of values.
Each partition’s column list and value list must appear in the same order.
The order of the partitioning column list and the value lists does not have to match the order of the table column definitions in the CREATE TABLE statement.

Lets go through the following example:

CREATE TABLE test_part (A INT, B CHAR(5), C INT, D INT)  
PARTITION BY RANGE COLUMNS(A, B, C)   
 (PARTITION p0 VALUES LESS THAN (50, 'test1', 100),   
 PARTITION p1 VALUES LESS THAN (100, 'test2', 200),   
 PARTITION p2 VALUES LESS THAN (150, 'test3', 300),   
 PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));  

In this instance, the “test_part” table consists of four columns, namely A, B, C, and D. To partition the table, we have utilized the first three columns in the order of A, B, C. Each list value defines a partition comprising three values in the same order as INT, CHAR, and INT. Upon executing this query, we will obtain the following output, which we have validated using a SELECT statement.

• As partition keys, LIST COLUMNS accepts a list of one or more columns. As partitioning columns, you can use a variety of data columns that aren’t integer types. String types, DATE, and DATETIME columns can all be used.

For sales and marketing purposes, the company has agents in three cities. The agents have been divided into three cities, as shown in the table below:

City	Agent ID
A	A1, A2, A3
B	B1, B2, B3
C	C1, C2, C3, C4, C5

Based on the above data, let’s create a table with LIST COLUMNS partitioning:

mysql> CREATE TABLE salemast ( agent_id VARCHAR(15), agent_name VARCHAR(50), 
agent_address VARCHAR(100), city_code VARCHAR(10)) 
PARTITION BY LIST COLUMNS(agent_id) ( 
PARTITION pcity_a VALUES IN('A1', 'A2', 'A3'), 
PARTITION pcity_b VALUES IN('B1', 'B2', 'B3'), 
PARTITION pcity_c VALUES IN ('C1', 'C2', 'C3', 'C4', 'C5'));
Query OK, 0 rows affected (1.06 sec)

6)MySQL Subpartitioning

Subpartitioning is a technique for dividing each partition in a partitioned table further. Take a look at the CREATE TABLE statement below:

CREATE TABLE table10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15), 
AMOUNT DECIMAL(8,2))
PARTITION BY RANGE(YEAR(sale_date) )
SUBPARTITION BY HASH(TO_DAYS(sale_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

In the preceding statement –

The table is divided into four RANGE partitions.
Each of these partitions (p0, p1, p2, and p3) is divided into four subpartitions.
As a result, the table is partitioned into 4 * 4 = 16 partitions.

An example output for MySQL partitioning can be the following:

What are the Advantages of MySQL Partitions?

Optimizing your data storage by utilizing the MySQL Partitions will provide you with the following advantages:

• During the scan operation, the MySQL engine does not have to search through the entire data to extract data. Instead, it can directly locate the partitioning where the relevant data is stored and scan through it. This way Partition can optimize the scan process.

• Partitioning provides you with greater control over the data operations. Depending on your needs, you can drop or add data from specific partitions without disturbing the other portions of your original data. For example, you can use the ALTER TABLE command to add new data to a partition easily.

• Partitioning facilitates optimal data storage. You can even store more data in one table than a single disk or file system can hold.

• MySQL’s new versions also support explicit partition selection using SQL queries to speed up the processing. An example of this feature is the SELECT * FROM table1 PARTITION (A, B) WHERE col1< 10 command. It will select only those rows in partitions A and B that satisfy the WHERE condition.

• Partition supports data modification commands such as DELETE, INSERT, REPLACE, UPDATE, etc Moreover you can perform Partition Pruning to exclude non-matching partitions from a search.

What are the Best Practices for MySQL Partitions?

Now, that you have understood the types and benefits of MySQL Partitions, you are ready to work with them. The following practices will ensure the best possible results for your data while working with MySQL Partitions:

• When implementing MySQL Partitions, try to find a natural partition key. This will ensure that table lookups will return the correct partition on querying. So, all SELECT, UPDATE, DELETE commands should include that natural key(column) in the WHERE clause so that the storage engine does not query all partitions in a non-concurrent UNION.

• You should always add your partition key along with the auto-increment and form a composite primary key. This way your secondary indexes won’t enlarge and waste storage.

• You should choose between RANGE and HASH Partitions depending on the stored data and expected output. The RANGE  Partitions is ideal when you have a set of known IDs in every table. It will simplify your task of querying across partition IDs. However, if you wish to add new data to the partition, using the HASH Partitions will be the better choice. This way you can balance the load of incoming data and write to partitions more concurrently. 

What are the Restrictions and Limitations on Partitioning?

• Prohibited Constructs: In partitioning expressions, the following constructs are not allowed:
  • Stored procedures, stored functions, loadable functions, and plugins are all examples of stored procedures.
  • Variables that have been declared or variables that have been entered by the user.

• Arithmetic and Logical Operators: In partitioning expressions, the arithmetic operators +, -, and * are permitted. The result, however, must be an integer or NULL. The DIV operator is also supported, but not the / operator. In partitioning expressions, the bit operators  |, &, ^, <<, >>, and ~ , and are not allowed.

• Server SQL Mode: Tables with user-defined partitioning don’t keep the SQL mode that was in effect when they were created. The results of many MySQL functions and operators can vary depending on the server SQL mode, as discussed elsewhere in this Manual. As a result, changing the SQL mode after partitioned tables are created can cause significant changes in their behavior, and can easily result in data corruption or loss. For these reasons, changing the server SQL mode after creating partitioned tables is strongly discouraged. Consider the following CREATE TABLE statement, which can be successfully executed only if the NO UNSIGNED SUBTRACTION mode is in effect, and which makes partitioned tables unusable:

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1563 (HY000): Partition constant is out of partition function domain

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------+
| @@sql_mode              |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.05 sec)

You may not be able to access this table if you remove the NO UNSIGNED SUBTRACTION server SQL mode after creating tu:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tu;
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> INSERT INTO tu VALUES (20);
ERROR 1563 (HY000): Partition constant is out of partition function domain

Replication of partitioned tables is also affected by server SQL modes. Different SQL modes on the source and replica can cause partitioning expressions to be evaluated differently, resulting in different data distribution among partitions in the source’s and replica’s copies of a given table, and even causing inserts into partitioned tables to fail on the replica. You should always use the same server SQL mode on the source and replica for the best results.

• Performance Considerations: The following is a list of performance effects of partitioning operations:
  • File System Operations: File system operations are required for partitioning and repartitioning operations (such as ALTER TABLE with PARTITION BY…, REORGANIZE PARTITION, or REMOVE PARTITIONING). This means that file system type and characteristics, disc speed, swap space, operating system file handling efficiency, and MySQL server options and variables related to file handling all affect the speed of these operations. Make sure large files support is enabled and the open files limit is set correctly. By enabling InnoDB file per table, partitioning and repartitioning operations involving InnoDB tables can be made more efficient.
  • Table Locks: The process that performs a partitioning operation on a table usually takes a write lock on the table. Reads from such tables are unaffected, and any pending INSERT and UPDATE operations are completed as soon as the partitioning operation is complete.
  • Indexes; Partition Pruning: Indexes can significantly speed up queries on partitioned tables, just as they can on nonpartitioned tables. Furthermore, using partition pruning to design partitioned tables and queries on these tables can dramatically improve performance.
  • Performance with LOAD DATA: LOAD DATA in MySQL 8.0 uses buffering to improve performance. It’s important to note that the buffer uses 130 KB of memory per partition to accomplish this.
• Maximum Number of Partitions: For a table that does not use the NDB storage engine, the maximum number of partitions is 8192. Subpartitions are included in this total. The number of user-defined partitions that can be created for a table using the NDB storage engine is determined by the version of the NDB Cluster software being used, the number of data nodes, and other factors. For more information, see NDB and user-defined partitioning. If you get an error message like “Got error… from storage engine:” when creating tables with a large number of partitions (but less than the maximum), it’s because you’re using the wrong storage engine. If you’re having trouble opening files because you’re running out of resources, try increasing the value of the open_files_limit system variable. This, however, is dependent on the operating system and may not be possible or recommended on all platforms. Using a large number of partitions (hundreds) may not be advisable in some cases due to other concerns, so using more partitions does not always imply better results.

• Foreign Keys not Supported for Partitioned InnoDB Tables: Foreign keys are not supported in partitioned tables that use the InnoDB storage engine. In more detail, this implies that the following two statements are correct:
  • No foreign key references may be used in the definition of an InnoDB table that uses user-defined partitioning;
  • No foreign key references may be used in the definition of an InnoDB table that uses user-defined partitioning.

All tables that use the InnoDB storage engine are covered by the restrictions listed above. Tables that violate these restrictions are not allowed to be created or altered using the CREATE TABLE and ALTER TABLE statements.

ORDER BY… ALTER TABLE When an ALTER TABLE… ORDER BY column statement is applied to a partitioned table, only the rows within each partition are ordered.

ALGORITHM=INSTANT… ADD COLUMN After you’ve run ALTER TABLE… When you use ADD COLUMN… ALGORITHM=INSTANT on a partitioned table, you can no longer exchange partitions.

• Effects on REPLACE Statements by Modification of Primary Keys: In some cases, changing a table’s primary key is necessary. Be aware that if you do this and your application uses REPLACE statements, the results of these statements may be drastically altered.
• FULLTEXT Indexes: FULLTEXT indexes. Partitioned tables do not support FULLTEXT indexes or searches.
• Spatial Columns: In partitioned tables, columns with spatial data types like POINT or GEOMETRY aren’t allowed.
• Temporary Tables: Partitioning is not possible with temporary tables.
• Log Tables: The log tables cannot be partitioned; an ALTER TABLE… PARTITION BY… statement on such a table returns an error.
• Data Type of Partitioning Key: An integer column or an expression that resolves to an integer must be used as a partitioning key. ENUM columns cannot be used in expressions. The value of the column or expression can also be NULL. This limitation is subject to two exceptions:
  • It is possible to use columns of any valid MySQL data type other than TEXT or BLOB as partitioning keys when partitioning by [LINEAR] KEY because the internal key-hashing functions produce the correct data type from these types. The following two CREATE TABLE statements, for instance, are correct:

CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

CREATE TABLE tke
    ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
PARTITION BY LINEAR KEY(c1)
PARTITIONS 6;

String, DATE, and DATETIME columns can be used when partitioning by RANGE COLUMNS or LIST COLUMNS. Each of the following CREATE TABLE statements, for example, is valid:

CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
    PARTITION p0 VALUES LESS THAN('1990-01-01'),
    PARTITION p1 VALUES LESS THAN('1995-01-01'),
    PARTITION p2 VALUES LESS THAN('2000-01-01'),
    PARTITION p3 VALUES LESS THAN('2005-01-01'),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

• Subqueries: Even if the subquery resolves to an integer value or NULL, a partitioning key cannot be a subquery.
• Column Index Prefixes not Supported for Key Partitioning: Any columns in the partitioning key that use column prefixes are not used in the table’s partitioning function when creating a table partitioned by key. Consider the CREATE TABLE statement below, which has three VARCHAR columns, a primary key that uses all three columns, and prefixes for two of them.

CREATE TABLE t1 (
    a VARCHAR(10000),
    b VARCHAR(25),
    c VARCHAR(10),
    PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;

This statement is accepted, but the resulting table is created as if you had issued the following statement, using only the primary key column (column b) for the partitioning key and not including a prefix:

CREATE TABLE t1 (
    a VARCHAR(10000),
    b VARCHAR(25),
    c VARCHAR(10),
    PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY(b) PARTITIONS 2;

Prior to MySQL 8.0.21, there was no warning or other indication that this had happened, unless all of the columns specified for the partitioning key were prefixes, in which case the statement failed with a misleading error message, as shown here:

mysql> CREATE TABLE t2 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b(5), c(2))
    -> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the
table's partitioning function

• File Name Delimiters for Partitions and Subpartitions: File names for table partitions and subpartitions include generated delimiters like #P# and #SP#. The letter case of such delimiters can vary, so don’t count on it.

Conclusion

The article introduced you to MySQL and explained its important features. It then introduced you to MySQL Partitions and discussed its various types with their syntax. The article also listed the advantages of implementing MySQL Partitions on your data. Finally, it provided the best practices that you must keep in mind while using MySQL Partitions.

Share your understanding of MySQL Partitions in the comments below!