Mysql分区表局限性总结

zjxhx

　　Mysql5.1已经发行很久了，本文根据官方文档的翻译和自己的一些测试，对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 (

　　->>
　　->>　　-> 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>>　　ERROR 1221 (HY000): Incorrect usage of PARTITION and log table
　　5.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)
　　5.12 分区键不能是一个子查询。 A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL
　　5.13 只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。
　　5.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)
　　5.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'
　　5.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)
　　5.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 dat