Essential MySQL Naming, Design, and Indexing Standards for Reliable Databases

1. Database Command Standards

All database object names must be lowercase and use underscores.

Avoid MySQL reserved keywords; if used, enclose them in single quotes.

Names should be meaningful and no longer than 32 characters.

Temporary tables must start with

tmp

and end with a date; backup tables must start with

bak

and end with a date or timestamp.

Columns storing the same data must have identical names and types to prevent implicit type conversion that can invalidate indexes.

2. Basic Database Design Standards

1. Use InnoDB storage engine for all tables. InnoDB provides transactions, row‑level locking, better recovery, and higher concurrency.

2. Use UTF8 charset for databases and tables. It improves compatibility and avoids index loss caused by charset conversion.

3. Add comments to all tables and columns. Use the

COMMENT

clause to maintain a data dictionary from the start.

4. Keep single‑table data size under 5 million rows. Large tables cause difficulties in schema changes, backups, and restores.

5. Use data archiving or sharding to control table size.

6. Use MySQL partition tables cautiously. Choose partition keys wisely; consider physical sharding instead.

7. Separate hot and cold data to reduce table width. MySQL limits a table to 4096 columns and 65535 bytes per row; narrow tables improve cache efficiency.

8. Do not store large binary data (images, files) in the database. Store only file paths and keep binaries on a file server.

9. Never perform stress testing on production databases.

10. Do not connect to production databases from development or test environments.

3. Database Field Design Standards

1. Choose the smallest data type that meets storage needs.

Large column types increase index size and reduce the number of index entries per page, leading to more I/O.

2. Convert strings to numeric types when possible (e.g., store IP addresses as integers).

Before inserting data, use

INET_ATON

to convert an IP address to an integer; display it with

INET_NTOA

.

3. Use unsigned integers for non‑negative data such as auto‑increment IDs and IP addresses.

4. VARCHAR(N) counts characters, not bytes; with UTF8, VARCHAR(255) can occupy up to 765 bytes.

5. Avoid TEXT and BLOB types; if necessary, store them in separate extension tables. MySQL temporary tables do not support these types, forcing disk‑based temporary tables and degrading performance.

6. Avoid ENUM types; they hinder index usage and require ALTER to modify values.

7. Define all columns as NOT NULL when possible. NULL columns increase index size and require special handling during comparisons.

8. Use TIMESTAMP (4 bytes) or DATETIME (8 bytes) for time values.

TIMESTAMP

covers 1970‑01‑01 to 2038‑01‑19; beyond that, use

DATETIME

. Storing dates as strings wastes space and prevents date functions.

9. Store monetary values with DECIMAL for precise arithmetic.

4. Index Design Standards

1. Limit the number of indexes per table to no more than five. Too many indexes can degrade insert/update performance and increase optimizer planning time.

2. Do not create a separate index on every column. A composite index is usually more efficient.

3. Every InnoDB table must have a primary key. InnoDB stores rows in primary‑key order; secondary indexes store only the primary‑key value.

4. Avoid using frequently updated columns as primary keys; do not use UUID, MD5, HASH, or long strings as primary keys. Use auto‑increment IDs instead.

5. Common Index Column Recommendations

Columns appearing in

WHERE

clauses of

SELECT

,

UPDATE

, or

DELETE

.

Columns used in

ORDER BY

,

GROUP BY

, or

DISTINCT

.

For multi‑table joins, the join columns.

Prefer creating composite indexes that cover both groups rather than separate single‑column indexes.

6. Choosing Index Column Order

Place the column with the highest selectivity (distinct values / total rows) at the leftmost position of a composite index; if selectivity is similar, place the shorter column first; also prioritize columns used most frequently.

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

Use covering indexes that contain all columns needed by a query to avoid secondary‑index lookups and convert random I/O to sequential I/O.

9. Foreign‑Key Constraints

Avoid foreign keys but ensure indexed join columns.

Implement referential integrity at the application level.

Foreign keys can slow write performance.

10. SQL Development Standards

1. Use prepared statements. They reduce compilation overhead and prevent SQL injection.

2. Avoid implicit data‑type conversions. They can invalidate indexes (e.g.,

WHERE id = '111'

).

3. Leverage existing indexes. Avoid patterns like

LIKE '%123%'

which prevent index usage; use leading wildcards only when necessary.

4. Do not use SELECT * ; specify column lists. This reduces CPU, I/O, and enables covering indexes.

5. Avoid INSERT statements without column lists. Always specify columns.

6. Replace subqueries with JOINs when possible. Subqueries often create temporary tables without indexes, harming performance.

7. Limit the number of tables joined in a single query (recommended ≤5). Excessive joins increase memory usage and can cause server instability.

8. Reduce round‑trips to the database by batching operations.

9. Use IN instead of multiple OR conditions on the same column. Keep

IN

lists under 500 items for efficiency.

10. Avoid ORDER BY RAND() for random sorting. Generate a random value in the application and query by that value instead.

11. Do not apply functions or calculations to indexed columns in WHERE clauses. This prevents index usage.

12. Use UNION ALL instead of UNION when duplicate rows are not a concern.

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

11. Database Operation Behavior Standards

1. For batch writes exceeding 1 million rows, split into multiple smaller batches to avoid master‑slave lag and large binary logs.

2. Use

pt‑online‑schema‑change

for altering large tables to avoid long locks and replication delays.

3. Do not grant

SUPER

privilege to application accounts; reserve it for DBA use.

4. Follow the principle of least privilege for application database accounts; avoid cross‑database usage and disallow

DROP

permissions.