MySQL分區(qū)表的局限和限制詳解

禁止構(gòu)建

分區(qū)表達式不支持以下幾種構(gòu)建：

存儲過程，存儲函數(shù)，UDFS或者插件

聲明變量或者用戶變量

可以參考分區(qū)不支持的SQL函數(shù)

算術(shù)和邏輯運算符

分區(qū)表達式支持+,-,*算術(shù)運算，但是不支持DIV和/運算（還存在，可以查看Bug #30188, Bug #33182）。但是，結(jié)果必須是整形或者NULL（線性分區(qū)鍵除外，想了解更多信息，可以查看分區(qū)類型）。

分區(qū)表達式不支持位運算：|，，^，，>>，~ .

HANDLER語句

在MySQL 5.7.1之前的分區(qū)表不支持HANDLER語句，以后的版本取消了這一限制。

服務(wù)器SQL模式

如果要用用戶自定義分區(qū)的表的話，需要注意的是，在創(chuàng)建分區(qū)表時的SQL模式是不保留的。在服務(wù)器SQL模式一章中已經(jīng)討論過，大多數(shù)MySQL函數(shù)和運算符的結(jié)果可能會根據(jù)服務(wù)器SQL模式而改變。所以，一旦SQL模式在創(chuàng)建分區(qū)表后改變，可能導(dǎo)致這些表的行為發(fā)生重大變化，很容易導(dǎo)致數(shù)據(jù)丟失或者損壞?；谝陨显颍瑥娏医ㄗh你在創(chuàng)建分區(qū)表后千萬不要修改服務(wù)器的SQL模式。

舉個例子來說明下上述情況：

1.錯誤處理

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)

MySQL默認(rèn)除以0的結(jié)果是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='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

2.表輔助功能

有時候修改SQL模式可能會導(dǎo)致分區(qū)表不可用。比如有些表只有在SQL模式為NO_UNSIGNED_SUBTRACTION才發(fā)揮作用，比如：

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)

如果你在創(chuàng)建tu后，修改SQL模式，就可能再也不能訪問這個表了：

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

服務(wù)器端的SQL模式也會影響分區(qū)表的復(fù)制。在主備間使用不同的SQL模式可能會導(dǎo)致分區(qū)表達式主備上執(zhí)行是不同的結(jié)果（而在阿里主備切換是很正常的操作）；這也會導(dǎo)致在主備復(fù)制過程中，不同分區(qū)間的數(shù)據(jù)分布不同；也有可能導(dǎo)致在主庫上的分區(qū)表insert成功，而備庫上失敗?；谏鲜銮闆r，最好的解決辦法是保證主備間的SQL模式要保持一致（這個是DBA在運維過程中需要注意的）。

性能注意事項

下面是一些會影響分區(qū)操作性能的因素：

文件系統(tǒng)操作
分區(qū)或者重新分區(qū)（比如ALTER TABLE ...PARTITION BY ..., REORGANIZE PARTITION, 或者REMOVE PARTITIONING ）操作取決于文件系統(tǒng)的實現(xiàn)。意思是說上述操作會受操作系統(tǒng)上，比如：文件系統(tǒng)的類型和特性，磁盤速度，swap空間，操作系統(tǒng)上的文件處理效率，以及MySQL服務(wù)器上的和文件句柄相關(guān)的選項，變量等因素影響。需要特別說明的是，你需要保證large_files_support是enabled的，open_files_limit設(shè)置是合理的。對于MyISAM引擎的分區(qū)表來說，需要增加myisam_max_sort_file_size以提高性能；對于InnoDB表來說，分區(qū)或者重新分區(qū)操作通過enabled innodb_file_per_table效率會更快。

也可以參考分區(qū)的最大數(shù)量。

MyISAM和分區(qū)文件描述符

對于MyISAM分區(qū)表來說，MySQL為每個打開的表，每個分區(qū)使用兩個文件描述符。這也就意味著，在MyISAM分區(qū)表上想執(zhí)行操作（特別是ALTER TABLE操作）比相同的表沒有分區(qū)，需要更多的文件描述符。

