MySQL specifies the path of each partition and mysql Partition

MySQL specifies the path of each partition and mysql Partition
Introduction

You can specify the storage path for each partition of a partitioned table. For an innodb Storage engine table, you can only specify the data path, because the data and indexes are stored in one file, for the MYISAM storage engine, you can specify data files and index files respectively. Generally, only the RANGE, LIST partitions, and sub-partitions can be specified separately, the paths of all HASH and KEY partitions are the same. The specified path of the RANGE partition is the same as that of the LIST partition. Here we will explain the LIST partition.

 

I. 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 sub-database stores. Therefore, you can define their respective paths for data files and index files. The INNODB Storage engine can only define data paths.

Ii. 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 generates four path files pointing to the data storage in the original path, and myisam generates a th. the par file indicates that the table is a partition table, and the data file and index file point to the actual storage path.

Iii. subpartitions

1. subpartition

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. subpartition re-partitioning

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 does not make sense, 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. MYISAM storage engine's data files and index files are database-based storage. Therefore, you can define their respective paths for data files and index files. INNODB Storage engine can only define data paths.

 

Refer:

RANGE partitioning: http://www.cnblogs.com/chenmh/p/5627912.html

LIST partition: http://www.cnblogs.com/chenmh/p/5643174.html

COLUMN partition: http://www.cnblogs.com/chenmh/p/5630834.html

HASH partition: http://www.cnblogs.com/chenmh/p/5644496.html

KEY partition: http://www.cnblogs.com/chenmh/p/5647210.html

Subpartition: http://www.cnblogs.com/chenmh/p/5649447.html

Partitioning index: http://www.cnblogs.com/chenmh/p/5761995.html

Partition Summary: http://www.cnblogs.com/chenmh/p/5623474.html

Summary

Specifying disks for each partition can effectively improve the read/write performance. This is a good method when conditions permit.

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion