InnoDB tables have the following benefits:
If the server unexpectedly exits because of a hardware or software issue, regardless of
what was happening in the database at the time, you don't need to do anything special
after restarting the database. InnoDB crash recovery automatically finalizes changes that
were committed before the time of the crash, and undoes changes that were in process but
not committed, permitting you to restart and continue from where you left off. See Section
17.18.2, "InnoDB Recovery".
The InnoDB storage engine maintains its own buffer pool that caches table and index data
in main memory as data is accessed. Frequently used data is processed directly from
memory. This cache applies to many types of information and speeds up processing. On
dedicated database servers, up to 80% of physical memory is often assigned to the buffer
pool. See Section 17.5.1, "Buffer Pool".
If you split up related data into different tables, you can set up foreign keys that
enforce referential integrity. See Section 15.1.20.5, "FOREIGN KEY Constraints".
If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the
bogus data before you use it. The innodb_checksum_algorithm variable defines the checksum
algorithm used by InnoDB.
When you design a database with appropriate primary key columns for each table, operations
involving those columns are automatically optimized. It is very fast to reference the
primary key columns in WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join
operations. See Section 17.6.2.1, "Clustered and Secondary Indexes".
Inserts, updates, and deletes are optimized by an automatic mechanism called change
buffering. InnoDB not only allows concurrent read and write access to the same table, it
caches changed data to streamline disk I/O. See Section 17.5.2, "Change Buffer".
Performance benefits are not limited to large tables with long-running queries. When the
same rows are accessed over and over from a table, the Adaptive Hash Index takes over to
make these lookups even faster, as if they came out of a hash table. See Section 17.5.3,
"Adaptive Hash Index".
You can compress tables and associated indexes. See Section 17.9, "InnoDB Table and Page
Compression".
You can encrypt your data. See Section 17.13, "InnoDB Data-at-Rest Encryption".
You can create and drop indexes and perform other DDL operations with much less impact on
performance and availability. See Section 17.12.1, "Online DDL Operations".
Truncating a file-per-table tablespace is very fast and can free up disk space for the
operating system to reuse rather than only InnoDB. See Section 17.6.3.2, "File-Per-Table
Tablespaces".
The storage layout for table data is more efficient for BLOB and long text fields, with
the DYNAMIC row format. See Section 17.10, "InnoDB Row Formats".
You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA
tables. See Section 17.15, "InnoDB INFORMATION_SCHEMA Tables".
You can monitor the performance details of the storage engine by querying Performance
Schema tables. See Section 17.16, "InnoDB Integration with MySQL Performance Schema".
You can mix InnoDB tables with tables from other MySQL storage engines, even within the
same statement. For example, you can use a join operation to combine data from InnoDB and
MEMORY tables in a single query.
InnoDB has been designed for CPU efficiency and maximum performance when processing large
data volumes.
InnoDB tables can handle large quantities of data, even on operating systems where file
size is limited to 2GB.