Mysql表分區

標籤：org   外鍵   har   bpa   資料   ceil   border   com   and   

參考地址：http://dev.mysql.com/doc/refman/5.6/en/partitioning.html

什麼時候使用分區

1. 海量資料
2. 資料表索引大於伺服器有效記憶體

 

分區的限制

1. 大部分只能對資料表的整型列進行分區，或者資料列可以通過分區函數轉化成整型列（其中COLUMNS支援 Integer / String / DATE / DATETIME類型）
2. 最大分區數目不能超過1024
3. 如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵內
4. 不支援外鍵
5. 不支援全文索引（fulltext）
6. 分區支援函數限制

ABS()	CEILING()	DAY()
DAYOFMONTH()	DAYOFWEEK()	DAYOFYEAR()
DATEDIFF()	EXTRACT()	FLOOR()
HOUR()	MICROSECOND()	MINUTE()
MOD()	MONTH()	QUARTER()
SECOND()	TIME_TO_SEC()	TO_DAYS()
TO_SECONDS()	UNIX_TIMESTAMP()	WEEKDAY()
YEAR()		YEARWEEK()

 

 

       MySQL支援RANGE，LIST，HASH，KEY分區類型，其中以RANGE最為常用：

1. Range（範圍）–這種模式允許將資料劃分不同範圍。例如可以將一個表通過年份劃分成若干個分區。
2. Hash（雜湊）–這中模式允許通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分區。例如可以建立一個對錶主鍵進行分區的表。
3. Key（索引值）-上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
4. List（預定義列表）–這種模式允許系統通過預定義的列表的值來對資料進行分割。
5. Composite（複合模式） –以上模式的組合使用　

 

分區SQL

過濾分區表的WHERE條件必須是切分分區表的列

range分區

ALTER TABLE employees 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)

);

list分區

ALTER TABLE employees 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)

);

hash分區

hash分區的目的是將資料均勻的分布到預先定義的各個分區中，保證各分區的資料量大致一致。

ALTER TABLE employees PARTITION BY HASH(store_id)

PARTITIONS 4;

hash的分區函數頁需要返回一個整數值。partitions子句中的值是一個非負整數，不加的partitions子句的話，預設為分區數為1。

key分區

key分區和hash分區相似，不同在於hash分區是使用者自訂函數進行分區，key分區使用mysql資料庫提供的函數進行分區，NDB cluster使用MD5函數來分區，對於其他儲存引擎mysql使用內部的hash函數，這些函數基於password()一樣的演算法。

ALTER TABLE tm1  PARTITION BY KEY(s1)

PARTITIONS 10;

columns分區

上面的RANGE、LIST、HASH、KEY四種分區中，分區的條件必須是整形，如果不是整形需要通過函數將其轉換為整形。

mysql-5.5開始支援COLUMNS分區，可視為RANGE和LIST分區的進化，COLUMNS分區可以直接使用非整形資料進行分區。COLUMNS分區支援以下資料類型：

所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支援。

日期類型，如DATE和DATETIME。其餘日期類型不支援。

字串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支援。

COLUMNS可以使用多個列進行分區。

 

子分區Subpartitioning

ALTER TABLE ts  PARTITION BY RANGE( YEAR(purchased) )

    SUBPARTITION BY HASH( TO_DAYS(purchased) )

    SUBPARTITIONS 2 (

        PARTITION p0 VALUES LESS THAN (1990),

        PARTITION p1 VALUES LESS THAN (2000),

        PARTITION p2 VALUES LESS THAN MAXVALUE

);

管理分區

增加分區

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

刪除分區

ALTER TABLE tr DROP PARTITION p2;

合并分區

ALTER TABLE members

    REORGANIZE PARTITION p0 INTO (

        PARTITION n0 VALUES LESS THAN (1960),

        PARTITION n1 VALUES LESS THAN (1970)

);

Mysql表分區