MySQL之-資料表分區技術PARTITION的程式碼範例淺析-mysql教程-PHP中文網

這篇文章主要介紹了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;

登入後複製

以上是MySQL之-資料表分區技術PARTITION的程式碼範例淺析的詳細內容。更多資訊請關注PHP中文網其他相關文章！