[分享]数据库:Partitioning Keys, Primary Keys, and Unique Keys_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2346 | 回复: 0

主题： [分享]数据库:Partitioning Keys, Primary Keys, and Unique Keys

leixianp

注册用户

等级：新兵
经验：57
发帖：64
精华：0
注册：2011-7-26
状态：离线
发送短消息息给leixianp

加好友发送短消息息给leixianp

发消息

发表于：

2014-12-11 10:49:56 | [全部帖] [楼主帖]

楼主

本文讨论了用于分区的键跟主键和唯一键的关系。管制这个关系的规则可以表述如下：在一个分区表中，所有用于分区表达式的列必须是该表每个可能存在的唯一键的一部分。

补充说明：怎么理解呢？首先条件是：1，分区的表，不打算进行分区的表不算；2，表存在唯一键，主键也算是唯一键，即primary key和unique key，则表都没有任何primary key 或者unique key的也不算，3，在满足1和2的情况下，对表中的每一个唯一键（注意是每一个）的列必须包含分区表达式的所有列。

In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:

翻译：用另外一种方式说就是，表中每一个唯一键必须用到表中的分区表达式的每一列（这也包括表的主键，因为从定义上看主键也是一个唯一键，特例的情况将会在本文稍后进行讨论）。比如，下面每一个创建表的语句均是不合法的。

CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

补充说明：唯一键是col1和col2，分区的列是col3，唯一键没有包含col3，所以非法，分区的键应该只能是col1，col2或者col1和col2的计算表达式。

CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

补充说明：上面的情况一般人可能认为是没问题的，但实际上也是非法的，因为，分区的key是col1和col3，表中的每一个唯一键没有包含所有col1和col3，虽然所有唯一键加在一起

包含了col1和col3，但不是每一个唯一键都包含，因此不符合要求。

In each case, the proposed table would have at least one unique key that does not include all columns used in the partitioning expression.
Each of the following statements is valid, and represents one way in which the corresponding invalid table creation statement could be made to work:

翻译：在各种情况下，一个建议的表应该至少有一个唯一键，并且该唯一键并不包含分区表达式中的所有列。下面每一个语句都是合法的，且代表一种方式：相应非法的建表语句能够工作。（后面一句翻译感觉不是很顺，大家有好的建议可以一起分享）

CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
This example shows the error produced in such cases:

翻译：这些例子展示了如下情形中产生的错误。

mysql> CREATE TABLE t3 (
->     col1 INT NOT NULL,
->     col2 DATE NOT NULL,
->     col3 INT NOT NULL,
->     col4 INT NOT NULL,
->     UNIQUE KEY (col1, col2),
->     UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col1 + col3)
-> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

翻译：一个主键必须包含表中分区函数的所有列。

The CREATE TABLE statement fails because both col1 and col3 are included in the proposed partitioning key, but neither of these columns is part of both of unique keys on the table. This shows one possible fix for the invalid table definition:

翻译：这个create table 语句之所以创建失败是因为col1和col3都包含在分区的键中，但这些列均不是表中两个唯一键的一部分。下面展示了这个非法表定义的一个可能的修复方案。

mysql> CREATE TABLE t3 (
->     col1 INT NOT NULL,
->     col2 DATE NOT NULL,
->     col3 INT NOT NULL,
->     col4 INT NOT NULL,
->     UNIQUE KEY (col1, col2, col3),
->     UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col3)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)
In this case, the proposed partitioning key col3 is part of both unique keys, and the table creation statement succeeds.
The following table cannot be partitioned at all, because there is no way to include in a partitioning key any columns that belong to both unique keys:

翻译：在这种情况下，分区键col3均是两个唯一键的一部分了，因此表创建语句执行成功。下列的表根本不能进行分区操作，因为没有办法找到一个分区键均包含属于两个唯一键的任何列。（补充说明：因为两个唯一键的列没有交集）

