Codebeispiele, die die Einschränkungen und Einschränkungen von MySQL-Partitionstabellen ausführlich erläutern-MySQL-Tutorial-php.cn

Dieser Artikel gibt einen Überblick über die Einschränkungen der MySQL-Partitionstabelle. Bitte weisen Sie Ihre Freunde rechtzeitig darauf hin Schauen Sie es sich auf der offiziellen Website an.

Bauen verboten

Partitionsausdrücke unterstützen die folgenden Builds nicht:

Speicherprozedur, gespeicherte Funktion, UDFS oder Plug-in

Variablen oder Benutzervariablen deklarieren

Sie können auf SQL-Funktionen verweisen, die von Partitionen nicht unterstützt werden

arithmetische und logische Operatoren

Partitionsausdruck unterstützt arithmetische Operationen +, -, *, unterstützt jedoch keine p- und /-Operationen (er existiert immer noch, bitte überprüfen Sie Bug #30188, Bug #33182). Das Ergebnis muss jedoch eine Ganzzahl oder NULL sein (außer bei linearen Partitionsschlüsseln, weitere Informationen finden Sie unter Partitionstypen).

Partitionsausdrücke unterstützen keine Bitoperationen: |, &, ^, <<, >>, ~ .

HANDLER-Anweisung

Partitionierte Tabellen vor MySQL 5.7.1 unterstützen die HANDLER-Anweisung nicht und spätere Versionen heben diese Einschränkung auf.

Server-SQL-Modus

Wenn Sie eine benutzerdefinierte partitionierte Tabelle verwenden möchten, ist zu beachten, dass der SQL-Modus beim Erstellen einer partitionierten Tabelle aktiviert ist nicht reserviert. Wie im Kapitel „Server-SQL-Modus“ erläutert, können sich die Ergebnisse der meisten MySQL-Funktionen und -Operatoren je nach Server-SQL-Modus ändern. Daher kann eine Änderung des SQL-Schemas nach der Erstellung partitionierter Tabellen zu erheblichen Änderungen im Verhalten dieser Tabellen führen, was leicht zu Datenverlust oder -beschädigung führen kann. Aus den oben genannten Gründen wird dringend empfohlen, den SQL-Modus des Servers nach dem Erstellen der Partitionstabelle niemals zu ändern.

Geben Sie ein Beispiel, um die obige Situation zu veranschaulichen:

1.Fehlerbehandlung

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

Nach dem Login kopieren

Das Standardergebnis von MySQL dividiert durch 0 ist NULL . Anstatt einen Fehler zu melden:

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

Nach dem Login kopieren

Wenn wir jedoch den SQL-Modus ändern, wird ein Fehler gemeldet:

mysql> SET sql_mode=&#39;STRICT_ALL_TABLES,ERROR_FOR_pISION_BY_ZERO&#39;;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): pision by 0

Nach dem Login kopieren

2. Tabellenhilfsfunktion

Manchmal kann eine Änderung des SQL-Modus dazu führen, dass die Partitionstabelle nicht verfügbar ist. Einige Tabellen funktionieren beispielsweise nur, wenn der SQL-Modus NO_UNSIGNED_SUBTRACTION ist, wie zum Beispiel:

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=&#39;NO_UNSIGNED_SUBTRACTION&#39;;
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)

Nach dem Login kopieren

Wenn Sie den SQL-Modus nach dem Erstellen von tu ändern, können Sie möglicherweise nicht mehr auf diese Tabelle zugreifen:

mysql> SET sql_mode=&#39;&#39;;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

Nach dem Login kopieren

Der serverseitige SQL-Modus wirkt sich auch auf die Replikation partitionierter Tabellen aus. Die Verwendung unterschiedlicher SQL-Modi zwischen dem primären und dem sekundären Server kann zu unterschiedlichen Ausführungsergebnissen der Partitionsausdrücke auf dem primären und sekundären Server führen (und der Wechsel zwischen primärem und sekundärem Server in Alibaba ist ein normaler Vorgang). Die Datenverteilung im Intervall ist unterschiedlich. Dies kann auch dazu führen, dass das Einfügen der Partitionstabelle in der Primärdatenbank erfolgreich ist, in der Standby-Datenbank jedoch fehlschlägt. Basierend auf der obigen Situation besteht die beste Lösung darin, sicherzustellen, dass die SQL-Modi zwischen dem primären und dem sekundären Server konsistent sind (darauf muss der DBA während des Betriebs- und Wartungsprozesses achten).

LeistungHinweise

Im Folgenden sind einige Faktoren aufgeführt, die sich auf die Leistung von Partitionsvorgängen auswirken:

