Adding to the excellent answer provided by @BhupeshPant
Storage of InnoDB is divided into tablespaces. A Tablespace is a logical structure associated with multiple data files (objects). Each tablespace contains pages(blocks), extents and segments.
Pages - A smallest piece of data for InnoDB also known as blocks. A page can hold one or more rows depending upon row size.
In general, every table has it's own tablespace that is associated with one datafile
The following command displays a full list of tablespace and the corresponding datafile -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
To get a more proper under standing create a sample database - database1
CREATE DATABASE database1;
USE database1;
Now create a sample table table1 -
CREATE TABLE table1 (
-> col1 INT
-> );
Now we can see that the corresponding tablespace and datafile has also been created -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+--------------------------+--------------------------+
| TABLESPACE_NAME | FILE_NAME |
+--------------------------+--------------------------+
|database1/table1 | ./database1/table1.ibd |
+--------------------------+--------------------------+
Here the .ibd extension signifies Innodb datafile
Now create another sample table table2 -
CREATE TABLE table2 (
-> col1 INT
-> );
We can again see that the corresponding tablespace and datafile has been created -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+---------------------------+--------------------------+
| TABLESPACE_NAME | FILE_NAME |
+---------------------------+--------------------------+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
+---------------------------+--------------------------+
Here the system is creating a default name for the tablespace. These system generated tablespaces are called - Single-Table tablespaces
Single-Table tablespaces are created by system only when innodb_file_per_table is ON
It can be checked by -
SHOW VARIABLES LIKE "innodb_file_per_table";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
By default, the values is ON. It can be turned off by -
SET GLOBAL innodb_file_per_table = OFF;
SHOW VARIABLES LIKE "innodb_file_per_table";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
If the value is turned off, no Single-Table tablespaces are created.
To demonstrate this we again create a sample table -
CREATE TABLE table10 (
-> col1 INT
-> );
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+---------------------------+--------------------------+
| TABLESPACE_NAME | FILE_NAME |
+---------------------------+--------------------------+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
+---------------------------+--------------------------+
Thus, we can see that no tablespace named - database1/table10 gets created
In such cases the table gets stored in innodb_system tablespace;
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES WHERE FILE_NAME LIKE "%ibdata1";
+-----------------+-----------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+-----------+
| innodb_system | ./ibdata1 |
+-----------------+-----------+
But this is not a good practice as it gives more burden to system tablespace which results in performance related issue.
In case of system generated tablespace, on dropping the table the tablespace also gets dropped automatically.
There is another type of tablespace called - General Tablespaces where we can create a custom name for tablespace.
To do it we need to issue the following command -
CREATE TABLESPACE tablespace1 ADD DATAFILE '/var/lib/mysql/tablespace1.ibd' engine = Innodb;
We can check that the tablespace and corresponding datafile has been created -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+---------------------------+--------------------------------+
| TABLESPACE_NAME | FILE_NAME |
+---------------------------+--------------------------------+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
| tablespace1 | /var/lib/mysql/tablespace1.ibd |
+---------------------------+--------------------------------+
Here all tablespaces are created in Mysql datadir which by default is /var/lib/mysql/
The datadir value can be checked by the following command -
SELECT @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
In MySQL only one datafile can be added per tablespace. But in Oracle or SQL Server multiple datafiles can be added to a tablespace.
To create a table in the tablespace - tablespace1 we need to issue the command -
CREATE TABLE table3 (
-> col1 INT
-> )
-> TABLESPACE = tablespace1;
In case of general tablespaces, multiple tables can be created inside one tablespace -
CREATE TABLE table4 (
-> col1 INT
-> )
-> TABLESPACE = tablespace1;
While creating a tablespace, the datafile location may not be stated exclusively. The following command will also work -
CREATE TABLESPACE tablespace2 ADD DATAFILE 'tablespace2.ibd' engine = Innodb;
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+---------------------------+--------------------------------+
| TABLESPACE_NAME | FILE_NAME |
+---------------------------+--------------------------------+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
| tablespace1 | /var/lib/mysql/tablespace1.ibd |
| tablespace2 | ./tablespace2.ibd |
+---------------------------+--------------------------------+
Here by default all tablespaces get created in the datadir directory
Tablespace creates a page size of default value. However we can also set a page size of our own while creating a tablespace. However, the size must be within default page size, else there will be error. The default page size can be set during installation of MySQL server.
Permitted page sizes are - 4 kb, 8 kb, 16 kb, 32 kb, 64 kb.
In general during installation, 16 kb is set as default page size.
The following command is used to view the default page size -
SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
The command to create a tablespace with a custom page size is -
CREATE TABLESPACE tablespace3_8kb ADD DATAFILE '/var/lib//mysql/tablespace3_8kb.ibd' FILE_BLOCK_SIZE = 8192 ENGINE = Innodb;
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+---------------------------+-------------------------------------+
| TABLESPACE_NAME | FILE_NAME |
+---------------------------+-------------------------------------+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
| tablespace1 | /var/lib/mysql/tablespace1.ibd |
| tablespace2 | ./tablespace2.ibd |
| tablespace3_8kb | /var/lib//mysql/tablespace3_8kb.ibd |
+---------------------------+-------------------------------------+
To store a table in a tablespace with user-defined page size a different syntax is needed -
CREATE TABLE table5 (
-> col1 INT
-> )
-> TABLESPACE = tablespace3_8kb
-> KEY_BLOCK_SIZE = 8;
Unlike system generated tablespace, general tablespace does not get dropped automatically if we drop the tables, the tablespaces need to be dropped explicitly.
The command is -
DROP TABLESPACE tablespace_name;