CREATE TABLE t4 (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3),
UNIQUE KEY (col2, col4)
);
Since every primary key is by definition a unique key, this restriction also includes the table's primary key, if it has one. For example, the next two statements are invalid:

翻译：既然每一个主键从定义上看都是一个唯一键，这个约束同样也适用于主键的情况。比如，若表存在主键，则接下来的两个语句是非法的。

CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
In both cases, the primary key does not include all columns referenced in the partitioning expression. However, both of the next two statements are valid:

翻译：在[如上的]两个例子中，主键没有包含所有分区表达式中引用到的所有列，然而，接下来的两个语句却是合法的：

CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t8 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
If a table has no unique keys—this includes having no primary key—then this restriction does not apply, and you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type.

翻译：若一个表没有唯一键 - 这包括没有主键- 则这个约束则不适用了。并且这时只要列的类型跟分区类型匹配，你可以使用任何一列或者多列用在分区表达式。

For the same reason, you cannot later add a unique key to a partitioned table unless the key includes all columns used by the table's partitioning expression. Consider the partitioned table created as shown here:

翻译：同理，你不能稍后增加一个唯一键到一个分区的表中，除非该键包含了分区表达式中的所有列。考虑这里创建的一个分区表：

mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
->     PARTITION BY RANGE(c1) (
->         PARTITION p0 VALUES LESS THAN (10),
->         PARTITION p1 VALUES LESS THAN (20),
->         PARTITION p2 VALUES LESS THAN (30),
->         PARTITION p3 VALUES LESS THAN (40)
->     );
Query OK, 0 rows affected (0.12 sec)
It is possible to add a primary key to t_no_pk using either of these ALTER TABLE statements:

翻译：有可能使用下面这些alter table语句之一增加一个主键到表t_no_pk中：

# possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

补充说明：因为增加的主键是c1，该列包含了分区表达式中的所有列c1，所以操作成功。

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
#  use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

补充说明：因为增加的主键是c1和c2，同理也包含了分区表达式中的所有列c1，所以操作成功。

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
However, the next statement fails, because c1 is part of the partitioning key, but is not part of the proposed primary key:

翻译：然而，下列的语句失败了，因为c1是分区键的一部分，但不是主键的一部分。

# fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
Since t_no_pk has only c1 in its partitioning expression, attempting to adding a unique key on c2 alone fails. However, you can add a unique key that uses both c1 and c2.
These rules also apply to existing nonpartitioned tables that you wish to partition using ALTER TABLE ... PARTITION BY. Consider a table np_pk created as shown here:

翻译：由于t_no_pk的分区表达式只有列c1，尝试增加只有c2的唯一键操作将失败。然而，你可以增加一个既包含c1又包含c2的主键。这些规则同样适用于已存在的非分区表，这些表你希望通过使用alter table ... partition by进行分区的。考虑表np_pk创建如下所示：

mysql> CREATE TABLE np_pk (
->     id INT NOT NULL AUTO_INCREMENT,
->     name VARCHAR(50),
->     added DATE,
->     PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)
The following ALTER TABLE statement fails with an error, because the added column is not part of any unique key in the table:

翻译：下面的alter table语句执行抛出错误，因为added 列不是表中唯一键的一部分。

mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
However, this statement using the id column for the partitioning column is valid, as shown here:

翻译：然而，这个语句使用id列作为分区列则是合法的，如下所示：

mysql> ALTER TABLE np_pk
->     PARTITION BY HASH(id)
->     PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
In the case of np_pk, the only column that may be used as part of a partitioning expression is id; if you wish to partition this table using any other column or columns in the partitioning expression, you must first modify the table, either by adding the desired column or columns to the primary key, or by dropping the primary key altogether.

翻译：在np_pk的这个例子中，唯一能够作为分区表达式一部分的列只有id，若你希望可以使用其他一列或者多列作为分区表达式，你首先必须修改表结构，总而言之，要么增加希望[加到分区表达式中]的1列或者多列到主键中，要么删除主键。

--转自