Mysql数据分区局限性大总结
参考博文：http://www.sziwap.com/wordpress/archives/10
?
对Mysql分区表的局限性做了一些总结，因为个人能力以及测试环境的原因，有可能有错误的地方，还请大家看到能及时指出，当然有兴趣的朋友可以去官方网站查阅。供以后参考用：
本文测试的版本
mysql> select version();
+------------+
| version()? |
+------------+
| 5.1.33-log |
+------------+
1 row in set (0.00 sec)
一、关于Partitioning Keys, Primary Keys, and Unique Keys的限制
在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。
这句话也许不好理解，我们做几个实验：
CREATE TABLE t1
(????? id INT NOT NULL,
?????? uid INT NOT NULL,
?????? PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = INNODB,
?PARTITION p1 VALUES LESS THAN(10) ENGINE = INNODB
);
?
CREATE TABLE t1
(????? id INT NOT NULL,
?????? uid INT NOT NULL,
?????? PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',
?PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp'
);
?
mysql> CREATE TABLE t1
??? -> (????? id INT NOT NULL,
??? ->??????? uid INT NOT NULL,
??? ->??????? PRIMARY KEY (id),
??? ->??????? UNIQUE KEY (uid)
??? -> )
??? -> PARTITION BY RANGE (id)
??? -> (PARTITION p0 VALUES LESS THAN(5),
??? ->? PARTITION p1 VALUES LESS THAN(10)
??? -> );
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
二、关于存储引擎的限制
2.1 MERGE引擎不支持分区，分区表也不支持merge。
2.2 FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。
2.3 CSV引擎不支持分区
2.4 BLACKHOLE引擎不支持分区
2.5 在NDBCLUSTER引擎上使用分区表，分区类型只能是KEY(or LINEAR KEY) 分区。
2.6 当升级MYSQL的时候，如果你有使用了KEY分区的表（不管是什么引擎，NDBCLUSTER除外），那么你需要把这个表dumped在 reloaded。
2.7 分区表的所有分区或者子分区的存储引擎必须相同，这个限制也许会在以后的版本取消。
不指定任何引擎（使用默认引擎）。
所有分区或者子分区指定相同引擎。
三、关于函数的限制
在mysql5.1中建立分区表的语句中，只能包含下列函数：
ABS()
CEILING() and FLOOR() （在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型），例如
mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
??? -> PARTITION p0 VALUES IN (1,3,5),
??? -> PARTITION p1 VALUES IN (2,4,6)
??? -> );;
ERROR 1491 (HY000): The PARTITION function returns the wrong type
?
mysql> CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )(
??? -> PARTITION p0 VALUES IN (1,3,5),
??? -> PARTITION p1 VALUES IN (2,4,6)
??? -> );
Query OK, 0 rows affected (0.01 sec)
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK()
四、其他限制
4.1 对象限制
下面这些对象在不能出现在分区表达式
Stored functions, stored procedures, UDFs, or plugins.
Declared variables or user variables.
4.2 运算限制
支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。 |, &, ^, <<, >>, , ~ 等不允许出现在分区表达式。
4.3 sql_mode限制
官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样。
4.4 Performance considerations.(省略)
4.5 最多支持1024个分区，包括子分区。
当你建立分区表包含很多分区但没有超过1024限制的时候，如果报错 Got error 24 from storage engine，那意味着你需要增大open_files_limit参数。
4.6 不支持外键。MYSQL中，INNODB引擎才支持外键。
4.7 不支持FULLTEXT indexes（全文索引），包括MYISAM引擎。
mysql> CREATE TABLE articles (
??? -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
??? -> title VARCHAR(200),
??? -> body TEXT,
??? -> FULLTEXT (title,body)
??? -> )
??? -> PARTITION BY HASH(id)
??? -> PARTITIONS 4;
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
4.8 不支持spatial column types。
4.9 临时表不能被分区。
mysql> CREATE Temporary TABLE t1
??? -> (????? id INT NOT NULL,
??? ->??????? uid INT NOT NULL,
??? ->??????? PRIMARY KEY (id)
??? -> )
??? -> PARTITION BY RANGE (id)
??? -> (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM,
??? ->? PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM
??? -> );
ERROR 1562 (HY000): Cannot create temporary table with partitions
4.10 log table不支持分区。
mysql> alter table mysql.slow_log PARTITION BY KEY(start_time) PARTITIONS 2;
ERROR 1221 (HY000): Incorrect usage of PARTITION and log table
4.11 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。
mysql> CREATE TABLE tkc (c1 CHAR)
??? -> PARTITION BY KEY(c1)
??? -> PARTITIONS 4;
Query OK, 0 rows affected (0.00 sec)
?
mysql> CREATE TABLE tkc2 (c1 CHAR)
??? -> PARTITION BY HASH(c1)
??? -> PARTITIONS 4;
ERROR 1491 (HY000): The PARTITION function returns the wrong type
?
mysql> CREATE TABLE tkc3 (c1 INT)
??? -> PARTITION BY HASH(c1)
??? -> PARTITIONS 4;
Query OK, 0 rows affected (0.00 sec)
4.12 分区键不能是一个子查询。 A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL
4.13 只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。
4.14 分区表不支持Key caches。
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CACHE INDEX login,user_msg,user_msg_p IN keycache1;
+-----------------+--------------------+----------+---------------------------------+
| Table?????????? | Op???????????????? | Msg_type | Msg_text??????????????????????????????????????????????????????????? |
+-----------------+--------------------+----------+---------------------------------+
| test.login????? | assign_to_keycache | status?? | OK????????????????????????????????????????????????????????????????? |
| test.user_msg?? | assign_to_keycache | status?? | OK????????????????????????????????????????????????????????????????? |
| test.user_msg_p | assign_to_keycache | note???? | The storage engine for the table doesn't support assign_to_keycache |
+-----------------+--------------------+----------+---------------------------------+
3 rows in set (0.00 sec)
4.15 分区表不支持INSERT DELAYED.
mysql> insert? DELAYED into user_msg_p values(18156629,0,0,0,0,0,0,0,0,0);
ERROR 1616 (HY000): DELAYED option not supported for table 'user_msg_p'
4.16 DATA DIRECTORY 和 INDEX DIRECTORY 参数在分区表将被忽略。
这个限制应该不存在了：
mysql> CREATE TABLE t1
??? -> (????? id INT NOT NULL,
??? ->??????? uid INT NOT NULL,
??? ->??????? PRIMARY KEY (id)
??? -> )
??? -> PARTITION BY RANGE (id)
??? -> (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',
??? ->? PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp'
??? -> );
Query OK, 0 rows affected (0.01 sec)
4.17 分区表不支持mysqlcheck和myisamchk
在5.1.33版本中已经支持mysqlcheck和myisamchk
./mysqlcheck -u -p -r test user_msg_p;
test.user_msg_p??????????????????????????????????? OK
?
./myisamchk -i /u01/data/test/user_msg_p#P#p0.MYI
Checking MyISAM file: /u01/data/test/user_msg_p#P#p0.MYI
Data records: 4423615?? Deleted blocks:?????? 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
Key:? 1:? Keyblocks used:? 98%? Packed:??? 0%? Max levels:? 4
Total:??? Keyblocks used:? 98%? Packed:??? 0%
?
User time 0.97, System time 0.02
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 324, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 1, Involuntary context switches 5
4.18 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。
4.19 在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行order by。
