mysql~关于mysql分区表的测试

天蓝之星

在MySQL 8.0中，可以通过创建自定义哈希函数来处理VARCHAR类型的字段，以便用作分区键。下面是一个简单的示例，演示如何在MySQL8.0中创建自定义哈希函数来处理VARCHAR类型的字段

• 分区后的表效果
  
  
  

方法一，可能无效

步骤：

• 创建自定义哈希函数：
  

1. DELIMITER //
3. CREATE FUNCTION custom_hash(str VARCHAR(255)) RETURNS INT
4. DETERMINISTIC
5. BEGIN
6.     DECLARE hash_val INT;
7.     SET hash_val = 0;
8.    
9.     -- 计算字符串的哈希值
10.     SET hash_val = CRC32(user_id) % 3;
11.     RETURN hash_val;
12. END//
14. DELIMITER ;

复制代码

在上面的示例中，我们创建了一个名为custom_hash的自定义哈希函数，接受一个VARCHAR类型的参数，并返回一个整数类型的哈希值。这里使用了MD5算法计算字符串的哈希值，并将其转换为有符号整数。

• 使用自定义哈希函数进行分区：
  

1. CREATE TABLE user_attribute (
2.     user_id VARCHAR(50),
3.     attribute_name VARCHAR(50),
4.     attribute_value VARCHAR(255),
5.     PRIMARY KEY (user_id, attribute_name)
6. ) PARTITION BY HASH(custom_hash(user_id)) PARTITIONS 4;

复制代码

在上面的示例中，我们创建了一个名为user_attribute的表，并使用自定义哈希函数custom_hash对user_id字段进行哈希计算，然后将结果用作分区键进行分区。
通过以上步骤，你可以自定义一个哈希函数来处理VARCHAR类型的字段，并将其应用于分区表的设计中。请注意，在实际应用中，需要根据具体需求和数据特征来设计和调整哈希函数的逻辑。希朼这个示例能够帮助你解决问题。如果有任何其他问题，请随时告诉我。
方法二，虚拟字段

1. ALTER TABLE user_attribute_pattern ADD COLUMN user_id_hash2    generated always  varchar(512) AS (CRC32(user_id) % 3)  VIRTUAL;

复制代码

对已经有表分区

1. ALTER TABLE user_attribute_pattern
2. PARTITION BY hash(user_id_hash) PARTITIONS 4;

复制代码

查看分区信息

1. SELECT table_name, partition_ordinal_position, partition_method, partition_expression
2. FROM information_schema.partitions
3. WHERE table_name = 'users';

复制代码

方法一和方法二产生的问题和错误

• 需要hash分区时，分区字段必须是整型
  
• 分区字段必须是主键字段，多主键时，分区字符是其中一个主键
  
• 
  

1. # 虚拟字段的建立
2. drop table user_attribute_pattern;
3. CREATE TABLE `user_attribute_pattern` (
4.   `NAME` varchar(255) ,
5.   `VALUE` varchar(255),
6.   `USER_ID` varchar(36) ,
7.   `ID` varchar(36),
8.         `user_id_hash` int(11)  generated always  AS (CRC32(user_id) % 4) virtual,
9.   PRIMARY KEY (`ID`) USING BTREE,
10.   INDEX `IDX_USER_ATTRIBUTE2`(`USER_ID`) USING BTREE,
11.   INDEX `IDX_NAME2`(`NAME`) USING BTREE,
12.   INDEX `IDX_VALUE2`(`VALUE`) USING BTREE
13. )

复制代码

最后相互矛盾，没有实现

• 因为我的主键是varchar的，而hash分区表的键需要是int的
  
• 所以我要把它添加一个虚拟字段取主键的hash值
  
• 然后经过这些步骤之后，又需要把这个虚拟key添加为主键，当mysql又提示了，虚拟字段不能设为主键
  
• 
  

最后可能需要把VIRTUAL这个虚拟的键变成STORE存储的键才行了
最后直接用KEY的方式，它是支持字符串的

事实上，它是mysql内部对这个varchar进行了hash的计算，咱们上面的测试多次一举了!

1. ALTER TABLE user_attribute_copy1
2. PARTITION BY KEY(ID) PARTITIONS 5;

复制代码

根据你提供的代码，针对表 user_attribute_copy1 进行了分区设置。在这段代码中，使用的是 KEY(ID) 来定义分区规则，其中 ID 是主键。根据这个规则，表 user_attribute_copy1 将会以主键 ID 作为分区字段进行分区，共分为5个分区。
在MySQL中，使用 KEY 关键字指定主键作为分区键，表示根据主键的值来进行分区。具体地说，MySQL会根据主键的哈希值或者范围值来将数据行分配到不同的分区中。在这种情况下，如果主键 ID 是 varchar 类型，MySQL会根据该字段的值进行哈希或范围分区，将数据均匀地分布到5个分区中。
总的来说，这个分区规则会根据表的主键 ID 的值来进行分区，将数据分散存储到5个不同的分区中，有助于提高查询效率和管理大量数据。如果需要进一步了解分区规则的具体实现和效果，可以查看表的分区信息或执行相应的查询来验证数据是否按照预期分布到各个分区中。

来源:https://www.cnblogs.com/lori/p/18071189
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！