Database Command and Design Standards

Database Command Standards

1. All database object names must use lowercase letters and be separated by underscores. 2. Object names must not use MySQL reserved keywords (if they appear, enclose them in single quotes). 3. Names should be meaningful and no longer than 32 characters. 4. Temporary tables must be prefixed with tmp and suffixed with a date; backup tables with bak and a timestamp. 5. Columns storing the same data must have identical names and types to avoid implicit conversion and index loss.

Basic Database Design Standards

1. Use InnoDB storage engine for all tables

Unless a special requirement forces another engine, all tables should use InnoDB (default since MySQL 5.6). InnoDB supports transactions, row‑level locking, better recovery, and higher concurrency.

2. Use UTF8 (or UTF8MB4) as the unified character set

UTF8 improves compatibility and avoids garbled text caused by charset conversion. For emoji storage, use utf8mb4 .

3. Add comments to all tables and columns

Use the COMMENT clause to document tables and columns and maintain a data dictionary from the start.

4. Keep single‑table row count under 5 million

Large tables make schema changes, backups, and restores difficult. Use archiving, sharding, or partitioning to control size.

5. Use MySQL partition tables cautiously

Partitioned tables are multiple physical files presented as one logical table. Choose partition keys carefully; often a physical sharding approach is preferable.

6. Separate hot and cold data to reduce table width

MySQL limits a table to 4096 columns and a row to 65535 bytes. Reducing table width improves cache hit rate and I/O efficiency.

7. Do not create placeholder columns

Placeholder columns are hard to name meaningfully, lack a clear data type, and modifying them locks the table.

8. Do not store large binary data (images, files) in the database

Large files cause rapid data growth and heavy random I/O. Store files on a file server and keep only their paths in the database.

9. Never perform stress testing on a production database

10. Do not connect directly from development or test environments to the production database

Database Column Design Standards

1. Choose the smallest appropriate data type

Smaller columns use less index space, reducing I/O. Example: store IP addresses as integers using inet_aton and convert back with inet_ntoa .

For non‑negative data (e.g., auto‑increment IDs, integer IPs), prefer UNSIGNED INT to double the positive range.

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

2. Avoid TEXT and BLOB types when possible

These types cannot be indexed efficiently and force MySQL to use disk‑based temporary tables. If needed, separate them into an auxiliary table and exclude them from SELECT statements.

3. Avoid ENUM type

Changing ENUM values requires ALTER statements, ORDER BY on ENUM is slow, and numeric ENUM values are discouraged.

4. Define columns as NOT NULL whenever possible

NULL columns waste index space and require extra handling during comparisons.

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

TIMESTAMP covers 1970‑01‑01 to 2038‑01‑19; use DATETIME for dates outside this range. Storing dates as strings loses the ability to use date functions and consumes more space.

6. Use DECIMAL for financial amounts

DECIMAL provides exact precision, unlike FLOAT/DOUBLE, and its storage size depends on defined precision.

Index Design Standards

1. Limit the number of indexes per table (recommended ≤ 5)

Too many indexes increase optimizer planning time and can degrade query performance.

2. Do not create a separate index for every column

MySQL 5.6+ can use index merge, but a well‑designed composite index is usually more efficient.

3. Every InnoDB table must have a primary key

Use an auto‑increment integer primary key; avoid frequently updated columns, UUIDs, hashes, or multi‑column keys.

Common Index Column Recommendations

1. Columns used in WHERE clauses of SELECT/UPDATE/DELETE. 2. Columns appearing in ORDER BY, GROUP BY, or DISTINCT. 3. Columns used for JOIN conditions.

How to Choose Index Column Order

Place the most selective column first, then the shortest column, then the most frequently used column in a composite index.

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).

Prefer Covering Indexes for Frequent Queries

A covering index contains all columns needed by the query, eliminating the need for a secondary lookup and reducing I/O.

Index SET Standards

1. Avoid foreign key constraints

Foreign keys should be enforced at the application level; they add overhead and can slow write operations.

Database SQL Development Standards

1. Use prepared statements

Prepared statements reuse execution plans, reduce compilation time, and prevent SQL injection.

2. Avoid implicit data‑type conversion

Implicit conversion can invalidate indexes.

3. Leverage existing indexes

Avoid patterns that prevent index usage, such as leading wildcards in LIKE ('%123%') or functions on indexed columns.

4. Use IN instead of OR for the same column

IN can use indexes efficiently; keep the list under 500 items.

5. Avoid ORDER BY RAND()

It forces MySQL to load all rows into memory. Generate a random value in the application instead.

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

Rewrite conditions to use raw column values to keep indexes usable.

7. Use UNION ALL when duplicate rows are not a concern

UNION ALL skips the costly deduplication step.

8. Split large, complex SQL statements into smaller ones

MySQL can only use one CPU per query; splitting enables parallel execution.

Database Operation Behavior Standards

1. Batch large write operations (over 1 million rows) into smaller chunks

Large batches cause master‑slave replication lag, generate massive binlog entries, and create long‑running transactions that lock tables.

2. Use pt‑online‑schema‑change for altering large tables

This tool creates a new table, copies data in chunks, and swaps tables to avoid long locks and replication delays.

3. Do not grant SUPER privilege to application accounts

SUPER should be reserved for DBA accounts only.

4. Follow the principle of least privilege for application database accounts

Application accounts should be limited to a single database, avoid cross‑database access, and generally not have DROP privileges.

Please follow the "Souyunku Technology Team" WeChat public account for the latest articles.