Dateisystem--Operationen
Partitionierungs- oder Neupartitionierungsoperationen (z. B. ALTER TABLE ...PARTITION BY ..., REORGANIZE PARTITION oder REMOVE PARTITIONING) hängen von der Dateisystemimplementierung ab. Dies bedeutet, dass die oben genannten Vorgänge vom Betriebssystem beeinflusst werden, z. B. vom Typ und den Eigenschaften des Dateisystems, der Festplattengeschwindigkeit, dem Auslagerungsspeicher, der Effizienz der Dateiverarbeitung im Betriebssystem und der Dateiverwaltung. verwandte Probleme auf dem MySQL-Server wirken sich auf Optionen, Variablen und andere Faktoren aus. Beachten Sie, dass Sie sicherstellen müssen, dass „large_files_support“ aktiviert ist und die Einstellung „open_files_limit“ angemessen ist. Für die partitionierte Tabelle der MyISAM-Engine müssen Sie myisam_max_sort_file_size erhöhen, um die Leistung der InnoDB-Tabelle zu verbessern. Durch die Aktivierung von innodb_file_per_table wird der Partitionierungs- oder Neupartitionierungsvorgang beschleunigt.

Sie können sich auch auf die maximale Anzahl von Partitionen beziehen.

MyISAM- und Partitionsdateideskriptoren

Für MyISAM-partitionierte Tabellen verwendet MySQL zwei Dateideskriptoren für jede geöffnete Tabelle und Partition. Dies bedeutet, dass für die Ausführung von Operationen an einer MyISAM-partitionierten Tabelle (insbesondere ALTER TABLE-Operationen) mehr Dateideskriptoren erforderlich sind als für dieselbe Tabelle ohne Partitionen.

Angenommen, wir möchten eine MyISAM-Tabelle mit 100 Partitionen erstellen. Die Anweisung lautet wie folgt:

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

Nach dem Login kopieren

Um es einfach auszudrücken, obwohl wir in diesem Beispiel die KEY-Partition verwenden Dateideskriptor Das Problem tritt in allen Partitionen auf, die Tabellen-EngineMyISAM verwenden, unabhängig vom Partitionstyp. Bei partitionierten Tabellen, die andere Speicher-Engines (z. B. InnoDB) verwenden, tritt dieses Problem jedoch nicht auf.

Angenommen, Sie möchten t neu partitionieren und möchten, dass es 101 Partitionen hat, verwenden Sie die folgende Anweisung:

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

如果要处理ALTER TABLE语句需要402个文件描述符，原来100个分区*2个+101个新分区*2。这是因为在重新组织表数据时，必须打开所有的（新旧）分区。所以建议在执行这些操作时，要确保--open-files-limit要设置的大些。

表锁

对表执行分区操作的进程会占用表的写锁，不影响读，例如在这些分区上的INSERT和UPDATE操作只有在分区操作完成后才能执行。

存储引擎

分区操作，比如查询，和更新操作通常情况下用MyISAM引擎要比InnoDB和NDB快。

索引；分区修剪

分区表和非分区表一样，合理的利用索引可以显著地提升查询速度。另外，设计分区表以及在这些表上的查询，可以利用分区修剪来显著提升性能。

在MySQL 5.7.3版本之前，分区表不支持索引条件下推，之后的版本可以支持了。

load data性能

在MySQL 5.7，load data 使用buffer提高性能。你需要知道的是buffer会占用每个分区的130KB来达到这个目的。

分区的最大个数

如果不是用NDB作为存储引擎的分区表，支持分区（这里子分区也包含在内）最大个数是8192。

如果使用NDB作为存储引擎的用户自定义分区的最大分区个数，取决于MySQL Cluster的版本，数据节点和其他因素。

如果你创建一个非常多（比最大分区数要少）的分区时，遇到诸如Got error ... from storage engine: Out of resources when opening file类的错误，你可能需要增加open_files_limit。但是open_files_limit其实也依赖操作系统，可能不是所有的平台都可以建议调整。还有一些其他情况，不建议使用巨大或者成百上千个分区，所以使用越来越多的分区并不见得能带来好结果。

不支持Query cache

分区表不支持query cache，在分区表的查询中自动避开了query cache。也就是说在分区表的查询语句中query cache是不起作用的。

每个分区一个key caches

在MySQL 5.7版本中，可以通过CACHE INDEX和LOAD INDEX INTO CACHE来使用MyISAM分区表的key cache。可以为一个，几个或者所有分区都定义key cache，这样可以把一个，几个或者所有分区的索引预加载到key cache中。

不支持InnoDB分区表的外键

使用InnoDB引擎的分区表不支持外键。下面的两种具体情况来阐述：

在InnoDB表不能使用包含有外键的自定义分区；如果已经使用了外键的InnoDB表，则不能被分区。

InnoDB表不能包含一个和用户自定义分区表相关的外键；使用了用户自定义分区的InnoDB表，不能包含和外键相关的列。

刚刚列出的限制的范围包括使用InnoDB存储引擎的所有表。违反这些限制的CREATE TABLE和ALTER TABLE语句是不被允许的。

ALTER TABLE ... ORDER BY

如果在分区表上执行ALTER TABLE ... ORDER BY的话，会导致每个分区的行排序。

