Shulou(Shulou.com)06/01 Report--

Introduction

You can specify your own storage path for each partition of the partition table. For innodb storage engine tables, you can only specify the data path, because the data and index are stored in one file. For MYISAM storage engine, you can specify data file and index file respectively. Generally, only RANGE, LIST partition and subpartition need to specify the path of each partition separately. All partitions of HASH and KEY partition have the same path. The specified path of the RANGE partition is the same as that of the LIST partition. Here we will use the LIST partition to explain.

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 libraries, so you can define their own paths for the data files and index files, while 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 generates four path files pointing to the data store in the original path, and myisam generates a th.par file indicating that the table is a partition table, while the data file and index file point to the actual storage path.

3. Sub-partition

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

two。 Sub-partition

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 assign a path to a partition and then a path to a subpartition, but this makes no sense because the existence of the data is determined by the subpartition.

Note:

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

The data files and index files of the 2.MYISAM storage engine are stored in separate libraries, so you can define their own paths for the data files and index files, while the INNODB storage engine can only define data paths.

Reference:

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

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

COLUMN partition: h ttp://w ww.c nblogs.com/c henmh/p/5630834.html

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

KEY partition: htt p://ww w.cnblo gs.com/ch enmh/p/5647210.html

Sub-partition: htt pvvax ww.cnblo gs.com/che nmh/p/5649447.html

Partition indexing: h ttp://w ww.cn blogs.com/chenmh/p/5761995.html

Summary of zoning introduction: ht tp://w ww.cnb logs.com/chen mh/p/5623474.html

Summary

By assigning each partition its own disk, it can effectively improve read and write performance, which is a good method when conditions permit.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.