程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql數據表分區技巧PARTITION淺析

Mysql數據表分區技巧PARTITION淺析

編輯：MySQL綜合教程

Mysql數據表分區技巧PARTITION淺析。本站提示廣大學習愛好者：（Mysql數據表分區技巧PARTITION淺析）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql數據表分區技巧PARTITION淺析正文

在這一章節裡，我們來懂得下 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;