Mysql分区表局限性总结

2024-04-27 09:18

本文主要是介绍Mysql分区表局限性总结,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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 参数在分区表将被忽略。
这个限制应该不存在了:
注:在创建InnoDB引擎的情况下,还是会忽略,即使已经打开innodb_file_per_table,还是需要手动做符号连接。
例如:
drop table if exists ts ;
CREATE TABLE ts (id INT, purchased DATE)
ENGINE=innodb
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(id)
(
PARTITION p0 VALUES LESS THAN (1990)
(
SUBPARTITION s0
DATA DIRECTORY=’/usr/local/mysql/data0′
INDEX DIRECTORY=’/usr/local/mysql/index0′,
SUBPARTITION s1
DATA DIRECTORY=’/usr/local/mysql/data1′
INDEX DIRECTORY=’/usr/local/mysql/index1′
),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION s2
DATA DIRECTORY=’/usr/local/mysql/data1′
INDEX DIRECTORY=’/usr/local/mysql/index1′,
SUBPARTITION s3
DATA DIRECTORY=’/usr/local/mysql/data2′
INDEX DIRECTORY=’/usr/local/mysql/index2′
)
);
手动做完符号连接后如下:
-rw-rw—- 1 mysql mysql     8596 02-28 22:07 ts.frm
-rw-rw—- 1 mysql mysql       56 02-28 22:07 ts.par
lrwxrwxrwx 1 mysql mysql       40 02-28 22:30 ts#P#p0#SP#s0.ibd -> /usr/local/mysql/data0/ts#P#p0#SP#s0.ibd
lrwxrwxrwx 1 mysql mysql       40 02-28 22:31 ts#P#p0#SP#s1.ibd -> /usr/local/mysql/data0/ts#P#p0#SP#s1.ibd
lrwxrwxrwx 1 mysql mysql       40 02-28 22:31 ts#P#p1#SP#s2.ibd -> /usr/local/mysql/data0/ts#P#p1#SP#s2.ibd
lrwxrwxrwx 1 mysql mysql       40 02-28 22:31 ts#P#p1#SP#s3.ibd -> /usr/local/mysql/data0/ts#P#p1#SP#s3.ibd

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

5.18 分区表的分区键创建索引,那么这个索引也将被分区。分区键没有全局索引一说。
5.19 在分区表使用ALTER TABLE … ORDER BY,只能在每个分区内进行order by。

这篇关于Mysql分区表局限性总结的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/940107

相关文章

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接?1. 内连接(INNER JOIN)2. 左连接(LEFT JOIN 或 LEFT

Android数据库Room的实际使用过程总结

《Android数据库Room的实际使用过程总结》这篇文章主要给大家介绍了关于Android数据库Room的实际使用过程,详细介绍了如何创建实体类、数据访问对象(DAO)和数据库抽象类,需要的朋友可以... 目录前言一、Room的基本使用1.项目配置2.创建实体类(Entity)3.创建数据访问对象(DAO

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》:本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步:先停止mysql服务,一定要停止!方式一:通过命令行关闭mysql服务方式二:通过服务项关闭

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql

Java向kettle8.0传递参数的方式总结

《Java向kettle8.0传递参数的方式总结》介绍了如何在Kettle中传递参数到转换和作业中,包括设置全局properties、使用TransMeta和JobMeta的parameterValu... 目录1.传递参数到转换中2.传递参数到作业中总结1.传递参数到转换中1.1. 通过设置Trans的

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6

Linux下MySQL8.0.26安装教程

《Linux下MySQL8.0.26安装教程》文章详细介绍了如何在Linux系统上安装和配置MySQL,包括下载、解压、安装依赖、启动服务、获取默认密码、设置密码、支持远程登录以及创建表,感兴趣的朋友... 目录1.找到官网下载位置1.访问mysql存档2.下载社区版3.百度网盘中2.linux安装配置1.

C# Task Cancellation使用总结

《C#TaskCancellation使用总结》本文主要介绍了在使用CancellationTokenSource取消任务时的行为,以及如何使用Task的ContinueWith方法来处理任务的延... 目录C# Task Cancellation总结1、调用cancellationTokenSource.

PostgreSQL如何用psql运行SQL文件

《PostgreSQL如何用psql运行SQL文件》文章介绍了两种运行预写好的SQL文件的方式:首先连接数据库后执行,或者直接通过psql命令执行,需要注意的是,文件路径在Linux系统中应使用斜杠/... 目录PostgreSQ编程L用psql运行SQL文件方式一方式二总结PostgreSQL用psql运