MySQL Table Design Guide: Naming Conventions, Index Optimization & Production SQL Templates
This comprehensive guide covers MySQL table creation best practices including naming conventions, field type selection, index design principles, constraint strategies, and solutions for large-table performance, JSON querying, and logical deletion conflicts, with production-ready SQL templates and interview-focused insights.
MySQL Table Creation: What to Pay Attention To
Naming Conventions (Big Tech Red Lines, Basic but Essential)
Unify team standards to avoid maintenance disasters. Core principles:
All lowercase with underscore separators; prohibit camelCase and Chinese characters.
Table name prefix unified (e.g., t_) to avoid MySQL keyword conflicts.
Self-explanatory names; prohibit meaningless abbreviations (e.g., u_id → user_id).
Index name unified prefixes: normal index idx_xxx, unique index uk_xxx.
Examples of wrong vs correct usage:
Wrong: UserInfo → Correct: user_info (all lowercase, underscore separated)
Wrong: order → Correct: t_order (avoid MySQL keywords)
Wrong: u_name → Correct: user_name (self-explanatory, no abbreviations)
Wrong: createTime → Correct: create_time (camelCase to underscore, unified style)
Field Design (First Line of Defense for Performance)
Most overlooked but highest impact. Core principle: use the smallest data type that satisfies business needs .
Examples of wrong vs correct usage:
Wrong: int(1) for status → Correct: tinyint(1) — saves 3 bytes/row, 3MB per million rows
Wrong: char(20) for phone → Correct: varchar(20) — variable-length storage, significant space savings
Wrong: timestamp for time → Correct: datetime(6) — timestamp has 2038 overflow + timezone issues
Wrong: varchar(255) for all strings → Correct: set length as needed — overlong causes row overflow, hurts performance
Wrong: NULL for defaults → Correct: 0/''/special values — NULL causes index failure, statistics errors
Additional Points:
Large fields (text/blob) split into separate tables to avoid row overflow and query degradation.
Enum types discouraged: schema change required for extension; use tinyint + dictionary table instead.
Primary key must be bigint to avoid int overflow (single table >2.1B rows breaks).
Index Design (Core Interview Topic)
Indexes are the soul of MySQL performance; plan during table creation. Core principle: few but precise, create on demand .
Must have primary key: recommend auto-increment bigint or snowflake ID; absolutely no tables without PK.
Composite indexes follow leftmost prefix principle ; high-selectivity columns first.
Avoid redundant indexes: if idx(a,b) exists, no need for idx(a).
Limit index count: single table ≤5 indexes; excess severely hurts write performance.
Prefer covering indexes to avoid table lookups.
Table Structure Optimization (Preempt Performance Bottlenecks)
Single table rows ≤20 million ; beyond that, plan sharding early.
Cold/hot data separation: archive historical data to history tables; main table stores only hot data.
Vertical split: move infrequently used large/low-frequency fields to extension tables.
Avoid wide tables: column count ≤30; excess impacts query efficiency.
Constraints & Security (Data Consistency Guarantees)
Not-null constraints: all business fields should be non-null with defaults.
Unique constraints: ensure data uniqueness, prevent dirty data.
Foreign keys discouraged : internet giants rarely use them; consistency enforced at business layer (avoid deadlocks, cascade updates).
Charset unified to utf8mb4 for emoji and full Chinese support.
Collation utf8mb4_unicode_ci more accurate than utf8mb4_general_ci.
Never store plaintext passwords; must use bcrypt/Argon2 encryption.
Special Scenario Design (Reflects Real-World Experience)
Soft delete: unified is_deleted tinyint(1) default 0, auto-filter in queries.
Auto timestamps:
create_time datetime(6) default current_timestamp(6),
update_time datetime(6) default current_timestamp(6) on update current_timestamp(6)Optimistic lock: version int default 0 to prevent concurrent update conflicts.
Audit fields: create_by, update_by record operator for troubleshooting.
Interview Bonus Points (Gap-Widening Topics)
Pre-plan sharding strategy, e.g., hash by user ID.
Know lock-free schema change tools: pt-online-schema-change, gh-ost.
Monitor index cardinality; avoid indexes on low-cardinality columns (e.g., gender).
Understand implicit conversion causing index failure (e.g., varchar column queried with number).
Use EXPLAIN to analyze execution plans, verify index usage.
Pitfall Blacklist (Absolute No-Go Mistakes)
Using text/blob as PK or index column.
Indexing every column (over-indexing).
Wrong composite index column order causing failure.
Using NULL as default value.
Using char for variable-length strings.
Using timestamp for timestamps.
Big Tech Standard Table Template (Core Code + Technical Highlights)
Below is a production-validated standard CREATE TABLE SQL used in real projects with ten-million-row verification; all technical highlights annotated in comments:
CREATE TABLE `t_user` (
-- Highlight 1: PK must be bigint unsigned, avoid int overflow (2.1B limit), unsigned doubles capacity
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID',
-- Highlight 2: Field length set as needed, no blind varchar(255)
`user_name` varchar(32) NOT NULL DEFAULT '' COMMENT 'Username',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT 'Mobile',
`email` varchar(64) NOT NULL DEFAULT '' COMMENT 'Email',
-- Highlight 3: Status uses tinyint + dictionary table, not enum (extension requires ALTER)
`status` tinyint NOT NULL DEFAULT 1 COMMENT 'User status: 1-normal, 2-disabled, 3-cancelled',
-- Highlight 4: datetime(6) replaces timestamp, solves 2038 overflow & timezone
`last_login_time` datetime(6) DEFAULT NULL COMMENT 'Last login time',
-- Highlight 5: Soft delete standard, 0-not deleted, 1-deleted
`is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT 'Is deleted',
-- Highlight 6: Auto timestamps, millisecond precision, DB-maintained, no app code needed
`create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Create time',
`update_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'Update time',
-- Highlight 7: Optimistic lock for concurrent updates
`version` int NOT NULL DEFAULT 0 COMMENT 'Optimistic lock version',
-- Highlight 8: Index naming standard, unique uk_xxx, normal idx_xxx
PRIMARY KEY (`id`),
UNIQUE KEY `uk_mobile` (`mobile`) COMMENT 'Mobile unique index',
UNIQUE KEY `uk_email` (`email`) COMMENT 'Email unique index',
-- Highlight 9: Composite index follows leftmost prefix, high-selectivity first
-- status low selectivity (3 values), create_time high, so order status+create_time
KEY `idx_status_create_time` (`status`, `create_time`) COMMENT 'Status+create_time composite index',
-- Highlight 10: Unified charset utf8mb4 for emoji/Chinese; collation unicode_ci more accurate
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User table';Additional Technical Highlights in Code
No foreign keys : all relations enforced at business layer; avoids deadlocks, cascade updates, distributed transaction issues (internet giant iron law).
All fields non-null + defaults : completely eliminates NULL-caused index failures, statistics errors, NPEs.
ENGINE=InnoDB : forces InnoDB for transactions, row locks, crash recovery.
COMMENT mandatory : every table and column must have clear comments for maintenance.
Production Core Technical Challenges & Solutions
Challenge 1: Ten-million-row online schema change locks table
Impact: Long lock time, business unavailable, potential DB avalanche
Solution: Use gh-ost or pt-online-schema-change: create shadow table → sync data → swap names, zero locks throughout
Challenge 2: PK uniqueness conflict after sharding
Impact: Auto-increment IDs repeat across shards, data overwrite
Solution: Prefer snowflake algorithm (64-bit: timestamp + machine ID + sequence); fallback: segment mode (Meituan Leaf), UUID (not recommended, unordered & space-heavy)
Challenge 3: NULL causing index failure
Impact: IS NULL/IS NOT NULL queries skip index, full table scan
Solution: Force all fields non-null + defaults with special values (0, '', -1) to represent empty state
Challenge 4: Implicit conversion causing index failure
Impact: Column type vs param type mismatch triggers auto-conversion, index lost
Solution: Unify column and query types; e.g., mobile is varchar, must use where mobile='13800138000', not numeric
Challenge 5: Large fields (text/blob) causing row overflow
Impact: Row exceeds InnoDB 16KB page, overflow pages, query performance drops 10x+
Solution: Split large fields to separate table (e.g., t_user_extend), query on demand, avoid SELECT *
Challenge 6: Composite index leftmost prefix trap
Impact: Index(a,b,c), query where b=? and c=? fails completely
Solution: Strictly follow leftmost prefix; equality columns first, range columns last
Challenge 7: Single table >20M rows
Impact: Query performance plummets, index maintenance cost spikes
Solution: Cold/hot separation: archive history to history tables; pre-plan sharding by time or user ID hash
Challenge 8: Over-indexing hurts writes
Impact: Single table >5 indexes, insert/update/delete performance drops 50%+
Solution: Follow "few but precise"; drop redundant indexes, merge duplicates (if idx(a,b) exists, drop idx(a))
Realistic Mock Interview
Mock Interview: MySQL Table Creation Considerations
Interviewer:
"You've handled many DB designs. Tell me directly, what do you focus on most when creating MySQL tables? No textbook answers, just real experience."
Candidate:
"Okay, I've summarized 7 must-grab points from project practice. Starting with basics:
1️⃣ Naming & comments must be standardized. Table names all lowercase with underscores, e.g., t_order. Every field forced to have COMMENT, table itself also commented; database becomes living documentation.
2️⃣ Field types chosen carefully . Status/type use TINYINT, money strictly DECIMAL, variable strings VARCHAR with proper length, time unified DATETIME. Almost all fields set NOT NULL with defaults to avoid NULL pitfalls."
Interviewer:
"Good, standards are detailed. What about indexes ? Many just add INDEX(user_id), INDEX(create_time) blindly. Your take?"
Candidate:
"Absolute hotspot. I require the team:
PK mandatory, InnoDB strongly recommends auto-increment BIGINT , inserts ordered, reduces page splits. Distributed scenarios use ordered snowflake IDs.
Normal indexes only for WHERE, ORDER BY, GROUP BY; composite indexes follow leftmost prefix , high-distinctness columns first.
Long strings use prefix indexes , e.g., KEY idx_desc(description(20)).
Every index must pass EXPLAIN before deployment; redundant indexes forbidden.
Also, I require every table to carry the metadata quartet : id, create_time, update_time, is_deleted. Makes soft delete, time tracking easy; works great with MyBatis-Plus."
Interviewer:
"Nice. You mentioned DATETIME for time, why not TIMESTAMP? And charset/engine defaults?"
Candidate:
" TIMESTAMP has timezone conversion issues and range only to 2038; DATETIME wider range, worry-free. Engine always InnoDB for transactions, row locks, crash recovery. Charset must be utf8mb4 + utf8mb4_general_ci, stores emojis 😂, never use fake three-byte utf8. These are usually locked at database creation to prevent omissions."
Interviewer:
"Good. Deeper: if table needs large text like article content, or user extension attributes that change often, how to design?"
Candidate:
" Large fields split out . Article content to separate t_article_content, one-to-one with main table via PK, avoids main table dragging TEXT fields. For extension attributes, use JSON type for flexibility. If need to search inside JSON, create a virtual column index . Also appropriate denormalization: redundantly store common fields to reduce joins. More hard-earned avoidances 🩸: never use foreign keys, no ext1,ext2 reserved columns, single table columns ≤20. If data volume estimated at tens of millions, pre-plan sharding or partitioning."
Interviewer:
"Solid theory. Can you show a table structure diagram to visualize your design habits?"
Candidate:
"Sure, typical user module design looks like this:"
📌 Main table holds high-frequency small fields; details split out large fields and JSON; linked by user_id but no physical FK. All essential fields present, both disciplined and flexible.
Interviewer:
"Theory solid. Talk is cheap; show me the actual CREATE TABLE SQL you use daily , preferably with highlights showing technical depth."
Candidate:
"No problem. Here's a recent e-commerce order table design covering most points discussed plus advanced usages."
-- Order main table: note table name, field comments, type selection, index design, metadata fields
CREATE TABLE t_order (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'PK ID',
order_no CHAR(32) NOT NULL COMMENT 'Order no, distributed ID generation',
user_id BIGINT UNSIGNED NOT NULL COMMENT 'User ID',
total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Order total amount',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Order status 0-pending 1-paid 2-cancelled',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Logical delete 0-normal 1-deleted',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_status_time (user_id, status, create_time) -- composite index covering common queries
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order table';
-- Order detail table: large field split, JSON extension + virtual column index
CREATE TABLE t_order_detail (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'PK ID',
order_id BIGINT UNSIGNED NOT NULL COMMENT 'Order ID',
product_snapshot JSON NOT NULL COMMENT 'Product snapshot at order time',
-- Extract frequently queried field from JSON, generate virtual column, index it
product_name VARCHAR(200) GENERATED ALWAYS AS (product_snapshot ->> '$.name') VIRTUAL,
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Logical delete',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
PRIMARY KEY (id),
KEY idx_order_id (order_id),
KEY idx_product_name (product_name) -- virtual column index for product name search
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order detail table';
-- If data volume exceeds 100M, consider partitioned table (by time range)
-- Syntax shown as optional (production needs specific evaluation)
ALTER TABLE t_order PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027)
);Technical Highlights:
GENERATED ALWAYS AS ... VIRTUALvirtual column + index perfectly solves need for both JSON flexibility and search performance.
Composite index idx_user_status_time precisely covers "my orders" filtered by status and sorted by time.
Partition syntax kept as optional ; transform directly when data volume grows.
Interviewer:
"SQL very standard, virtual column index used beautifully. In actual development, what technical challenges did this design face and how were they solved? "
Candidate:
"Pain points are real; I'll share the most representative ones."
Challenge 1: Composite Index "Leftmost Prefix" Pitfall
Symptom: Index KEY idx_user_status_time (user_id, status, create_time); querying only status = 1 or sorting only by create_time causes index failure, full table scan.
Cause: Composite index must match from leftmost column; skipping user_id to query later columns makes index unusable .
Solution:
Queries must include user_id; business-wise also reasonable.
If cross-user status query truly needed, add KEY idx_status (status) but it's redundant, weigh trade-offs.
Enforce EXPLAIN verification for all online SQL; team rule: index creation requires EXPLAIN screenshot on record .
-- ✅ Can use index
SELECT * FROM t_order WHERE user_id = 1001 AND status = 0 ORDER BY create_time DESC;
-- ❌ Cannot use index, key column NULL
SELECT * FROM t_order WHERE status = 0 ORDER BY create_time DESC;Challenge 2: Soft Delete + Unique Index Conflict
Symptom: order_no unique index; user cancels order (soft delete is_deleted=1). When they reorder with same order_no, unique constraint errors.
Cause: Soft delete only marks field; physical record remains, unique index cannot treat "deleted" as non-duplicate.
Solution:
Include is_deleted in unique index: UNIQUE KEY uk_order_no (order_no, is_deleted); allows ('NO123', 0) and ('NO123', 1) coexistence.
Better: design order_no with timestamp/sequence to avoid reuse.
Team ultimately chose latter: order_no globally unique, never recycled , completely avoids conflict.
Challenge 3: Large Table COUNT() Performance Disaster
Symptom: Tens of millions rows;
SELECT COUNT(*) FROM t_order WHERE user_id = ? AND status = 0takes seconds, high concurrency saturates CPU.
Cause: InnoDB has no cached row counter; each execution traverses index or table.
Solution:
Business often doesn't need exact count; use EXPLAIN SELECT ... for estimated rows, or Redis approximate counter.
If exact required, use covering index + subquery to scan only index, not data.
Ultimate: offload to Elasticsearch aggregations, or dedicated counter table + transactional updates.
-- Optimized: force composite index, avoid table lookup
SELECT COUNT(*) FROM t_order WHERE user_id = 1001 AND status = 0;
-- If index covers well, MySQL uses idx_user_status_time directlyChallenge 4: JSON Field Search & Indexing
Symptom: Early extension info dumped into JSON column; later ops needed "find all orders buying specific brand products", stuck with full table scan.
Solution: Exactly the virtual column index used above. Extract $.brand from JSON into virtual column, index it; query performance goes from full scan to index seek, zero business code intrusion . This case is the source of the product_snapshot SQL segment, perfectly balancing flexibility and performance.
Challenge Summary
Composite index failure : Query slow, CPU spike → Root cause: violates leftmost prefix → Solution: adjust query conditions or reasonably add redundant indexes
Soft delete + unique key conflict : Insert errors → Root cause: physical record still exists → Solution: order_no globally unique, never recycled
Large table COUNT slow : Timeout, concurrency crash → Root cause: InnoDB no row count cache → Solution: covering index optimization / move to ES / counter table
JSON field search difficult : Full table scan → Root cause: JSON lacks normal indexes → Solution: virtual column + index
Deep pagination poor : Slower further pages → Root cause: LIMIT 10000,10 massive table lookups → Solution: cursor pagination / delayed join
Interviewer:
"These challenges are all real production-grade; shows you don't just stop at syntax but consider performance, scalability, fault tolerance behind the scenes. Very solid 👍."
Candidate:
"Thanks, our project suffered from these designs, so now we inject these preventive shots upfront during table creation 😄."
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
