iT邦幫忙

4

MySQL 系列文 - Partition Table(3) - partition 數量與效能的影響

前言

上篇文章提到,我切 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

https://ithelp.ithome.com.tw/upload/images/20200428/20124671IqzQkuBgbW.png
圖1

https://ithelp.ithome.com.tw/upload/images/20200428/201246711Vb2bHVj13.png
圖2

https://ithelp.ithome.com.tw/upload/images/20200428/2012467181IwdpTeeu.png
圖3

https://ithelp.ithome.com.tw/upload/images/20200428/20124671wsHgLFath8.png
圖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
    https://ithelp.ithome.com.tw/upload/images/20200429/20124671xKhjypJiSe.png
    圖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
    https://ithelp.ithome.com.tw/upload/images/20200429/20124671ClMDUGIc6O.png
    圖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


1 則留言

2
一級屠豬士
iT邦大師 1 級 ‧ 2020-04-29 22:44:30

預先建立一堆 partition,有人會為了方便都先建好. <= 這個在實際運用上,還蠻常見的.
Partition 蠻常見的用在 日期範圍, 例如交易資料,事先定義好 年月分割.

Stock iT邦新手 5 級 ‧ 2020-04-30 12:37:54 檢舉

大大說的沒錯,很多可能會先建立個一年份。

我要留言

立即登入留言