mysql 分割槽表用法

MySQL使用分割槽表的好處：

1，可以把一些歸類的資料放在一個分割槽中，可以減少伺服器檢查資料的數量加快查詢。

2，方便維護，透過刪除分割槽來刪除老的資料。

3，分割槽資料可以被分佈到不同的物理位置，可以做分散式有效利用多個硬碟驅動器。

MySQL可以建立四種分割槽型別的分割槽：

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

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

· HASH分割槽：基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL 中有效的、產生非負整數值的任何表示式。

· KEY 分割槽：類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL 伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。

一般用得多的是range分割槽和list分割槽。

RANGE分割槽

這裡以一個銷售的業務來做測試

銷售表有日期/商品/銷售額三個欄位

測試資料從2010年1月1日至2010年9月31日

以“月”為單位進行分割槽

初期分割槽定義

首先需要檢視，當前 資料庫是否支援分割槽

mysql>SHOW VARIABLES LIKE '%partition%';

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

| Variable_name | Value |

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

| have_partitioning | YES |

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

1 row in set (0.03 sec)

建立分割槽表，按照年月的方式分割槽。

mysql> CREATE TABLE sale_data (

-> sale_date DATETIME NOT NULL,

-> sale_item VARCHAR(2) NOT NULL ,

-> sale_money DECIMAL(10,2) NOT NULL

-> )

-> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (

-> PARTITION p201001 VALUES LESS THAN (201002),

-> PARTITION p201002 VALUES LESS THAN (201003),

-> PARTITION p201003 VALUES LESS THAN (201004),

-> PARTITION p201004 VALUES LESS THAN (201005),

-> PARTITION p201005 VALUES LESS THAN (201006),

-> PARTITION p201006 VALUES LESS THAN (201007),

-> PARTITION p201007 VALUES LESS THAN (201008),

-> PARTITION p201008 VALUES LESS THAN (201009),

-> PARTITION p201009 VALUES LESS THAN (201010),

-> PARTITION pcatchall VLAUES LESS THAN MAXVALUE

-> );

Query OK, 0 rows affected (0.20 sec)

新增分割槽

mysql> ALTER TABLE sale_data

-> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

Query OK, 0 rows affected (0.36 sec)

Records: 0 Duplicates: 0 Warnings: 0

刪除分割槽

--當刪除了一個分割槽，也同時刪除了該分割槽中所有的資料。

mysql> ALTER TABLE sale_data DROP PARTITION p201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0 Duplicates: 0 Warnings: 0

分割槽的合併

下面的SQL，將p201001 - p201009 合併為3個分割槽p2010Q1 - p2010Q3

mysql> ALTER TABLE sale_data

-> REORGANIZE PARTITION p201001,p201002,p201003,

-> p201004,p201005,p201006,

-> p201007,p201008,p201009 INTO

-> (

-> PARTITION p2010Q1 VALUES LESS THAN (201004),

-> PARTITION p2010Q2 VALUES LESS THAN (201007),

-> PARTITION p2010Q3 VALUES LESS THAN (201010)

-> );

Query OK, 0 rows affected (1.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

分割槽的拆分

下面的SQL，將p2010Q1 分割槽，拆分為s2009 與s2010 兩個分割槽

mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (

-> PARTITION s2009 VALUES LESS THAN (201001),

-> PARTITION s2010 VALUES LESS THAN (201004)

-> );

Query OK, 0 rows affected (0.36 sec)

Records: 0 Duplicates: 0 Warnings: 0

一個利用不同物理位置資料來源做分割槽的例子：

CREATE TABLE ts (id INT, purchased DATE)

ENGINE=innodb

PARTITION BY RANGE(YEAR(purchased))

SUBPARTITION BY HASH(id)

(

PARTITION p0 VALUES LESS THAN (1990)

(

SUBPARTITION s0 //在大的分割槽下又有小的分割槽

DATA DIRECTORY='/usr/local/mysql/data0' //資料來源

INDEX DIRECTORY='/usr/local/mysql/index0', //索引資料來源

SUBPARTITION s1

DATA DIRECTORY='/usr/local/mysql/data1'

INDEX DIRECTORY='/usr/local/mysql/index1'

),

PARTITION p1 VALUES LESS THAN (MAXVALUE)

(

SUBPARTITION s2

DATA DIRECTORY='/usr/local/mysql/data1'

INDEX DIRECTORY='/usr/local/mysql/index1',

SUBPARTITION s3

DATA DIRECTORY='/usr/local/mysql/data2'

INDEX DIRECTORY='/usr/local/mysql/index2'

)

);

分割槽索引的侷限：

1，所有分割槽都要使用同樣的引擎。

2，分割槽表的每一個唯一索引必須包含由分割槽函式引用的列。

3，mysql能避免查詢所有的分割槽，但仍然鎖定了所有分割槽。

4，分割槽函式能使用的函式和表示式有限，例如函式有上面的4種。

5，分割槽不支援外來鍵。

6，不能使用LOAD INDEX INTO CACHE

7，分割槽並不能總是改善效能，要進行效能評測。

例如可以使用expalin partitions 來檢視查詢語句是否使用分割槽過濾了資料：

mysql> explain partitions select * from fenqubiao where day

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | fenqubiao | p_2010,p_2011 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |

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

1 row in set (0.00 sec)

更多資訊請檢視