[+/-]
This section discusses current restrictions and limitations on MySQL partitioning support, as listed here:
Prohibited constructs. The following constructs are not permitted in partitioning expressions:
Stored functions, stored procedures, UDFs, or plugins.
Declared variables or user variables.
For a list of SQL functions which are permitted in partitioning expressions, see Section 17.5.3, “Partitioning Limitations Relating to Functions”.
Arithmetic and logical operators.
Use of the arithmetic operators
+
,
–
, and
*
is
permitted in partitioning expressions. However, the result
must be an integer value or NULL
(except
in the case of [LINEAR] KEY
partitioning,
as discussed elswhere in this chapter — see
Section 17.2, “Partition Types”, for more information).
The DIV
operator is also
supported, and the
/
operator is
disallowed. (Bug#30188, Bug#33182)
The bit operators
|
,
&
,
^
,
<<
,
>>
,
and ~
are not permitted in partitioning expressions.
Server SQL mode. Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. As discussed in Section 5.1.8, “Server SQL Modes”, the results of many MySQL functions and operators may change according to the server SQL mode. Therefore, a change in the SQL mode at any time after the creation of partitioned tables may lead to major changes in the behavior of such tables, and could easily lead to corruption or loss of data. For these reasons, it is strongly recommended that you never change the server SQL mode after creating partitioned tables.
Examples. The following examples illustrate some changes in behavior of partitioned tables due to a change in the server SQL mode:
Error handling.
Suppose that you create a partitioned table whose
partitioning expression is one such as
or
column
DIV
0
, as shown here:
column
MOD
0
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)
The default behavior for MySQL is to return
NULL
for the result of a division by
zero, without producing any errors:
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, changing the server SQL mode to treat division by
zero as an error and to enforce strict error handling
causes the same INSERT
statement to fail, as shown here:
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
Table accessibility.
Sometimes a change in the server SQL mode can make
partitioned tables unusable. The following
CREATE TABLE
statement
can be executed successfully only if the
NO_UNSIGNED_SUBTRACTION
mode is in effect:
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 remove the
NO_UNSIGNED_SUBTRACTION
server SQL mode after creating 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
Server SQL modes also impact replication of partitioned tables. Differing SQL modes on master and slave can lead to partitioning expressions being evaluated differently; this can cause the distribution of data among partitions to be different in the master's and slave's copies of a given table, and may even cause inserts into partitioned tables that succeed on the master to fail on the slave. For best results, you should always use the same server SQL mode on the master and on the slave.
Performance considerations. Some affects of partitioning operations on performance are given in the following list:
File system operations.
Partitioning and repartitioning operations (such as
ALTER TABLE
with
PARTITION BY ...
, REORGANIZE
PARTITIONS
, or REMOVE
PARTITIONING
) depend on file system operations
for their implementation. This means that the speed of
these operations is affected by such factors as file
system type and characteristics, disk speed, swap space,
file handling efficiency of the operating system, and
MySQL server options and variables that relate to file
handling. In particular, you should make sure that
large_files_support
is
enabled and that
open_files_limit
is set
properly. For partitioned tables using the
MyISAM
storage engine, increasing
myisam_max_sort_file_size
may improve performance; partitioning and repartitioning
operations involving InnoDB
tables
may be made more efficient by enabling
innodb_file_per_table
.
See also Maximum number of partitions.
Table locks.
The process executing a partitioning operation on a
table takes a write lock on the table. Reads from such
tables are relatively unaffected; pending
INSERT
and
UPDATE
operations are
performed as soon as the partitioning operation has
completed.
Storage engine.
Partitioning operations, queries, and update operations
generally tend to be faster with
MyISAM
tables than with
InnoDB
or
NDB
tables.
Use of indexes and partition pruning. As with nonpartitioned tables, proper use of indexes can speed up queries on partitioned tables significantly. In addition, designing partitioned tables and queries on these tables to take advantage of partition pruning can improve performance dramatically. See Section 17.4, “Partition Pruning”, for more information.
Performance with LOAD DATA
.
In MySQL 5.4, LOAD
DATA
uses buffering to improve performance.
You should be aware that the buffer uses 130 KB memory
per partition to achieve this.
Maximum number of partitions. The maximum possible number of partitions for a given table is 1024. This includes subpartitions.
If, when creating tables with a large number of partitions
(but less than the maximum), you encounter an error message
such as Got error ... from storage engine: Out of
resources when opening file, you may be able to
address the issue by increasing the value of the
open_files_limit
system
variable. However, this is dependent on the operating system,
and may not be possible or advisable on all platforms; see
Section B.5.2.18, “'File
' Not Found and
Similar Errors”, for more
information. In some cases, using large numbers (hundreds) of
partitions may also not be advisable due to other concerns, so
using more partitions does not automatically lead to better
results.
See also File system operations.
Foreign keys not supported. Partitioned tables do not support foreign keys. This means that:
Definitions of tables employing user-defined partitioning may not contain foreign key references to other tables.
No table definition may contain a foreign key reference to a partitioned table.
The scope of these restrictions includes tables that use the
InnoDB
storage engine.
ALTER TABLE ... ORDER BY
.
An ALTER TABLE ... ORDER BY
statement run
against a partitioned table causes ordering of rows only
within each partition.
column
FULLTEXT indexes.
Partitioned tables do not support
FULLTEXT
indexes. This includes
partitioned tables employing the MyISAM
storage engine.
Spatial columns.
Columns with spatial data types such as
POINT
or GEOMETRY
cannot be used in partitioned tables.
Temporary tables. Temporary tables cannot be partitioned. (Bug#17497)
Log tables.
It is not possible to partition the log tables; an
ALTER TABLE ... PARTITION BY ...
statement on such a table fails with an error. (Bug#27816)
Data type of partitioning key.
A partitioning key must be either an integer column or an
expression that resolves to an integer. The column or
expression value may also be NULL
. (See
Section 17.2.6, “How MySQL Partitioning Handles NULL
”.)
The lone exception to this restriction occurs when
partitioning by [LINEAR
]
KEY
, where it is possible to use columns of
other types as partitioning keys, because MySQL's internal
key-hashing functions produce the correct data type from these
types. For example, the following CREATE
TABLE
statement is valid:
CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4;
Subqueries.
A partitioning key may not be a subquery, even if that
subquery resolves to an integer value or
NULL
.
Issues with subpartitions.
Subpartitions are limited to HASH
or
KEY
partitioning. HASH
and KEY
partitions cannot be
subpartitioned.
Currently, SUBPARTITION BY KEY
requires
that the subpartitioning column or columns be specified
explicitly, unlike the case with PARTITION BY
KEY
, where it can be omitted (in which case the
table's primary key column is used by default). Consider
table created by this statement:
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) );
You can create a table having the same columns, partitioned by
KEY
, using a statement such as this one:
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ) PARTITION BY KEY() PARTITIONS 4;
The previous statement is treated as though it had been written like this, with the table's primary key column used as the partitioning column:
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ) PARTITION BY KEY(id) PARTITIONS 4;
However, the following statement that attempts to create a subpartitioned table using the default column as the subpartitioning column fails, and the column must be specified in order for the statement to succeed, as shown here:
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)
This is a known issue, which we are currently working to address (Bug#51470).
Key caches not supported.
Key caches are not supported for partitioned tables. The
CACHE INDEX
and
LOAD INDEX INTO
CACHE
statements, when you attempt to use them on
tables having user-defined partitioning, fail with the
errors The storage engine for the table doesn't
support assign_to_keycache and The
storage engine for the table doesn't support
preload_keys, respectively.
DELAYED
option not supported.
Use of INSERT DELAYED
to
insert rows into a partitioned table is not supported.
Attempting to do so fails with an error. (Bug#31210)
DATA DIRECTORY
and INDEX DIRECTORY
options.
DATA DIRECTORY
and INDEX
DIRECTORY
are subject to the following
restrictions when used with partitioned tables:
Repairing and rebuilding partitioned tables.
The statements CHECK TABLE
,
OPTIMIZE TABLE
,
ANALYZE TABLE
, and
REPAIR TABLE
are supported
for partitioned tables. (See Bug#20129.)
mysqlcheck and
myisamchk are not supported with
partitioned tables.
In addition, you can use ALTER TABLE ... REBUILD
PARTITION
to rebuild one or more partitions of a
partitioned table; ALTER TABLE ... REORGANIZE
PARTITION
also causes partitions to be rebuilt. See
Section 12.1.6, “ALTER TABLE
Syntax”, for more information about
these two statements.
User Comments
Add your own comment.