If @@innodb_file_per_table
is set to OFF
when the table is partitioned, all partitions are stored in the InnoDB system tablespace, but in different areas. If this variable is set to 1
, each partition is stored in a different file.
Storage engines that store data and indexes in separate files, such as Aria and MyISAM, create a data
file and an index
file for each partition.
The extension of partition data
or index
files is the same that is used for unpartitioned tables. The basename of the files is the name of the table, plus #P#
, plus the name of the partition. So, the name pattern is as follows:
<table_name>#P#<partition_name>.<extension>
Like all tables, partitioned tables have a .frm
file that contains the table definition. They also have a file with partitions' definitions, which has a .par
extension.
For example, let's suppose we have an InnoDB table called employee
, with two partitions called p0
and p1
. We will have the following files:
employee.frm employee.par employee#P#p0.ibd employee#P#p1.ibd
If it is an Aria table, we will have the following files:
employee.frm employee.par employee#P#p0.MAD employee#P#p0.MAI employee#P#p1.MAD employee#P#p1.MAI
For subpartitioned tables, each subpartition has a separate file. The basename of these files is the name of the table, plus the partition suffix, plus a subpartition suffix. For example, let the previous InnoDB employee
table have two subpartitions per partition, called s0
, s1
, s2
, and s3
. We will see the following files:
employee.frm employee.par employee#P#p0#SP#s0.ibd employee#P#p0#SP#s1.ibd employee#P#p1#SP#s2.ibd employee#P#p1#SP#s3.ibd
When setting the value of @@table_open_cache
(explained in Chapter 6, Caches), we must remember that each partition will require a separate file handle.
By default, partitions' files are stored in the database path in the data
directory. It is possible to specify a path for each partition's data
file and index
file. By doing so, we can distribute the partitions through several disks, reducing the overhead of disks input and output. This feature is very useful, and it generally is the main reason why we may want to use partitioning. The syntax to do this is the following:
CREATE TABLE employee ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE = InnoDB PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (10000) DATA DIRECTORY '/disk_x', PARTITION p1 VALUES LESS THAN MAXVALUE );
In this example, a database
directory will be created on disk_x
if it does not exist, and it will contain the data
file for partition p0
. Even if all partitions are stored on nonstandard paths, the data
directory will still contain the .par
file.