Detailed explanation of the limitations and restrictions of MySQL partitioned tables

Disable Build

Partition expressions do not support the following constructs:

Stored procedures, stored functions, UDFS or plug-ins

Declare variables or user variables

Please refer to SQL functions not supported by partitions.

Arithmetic and logical operators

Partition expressions support +, -, and * arithmetic operations, but do not support DIV and / operations (this issue still exists, see Bug #30188, Bug #33182). However, the result must be an integer or NULL (except for linear partition keys, for more information, see Partition Types).

Partition expressions do not support bitwise operations: |, &, ^, <<, >>, ~.

HANDLER Statement

Partitioned tables before MySQL 5.7.1 do not support the HANDLER statement. This limitation has been removed in later versions.

Server SQL Mode

If you want to use a user-defined partitioned table, please note that the SQL mode when creating the partitioned table is not retained. As discussed in the Server SQL Modes chapter, the results of most MySQL functions and operators may change depending on the server SQL mode. Therefore, once the SQL mode is changed after creating partitioned tables, it may cause significant changes in the behavior of these tables, which can easily lead to data loss or corruption. For the above reasons, it is strongly recommended that you never change the server's SQL mode after creating a partitioned table.

Let's take an example to illustrate the above situation:

1. Error handling

mysql> CREATE TABLE tn (c1 INT)
  -> PARTITION BY LIST(1 DIV c1) (
  -> PARTITION p0 VALUES IN (NULL),
  -> PARTITION p1 VALUES IN (1)
  -> );
  Query OK, 0 rows affected (0.05 sec)

By default, the result of dividing by 0 in MySQL is NULL, rather than an error:

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
 
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

However, if we change the SQL mode, an error will be reported:

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0

2. Table auxiliary functions

Sometimes changing the SQL mode may make the partitioned table unusable. For example, some tables only work when the SQL mode is NO_UNSIGNED_SUBTRACTION, such as:

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)

If you change the SQL mode after creating tu, you may no longer be able to access the table:

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

The SQL mode on the server side also affects the replication of partitioned tables. Using different SQL modes between the master and slave databases may result in different results when executing partition expressions on the master and slave databases (although master-slave switching is a normal operation in Alibaba). This may also result in different data distribution between different partitions during the master-slave replication process. It may also cause the partition table insert to succeed on the master database but fail on the slave database. Based on the above situation, the best solution is to ensure that the SQL mode between the primary and backup servers is consistent (this is what the DBA needs to pay attention to during operation and maintenance).

Performance Considerations

The following are some factors that affect the performance of partition operations:

The file system operations that partition or repartition (such as ALTER TABLE ... PARTITION BY ..., REORGANIZE PARTITION, or REMOVE PARTITIONING) perform depend on the file system implementation. This means that the above operations will be affected by factors on the operating system, such as: the type and characteristics of the file system, disk speed, swap space, file processing efficiency on the operating system, and options and variables related to file handles on the MySQL server. It is important to note that you need to ensure that large_files_support is enabled and that the open_files_limit setting is reasonable. For MyISAM partitioned tables, you need to increase myisam_max_sort_file_size to improve performance; for InnoDB tables, partitioning or repartitioning operations will be faster with enabled innodb_file_per_table.

See also Maximum number of partitions.

MyISAM and partition file descriptors

For MyISAM partitioned tables, MySQL uses two file descriptors per partition for each open table. This means that performing operations on a partitioned MyISAM table (particularly ALTER TABLE operations) requires more file descriptors than the same table without partitions.

Suppose we want to create a MyISAM table with 100 partitions. The statement is as follows:

CREATE TABLE t (c1 VARCHAR(50))
PARTITION BY KEY (c1) PARTITIONS 100
ENGINE=MYISAM;

In short, in this example, although we use KEY partitioning, the problem of file descriptors will be encountered in all partitions that use the MyISAM table engine, regardless of the partition type. However, partitioned tables using other storage engines (such as InnoDB) do not have this problem.

Suppose you want to repartition t so that it has 101 partitions, use the following statement:

ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;

If ALTER TABLE statements are to be processed, 402 file descriptors are required, i.e. 2 for the original 100 partitions + 2 for the 101 new partitions. This is because when reorganizing table data, all partitions (old and new) must be opened. It is therefore recommended that when performing these operations, make sure that --open-files-limit is set larger.

Table Lock

The process of performing partition operations on the table will occupy the write lock of the table, which does not affect the read. For example, INSERT and UPDATE operations on these partitions can only be performed after the partition operation is completed.

Storage Engine

Partition operations, such as query and update operations, are usually faster with the MyISAM engine than with InnoDB and NDB.

Indexes; Partition Pruning

Like non-partitioned tables, partitioned tables can significantly improve query speed by using indexes properly. Additionally, designing partitioned tables and queries on these tables can take advantage of partition pruning to significantly improve performance.

Prior to MySQL 5.7.3, partitioned tables did not support index condition pushdown, but later versions support it.

Load data performance

In MySQL 5.7, load data uses buffer to improve performance. What you need to know is that the buffer will take up 130KB of each partition to achieve this purpose.

Maximum number of partitions

If the partition table does not use NDB as the storage engine, the maximum number of partitions (including subpartitions) supported is 8192.

The maximum number of partitions for user-defined partitioning if NDB is used as the storage engine depends on the MySQL Cluster version, data nodes, and other factors.

If you create a very large number of partitions (less than the maximum number of partitions) and encounter errors such as Got error ... from storage engine: Out of resources when opening file, you may need to increase open_files_limit. However, open_files_limit actually depends on the operating system, and it may not be recommended to adjust it on all platforms. There are other situations where it is not recommended to use huge or hundreds of partitions, so using more and more partitions may not necessarily bring good results.