假設(shè)我們要創(chuàng)建有100個分區(qū)的MyISAM表，語句如下：

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

簡單來講，在這個例子中，雖然我們用的KEY分區(qū)，但是文件描述符的問題，在所有使用表引擎是MyISAM的分區(qū)里都會遇到，不管是分區(qū)類型是哪種。但是使用其他存儲引擎（比如InnoDB）的分區(qū)表沒有這個問題。

假設(shè)你想對t重新分區(qū)，想讓它有101個分區(qū)的話，使用下面的語句：

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

如果要處理ALTER TABLE語句需要402個文件描述符，原來100個分區(qū)*2個+101個新分區(qū)*2。這是因為在重新組織表數(shù)據(jù)時，必須打開所有的（新舊）分區(qū)。所以建議在執(zhí)行這些操作時，要確保--open-files-limit要設(shè)置的大些。

表鎖

對表執(zhí)行分區(qū)操作的進程會占用表的寫鎖，不影響讀，例如在這些分區(qū)上的INSERT和UPDATE操作只有在分區(qū)操作完成后才能執(zhí)行。

存儲引擎

分區(qū)操作，比如查詢，和更新操作通常情況下用MyISAM引擎要比InnoDB和NDB快。

索引；分區(qū)修剪

分區(qū)表和非分區(qū)表一樣，合理的利用索引可以顯著地提升查詢速度。另外，設(shè)計分區(qū)表以及在這些表上的查詢，可以利用分區(qū)修剪來顯著提升性能。

在MySQL 5.7.3版本之前，分區(qū)表不支持索引條件下推，之后的版本可以支持了。

load data性能

在MySQL 5.7，load data 使用buffer提高性能。你需要知道的是buffer會占用每個分區(qū)的130KB來達到這個目的。

分區(qū)的最大個數(shù)

如果不是用NDB作為存儲引擎的分區(qū)表，支持分區(qū)（這里子分區(qū)也包含在內(nèi)）最大個數(shù)是8192。

如果使用NDB作為存儲引擎的用戶自定義分區(qū)的最大分區(qū)個數(shù)，取決于MySQL Cluster的版本， 數(shù)據(jù)節(jié)點和其他因素。

如果你創(chuàng)建一個非常多（比最大分區(qū)數(shù)要少）的分區(qū)時，遇到諸如Got error ... from storage engine: Out of resources when opening file類的錯誤，你可能需要增加open_files_limit。但是open_files_limit其實也依賴操作系統(tǒng)，可能不是所有的平臺都可以建議調(diào)整。還有一些其他情況，不建議使用巨大或者成百上千個分區(qū)，所以使用越來越多的分區(qū)并不見得能帶來好結(jié)果。

不支持Query cache

分區(qū)表不支持query cache，在分區(qū)表的查詢中自動避開了query cache。也就是說在分區(qū)表的查詢語句中query cache是不起作用的。

每個分區(qū)一個key caches

在MySQL 5.7版本中，可以通過CACHE INDEX和LOAD INDEX INTO CACHE來使用MyISAM分區(qū)表的key cache?？梢詾橐粋€，幾個或者所有分區(qū)都定義key cache，這樣可以把一個，幾個或者所有分區(qū)的索引預(yù)加載到key cache中。

不支持InnoDB分區(qū)表的外鍵

使用InnoDB引擎的分區(qū)表不支持外鍵。下面的兩種具體情況來闡述：

在InnoDB表不能使用包含有外鍵的自定義分區(qū)；如果已經(jīng)使用了外鍵的InnoDB表，則不能被分區(qū)。

InnoDB表不能包含一個和用戶自定義分區(qū)表相關(guān)的外鍵；使用了用戶自定義分區(qū)的InnoDB表，不能包含和外鍵相關(guān)的列。

剛剛列出的限制的范圍包括使用InnoDB存儲引擎的所有表。違反這些限制的CREATE TABLE和ALTER TABLE語句是不被允許的。

ALTER TABLE ... ORDER BY

