MySQL Database Naming, Design, Field, Index, and Operation Standards

1. Database Command Standards

All database object names must be lowercase and use underscores; reserved MySQL keywords are prohibited (if needed, wrap with single quotes); names should be meaningful and no longer than 32 characters; temporary tables use the tmp_ prefix with a date suffix, backup tables use bak_ with a date or timestamp; columns storing the same data must have identical names and types to avoid implicit type conversion that can invalidate indexes.

2. Basic Database Design Standards

All tables should use the InnoDB storage engine, which supports transactions, row‑level locking, better recovery, and higher concurrency.

The database and tables must use the UTF‑8 character set to avoid conversion errors and index loss.

Every table and column must have comments to maintain a data dictionary.

Table size should be kept under 5 million rows to simplify schema changes, backups, and restores; large tables can be archived or sharded.

Avoid using MySQL partition tables unless absolutely necessary; prefer physical sharding.

Separate hot and cold data to keep tables narrow; MySQL limits a table to 4096 columns and a row to 65535 bytes.

Do not create placeholder columns; they are hard to name meaningfully and can cause locking when altered.

Do not store large binary data (images, files) in the database; store them on a file server and keep only the path.

Never run performance tests on a production database; testing should be done in a non‑production environment.

3. Database Field Design Standards

Choose the smallest appropriate data type; larger columns increase index size and I/O.

Store IP addresses as integers using INET_ATON and display with INET_NTOA .

Prefer unsigned integers for non‑negative data to double the range.

VARCHAR(N) counts characters, not bytes; UTF‑8 stores up to 765 bytes for 255 Chinese characters, so avoid overly large lengths.

Avoid TEXT/BLOB types; if needed, place them in a separate extension table because they cannot have default values and only support prefix indexes.

Avoid ENUM types; changing values requires ALTER statements, ORDER BY is slow, and numeric enums are discouraged.

Define columns as NOT NULL whenever possible to save space and simplify comparisons.

Use TIMESTAMP (4 bytes) or DATETIME (8 bytes) for date/time values; TIMESTAMP covers 1970‑01‑01 to 2038‑01‑19, otherwise use DATETIME.

Store monetary values with DECIMAL for exact precision; FLOAT/DOUBLE are non‑precise.

4. Index Design Standards

Limit each table to no more than five indexes; excessive indexes increase optimizer planning time and can degrade performance.

Do not create an index on every column; use composite indexes wisely.

Every InnoDB table must have a primary key; avoid using frequently updated columns, UUIDs, MD5, or strings as primary keys—prefer auto‑increment IDs.

5. Common Index Column Recommendations

Columns used in WHERE clauses of SELECT/UPDATE/DELETE.

Columns appearing in ORDER BY, GROUP BY, or DISTINCT.

Prefer composite indexes over separate indexes for the above columns.

Join columns for multi‑table queries.

6. How to Choose Index Column Order

Place the column with the highest cardinality on the leftmost side of a composite index.

Put shorter columns first to maximize rows per page.

Put the most frequently used columns first.

7. Avoid Redundant and Duplicate Indexes

Duplicate example: PRIMARY KEY(id) , INDEX(id) , UNIQUE INDEX(id) .

Redundant example: INDEX(a,b,c) , INDEX(a,b) , INDEX(a) .

8. Prefer Covering Indexes

Covering indexes contain all columns needed by a query, eliminating the need for a secondary lookup and turning random I/O into sequential I/O.

9. Index SET Guidelines

Avoid foreign key constraints; instead, create indexes on the related columns and enforce referential integrity at the application level to reduce write overhead.

10. Database SQL Development Standards

Use prepared statements to reduce compilation time and prevent SQL injection.

Avoid implicit type conversion; it can invalidate indexes.

Leverage existing indexes; avoid leading % wildcards in LIKE patterns.

Only one column of a composite index can be used for a range query; place range columns on the right side.

Replace NOT IN with LEFT JOIN or NOT EXISTS.

Limit the number of joined tables (MySQL default max 61, recommended ≤5) to prevent excessive memory usage.

Batch operations to reduce round‑trips.

Use IN instead of multiple ORs (keep IN list ≤500 items).

Never use ORDER BY RAND() for random ordering; generate a random value in the application instead.

Avoid functions or calculations on indexed columns in WHERE clauses.

Prefer UNION ALL over UNION when duplicate rows are impossible.

Break large complex SQL statements into smaller ones to enable parallel execution.

11. Database Operation Behavior Standards

Batch large write operations (>1 million rows) to avoid master‑slave lag and massive binlog generation.

Use pt-online-schema-change for schema changes on big tables to avoid locking.

Do not grant SUPER privileges to application accounts; follow the principle of least privilege.

Application accounts should be limited to a single database, without DROP permissions.