MySQL Partition Table limitations and restrictions

MySQL Partition Table limitations and restrictions

Build prohibited

Partition expressions do not support the following types of construction:

Stored Procedures, stored functions, UDFS, or plug-ins

Declare variables or user Variables

See SQL functions not supported by partitions.

Arithmetic and logical operators

Partition expressions support +,-, and * arithmetic operations, but do not support DIV and/operations (you can check bugs #30188, Bug #33182 ). However, the result must be an integer or NULL (except for the linear partition key. For more information, see partition type ).

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

HANDLER statement

Partition tables earlier than MySQL 5.7.1 do not support HANDLER statements. Later versions cancel this restriction.

Server SQL Mode

If you want to use a user-defined partition table, note that the SQL mode is not reserved when creating a partition table. As discussed in the server SQL mode chapter, the results of most MySQL functions and operators may change according to the server SQL mode. Therefore, once the SQL mode changes after the partition table is created, the behavior of these tables may change significantly, which may easily lead to data loss or damage. We strongly recommend that you do not modify the SQL mode of the server after creating a partition table.

Here is an example to illustrate the above situation:

1. handle errors

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 MySQL by 0 is NULL instead of 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 modify the SQL mode, an error is returned:

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. Auxiliary table functions

Sometimes modifying the SQL mode may make the Partition Table unavailable. For example, some tables only play a role when the SQL mode is NO_UNSIGNED_SUBTRACTION. For example:

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 modify the SQL mode after creating the tu, you may no longer be able to access this 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 also affects partition table replication. Using different SQL modes between the master and slave nodes may result in different execution results of the partition expression on the master and slave nodes (while switching between the master and slave nodes is normal ); this will also lead to different data distribution in different intervals during the master-slave replication process; it may also lead to the successful insert of the partition table on the master database and the failure of the slave database. Based on the above situation, the best solution is to ensure the consistency of the SQL mode between the master and slave databases (this is what DBAs should pay attention to during the O & M process ).

Performance Considerations

The following are some factors that affect the partition operation performance:

File System Operations
PARTITIONING or re-PARTITIONING (such as alter table... partition by..., reorganize partition, or remove partitioning) operations depend on the implementation of the file system. This means that the above operations will be performed on the operating system, such as file system types and features, disk speed, swap space, and file processing efficiency on the operating system, and file handle related options and variables on the MySQL server. Note that large_files_support is enabled and open_files_limit settings are reasonable. For a partition table of the MyISAM engine, you need to add myisam_max_sort_file_size to improve performance. For an InnoDB table, partitioning or re-partitioning is faster through enabled innodb_file_per_table.

You can also refer to the maximum number of partitions.

MyISAM and partition file descriptor

For MyISAM partition tables, MySQL uses two file descriptors for each opened table and each partition. This means that the MyISAM Partition TABLE requires more file descriptors to perform operations (especially the alter table operation) 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 100ENGINE=MYISAM;

In simple terms, in this example, although we use KEY partitions, but the file descriptor problem will occur in all partitions using the table engine MyISAM, regardless of the partition type. However, this problem does not occur when you use partition tables of other storage engines (such as InnoDB.

Suppose you want to repartition t and make it have 101 partitions, use the following statement:

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

If the alter table statement requires 402 file descriptors, the original 100 partitions X 2 + 101 new partitions x 2. This is because all (new and old) Partitions must be opened when the table data is re-organized. Therefore, we recommend that you set -- open-files-limit to a greater value when performing these operations.

Table lock

The process of performing the partition operation on a table occupies the write lock of the table and does not affect reading. For example, the 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, usually use the MyISAM engine faster than InnoDB and NDB.

Index and partition pruning

Like non-Partitioned Tables, using indexes can significantly increase the query speed. In addition, partition pruning can be used to significantly improve performance by designing partition tables and querying these tables.

Before MySQL 5.7.3, partition tables do not support index push-down. Later versions support this feature.

Load data Performance

In MySQL 5.7, load data uses buffer to improve performance. What you need to know is that buffer occupies KB of each partition to achieve this goal.

Maximum number of partitions

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

If you use NDB as the storage engine, the maximum number of custom partitions depends on the MySQL Cluster version, data nodes, and other factors.

If you create a partition with a very large number (smaller than the maximum number of partitions), such as Got error... from storage engine: Out of resources when opening file class error, you may need to add open_files_limit. However, open_files_limit actually depends on the operating system, and may not be recommended for all platforms. In other cases, we do not recommend using a large or hundreds of thousands of partitions. Therefore, using more and more partitions does not necessarily produce good results.

Query cache not supported

Partition tables do not support query cache. query cache is automatically avoided in partition table queries. That is to say, the query cache does not work in the query statement of the partition table.

One key caches per Partition

In MySQL 5.7, the key CACHE of the MyISAM partition table can be used through cache index and load index into cache. You can define a key cache for one, several, or all partitions so that you can pre-load the indexes of one, several, or all partitions to the key cache.

Foreign keys of InnoDB partition tables are not supported

Partition tables using the InnoDB Engine do not support foreign keys. The following two situations are described:

In an InnoDB table, you cannot use a custom partition containing a foreign key. If an InnoDB table already uses a foreign key, it cannot be partitioned.

The InnoDB table cannot contain a foreign key related to the user-defined partition table. the InnoDB table that uses the user-defined partition cannot contain columns related to the foreign key.

The limitations listed above include all tables using the InnoDB Storage engine. The create table and alter table statements that violate these restrictions are not allowed.

Alter table... ORDER

If alter table... order by is executed on a partitioned TABLE, the rows in each partition are sorted.

Efficiency of REPLACE Statement on modifying primary key

In some cases, you need to modify the table's primary key. If your application uses the REPLACE statement, the results of these statements may be greatly modified.

Full-text index

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

Space Column

Partition tables do not support spatial columns, such as vertices or ry.

Temporary table

Temporary tables cannot be partitioned (Bug #17497 ).

Log table

You cannot PARTITION a log TABLE. If you force alter table... partition by..., an error is returned.

Partition key data type

The partition key must be an integer or an integer expression. An ENUM-type expression cannot be used. This type of expression may be NULL.

The following two situations are exceptions:

When using LINER to partition, you can use data types other than TEXT or BLOBS as the partition key, because the hash function in MySQL will generate the correct data type from these columns. For example, the following statement is valid:

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;

If RANGE, LIST, DATE, or DATETIME column is used for partitioning, string may be used. For example, the following statement 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));

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

Subquery

The partition key cannot be used for a subquery without the integer or NULL value.

Subpartition Problems

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

Subpartition by key requires that the SUBPARTITION column be displayed. It cannot be omitted from partition by key (in this case, the table's primary key is used BY default ). For example, to create a table like this:

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

You can also use the same column to create a partition table (partitioned by KEY) and use the following statement:

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

The preceding 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 use the default column as the sub-partition column to create a sub-partition table, the following statement fails. You must specify this statement to successfully execute the statement, as shown in the following figure: (bug known 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 thatcorresponds 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 factors:

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

On Windows, MyISAM partition tables do not support data dictionary and index dictionary options for independent partitions or subpartitions. However, InnoDB Partitioned Tables support data dictionaries for independent partitions or subpartitions.

Fix and recreate a partition table

Partitioned Tables support check table, optimize table, analyze table, and repair table statements.

In addition, you can also use alter table... rebuild partition to re-create one or more partitions on a partition table. You can also use alter table... reorganize partition to re-create partitions.

From MySQL 5.7.2, sub-partitions support ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE operations. The REBUILD syntax has been introduced in versions earlier than MySQL 5.7.5, but does not work (refer to Bug #19075411, Bug #73130 ).

Partition tables do not support mysqlcheck, myisamchk, and myisampack operations.

Export options

MySQL versions earlier than MySQL 5.7.4 do not support export options for the flush tables Statement of InnoDB partition TABLES (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/