如果在分區(qū)表上執(zhí)行ALTER TABLE ... ORDER BY的話，會導(dǎo)致每個分區(qū)的行排序。

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

在某些情況下是需要修改表的primary key的，如果你的應(yīng)用程序使用了REPLACE語句,這些語句的結(jié)果可能會被大幅度修改。

全文索引

分區(qū)表不支持全文索引或者搜索，即使分區(qū)表的存儲引擎是InnoDB或者MyISAM也不行。

空間列

分區(qū)表不支持空間列，比如點或者幾何。

臨時表

不能對臨時表進行分區(qū)(Bug #17497)。

日志表

不能對日志表進行分區(qū)，如果強制執(zhí)行ALTER TABLE ... PARTITION BY ... 語句會報錯。

分區(qū)鍵的數(shù)據(jù)類型

分區(qū)鍵必須是整形或者結(jié)果是整形的表達式。不能用結(jié)果為ENUM類型的表達式。因為這種類型的表達式可能是NULL。

下面兩種情況是例外的：

當(dāng)用LINER分區(qū)時，可以使用除TEXT或者BLOBS以外的數(shù)據(jù)類型作為分區(qū)鍵，因為MySQL內(nèi)部的 hash函數(shù)會從這些列中產(chǎn)生正確的數(shù)據(jù)類型。例如，下面的創(chuàng)建語句是合法的：

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;

當(dāng)用RANGE，LIST，DATE或者DATETIME列分區(qū)的話，可能會用string。例如，下面的創(chuàng)建語句是合法的：

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

上述異常都不適用于BLOB或TEXT列類型。

子查詢

即使子查詢避開整形值或者NULL值，分區(qū)鍵不能子查詢。

子分區(qū)的問題

子分區(qū)必須使用HASH或者KEY分區(qū)。只有RANGE和LIST分區(qū)支持被子分區(qū)；HASH和KEY不支持被子分區(qū)。

SUBPARTITION BY KEY要求顯示指定子分區(qū)列，不像PARTITION BY KEY可以省略（這種情況下會默認(rèn)使用表的primary key）。例如，如果是這樣創(chuàng)建表：

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

你也可以使用相同的列的創(chuàng)建分區(qū)表（以KEY分區(qū)），使用下面語句：

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;
 

但是，如果嘗試使用缺省列作為子分區(qū)列，創(chuàng)建子分區(qū)表的話，以下語句將失敗，必須指定該語句才能執(zhí)行成功，如下所示：（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 ')
 
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)

數(shù)據(jù)字典和索引字典選項

分區(qū)表的數(shù)據(jù)字典和索引字典受以下因素制約：

表級的數(shù)據(jù)字典和索引字典被忽略（Bug #32091）

在Windows系統(tǒng)上，MyISAM分區(qū)表不支持獨立分區(qū)或子分區(qū)的數(shù)據(jù)字典和索引字典選項。但是支持InnoDB分區(qū)表的獨立分區(qū)或者子分區(qū)的數(shù)據(jù)字典。

修復(fù)和重建分區(qū)表

分區(qū)表支持CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, 和 REPAIR TABLE語句。

另外，你也可以用ALTER TABLE ... REBUILD PARTITION在一個分區(qū)表上重建一個或多個分區(qū)；用ALTER TABLE ... REORGANIZE PARTITION同樣可以重建分區(qū)。

從MySQL 5.7.2開始，子分區(qū)支持ANALYZE, CHECK, OPTIMIZE, REPAIR, 和 TRUNCATE操作。而在MySQL5.7.5之前的版本就已經(jīng)引入REBUILD語法，只是不起作用（可以參考Bug #19075411, Bug #73130）。

分區(qū)表不支持mysqlcheck, myisamchk, 和 myisampack操作。

導(dǎo)出選項

在MySQL 5.7.4以前的版本，不支持InnoDB分區(qū)表的FLUSH TABLES語句的導(dǎo)出選項（Bug #16943907）。

參考資料

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html

https://www.slideshare.net/datacharmer/mysql-partitions-tutorial/34-Partition_pruning_unpartitioned_tableexplain_partitions

https://www.percona.com/blog/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/