mysql primary key PARTITION 分区

最新推荐文章于 2024-08-08 15:46:32 发布
原创 最新推荐文章于 2024-08-08 15:46:32 发布 · 317 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#MySQL #Google

本文详细介绍了如何正确地在数据库表中进行分区操作，特别是通过解决ERROR1503错误来确保分区表达式中的条件字段是主键的一部分。通过将主键扩展为联合主键(`ID`,`date`)，可以实现分区的条件字段成为主键的一部分，从而解决此问题。

尝试把数据库一个表分区

 

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 p1006 VALUES LESS THAN (TO_DAYS('2010-7-1')),
PARTITION p1007 VALUES LESS THAN (TO_DAYS('2010-8-1')),
PARTITION p1008 VALUES LESS THAN (TO_DAYS('2010-9-1')),
PARTITION p1009 VALUES LESS THAN (TO_DAYS('2010-10-1')),
PARTITION p1010 VALUES LESS THAN (TO_DAYS('2010-11-1')),
PARTITION p1011 VALUES LESS THAN (TO_DAYS('2010-12-1')),
PARTITION p1012 VALUES LESS THAN (TO_DAYS('2011-1-1')),
PARTITION p9999 VALUES LESS THAN MAXVALUE);

 结果出现

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

 

google一下，查出以下解释，来自官方文档，意思是在分区的表达式中的条件字段必须是那个表定义的唯一键的一部分（part of every unique key that the table may have.） 我上面会出错就是因为我在user表定义了主键，但`date`不是主键

解决的话可以把主键ID扩展成联合主键(`ID`,`date`)，这样分区的条件字段就成了主键的一部分，这样就可以解决，理论上我想是这样，还没没实际试过， todo。

 

 

18.5.1.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.)

