Growth, Uncategorized, 什麼是?, 工具

簡單使用 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 查詢資料

normal-query
(單表指定 index 的查詢通常效率不會太差~)

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

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

explain-where-query
簡單的 where 區間查找了整張表 / table…

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

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

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

horizontal-query
將 20 萬筆資料分散儲存於 n 張表,分擔各表資料量~

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

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

repeat-query
這個方法雖然很蠢,但也有效!! 😓
圖例不太正確,不過就只是個範例~

.
.
.

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

mysql-partition
使用 Partition 的 table,是肉眼看不見的喔 🙈
透過 EXPLAIN 查看 select 與 partation 的使用情況
explain-select
(可以看出該表 / table 已經拆分成 5 張表了)

簡單來說~
使用 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';
mysql-partition-support-version-and-active
(由上述指令~ 檢查 DB 是否支援 Partition)

新增 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;
mysql-partition-show-create-table
(顯示 table 建立時的 schema 可以得知目前的 partition 的分割情況)

刪除 partition

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

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

.
.
.

效能測試 / query performance

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

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

explain-select
“member” 包含 5 個 partition
explain-select-without-partition
“member2” 沒有任何 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

Leave a Reply