Master MySQL: Production-Ready Database Design & Operations Handbook

1. Choose the Right Primary Key for Each Table

Core idea: Use a column unrelated to business logic as the unique identifier.

Common Practices

Auto‑increment integer

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

UUID

CREATE TABLE orders (
    id CHAR(36) PRIMARY KEY DEFAULT UUID(),
    user_id INT UNSIGNED,
    total_amount DECIMAL(10,2)
);

Advantages

Performance: Integer keys are compact and index‑friendly.

Stability: Changes to business fields won’t cascade.

Decoupling: Business identifiers are separate from DB identifiers.

2. Follow Normalization Rules but Avoid Over‑Normalization

Basic Normal Forms

1NF: Fields must be atomic.

2NF: Non‑key fields fully depend on the primary key.

3NF: Eliminate transitive dependencies.

Example

Wrong design:

CREATE TABLE users (
    user_id INT,
    city_id INT,
    city_name VARCHAR(100)
);

Correct design: Move city_name to a separate city table.

Denormalization

For performance, you may duplicate some data (e.g., user_name in the orders table) to trade space for speed.

3. Build Efficient and Reasonable Indexes

Common Practices

Primary and foreign keys must have indexes.

Follow the left‑most prefix rule: (col1, col2, col3) index works as (col1), (col1,col2), (col1,col2,col3).

Index high‑cardinality columns (e.g., phone number, not gender).

Avoid excessive indexes because they hurt write performance.

Benefits

Query complexity drops from O(n) to O(log n).

JOIN, ORDER BY, and GROUP BY operations become much faster.

4. Column Design: Choose Appropriate Data Types

Prefer integers over strings for status values, e.g., TINYINT instead of VARCHAR.

Prefer fixed‑length over variable‑length for phone numbers: CHAR(11).

Avoid TEXT / BLOB; use separate tables or object storage.

Store monetary values with DECIMAL(10,2) to prevent floating‑point errors.

5. Plan for Scalability Early: Sharding and Hot‑Cold Data

Sharding key examples: user ID, order ID.

Time‑partitioned tables are suitable for log‑type data.

Separate hot (active) data from cold (historical) data; archive old rows.

6. When to Use Foreign Keys

Small systems can enforce referential integrity with foreign keys.

High‑concurrency systems should often drop foreign keys and enforce consistency in the application layer.

7. Naming and Documentation Standards

Use snake_case consistently.

Make column names self‑descriptive, e.g., user_email rather than cryptic abbreviations.

Avoid over‑abbreviation.

Keep ER diagrams and documentation in sync with the schema.

8. Transaction and Concurrency Control

Prefer the InnoDB storage engine.

Avoid long‑running transactions to reduce lock time.

Explicit transaction example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

9. Use NULL Values Cautiously

NULLs can degrade index efficiency.

Query logic becomes more complex (e.g., NULL <> '').

Consider default values such as 0, '', or a sentinel date like 1970-01-01 instead of NULL.

10. Avoid Implicit Type Conversion and Reserved Words

Do not name columns with reserved words like key or order.

Ensure query predicates match column types, e.g., comparing a VARCHAR phone column with a quoted string.

-- phone is VARCHAR
SELECT * FROM users WHERE phone = '13800138000'; -- ✅

11. Read‑Write Splitting and Caching Design

Master‑slave replication to boost read throughput.

Application‑level routing for read/write separation.

Place hot query results in Redis cache before hitting the database.

12. Use Triggers and Stored Procedures Sparingly

Pros: Pushes logic down to the database.

Cons: Hard to debug and low portability.

Recommendation: Keep core business logic in the application layer; let the database handle only data storage.

13. Logging and Monitoring

Enable slow‑query logs.

Configure audit logs to track sensitive tables.

Monitor metrics such as QPS, TPS, lock wait time, and Buffer Pool hit rate.

14. Partitioned Tables and Archiving Strategy

Partition tables by time, range, or hash.

Archive tables periodically to move historical data out of the primary database.

15. Security and Permission Management

Apply the principle of least privilege: grant only SELECT/INSERT/UPDATE/DELETE to application accounts.

Prohibit direct root login to production.

Mask and encrypt sensitive data such as phone numbers and ID numbers.

16. Summary

MySQL database design best practices can be grouped into three layers:

Design layer: primary key choice, normalization, indexing strategy, column types.

Scalability layer: sharding, hot‑cold separation, read‑write splitting.

Operations layer: transaction control, logging, monitoring, security, and permissions.

These layers complement each other: a stable skeleton from keys and normalization, a high‑performance highway from indexes and scaling tactics, and safety/maintainability from logging and permission controls. The ultimate goal is a database that supports high concurrency while remaining maintainable and extensible over time.