InnoDB Storage Engine
IntermediateInnoDB is MySQL's default ACID-compliant engine providing row-level locking, MVCC, foreign keys, crash recovery via redo/undo logs, and clustered primary key index.
Overview
InnoDB is MySQL's default and most important storage engine. It is the only engine that provides full ACID compliance: atomicity (redo/undo logs), consistency (constraints and foreign keys), isolation (MVCC + locking), and durability (fsync to redo log before commit). InnoDB uses a clustered primary key index — the B-Tree leaf nodes store the full row data, not just the PK value. Secondary indexes store the PK value as the row locator, so secondary index lookups require two B-Tree lookups (secondary → PK → row). Understanding InnoDB internals — the buffer pool, redo/undo logs, clustered index, MVCC, and row-level locking — is essential for writing high-performance, correct MySQL applications.
Clustered Primary Key Index
InnoDB organises table data in a B-Tree sorted by the primary key — this is the clustered index. The leaf nodes of this B-Tree contain the full row data (all columns). This means:
1. **PK lookups are single B-Tree traversal** — extremely fast. 2. **Sequential inserts by PK are fast** — new rows append to the rightmost leaf. 3. **Random inserts by PK are slow** — UUID primary keys cause random B-Tree page splits (page fragmentation). 4. **Secondary indexes are larger** — every secondary index B-Tree leaf stores the PK value to refer back to the clustered index.
Best practice: use a monotonically increasing PK (AUTO_INCREMENT INT/BIGINT). Avoid UUID primary keys in high-write tables.
-- Clustered index — row data lives in the B-Tree leaf nodes
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- clustered index key
customer_id INT NOT NULL,
status VARCHAR(20),
total DECIMAL(10,2),
created_at DATETIME,
INDEX idx_customer (customer_id) -- secondary index
);
-- PK lookup: 1 B-Tree traversal → leaf contains full row
SELECT * FROM orders WHERE id = 12345;
-- Secondary index lookup: 2 B-Tree traversals
-- Step 1: traverse idx_customer B-Tree → find id=12345 for customer_id=99
-- Step 2: traverse clustered index with id=12345 → get full row
SELECT * FROM orders WHERE customer_id = 99;
-- Covering index avoids step 2 (secondary lookup) entirely
-- Add all needed columns to the index:
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status, total);
-- Query can now be answered from index alone (Extra: Using index in EXPLAIN)
SELECT customer_id, status, total FROM orders WHERE customer_id = 99;Buffer Pool — The Most Important InnoDB Tuning Parameter
The InnoDB buffer pool is an in-memory cache for data pages (16 KB each) and index pages. Every read and write goes through the buffer pool — MySQL reads a page from disk into the buffer pool, then serves reads from memory and performs writes in-memory before flushing to disk. The buffer pool size is the single most impactful InnoDB configuration setting: a larger buffer pool means fewer disk reads. Rule of thumb: set innodb_buffer_pool_size to 70–80% of available RAM on a dedicated DB server.
# my.cnf — buffer pool configuration
[mysqld]
# Set to 70-80% of available RAM on dedicated DB server
innodb_buffer_pool_size = 16G # for 20GB RAM server
innodb_buffer_pool_instances = 8 # parallel access (1 per GB, min 8)
# Check buffer pool hit ratio — should be > 99%
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests: total logical reads (from pool)
-- Innodb_buffer_pool_reads: disk reads (cache miss)
-- Hit ratio = 1 - (disk_reads / logical_reads)
-- Compute hit ratio:
SELECT (1 - (
(SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads') /
(SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_read_requests')
)) * 100 AS buffer_pool_hit_ratio_pct;
-- Target: > 99%; if lower, increase innodb_buffer_pool_sizeMVCC and Row-Level Locking
InnoDB achieves high concurrency through MVCC (Multi-Version Concurrency Control) and row-level locking. MVCC allows readers to see a consistent snapshot without blocking writers — each transaction gets a read view (snapshot ID) and reads the version of a row that was visible as of that snapshot. Writers use undo logs to reconstruct historical versions for readers.
InnoDB uses row-level locking (not table-level) for writes. Three lock types: shared locks (S) for reads that need to prevent concurrent writes, exclusive locks (X) for writes, and intention locks (IS/IX) at the table level to signal intention to lock rows. Deadlocks can occur when two transactions each hold a lock the other needs; InnoDB detects and resolves them automatically by rolling back the transaction with fewer rows locked.
-- Row-level locking examples
-- Shared lock (S): read and prevent concurrent writes
SELECT * FROM orders WHERE id = 123 FOR SHARE;
-- Exclusive lock (X): read and lock for update
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- Other transactions cannot read (FOR SHARE) or write this row until commit
-- Gap lock: prevents phantom inserts into a range
SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE;
-- Gap locked: no other transaction can INSERT id 101-199
-- Deadlock scenario
-- Transaction A: locks order 1, then tries to lock order 2
-- Transaction B: locks order 2, then tries to lock order 1
-- → InnoDB detects cycle, rolls back the smaller transaction
-- → Always acquire locks in consistent order to prevent deadlocks
-- Check current locks and waits
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- Or: SHOW ENGINE INNODB STATUSG ← shows latest deadlockKey Points to Remember
- 1InnoDB uses a clustered primary key B-Tree — leaf nodes contain full row data; use AUTO_INCREMENT INT/BIGINT, not UUID.
- 2Secondary indexes store the PK value as a row pointer — secondary index lookups require two B-Tree traversals unless covered by a covering index.
- 3innodb_buffer_pool_size is the #1 tuning parameter — target 70–80% of RAM; monitor hit ratio (should be > 99%).
- 4MVCC allows concurrent reads and writes without blocking each other by maintaining multiple versions of each row in undo logs.
- 5InnoDB uses row-level locking — shared (S), exclusive (X), and gap locks; always acquire locks in consistent order to avoid deadlocks.
- 6InnoDB automatically detects and resolves deadlocks by rolling back one transaction; the application must retry the rolled-back transaction.
Interview Questions
Sign in to ask AriaWhat is a clustered index in InnoDB and how does it differ from a secondary index?
Why are UUID primary keys bad for performance in InnoDB?
What is the InnoDB buffer pool and what percentage of RAM should you allocate to it?
How does MVCC allow readers and writers to coexist without blocking each other?
How does InnoDB handle deadlocks and what should your application do when it detects a deadlock rollback?
Ask Aria about InnoDB Storage Engine
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.