| 導購 | 订阅 | 在线投稿
分享
 
 
 

MySQL5.1體驗--MySQL分區

來源:互聯網  2008-05-21 09:45:31  評論

一、概述

相信有很多人經常會問同樣的一個問題:當 MySQL

的總記錄數超過了100萬後,會出現性能的大幅度下降嗎?答案是肯定的,但是性能下降>的比率不一而同,要看系統的架構、應用程序、還有>包 括索引、服務器硬件等多種因素而定。當有網友問我這個問題的時候,我最常見的回答>就是:分表,可以根據id區間或者時間先後順序等多

種規則來分表。分表很容易,然而由此所帶來的應用程序甚至是架構方面的改動工作卻不>容小觑,還包括將來的擴展性等。

在以前,一種解決方案就是使用 MERGE

類型,這是一個非常方便的做飯。架構和程序基本上不用做改動,不過,它的缺點是顯見的:

只能在相同結構的 MyISAM 表上使用

無法享受到 MyISAM 的全部功能,例如無法在 MERGE 類型上執行 FULLTEXT 搜索

它需要使用更多的文件描述符

讀取索引更慢

這個時候,MySQL 5.1 中新增的分區(Partition)功能的優勢也就很明顯了:

與單個磁盤或文件系統分區相比,可以存儲更多的數據

很容易就能刪除不用或者過時的數據

一些查詢可以得到極大的優化

涉及到 SUM()/COUNT() 等聚合函數時,可以並行進行

IO吞吐量更大

分區允許可以設置爲任意大小的規則,跨文件系統分配單個表的多個部分。實際上,表的不同部分在不同的位置被存儲爲單獨的表。

二、分區的類型

RANGE 分區:基于屬于一個給定連續區間的列值,把多行分配給分區。參見18.2.1節,RANGE分區

LIST 分區:類似于按RANGE分區,區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇。參見18.2.2節,LIST分區

HASH分區:基于用戶定義的表達式的返回值來進行選擇的分區,該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包>含MySQL中有效的、産生非負整數值的任何表達式。參見18.2.3節,HASH分區

KEY

分區:類似于按HASH分區,區別在于KEY分區只支持計算一列或多列,且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含>整數值。

參見18.2.4節,KEY分區

三、分區例子:

