Mastering InnoDB: How Table Design and Index Strategies Boost MySQL Performance

InnoDB Record Storage Mechanism

InnoDB stores rows in primary‑key order, forming a clustered index. Records are organized by page order (inter‑page linked list) and intra‑page order (single‑linked list with slot structures), enabling near‑binary‑search efficiency within a page.

Primary Key Index (Clustered Index)

InnoDB automatically creates a B+Tree clustered index on the primary key; the leaf nodes store the actual row data. This tightly couples index and data, making primary‑key lookups very fast.

Secondary (Non‑Primary) Index

Secondary indexes store the primary‑key value in their leaf nodes. A query on a secondary column first finds the primary‑key via the secondary index, then uses the primary‑key to locate the row, requiring two index lookups.

Indexes other than the primary key

Leaf nodes contain primary‑key values

Each query traverses both the secondary and primary indexes

Composite (Multi‑Column) Index

A composite index contains multiple columns; the key is ordered by the leftmost column first, then the next, and so on. The index can be used only when queries follow the leftmost prefix rule.

Key must start with the leftmost column; otherwise the index is not used

Skipping intermediate columns prevents later columns from being used

If a column uses a range condition, columns to its right cannot be used

With a composite index (a,b,c), queries can efficiently use (a), (a,b), or (a,b,c).

Key Takeaways on Primary‑Key Types

Auto‑increment keys insert sequentially, yielding high disk‑space utilization and low random I/O, but every query still traverses two index levels. Business keys (e.g., uid, infoId) are not physically sequential and may cause page splits, yet they enable single‑level index lookups and can benefit from covering indexes.

In SSD environments the I/O advantage of auto‑increment keys diminishes, while business keys often provide better query performance, so many production systems prefer business keys.

E‑Commerce Table‑Design Principles and Practices

1. Table Design Principles

Primary‑key choice: Prefer business keys to match read‑heavy workloads.

Index count: Limit to ≤5 indexes to avoid oversized index files.

Column type selection: Use compact types (e.g., TINYINT for BOOL/enum, LONG for monetary values). Store monetary amounts as integers to avoid DECIMAL’s CPU overhead.

Sharding strategy: Anticipate growth; keep each table under tens of millions of rows. Choose either hash‑mod (even read/write) or time‑based sharding (hot/cold data separation).

2. Real‑World Cases

Case 1: User Table

The table includes uid (primary key), nickname, mobile, address, image, switch (BIGINT). The switch column packs multiple boolean flags into bits. To query switch efficiently, a covering composite index on (uid, switch) is created, allowing the engine to retrieve the flag directly from the secondary index without accessing the row data.

The index remains stable when switch changes because the first column uid is immutable, so only the secondary key value changes without restructuring the index.

Case 2: IM Subsystem Sharding

Four main tables (users, contacts, cloud messages, system messages) are split per business. All but system messages are sharded by uid % 128. System messages have a 30‑day retention requirement, so they are sharded monthly, each month further divided into 128 tables.

To avoid two‑round queries across month boundaries, a redundant write strategy is used:

When inserting a system message, write it to both the current month and the previous month tables.

When reading, start from the previous month, ensuring a single query can retrieve all messages within the 30‑day window.

Final Conclusions

Auto‑increment keys are not universally superior; choose based on workload.

Prefer simple, compact column types for better performance.

More indexes do not equal better performance; excess indexes inflate index files.

If needed data resides in an index, InnoDB can avoid accessing the primary‑key row entirely.