MySQL分區表的使用

MySQL使用分區表的好處：

1，可以把一些歸類的數據放在一個分區中，可以減少服務器檢查數據的數量加快查詢。 2，方便維護，通過刪除分區來刪除老的數據。 3，分區數據可以被分布到不同的物理位置，可以做分布式有效利用多個硬盤驅動器。
MySQL可以建立四種分區類型的分區：           RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。 ·         LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。  www.2cto.com   ·         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          sale_date      
        sale_item  ()    
        sale_money (,)  
  
       
      PARTITION  RANGE ((sale_date)  
        PARTITION p201001  LESS THAN (  
        PARTITION p201002  LESS THAN (  
        PARTITION p201003  LESS THAN (  
        PARTITION p201004  LESS THAN (  
        PARTITION p201005  LESS THAN (  
        PARTITION p201006  LESS THAN (  
        PARTITION p201007  LESS THAN (  
        PARTITION p201008  LESS THAN (  
        PARTITION p201009  LESS THAN (  
            
 Query OK,  rows affected ( sec)

新增分區

mysql      PARTITION (PARTITION p201010  LESS THAN ( rows affected (  Duplicates:   Warnings: 

刪除分區

--當刪除了一個分區，也同時刪除了該分區中所有的數據。
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)  www.2cto.com  
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),
             www.2cto.com  
    ->     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，分區不支持外鍵。  www.2cto.com   6，不能使用LOAD INDEX INTO CACHE 7，分區並不能總是改善性能，要進行性能評測。 例如可以使用expalin partitions 來查看查詢語句是否使用分區過濾了數據： 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)