Essential MySQL Database Design Standards for Reliable Production Systems

Background and Purpose

MySQL offers specific strengths and weaknesses compared with Oracle, SQL Server, and other RDBMSs. This guide defines standards for R&D, QA, and Operations to design stable online databases, covering change management, table design, and SQL writing.

Design Standards

Database Naming

Mandatory Database name ≤32 characters and reflect join relationships (e.g., user and user_login).

Mandatory Format: businessSystem_subsystem; tables in the same module share a common prefix.

Mandatory Sharding pattern: dbPattern_001 (incremental) or dbPattern_YYYYMMDD for time‑based shards.

Mandatory Create database with explicit charset utf8 or utf8mb4.

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8;

Table Structure

Mandatory Table and column names ≤32 characters, lowercase, letters/numbers/underscores only.

Mandatory Table name must include module prefix (e.g., sz_ for teacher system, qd_ for channel system).

Mandatory Specify charset utf8 / utf8mb4 on CREATE TABLE.

Mandatory Use InnoDB storage engine unless DBA approves otherwise.

Mandatory Every table must have a COMMENT.

Recommended Primary key named id of type int or

bigint

AUTO_INCREMENT

. Business fields should be defined as UNIQUE keys (e.g., user_id).

Recommended Include create_time and update_time columns on core tables.

Recommended Define all columns as NOT NULL with appropriate DEFAULT values.

Recommended Large BLOB / TEXT fields should be split into separate tables.

Recommended Denormalize frequently joined fields (e.g., store user_name in user_account).

Mandatory Intermediate tables start with tmp_; backup tables start with bak_ and are cleaned regularly.

Mandatory Alter tables larger than 1 M rows only after DBA review and during low‑traffic windows.

Column Type Optimization

Recommended Use BIGINT for auto‑increment columns to avoid overflow.

Recommended Use TINYINT or SMALLINT for low‑cardinality status fields.

Recommended Store IPv4 addresses as INT and convert with INET_ATON() / INET_NTOA() (or PHP ip2long() / long2ip()).

Recommended Avoid ENUM / SET; prefer integer types.

Recommended Use VARCHAR for text, length ≤2700 characters; avoid CHAR for variable data.

Recommended Store monetary values as integer cents ( INT) instead of DOUBLE.

Recommended Prefer TIMESTAMP (4 bytes) over DATETIME (8 bytes); alternatively store Unix epoch as INT using UNIX_TIMESTAMP() and FROM_UNIXTIME().

Index Design

Mandatory All InnoDB tables must have a primary key such as id INT/BIGINT AUTO_INCREMENT that never changes.

Recommended Naming convention: primary key pk_, unique key uk_ or uq_, regular index idx_, all lowercase.

Mandatory Use BTREE for InnoDB/MyISAM; HASH or BTREE for MEMORY tables.

Mandatory Single index record length ≤64 KB.

Recommended Limit indexes per table to ≤7.

Recommended Build composite indexes with the most selective columns first.

Recommended Ensure join columns on the driving table are indexed.

Recommended Remove redundant indexes (e.g., if KEY(a,b) exists, drop KEY(a)).

Partitioning and Sharding

Mandatory Partition key must be indexed or be the first column of a composite index.

Mandatory Max partitions per table (including sub‑partitions) ≤1024.

Mandatory Define creation and cleanup policies for partitioned tables before release.

Mandatory Queries on partitioned tables must include the partition key.

Recommended Partition file ≤2 GB, total size ≤50 GB, total partitions ≤20.

Mandatory Alter partitioned tables only during low‑traffic periods.

Mandatory Max sharded databases ≤1024; max tables per sharded database ≤4096.

Recommended Single partitioned table ≤5 M rows, .ibd file ≤2 GB.

Recommended Horizontal sharding via modulo; date‑based sharding for logs/reports.

Character Set Consistency

Mandatory Database, tables, and columns must use the same charset: utf8 or utf8mb4.

Mandatory Application layer and environment variables must match the database charset.

DAO Design Recommendations

Recommended Prefer manual SQL with bound variables over ORM models for performance.

Recommended Implement connection timeout and retry logic for MySQL/Redis clients.

Recommended Propagate native MySQL/Redis error messages for easier debugging.

Recommended Configure connection pool sizes (initial, min, max) and timeout based on workload.

Recommended Provide cleanup or archiving plans for large log/history tables.

