MySQL之-數據表分區技術PARTITION的代碼示例淺析

385

這篇文章主要介紹了mysql數據表分區技術partition淺析,分別介紹了 mysql 中的分區技術 range、list、 hash,需要的朋友可以參考下。

在這一章節里， 我們來了解下 Mysql 中的分區技術 (RANGE, LIST, HASH)
?
Mysql 的分區技術與水平分表有點類似， 但是它是在邏輯層進行的水平分表， 對于應用而言它還是一張表， 換句話說: 分區不是實際真正的對一張表進行拆分，分區之后表還是一個表，它是把存儲文件進行拆分。

在 Mysql 5.1(后) 有了幾種分區類型:

RANGE分區: 基于屬于一個給定連續區間的列值， 把多行分配給分區

LIST分區: 類似于按 RANGE 分區， 區別在于 LIST 分區是基于列值匹配一個離散值集合中的某個值來進行選擇

HASH分區: 基于用戶定義的表達式的返回值來進行選擇分區， 該表達式使用將要插入到表中的這些行的列值進行計算， 這個函數可以包含 Mysql 中有效的、產生非負整數值的任何表達式

KEY分區: 累世于按 HASH 分區， 區別在于 KEY 分區只支持計算一列或多列， 且 Mysql 服務器提供其自身的哈希函數

分區應該注意的事項：

1、 做分區時，要么不定義主鍵，要么把分區字段加入到主鍵中
2、 分區字段不能為NULL，要不然怎么確定分區范圍呢，所以盡量 NOT NULL
?
首先你可以查看下你的 Mysql 版本是否支持 PARTITION

mysql>?show?plugins;  ?  |?partition????|?ACTIVE???|?STORAGE?ENGINE?????|?NULL????|?GPL?????|

或者:

mysql>?show?variables?like?"%part%";  ?  +-------------------+-------+  |?Variable_name?????|?Value?|  +-------------------+-------+  |?have_partitioning?|?YES???|  +-------------------+-------+

RANGE 分區

假定你創建了一個如下的表， 該表保存有20家音像店的職員記錄， 這20家音像店的編號從1到20。 如果你想將其分成4個小分區， 那么你可以采用RANGE分區， 創建的數據庫表如下:

mysql->?CREATE?TABLE?employees?(  ?????->?????id?INT?NOT?NULL,  ?????->?????fname?VARCHAR(30),  ?????->?????lname?VARCHAR(30),  ?????->?????hired?DATE?NOT?NULL?DEFAULT?'1970-01-01',  ?????->?????separated?DATE?NOT?NULL?DEFAULT?'9999-12-31',  ?????->?????job_code?INT?NOT?NULL,  ?????->?????store_id?INT?NOT?NULL  ?????->?)?ENGINE=Myisam?DEFAULT?CHARSET=utf8  ?????->?PARTITION?BY?RANGE?(store_id)?(  ?????->?????PARTITION?P0?VALUES?LESS?THAN?(6),  ?????->?????PARTITION?P1?VALUES?LESS?THAN?(11),  ?????->?????PARTITION?P2?VALUES?LESS?THAN?(16),  ?????->?????PARTITION?P3?VALUES?LESS?THAN?(21)  ?????->?);

如果你想把不同時期離職的員工進行分別存儲， 那么你可以將日期字段 separated (即離職時間) 作為一個 key, 創建的 SQL 語句如下:

mysql->?CREATE?TABLE?employees?(  ?????->?????id?INT?NOT?NULL,  ?????->?????fname?VARCHAR(30),  ?????->?????lname?VARCHAR(30),  ?????->?????hired?DATE?NOT?NULL?DEFAULT?'1970-01-01',  ?????->?????separated?DATE?NOT?NULL?DEFAULT?'9999-12-31',  ?????->?????job_code?INT?NOT?NULL,  ?????->?????store_id?INT?NOT?NULL  ?????->?)?ENGINE=Myisam?DEFAULT?CHARSET=utf8  ?????->?PARTITION?BY?RANGE?(YEAR(separated))?(  ?????->?????PARTITION?P0?VALUES?LESS?THAN?(2001),  ?????->?????PARTITION?P1?VALUES?LESS?THAN?(2011),  ?????->?????PARTITION?P2?VALUES?LESS?THAN?(2021),  ?????->?????PARTITION?P3?VALUES?LESS?THAN?MAXVALUE  ?????->?);

List 分區

同樣的例子， 如果這20家影像店分布在4個有經銷權的地區，

