Mysql分区表局限性总结

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

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 (

   -> 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

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










本文转自 jxwpx 51CTO博客,原文链接:http://blog.51cto.com/jxwpx/213279,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
相关文章
实时数仓 Hologres产品使用合集之如何将MySQL数据初始化到分区表中
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
DataWorks产品使用合集之当需要将数据从ODPS同步到RDS,且ODPS表是二级分区表时,如何同步所有二级分区的数据
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
118 7
【MySQL技术内幕】4.8-分区表
【MySQL技术内幕】4.8-分区表
216 0
MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)
MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)
112 0
MySQL分区表详解
在我们日常处理海量数据的过程中,如何有效管理和优化数据库一直是一个既重要又具有挑战性的问题
147 0