RANGE 類型

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) ( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES LESS THAN (9000000) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

在這裏,將用戶表分成4個分區,以每300萬條記錄爲界限,每個分區都有自己獨立的數據、索引文件的存放目錄,與此同時,這些目錄所在的>

物理磁盤分區可能也都是完全獨立的,可以多大提高了磁盤IO吞吐量。

LIST 類型

CREATE TABLE category ( cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY LIST (cid) ( PARTITION p0 VALUES IN (0,4,8,12) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES IN (1,5,9,13) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES IN (2,6,10,14) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES IN (3,7,11,15) DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

分成4個區,數據文件和索引文件單獨存放。

HASH 類型

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY HASH (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

分成4個區,數據文件和索引文件單獨存放。

KEY 類型

REATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY KEY (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

分成4個區,數據文件和索引文件單獨存放。

子分區

子分區是針對 RANGE/LIST 類型的分區表中每個分區的再次分割。再次分割可以是 HASH/KEY 等類型。例如:

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');

對 RANGE 分區再次進行子分區劃分,子分區采用 HASH 類型。

或者

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');

對 RANGE 分區再次進行子分區劃分,子分區采用 KEY 類型。

四、分區管理

刪除分區

ALERT TABLE users DROP PARTITION p0;

刪除分區 p0。

重建分區

RANGE 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

將原來的 p0,p1 分區合並起來,放到新的 p0 分區中。

LIST 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

將原來的 p0,p1 分區合並起來,放到新的 p0 分區中。

HASH/KEY 分區重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分區的數量變成2,在這裏數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分區

新增 RANGE 分區

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)DATA DIRECTORY = '/data8/data'INDEX DIRECTORY = '/data9/idx');

新增一個RANGE分區。

新增 HASH/KEY 分區

ALTER TABLE users ADD PARTITION PARTITIONS 8;

將分區總數擴展到8個。

好了,本次體驗先到這裏,更多詳情請看 MySQL 手冊第18章。

  一、概述   相信有很多人經常會問同樣的一個問題:當 MySQL   的總記錄數超過了100萬後,會出現性能的大幅度下降嗎?答案是肯定的,但是性能下降>的比率不一而同,要看系統的架構、應用程序、還有>包 括索引、服務器硬件等多種因素而定。當有網友問我這個問題的時候,我最常見的回答>就是:分表,可以根據id區間或者時間先後順序等多   種規則來分表。分表很容易,然而由此所帶來的應用程序甚至是架構方面的改動工作卻不>容小觑,還包括將來的擴展性等。   在以前,一種解決方案就是使用 MERGE   類型,這是一個非常方便的做飯。架構和程序基本上不用做改動,不過,它的缺點是顯見的:      只能在相同結構的 MyISAM 表上使用   無法享受到 MyISAM 的全部功能,例如無法在 MERGE 類型上執行 FULLTEXT 搜索   它需要使用更多的文件描述符   讀取索引更慢      這個時候,MySQL 5.1 中新增的分區(Partition)功能的優勢也就很明顯了:      與單個磁盤或文件系統分區相比,可以存儲更多的數據   很容易就能刪除不用或者過時的數據   一些查詢可以得到極大的優化   涉及到 SUM()/COUNT() 等聚合函數時,可以並行進行   IO吞吐量更大      分區允許可以設置爲任意大小的規則,跨文件系統分配單個表的多個部分。實際上,表的不同部分在不同的位置被存儲爲單獨的表。   二、分區的類型      RANGE 分區:基于屬于一個給定連續區間的列值,把多行分配給分區。參見18.2.1節,RANGE分區   LIST 分區:類似于按RANGE分區,區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇。參見18.2.2節,LIST分區   HASH分區:基于用戶定義的表達式的返回值來進行選擇的分區,該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包>含MySQL中有效的、産生非負整數值的任何表達式。參見18.2.3節,HASH分區   KEY   分區:類似于按HASH分區,區別在于KEY分區只支持計算一列或多列,且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含>整數值。   參見18.2.4節,KEY分區      三、分區例子:      RANGE 類型   CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) ( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES LESS THAN (9000000) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');   在這裏,將用戶表分成4個分區,以每300萬條記錄爲界限,每個分區都有自己獨立的數據、索引文件的存放目錄,與此同時,這些目錄所在的>   物理磁盤分區可能也都是完全獨立的,可以多大提高了磁盤IO吞吐量。      LIST 類型   CREATE TABLE category ( cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY LIST (cid) ( PARTITION p0 VALUES IN (0,4,8,12) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES IN (1,5,9,13) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES IN (2,6,10,14) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES IN (3,7,11,15) DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');   分成4個區,數據文件和索引文件單獨存放。      HASH 類型   CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY HASH (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');   分成4個區,數據文件和索引文件單獨存放。      KEY 類型   REATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY KEY (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');   分成4個區,數據文件和索引文件單獨存放。      子分區   子分區是針對 RANGE/LIST 類型的分區表中每個分區的再次分割。再次分割可以是 HASH/KEY 等類型。例如:   CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');   對 RANGE 分區再次進行子分區劃分,子分區采用 HASH 類型。   或者   CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');   對 RANGE 分區再次進行子分區劃分,子分區采用 KEY 類型。         四、分區管理      刪除分區   ALERT TABLE users DROP PARTITION p0;   刪除分區 p0。      重建分區      RANGE 分區重建   ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));   將原來的 p0,p1 分區合並起來,放到新的 p0 分區中。      LIST 分區重建   ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));   將原來的 p0,p1 分區合並起來,放到新的 p0 分區中。      HASH/KEY 分區重建   ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;   用 REORGANIZE 方式重建分區的數量變成2,在這裏數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。            新增分區      新增 RANGE 分區   ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)DATA DIRECTORY = '/data8/data'INDEX DIRECTORY = '/data9/idx');   新增一個RANGE分區。      新增 HASH/KEY 分區   ALTER TABLE users ADD PARTITION PARTITIONS 8;   將分區總數擴展到8個。            好了,本次體驗先到這裏,更多詳情請看 MySQL 手冊第18章。     
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有