InnoDB tables are stored in storage areas called tablespaces, which contain both data and indexes. In older MariaDB versions, by default, InnoDB used only one tablespace called the system tablespace. It also contains the change buffer, the doublewrite buffer, and by default, the undo log. This tablespace is physically stored in the ibdata file in the data directory of MariaDB (ibdata1, ibdata2, and so on). While the system tablespace still exists on the latest versions of MariaDB and can still contain all the tables, it is now possible to store each new table in a dedicated tablespace. Each tablespace created in this way is a separate file with the .ibd extension, which is located in a database subdirectory. This storage method is called the file-per-table mode. It is enabled by default since MariaDB 10.0.

The file-per-table mode is enabled by default. To enable or disable it, we can use the innodb_file_per_table system variable. When a table is created, InnoDB checks this variable to determine whether the new table should be created in the system tablespace or in a separate tablespace.

Let's see an example of how it works. Let's create some tables in MariaDB:

MariaDB [test_innodb]> CREATE TABLE haon (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.27 sec)
MariaDB [test_innodb]> SET @@global.innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_innodb]> CREATE TABLE do (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.46 sec)
MariaDB [test_innodb]> SET @@global.innodb_file_per_table = OFF;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_innodb]> CREATE TABLE tri (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.26 sec)

Now, let's check the files in the data directory and in the database subdirectory:

root@this:/usr/local/mysql# ls data/ibdata* -1
data/ibdata1
root@this:/usr/local/mysql# ls data/test_innodb -1
db.opt
do.frm
do.ibd
haon.frm
tri.frm

Of course, the system tablespace exists (the ibdata1 file). In the test_innodb subdirectory, we can see:

When the haon and tri tables were created, the innodb_file_per_table variable was off, and so, no separate tablespace has been created for them: they are stored in the system tablespace. When the do table was created, innodb_file_per_table was set to ON, and so, it is stored in a file called do.ibd.

MariaDB [test_innodb2]> SET @@global.innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_innodb2]> SET @@global.innodb_file_format = 'Antelope';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_innodb2]> CREATE TABLE um (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.37 sec)
MariaDB [test_innodb2]> SET @@global.innodb_file_format = 'Barracuda';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_innodb2]> CREATE TABLE dois (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
Query OK, 0 rows affected (0.39 sec)

MariaDB [test_innodb2]> SELECT NAME, FILE_FORMAT, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME LIKE 'test_innodb2/%';
+-------------------+-------------+----------------------+
| NAME              | FILE_FORMAT | ROW_FORMAT           |
+-------------------+-------------+----------------------+
| test_innodb2/um   | Antelope    | Compact or Redundant |
| test_innodb2/dois | Barracuda   | Dynamic              |
+-------------------+-------------+----------------------+
2 rows in set (0.00 sec)