Designing MySQL InnoDB Tables and Indexes for Business Scenarios

ZhaiZhai uses MySQL with the InnoDB engine as its backend storage. Based on the characteristics of both the business and InnoDB, the article introduces how to design tables and indexes for specific scenarios, starting with fundamental MySQL concepts.

InnoDB Record Storage

Records are stored in primary‑key order, forming a clustered index. InnoDB pages are linked by a doubly‑linked list (inter‑page order) and records within a page are linked by a singly‑linked list (intra‑page order), enabling logical ordering and near‑binary‑search efficiency.

Impact of Different Primary Keys

Auto‑increment primary key: inserts are sequential, pages are filled continuously, yielding high disk utilization, low random I/O, and excellent insert performance.

Business primary key (e.g., uid, infoId): inserts may cause page splits and fragmentation, leading to lower write and query efficiency compared with auto‑increment keys.

However, the choice of primary key must consider later index usage, not just storage characteristics.

Primary Key Index (Clustered Index)

InnoDB automatically creates a B+Tree clustered index on the primary key; the leaf nodes store the actual rows.

Secondary (Non‑Primary) Index

Secondary indexes store the primary key value in their leaf nodes. A query on a secondary index first retrieves the primary key, then accesses the clustered index to fetch the full row, thus requiring two index lookups.

Composite (Combined) Index

Composite indexes contain multiple columns; the key is ordered left‑to‑right. They can be used only when the query predicates follow the leftmost prefix rule, and range conditions on a column prevent the use of subsequent columns.

Summary of Primary Key Choices

Auto‑increment key: high write/query efficiency and disk utilization, but queries often need two‑level index lookups because business queries rarely use the primary key directly.

Business key: slightly lower write/query efficiency and disk utilization, but can serve as a covering index, allowing single‑level lookups in some cases.

On SSDs the I/O advantage of auto‑increment keys is minimal; ZhaiZhai prefers business keys for its workloads.

Table Design Principles

Primary key selection : Prefer business keys that align with query patterns.

Number of indexes : Limit to no more than five to avoid oversized index files.

Column type choice : Use the smallest, simplest types (e.g., TINYINT for BOOL/enum, LONG for monetary values, avoid DECIMAL for performance).

Sharding strategy : Keep table size around ten million rows; shard by key modulo or by time to separate hot and cold data.

Practical Cases

Case 1 – User Table : Primary key uid, secondary index on mobile. A BIGINT "switch" column stores 64 boolean flags; a composite index on (uid, switch) enables covering queries for the switch flag without accessing the full row.

Case 2 – IM Subsystem Sharding : Four main tables are sharded by uid modulo 128. System messages are time‑sensitive (30‑day TTL) and are sharded by month, each month further split into 128 tables. To reduce cross‑month queries, the system writes each message to both the current and previous month tables, allowing a single query to retrieve all valid messages.

Overall Conclusions

Auto‑increment primary keys are not universally superior; choose based on business query patterns.

Prefer simple data types for better performance.

More indexes do not equal better performance; excessive indexes increase file size and maintenance cost.

If required data can be satisfied from an index, InnoDB avoids accessing the clustered row, improving query speed.

Author : Chen Dong, Head of Architecture Platform at ZhaiZhai, former database R&D engineer with deep knowledge of embedded storage engines.