MySQL パーティションテーブルの制限事項と制限事項を詳細に説明するコード例-mysql チュートリアル-php.cn

この記事は、Mysqlパーティションテーブルの制限をいくつかまとめたものです。個人の能力やテスト環境により、間違いがあるかもしれません。もちろん、興味のある方は公式ウェブサイトにアクセスしてください。

構築は禁止されています

パーティション式は次のタイプの構築をサポートしていません:

ストアドプロシージャ、ストアド関数、UDFS またはプラグイン

変数またはユーザー変数の宣言

SQL 関数を参照できますパーティションではサポートされていません

算術演算子および論理演算子

パーティション式は +、-、* 算術演算をサポートしていますが、p および / 演算はサポートしていません (まだ存在します。Bug #30188、Bug #33182 を確認してください) 。ただし、結果は整数または NULL でなければなりません (線形パーティションキーを除く。詳細については、パーティションタイプを参照してください)。

パーティション式はビット操作をサポートしていません: |、&、^、<<、>>、~ .

HANDLER ステートメント

MySQL 5.7.1 より前のパーティション化されたテーブルはサポートされていません HANDLERこの制限は、後のバージョンでは削除される予定です。

サーバー SQL モード

ユーザー定義のパーティションテーブルを使用する場合は、パーティションテーブルの作成時の SQL モードが保持されないことに注意してください。「サーバー SQL モード」の章で説明したように、ほとんどの MySQL 関数と演算子の結果はサーバー SQL モードに応じて変化する可能性があります。したがって、パーティションテーブルの作成後に SQL スキーマが変更されると、これらのテーブルの動作が大幅に変更される可能性があり、データの損失や損傷が容易に発生する可能性があります。上記の理由により、パーティションテーブルの作成後はサーバーの SQL モードを変更しないことを強くお勧めします。

上記の状況を説明する例を示します:

1.エラー処理

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)

ログイン後にコピー

MySQL の 0 による除算のデフォルトの結果は、エラーを報告せずに NULL になります:

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

ログイン後にコピー

ただし、SQL モードを変更すると、エラーが報告されます:

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

ログイン後にコピー

2. テーブル補助関数

SQL モードを変更すると、パーティションテーブルが使用できなくなる場合があります。たとえば、次のような一部のテーブルは、SQL モードが NO_UNSIGNED_SUBTRACTION の場合にのみ機能します。

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)

ログイン後にコピー

tu を作成した後に SQL モードを変更すると、テーブルにアクセスできなくなる可能性があります。

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

ログイン後にコピー

サーバー側 SQLモードはテーブルのコピーにも影響します。プライマリサーバーとセカンダリサーバーで異なる SQL モードを使用すると、プライマリサーバーとセカンダリサーバーでパーティション式の実行結果が異なる場合があります (Alibaba でのプライマリサーバーとセカンダリサーバー間の切り替えは通常の操作です)。これにより、実行中にパーティション式も異なります。プライマリレプリケーションプロセスとセカンダリレプリケーションプロセスが異なるため、プライマリデータベースではパーティションテーブルの挿入が成功しても、スタンバイデータベースでは失敗する可能性があります。上記の状況に基づくと、最善の解決策は、プライマリサーバーとセカンダリサーバーの間で SQL モードの一貫性を確保することです (これは、運用および保守プロセス中に DBA が注意を払う必要があることです)。

パフォーマンス注意事項

以下は、パーティション操作のパフォーマンスに影響を与えるいくつかの要因です:

ファイルシステム操作
パーティション化または再パーティション化 (ALTER TABLE ...PARTITION BY ...、など) REORGANIZE PARTITION 、または REMOVE PARTITIONING ) 操作は、ファイルシステムの実装によって異なります。これは、上記の操作が、ファイルシステムの種類と特性、ディスク速度、スワップ領域、オペレーティングシステム上のファイル処理効率、およびそれに関連するオプションや変数などのオペレーティングシステムの影響を受けることを意味します。 MySQL サーバー上のファイルハンドルなどの要因が影響します。 large_files_support が有効になっていて、open_files_limit 設定が適切であることを確認する必要があることに注意してください。 MyISAM エンジンのパーティションテーブルの場合は、パフォーマンスを向上させるために myisam_max_sort_file_size を増やす必要があります。InnoDB テーブルの場合、innodb_file_per_table を有効にすることでパーティション化または再パーティション化の操作が高速になります。

パーティションの最大数を参照することもできます。

MyISAM とパーティションファイル記述子

MyISAM パーティションテーブルの場合、MySQL は、開いているテーブルごとにパーティションごとに 2 つのファイル記述子を使用します。これは、MyISAM パーティションテーブルで操作 (特に ALTER TABLE 操作) を実行するには、パーティションのない同じテーブルよりも多くのファイル記述子が必要であることを意味します。

100 個のパーティションを持つ MyISAM テーブルを作成するとします。ステートメントは次のとおりです。

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

ログイン後にコピー

簡単に言えば、この例では KEY パーティションを使用しますが、ファイル記述子の問題はを使用するすべてのテーブルに適用されるわけではありません。テーブルエンジンパーティションタイプに関係なく、MyISAM パーティションで発生します。ただし、他のストレージエンジン (InnoDB など) を使用するパーティションテーブルにはこの問題はありません。

t を再パーティション化して 101 個のパーティションを持つようにするには、次のステートメントを使用します:

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;

ログイン後にコピー

当用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)
);

ログイン後にコピー

上述异常都不适用于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)
);

ログイン後にコピー

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

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

ログイン後にコピー

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

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

ログイン後にコピー

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

ログイン後にコピー

数据字典和索引字典选项

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

表级的数据字典和索引字典被忽略（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）。