Essential MySQL Design & Performance Guidelines You Should Follow

Database Design

Use UTF‑8 for all databases and tables

Provides internationalization support and avoids conversion errors.

Compatible with ASCII, UTF‑16, UTF‑32 without endian issues.

Web clients mainly use ASCII, so UTF‑8 is a safe default.

Supports prefix codes for easier debugging of network‑transmitted strings.

Use utf8mb4 when storing emojis.

Prefer InnoDB storage engine for all tables

Supports transactions, row‑level locking, better recovery, and high concurrency.

Add comments to every table and column

Facilitates maintenance and data collection.

Avoid placeholder columns

Names are unclear, types cannot be predetermined, default NULL is meaningless, and altering them locks the table.

Do not store large binary data (images, files) in the database

Store such files on dedicated object‑storage services (e.g., Alibaba Cloud, Tencent Cloud) and keep only the file URL in the database.

Large binaries cause rapid data growth, random I/O, and connection timeouts.

Keep single‑table row count under 5 million

Helps with backup, restore, and schema changes; archive historical data or shard tables when needed.

Use MySQL partition tables cautiously

Partitions are multiple files but logically a single table; can cause full‑table locks, poor concurrency, and limited performance benefits.

Prefer physical sharding instead.

Separate hot and cold data to reduce table width

MySQL limits a table to 4096 columns and 65 535 bytes per row.

Smaller tables improve cache hit rates and reduce unnecessary I/O.

Never run performance tests on production databases

Never connect development or test environments directly to production

Some small companies may do this for debugging, but it should be avoided.

Database Command Naming

Use lowercase letters with underscores for field names.

Avoid MySQL reserved keywords; quote them if necessary.

Keep field names meaningful and under 32 characters.

Ensure columns used for joins have identical types.

Prefix temporary tables with tmp_ and backup tables with bak_ plus a date.

Field Design

Select the smallest suitable data type

Unsigned types double the positive range.

Smaller types reduce index size and I/O.

Store IP addresses in numeric form.

Use UNSIGNED for non‑negative auto‑increment IDs.

VARCHAR(N) counts characters, not bytes; UTF‑8 can inflate storage.

Avoid TEXT and BLOB types

Separate them into auxiliary tables.

MySQL cannot use in‑memory temporary tables with TEXT/BLOB, leading to slower disk operations.

If unavoidable, query only needed columns and use prefix indexes.

Avoid ENUM types

ORDER BY on ENUM is inefficient.

Do not use numeric values for ENUM members.

Prefer NOT NULL for all columns

NULL values waste space in indexed columns and require extra handling.

Use TIMESTAMP (4 bytes) or DATETIME (8 bytes) for dates

Storing dates as strings prevents date functions from working and consumes more space.

Timestamp range: 1970‑01‑01 to 2038‑01‑19.

Use DATETIME for values outside this range.

Use DECIMAL for financial amounts

Float/double are imprecise; DECIMAL stores exact values.

Storage size depends on defined precision.

Index Design Guidelines

Limit the number of indexes per table (≤5)

Too many indexes increase write overhead and optimizer planning time.

Do not create a separate index for every column

Before MySQL 5.6 only one index per query was used; even after, composite indexes are more efficient.

Every InnoDB table must have a primary key

Use auto‑increment IDs; avoid frequently updated columns, UUIDs, or long strings.

Create indexes only when they can be used

Avoid indexing columns not used in WHERE, GROUP BY, ORDER BY.

Skip indexes on low‑cardinality columns (e.g., gender).

Do not index tiny tables or columns that change often.

When appropriate, use covering indexes

They avoid secondary‑index lookups and turn random I/O into sequential I/O.

Avoid redundant or duplicate indexes

Examples: primary key + index on same column; index(a,b,c) + index(a,b).

Follow left‑most principle for composite indexes

Place the most selective column first, then shorter columns, then most frequently queried columns.

Do not use LIKE with leading wildcards or functions on indexed columns

Patterns like LIKE '%xx' or LIKE '%xx%' invalidate indexes.

Functions or type casts on indexed columns force full scans.

Avoid foreign key constraints for performance

Use them only when necessary and ensure indexed reference columns.

SQL Development Standards

Prefer prepared statements

Reuse execution plans, reduce compilation time, and prevent SQL injection.

Avoid implicit type conversion

select name, phone from customer where id = '111';

Design for future expansion

Leave room for schema growth based on table purpose.

Leverage existing indexes

Do not use double‑percentage wildcards; use range queries where possible.

Only one column of a composite index can be used for range scans.

Separate database accounts per service and avoid cross‑database queries

Reduces CPU, I/O, and network overhead.

Always specify column lists in INSERT statements

insert into t(c1,c2,c3) values ('a','b','c');

Replace simple subqueries with JOINs

JOIN optimization will be covered in a separate article.

Limit the number of tables joined in a single query (≤5)

Too many joins increase join buffer size and can cause memory overflow.

Batch operations to reduce round‑trips

Combine multiple similar statements into a single batch.

Use IN instead of multiple OR conditions (max 500 values)

Avoid ORDER BY RAND()

It forces full table loading and heavy CPU/IO usage; generate random values in application code instead.

Do not apply functions or calculations to indexed columns in WHERE clauses

where date(create_time)='20190101'

Prefer range conditions without functions.

Prefer UNION ALL when duplicate rows are impossible

Split large, complex SQL statements into smaller ones

Allows parallel execution and reduces CPU load.

Operational Practices

Batch large write operations (>1 million rows) to avoid master‑slave lag and excessive binlog size

Use pt‑online‑schema‑change for altering big tables

It creates a copy, migrates data, and swaps tables to avoid locking.

Never grant SUPER privilege to application accounts Reserve SUPER for DBA use only. Follow the principle of least privilege for database accounts One DB per account, no cross‑database access, avoid DROP permission.

Adhering to these conventions helps maintain database robustness, performance, and scalability.