MySQL資料表分割槽手記

木大大發表於2021-05-03
MySql

當資料已存，對現有的表進行分割槽，分割槽演算法使用list演算法，根據type欄位進行分割槽

ALTER TABLE `articles`
PARTITION BY list (type)
(
PARTITION p1 VALUES in(1),
PARTITION p2  VALUES in(2),
PARTITION p3  VALUES in(3),
PARTITION p4  VALUES in(4)
);

由於type不是主鍵，mysql報錯資訊如下

A PRIMARY KEY must include all columns in the table's partitioning function
分割槽的欄位必須是要包含在主鍵當中。這時候分割槽的欄位要麼是主鍵，要麼把分割槽欄位加入到主鍵中，從而形成複合主鍵。

list :條件值為一個資料列表。
演算法：根據“欄位的內容值”是否在某個“列表”中進行分割槽，透過預定義的列表的值來對資料進行分割。

例子：假如你建立一個如下的一個表，該表儲存有全國20家分公司的職員記錄，這20家分公司的編號從1到20.而這20家分公司分佈在全國4個區域，如下表所示：

職員表：emp
id name store_id(分公司的id)

北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
東部 3,12,19,20
西部 8,14,15,16

create table emp(
    id int,
    name varchar(32),
    store_id int
)engine myisam charset utf8
partition by list (store_id)(
    partition p_north values in (1,4,5,6,17,18),
    partition p_east values in(2,7,9,10,11,13),
    partition p_south values in(3,12,19,20),
    partition p_west values in(8,14,15,16)
);
#插入資料
insert into emp values(12,’xiaobao’,14)

檢視執行計劃得知，只有在條件語句中帶有分割槽條件值，才會在設定的分割槽進行查詢，否則會在所有分割槽中查詢。

MySQL分割槽的實測手記

演算法：根據欄位內容的值是否在某個範圍進行分割槽，透過預定義的範圍值來對資料進行分割。

這種模式允許將資料劃分不同範圍。例如可以將一個表透過月份劃分成若干個分割槽

create table p_range(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by range (month(birthday))(
    partition p_1 values less than (3),
    partition p_2 values less than(6),
    partition p_3 values less than(9),
    partition p_4 values less than MAXVALUE
);

less than 小於；
MAXVALUE可能的最大值

分割槽欄位的值對 分割槽個數 取模（%）運算，根據餘數將資料分配到不同的分割槽。

注意：hash分割槽只能針對整數進行hash！

這種模式允許透過對錶的一個或多個列的Hash Key進行計算，最後透過這個Hash碼不同數值對應的資料區域進行分割槽。

說白了就是：基於給定的分割槽個數，將資料分配到不同的分割槽。

例如可以建立一個對錶主鍵進行分割槽的表。

# 按照生日的月份hash值,將資料記錄劃分到5個區內:
create table  p_hash(
    id  int,
    name varchar(20),
    birthday date
)engine myisam charset utf8
partition by hash(month(birthday)) partitions 5;

上面Hash模式的一種延伸（KEY分割槽支援除text和BLOB之外的所有資料型別的分割槽），這裡的Hash Key是MySQL系統產生的。

對分割槽欄位的值 進行HASH運算（系統自行運算），根據得到的 Hash key，將資料分配到對應的分割槽中。

#根據id分割槽，分五個區
create table p_key(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by key (id) partitions 5;

注意：KEY分割槽不允許使用使用者自定義的表示式進行分割槽，
例如：key(month(birthday))是不允許的。

① 在key/hash領域不會造成資料丟失(刪除分割槽後資料會重新整合到剩餘的分割槽去，即合併分割槽)
② 在range/list領域會造成資料丟失（真實刪除資料）

求餘方式(key/hash):

alter table 表名 coalesce partition 數量;

範圍方式(range/list):

alter table 表名 drop partition 分割槽名稱;

MySQL分割槽的實測手記

MySQL分割槽的實測手記

MySQL分割槽的實測手記

增加分割槽

求餘方式： key/hash

alter table 表名 add partition partitions 數量;

範圍方式： range/list

>  alter table 表名 add partition(
           partition 名稱 values less than (常量)
           或
           partition 名稱 in (n,n,n)
       );

MySQL分割槽的實測手記

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章