【MYSQL】 分割槽表

wh7577發表於2021-09-09


    對於MYSQL的態度一直都是會基本SQL和簡單命令就行,最近處理一個資料量很大的專案,為了提高效率,在資料庫方面的瓶頸上,選擇了使用分割槽表來提高查詢效率。至此和大家一起分享一下。

    1.引言

    本文初略的講述了mysql資料庫如何分割槽表。

    

    2.環境要求

    在5.1版本中不是預設就安裝了,而在之後版本中一般預設選擇了安裝分割槽表支援。可以透過如下方式檢視當前資料庫是否支援分割槽表操作:

wKioL1R6ym7A0CplAABfWiOHn-I850.jpg

    使用show variables like '%partition%';如果不支援分割槽,那麼value欄位值為No。

    3.重要概念描述

    3.1 分割槽欄位

    1)當僅存在單一主鍵時,不存在唯一鍵,那麼分割槽欄位必須是主鍵欄位;

    2)當存在複合主鍵時,不存在唯一鍵,那麼分割槽欄位必須是主鍵組合的一部分欄位,一個或多個。

    3)當主鍵和唯一鍵都存在時,那麼分割槽欄位必須同時包括主鍵欄位和唯一鍵欄位。

    4.分割槽表型別

    4.1 range分割槽

    1)語法展示:

# 語法

# 在建立表單的最後,新增partitions by range(分割槽欄位)(

#   partition 分割槽名 values less than(閥值1),

#   partition 分割槽名 values less than(閥值2),

#   ...

#   partition 分割槽名 values less than(閥值n),

# )

    示例展示:

create table test_range(

  id int auto_increment,

  description varchar(50),

  primary key(id)

) ENGINE=InnoDB auto_increment=1 default charset=utf8

partition by range(id)(

  partition p1 values less than(6), #id<6的存放在p1分割槽

  partition p2 values less than(11) #6 <= id < 11 存放在p2分割槽

);

    檢視分割槽情況:

   show create table test_range;

    wKioL1R6z4qC137DAADITZNS8H8149.jpg    

    注意到,在顯示的表結構新增了分割槽表的資訊。

    資料測試:

    insert into test_range values(null, "test1");    

    insert into test_range values(null, "test2");

    insert into test_range values(null, "test3");

    insert into test_range values(null, "test4");

    insert into test_range values(null, "test5");

    insert into test_range values(null, "test6");

    insert into test_range values(null, "test7");

    insert into test_range values(null, "test8");

    insert into test_range values(null, "test9");

    insert into test_range values(null, "test10");

    插入10條資料,此時我們來檢視其查詢執行過程:

wKioL1R60LDBET1bAADRpczcpSo931.jpg

    從結果可以發現,其只是在p1分割槽執行的查詢,那麼此時就減少了查詢掃描的資料量,從而提高了查詢效率。

    如果此時,我們插入第11條資料會發生什麼情況呢?

   insert into test_range values(null, "test11");

   會發錯:insert into test_range values(null, "test11")  Error Code: 1526. Table has no partition for value 11 0.015 sec

    原因很簡單,因為在我們建立表單時,僅僅指定了1 - 10的id數值分割槽,當插入id=11時的分割槽時,此時沒有分割槽提供,那麼就引發錯誤,那麼如果解決這樣的問題呢,採取如下方式,修改表的分割槽方式:

alter table test_range add partition(

  partition p3 values less than(MAXVALUE)

);

# 新增一個分割槽,也就是p3是id從11到maxValue的存放區域

    此時插入id=11的資料,並執行查詢解析:

wKiom1R60nnwSlUUAADL0geAo20618.jpg    發現,已經將其分配到p3分割槽中了。

    還需要特別注意的時,使用partition by range(分割槽欄位),其中的分割槽欄位可以是分割槽欄位的表單式,但是必須是返回的整數,在5.5版本中,可以使用partition by range column/columns語法,指定某個欄位。這裡不做介紹。大家可以自己嘗試一下。

    4.2 list分割槽

    list分割槽可以理解為集合分割槽方式,意思就是指定某個集合來分割槽。

    語法展示:

   partition by list(分割槽欄位表示式)(

     partition 分割槽名 values in(value1, value2,...,valuen)#分割槽集合

   );

    示例展示:

create table test_list(

  id int auto_increment,

  description varchar(50),

  primary key(id)

)ENGINE=InnoDB auto_increment=1 default charset=utf8

partition by list(id)(

  partition p1 values in (1, 3, 5, 7, 9),#id=1,3,5,7,9分配至p1區

  partition p2 values in (2, 4, 6, 8, 10)#id=2,4,6,8,10分配至p2區

);

#可以如4.1中使用show create table test_list檢視錶建立結構。

    資料測試:使用4.1中資料測試sql,插入10條資料。

wKioL1R61Y-RjP7aAADWGviW2FI351.jpg    可以發現其查詢的僅僅是p1區。如果需要新增分割槽,可以使用4.1中使用的add partition來新增分割槽。

    4.3 hash分割槽

    使用hash函式得到取模,分配到不同的分割槽中。分割槽表示式必須返回整數。

    語法展示:

   partition by hash(分割槽表示式) partitions 表數量(模數).

    示例展示:

create table test_hash(

  id int auto_increment,

  description varchar(50),

  primary key(id)

) ENGINE=InnoDB auto_increment=1 default charset=utf8

partition by hash(id) partitions 3; #以id分割槽,分配到3張表中

    資料測試:插入4.1類同10條資料

    wKioL1R615mA8FC8AADLcG2rGPs147.jpg

    你也可以嘗試修改id值,檢視其分配的分割槽。hash分割槽還有一種叫做linear hash線性分割槽,這裡不做介紹,

    

    4.4 key分割槽

    在本次開發中,我選擇的是key分割槽,因為其是針對一個或多個欄位作為分割槽欄位,不要求是正整數,其內部呼叫的是自己的hash函式,計算出hash整數值,然後取模分表。

    語法展示:

   partition by key(分割槽欄位組合) partitions 表數(模數)。

    操作和Hash分割槽一致,這裡就不做累贅的展示了。

    5.額外擴充套件

    5.1 在實際開發中,經常出現的情況是表已經上線使用,那麼必須動態新增分割槽型別。

   alter table 表名 partition by hash/key (分割槽欄位表示式) [partitions 表數]#如果不加partitions那麼預設為1.

   

   alter table 表名 partition by range/list(分割槽欄位表示式)(具體分割槽設定)。

    5.2 當發現之前的分割槽需要新增新的分割槽時,採取如下方式:

   list/range : alter table 表名 add partition (partition 分割槽名 [values in|values less than] [集合|數值]);

   hash/key : alter table 表名 add partition partitions 表數;

    例如:修改上述test_hash的分割槽數量

   alter table test_hash add partition partitions 6;

    5.3 刪除某個分割槽/刪除所有分割槽

   # 刪除某個分割槽

   list/range : alter table 表名 drop partition 分割槽名1, 分割槽名2,...;

   #例如:

   alter table test_list drop partition p1;

   hash/key : 上述語法不成立

   

   # 刪除整個分割槽

   alter table test_hash remove partitioning;

    還有諸如合併分割槽,以及5.5的一些新特性,list/range 增加column,columns支援。本文不做過多闡述。

©著作權歸作者所有:來自51CTO部落格作者程式冥的原創作品,如需轉載,請註明出處,否則將追究法律責任

mysql 分割槽表專題


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/2480/viewspace-2820648/,如需轉載,請註明出處,否則將追究法律責任。

相關文章