MySQL InnoDB Storage Engine

Overview

In this tutorial, we will learn about InnoDB Storage Engine in MySQL. The support for the InnoDB storage engine is a standard feature in MYSQL database.

InnoDB Features

The InnoDB storage engine manages tables that have the following characteristics:

The InnoDB table is represented on disk by an *.ibd file format in the database directory using InnoDB tablespace. A tablespace is a logical single storage area made up of one or more files or partitions on the disk. By default, it uses a single tablespace that is shared by all database tables. Tablespace storage format is portable.

We can configure InnoDB to create each table with its own tablespace.

mysql> SHOW VARIABLES LIKE ‘%innodb%’;

MySQL InnoDB Variables

InnoDB storage engine supports foreign keys and referential integrity, including cascaded deletes and updates,multi-versioning concurrency control (MVCC) and Row-level locking.

This InnoDB storage engine supports transactions, with COMMIT and ROLLBACK. It also provides full ACID compliance. This storage engine manages a set of InnoDB-specific log files that contain information about ongoing transactions. As a client performs a transaction, the changes that it makes are held in the log file. The more recent log contents are cached in memory. Normally, the log buffer is written and flushed to log files on disk at transaction commit time. We can also configure this to take place periodically.

It also provides auto-recovery after a crash of the MySQL server or the host on which the server runs. If a crash occurs while the tables are being modified, the log files are used for auto-recovery: when the MySQL server restarts, it reapplies the changes recorded in the redo logs, to ensure that the tables reflects all committed transactions.

ENGINE=InnoDB option

When we create a table, we can choose what storage engine to use. We can use the ENGINE=InnoDB option in the CREATE TABLE statement to create table using the InnoDB engine.

For example, the following statement creates table using the option:

mysql> CREATE TABLE demotable (
-> id INT NOT NULL,
-> name CHAR(40) NOT NULL
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

ENGINE Option InnoDB Table

When we create a table WITHOUT using the ENGINE option to specify a storage engine,
the MySQL server creates the table using the default storage engine.

—