MySQL Study之--分区键和唯一性索引及主键之关系

最新推荐文章于 2024-09-29 18:25:30 发布
最新推荐文章于 2024-09-29 18:25:30 发布
阅读量8.3k 收藏 10
点赞数
分类专栏： 数据库技术 文章标签： mysql
MySQL Study之（翻译）--分区键和唯一性索引及主键之关系

以下文档翻译自MySQL官方文档，水平有限，翻译不当之处，请大家指正微笑。

案例全部验证：

Partitioning Keys, Primary Keys, and Unique Keys

分区键、               主键、                     唯一性索引

This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

这一节将讨论分区键和主键索引及唯一性索引之间的关系：

可以这样说：

在分区表上，用于分区表达式里的每一个字段都必须是唯一性索引的一部分。

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:

换句话说，表上的每一个唯一性索引必须用于分区表的表达式上（其中包括主键索引）。

例如：以下案例，建立分区表是无效的。

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

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

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

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

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:

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

Query OK, 0 rows affected (4.70 sec)

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

Query OK, 0 rows affected (2.93 sec)

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 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

mysql> CREATE TABLE t3a (
    ->          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 + col2)
    ->     PARTITIONS 4;

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

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 + col2+col3 )
    PARTITIONS 4;

ERROR 1503 (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:

    以下cretate 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(col1 + col2+col3 )
    ->     PARTITIONS 4;

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

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 (3.11 sec)

In this case, the proposed partitioning key col3 is part of both unique keys, and the table creation statement succeeds.

以上可以看出，col3同时属于两个唯一性索引的键，所以create table执行成功。

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:

   如下所示，此分区表是无法建立的，因为没有一个分区键，可以同时属于两个唯一性索引的键。

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:

主键也属于唯一性索引，所以以上规则适合于primary key。

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

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

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

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

mysql> CREATE TABLE t6 (
    ->          col1 INT NOT NULL,
    ->          col2 DATE NOT NULL,
    ->          col3 INT NOT NULL,
    ->        col4 INT NOT NULL,
    ->          PRIMARY KEY(col1,col2, col3),
    ->          UNIQUE KEY(col2)
    ->      )
    ->      PARTITION BY HASH( YEAR(col2) )
    ->      PARTITIONS 4;

Query OK, 0 rows affected (2.88 sec)

mysql> CREATE TABLE t6a(
    ->          col1 INT NOT NULL,
    ->          col2 DATE NOT NULL,
    ->          col3 INT NOT NULL,
    ->        col4 INT NOT NULL,
    ->          PRIMARY KEY(col1,col2, col3)
    ->      )
    ->      PARTITION BY HASH( YEAR(col2) )
    ->      PARTITIONS 4;

Query OK, 0 rows affected (3.51 sec)

mysql> CREATE TABLE t6b(
    ->          col1 INT NOT NULL,
    ->          col2 DATE NOT NULL,
    ->          col3 INT NOT NULL,
    ->        col4 INT NOT NULL,
    ->          PRIMARY KEY(col1,col2, col3)
    ->      )
    ->      PARTITION BY HASH( col3)
    ->      PARTITIONS 4;

Query OK, 0 rows affected (4.26 sec)

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:

以上两个案例，分区表达式里的字段不包含所有的主键字段。

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

Query OK, 0 rows affected (4.40 sec)

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

Query OK, 0 rows affected (3.51 sec)

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:

#  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

# 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

# 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，不属于primary key。

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

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语句将会失败，因为分区键，不包含于唯一性索引。

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，包含于主键primary key

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.

  如以上案例，只有id字段可以作为分区表达式键，如果你还想使用其他的字段用于分区表达式中，你必须修改表结构，添加字段到你的主键里，或者删除主键。

    

 


客居天涯
关注
专栏目录
零基础入门 全角度解读企业主流数据库MySQL8.0 --- 复习笔记
争当做一个有趣的人
03-09 1399
〖课程介绍〗: 本课程遵循“用户体验至上”的原则，不敢说设计精良，尽善尽美，却也竭尽全力去锻造良好的学习体验。包含三方面，（1）设计之初：遵循“有教无类，教学相长”的原则；（2）学习路线：采用循序渐进的教学模式，带你从了解到熟悉，到掌握，到精通，最后到深谙；（3）学习内容：课程遵循前沿性，以当前最新企业主流的MySQL8.0版本为内容。以“实用”为课程最高宗旨，带你搞定工作中SQL优化 痛点问题，最终的目的是：助力个人能力的快速提升，最终服务工作所需，助你占据不可替代的一席之地 第1章 【蓦然回首】开篇..
MySQL数据库之分库分表方案
CXY_QIQI的博客
05-14 8576
数据库之互联网常用分库分表方案 一、数据库瓶颈 1、IO瓶颈 2、CPU瓶颈 二、分库分表 1、水平分库 2、水平分表 3、垂直分库 4、垂直分表 三、分库分表工具 四、分库分表步骤 五、分库分表问题 1、非partition key的查询问题（水平分库分表，拆分策略为常用的hash法） 2、非partition key跨库跨表分页查询问题（水平分库分表，拆分策略为常用的hash法） 3、扩容问题（水平分库分表，拆分策略为常用的hash法） 六、分库分表总结 七、分库分表示例
mysql建立分区索引_mysql创建分区索引
weixin_39553600的博客
01-21 2506
该楼层疑似违规已被系统折叠隐藏此楼查看此楼mysql创建分区索引(一)分区表:把所有的数据放在一个表中,但是物理存储数据会根据一定规则存放到不同的文件中(二)什么时候使用分区表?数据比较大时候,数以亿记或者数以TB记的数据,如果使用索引在空间和维护消耗巨大,甚至索引没有效果了.例子:查看是否支持分区show variables like '%partition%';创建一个普通表create t...
mysql 分区 唯一_Mysql分区:分区键和唯一索引主键的关系
weixin_36018183的博客
01-19 548
今天主要介绍下面这几个错误:ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioningERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table’s partitioning function主键必须...
pg建唯一约束报错
最新发布
s07aser123的专栏
09-29 313
pg数据库增加唯一约束报错
mysql分区-索引
麻辣香蕉
12-15 1464
mysql分区 mysql索引 1、添加索引 CREATE INDEX index_name ON my_table(column_name); 2、根据索引查询 具体查询： SELECT * FROM table_name WHERE column_1=column_2;(为column_1建立了索引) 或者模糊查询 SELECT * FROM table_name WHERE column_1 LIKE '%三' SELECT * FROM table_name WHERE column_1 L.
mysql的分区和索引详解_Mysql分区和索引记录
weixin_36226933的博客
02-28 1940
分区前提：当数据量达到800w的时候，用create_date进行设置索引查询，也需要花费5s以上，所以还得进行分区操作，达到更快的效果。设置好分区后，查询就是0.5s。开始操作：用create_date创建分区，需要把create_date设置为主键所以，首先，建id和create_date的联合主键CREATE TABLE `t_service_object` (`id` varchar(64...
mysql的分区和索引详解_MySQL详解：索引的介绍和原理分析
weixin_35152751的博客
02-28 819
索引的定义MySQL官方对索引的定义为：索引(Index)是协助MySQL高效获取数据的数据结构。本质上，索引的目的是为了提高查询效率，通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。可以类比银行的保险柜，比如你要找归属你的保险柜子。如果没有索引，你需要拿着钥匙，一个个的保险柜的试过去才能找...
MySQL 学习笔记
甲三的博客
05-07 830
MySQL数据库介绍及安装 官方定义MySQL DBA工作内容 运维DBA 初级：各版本、各平台安装搭建、升级 中级：体系结构原理、基础管理（启动关闭、初始化配置文件管理、多实例管理、用户权限管理、基本SQL（增删改查））、 日志管理、备份与恢复、主从复制（构建、状态监控） 高级：高可用（MGR、InnoDB Cluster）、高性能（优化） 开发DBA 懂至少一门开发语言 ：JAVA、Python 基本SQL语句深入学习（增删改查）、数据库结构设计（建模） 高级SQL：存储过程、函数、触发器、视图、事件
MySQL进阶的增删改查操作,包含各类查询的详细解释和操作以及表的设计讲解
JavaShark的博客
07-21 681
对于大多数企业级应用系统而言，数据库是整个应用系统的基石，因此数据库的表设计（也被称为schema设计）也就成为整个系统设计的重中之重。表设计的不好或者不合理，不仅会影响系统性能，而且会增加开发和集成的复杂性，甚至埋下隐患，最终会导致一系列的问题，例如数据不一致性问题等。概括而言，需要根据业务需求和系统功能，采用如下步骤设计表。应用系统往往涉及很多表,这些表的涉及存在先后顺序,而表之间也具有各种关联和依赖关系.因此,表的涉及并不是一蹴而就的，步骤2、3和4在大多数时候也不是清晰可分的，甚至整个设计也是一个循
mysql分区键约束,mysql 创建分区表注意事项,每一个唯一约束必须包含在Mysql分区表的分区键（也包括主键约束）。...
weixin_29443201的博客
03-23 377
CREATE TABLE tnp (id INT NOT NULL AUTO_INCREMENT,ref BIGINT NOT NULL,name INT,PRIMARY KEY pk (id),UNIQUE KEY uk (name));mysql> desc tnp-> ;+-------+--------------+------+-----+---------+--------...
mysql分区列索引_MySQL分区字段列有必要再单独建索引吗？
weixin_30673943的博客
02-02 324
软件安装：装机软件必备包SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。大家都知道对于分区字段必须是主键的一部分，那么建了复合...
运维工程师常用的Mysql语句——3.创建Mysql分区表以及分区索引
WUXIAOKAI的专栏
03-23 880
创建分区表 DROP TABLE IF EXISTS `test`； CREATE TABLE test ( id int(10) unsigned NOT NULL AUTO_INCREMENT, time datetime NOT NULL COMMENT '业务时间', PRIMARY KEY ( id , time ) ) ENGINE=InnoDB AUTO_...
MySQL分区表使用普通列作为分区键：创建复合唯一索引、主键复合索引来实现普通列作为分区键
学亮编程手记
08-04 224
MySQL 5.7分区表使用普通列作为分区键时需要注意当前表中的主键、唯一键情况，如果两者都存在，那么就需要创建复合类型的主键和唯一键。创建复合唯一索引、主键复合索引——
mysql primary key PARTITION 分区
任香980101
09-02 270
尝试把数据库一个表分区   ALTER TABLE user PARTITION BY RANGE(TO_DAYS(`date`)) ( PARTITION p1004 VALUES LESS THAN (TO_DAYS('2010-5-1')), PARTITION p1005 VALUES LESS THAN (TO_DAYS('2010-6-1')), PARTITION p...
mysql分区表之三：MySQL分区建索引[转]
weixin_33804582的博客
03-08 1120
介绍 mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表，从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在".ibd"文件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和u...
Mysql --分区表(7)Key分区
FaN()
08-09 6059
Key分区按照Key进行分区非常类似于按照Hash进行分区，只不过Hash分区允许使用用户自定义的表达式,而Key分区不允许使用用户自定义的表达式，需要使用MySQL服务器提供的HASH函数;同时Hash分区只支持整数分区，而Key分区支持使用BLOB或Text类型外其他类型的列作为分区键 我们同样可以使用Partition by key(expr)子句来创建一个key分区表，expr是零个或
浅析MySQL索引
吃果冻不吐果冻皮
03-21 932
简述MySQL索引 索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。MySQL里同一个数据表里的索引总数限制为16个。 以汉语字典的目录页（索引）举例，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 创建索引时，你需要确保该
为什么mysql分区列必须是主键中的一个
weixin_35756373的博客
02-02 1041
MySQL 分区列必须是主键中的一个，因为主键是唯一标识一条记录的关键字，如果分区列不是主键中的一个，那么可能会出现重复的分区，导致数据不一致。同时，将主键作为分区列还能够提高查询效率，因为分区能够快速定位到数据所在的分区。 ...
MySQL数据完整性约束：主键、唯一、非空与外键详解
主键约束是同时具备唯一性和非空性的约束，它是表中记录的唯一标识。创建表时，使用`PRIMARY KEY`来指定主键： ```sql CREATE TABLE stu ( id INT PRIMARY KEY, -- 给id添加主键约束 name VARCHAR(20) ); ```...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值