Master MySQL: Essential Naming, Design, and Indexing Standards

1. Database Naming Conventions

Use lowercase letters with underscores for all object names.

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

Names should be meaningful and no longer than 32 characters.

Temporary tables start with

tmp

and end with a date; backup tables start with

bak

and end with a date or timestamp.

Columns storing the same data must have identical names and types to prevent implicit conversion and index loss.

2. Basic Database Design Rules

All tables must use the InnoDB storage engine. InnoDB provides transactions, row‑level locking, better recovery, and higher concurrency.

Use UTF8 as the default character set for databases and tables. This avoids encoding issues and index failures caused by charset conversion.

Add comments to every table and column. Use the

COMMENT

clause to maintain a data dictionary from the start.

Keep single‑table row counts under 5 million. Large tables cause costly schema changes, backups, and restores; consider archiving, sharding, or partitioning.

Use partition tables cautiously. Choose partition keys wisely; cross‑partition queries can be slower, so physical sharding is often preferable.

Separate hot and cold data to reduce table width. MySQL limits a table to 4096 columns and 65 535 bytes per row; narrow tables improve cache hit rates and I/O.

Avoid placeholder columns. They are hard to name meaningfully and lock the table when altered.

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

Never run stress tests on production databases.

Do not connect development or test environments directly to production databases.

3. Field Design Guidelines

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

Examples:

Convert IP strings to integers using

INET_ATON

and back with

INET_NTOA

.

Use unsigned integers for non‑negative values to double the range.

VARCHAR(N) counts characters, not bytes; storing 255 Chinese characters requires 765 bytes in UTF8.

Avoid TEXT and BLOB unless necessary. If used, separate them into auxiliary tables and avoid

SELECT *

on those columns.

TEXT/BLOB can only use prefix indexes and cannot have default values.

Do not use ENUM. Changing ENUM values requires

ALTER

, and ORDER BY on ENUM is inefficient.

Define columns as NOT NULL whenever possible. NULL columns waste index space and require special handling in comparisons.

Store timestamps using TIMESTAMP (4 bytes) or DATETIME (8 bytes). TIMESTAMP covers 1970‑01‑01 to 2038‑01‑19; beyond that use DATETIME.

Use DECIMAL for financial amounts. FLOAT/DOUBLE are imprecise; DECIMAL provides exact arithmetic.

4. Index Design Standards

Limit indexes per table to five. Excessive indexes increase optimizer planning time and can degrade performance.

Do not create an index on every column. Use composite indexes wisely; MySQL 5.6+ can merge indexes but single‑column indexes are still preferable.

Every InnoDB table must have a primary key. Prefer auto‑increment IDs; avoid frequently updated columns, UUIDs, hashes, or long strings as PKs.

5. Common Index Column Recommendations

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

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

Join columns for multi‑table queries.

Combine such columns into composite indexes rather than creating separate indexes for each.

6. Choosing Index Column Order

Place the most selective column on the leftmost side of a composite index.

Prefer shorter columns on the left to maximize rows per page.

Put the most frequently used column 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 to look up the primary key row and reducing I/O.

9. Foreign Key Guidelines

Avoid foreign key constraints in MySQL; instead, create indexes on the related columns and enforce referential integrity at the application level to prevent write‑performance penalties.

10. SQL Development Practices

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.

When using range queries, place the range column on the right side of a composite index.

Prefer LEFT JOIN or NOT EXISTS over NOT IN for better index usage.

Use explicit column lists instead of

SELECT *

to enable covering indexes and reduce CPU/IO.

Always specify column lists in INSERT statements.

Replace simple subqueries with JOINs where possible.

Limit the number of tables joined in a single query (MySQL allows up to 61, but keep it ≤5).

Batch multiple operations to reduce round‑trips.

Replace multiple OR conditions with IN (keep IN list ≤500 items).

Avoid

ORDER BY RAND()

; generate random values in the application instead.

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

Use

UNION ALL

when duplicate rows are impossible.

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

11. Database Operation Guidelines

For batch writes affecting >1 million rows, split into smaller transactions to avoid master‑slave lag, excessive binlog generation, and long‑running locks.

Use

pt-online-schema-change

for altering large tables to minimize downtime and replication lag.

Never grant SUPER privilege to application accounts; follow the principle of least privilege and restrict accounts to a single database without DROP rights.

Source: 转自公众号“芋道源码“