Does not support query cache

Partitioned tables do not support query cache, and queries on partitioned tables automatically avoid query cache. That is to say, query cache does not work in the query statement of the partition table.

One key cache per partition

In MySQL 5.7, you can use the key cache of MyISAM partitioned tables through CACHE INDEX and LOAD INDEX INTO CACHE. A key cache can be defined for one, several, or all partitions, so that the indexes of one, several, or all partitions can be preloaded into the key cache.

Foreign keys on InnoDB partitioned tables are not supported

Partitioned tables using the InnoDB engine do not support foreign keys. The following two specific situations are explained:

You cannot use custom partitioning with foreign keys in InnoDB tables; if an InnoDB table already uses foreign keys, it cannot be partitioned.

An InnoDB table cannot contain a foreign key associated with a user-defined partitioned table; an InnoDB table that uses user-defined partitions cannot contain columns associated with foreign keys.

The scope of the restrictions just listed includes all tables that use the InnoDB storage engine. CREATE TABLE and ALTER TABLE statements that violate these restrictions are not allowed.

ALTER TABLE ... ORDER BY

Executing ALTER TABLE ... ORDER BY on a partitioned table results in the rows of each partition being sorted.

Efficiency of REPLACE statement in modifying primary key

In some cases it is necessary to modify the primary key of a table. If your application uses REPLACE statements, the results of these statements may be significantly modified.

Full-text index

Partitioned tables do not support full-text indexing or searching, even if the storage engine for the partitioned table is InnoDB or MyISAM.

Spatial Column

Partitioned tables do not support spatial columns such as point or geometry.

Temporary Tables

Temporary tables cannot be partitioned (Bug #17497).

Log table

The log table cannot be partitioned. If you force the ALTER TABLE ... PARTITION BY ... statement to be executed, an error will be reported.

Data type of partition key

The partition key must be an integer or an expression that evaluates to an integer. You cannot use expressions that result in an ENUM type. Because expressions of this type may be NULL.

There are two exceptions:

When using LINER partitioning, you can use data types other than TEXT or BLOBS as partitioning keys because MySQL's internal hash functions will generate the correct data type from these columns. For example, the following create statements are legal:

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;

When partitioning by RANGE, LIST, DATE, or DATETIME columns, string may be used. For example, the following create statements are legal:

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)
);

None of the above exceptions apply to BLOB or TEXT column types.

Subqueries

Even if the subquery avoids integer or NULL values, the partition key cannot be subqueried.

The problem with subpartitioning

Subpartitions must use HASH or KEY partitions. Only RANGE and LIST partitions support subpartitions; HASH and KEY do not support subpartitions.

SUBPARTITION BY KEY requires explicit specification of the subpartition columns, unlike PARTITION BY KEY which can be omitted (in which case the table's primary key is used by default). For example, if you create the table like this:

CREATE TABLE ts (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
);

You can also create a partitioned table (partitioned by KEY) using the same columns, using the following statement:

CREATE TABLE ts (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;

The previous statement is actually the same as the following statement:

CREATE TABLE ts (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;
 

However, if you try to create a subpartitioned table using the default column as the subpartition column, the following statement will fail and must be specified for the statement to succeed, as shown below: (Bug known as Bug #51470).

mysql> CREATE TABLE ts (
  -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -> name VARCHAR(30)
  -> )
  -> PARTITION BY RANGE(id)
  -> SUBPARTITION BY KEY()
  -> SUBPARTITIONS 4
  -> (
  -> PARTITION p0 VALUES LESS THAN (100),
  -> PARTITION p1 VALUES LESS THAN (MAXVALUE)
  -> );
   
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
 
mysql> CREATE TABLE ts (
  -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -> name VARCHAR(30)
  -> )
  -> PARTITION BY RANGE(id)
  -> SUBPARTITION BY KEY(id)
  -> SUBPARTITIONS 4
  -> (
  -> PARTITION p0 VALUES LESS THAN (100),
  -> PARTITION p1 VALUES LESS THAN (MAXVALUE)
  -> );
   
Query OK, 0 rows affected (0.07 sec)

Data dictionary and index dictionary options

The data dictionary and index dictionary of a partitioned table are subject to the following constraints:

Table-level data dictionary and index dictionary are ignored (Bug #32091)

On Windows systems, MyISAM partitioned tables do not support data dictionary and index dictionary options for individual partitions or subpartitions. However, it supports data dictionaries for independent partitions or subpartitions of InnoDB partitioned tables.

Repair and rebuild partition table

Partitioned tables support the CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE statements.

Alternatively, you can use ALTER TABLE ... REBUILD PARTITION to rebuild one or more partitions on a partitioned table; you can also use ALTER TABLE ... REORGANIZE PARTITION to rebuild partitions.

As of MySQL 5.7.2, subpartitions support ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE operations. The REBUILD syntax has been introduced in versions prior to MySQL 5.7.5, but it does not work (see Bug #19075411, Bug #73130).

mysqlcheck, myisamchk, and myisampack operations are not supported on partitioned tables.

Export Options

In MySQL versions prior to 5.7.4, the export option of the FLUSH TABLES statement for InnoDB partitioned tables is not supported (Bug #16943907).

References

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html

https://www.slideshare.net/datacharmer/mysql-partitions-tutorial/34-Partition_pruning_unpartitioned_tableexplain_partitions

https://www.percona.com/blog/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/