MENU
InnoDB
Balancing high reliability and performance, InnoDB is MySQL's default storage engine. See Storage Engines for how it compares to the others.Advantages of InnoDB include:
- DML operations follow the ACID (atomicity, consistency, isolation, durability) model, with commit, rollback, and crash-recovery capabilities. See Transactions.
- Row-level locking and Oracle-style consistent reads increase performance and multi-user concurrency. See Locks.
- Tables arrange data on disk to optimize queries based on primary keys.
- FOREIGN KEY constraints are supported.
- InnoDB tables can be freely mixed with tables from other storage engines, even within the same statement – for example, joining an InnoDB table with a MEMORY table in a single query.
- InnoDB is designed for maximum performance when processing large data volumes.
- InnoDB maintains its own buffer pool for caching data and indexes in main memory.
- Tables can handle large amounts of data even on operating systems where file size is limited to 2GB.
| Feature | InnoDB |
| Storage limits | 64TB |
| Transactions | Yes |
| Locking granularity | Row |
| MVCC | Yes |
| Geospatial data type support | Yes |
| Geospatial indexing support | Yes |
| B-tree indexes | Yes |
| T-tree indexes | No |
| Hash indexes | No (internal adaptive hash index only, not user-visible) |
| Full-text search indexes | Yes |
| Clustered indexes | Yes |
| Data caches | Yes |
| Index caches | Yes |
| Compressed data | Yes |
| Encrypted data | Yes |
| Cluster database support | No |
| Replication support | Yes |
| Foreign key support | Yes |
| Backup / point-in-time recovery | Yes |
| Query cache support | Yes |
| Update statistics for data dictionary | Yes |
-- InnoDB is the default engine, but it can be specified explicitly
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE = InnoDB;
-- ACID transaction using InnoDB's row-level locking and MVCC
START TRANSACTION;
UPDATE orders SET total = total - 10.00 WHERE id = 1;
UPDATE orders SET total = total + 10.00 WHERE id = 2;
COMMIT;
-- Detailed engine status, including buffer pool and lock information
SHOW ENGINE INNODB STATUS;