Essential MySQL Database Standards: Table, SQL, and Index Guidelines

Background

Because of job responsibilities, a set of database standards for the backend team was created, refined over several versions, and has been in use for more than half a year. Sharing these guidelines helps reduce inappropriate table definitions, erroneous SQL, and wrong indexes during development.

The standards are divided into three parts: table creation rules, SQL rules, and index rules. Each item has a mandatory or recommended level, allowing teams to adapt them to their own context.

1. Table Creation Standards

Mandatory (1) Storage engine must be InnoDB

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

Mandatory (2) Each table must have a primary key ID using an auto‑increment value (short if possible) unless in a sharding environment

Explanation: InnoDB requires a primary key; a monotonically increasing ID improves insert performance, reduces page splits and fragmentation, and in sharding scenarios the 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 real UTF‑8 encoding.

Mandatory (4) Database tables and columns must have Chinese comments

Explanation: Do not be lazy.

Mandatory (5) Database, table, and column names must be lowercase, snake_case, no longer than 32 characters, meaningful, and must not mix Chinese and English

Explanation: Naming convention.

Mandatory (6) Columns per table must be fewer than 30; if more, consider splitting the table

Explanation: Too many columns increase the mapping cost between MySQL and InnoDB.

Mandatory (7) Foreign keys are prohibited; integrity must be handled in the application layer

Explanation: Foreign keys couple tables; UPDATE and DELETE affect related tables, hurting performance and potentially causing deadlocks.

Mandatory (8) Columns must be defined as NOT NULL and provide default values

Explanation: NULL columns complicate indexing, increase storage, and reduce query performance.

Mandatory (9) Reserved words such as DESC , RANGE , MARCH must not be used

Explanation: Refer to MySQL official reserved words.

Mandatory (10) Use CHAR fixed‑length strings when stored string lengths are almost equal

Explanation: Reduces space fragmentation and saves storage.

Recommended (11) Consider using TIMESTAMP instead of DATETIME in some scenarios

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

Recommended (12) Be cautious of automatically generated schemas; manually write them

Explanation: Do not trust some database clients too much.

2. SQL Standards

Recommended (1) Do not use custom functions, stored functions, or user variables to allow query caching

Explanation: Queries containing user‑defined functions, stored functions, user variables, temporary tables, or system tables are not cached. Functions like NOW() or CURRENT_DATE() return different results each time.

Mandatory (2) Specify required columns in SELECT instead of using *

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

Mandatory (3) Implicit type conversion is not allowed

Explanation: For example, querying a phone number column with an unquoted numeric literal prevents the index from being used, causing a full table scan.

Recommended (4) Do not use functions or expressions on columns in WHERE conditions

Explanation: MySQL cannot parse such expressions, so indexes cannot be used.

Mandatory (5) Prohibit foreign keys and cascade; handle all foreign‑key logic in the application layer

Explanation: Foreign keys and cascade are suitable for single‑machine low‑concurrency scenarios, not for distributed high‑concurrency clusters; they cause blocking, update storms, and slow inserts.

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

Mandatory (7) Do not use leading % wildcard in LIKE patterns

Explanation: Leading % prevents index usage; use Elasticsearch for full‑text search.

3. Index Standards

Recommended (1) Avoid creating separate indexes on columns that are frequently updated and have low cardinality

Explanation: Low‑cardinality columns provide little benefit, and frequent updates increase index maintenance cost.

Mandatory (2) JOINs should involve no more than five tables; join columns must have identical data types; ensure indexed join columns

Explanation: Too many tables make the optimizer’s plan selection difficult; data type consistency is required.

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

Explanation: The first column alone can locate rows, making the remaining columns redundant.

Mandatory (4) In composite indexes, place higher‑cardinality columns on the left

Explanation: Higher‑cardinality leftmost column filters data early, improving index efficiency; when writing WHERE clauses, align condition order with index order.

Recommended (5) Use covering indexes to avoid table lookups

Explanation: Covering queries retrieve all needed data from the index; avoid SELECT * to enable covering indexes.

Recommended (6) When indexing long VARCHAR columns, specify index length based on discriminative power

Explanation: Choose an index length that provides sufficient cardinality, e.g., using SELECT COUNT(DISTINCT LEFT(col,20))/COUNT(*) FROM table to evaluate.

Recommended (7) For ORDER BY queries, leverage index ordering; ensure the ORDER BY column is the last part of a composite index

Explanation: If a range condition appears before the ORDER BY column, the index’s ordering cannot be used.

Recommended (8) Columns used in WHERE must not be part of an expression or function argument

Explanation: Indexed columns inside expressions prevent index usage.

Recommended (9) Only one range condition can use an index; additional range conditions will not use indexes

Recommended (10) In outer joins, the join fields’ types must be exactly the same

References

High Performance MySQL, 3rd Edition

Alibaba Java Development Manual