Essential MySQL Design & Development Standards for Reliable Databases

Database Command Standards

All database object names must be lowercase and use underscores.

Avoid using MySQL reserved keywords for object names; if necessary, enclose them in single quotes.

Names should be meaningful and not exceed 32 characters.

Temporary tables must start with tmp_ and end with a date; backup tables must start with bak_ and end with a date (timestamp).

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

Basic Database Design Standards

1. All tables must use InnoDB storage engine

Unless special requirements exist (e.g., columnar storage), all tables should use InnoDB (default after MySQL 5.6).

InnoDB supports transactions, row‑level locking, better recovery, and higher concurrency performance.

2. Use UTF8 charset for databases and tables

UTF8 provides better compatibility and avoids garbled text caused by charset conversion; use utf8mb4 when storing emojis.

3. Add comments to all tables and columns

Use the COMMENT clause to maintain a data dictionary from the start.

4. Limit single‑table size, preferably under 5 million rows

Large tables cause difficulties in schema changes, backups, and restores. Use archiving, sharding, or partitioning to control size.

5. Use MySQL partition tables cautiously

Partitions are separate files; choose partition keys carefully as cross‑partition queries may be slower. Physical sharding is often preferred for large data.

6. Separate hot and cold data to reduce table width

MySQL limits a table to 4096 columns and a row size to 65535 bytes.

Reducing table width improves cache hit rates and I/O efficiency.

7. Prohibit placeholder columns in tables

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 them on a file server and keep only the file path in the database.

9. Do not perform load testing on production databases

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

Database Column Design Standards

1. Choose the smallest suitable data type

Reason: Larger columns increase index size, reducing the number of index entries per page and raising I/O.

Method:

a. Store numeric representations of strings, e.g., convert IP addresses to integers.

MySQL provides INET_ATON (IP to unsigned int) and INET_NTOA (int to IP).

b. Use unsigned integers for non‑negative data such as auto‑increment IDs.

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295

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

2. Avoid TEXT and BLOB types when possible

a. Separate BLOB/TEXT columns into auxiliary tables.

MySQL cannot use indexes on TEXT/BLOB in memory temporary tables, leading to slower queries.

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

3. Avoid ENUM type

Changing ENUM values requires ALTER; ORDER BY on ENUM is inefficient; never use numeric values for ENUM.

4. Define columns as NOT NULL whenever possible

NULL columns waste index space and require special handling in comparisons.

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

TIMESTAMP range: 1970‑01‑01 00:00:01 to 2038‑01‑19 03:14:07; use DATETIME for values outside this range.

Storing dates as strings prevents date functions and consumes more space.

6. Use DECIMAL for monetary values

Floating‑point types (float, double) are imprecise.

DECIMAL provides exact precision; each 4 bytes store 9 digits, with one byte for the decimal point.

Index Design Standards

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

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

2. Do not create an index on every column

Before MySQL 5.6 only one index per query was used; even after, combined indexes are preferable.

3. Every InnoDB table must have a primary key

InnoDB stores rows in primary‑key order; avoid frequently updated columns, UUIDs, or strings as primary keys; use auto‑increment IDs.

4. Common index column recommendations

Columns appearing in WHERE clauses.

Columns used in ORDER BY, GROUP BY, DISTINCT.

Prefer composite indexes over multiple single indexes.

Join columns.

5. Choose index column order wisely

Place the most selective column on the left.

Prefer shorter columns on the left.

Put the most frequently used column on the left.

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

7. Prefer covering indexes for frequent queries

A covering index contains all fields used by the query (WHERE, SELECT, ORDER BY, GROUP BY).

Benefits: eliminates secondary‑index lookups and converts random I/O to sequential I/O.

8. Index SET guidelines

Avoid foreign key constraints. Use indexes on related columns; enforce referential integrity at the application level to avoid write performance penalties.

SQL Development Standards

1. Use prepared statements

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

2. Avoid implicit type conversion

Implicit conversion can invalidate indexes.

select name,phone from customer where id = '111';

3. Leverage existing indexes

Avoid leading wildcards (e.g., a LIKE '%123%') which prevent index usage.

4. Prefer explicit column lists over SELECT *

Reduces CPU, I/O, and enables covering indexes.

5. Always specify column list in INSERT statements

insert into t(c1,c2,c3) values ('a','b','c');

6. Replace subqueries with JOINs when possible

Subqueries often materialize temporary tables without indexes, harming performance.

7. Limit the number of tables joined

MySQL has a join buffer; joining many tables increases memory usage and can cause out‑of‑memory errors.

8. Batch large write operations (UPDATE, DELETE, INSERT)

Splitting >1 million row operations reduces master‑slave replication lag and log volume.

9. Use IN instead of multiple OR for the same column

Keep IN list under 500 values for optimal index usage.

10. Avoid ORDER BY RAND() for random sorting

It loads all rows into memory; generate a random value in the application instead.

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

Example of bad practice:

where date(create_time)='20190101'

Recommended:

where create_time >= '20190101' and create_time < '20190102'

12. Use UNION ALL when duplicate rows are impossible

UNION

removes duplicates via a temporary table; UNION ALL skips this step.

13. Split complex large SQL statements into smaller ones

Large statements consume a single CPU; splitting enables parallel execution.

Database Operation Behavior Standards

1. Batch large‑scale writes (over 1 million rows)

Large batches cause replication lag, massive binlog generation, and 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 locking and replication delay.

3. Do not grant SUPER privilege to application accounts

Reserve SUPER for DBA accounts only.

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

Accounts should access only one database and not have DROP privileges.

Source: https://www.cnblogs.com/xzsj/p/gzxzsj-database-mysql.html