詳細(xì)說(shuō)明MySQL分區(qū)表的局限與局限的代碼案例

禁止構(gòu)建

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

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

聲明變量或者用戶變量

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

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

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

分區(qū)表達(dá)式不支持位運(yùn)算： ，&，^，<<，>>，~ .

HANDLER語(yǔ)句

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

服務(wù)器SQL模式

如果要用用戶自定義分區(qū)的表的話，需要注意的是，在創(chuàng)建分區(qū)表時(shí)的SQL模式是不保留的。在服務(wù)器SQL模式一章中已經(jīng)討論過(guò)，大多數(shù)MySQL函數(shù)和運(yùn)算符的結(jié)果可能會(huì)根據(jù)服務(wù)器SQL模式而改變。所以，一旦SQL模式在創(chuàng)建分區(qū)表后改變，可能導(dǎo)致這些表的行為發(fā)生重大變化，很容易導(dǎo)致數(shù)據(jù)丟失或者損壞�；�于以上原因，強(qiáng)烈建議你在創(chuàng)建分區(qū)表后千萬(wàn)不要修改服務(wù)器的SQL模式。

舉個(gè)例子來(lái)說(shuō)明下上述情況：

1.錯(cuò)誤處理

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默認(rèn)除以0的結(jié)果是NULL，而不是報(bào)錯(cuò)：

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模式的話，就會(huì)報(bào)錯(cuò)：

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

2.表輔助功能

有時(shí)候修改SQL模式可能會(huì)導(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模式，就可能再也不能訪問(wèn)這個(gè)表了：

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模式也會(huì)影響分區(qū)表的復(fù)制。在主備間使用不同的SQL模式可能會(huì)導(dǎo)致分區(qū)表達(dá)式主備上執(zhí)行是不同的結(jié)果（而在阿里主備切換是很正常的操作）；這也會(huì)導(dǎo)致在主備復(fù)制過(guò)程中，不同分區(qū)間的數(shù)據(jù)分布不同；也有可能導(dǎo)致在主庫(kù)上的分區(qū)表insert成功，而備庫(kù)上失敗�；�于上述情況，最好的解決辦法是保證主備間的SQL模式要保持一致（這個(gè)是DBA在運(yùn)維過(guò)程中需要注意的）。

性能注意事項(xiàng)

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

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

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

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

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

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

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

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

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

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

如果要處理ALTER TABLE語(yǔ)句需要402個(gè)文件描述符，原來(lái)100個(gè)分區(qū)*2個(gè)+101個(gè)新分區(qū)*2。這是因?yàn)樵谥匦陆M織表數(shù)據(jù)時(shí)，必須打開(kāi)所有的（新舊）分區(qū)。所以建議在執(zhí)行這些操作時(shí)，要確保--open-files-limit要設(shè)置的大些。

表鎖

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

存儲(chǔ)引擎

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

索引；分區(qū)修剪

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

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

load data性能

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

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

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

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

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

不支持Query cache

分區(qū)表不支持query cache，在分區(qū)表的查詢中自動(dòng)避開(kāi)了query cache。也就是說(shuō)在分區(qū)表的查詢語(yǔ)句中query cache是不起作用的。

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

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

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

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

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

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

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

ALTER TABLE ... ORDER BY

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

REPLACE語(yǔ)句在修改primary key上的效率

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

全文索引

分區(qū)表不支持全文索引或者搜索，即使分區(qū)表的存儲(chǔ)引擎是InnoDB或者M(jìn)yISAM也不行。

空間列

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

臨時(shí)表

不能對(duì)臨時(shí)表進(jìn)行分區(qū)(Bug #17497)。

日志表

不能對(duì)日志表進(jìn)行分區(qū)，如果強(qiáng)制執(zhí)行ALTER TABLE ... PARTITION BY ... 語(yǔ)句會(huì)報(bào)錯(cuò)。

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

分區(qū)鍵必須是整形或者結(jié)果是整形的表達(dá)式。不能用結(jié)果為ENUM類型的表達(dá)式。因?yàn)檫@種類型的表達(dá)式可能是NULL。

下面兩種情況是例外的：

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

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ū)的話，可能會(huì)用string。例如，下面的創(chuàng)建語(yǔ)句是合法的：

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列類型。

子查詢

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

子分區(qū)的問(wèn)題

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

SUBPARTITION BY KEY要求顯示指定子分區(qū)列，不像PARTITION BY KEY可以省略（這種情況下會(huì)默認(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ū)），使用下面語(yǔ)句：

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

前面的語(yǔ)句其實(shí)和下面的語(yǔ)句是一樣的：

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

但是，如果嘗試使用缺省列作為子分區(qū)列，創(chuàng)建子分區(qū)表的話，以下語(yǔ)句將失敗，必須指定該語(yǔ)句才能執(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ù)字典和索引字典選項(xiàng)

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

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

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

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

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

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

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

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

導(dǎo)出選項(xiàng)

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

以上就是詳解MySQL分區(qū)表的局限和限制的代碼實(shí)例的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！