TopolearnUnderstanding the clustered index explains most InnoDB performance advice about keys and secondary indexes.
MySQL supports pluggable storage engines; InnoDB is the default and the one to use. It provides transactions, row-level locking, foreign keys, MVCC and crash recovery. (MyISAM, the old default, has none of these and should not be used for application data.)
The most important InnoDB fact: every table is a B-tree index on its primary key — a clustered index. Rows are stored in the leaf pages of that tree, in primary-key order. Consequences:
- Lookups and range scans by primary key are very fast; they find the row directly.
- Secondary indexes store the indexed columns plus the primary key value, not a row pointer. A lookup through a secondary index finds the PK, then searches the clustered index again. A wide primary key (say a 36-character UUID string) is therefore copied into every secondary index.
- Inserting in increasing PK order (AUTO_INCREMENT) appends to the end of the tree. Random keys like UUIDv4 cause page splits and a bigger working set; if you need UUIDs, store them as
BINARY(16)and prefer time-ordered ones (UUID_TO_BIN(UUID(), 1)reorders MySQL's time-based UUID for this). - If you don't define a primary key, InnoDB uses the first
NOT NULLunique key or adds a hidden 6-byte row id. Always declare one explicitly.
Each table lives in its own .ibd file by default (innodb_file_per_table).
Example
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- clustered index
customer_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_customer (customer_id) -- leaf entries: (customer_id, id)
) ENGINE = InnoDB;
-- uses idx_customer only: the PK is already inside the secondary index
EXPLAIN SELECT id FROM orders WHERE customer_id = 7;
-- type: ref key: idx_customer Extra: Using index
SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema = DATABASE() GROUP BY engine; -- InnoDB | 1Remember
- InnoDB tables are clustered on the primary key
- Secondary indexes contain the primary key, so keep the PK narrow
- Sequential primary keys insert efficiently; always declare one
Watch out for
- Using a CHAR(36) UUID string as the primary key of a large table with many secondary indexes.