MySQL分區(mysql partition)[zz]

 

一、概述 相信有很多人經常會問同樣的一個問題：當 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章。