Mysql資料表分區技術PARTITION淺析_Mysql

在這一章節裡， 我們來瞭解下 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個有經銷權的地區，

複製代碼 代碼如下:

+------------------+--------------------------------------+
| 地區             | 音像店 識別碼                         |
+------------------+--------------------------------------+
| 北區             | 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;