Essential MySQL Best Practices for High‑Performance, Scalable Architecture

1. Basic Rules

Use InnoDB storage engine. It supports transactions and row‑level locking, offers better concurrency, and optimizes CPU and memory cache usage.

Add Chinese comments to tables and columns. Improves readability and clarifies enumerated values such as status codes.

Use UTF8mb4 character set. Extends UTF‑8 to store emojis and other newer characters.

Avoid stored procedures, views, triggers, events, and joins. Offload CPU‑intensive logic to the service layer; keep the database focused on storage and indexing.

Do not store large files or images in the database. Store them in the file system and keep only their URIs in the DB.

2. Naming Conventions

Use internal domain names instead of IP addresses for database connections to enable seamless master‑slave or instance switching.

Follow a consistent naming scheme for environments (online, development, testing) and databases, e.g., nx.xxx.db for primary and nx.xxx-s.db for replicas.

Table names: t_xxx; non‑unique indexes: idx_xxx; unique indexes: uniq_xxx.

3. Table Design Guidelines

Every table must have a primary key, preferably an auto‑increment integer.

Increasing primary keys improve insert performance and reduce page splits.

Numeric primary keys consume less space and improve index cache efficiency.

Absence of a primary key can cause replication lag on replicas.

Business‑level primary keys aid sharding and partitioning.

Avoid foreign keys; enforce referential integrity in application code. Foreign keys increase coupling and can cause performance degradation or deadlocks.

4. Column Design Guidelines

Define columns as NOT NULL and provide default values.

NULL values complicate index statistics and query optimization.

NULL handling requires extra storage and special operators, reducing performance.

Do not use TEXT or BLOB types unless absolutely necessary. They waste disk and memory and push hot data out of cache.

Never store monetary values as floating‑point numbers. Use integer representations (e.g., cents) to avoid rounding errors.

Store phone numbers as VARCHAR(20).

Accommodates country codes, symbols, and formatting.

No arithmetic is performed on phone numbers.

Allows pattern matching with LIKE.

Replace ENUM with TINYINT.

Adding ENUM values requires DDL changes.

Internally ENUM is stored as an integer, so TINYINT is clearer.

5. Index Design Guidelines

Limit the number of indexes per table to five or fewer.

Avoid indexing columns that are updated frequently or have low selectivity (e.g., gender).

When creating composite indexes, place the most selective columns first.

6. SQL Usage Guidelines

Never use INSERT INTO t_xxx VALUES(...); always specify column names to avoid bugs after schema changes.

Avoid functions or expressions on indexed columns in WHERE clauses; they prevent index usage.

Do not use negative predicates (NOT, !=, <>, etc.) or leading‑wildcard LIKE patterns; they cause full table scans.

Avoid JOINs and subqueries on large tables; they generate temporary tables and consume excessive memory and CPU.

Replace OR conditions with IN clauses for better index utilization.

Application code must catch SQL exceptions and handle them appropriately.