Detailed introduction to MySQL specifying the path of each partition

You can specify its own storage path for each partition of the partition table. For the table of the innodb storage engine, you can only specify the data path, because the data and index are stored in a file. The MYISAM storage engine can specify data files and index files separately. Generally, only RANGE, LIST partitions, and sub subpartitions may need to specify the paths of each partition separately. The paths of all partitions of HASH and KEY partitions are the same. The same one. The specified path for the RANGE partition is the same as the LIST partition. Here we will use the LIST partition for explanation. 1. MYISAM storage engine

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

Note: The data files and index files of the MYISAM storage engine are stored in separate databases, so you can define separate databases for the data files and index files. Path, the INNODB storage engine can only define data paths.

2. INNODB storage engine

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

After specifying the path, innodb generated 4 path files pointing to the data storage in the original path. Myisam generates a th.par file indicating that the table is a partitioned table, and the data files and index files point to the actual storage path.

3. Sub-partition

1. Sub-partition

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. Divide sub-partitions

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

You can also specify a path for a partition and then specify a path for the sub-partition, but this is meaningless because the existence of data is determined by the sub-partition. .

Note:

1. The specified path must exist, otherwise the partition cannot be created successfully

2. Data files of the MYISAM storage engine and index files are stored in separate databases, so separate paths can be defined for data files and index files. The INNODB storage engine can only define data paths

The above is the detailed content of Detailed introduction to MySQL specifying the path of each partition. For more information, please follow other related articles on the PHP Chinese website!