MySQL Table Design Best Practices and Common Pitfalls

Preface

As a backend developer, we often need to design database tables; the following are my collected MySQL table design experiences to help and reference.

1. Naming Conventions

Table names, column names, index names, etc., must follow naming rules.

Names should use lowercase letters or numbers (numbers not recommended), must not start with a digit, avoid pinyin, and generally avoid English abbreviations.

Primary key index name: pk_字段名 ; unique index name: uk_字段名 ; normal index name: idx_字段名 .

Bad example:

acc_no,1_acc_no,zhanghao

Good example:

account_no,account_number

2. Choose Appropriate Data Types

Select the smallest suitable type to save storage space, e.g.:

Numeric types: tinyint (1 byte), smallint (2 bytes), int (4 bytes), bigint (8 bytes).

For monetary values, use decimal and avoid float / double .

If string lengths are almost equal, use char (fixed length).

varchar is variable‑length; keep length under 5000.

For very long strings, use text and consider a separate table linked by a primary key.

Thoughts & Questions

Why choose char(1) over varchar(1) for gender? char(1) allocates exactly one character, while varchar(1) stores an extra length byte.

Why some use tinyint for gender? Using 0/1 saves one byte compared to storing the actual characters.

In MySQL, char / varchar count characters, other types count bytes. For example, the Chinese character ‘男’ is 1 character but 3 bytes in UTF‑8.

3. Reasonable Primary Key Design

Avoid tying primary keys to business logic; use meaningless unique numbers such as UUID, auto_increment , or Snowflake IDs.

4. Choose Appropriate Field Lengths

For a username of 5‑20 characters, consider username varchar(32) . Field lengths are usually set to powers of two for storage efficiency.

Why use powers of two? Databases use bit‑wise operations for length handling; powers of two simplify calculations and reduce memory waste.

Difference between varchar(20) and varchar(255) ? Storage is the same, but index length differs.

Difference between int(1) and int(11) ? The number in parentheses defines display width, not storage size.

5. Prefer Logical Deletion Over Physical Deletion

Physical deletion removes data from disk, freeing space. Logical deletion adds a flag (e.g., is_deleted ) to mark records as deleted.

delete from account_info_tab where account_no='666';

update account_info_tab set is_deleted=1 where account_no='666';

Logical deletion is recommended because it preserves data for recovery, keeps auto‑increment keys continuous, and is safer for core business tables.

6. Common Fields for Every Table

id : primary key (required)

create_time : creation timestamp (required)

modifed_time : modification timestamp (required)

version : version number for optimistic locking (optional)

remark : remarks (optional)

modified_by : modifier (optional)

creator : creator (optional)

7. Use NOT NULL When Possible NOT NULL prevents null‑pointer issues, reduces storage overhead, simplifies optimizer calculations, and avoids index invalidation. 8. Evaluate Which Fields Need Indexes Only add indexes when the table has enough rows to benefit; avoid excessive indexes (generally no more than five per table) and avoid indexing low‑cardinality columns like gender. Be careful with functions on indexed columns, expressions in WHERE clauses, and implicit conversions that can invalidate indexes. 9. 3NF Is Not Mandatory; Controlled Redundancy Can Improve Performance While third normal form discourages redundant fields, sometimes storing derived data (e.g., total amount = price × quantity) speeds up queries. 10. Avoid Foreign Keys; Handle Relationships in Application Code “[Mandatory] Do not use foreign keys or cascade; all foreign‑key concepts must be resolved at the application layer.” Foreign keys can cause performance issues, deadlocks, and complicate data generation for tests, especially in sharding scenarios. 11. Prefer InnoDB Storage Engine Use InnoDB unless the read/write ratio is below 1 %, in which case MyISAM might be considered. 12. Choosing Time Types MySQL provides date , datetime , time , timestamp , and year . The recommendation is to use datetime for most cases because of its larger range and timezone‑independent nature. 13. Avoid Storing Large TEXT in Main Tables Storing large TEXT fields in the same table reduces the number of rows per index page, hurting performance. Splitting TEXT into a separate table can improve index density. 14. Consider Sharding (Database & Table Partitioning) When tables are expected to grow to millions or tens of millions of rows, plan for sharding to maintain query performance. 15. SQL Optimization Tips Avoid select * ; specify needed columns. Do not use OR in WHERE clauses when it can invalidate indexes. Avoid functions on indexed columns. Avoid expressions and implicit conversions on indexed fields. Prefer != or <> sparingly. Follow the left‑most principle for composite indexes. Create indexes on columns used in WHERE and ORDER BY. Batch inserts for large data loads. Use covering indexes when appropriate. Analyze queries with EXPLAIN . 16. Conclusion The above are my experiences with MySQL table design and optimization; feel free to share additional tips in the comments.