簡單使用 Mysql Partition 優化查詢

mysql 資料表分區 mysql table partition 從架構上調整 mysql 的查詢效率。
mysql DB 的優化可以簡單也能複雜，除了調整設定值。也可以透過水平分割(Horizontal Partitioning)、垂直分割(Vertical Partitioning) 分庫或分表將資料分散儲存減少資料搜尋、group by 時的效能消耗。

拆開批次處理，理論上效率都會變好，本文就水平分割的 MySQL RANGE Partitioning 簡單說明一下~~

什麼是 Mysql Partition

假設有 20 萬筆使用者年齡資料(table schema)
APP 會向 DB 查詢資料

但通常~ 業務邏輯的查詢條件不會這麼單純。
App 端需要列出 10 ~30 歲的資料，這時候就要向 DB 發出區間查詢條件

透過 explain 可以觀察出查詢效率並不是很好…

當然~ 我們可以用更多的 index 解決 😂
但某些情況下不適合這麼做…
像是對像鍵值 (key) 過於分散或是不想讓 table index 過於肥大

另一種選擇~
在資料筆數過多情況下會將資料拆開儲存。(水平分割)
例如：
0~9 歲儲存 表0
10~19 歲儲存 表1
20~29 歲儲存 表2
30~39 歲儲存 表3
超過 40 歲儲存 表4

(當然實際的拆分方法依當下業務邏輯判斷~)

表內資料量變少了，處理、回應速度自然變快。
但這麼一來問題每次查詢都要依拆開數量，查詢多次嗎?

而且 APP 端還需要將多個 query / 查詢合併，這樣 APP 端要做的功不就變多了?
不是讓程式變的很蠢 🤯

.
.
.

因此~ 為解決上述問題
Mysql Partition 就適合在這時候被使用啦~~~ 🎉
簡單的讓資料操作變快(select, insert, update ,delete)~
Mysql Partition 會將 table 依指定條件拆為多張隱藏 table
如此一來 APP 端不需另做修改可以直接用既有方式 query 查詢。

透過 EXPLAIN 查看 select 與 partation 的使用情況

簡單來說~
使用 Mysql Partition 之後 APP 只要面對、操作一張表 / table 就能享受拆表、多表的好處，在實際操作上沒有太大的改變!! 🚀

.
.
.

如何建立 Mysql Partition?

可以透過 SQL 語法在新建 table 時設定或對已存在 table 進行 alter 修改~

查詢 MySQL DB 是否支援 Partition

-- 查詢 mysql 版本，基本上 5.6 之後都支援 partition
SHOW VARIABLES LIKE "%version%";

-- 查詢 partition 是否存在並啟用
SHOW PLUGINS;

SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_TYPE='STORAGE ENGINE';

新增 table 與 partition (table schema)

這邊選用 RANGE Partitioning 做為範例，另外還有幾個類型可用
(參考 MySQL 官網 Ref. 註1)

個人認為 range 比較實用 🤓
在這個範例中，使用 age(int) 做為 partition key，多數的情況下 date 的年份或月份也是推薦的 partition key

CREATE TABLE member (
    id INT NOT NULL AUTO_INCREMENT,
    age INT NOT NULL,
    cont VARCHAR(255) NOT NULL, 
        PRIMARY KEY(id, age)
)
PARTITION BY range(age) (
   -- 由大至小設定
    PARTITION p0 VALUES LESS THAN (10), -- 0~9
    PARTITION p1 VALUES LESS THAN (20), -- 10~19
    PARTITION p2 VALUES LESS THAN (30), -- 20~29
    PARTITION p3 VALUES LESS THAN (40), -- 30~39
    PARTITION p4 VALUES LESS THAN (MAXVALUE) -- over 40
)
;


-- 若表已存在使用 alter (注意執行時間與效能)
ALTER table member 
PARTITION BY RANGE(age) -- partition key 必須為 primary key
    PARTITION p0 VALUES LESS THAN (10), -- 0~9
    PARTITION p1 VALUES LESS THAN (20), -- 10~19
    PARTITION p2 VALUES LESS THAN (30), -- 20~29
    PARTITION p3 VALUES LESS THAN (40), -- 30~39
    PARTITION p4 VALUES LESS THAN (MAXVALUE) -- over 40
);

查看 partition 狀態

-- 查看 table 被設定的 partition 參數
show create table member;

刪除 partition

-- 如果要刪掉 partition(注意執行時間與效能)
ALTER TABLE member REMOVE PARTITIONING;

-- 刪掉後建議重跑一下(注意執行時間與效能)
OPTIMIZE TABLE member;

.
.
.

效能測試 / query performance

透過上述的 schema 建立兩張 table，一張有 partition (member) 一張則否 (member2)
然後各塞入假資料約 20 萬筆(Ref. 註2)

利用 explain 指令查看 partition 的分割狀態

一般 where 比較… 實際上的差異較小

-- 有 partition
select SQL_NO_CACHE * from member
where age > 10 and age < 30;
-- avg: 10~17ms

-- 沒 partition
select SQL_NO_CACHE * from member2
where age > 10 and age < 30;
-- avg: 100~140ms

where 加上 group by

-- 有 partition
select SQL_NO_CACHE * from member
where age > 10 and age < 30
group by age;
-- avg: 80~130ms

-- 沒 partition
select SQL_NO_CACHE * from member2
where age > 10 and age < 30
group by age;
-- avg: 400~500ms



--- 暴力一點的查詢


-- 有 partition
select SQL_NO_CACHE sum(age), count(age), member.* from member
where age > 10 and age < 30
group by age;
-- avg: 190~220ms

-- 沒 partition
select SQL_NO_CACHE sum(age), count(age), member2.* from member2
where age > 10 and age < 30
group by age;
-- avg: 890~1.02ms

資料量愈多、愈大，更能看出有無 partition 的差異性~ 🚀

Partition 的使用時機與限制

• 適合資料筆數多或資料量大; 資料筆數如果只有幾萬筆就沒必要使用 partition 😕
• 資料量大且能明確找出資料分割點，如上面例子的 age 欄位或是常見的日期年份或月份
• 沒有鍵 (key) 時不適用
• 無法使用 Foreign Key(待確認)
• 常常需要 alter 大表時，partition 的效率較好
• 注意 mysql 5.6 後最多可分 8192 個區塊，並不是無限但十多個區塊不是問題~

Partition 的優缺點

優點：

• 現有 query 語句不變的情況下有效增進效能
• 不需因效能問題變更查詢規則或調整 APP 邏輯
• 有效將 index tree 分散，避免單表肥大;
• 易於將冷熱資料分開
• 常常需要將大批資料清空的情況 (e.g. log 或爬蟲原始資料)

缺點：

• DB 使用者或連線數多的時候 partition 並不會有幫助; 不過這個問題和 partition 無關就是~ 😗
• DB CPU 換來方便與效能 😑
• 大批資料變更 (insert, delete) 的鎖表行為造成速度下降。
  例如：大批寫入資料時，會先將所有底層分區 (partition) 鎖住，待資料寫入後才會將所有底層分區解鎖 (table lock or row lock)
  批次 insert 5000 筆資料有無 partition 的差異大概在 100~200ms 左右

Ref.

murmur

mysql partition range 用起來很像是機器選蕃茄 XD