mysql 已有大数据量表进行分区踩坑

#编程技术 2019-10-29 16:46:00 | 全文 482 字,阅读约需 1 分钟 | 加载中... 次浏览

👋 相关阅读


一、背景

mysql 表中已有 4 亿数据,为提高查询效率,需创建分区,一开始计划是创建 HASH 分区,结果报错:

ERROR 1659 (HY000): Field '**' is of a not allowed type for this type of partitioning

在这里插入图片描述

查询得知报错原因,HASH 分区只支持数字分区,而我要分区的字段是 varchar 类型,故改用 KEY 分区

二、解决

  1. KEY 分区语句
alter table TABLENAME PARTITION BY key(COLUMN) PARTITIONS NUM;

TABLENAME :表名 COLUMN:列名 NUM:分区数量

  1. 检查分区是否创建成功
EXPLAIN PARTITIONS SELECT * FROM `cars`

若成功,结果如下: 在这里插入图片描述

  1. 踩坑

拿小表测试 100 个分区发现,分区不均匀,一半分区有数据,一半没有数据,网上查询得知:key 分区,只有指定分区数目为质数,才能保证每个分区都有数据。

小表测试分区 97 个,分布均匀。

附:100以内质数表 100以内质数表

三、其他

  1. KEY 分区和 HASH 分区的区别

KEY 分区和 HASH 分区相似,但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区,而 HASH 分区只支持数字分区,KEY 分区不允许使用用户自定义的表达式进行分区,KEY 分区使用系统提供的 HASH 函数进行分区。

当表中存在主键或者唯一键时,如果创建 key 分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。

  1. 查看已分区表的详情
SELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'TABLE_NAME';

参考文章: https://blog.csdn.net/echo_hello_world/article/details/80693224 https://bbs.csdn.net/topics/390857704 https://blog.csdn.net/jhq0113/article/details/44593511

Edit | Last updated on 2024-02-23 10:33:45




×