REPLACE语句在修改primary key上的效率

在某些情况下是需要修改表的primary key的，如果你的应用程序使用了REPLACE语句,这些语句的结果可能会被大幅度修改。

全文索引

分区表不支持全文索引或者搜索，即使分区表的存储引擎是InnoDB或者MyISAM也不行。

空间列

分区表不支持空间列，比如点或者几何。

临时表

不能对临时表进行分区(Bug #17497)。

日志表

不能对日志表进行分区，如果强制执行ALTER TABLE ... PARTITION BY ... 语句会报错。

分区键的数据类型

分区键必须是整形或者结果是整形的表达式。不能用结果为ENUM类型的表达式。因为这种类型的表达式可能是NULL。

下面两种情况是例外的：

当用LINER分区时，可以使用除TEXT或者BLOBS以外的数据类型作为分区键，因为MySQL内部的 hash函数会从这些列中产生正确的数据类型。例如，下面的创建语句是合法的：

CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

CREATE TABLE tke
  ( c1 ENUM(&#39;red&#39;, &#39;orange&#39;, &#39;yellow&#39;, &#39;green&#39;, &#39;blue&#39;, &#39;indigo&#39;, &#39;violet&#39;) )
PARTITION BY LINEAR KEY(c1)
PARTITIONS 6;

Nach dem Login kopieren

当用RANGE，LIST，DATE或者DATETIME列分区的话，可能会用string。例如，下面的创建语句是合法的：

CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
  PARTITION p0 VALUES LESS THAN(&#39;1990-01-01&#39;),
  PARTITION p1 VALUES LESS THAN(&#39;1995-01-01&#39;),
  PARTITION p2 VALUES LESS THAN(&#39;2000-01-01&#39;),
  PARTITION p3 VALUES LESS THAN(&#39;2005-01-01&#39;),
  PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
  PARTITION p0 VALUES IN(&#39;a&#39;, &#39;d&#39;, &#39;g&#39;, &#39;j&#39;, &#39;m&#39;, &#39;p&#39;, &#39;s&#39;, &#39;v&#39;, &#39;y&#39;),
  PARTITION p1 VALUES IN(&#39;b&#39;, &#39;e&#39;, &#39;h&#39;, &#39;k&#39;, &#39;n&#39;, &#39;q&#39;, &#39;t&#39;, &#39;w&#39;, &#39;z&#39;),
  PARTITION p2 VALUES IN(&#39;c&#39;, &#39;f&#39;, &#39;i&#39;, &#39;l&#39;, &#39;o&#39;, &#39;r&#39;, &#39;u&#39;, &#39;x&#39;, NULL)
);

Nach dem Login kopieren

上述异常都不适用于BLOB或TEXT列类型。

子查询

即使子查询避开整形值或者NULL值，分区键不能子查询。

子分区的问题

子分区必须使用HASH或者KEY分区。只有RANGE和LIST分区支持被子分区；HASH和KEY不支持被子分区。

SUBPARTITION BY KEY要求显示指定子分区列，不像PARTITION BY KEY可以省略（这种情况下会默认使用表的primary key）。例如，如果是这样创建表：

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

Nach dem Login kopieren

你也可以使用相同的列的创建分区表（以KEY分区），使用下面语句：

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

Nach dem Login kopieren

前面的语句其实和下面的语句是一样的：

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

Nach dem Login kopieren

但是，如果尝试使用缺省列作为子分区列，创建子分区表的话，以下语句将失败，必须指定该语句才能执行成功，如下所示：（bug已知 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 &#39;)
 
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)

Nach dem Login kopieren

数据字典和索引字典选项

分区表的数据字典和索引字典受以下因素制约：

表级的数据字典和索引字典被忽略（Bug #32091）

在Windows系统上，MyISAM分区表不支持独立分区或子分区的数据字典和索引字典选项。但是支持InnoDB分区表的独立分区或者子分区的数据字典。

修复和重建分区表

分区表支持CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, 和 REPAIR TABLE语句。

另外，你也可以用ALTER TABLE ... REBUILD PARTITION在一个分区表上重建一个或多个分区；用ALTER TABLE ... REORGANIZE PARTITION同样可以重建分区。

从MySQL 5.7.2开始，子分区支持ANALYZE, CHECK, OPTIMIZE, REPAIR, 和 TRUNCATE操作。而在MySQL5.7.5之前的版本就已经引入REBUILD语法，只是不起作用（可以参考Bug #19075411, Bug #73130）。

分区表不支持mysqlcheck, myisamchk, 和 myisampack操作。

导出选项

在MySQL 5.7.4以前的版本，不支持InnoDB分区表的FLUSH TABLES语句的导出选项（Bug #16943907）。

Das obige ist der detaillierte Inhalt vonCodebeispiele, die die Einschränkungen und Einschränkungen von MySQL-Partitionstabellen ausführlich erläutern. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!