Comprehensive MySQL Database Design and Development Standards

1. Database Naming Conventions All object names must be lowercase with underscores, avoid MySQL reserved keywords, be meaningful, and not exceed 32 characters. Temporary tables use the tmp_ prefix 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 prevent implicit conversion and index loss.

2. Basic Design Rules Use the InnoDB storage engine for all tables (supports transactions, row‑level locking, and better concurrency). Set the default character set to UTF‑8 to avoid encoding issues. Add comments to tables and columns for data‑dictionary maintenance. Keep single‑table row counts below 5 million and avoid MySQL partition tables unless absolutely necessary. Separate hot and cold data, and keep table width small (max 4096 columns, row size ≤ 65535 bytes). Do not store large binary files in the database; store only file paths.

3. Field Design Guidelines Choose the smallest suitable data type; for example, store IP addresses as integers using INET_ATON() and retrieve them with INET_NTOA() . Prefer unsigned integers for non‑negative values. Use VARCHAR(N) where N is the character count (UTF‑8 characters may occupy up to 3 bytes each). Avoid TEXT and BLOB types; if needed, place them in separate extension tables and never use SELECT * . Avoid ENUM due to ALTER‑statement overhead and poor ORDER BY performance. Define columns as NOT NULL to save index space and simplify comparisons. Store timestamps with TIMESTAMP (4 bytes) when the range fits, otherwise use DATETIME . Use DECIMAL for financial values to preserve precision.

4. Index Design Principles Limit each table to no more than five indexes; avoid per‑column indexes and redundant or overlapping indexes. Every InnoDB table must have a primary key, preferably an auto‑increment integer, and avoid using frequently updated columns, UUIDs, or large strings as primary keys. Place the most selective columns on the left side of composite indexes, prioritize short columns, and order by usage frequency. Prefer covering indexes that contain all columns needed by a query to eliminate secondary‑index lookups. Use foreign keys sparingly; create indexes on referenced columns but enforce referential integrity at the application level to avoid write‑performance penalties.

5. SQL Development Standards Use prepared statements to reduce compilation overhead and prevent SQL injection. Avoid implicit type conversion in WHERE clauses (e.g., comparing a numeric column to a string). Leverage existing indexes and avoid leading wildcards ( LIKE '%value%' ). Use IN instead of multiple OR conditions (limit to ~500 values). Do not use ORDER BY RAND() ; generate random values in the application instead. Avoid functions or calculations on indexed columns in WHERE clauses. Prefer UNION ALL over UNION when duplicate rows are impossible. Split large, complex SQL statements into smaller ones to enable parallel execution.

6. Operational Practices Batch large write operations (>1 million rows) to prevent master‑slave replication lag and excessive binary logs. Use pt‑online‑schema‑change for altering large tables without locking. Restrict database accounts to the minimum required privileges; never grant SUPER to application users, and avoid cross‑database access. Follow the principle of least privilege for all program‑level accounts.

7. Miscellaneous Recommendations Do not store large binary data directly in the database; use a file server and store only the path. Avoid creating placeholder columns, and never use SELECT * in production code. Limit the number of tables joined in a single query (MySQL supports up to 61, but keeping it ≤ 5 is advisable). Ensure that batch operations are broken into manageable chunks to avoid long‑running transactions and lock contention.