手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆

Mysql分区表局限性总结

首页 > DataBase >

Submitted by gouki on 2010, March 18, 8:59 AM. DataBase

昨天晚上asers.z问我怎么样使得数据在搜索的时候和58.com差不多，而且展示数据的速度要快。我一直想着用mysql的分区表解决，而乔楚（乔大姐）则认为是采用sphinx来解决。
但后来我找了一个资料才发现，原来分区表还是有局限性的，比如他就不支持全文索引。我是看这里看到的。。
--START--

Mysql5.1已经发行很久了，本文根据官方文档的翻译和自己的一些测试，对Mysql分区表的局限性做了一些总结，因为个人能力以及测试环境的 原因，有可能有错误的地方，还请大家看到能及时指出，当然有兴趣的朋友可以去官 方网站查阅。

本文测试的版本

XML/HTML代码
  1. mysql> select version();  
  2. +------------+  
  3. | version()  |  
  4. +------------+  
  5. | 5.1.33-log |   
  6. +------------+  
  7. 1 row in set (0.00 sec)  

一、关于Partitioning Keys, Primary Keys, and Unique Keys的限制

在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。
这句话也许不好理解，我们做几个实验：

SQL代码
  1. CREATE TABLE t1     
  2. (      id INT NOT NULL,        
  3.        uid INT NOT NULL,  
  4.        PRIMARY KEY (id)  
  5. )  
  6. PARTITION BY RANGE (id)     
  7. (PARTITION p0 VALUES LESS THAN(5) ENGINE = INNODB,  
  8.  PARTITION p1 VALUES LESS THAN(10) ENGINE = INNODB  
  9. );  
  10.    
  11. CREATE TABLE t1     
  12. (      id INT NOT NULL,        
  13.        uid INT NOT NULL,  
  14.        PRIMARY KEY (id)  
  15. )  
  16. PARTITION BY RANGE (id)     
  17. (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',  
  18.  PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp'  
  19. );  
  20.    
  21. mysql> CREATE TABLE t1     
  22.     -> (      id INT NOT NULL,        
  23.     ->        uid INT NOT NULL,  
  24.     ->        PRIMARY KEY (id),  
  25.     ->        UNIQUE KEY (uid)  
  26.     -> )  
  27.     -> PARTITION BY RANGE (id)     
  28.     -> (PARTITION p0 VALUES LESS THAN(5),  
  29.     ->  PARTITION p1 VALUES LESS THAN(10)  
  30.     -> );  
  31. 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类型），例如

XML/HTML代码
  1. mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(  
  2.     -> PARTITION p0 VALUES IN (1,3,5),  
  3.     -> PARTITION p1 VALUES IN (2,4,6)  
  4.     -> );;  
  5. ERROR 1491 (HY000): The PARTITION function returns the wrong type  
  6.    
  7. mysql> CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )(  
  8.     -> PARTITION p0 VALUES IN (1,3,5),  
  9.     -> PARTITION p1 VALUES IN (2,4,6)  
  10.     -> );  
  11. 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引擎。

SQL代码
  1. mysql> CREATE TABLE articles (  
  2.     -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,  
  3.     -> title VARCHAR(200),  
  4.     -> body TEXT,  
  5.     -> FULLTEXT (title,body)  
  6.     -> )  
  7.     -> PARTITION BY HASH(id)  
  8.     -> PARTITIONS 4;  
  9. ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes  
4.8 不支持spatial column types。
4.9 临时表不能被分区。
SQL代码
  1. mysql> CREATE Temporary TABLE t1     
  2.     -> (      id INT NOT NULL,        
  3.     ->        uid INT NOT NULL,  
  4.     ->        PRIMARY KEY (id)  
  5.     -> )  
  6.     -> PARTITION BY RANGE (id)     
  7.     -> (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM,  
  8.     ->  PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM  
  9.     -> );  
  10. ERROR 1562 (HY000): Cannot create temporary table with partitions  
4.10 log table不支持分区。
SQL代码
  1. mysql> alter table mysql.slow_log PARTITION BY KEY(start_time) PARTITIONS 2;  
  2. ERROR 1221 (HY000): Incorrect usage of PARTITION and log table  
5.11 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。
SQL代码
  1. mysql> CREATE TABLE tkc (c1 CHAR)  
  2.     -> PARTITION BY KEY(c1)  
  3.     -> PARTITIONS 4;  
  4. Query OK, 0 rows affected (0.00 sec)  
  5.    
  6. mysql> CREATE TABLE tkc2 (c1 CHAR)  
  7.     -> PARTITION BY HASH(c1)  
  8.     -> PARTITIONS 4;  
  9. ERROR 1491 (HY000): The PARTITION function returns the wrong type  
  10.    
  11. mysql> CREATE TABLE tkc3 (c1 INT)  
  12.     -> PARTITION BY HASH(c1)  
  13.     -> PARTITIONS 4;  
  14. 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。

