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

👋 相关阅读

• Python 连接 Oracle 数据库时遇到的坑及解决办法
• mysql binlog日志自动清理及手动删除

一、背景

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：分区数量

2. 检查分区是否创建成功

EXPLAIN PARTITIONS SELECT * FROM `cars`

若成功，结果如下：

3. 踩坑

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

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

附：100以内质数表

三、其他

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

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

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

2. 查看已分区表的详情

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

×