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

Background

A set of database standards was created for a backend team to reduce inappropriate CREATE TABLE statements, erroneous SQL, and poor indexes. The standards have been applied across product lines for over half a year.

1. Table Specification

Mandatory (1) Use InnoDB storage engine InnoDB provides transactions, row‑level locking, and better concurrency, improving CPU and memory cache utilization.

Mandatory (2) Every table must have a primary key ID using an auto‑increment key (as short as possible) unless in a sharding environment InnoDB requires a primary key; a monotonic auto‑increment key improves insert performance, reduces page splits and fragmentation. In sharding scenarios, a globally unique key prevents duplicate primary keys.

Mandatory (3) Use utf8mb4 character set utf8mb4 provides true UTF‑8 support, unlike MySQL's legacy utf8.

Mandatory (4) Add Chinese comments to tables and columns Improves documentation and readability.

Mandatory (5) Database, table, and column names must be lowercase, underscore‑separated, ≤32 characters, meaningful, and avoid mixing pinyin with English Enforces naming consistency.

Mandatory (6) Limit a table to fewer than 30 columns; split the table if exceeded Too many columns increase MySQL’s mapping cost between storage pages and result sets.

Mandatory (7) Prohibit foreign keys; enforce referential integrity in the application layer Foreign keys cause table coupling, increase UPDATE/DELETE overhead, can lead to deadlocks, and degrade performance.

Mandatory (8) Define columns as NOT NULL and provide default values NULL columns complicate index statistics, increase storage overhead, and make optimization harder.

Mandatory (9) Disallow reserved words such as DESC, RANGE, MARCH, etc. Prevents syntax conflicts; refer to MySQL’s official reserved‑word list.

Mandatory (10) Use CHAR for fixed‑length strings when stored lengths are almost identical Reduces space fragmentation and saves storage.

Suggested (11) Consider TIMESTAMP instead of DATETIME in some scenarios TIMESTAMP occupies 4 bytes, stores values from 1970‑2038 and is timezone‑aware; DATETIME occupies 8 bytes, is timezone‑agnostic, and supports a wider range (1001‑9999).

Suggested (12) Manually write all schemas instead of relying on auto‑generated ones Reduces hidden errors from database client tools.

2. SQL Specification

Suggested (1) Avoid custom functions, stored functions, or user variables to enable query caching Queries containing user‑defined functions, stored functions, user variables, temporary tables, or MySQL system tables cannot be cached (e.g., NOW() or CURRENT_DATE()).

Mandatory (2) Specify required columns in SELECT instead of using "*" Fetching unnecessary columns increases CPU, I/O, and network usage and prevents effective use of covering indexes.

Mandatory (3) Disallow implicit type conversion in queries Implicit conversion can cause index loss, leading to full table scans (e.g., comparing a numeric column to an unquoted number).

Suggested (4) Avoid using functions or expressions on indexed columns in WHERE MySQL cannot use the index when the column appears inside a function or expression.

Mandatory (5) Prohibit foreign keys and cascade operations; handle them in the application layer Foreign keys and cascade updates are unsuitable for distributed, high‑concurrency clusters; they can cause blocking and slow inserts.

Suggested (6) Avoid using OR in WHERE clauses; consider UNION ALL instead OR can prevent index usage; UNION ALL may be a better alternative.

Mandatory (7) Disallow leading‑wildcard ("%…") LIKE patterns Leading wildcards cannot use indexes; consider using Elasticsearch for such searches.

3. Index Specification

Suggested (1) Do not create separate indexes on columns that are frequently updated and have low cardinality Low‑cardinality indexes provide little benefit, while frequent updates increase maintenance cost.

Mandatory (2) Limit JOINs to at most five tables; ensure joined columns have identical data types and are indexed Too many JOINs overwhelm the optimizer; mismatched column types prevent index usage.

Mandatory (3) In a composite index, if the first column’s cardinality is 1, the composite index is unnecessary The first column already uniquely identifies rows, making additional columns redundant.

Mandatory (4) Place higher‑cardinality columns on the left side of a composite index This allows early filtering of irrelevant rows and improves index efficiency; align WHERE clause order with index order.

Suggested (5) Use covering indexes to avoid back‑table lookups A covering query retrieves all needed data from the index alone; look for "using index" in EXPLAIN’s Extra column.

Suggested (6) When indexing long VARCHAR columns, specify an appropriate prefix length instead of the full column Determine prefix length based on actual text cardinality (e.g., SELECT COUNT(DISTINCT LEFT(col, N))/COUNT(*) to evaluate).

Suggested (7) Leverage index ordering for ORDER BY; ensure ORDER BY columns are the last part of a composite index This enables MySQL to sort using the index and avoid filesort.

Suggested (8) Do not place indexed columns inside expressions or as function arguments in WHERE MySQL cannot use the index in such cases.

Suggested (9) For range queries, only the leftmost range condition can use an index; subsequent ranges are ignored MySQL applies the first range condition but not the following ones.

Suggested (10) Ensure that join columns across tables have exactly the same type, length, charset, and collation Any mismatch prevents index usage.