This section discusses current restrictions and limitations on
MySQL partitioning support.
Prohibited constructs.
The following constructs are not permitted in partitioning
expressions:
Stored procedures, stored functions, UDFs, or plugins.
Declared variables or user variables.
For a list of SQL functions which are permitted in partitioning
expressions, see
Section 19.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
elsewhere in this chapter; see
Section 19.2, “Partitioning Types”, for more information).
The DIV
operator is also supported,
and the /
operator
is not permitted. (Bug #30188, Bug #33182)
The bit operators
|
,
&
,
^
,
<<
,
>>
, and
~
are not
permitted in partitioning expressions.
HANDLER statements.
In MySQL 5.5, the
HANDLER
statement is not
supported with partitioned tables.
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.7, “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
column
DIV 0
or column
MOD
0
, as shown here:
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.
MyISAM and partition file descriptor usage.
For a partitioned MyISAM
table,
MySQL uses 2 file descriptors for each partition, for each
such table that is open. This means that you need many more
file descriptors to perform operations on a partitioned
MyISAM
table than on a table which is
identical to it except that the latter table is not
partitioned, particularly when performing
ALTER
TABLE
operations.
Assume a MyISAM
table t
with 100 partitions, such as the table created by this SQL
statement:
CREATE TABLE t (c1 VARCHAR(50))
PARTITION BY KEY (c1) PARTITIONS 100
ENGINE=MYISAM;
Note
For brevity, we use KEY
partitioning for
the table shown in this example, but file descriptor usage
as described here applies to all partitioned
MyISAM
tables, regardless of the type of
partitioning that is employed. Partitioned tables using
other storage engines such as
InnoDB
are not affected by this
issue.
Now assume that you wish to repartition t
so that it has 101 partitions, using the statement shown here:
ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;
To process this ALTER TABLE
statement,
MySQL uses 402 file descriptors—that is, two for each of
the 100 original partitions, plus two for each of the 101 new
partitions. This is because all partitions (old and new) must
be opened concurrently during the reorganization of the table
data. It is recommended that, if you expect to perform such
operations, you should make sure that
--open-files-limit
is not set
too low to accommodate them.
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.
Indexes; partition pruning.
As with nonpartitioned tables, proper use of indexes can
speed up queries on partitioned tables significantly. In
addition, designing partitioned tables and statements using
these tables to take advantage of partition
pruning can improve performance dramatically.
See Section 19.4, “Partition Pruning”, for more
information.
Performance with LOAD DATA.
In MySQL 5.5, 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
(that does not use the NDB
storage
engine) is 1024. This number includes subpartitions.
The maximum possible number of user-defined partitions for a table
using the NDBCLUSTER
storage engine
is determined according to the version of the MySQL Cluster
software being used, the number of data nodes, and other factors.
See
NDB and user-defined partitioning,
for more information.
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.
Query cache not supported.
The query cache is not supported for partitioned tables.
Beginning with MySQL 5.5.23, the query cache is automatically
disabled for queries involving partitioned tables, and cannot be
enabled for such queries. (Bug #53775)
Per-partition key caches.
In MySQL 5.5, key caches are supported for
partitioned MyISAM
tables, using
the CACHE INDEX
and
LOAD INDEX INTO
CACHE
statements. Key caches may be defined for one,
several, or all partitions, and indexes for one, several, or all
partitions may be preloaded into key caches.
Foreign keys not supported for partitioned InnoDB tables.
Partitioned tables using the InnoDB
storage engine do not support foreign keys. More specifically,
this means that the following two statements are true:
No definition of an InnoDB
table employing
user-defined partitioning may contain foreign key references;
no InnoDB
table whose definition contains
foreign key references may be partitioned.
No InnoDB
table definition may contain a
foreign key reference to a user-partitioned table; no
InnoDB
table with user-defined partitioning
may contain columns referenced by 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 would result in tables violating these
restrictions are not allowed.
ALTER TABLE ... ORDER BY.
An ALTER TABLE ... ORDER BY
column
statement run
against a partitioned table causes ordering of rows only within
each partition.
Effects on REPLACE statements by modification of primary keys.
It can be desirable in some cases (see
Section 19.5.1, “Partitioning Keys, Primary Keys, and Unique Keys”)
to modify a table's primary key. Be aware that, if your
application uses REPLACE
statements and you do this, the results of these statements can
be drastically altered. See Section 13.2.8, “REPLACE Syntax”, for more
information and an example.
FULLTEXT indexes.
Partitioned tables do not support FULLTEXT
indexes or searches. 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.
Data type of partitioning key.
A partitioning key must be either an integer column or an
expression that resolves to an integer. Expressions employing
ENUM
columns cannot be used. The
column or expression value may also be NULL
.
(See Section 19.2.7, “How MySQL Partitioning Handles NULL”.)
There are two exceptions to this restriction:
When partitioning by [LINEAR] KEY
, it is
possible to use columns of any valid MySQL data type other
than TEXT
or
BLOB
as partitioning keys,
because MySQL's internal key-hashing functions produce
the correct data type from these types. For example, the
following two CREATE TABLE
statements are 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;
When partitioning by RANGE COLUMNS
or
LIST COLUMNS
, it is possible to use string,
DATE
, and
DATETIME
columns. For example,
each of the following CREATE
TABLE
statements 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)
);
Neither of the preceding exceptions applies to
BLOB
or
TEXT
column types.
Subqueries.
A partitioning key may not be a subquery, even if that subquery
resolves to an integer value or NULL
.
Issues with subpartitions.
Subpartitions must use HASH
or
KEY
partitioning. Only
RANGE
and LIST
partitions
may be subpartitioned; 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 the 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 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 (see Bug #51470).
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.
DATA DIRECTORY and INDEX DIRECTORY options.
DATA DIRECTORY
and INDEX
DIRECTORY
are subject to the following restrictions
when used with partitioned tables:
Table-level DATA DIRECTORY
and
INDEX DIRECTORY
options are ignored (see
Bug #32091).
On Windows, the DATA DIRECTORY
and
INDEX DIRECTORY
options are not supported
for individual partitions or subpartitions (Bug #30459).
Repairing and rebuilding partitioned tables.
The statements CHECK TABLE
,
OPTIMIZE TABLE
,
ANALYZE TABLE
, and
REPAIR TABLE
are supported for
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 13.1.7, “ALTER TABLE Syntax”, for more information about these
two statements.
mysqlcheck, myisamchk, and
myisampack are not supported with partitioned
tables.
User Comments
Posted by Chris Wagner on August 19 2011 10:10pm | [Delete] [Edit] |
The bit shift operators >> and << can be emulated in the partitioning function by DIV and multiplication. This is because shift is identical to multiplying or dividing by 2 on an integer. I'm using POW here just to illustrate the relationship between the functions. U can't use POW so write the actual product in the function as in my e.g.
`int` >> num --> `int` DIV POW(2, num)
`int` << num --> `int` * POW(2, num)
e.g.
`int` >> 8 is `int` DIV 256