Database Design and SQL Best‑Practice Guidelines

Background

Because of my job responsibilities, I was tasked with creating a set of database standards for the backend team. After several revisions the text below became the official guideline, which has been enforced for more than half a year and helps reduce inappropriate table‑creation statements, erroneous SQL, and wrong indexes.

1. Table‑Creation Guidelines

【Mandatory】(1) Storage Engine – Must use InnoDB

Explanation: InnoDB supports transactions, row‑level locking, and better concurrency. CPU and memory cache page optimizations also increase resource utilization.

【Mandatory】(2) Every table must have a primary key named ID and use an auto‑incrementing integer (short when possible), unless operating in a sharding environment.

Explanation: InnoDB requires a primary key; a monotonically increasing ID improves insert performance, reduces page splits, and lowers table fragmentation. In sharding scenarios the primary key must be allocated centrally to avoid duplicates.

【Mandatory】(3) Must use the utf8mb4 character set

Explanation: MySQL’s utf8 is not true UTF‑8; utf8mb4 is the proper UTF‑8 implementation.

【Mandatory】(4) All tables and columns must include Chinese comments

Explanation: Encourage documentation and avoid laziness.

【Mandatory】(5) Database, table, and column names must be lowercase, use snake_case, be no longer than 32 characters, be self‑descriptive, and must not mix pinyin with English.

Explanation: Enforce naming conventions.

【Mandatory】(6) A single table should have fewer than 30 columns; if more, consider splitting the table.

Explanation: Too many columns increase MySQL’s mapping cost and degrade performance.

【Mandatory】(7) Do not use foreign keys; if referential integrity is needed, handle it in the application layer.

Explanation: Foreign keys create coupling, cause performance‑impacting UPDATE and DELETE operations, and can lead to deadlocks.

【Mandatory】(8) All fields must be defined as NOT NULL and provide a default value.

Explanation: NULL columns complicate index statistics, increase storage overhead, and degrade query performance.

【Mandatory】(9) Prohibit the use of reserved words such as DESC , RANGE , MATCH , etc.; refer to MySQL’s official reserved‑word list.

【Mandatory】(10) If stored string lengths are almost equal, use the fixed‑length CHAR type.

Explanation: Fixed‑length strings reduce fragmentation and save space.

【Recommended】(11) In some scenarios consider using TIMESTAMP instead of DATETIME .

Explanation: TIMESTAMP occupies 4 bytes and covers 1970‑2038, while DATETIME occupies 8 bytes and covers 1001‑9999; TIMESTAMP is timezone‑aware.

【Recommended】(12) Be cautious of automatically generated Schema ; manually write all schemas.

Explanation: Do not trust database clients blindly.

2. SQL Writing Guidelines

【Recommended】(1) To fully utilize cache, do not use custom functions, stored functions, or user variables.

Explanation: Queries containing user‑defined functions, stored functions, user variables, temporary tables, or MySQL system tables cannot be cached. For example, NOW() or CURRENT_DATE() produce different results at different times.

【Mandatory】(2) Specify required columns in SELECT statements instead of using “*”.

Explanation: Selecting unnecessary columns increases CPU, I/O, and network consumption and prevents effective use of covering indexes.

【Mandatory】(3) Do not rely on implicit type conversion.

Explanation: Implicit conversion can cause index loss, leading to full‑table scans.

【Recommended】(4) Do not use functions or expressions on indexed columns in WHERE clauses.

Explanation: MySQL cannot parse such expressions, so the index is not used.

【Mandatory】(5) Prohibit foreign‑key cascades; handle all referential logic in the application layer.

Explanation: Cascades are unsuitable for distributed, high‑concurrency environments and can cause update storms.

【Recommended】(6) Avoid using OR in WHERE clauses; consider UNION ALL instead.

【Mandatory】(7) Do not use leading‑wildcard patterns (e.g., %foo ) in LIKE queries.

Explanation: Leading wildcards prevent index usage; consider using Elasticsearch for such searches.

Index Guidelines

【Recommended】(1) Avoid creating separate indexes on columns with low cardinality that are frequently updated.

Explanation: Low‑cardinality indexes provide little benefit and increase maintenance cost.

【Mandatory】(2) A JOIN should involve no more than five tables; joined fields must have identical data types, and indexed fields should be used for the join.

Explanation: Too many joins make the optimizer’s plan search space explode; mismatched types prevent index usage.

【Mandatory】(3) In a composite index, if the first column’s cardinality is 1, the composite index is unnecessary.

【Mandatory】(4) When creating a composite index, place the column with higher cardinality on the left.

Explanation: Higher‑cardinality leftmost columns filter data earlier, improving index efficiency. When writing WHERE clauses, try to match the index column order.

【Recommended】(5) Use covering indexes to avoid back‑table lookups.

Explanation: When EXPLAIN shows "using index", the query can be satisfied entirely from the index. Avoid SELECT * to enable covering indexes.

【Recommended】(6) For long VARCHAR columns (e.g., VARCHAR(100) ), specify an appropriate prefix length for the index based on actual data selectivity.

Explanation: Use a query such as

SELECT COUNT(DISTINCT LEFT(lesson_code,20))/COUNT(*) FROM lesson;

to decide the optimal prefix length.

【Recommended】(7) When using ORDER BY , ensure the ordering column is part of a suitable index and appears as the last column in the composite index to avoid file‑sort.

Explanation: Example: WHERE a=? AND b=? ORDER BY c can use index a_b_c. Range conditions on the leading column break index ordering.

【Recommended】(8) Columns used in WHERE must not be wrapped in functions or expressions; otherwise the index cannot be used.

【Recommended】(9) Only one range condition can be used effectively in a multi‑condition WHERE clause; additional range conditions will not use indexes.

【Recommended】(10) When joining multiple tables, the join columns must have exactly the same type, length, charset, and collation.

References

High Performance MySQL (3rd Edition)

Alibaba Java Development Manual

PS: If you find this sharing useful, feel free to like and read it.