Master MySQL Naming, Field, and Index Standards for Scalable Databases

1. Naming Conventions

Table names (mandatory) : lowercase letters, numbers, underscores; max 64 characters; singular form.

Temporary tables: prefix tmp and suffix a timestamp, e.g. tmp_user_202404161200.

Backup tables: prefix bak and suffix a timestamp, e.g. bak_user_202404161200.

Database name pattern: <system>_<module> (module part optional).

Regular table name pattern: <db>_<table>. Typical examples:

Batch table : batch_import_user Log/flow table : user_operation_log or account_trans_flow Parent‑child tables : order and order_item Extension table : user_ext Chain table : user_level_chain Config/dictionary tables : sys_config, sys_dict Relation table : user_role_relation Summary table : daily_sales_summary Task queue : task_queue or job_queue Version/history table : contract_version Audit table : data_change_audit Geospatial table : store_location Tree‑structure table : department or category Key‑Value vertical table : product_attributes Bitmap table :

user_flags

2. Field Design Standards

All related columns must share the same data type, length, charset, and collation to avoid index loss.

Never allow NULL; define columns as NOT NULL and provide sensible defaults (e.g., empty string, 0).

Choose the smallest possible type. Example: ages 0‑200 → TINYINT UNSIGNED instead of INT.

Prefer integer types over IP, enum, string, or floating‑point types when the value is numeric.

Use UNSIGNED for all non‑negative numbers.

Boolean flags: UNSIGNED TINYINT (1 = true, 0 = false).

Every table must contain at least four core columns: id, create_time, update_time, delete_flag. Add create_user / update_user for configuration tables and tenant_id for multi‑tenant scenarios.

Do not use ENUM; replace with TINYINT or VARCHAR.

String columns : fixed length → CHAR; variable length → VARCHAR. Keep VARCHAR length minimal; use TEXT for large text and consider moving it to an extension table.

Numeric columns : select integer size ( TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) based on range; use DECIMAL for precise calculations (e.g., money), FLOAT/DOUBLE for approximations.

Date/Time columns : prefer DATETIME (larger range than TIMESTAMP). Be aware of TIMESTAMP limits (1970‑2038) and timezone handling.

Primary key must be a single‑column, simple integer, immutable. Use AUTO_INCREMENT or UUID (consider storage and index impact). id should be BIGINT UNSIGNED (never INT).

Large text/BLOB data should be stored in separate tables; never store images, static files, or large binaries directly—store only URLs or file paths.

3. Index Design Standards

Principle : indexes accelerate reads but degrade writes; create indexes only for columns used in WHERE, JOIN, ORDER BY, or GROUP BY.

Naming convention :

Primary key → pk_<table>_<column> Unique index → uk_<table>_<column> Normal index → idx_<table>_<column> Composite indexes : follow the left‑most prefix rule; place high‑selectivity columns first; avoid redundant indexes (e.g., if (a,b) exists, do not add (a)).

Prefix indexes : for long strings, index a prefix such as INDEX (col(10)), ensuring sufficient selectivity.

Unique indexes : use sparingly; single‑field unique indexes are fine for business keys (order numbers, serials). Avoid multi‑column unique indexes that cause gap locks during batch inserts.

Usage tips :

Do not apply functions or expressions on indexed columns.

Avoid range queries ( <>, NOT IN, leading LIKE) on indexed columns.

Never store NULL in indexed columns; use defaults instead.

Limit total indexes per table to ≤5 and fields per composite index to ≤5.

Low‑cardinality columns (e.g., gender) rarely benefit from indexing.

Do not index columns that never appear in query predicates.

Prefer a “most‑used composite + single‑column + index‑merge” strategy; MySQL 5.7+ can automatically merge indexes.

Validate indexes with EXPLAIN. Look for type = index_merge and Extra containing Using union/intersect.

4. Table Design Standards

All database objects (tables, columns, indexes) must have up‑to‑date comments.

Default charset: utf8mb4; default collation: utf8mb4_general_ci. Table and column charsets must match.

Control object count: ≤500 tables per database; ≤30 columns per table (hard limit 50); ≤20 million rows or 20 GB per table (reference values).

5. Additional Development Guidelines

Use the InnoDB engine (transactional, row‑level locking).

Avoid triggers, stored procedures, and partitioning for easier maintenance.

Do not rely on foreign‑key constraints; enforce relationships in application code.

Limit IN list size to ≤100 (absolute max ≤1000) and avoid OR in WHERE clauses.

Never use SELECT *; always specify required columns. Example: SELECT `blog_id` FROM `blog`; Prefer UNION ALL over UNION to skip duplicate elimination.

For fuzzy searches, avoid leading wildcards; use right‑anchored LIKE 'xxx%' to keep index usage.

Avoid reverse queries ( NOT, !=, NOT IN, etc.) as they trigger full scans.

Never rely on implicit type conversion; mismatched types (e.g., comparing an INT column to a string) invalidate indexes.

When joining large tables, ensure join columns are indexed; limit multi‑table joins to three tables when possible and drive joins with the smaller table.

Batch inserts: ≤1000 rows per statement; batch updates ≤200 rows; paginate queries with page size ≤1000 (≤2000 for batch processing).

Use @Transactional for declarative transactions; avoid programmatic transaction management.

DDL must be performed by DBAs, not from application code. Combine multiple changes to the same table into a single statement.

Aggregation: use COUNT(1) or COUNT(*) instead of COUNT(column) for better performance.

Connect to databases via domain names, never raw IP addresses.

6. Large‑Table Governance

Archive historical data that is no longer needed for operational queries.

Clean up logically deleted or unused rows regularly.

Separate hot (frequently accessed) and cold (infrequently accessed) data; move cold data to a historical data platform.

Apply horizontal sharding (by business key) or vertical splitting (by column groups) for very large tables.

Remove unused indexes and periodically run OPTIMIZE TABLE to defragment.

7. Binlog Delay Mitigation

Avoid table‑level synchronization tools (e.g., Otter, Canal); prefer APIs, message queues, or file transfer for data propagation.

Split rarely updated large columns into separate tables to reduce binlog volume.

Cache rapidly changing intermediate values instead of writing them immediately.

Prefer UPDATE over DELETE + INSERT for mutable rows (e.g., chain tables).

Separate core fields from extension fields to keep binlog size small.

Use TRUNCATE for bulk table clearing to minimize binlog generation.