【MYSQL】 分割槽表
對於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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 分割槽表MySql
- MySQL分割槽表MySql
- MySQL 分割槽表探索MySql
- mysql 分表 分割槽MySql
- mysql 分割槽表用法MySql
- MySQL表分割槽管理MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- mysql 進行表分割槽MySql
- Mysql表分割槽實現MySql
- mysql分割槽表筆記MySql筆記
- MySQL入門--分割槽表MySql
- mysql —— 分表分割槽(1)MySql
- MySQL的List分割槽表MySql
- MySQL分割槽表的分割槽原理和優缺點MySql
- 將mysql非分割槽錶轉換為分割槽表MySql
- MySQL 分割槽表知識整理MySql
- MySQL調優之分割槽表MySql
- mysql的分割槽和分表MySql
- mysql的分割槽與分表MySql
- mysql分割槽表小結2MySql
- mysql表水平拆分和分割槽分表MySql
- oracle分割槽表和分割槽表exchangeOracle
- MySQL資料表分割槽手記MySql
- (3) MySQL分割槽表使用方法MySql
- mysql之分割槽表小結1MySql
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oracle分割槽表和非分割槽表exchangeOracle
- mysql~關於mysql分割槽表的測試MySql
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- mysql 分割槽MySql
- MySQL分割槽MySql
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽(二)筆記索引
- 如何查詢分割槽表的分割槽及子分割槽
- 第41期:MySQL 雜湊分割槽表MySql