What is a tablespace and why is it used?

A data file that can hold data for one or more InnoDB tables and associated indexes.

There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are,

a. System tablespaceb. File per tablespacec. General tablespace

System tablespace contains,

1. InnoDB data dictionary.
2. DoubleWrite Buffer.
3. Change buffer
4. Undo Logs.

Apart from this it also contains,

1. Tables &
2. Index data

Associated file is .idbdata1

The innodb_file_per_table option, which is enabled by default in MySQL 5.6 and higher, allows tables to be created in file-per-table tablespaces, with a separate data file for each table. Enabling the innodb_file_per_table option makes available other MySQL features such as table compression and transportable tablespaces.

Associated file is .idbd

InnoDB introduced general tablespaces in MySQL 5.7.6. General tablespaces are shared tablespaces created using CREATE TABLESPACE syntax. They can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.

MySQL Innodb TableSpace is a location where the data resides in the disk called data directory (By default "system tablespace"). Example:

"/var/lib/mysql"

From MySQL version 5.6.6, an user can create and specify the tablespace they want to store the data which enables throughput of data manipulation and recovery process. InnoDB's file-per-table feature offers each tables to have separate .ibd data & index files which represents an individual general tablespace. So that each table in a database can point various locations of data directories.

Ex :

/home/{user}/test/data/{dbName}/{tableName}.ibd

/home/{user}/work/data/{dbName}/{tableName}.ibd

For more about the file-per-table tablespace, refer this mysql documentation.

By default, InnoDB contains only one tablespace called the system tablespace whose identifier is 0. More tablespaces can be created indirectly using the innodb_file_per_table configuration parameter. A tablespace consists of a chain of files. The size of the files does not have to be divisible by the database block size, because we may just leave the last incomplete block unused. When a new file is appended to the tablespace, the maximum size of the file is also specified. At the moment, we think that it is best to extend the file to its maximum size already at the creation of the file, because then we can avoid dynamically extending the file when more space is needed for the tablespace. Data files are dynamically extended, but redo log files are pre-allocated. Also, as already mentioned earlier, only the system tablespace can have more than one data file. It is also clearly mentioned that even though the tablespace can have multiple files, they are thought of as one single large file concatenated together. So the order of files within the tablespace is important.

From https://blogs.oracle.com/mysqlinnodb/entry/data_organization_in_innodb