MySQL Partitioning 分區

限制

 

MySQL 分區可以設定更改原先資料儲存結果。

以 MYISAM 引擎為例,原本一個 A 資料表在硬碟會存在

  • A.frm
  • A.MYD
  • A.MYI

三個檔案,無論資料筆數多寡都放在同一檔案內,這時資料儲存限制 == 作業系統單一檔案大小限制。在目前主流檔案系統之下,單一檔案大小限制在 4G ~ 16TByte 之間。

資料表太大對資料庫不是好事,有可能只為了讀取 10Byte 的資料,卻將整個資料表都讀取一遍,如果可能還是需要控制資料表的實體儲存容量,達到增進效能以及方便管理的目的。

參考

 

MySQL_Partitioning01

以時間作為分區依據

以一個有 AUTO_INCREMENT 及 CURRENT_TIMESTAMP 的資料表為例,

關鍵是「必須把區分時間的欄位包含到 Primary Key 內

 

一個將無分區資料表修改為具分區功能範例

  1. 建立一個具有分區功能資料表,必須和原資料表資料結構相同,準備做替換用
  2. 將原資料表資料複製到此分區資料表,使用的是 INSERT SELECT 語法
  3. 刪除原資料表
  4. 將分區資料表名稱更換為原資料表名稱

 

顯示資料表分區資訊

使用的是顯示建立資料表資訊技巧

一個分區資料表建立後修改範例

 

 

分區區分到年、月

順便說一個情況,第一個設定是 199501 ,代表 1995年1月以前的資料存在此,而199502 則只存放 1995年 2 月的資料,因為沒有 MAXVALUE 所以超過的日期會新增失敗

InnoDB 如何分區區分檔案

重新啟動資料庫

使用 HASH

使用 Create_Time 轉天數後 hash

 

從插入多筆 2018/06/07 資料來觀察 HASH 儲存資料邏輯

分區刪除合併檔案變化

分區會將檔案根據條件分開,刪除分區檔案就會合併?

理論上是如此,實際測試亦是如此

查詢時是否有效套用到分區?

使用 EXPLAIN PARTITIONS 可以查詢取用分區檔案的狀態

id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE Data2 p6 ALL NULL NULL NULL NULL 2738 Using where

如果查訊跨到多日就會查詢到多個或是全部的分區檔案,如此就失去分區作用