MySQL的KEY分区存在BUG，请慎用

最新推荐文章于 2024-07-24 23:35:41 发布

enjoy编程

最新推荐文章于 2024-07-24 23:35:41 发布

阅读量962

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 bug

本文链接：https://blog.csdn.net/penriver/article/details/115551259

版权

数据库专栏收录该内容

28 篇文章

订阅专栏

1. 问题现象

上个星期排查项目【此项目是一个外包项目，但数据查询慢】问题时，发现一个很奇怪的现象：

项目使用mysql 5.7，有几个频繁操作的表，使用mysql 的key 分区，分区数是10、100，建表语句类似如下：

CREATE TABLE `tbl_key_partition` (
  `id` varchar(64) NOT NULL COMMENT 'id',
  `updateTime` datetime DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY ()
PARTITIONS 100 ;

奇数编号分区的ibd文件大小都是固定的，96k，这是创建分区表时初始化大小，实际并没有任何数据，可以通过

select partition_name, partition_expression, table_rows from information_schema.partitions where table_schema = schema() and table_name='tbl_key_partition'

或

select count(*) from tbl_key_partition partition(p91)

发现数据都分布在偶数编号的分区，奇数编号分区数据量为0

2. 问题重现

在mysql中创建tbl_key_partition表，然后使用load_data存储过程插入10万数据，存储过程的源码如下：

--定义存储过程
drop PROCEDURE if EXISTS load_data;
delimiter $$
CREATE PROCEDURE load_data (in num int) 
BEGIN
	DECLARE v INT DEFAULT 0;
	WHILE v < num DO
		INSERT INTO tbl_key_partition VALUES (concat(substring(md5(rand()),1,10),v), date_add('2020-01-01 00:00:00', interval v second) );
		SET v = v + 1;
	END WHILE;
END$$ 
delimiter ;

--调用存储过程
call load_data(100000)

查看表中各分区的数据量分布，如下图，数据基本上都写到了偶数编号的分区上，奇数编号的分区只写了极少数的数据。另外奇数编号分区的ibd文件大小都是固定为96K，所以重现了问题。

3. 原因分析

3.1 MySQL key 分区原理

KEY分区的原理：通过MySQL内置hash算法对分片键计算hash值后再对分区数取模，详见：https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html

按键分区与按哈希分区类似，不同的是，哈希分区使用用户定义的表达式，用于键分区的哈希函数由MySQL服务器提供。NDB集群使用MD5()来实现此目的;

对于使用其他存储引擎的表，服务器使用自己的内部散列函数，该函数基于与PASSWORD()相同的算法。

3.2 原因

使用 mysql key 分区 bug 作为关键字进行搜索，发现有不少内容描述此问题。比如这个 MySQL之KEY分区引发的血案。这个就是mysql 5.7的bug, 但官网却没有任何说明，

所以建议不要使用key分区，建议使用hash或范围分区

其他文章的结论如下：

根据password函数，分析并测出，key分区，只能指定分区数目为质数，才能保证每个分区都有数据。我测了下，从11个分区，到17个分区。只有11,13,17 ，这3个分区的数据是基本平均分布的
如果设置40，64，128等偶数个分区数（PARTITIONS 64），会导致编号为奇数的分区（p1, p3, p5, p7, … p2n-1）完全插不进数据；
如果设置63，121（PARTITIONS 63）这种奇数但非质数个分区数，所有分区都会有数据，但是不均匀；
如果设置137，31这种质数个分区数（PARTITIONS 137），所有分区都会有数据，并且非常均匀；