Comprehensive MySQL Database Naming, Design, Field, Index, and SQL Development Standards

Database Command Standards

All database object names must be lowercase and use underscores.

Avoid MySQL reserved keywords; if necessary, quote them with single quotes.

Names should be meaningful and no longer than 32 characters.

Temporary tables use the prefix tmp_ with a date suffix; backup tables use bak_ with a date or timestamp suffix.

Columns storing the same data must have identical names and types to keep indexes usable.

Database Basic Design Standards

All tables should use the InnoDB storage engine (default since MySQL 5.6) for transaction support, row‑level locking, and better concurrency.

Use UTF8 (or UTF8MB4 for emoji) as the unified character set to avoid conversion overhead and index loss.

Add comments to every table and column to maintain a data dictionary.

Keep single‑table row counts under 5 million; archive or shard data when larger.

Use physical partitioning (sharding) instead of MySQL partition tables for large datasets.

Separate hot and cold data to reduce table width and improve cache hit rates.

Do not create placeholder columns; they are hard to name meaningfully and lock the table when altered.

Do not store large binary objects (images, files) in the database; store only their paths.

Avoid running stress tests on production databases.

Never connect to production from development or test environments.

Database Field Design Standards

Choose the smallest appropriate data type; larger types waste index space and I/O.

Prefer unsigned integers for non‑negative values (e.g., auto‑increment IDs, IP addresses).

VARCHAR(N) counts characters, not bytes; with UTF8, 255 Chinese characters require 765 bytes, so avoid excessive lengths.

Avoid TEXT/BLOB types; if needed, place them in separate tables and use prefix indexes only.

Do not use ENUM; it requires ALTER for changes and has poor ORDER BY performance.

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

Store timestamps with TIMESTAMP (4 bytes) or DATETIME (8 bytes); avoid storing dates as strings.

Financial amounts must use DECIMAL for exact precision.

Index Design Standards

Limit each table to no more than five indexes; excessive indexes increase write cost and optimizer planning time.

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

Every InnoDB table must have a primary key; use an auto‑increment integer, avoid frequently updated columns, UUIDs, or large strings.

Common index candidates: columns used in WHERE, ORDER BY, GROUP BY, DISTINCT, and join conditions.

When ordering columns in a composite index, place the most selective, shortest, and most frequently used columns on the left.

Avoid redundant indexes (e.g., primary key + index on the same column) and overlapping indexes (e.g., index(a,b,c) plus index(a,b)).

Prefer covering indexes that contain all columns needed by a query to eliminate the secondary‑index lookup.

Index SET Standards

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

Database SQL Development Standards

Use prepared statements to reuse execution plans and prevent SQL injection.

Avoid implicit type conversion; it can invalidate indexes.

Leverage existing indexes; avoid leading wildcards in LIKE patterns (e.g., a LIKE '%123' cannot use an index).

Prefer range conditions over functions on columns (e.g., use create_time >= '2019-01-01' AND create_time < '2019-01-02' instead of DATE(create_time) = '20190101' ).

Use UNION ALL when duplicate rows are impossible.

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

Avoid SELECT * ; list required columns to reduce I/O and enable covering indexes.

Always specify column lists in INSERT statements.

Replace subqueries with JOINs when possible; subqueries generate temporary tables without indexes.

Limit the number of joined tables (MySQL allows up to 61, but keep it ≤5) and tune join_buffer_size appropriately.

Use IN instead of multiple OR conditions on the same column (max ~500 values).

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

Database Operation Behavior Standards

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

Use tools like pt‑online‑schema‑change for altering big tables to prevent locking and replication delay.

Do not grant SUPER privilege to application accounts; reserve it for DBA accounts only.

Apply the principle of least privilege: limit accounts to a single database, avoid DROP privileges, and prohibit cross‑database access.

Author: Ting Feng. Original article: cnblogs.com/huchong/p/10219318.html