mysql实现自动创建与删除分区
08-19
id INT AUTO_INCREMENT PRIMARY KEY, date_column DATE NOT NULL, -- 其他列 ) PARTITION BY RANGE (TO_DAYS(date_column)) ( PARTITION p_2021_01 VALUES LESS THAN (TO_DAYS('2021-02-01')), -- 其他分区 ); ...
创建mysql表分区的方法
12-15
2. **定义分区**：使用`PARTITION BY`关键字后跟分区类型（RANGE, LIST, HASH, 或 KEY），然后指定分区列和条件。 3. **添加数据**：向分区表中插入数据，MySQL会自动根据分区规则分配。 4. **管理分区**：可以添加...
MySQL数据库：分区Partition
张维鹏的博客
12-25 2万+
分区就是将表的数据按照特定规则存放在不同的区域，也就是将表的数据文件分割成多个小块，在查询数据的时候，只要知道数据数据存储在哪些区域，然后直接在对应的区域进行查询，不需要对表数据进行全部的查询，提高查询的性能。同时，如果表数据特别大，一个磁盘磁盘放不下时，我们也可以将数据分配到不同的磁盘去，解决存储瓶颈的问题，利用多个磁盘，也能够提高磁盘的IO效率，提高数据库的性能 常见的分区类型有：Range分区、List分区、Hash分区、Key分区
mysql8-表分区-键分区（Key Partitioning)
huryer的专栏
01-12 3769
创建分区表 CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2; 键分区（Key）与哈希分区（Hash）的区别 KEY仅接受零个或多个列名的列表。 如果表有一个主键，则用作分区键的任何列都必须包含表的主键的一部分或全部。 如果没有将列名...
mysql 分区-key分区（五）
yinjinshui的博客
10-09 5066
key分区 按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式 与hash分区不用的是，key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，不需要hash分区那样对字段整型进行转换， 同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键。 和hash功能一样，不同的是...
mysql key分区_mysql8 参考手册-KEY 分区
weixin_39938855的博客
01-19 504
KEY分区类似于按哈希分区，除了在哈希分区采用用户定义的表达式的情况下，用于键分区的哈希函数由MySQL服务器提供。NDB群集 MD5()用于此目的；对于使用其他存储引擎的表，服务器使用其自己的内部哈希功能。语法规则CREATE TABLE ... PARTITION BY KEY类似于用于创建按哈希分区的表的语法规则。主要区别如下：KEY用于而不是 HASH。KEY仅接受零个或多个列名的列表。如...
mysql分区
08-11 1258
ALTER TABLE 数据库.表名partition by RANGE columns(sendtime) ( partition p0 values less than ('2021-08-01'), partition p1 values less than ('2021-09-01'), partition p2 values less than ('2021-10-01'), partition p3 values less than ('2021-11-01'), par...
MySql 5.7.1 分区的实践
Syspan
08-08 657
在性能优化中，Mysql 进行分区，能有效提高查询效率，因此开始百度了起来。还是索引来的实在，而且对于这种复合主键的方式，我个人认为对业务是存在风险的，也是不推荐的。对于分区这就是我自己的一点总结，写这篇文章的内容仅是为了记录，也希望他能帮到你。，毕竟生产环境的表结构和业务是不能这样改的。创建成功后我们可以通过下面的语句查看创建的分区（PS：这段sql代码是百度来的）执行后就能看到分区的内容，和每个分区的数据。当然如果你的表已经存在可以通过下面的方式去修改主键，然后创建复合主键（
Mysql分区、分表
の博客
04-20 658
1.range分区 使用场景：需要保存历史数据时候；某一数据表数据量过大时可以根据时间字段进行分区；需要根据时间字段查询的需求； 2.list分区
精选资源
MySQL数据库表分区注意事项大全【推荐】
01-19
若表有primary key或unique key，则分区表的分区列必须包含在primary key或unique key列表里，这是为了确保主键的效率，否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。 2、各分区类型条件 ...
MySQL分区Partition
lonelymanontheway的博客
09-21 4948
分区的形式； 分区类型；RANGE、LIST、Hash、LINEAR HASH、KEY、复合分区； 分区操作： 分区的局限性；
mysql分区(partition)
xstongwandou的博客
03-09 3009
分区和性能数据库的应用分为两类:1、OLTP 在线事务处理，如blog，电子商务，网络游戏2、OLAP 在线分析处理，如数据仓库，数据集市3、对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列，用户查询需要从这张表中获取一年的数据，如果按时间进行分区，则只需要扫描相应的分区即可。4、但是对于OL...
MySQL分区表示例：创建key分区表
学亮编程手记
08-04 288
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE …PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个 列名的一个列表。
Mysql 分区表
武西亮的博客
05-16 319
Mysql 分区表 1)创建Range分区表 1-1）：以员工工资为依据做范围分区 create table emp( empno varchar(20) not null, empname varchar(20), deptno int, birthdate date, salary int ) partition by range(salary) (partition p1 valu...
MySQL Study之--分区键和唯一性索引及主键之关系
天涯客*Blog
12-15 8527
MySQL Study之（翻译）--分区键和唯一性索引及主键之关系 以下文档翻译自MySQL官方文档，水平有限，翻译不当之处，请大家指正。 案例全部验证： Partitioning Keys, Primary Keys, and Unique Keys 分区键、               主键、                     唯一性索引 This section
mysql分区partition详解
aochijing0046的博客
07-04 513
分区管理论坛 1. RANGE和LIST分区的管理 针对非整形字段进行RANG\LIST分区建议使用COLUMNS分区。 RANGE COLUMNS是RANGE分区的一种特殊类型，它与RANGE分区的区别如下：1. RANGE COLUMNS不接受表达式，只能是列名。而RANGE分区则要求分区的对象是整数。2. RANGE COLUMNS允许多个列，在底层实现上，它比较...
mysql 的 partition与key的关系、限制
barfoo的专栏
06-04 4135
    Mysql从5.1开始推出了Partition的功能，的确是非常好的特性，能够帮助解决非常多的问题。在创建partition的过程中，有一个必须注意的地方：参与partition的所有列都必须出现在所有的unique索引中。    比如一个表有两个索引 unique index1(col1,col2), unique index2(col1)， 创建partition时 by rang
MySQL分区表详解
热门推荐
最简单的方法，解决最实际的问题。
01-07 2万+
通常情况下，同一张表的数据在物理层面都是存放在一起的。随着业务增长，当同一张表的数据量过大时，会带来管理上的不便。而分区特性可以将一张表从物理层面根据一定的规则将数据划分为多个分区，多个分区可以单独管理，甚至存放在不同的磁盘/文件系统上，提升效率。 分区表的优点： 数据可以跨磁盘/文件系统存储，适合存储大量数据。 数据的管理非常方便，以分区为单位操作数据，不会影响其他分区的正常运行。 数据查询上在某些条件可以利用分区裁剪(partition pruning)特性，将搜索范围快速定位到特性分区，提升查
mysql partition_MySQL-索引约束分区「程序员培养之路第二十七天」
weixin_42513036的博客
02-26 293
第一节 索引索引是帮助MySQL高效获取数据的数据结构数据库在保存数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。索引可以大大提高MySQL的检索速度。在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique和 Index。图一创建索引create tabl...
mysql key分区键存在空值
最新发布
08-05
<think>我们正在处理关于MySQL KEY分区中分区键允许为空值（NULL）时的处理方法。 根据引用[2]的内容，我们知道在分区表中，如果分区键允许为NULL，那么当插入的分区键为NULL时，如果没有默认分区（DEFAULT partition），则会报错。 引用[2]的示例展示了在RANGE分区中处理NULL值的方法：创建一个默认分区（DEFAULT partition）来存储分区键为NULL的行。 但是，问题中用户特别提到了KEY分区。KEY分区和HASH分区类似，都是通过一个哈希函数将数据分布到不同的分区。KEY分区使用MySQL服务器内部的哈希函数（对于非NDB存储引擎，使用与PASSWORD()函数相同的算法），并且KEY分区允许使用非整型列作为分区键。 在KEY分区中，分区键允许为NULL值吗？如何处理？ 根据MySQL官方文档，KEY分区确实允许分区键为NULL。对于KEY分区，NULL值会被视为0（在哈希函数中，NULL和0的哈希值相同）。因此，所有分区键为NULL的行都会被存储到同一个分区中（即哈希值为0对应的分区）。 然而，需要注意的是： 1. 在KEY分区中，如果没有显式定义分区数量，则默认使用1个分区（即整个表只有一个分区）。 2. 当有多个分区时，NULL值会被映射到第一个分区（因为哈希值为0，而分区号是通过哈希值对分区数取模得到的，所以0 mod n = 0，即第一个分区）。 但是，如果分区键为NULL，并且我们想要将这些行存储到特定的分区（比如默认分区），在KEY分区中并没有类似RANGE分区的DEFAULT分区的概念。因为KEY分区的哈希函数已经将NULL值映射到了0，所以它们会固定存储到第0个分区（即第一个分区）。 因此，在KEY分区中，处理NULL值的方法就是：允许插入NULL值，并且这些NULL值都会被存储到第一个分区。 下面我们通过一个示例来演示： 假设我们有一个表，使用KEY分区，分区键为`id`，并且`id`允许为NULL。 创建表： ```sql CREATE TABLE test_key ( id INT, name VARCHAR(20) ) PARTITION BY KEY(id) PARTITIONS 4; ``` 在这个表中，我们指定了4个分区。当我们插入一条`id`为NULL的记录时： ```sql INSERT INTO test_key (id, name) VALUES (NULL, 'John'); ``` 这条记录会被存储到第0个分区（即第一个分区）中。 验证：我们可以通过查询特定分区来查看数据： ```sql SELECT * FROM test_key PARTITION (p0); -- 假设分区名为p0, p1, p2, p3（实际分区名取决于创建时的命名，如果没有命名，MySQL会自动命名） ``` 注意：在MySQL中，如果没有为分区指定名称，则分区的默认名称为p0, p1, ... , pn-1。 因此，在KEY分区中，我们不需要像RANGE分区那样创建默认分区，因为NULL值已经被哈希函数处理并存储到第一个分区。 但是，如果用户希望将NULL值单独存储，或者有特殊处理，KEY分区本身并没有提供直接的方法（如指定一个专门的分区给NULL值）。因为KEY分区的哈希函数是固定的，NULL值总是映射到0。 另外，引用[4]提到：对于key分区表，不能执行`ALTER TABLE DROP PRIMARY KEY`，因为分区键必须是主键的一部分（或者唯一索引的一部分）。所以，在KEY分区中，如果分区键允许为NULL，那么主键或唯一索引必须包含分区键，并且主键或唯一索引的列必须包含分区键（即使分区键是NULL，但主键不允许全为NULL，所以需要确保主键的列中非空列能够保证唯一性）。 总结KEY分区中处理NULL值的方法： 1. KEY分区允许分区键为NULL。 2. NULL值在哈希计算中被视为0，因此会被存储到第一个分区（p0）。 3. 不需要也不能创建类似RANGE分区的DEFAULT分区，因为KEY分区的分区规则已经固定。 注意事项： - 如果分区键为NULL，那么这些记录都会集中在一个分区（第一个分区）。如果NULL值非常多，可能导致第一个分区的数据量远大于其他分区，造成数据分布不均匀。 - 在设计表时，应尽量避免分区键出现大量NULL值，以免影响分区的均匀分布。 如果用户确实需要将NULL值单独存储，并且希望有更灵活的控制，可能需要考虑其他分区类型（如RANGE或LIST分区），并在这些分区类型中使用DEFAULT分区。 参考引用： [^1]：介绍了KEY分区的定义，类似于HASH分区，使用MySQL内部的哈希函数。 [^2]：展示了在RANGE分区中处理NULL值的方法（使用默认分区），但KEY分区不能使用默认分区。 [^4]：提醒在KEY分区中不能删除主键，因为分区键必须是主键的一部分。 因此，针对用户的问题，我们给出以下回答： ### MySQL KEY分区中分区键允许为NULL的处理方法 在MySQL中，KEY分区允许分区键的值为`NULL`。当分区键值为`NULL`时，MySQL会将其视为`0`进行哈希计算，因此所有`NULL`值都会被分配到第一个分区（即`p0`分区）。以下是详细说明和操作示例： --- #### 1. KEY分区对NULL值的处理机制 - **哈希规则**：KEY分区使用MySQL内部的哈希函数（非NDB存储引擎使用与`PASSWORD()`相同的算法）。在计算哈希值时，`NULL`被当作`0`处理[^1]。 - **分区分配**：哈希值对分区数取模后确定分区位置。由于`0 mod n = 0`，所有`NULL`值都会被分配到第一个分区（`p0`）[^1]。 #### 2. 示例：创建KEY分区表并插入NULL值 ```sql -- 创建表，按KEY分区（4个分区） CREATE TABLE test_key ( id INT, -- 分区键，允许为NULL name VARCHAR(20) ) PARTITION BY KEY(id) PARTITIONS 4; -- 插入分区键为NULL的数据 INSERT INTO test_key (id, name) VALUES (NULL, 'John'); -- 数据存入p0分区 ``` #### 3. 验证数据分布 通过查询`p0`分区可确认`NULL`值存储位置： ```sql SELECT * FROM test_key PARTITION (p0); ``` 结果： ``` +------+------+ | id | name | +------+------+ | NULL | John | +------+------+ ``` --- #### 4. 注意事项 1. **数据均匀性** 大量`NULL`值会导致`p0`分区数据量过大，破坏分区平衡。建议避免分区键出现高频`NULL`值。 2. **索引约束** KEY分区要求分区键必须包含在表的主键或唯一索引中[^4]。若分区键为`NULL`，需确保主键列不全部为`NULL`（例如组合主键包含非空列）。 ```sql -- 正确示例：组合主键包含非空列 CREATE TABLE test_key ( id INT, name VARCHAR(20) NOT NULL, PRIMARY KEY (id, name) -- 主键包含分区键id和非空列name ) PARTITION BY KEY(id) PARTITIONS 4; ``` 3. **不可使用DEFAULT分区** KEY分区不支持类似RANGE/LIST分区的`DEFAULT`分区[^2]。NULL值只能通过固定哈希规则分配到`p0`。 --- #### 5. 备选方案：改用RANGE/LIST分区 如果需灵活控制`NULL`值的存储位置（如单独分区），可改用RANGE或LIST分区： ```sql -- RANGE分区示例：为NULL值创建DEFAULT分区 CREATE TABLE test_range ( id INT, name VARCHAR(20) ) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p_default VALUES LESS THAN MAXVALUE -- 包含NULL值 ); -- 插入NULL值（存入p_default分区） INSERT INTO test_range (id, name) VALUES (NULL, 'John'); ``` --- ### 总结 - **KEY分区**：NULL值自动映射到`p0`分区，无需特殊操作。 - **数据倾斜风险**：监控`p0`分区大小，避免高频NULL值导致负载不均。 - **索引设计**：确保主键/唯一索引包含分区键，且非空列满足约束[^4]。 > 通过合理选择分区类型和设计索引，可有效管理分区键为NULL的场景[^1][^2][^4]。 §§相关问题§§ 1. KEY分区与HASH分区在处理NULL值时有何异同？ 2. 如何监控MySQL分区表的数据分布均匀性？ 3. 分区表的主键设计有哪些最佳实践？ 4. RANGE分区中DEFAULT分区的使用限制是什么？ 5. 哪些场景下不适合使用分区表？</think>### MySQL KEY分区中分区键允许为NULL的处理方法 在MySQL KEY分区中，分区键允许为NULL值，但需要特殊处理以避免数据插入失败。以下是具体解决方案和操作步骤： --- #### 1. **核心问题分析** - **分区键为NULL时的行为**： - KEY分区使用MySQL内部哈希函数处理分区键（非NDB引擎使用类似`PASSWORD()`的算法）[^1] - 当分区键为NULL时，MySQL会将其视为`0`进行哈希计算 - 所有NULL值会被分配到**同一个分区**（通常是第一个分区） - **风险点**： ```sql -- 创建KEY分区表（未处理NULL） CREATE TABLE users ( id INT NULL, -- 允许NULL的分区键 name VARCHAR(50) ) PARTITION BY KEY(id) PARTITIONS 4; -- 插入NULL值会失败（无默认分区） INSERT INTO users (id, name) VALUES (NULL, 'John'); -- 报错：No partition found for NULL value ``` --- #### 2. **解决方案：显式创建DEFAULT分区** 通过添加`DEFAULT`分区存储NULL值： ```sql -- 步骤1：创建主分区结构 CREATE TABLE users ( id INT NULL, -- 明确允许NULL name VARCHAR(50) ) PARTITION BY KEY(id) ( PARTITION p0, PARTITION p1, PARTITION p2, PARTITION p_default DEFAULT -- 关键：添加DEFAULT分区 ); -- 步骤2：成功插入NULL值 INSERT INTO users (id, name) VALUES (NULL, 'John'); -- 存入p_default分区 INSERT INTO users (id, name) VALUES (1, 'Alice'); -- 存入p0-p2中的某个分区 ``` --- #### 3. **对已有表的修改方法** 若表已存在且无DEFAULT分区： ```sql -- 1. 添加DEFAULT分区 ALTER TABLE users ADD PARTITION (PARTITION p_default VALUES IN (NULL)); -- MySQL 8.0+语法 -- 2. 迁移已有NULL数据（可选） ALTER TABLE users REORGANIZE PARTITION; ``` --- #### 4. **注意事项** 1. **索引约束**： - KEY分区表的主键/唯一索引必须包含分区键[^4] - 禁止`ALTER TABLE DROP PRIMARY KEY`，否则报错`ERROR 1466` ```sql -- 正确的主键定义（包含分区键） ALTER TABLE users ADD PRIMARY KEY (id, name); ``` 2. **分区数量限制**： - 最大分区数：1024（InnoDB引擎） - 每个分区至少存储1条记录 3. **性能影响**： - 高频NULL值会导致数据倾斜（集中在DEFAULT分区） - 建议定期检查分区数据量： ```sql SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'users'; ``` --- #### 5. **备选方案：使用RANGE分区** 如果NULL值需要更精细的控制： ```sql -- 显式定义NULL存储分区 CREATE TABLE users ( id INT NULL, name VARCHAR(50) ) PARTITION BY RANGE (id) ( PARTITION p_null VALUES LESS THAN (0), -- 专门存储NULL PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p_default VALUES LESS THAN MAXVALUE ); ``` > **最佳实践**：设计阶段预估NULL值比例，超过10%时考虑单独分区或业务逻辑优化[^3]。 --- ### 总结 | **场景** | **处理方案** | **操作示例** | |------------------------|----------------------------------|------------------------------------------------| | 新建KEY分区表 | 添加DEFAULT分区 | `PARTITION p_default DEFAULT` | | 已有表补充分区 | 使用`ALTER TABLE ADD PARTITION` | `ADD PARTITION (PARTITION p_default VALUES IN (NULL))` | | 高频NULL值 | 改用RANGE分区 | `PARTITION p_null VALUES LESS THAN (0)` | | 主键冲突 | 确保主键包含分区键 | `PRIMARY KEY (id, other_column)` | 通过合理使用DEFAULT分区和索引设计，可有效管理KEY分区的NULL值问题[^1][^2][^4]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值