MySQL中表分區的詳細介紹

MySQL表分區和分庫分錶一樣，都是為了提高資料庫的吞吐量。分區類似與分錶，分錶是邏輯上將一個大資料量的表分成多個，可以是水平分也可以是垂直分。而分區是將表的一個資料檔拆分成多個。不同的數據拆分到不同的文件中。這樣對於一個資料量非常大的表，有多個資料檔案來進行存儲，這樣就提高了資料庫的 io 效能。

既然是針對的資料表的檔案進行操作，那麼我們就需要先來了解 MySQL 表的儲存。我們知道，MySQL 有多種儲存引擎，不同的儲存引擎所儲存的檔案格式不同。這裡主要以 InnoDB 和 MyISAM 這兩種儲存引擎來說明。

InnoDB

.frm 檔案資料表的結構

.idb 檔案表的數據文件，獨享表空間，每個表有一個.idb 文件

.ibdata 文件表的資料文件，共享表空間，所有的表使用這一個資料

檔案

MyISAM

#.frm 檔案資料表的結構

.myd 檔案資料檔

.myi 檔案索引檔案

# 

# 

##首先要查看我們目前的資料庫版本是否支援分割區

1 show variables like '%partition%';

如何進行分割區呢？在進行資料庫水平切分的時候我們知道，水平切分可以根據指定字段取模的方式來分到不同的表中，也可以根據日期來進行切分，或者根據id 來分段，1-100 萬在第一張表中，100 萬零1 到200 萬在第二張表中以此類推等等。總之我們在進行切分的過程中有很多的途徑。那麼在表分區上資料庫也提供了我們多種方案可供我們選擇。

 MySQL 表格分區策略

RANGE 分區

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

#

1 DROP TABLE IF EXISTS `p_range`;
2 CREATE TABLE `p_range` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `name` char(20) NOT NULL,
5 PRIMARY KEY (`id`)
6 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
7 /*!50100 PARTITION BY RANGE (id)
8 (PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

最大值

1 PARTITION BY RANGE (id)
2 (
3 PARTITION p0 VALUES LESS THAN (8),
4 PARTITION p1 VALUES LESS THAN MAXVALUE)

適用場景：

這樣就表示，所有id 大於7 的資料記錄存在在p1 分割區裡。

RANGE 分割區在以下場合特別有用：

#·當需要刪除「舊的」資料時。如果你使用上面最近的例子給出的分區方案，你只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在 1991 年前就已經停止工作的僱員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR(separated) <=

1990；”這樣的一個 DELETE 查詢要有效得多。

·想要使用一個包含有日期或時間值，或包含有從一些其他層級數開始成長的值的欄位。

·經常執行直接依賴用於分割表的資料列的查詢。例如，當執行一個如

“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”

這樣的查詢時，MySQL 可以很迅速地確定只有分區p2 需要掃描，這是因為餘下的分區不可能包含有符合該WHERE 子句的任何記錄

 

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

#########

1 DROP TABLE IF EXISTS `p_list`;
2 CREATE TABLE `p_list` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `typeid` mediumint(10) NOT NULL DEFAULT '0',
5 `typename` char(20) DEFAULT NULL,
6 PRIMARY KEY (`id`,`typeid`)
7 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY LIST (typeid)
9 (PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

######HASH 分區 基於使用者定義的表達式的傳回值來進行選擇的分區，該表達式使用將要插入表中的這些行的列值進行計算。這個函數可以包含 MySQL 中有效的、產生非負整數值的任何表達式。 HASH 分區主要用來確保資料在預先決定數目的分區中平均分佈。在RANGE 和LIST 分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH 分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要分割成的分區數量#######

1 DROP TABLE IF EXISTS `p_hash`;
2 CREATE TABLE `p_hash` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `storeid` mediumint(10) NOT NULL DEFAULT '0',
5 `storename` char(255) DEFAULT NULL,
6 PRIMARY KEY (`id`,`storeid`)
7 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY HASH (storeid)9 PARTITIONS 4 */;

简单点说就是数据的存入可以按 partition by hash(expr); 这里的 expr 可以

是键名也可以是表达式比如 YEAR(time)，如果是表达式的情况下

“但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计

算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时

影响大量行的运算（例如批量插入）的时候。 ”

在执行删除、写入、更新时这个表达式都会计算一次。

数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为 N ，其中“N = MOD(expr, num)”。

比如上面的 storeid 为 10；那么 N=MOD(10,4) ;N 是等于 2 的，那么这条记录就存储在 p2 的分区里面。

如果插入一个表达式列值为'2005-09-15′的记录到表中，那么保存该条记录的分区确定如下：MOD(YEAR('2005-09-01′),4) = MOD(2005,4) = 1 ; 就存储在 p1 分区里面了。

 

分区注意点

1、重新分区时，如果原分区里面存在 maxvalue 则新的分区里面也必须包含

maxvalue 否则就错误。

alter table p_range2x
reorganize partition p1,p2
into (partition p0 values less than (5), partition p1 values less than maxvalue);
[Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

2、分区删除时，数据也同样会被删除 alter table p_range drop partition p0;

3、如果 range 分区列表里面没有 maxvalue 则如有新数据大于现在分区 range 数据值那么这个数据是无法写入到数据库表的。

 

4、修改表名不需要 删除分区后在进行更改，修改表名后分区存储 myd myi 对应也会自动更改。

 

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用 TRUNCATE TABLE 命令。（请参见 13.2.9 节，“TRUNCATE 语法”）。

 

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容，或者在 13.1.2 节，“ALTER TABLE 语法” 中参考关于 REORGANIZE PARTITION 的信息。

 

5、对表进行分区时，不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。

 

6、list 方式分区没有类似于 range 那种 less than maxvalue 的写法，也就是说 list 分区表的所有数据都必须在分区字段的值列表集合中。

 

7、在 MySQL 5.1 版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用 MyISAM，而对另一个使用 InnoDB。

 

8、分区的名字是不区分大小写的，myp1 与 MYp1 是相同的。

  

以上是MySQL中表分區的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！