詳細介紹MySQL指定各分區路徑

迷茫

發布： 2017-03-26 14:08:57

原創

1344 人瀏覽過

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

一、MYISAM儲存引擎

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

登入後複製

注意：MYISAM儲存引擎的資料檔案和索引檔案是分庫儲存所以可以為資料檔案和索引檔案定義各自的路徑，INNODB儲存引擎只能定義資料路徑。

二、INNODB儲存引擎

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

登入後複製

#指定路徑之後在原來的路徑中innodb產生了4個指向資料儲存的路徑文件， myisam產生了一個th.par檔案指明該表是分區表，同時資料檔案和索引檔案指向了實際的儲存路徑。

三、子分割區

1.子分割區

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

登入後複製

# #2.子分割區再分

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

登入後複製

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

注意：

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

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

以上是詳細介紹MySQL指定各分區路徑的詳細內容。更多資訊請關注PHP中文網其他相關文章！