SQL代码
  1. mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3. mysql> CACHE INDEX login,user_msg,user_msg_p IN keycache1;  
  4. +-----------------+--------------------+----------+---------------------------------------------------------------------+  
  5. | Table           | Op                 | Msg_type | Msg_text                                                            |  
  6. +-----------------+--------------------+----------+---------------------------------------------------------------------+  
  7. | test.login      | assign_to_keycache | status   | OK                                                                  |   
  8. | test.user_msg   | assign_to_keycache | status   | OK                                                                  |   
  9. | test.user_msg_p | assign_to_keycache | note     | The storage engine for the table doesn't support assign_to_keycache |   
  10. +-----------------+--------------------+----------+---------------------------------------------------------------------+  
  11. 3 rows in set (0.00 sec)  
5.15 分区表不支持INSERT DELAYED.
SQL代码
  1. mysql> insert  DELAYED into user_msg_p values(18156629,0,0,0,0,0,0,0,0,0);  
  2. ERROR 1616 (HY000): DELAYED option not supported for table 'user_msg_p'  
5.16 DATA DIRECTORY 和 INDEX DIRECTORY 参数在分区表将被忽略。
这个限制应该不存在了：
SQL代码
  1. mysql> CREATE TABLE t1     
  2.     -> (      id INT NOT NULL,        
  3.     ->        uid INT NOT NULL,  
  4.     ->        PRIMARY KEY (id)  
  5.     -> )  
  6.     -> PARTITION BY RANGE (id)     
  7.     -> (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',  
  8.     ->  PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp'  
  9.     -> );  
  10. Query OK, 0 rows affected (0.01 sec)  
5.17 分区表不支持mysqlcheck和myisamchk
在5.1.33版本中已经支持mysqlcheck和myisamchk
SQL代码
  1. ./mysqlcheck -u -p -r test user_msg_p;  
  2. test.user_msg_p                                    OK  
  3.    
  4. ./myisamchk -i /u01/data/test/user_msg_p#P#p0.MYI  
  5. Checking MyISAM file: /u01/data/test/user_msg_p#P#p0.MYI  
  6. Data records: 4423615   Deleted blocks:       0  
  7. - check file-size  
  8. - check record delete-chain  
  9. - check key delete-chain  
  10. - check index reference  
  11. - check data record references index: 1  
  12. Key:  1:  Keyblocks used:  98%  Packed:    0%  Max levels:  4  
  13. Total:    Keyblocks used:  98%  Packed:    0%  
  14.    
  15. User time 0.97, System time 0.02  
  16. Maximum resident set size 0, Integral resident set size 0  
  17. Non-physical pagefaults 324, Physical pagefaults 0, Swaps 0  
  18. Blocks in 0 out 0, Messages in 0 out 0, Signals 0  
  19. Voluntary context switches 1, Involuntary context switches 5  
5.18 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。
5.19 在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行order by。

--EOF--

看了一下，居然有这么多的限制，唉。不是特别敢用了，再加上现在NoSQL开始风行，天知道以后会怎么样。

上面的文章来自：http://www.bigheaddba.net/article/y2009/339_mysql%E5%88%86%E5%8C%BA%E8%A1%A8%E5%B1%80%E9%99%90%E6%80%A7%E6%80%BB%E7%BB%93.html

 

 

 



本站采用创作共享版权协议, 要求署名、非商业和保持一致. 本站欢迎任何非商业应用的转载, 但须注明出自"易栈网-膘叔", 保留原始链接, 此外还必须标注原文标题和链接.

Tags: mysql, partition

« 上一篇 | 下一篇 »

只显示10条记录相关文章

慎用mysql的enum字段 (浏览: 57676, 评论: 1)
mysql:Lost connection to MySQL server at 'reading initial communication packet', system error: 0 (浏览: 43522, 评论: 2)
超详细：在Mac OS X中配置Apache ＋ PHP ＋ MySQL (浏览: 41860, 评论: 1)
联合索引的经典例子 (浏览: 37853, 评论: 1)
在mysql 里实现查询汉字的拼音首字母[摘] (浏览: 37054, 评论: 4)
MySQL各存储引擎(INNODB,MyISAM等)的区别及其启动方法 (浏览: 35524, 评论: 0)
MYSQL索引之小小分析 (浏览: 34270, 评论: 3)
MySQL 5 中 blob 类型字段的插入[转] (浏览: 34140, 评论: 0)
用TIMESTAMP类型取代INT和DATETIME[转] (浏览: 33069, 评论: 1)
Mysql动态修改参数 (浏览: 31436, 评论: 0)

发表评论

评论内容 (必填):