+------------------+--------------------------------------+  |?地區?????????????|?音像店?ID?號?????????????????????????|  +------------------+--------------------------------------+  |?北區?????????????|?3,?5,?6,?9,?17???????????????????????|  |?東區?????????????|?1,?2,?10,?11,?19,?20?????????????????|  |?西區?????????????|?4,?12,?13,?14,?18????????????????????|  |?中心區???????????|?7,?8,?15,?16?????????????????????????|  +------------------+--------------------------------------+  ?  mysql->?CREATE?TABLE?employees?(  ?????->?????id?INT?NOT?NULL,  ?????->?????fname?VARCHAR(30),  ?????->?????lname?VARCHAR(30),  ?????->?????hired?DATE?NOT?NULL?DEFAULT?'1970-01-01',  ?????->?????separated?DATE?NOT?NULL?DEFAULT?'9999-12-31',  ?????->?????job_code?INT?NOT?NULL,  ?????->?????store_id?INT?NOT?NULL  ?????->?)?ENGINE=Myisam?DEFAULT?CHARSET=utf8  ?????->?PARTITION?BY?LIST?(store_id)?(  ?????->?????PARTITION?pNorth???VALUES?IN?(3,?5,?6,?9,?17),  ?????->?????PARTITION?pEast????VALUES?IN?(1,?2,?10,?11,?19,?20),  ?????->?????PARTITION?pWest????VALUES?IN?(4,?12,?13,?14,?18),  ?????->?????PARTITION?pCentral?VALUES?IN?(7,?8,?15,?16)  ?????->?);

當你創建完之后， 你可以進入 Mysql 數據儲存文件， 該文件夾位置定義在 Mysql 配置文件中

shawn@Shawn:~$?sudo?vi?/etc/mysql/my.cnf;  ?  [mysqld]  datadir?????????=?/var/lib/mysql  ?  shawn@Shawn:~$?cd?/var/lib/mysql/dbName  shawn@Shawn:/var/lib/mysql/dbName$?ll  ?  顯示如下:  8768?Jun??7?22:01?employees.frm  ??48?Jun??7?22:01?employees.par  ???0?Jun??7?22:01?employees#P#pCentral.MYD  1024?Jun??7?22:01?employees#P#pCentral.MYI  ???0?Jun??7?22:01?employees#P#pEast.MYD  1024?Jun??7?22:01?employees#P#pEast.MYI  ???0?Jun??7?22:01?employees#P#pNorth.MYD  1024?Jun??7?22:01?employees#P#pNorth.MYI  ???0?Jun??7?22:01?employees#P#pWest.MYD  1024?Jun??7?22:01?employees#P#pWest.MYI

從這里可以看出， 它是把存儲文件根據我們的定義進行了拆分

employees.frm?=?表結構  employees.par?=?partition,?申明是一個分區表  .MYD?=?數據文件  .MYI?=?索引文件

HASH 分區

HASH 分區主要用來確保數據在預先確定數目的分區中平均分布
如果你想把不同時期加入的員工進行分別存儲， 那么你可以將日期字段 hired 作為一個 key

mysql->?CREATE?TABLE?employees?(  ?????->?????id?INT?NOT?NULL,  ?????->?????fname?VARCHAR(30),  ?????->?????lname?VARCHAR(30),  ?????->?????hired?DATE?NOT?NULL?DEFAULT?'1970-01-01',  ?????->?????separated?DATE?NOT?NULL?DEFAULT?'9999-12-31',  ?????->?????job_code?INT?NOT?NULL,  ?????->?????store_id?INT?NOT?NULL  ?????->?)?ENGINE=Myisam?DEFAULT?CHARSET=utf8  ?????->?PARTITION?BY?HASH?(YEAR(hired))?(  ?????->?????PARTITIONS?4  ?????->?);  ?????  #這里注意的是?PARTITIONS，?多了一個?s

這里要提一下的就是， 如上的例子都是使用的是 Myisam 存儲引擎，它默認使用獨立表空間， 所以你可以在上面的磁盤空間里看到不同的分區
而 InnoDB 引擎則默認使用共享表空間, 此時就算你對 InnoDB 表進行分區， 你查看下會發現， 它并沒有像 Myisam 那么樣進行物理上的分區， 所以你需要修改下 Mysql 配置文件:

shawn@Shawn:~$?sudo?vi?/etc/mysql/my.cnf;  ?  #添加:  innodb_file_per_table=1  ?  #重啟?mysql  shawn@Shawn:~$?sudo?/etc/init.d/mysql?restart

此時你再對 InooDB 進行分區， 則會有如下效果:

8768?Jun??7?22:54?employees.frm  ???48?Jun??7?22:54?employees.par  98304?Jun??7?22:54?employees#P#pCentral.ibd  98304?Jun??7?22:54?employees#P#pEast.ibd  98304?Jun??7?22:54?employees#P#pNorth.ibd  98304?Jun??7?22:54?employees#P#pWest.ibd

分區管理

刪除分區

mysql>?alter?table?employees?drop?partition?pWest;

新增分區

#range添加新分區?  mysql>?alter?table?employees?add?partition?(?partition?p4?values?less?than?(26)?);?  ??  #list添加新分區?  mysql>?alter?table?employees?add?partition(?partition?pSouth?values?in?(21,?22,?23)?);?  ??  #hash重新分區?  mysql>?alter?table?employees?add?partition?partitions?5;

? 版權聲明
THE END
數據庫
# 數據庫
喜歡就支持一下吧
點贊5 分享