Recommended Keep replication lag ≤20 s; read from master for latency‑sensitive operations.

Recommended Use primary‑key‑based updates to minimize row and table locks.

Recommended Keep lock acquisition order consistent across services to avoid deadlocks.

Recommended Cache hot rows/columns (e.g., via Memcached or Redis) when read/write ratio >10:1.

Example CREATE TABLE

CREATE TABLE user (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL COMMENT 'User ID',
  `username` VARCHAR(45) NOT NULL COMMENT 'Real name',
  `email` VARCHAR(30) NOT NULL COMMENT 'Email address',
  `nickname` VARCHAR(45) NOT NULL COMMENT 'Nickname',
  `avatar` INT NOT NULL COMMENT 'Avatar ID',
  `birthday` DATE NOT NULL COMMENT 'Birthday',
  `sex` TINYINT DEFAULT 0 COMMENT 'Gender',
  `short_introduce` VARCHAR(150) DEFAULT NULL COMMENT 'One‑line intro',
  `user_resume` VARCHAR(300) NOT NULL COMMENT 'Resume URL',
  `user_register_ip` INT NOT NULL COMMENT 'Registration IP (stored as int)',
  `create_time` TIMESTAMP NOT NULL COMMENT 'Record creation time',
  `update_time` TIMESTAMP NOT NULL COMMENT 'Record update time',
  `user_review_status` TINYINT NOT NULL COMMENT 'Review status (1‑4)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_username` (`username`),
  KEY `idx_create_time` (`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Website user basic information';

SQL Writing Guidelines

DML Statements

Mandatory SELECT must list explicit columns; * is prohibited.

Mandatory INSERT must list columns; INSERT INTO t1 VALUES(...) is prohibited.

Recommended Batch INSERT syntax: INSERT INTO ... VALUES (..),(..),(..) with ≤5000 rows per batch.

Recommended Prefer UNION ALL over UNION; limit UNION clauses to ≤5.

Recommended IN‑list size ≤500 to reduce full scans.

Recommended Batch updates in transactions should be small; optionally insert SLEEP (5‑10 s) between batches.

Mandatory Transactions must involve only InnoDB tables.

Mandatory Write and transaction traffic go to the master; read‑only queries go to slaves.

Mandatory DML on static or tiny tables (<100 rows) may omit WHERE; otherwise WHERE is required and must use indexed columns.

Mandatory Production must not use optimizer hints such as SQL_NO_CACHE, FORCE INDEX, etc.

Mandatory WHERE clause field types must match to enable index usage.

Multi‑Table Joins

Mandatory Cross‑database joins are prohibited.

Mandatory UPDATE statements must not contain joins.

Recommended Avoid subqueries; replace with multiple queries or joins.

Recommended Limit join count to ≤3 tables in production.

Recommended Use table aliases and qualify column names (e.g., SELECT a FROM db1.table1 t WHERE ...).

Recommended Drive joins with the smallest result set.

Transactions

Recommended Limit rows affected by INSERT/UPDATE/DELETE/REPLACE to ≤2000; IN‑list size ≤500.

Recommended Concurrency on auto‑increment columns should be ≤200.

Mandatory Use REPEATABLE-READ isolation level in production.

Recommended Keep the number of statements in a transaction ≤5 (except payment flows).

Recommended Update statements should target primary or unique keys to avoid gap locks.

Recommended Move external calls (web services, file storage) out of transactions.

Recommended For latency‑sensitive reads, force master access.

Sorting and Grouping

Recommended Minimize ORDER BY, GROUP BY, and DISTINCT; push sorting to the application when possible.

Recommended When needed, let indexes produce sorted results (e.g., WHERE a=1 ORDER BY ... using KEY(a,b)).

Recommended Result sets for such queries should stay ≤1000 rows for acceptable performance.

Prohibited SQL in Production

High‑Risk Disallow UPDATE|DELETE ... WHERE ... LIMIT ... without ordering by primary key.

High‑Risk Disallow correlated subqueries in UPDATE statements.

Mandatory Prohibit procedures, functions, triggers, views, events, and foreign key constraints; implement logic in application code.

Mandatory Disallow INSERT ... ON DUPLICATE KEY UPDATE in high‑concurrency scenarios.

Mandatory Disallow multi‑table UPDATE statements (e.g., UPDATE t1,t2 ...).

Source: https://github.com/jly8866/archer/blob/master/src/docs/mysql_db_design_guide.md