REST API的MySQL主键设计方案咨询：侧重性能与扩展性

Given you're building a REST API with a large user base—where users can only access their own objects (authenticated to get a userId)—and need to lock down the design of primary keys and user-unique idempotencyKeys, let’s break down each of your four options with a sharp focus on your core priorities: performance and scalability, plus the security and maintainability tradeoffs that come with each approach.

方案1: Composite Primary Key (userId + idempotencyKey) #

CREATE TABLE Cars (
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (userId, idempotencyKey),
 INDEX user_index (userId)
);

Query pattern: GET .../cars/{idempotencyKey} (filtered by authenticated userId)

Pros #

• No extra primary key field needed; the composite key naturally enforces uniqueness per user + idempotency key.
• The user_index is redundant here—since the primary key starts with userId, it already acts as an efficient index for user-specific queries.

Cons #

• The idempotencyKey used for queries is guessable. While authentication mitigates immediate risk, assuming guessable IDs isn’t a safe long-term practice (especially if your threat model expands).
• Composite keys make joining with other tables clunky—you’ll have to pass both fields everywhere.
• String-based composite keys have larger index footprints, which will hurt performance as your dataset scales to millions of records.

方案2: Hash of (userId + idempotencyKey) as Primary Key #

CREATE TABLE Cars (
 id VARCHAR(255) NOT NULL,
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id),
 INDEX user_index (userId)
);

Insert logic: Compute id = hash(userId + idempotencyKey)
Query pattern: GET .../cars/{id}

Pros #

• Single-value primary key makes table joins clean and simple.
• Hashed IDs are not guessable, reducing the risk of unauthorized enumeration.
• No need for an extra unique constraint on (userId, idempotencyKey)—the hash is derived from that unique pair.

Cons #

• Tiny risk of hash collisions (low probability, but you’ll need fallback logic if it ever happens).
• String-based hash keys still have larger index sizes than integer keys, leading to worse write/read performance at scale compared to numeric options.
• You still need to store userId and idempotencyKey separately, plus maintain the user_index for user-specific queries.

方案3: UUID as Primary Key #

CREATE TABLE Cars (
 id VARCHAR(255) NOT NULL,
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id),
 INDEX user_index (userId),
 CONSTRAINT unique_by_user UNIQUE (userId, idempotencyKey)
);

Insert logic: Set id = UUID.randomUUID()
Query pattern: GET .../cars/{id}

Pros #

• UUIDs are completely decoupled from business data, fully unguessable, and great for security.
• Single-value key simplifies cross-table relationships.

Cons #

• UUIDs are random strings, which cause severe index fragmentation on write operations. This kills performance as your dataset grows—indexes become bloated, and writes take longer due to random disk seeks.
• You have to maintain an extra unique constraint on (userId, idempotencyKey), adding another index overhead.
• String-based indexes are inherently less efficient than integer ones, making this a poor choice for scalability.

方案4: Unpublished Auto-Increment Primary Key #

CREATE TABLE Cars (
 id INT NOT NULL AUTO_INCREMENT,
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id),
 INDEX user_index (userId),
 CONSTRAINT unique_by_user UNIQUE (userId, idempotencyKey)
);

Query options: Either GET .../cars/{idempotencyKey} (filtered by userId) or generate a UUID referenceId (with index) for public queries.

Pros #

• Auto-increment INT keys are the most performant option by far: tiny index size, sequential writes (no fragmentation), and lightning-fast lookups. This scales incredibly well even for tens of millions of records.
• Keeping the primary key unpublished aligns with security best practices—no one can guess the number of users or records by enumerating IDs.
• Flexible for future changes: if your requirements around idempotencyKey shift, the underlying primary key structure stays untouched, reducing refactoring pain.
• The unique_by_user constraint ensures per-user idempotency, and the user_index keeps user-specific queries efficient.

Cons #

• Requires an extra unique constraint/index on (userId, idempotencyKey)—but this is a necessary overhead to enforce your business rule.
• If using idempotencyKey for public queries, you’ll rely on that unique index for lookups (which is still efficient, just not as fast as the primary key index).

Final Recommendation: Go with 方案4 #

For your core priorities of performance and scalability, 方案4 is the clear winner:

1. The auto-increment INT primary key delivers unbeatable index efficiency and write/read performance, which is critical as your user base and dataset grow.
2. Keeping the primary key private avoids security risks associated with guessable IDs, while still letting you use either idempotencyKey or a generated UUID referenceId for public queries.
3. It’s the most future-proof option—changes to your idempotency requirements or query patterns won’t force you to rebuild core table structures.

If you want to eliminate any guessability concerns for public query IDs entirely, add a referenceId UUID field with a unique index, and use that for your GET .../cars/{referenceId} endpoint. You’ll still get all the performance benefits of the auto-increment primary key, plus the security of unguessable public IDs.

内容的提问来源于stack exchange，提问作者TimJ