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),所有分区都会有数据,并且非常均匀;