Rechercher dans le manuel MySQL
23.6 Restrictions and Limitations on Partitioning
[+/-]
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 23.6.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 23.2, “Partitioning Types”, for more information).
The DIV
operator is also supported;
the /
operator is
not permitted.
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 elsewhere in this Manual (see Section 5.1.11, “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. As discussed elsewhere, handling of “special” values such as zero and
NULL
can differ between different server SQL modes (see Section 5.1.11, “Server SQL Modes”). For example,ERROR_FOR_DIVISION_BY_ZERO
can affect whether or not 0 can be inserted as a value into a table whose paritioning expression uses
orcolumn
DIVvalue
column
MODvalue
.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 theNO_UNSIGNED_SUBTRACTION
mode is in effect:- +------------+
- | @@sql_mode |
- +------------+
- | |
- +------------+
- -> );
- Query OK, 0 rows affected (0.00 sec)
- +-------------------------+
- | @@sql_mode |
- +-------------------------+
- | NO_UNSIGNED_SUBTRACTION |
- +-------------------------+
- -> );
- Query OK, 0 rows affected (0.05 sec)
If you remove the
NO_UNSIGNED_SUBTRACTION
server SQL mode after creatingtu
, you may no longer be able to access this table:- Query OK, 0 rows affected (0.00 sec)
See also Section 5.1.11, “Server SQL Modes”.
Server SQL modes also impact replication of partitioned tables. Disparate 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 effects of partitioning operations on performance are given in the following list:
File system operations. Partitioning and repartitioning operations (such as
ALTER TABLE
withPARTITION BY ...
,REORGANIZE PARTITION
, orREMOVE 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 thatlarge_files_support
is enabled and thatopen_files_limit
is set properly. Partitioning and repartitioning operations involvingInnoDB
tables may be made more efficient by enablinginnodb_file_per_table
.See also Maximum number of partitions.
Table locks. Generally, 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
andUPDATE
operations are performed as soon as the partitioning operation has completed. ForInnoDB
-specific exceptions to this limitation, see Partitioning Operations.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 queries on these tables to take advantage of partition pruning can improve performance dramatically. See Section 23.4, “Partition Pruning”, for more information.
Index condition pushdown is supported for partitioned tables. See Section 8.2.1.5, “Index Condition Pushdown Optimization”.
Performance with LOAD DATA. In MySQL 8.0,
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 not
using the NDB
storage engine is
8192. This number includes subpartitions.
The maximum possible number of user-defined partitions for a table
using the NDB
storage engine is
determined according to the version of the NDB 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.4.2.17, “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 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; noInnoDB
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; noInnoDB
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
statement run
against a partitioned table causes ordering of rows only within
each partition.
column
Effects on REPLACE statements by modification of primary keys.
It can be desirable in some cases (see
Section 23.6.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.9, “REPLACE Syntax”, for more
information and an example.
FULLTEXT indexes.
Partitioned tables do not support FULLTEXT
indexes or searches.
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.
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 23.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 thanTEXT
orBLOB
as partitioning keys, because the internal key-hashing functions produce the correct data type from these types. For example, the following twoCREATE TABLE
statements are valid:When partitioning by
RANGE COLUMNS
orLIST COLUMNS
, it is possible to use string,DATE
, andDATETIME
columns. For example, each of the followingCREATE TABLE
statements is valid:- );
- );
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.
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:
- );
You can create a table having the same columns, partitioned by
KEY
, using a statement such as this one:
- )
- 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:
- )
- 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:
- -> )
- -> SUBPARTITIONS 4
- -> (
- -> );
- 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).
DATA DIRECTORY and INDEX DIRECTORY options.
Table-level DATA DIRECTORY
and INDEX
DIRECTORY
options are ignored (see Bug #32091). You
can employ these options for individual partitions or
subpartitions of InnoDB
tables.
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.9, “ALTER TABLE Syntax”, for more information about these
two statements.
ANALYZE
, CHECK
,
OPTIMIZE
, REPAIR
, and
TRUNCATE
operations are supported with
subpartitions. See
Section 13.1.9.1, “ALTER TABLE Partition Operations”.
File name delimiters for partitions and subpartitions.
Table partition and subpartition file names include generated
delimiters such as #P#
and
#SP#
. The letter case of such delimiters can
vary and should not be depended upon.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-partitioning-limitations.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.