mysql使用教程之分區(qū)表的使用方法(刪除分區(qū)表)

更新時間：2013年12月27日 15:46:16 作者：

mysql分區(qū)表使用方法,新增分區(qū)、刪除分區(qū)、分區(qū)的合并、分區(qū)的拆分等使用方法

MySQL使用分區(qū)表的好處：

1，可以把一些歸類的數(shù)據(jù)放在一個分區(qū)中，可以減少服務(wù)器檢查數(shù)據(jù)的數(shù)量加快查詢。
2，方便維護，通過刪除分區(qū)來刪除老的數(shù)據(jù)。
3，分區(qū)數(shù)據(jù)可以被分布到不同的物理位置，可以做分布式有效利用多個硬盤驅(qū)動器。

MySQL可以建立四種分區(qū)類型的分區(qū)：

RANGE 分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。 www.dbjr.com.cn

HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

一般用得多的是range分區(qū)和list分區(qū)。
RANGE分區(qū)
這里以一個銷售的業(yè)務(wù)來做測試
銷售表有日期/商品/銷售額三個字段
測試數(shù)據(jù)從2010年1月1日至2010年9月31日
以“月”為單位進行分區(qū)
初期分區(qū)定義
首先需要查看，當(dāng)前數(shù)據(jù)庫是否支持分區(qū)

復(fù)制代碼代碼如下:

mysql>SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |

+-------------------+-------+
1 row in set (0.03 sec)

創(chuàng)建分區(qū)表，按照年月的方式分區(qū)。

復(fù)制代碼代碼如下:

mysql> CREATE TABLE sale_data (
    ->   sale_date  DATETIME NOT NULL,

    ->   sale_item  VARCHAR(2) NOT NULL ,

    ->   sale_money DECIMAL(10,2) NOT NULL

    -> )  www.dbjr.com.cn  

    -> 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)

新增分區(qū)

復(fù)制代碼代碼如下:

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

刪除分區(qū)

復(fù)制代碼代碼如下:

--當(dāng)刪除了一個分區(qū)，也同時刪除了該分區(qū)中所有的數(shù)據(jù)。
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)  www.dbjr.com.cn  
Records: 0  Duplicates: 0  Warnings: 0

分區(qū)的合并

下面的SQL，將p201001 - p201009 合并為3個分區(qū)p2010Q1 - p2010Q3

復(fù)制代碼代碼如下:

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

分區(qū)的拆分

下面的SQL，將p2010Q1 分區(qū)，拆分為s2009 與s2010 兩個分區(qū)

復(fù)制代碼代碼如下:

mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (

    ->     PARTITION s2009 VALUES LESS THAN (201001),
             www.dbjr.com.cn  
    ->     PARTITION s2010 VALUES LESS THAN (201004)

    -> );

Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

一個利用不同物理位置數(shù)據(jù)源做分區(qū)的例子：

復(fù)制代碼代碼如下:

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                  //在大的分區(qū)下又有小的分區(qū)
            DATA DIRECTORY='/usr/local/mysql/data0'      //數(shù)據(jù)源
            INDEX DIRECTORY='/usr/local/mysql/index0',   //索引數(shù)據(jù)源
            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'
        ) 
    ); 

分區(qū)索引的局限：
1，所有分區(qū)都要使用同樣的引擎。
2，分區(qū)表的每一個唯一索引必須包含由分區(qū)函數(shù)引用的列。
3，mysql能避免查詢所有的分區(qū)，但仍然鎖定了所有分區(qū)。
4，分區(qū)函數(shù)能使用的函數(shù)和表達(dá)式有限，例如函數(shù)有上面的4種。
5，分區(qū)不支持外鍵。 www.dbjr.com.cn
6，不能使用LOAD INDEX INTO CACHE
7，分區(qū)并不能總是改善性能，要進行性能評測。
例如可以使用expalin partitions 來查看查詢語句是否使用分區(qū)過濾了數(shù)據(jù)：

復(fù)制代碼代碼如下:

mysql> explain partitions select * from fenqubiao where day<'2011-09-12';
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| 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)

您可能感興趣的文章: