禁止構建
分區表達式不支持以下幾種構建：
存儲過程，存儲函數，UDFS或者插件
聲明變量或者用戶變量
可以參考分區不支持的SQL函數
算術和邏輯運算符
分區表達式支持+,-,*算術運算，但是不支持DIV和/運算（還存在，可以查看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 DIV 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='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模式可能會導致分區表不可用。比如有些表只有在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='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)
如果你在創建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
服務器端的SQL模式也會影響分區表的復制。在主備間使用不同的SQL模式可能會導致分區表達式主備上執行是不同的結果（而在阿里主備切換是很正常的操作）；這也會導致在主備復制過程中，不同分區間的數據分布不同；也有可能導致在主庫上的分區表insert成功，而備庫上失敗。基于上述情況，最好的解決辦法是保證主備間的SQL模式要保持一致（這個是DBA在運維過程中需要注意的）。
性能注意事項
下面是一些會影響分區操作性能的因素：
文件系統操作
分區或者重新分區（比如ALTER TABLE ...PARTITION BY ..., REORGANIZE PARTITION, 或者REMOVE PARTITIONING ）操作取決于文件系統的實現。意思是說上述操作會受操作系統上，比如：文件系統的類型和特性，磁盤速度，swap空間，操作系統上的文件處理效率，以及MySQL服務器上的和文件句柄相關的選項，變量等因素影響。需要特別說明的是，你需要保證large_files_support是enabled的，open_files_limit設置是合理的。對于MyISAM引擎的分區表來說，需要增加myisam_max_sort_file_size以提高性能；對于InnoDB表來說，分區或者重新分區操作通過enabled innodb_file_per_table效率會更快。
也可以參考分區的最大數量。
MyISAM和分區文件描述符
對于MyISAM分區表來說，MySQL為每個打開的表，每個分區使用兩個文件描述符。這也就意味著，在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('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
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('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值，分區鍵不能子查詢。
子分區的問題
子分區必須使用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 ')
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）。
參考資料
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/
您可能感興趣的文章:- mysql實現查詢最接近的記錄數據示例
- 詳解MySQL恢復psc文件記錄數為0的解決方案
- PHP+MySQL統計該庫中每個表的記錄數并按遞減順序排列的方法
- MySQL數據庫查看數據表占用空間大小和記錄數的方法
- MYSQL速度慢的問題 記錄數據庫語句
- MySQL通過觸發器解決數據庫中表的行數限制詳解及實例
- MySQL 如何限制一張表的記錄數
