Mysql數據表分區(qū)技術PARTITION淺析

更新時間：2014年06月25日 10:21:02 投稿：junjie

這篇文章主要介紹了Mysql數據表分區(qū)技術PARTITION淺析,分別介紹了 Mysql 中的分區(qū)技術 RANGE、LIST、 HASH,需要的朋友可以參考下

在這一章節(jié)里，我們來了解下 Mysql 中的分區(qū)技術 (RANGE, LIST, HASH)

Mysql 的分區(qū)技術與水平分表有點類似，但是它是在邏輯層進行的水平分表，對于應用而言它還是一張表，換句話說: 分區(qū)不是實際真正的對一張表進行拆分，分區(qū)之后表還是一個表，它是把存儲文件進行拆分。

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

RANGE分區(qū): 基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)

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

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

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

分區(qū)應該注意的事項：

1、做分區(qū)時，要么不定義主鍵，要么把分區(qū)字段加入到主鍵中
2、分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量 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 分區(qū)

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

復制代碼代碼如下:

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, 創(chuàng)建的 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 分區(qū)

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

復制代碼代碼如下:

+------------------+--------------------------------------+

| 地區(qū)             | 音像店 ID 號                         |

+------------------+--------------------------------------+

| 北區(qū)             | 3, 5, 6, 9, 17                       |

| 東區(qū)             | 1, 2, 10, 11, 19, 20                 |

| 西區(qū)             | 4, 12, 13, 14, 18                    |

| 中心區(qū)           | 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)

     -> );

當你創(chuàng)建完之后，你可以進入 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, 申明是一個分區(qū)表

.MYD = 數據文件

.MYI = 索引文件

HASH 分區(qū)

HASH 分區(qū)主要用來確保數據在預先確定數目的分區(qū)中平均分布
如果你想把不同時期加入的員工進行分別存儲，那么你可以將日期字段 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 存儲引擎，它默認使用獨立表空間，所以你可以在上面的磁盤空間里看到不同的分區(qū)
而 InnoDB 引擎則默認使用共享表空間, 此時就算你對 InnoDB 表進行分區(qū)，你查看下會發(fā)現，它并沒有像 Myisam 那么樣進行物理上的分區(qū)，所以你需要修改下 Mysql 配置文件:

復制代碼代碼如下:

shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;

#添加: 

innodb_file_per_table=1

#重啟 mysql

shawn@Shawn:~$ sudo /etc/init.d/mysql restart

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

復制代碼代碼如下:

Jun  7 22:54 employees.frm

   48 Jun  7 22:54 employees.par

Jun  7 22:54 employees#P#pCentral.ibd

Jun  7 22:54 employees#P#pEast.ibd

Jun  7 22:54 employees#P#pNorth.ibd

Jun  7 22:54 employees#P#pWest.ibd

分區(qū)管理

刪除分區(qū)

復制代碼代碼如下:

mysql> alter table employees drop partition pWest;

新增分區(qū)

復制代碼代碼如下:

#range添加新分區(qū)  

mysql> alter table employees add partition ( partition p4 values less than (26) );  

#list添加新分區(qū)  

mysql> alter table employees add partition( partition pSouth values in (21, 22, 23) );  

#hash重新分區(qū)  

mysql> alter table employees add partition partitions 5;

您可能感興趣的文章:

mysql 5.7.11 安裝配置教程
這篇文章主要為大家詳細介紹了mysql 5.7.11 安裝配置教程，感興趣的小伙伴們可以參考一下
2016-06-06
更改MySQL數據庫的編碼為utf8mb4問題
這篇文章主要介紹了更改MySQL數據庫的編碼為utf8mb4問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2022-11-11
配置MySQL與卸載MySQL實例操作
我們主要介紹的是正確通過rpm包安裝、對MySQL進行配置與卸載的實際操作步驟，以下就是文章的具體內容描述，望你會有所收獲。
2010-08-08
MySQL數據庫備份以及常用備份工具集合
數據庫備份種類按照數據庫大小備份，有四種類型，分別應用于不同場合。本文將MySQL 數據庫備份種類以及常用備份工具進行匯總，方便大家學習。
2015-08-08
教你一招永久解決mysql插入中文失敗問題
mysql經常會遇到某些中文插入異常,最近有同學反饋了這樣一個問題,所以下面這篇文章主要給大家介紹了關于如何永久解決mysql插入中文失敗問題的相關資料,需要的朋友可以參考下
2021-11-11
MySQL 如何查詢當前最新事務ID
這篇文章主要介紹了MySQL 如何查詢當前最新事務ID，幫助大家更好的理解和學習MySQL，感興趣的朋友可以了解下
2020-08-08
mysql存儲過程之游標（DECLARE）原理與用法詳解
這篇文章主要介紹了mysql存儲過程之游標（DECLARE）原理與用法,結合實例形式詳細分析了mysql存儲過程游標（DECLARE）的基本功能、原理、使用方法及操作注意事項,需要的朋友可以參考下
2019-12-12
mysql中四種備份模式
本文主要介紹了mysql中四種備份模式,無論使用哪種備份方式,都需要根據業(yè)務需求和數據量大小來選擇合適的備份策略,并定期驗證備份是否有效,感興趣的可以了解一下
2023-11-11
MySQL添加索引特點及優(yōu)化問題
這篇文章主要介紹了MySQL添加索引特點及優(yōu)化問題，MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度，感興趣的小伙伴可以參考一下
2022-07-07
MySQL數據xtrabackup物理備份的方式
Xtrabackup是開源免費的支持MySQL 數據庫熱備份的軟件,在 Xtrabackup 包中主要有 Xtrabackup 和 innobackupex 兩個工具,本文給大家介紹MySQL數據xtrabackup物理備份方法,感興趣的朋友跟隨小編一起看看吧
2023-10-10