14.1.1 Benefits of Using InnoDB Tables
You may find
InnoDB tables beneficial for the following reasons:
If your server crashes 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.
InnoDBcrash recovery automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.
InnoDBstorage 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.
If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and the bad data gets kicked out automatically.
If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
When you design your 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
GROUP BYclauses, and join operations.
Inserts, updates, and deletes are optimized by an automatic mechanism called change buffering.
InnoDBnot only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.
Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.
You can compress tables and associated indexes.
You can create and drop indexes with much less impact on performance and availability.
Truncating a file-per-table tablespace is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only
You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.
You can monitor the performance details of the storage engine by querying Performance Schema tables.
You can freely mix
InnoDBtables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from
MEMORYtables in a single query.
InnoDBhas been designed for CPU efficiency and maximum performance when processing large data volumes.
InnoDBtables can handle large quantities of data, even on operating systems where file size is limited to 2GB.
InnoDB-specific tuning techniques you can apply in your application code, see Section 8.5, “Optimizing for InnoDB Tables”.