前言

上篇文章提到，我切 partition 的条件用的是日期，而且是每日一个 partition，依照这样算，一年会有365个 partition ，假设10年就有 3650 个。那么会不会超过上限? 还是会不会超过某个数量后查询的效能就大幅降低?这是本篇文章想测试的。

partition 数量的效能测试

测试之前先说明一下，在 MySQL 5.6 之后一张表最多可以切 8192 个 partition，也就是说就算每天切一个，切20年都不是问题，所以想来测测看效能如何。

实验说明:
分别建立 partition 数量为 2 , 512, 1024, 8000 的表，每张表里面都放一样数量的资料

★ 测试资料

这里以建立 2 个 partition 的为例，其他512, 1024, 8000 大家依此类推然后在4张表都塞入50万笔的资料(可以利用一开始塞入的两笔资料不断 select into)

CREATE TABLE `dictionary_2` (  `trans_id` int NOT NULL AUTO_INCREMENT,  `lang_id` int NOT NULL,  `lang` char(5) NOT NULL,  `trans_text` text,  PRIMARY KEY (`trans_id`,`lang_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY LIST (`lang_id`)( PARTITION lang_1 VALUES IN (1) ENGINE = InnoDB, PARTITION lang_2 VALUES IN (2) ENGINE = InnoDB);

塞完资料后四张表各 partition 的佔用资料数如下图1~4

四个 TABLE 虽然分别切了2,512,1024,8000 个 partition ，但我资料都塞在前面两个 partition

图1

图2

图3

图4

★ 开始测试这4张TABLE 有什么差异

测试 1: 有用到正确的栏位，所以会使用到对的 partition

select * from dictionary_2    where lang_id = 2;        select * from dictionary_512  where lang_id = 2;       select * from dictionary_1024 where lang_id = 2;select * from dictionary_8000 where lang_id = 2;

这4句大约都跑了0.55秒以上的4句语法 where 条件都有用到切 partition 的栏位(lang_id)，所以可以从 Explain 里面看到的确有用到正确的 partition 如下图5

图5

测试 2: 没有用到正确的栏位，所以会全表 scan

select * from dictionary_2 where lang = 'zh-tw';             select * from dictionary_512 where lang = 'zh-tw';           select * from dictionary_1024 where lang = 'zh-tw';        select * from dictionary_8000 where lang = 'zh-tw';           

上面这4句的秒数分别为 0.553, 0.565, 0.571, 2.533前3句其实没什么差，可是可以发现 partition 切8000个的明显慢非常多拿第3句来看看 Explain，因为没有用到切 partition 的栏位(lang_id)，会全部的 partition 都扫一遍如下图6

图6

小结

相同处: 资料量一样，且资料都放在前面两个 partition不同处: partition 数量我这里用了比较极端的例子 8000个 partition 发现效能差很多，或许2000个就会慢很多，留给大家去测试。虽然4张表的资料量一样，但是 partition 不同，一样的全表 scan，就算其他的 partition 没资料，一样会浪费时间，但是原因是下 select 的时后没有用到正确的栏位。为了避免可能有人下语法的时候造成不必要的问题，没事不要预先建立一堆 partition，有人会为了方便都先建好语法要注意有用到正确的栏位去搜寻适度的删除旧的 partition 或是合併是必要的。太多的分区甚至可能造成主从複製的问题，这部分我还没深入研究，我有列在参考网站的第2个，有兴趣的可以看看

资料库知识相当广泛，文中若有不正确的地方，也烦请各位大神不吝指教，谢谢

参考网站

MySQL Partition and InnoDB
https://medium.com/corneltek/mysql-partition-and-innodb-c2b5982e3c04
意想不到的MySQL复制延迟原因
https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653930276&idx=1&sn=40d37e6b9e7b4cf53b321da745e6dbbd&chksm=bd3b594e8a4cd0585f7fbe7b328ae1afb510f9acde6e96642b7b021f2ee66bddc131e1c9c159&scene=21#wechat_redirect