MySQL 指定各分區路徑

介紹

可以針對分區表的每個分區指定各自的存儲路徑，對于innodb存儲引擎的表只能指定數據路徑，因為數據和索引是存儲在一個文件當中，對于MYISAM存儲引擎可以分別指定數據文件和索引文件，一般也只有RANGE、LIST分區、sub子分區才有可能需要單獨指定各個分區的路徑，HASH和KEY分區的所有分區的路徑都是一樣。RANGE分區指定路徑和LIST分區是一樣的，這里就拿LIST分區來做講解。

一、MYISAM存儲引擎

CREATE TABLE th (id INT, adate DATE)
engine='MyISAM'PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx');

注意：MYISAM存儲引擎的數據文件和索引文件是分庫存儲所以可以為數據文件和索引文件定義各自的路徑，INNODB存儲引擎只能定義數據路徑。

二、INNODB存儲引擎

CREATE TABLE thex (id INT, adate DATE)
engine='InnoDB'PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/data/data',
    
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/data/data',
   
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/data/data',
    
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/data/data'
  );

指定路徑之后在原來的路徑中innodb生成了4個指向數據存儲的路徑文件，myisam生成了一個th.par文件指明該表是分區表，同時數據文件和索引文件指向了實際的存儲路徑。

三、子分區 

1.子分區

CREATE TABLE tb_sub_dir (id INT, purchased DATE)
ENGINE='MYISAM'
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) 
        (
            SUBPARTITION s0
                DATA DIRECTORY = '/data/data_sub1'
                INDEX DIRECTORY = '/data/idx_sub1',
            SUBPARTITION s1
                DATA DIRECTORY = '/data/data_sub1'
                INDEX DIRECTORY = '/data/idx_sub1'
        ),
        PARTITION p1 VALUES LESS THAN (2000) 
        (
            SUBPARTITION s2
                DATA DIRECTORY = '/data/data_sub2'
                INDEX DIRECTORY = '/data/idx_sub2',
            SUBPARTITION s3
                DATA DIRECTORY = '/data/data_sub2'
                INDEX DIRECTORY = '/data/idx_sub2'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE 
        (
            SUBPARTITION s4
                DATA DIRECTORY = '/data/data_sub3'
                INDEX DIRECTORY = '/data/idx_sub3',
            SUBPARTITION s5
                DATA DIRECTORY = '/data/data_sub3'
                INDEX DIRECTORY = '/data/idx_sub3'
        )
    );

2.子分區再分

CREATE TABLE tb_sub_dirnew (id INT, purchased DATE)
ENGINE='MYISAM'
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) 
        DATA DIRECTORY = '/data/data'
        INDEX DIRECTORY = '/data/idx'
        (
            SUBPARTITION s0
                DATA DIRECTORY = '/data/data_sub1'
                INDEX DIRECTORY = '/data/idx_sub1',
            SUBPARTITION s1
                DATA DIRECTORY = '/data/data_sub1'
                INDEX DIRECTORY = '/data/idx_sub1'
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        DATA DIRECTORY = '/data/data'
        INDEX DIRECTORY = '/data/idx'
        (
            SUBPARTITION s2
                DATA DIRECTORY = '/data/data_sub2'
                INDEX DIRECTORY = '/data/idx_sub2',
            SUBPARTITION s3
                DATA DIRECTORY = '/data/data_sub2'
                INDEX DIRECTORY = '/data/idx_sub2'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        DATA DIRECTORY = '/data/data'
        INDEX DIRECTORY = '/data/idx'
        (
            SUBPARTITION s4
                DATA DIRECTORY = '/data/data_sub3'
                INDEX DIRECTORY = '/data/idx_sub3',
            SUBPARTITION s5
                DATA DIRECTORY = '/data/data_sub3'
                INDEX DIRECTORY = '/data/idx_sub3'
        )
    );

也可以給個分區指定路徑后再給子分區指定路徑，但是這樣沒有意義，因為數據的存在都是由子分區決定的。

注意：

1.指定的路徑必須存在，否則分區無法創建成功

2.MYISAM存儲引擎的數據文件和索引文件是分庫存儲所以可以為數據文件和索引文件定義各自的路徑，INNODB存儲引擎只能定義數據路徑

參考： 

RANGE分區：h ttp://w ww.cnb logs.com/ch enmh/p/5627912.html

LIST分區：h ttp://w ww.cnbl ogs.com/che nmh/p/5643174.html

COLUMN分區： h ttp://w ww.c nblogs.com/c henmh/p/5630834.html

HASH分區：ht tp://w ww.cnb logs.com/ch enmh/p/5644496.html

KEY分區：htt p://ww  w.cnblo gs.com/ch enmh/p/5647210.html

子分區：htt p://w ww.cnblo gs.com/che nmh/p/5649447.html

分區建索引：h ttp://w ww.cn blogs.com/chenmh/p/5761995.html

分區介紹總結： ht tp://w ww.cnb logs.com/chen mh/p/5623474.html

總結

 通過給各個分區指定各自的磁盤可以有效的提高讀寫性能，在條件允許的情況下是一個不錯的方法。