前言

上篇文章提到，我切 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=utf8
PARTITION 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