Essential Database Design and Operation Standards to Prevent Critical Failures

1. Security Standards

Mandatory: Never store plaintext passwords; encrypt them using a unified middleware solution.

Mandatory: Do not store user‑sensitive data such as phone numbers in plaintext; use a centralized phone‑lookup service.

Mandatory: Exporting or querying sensitive user data requires senior approval.

Mandatory: Exported data containing sensitive fields must be encrypted or masked.

Mandatory: All sensitive data operations must have audit logs and alerts.

Mandatory: Restrict database connection IPs with a whitelist.

Mandatory: Monitor access frequency of critical SQL (e.g., order queries) for anomalies.

Recommended: Periodically rotate database usernames and passwords.

2. Basic Standards

Recommended: Avoid heavy computations in the database; move complex calculations to the application layer.

Recommended: Reject large SQL statements, large transactions, and bulk operations; handle them in the business tier.

Explanation: Bulk operations can cause severe master‑slave lag; row‑based binlog generates large logs.

Recommended: Avoid stored procedures, triggers, and functions to prevent business logic coupling.

Explanation: Databases excel at storage and indexing; offload computation to services for better scalability.

Mandatory: All tables and columns must include Chinese comments for future maintainers.

Mandatory: Do not store large files or images in the database; use a file system instead.

Recommended: Follow the principle of least privilege for database accounts.

Recommended: Consider future extensibility when designing schemas.

Recommended: Use pt-query-digest to regularly analyze slow‑query logs.

Recommended: Use internal hostnames instead of IPs for connections.

Recommended: If data volume is large from the start, plan sharding strategies during design review.

Recommended: Enforce lowercase SQL keywords with a single space between words.

3. Naming Conventions

Mandatory: Database, table, and column names must be lowercase, use snake_case, be ≤32 characters, meaningful, and avoid mixed Chinese‑English pinyin.

Mandatory: Index naming: idx_<em>table</em>_<em>column</em> for normal indexes, uk_ for unique, pk_ for primary keys.

Mandatory: Do not use MySQL reserved words for names.

Mandatory: Temporary tables must start with tmp and end with a date.

Mandatory: Backup tables must start with bak and end with a date.

Recommended: Use hash suffixes (hex) for sharded tables.

Recommended: Date‑based sharding format: YYYY[MM][DD][HH].

Recommended: Use MD5 or similar hash for sharding suffixes.

Recommended: Use CRC32 or MD5 hash columns for long VARCHAR indexes.

Recommended: Use numeric suffixes for CRC‑based sharding (e.g., user_00 ‑ user_99).

Recommended: Time‑based sharding suffixes like user_20210409 (daily) or user_202104 (monthly).

Mandatory: Boolean‑like fields should be named is_<em>xxx</em>.

4. Database Engine and Charset

Recommended: Use InnoDB storage engine for transactions, row‑level locking, and better concurrency.

Recommended: Set database and table charset to UTF8; use utf8mb4 for emoji support.

Recommended: Separate business domains into different databases to avoid cross‑impact.

Mandatory: Deploy MHA high‑availability architecture for all production databases.

5. Table Design Guidelines

Recommended: Example CREATE TABLE statement:

CREATE TABLE `student_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',
  `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分',
  `stu_num` int(11) NOT NULL COMMENT '学号',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,
  KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';

Mandatory: Do not use foreign keys; enforce referential integrity in application code.

Mandatory: Every InnoDB table must have a primary key (prefer auto‑increment integer, not UUID/MD5).

Recommended: Keep column count < 50 per table.

Mandatory: Prohibit partitioned tables; prefer physical sharding.

Recommended: Separate hot and cold fields; split large or rarely accessed columns.

Recommended: Adopt appropriate sharding strategies (e.g., thousand‑db, hundred‑table) and keep table size ≤ 2 GB.

Recommended: Limit int fields to ≤ 50, char fields ≤ 20, text fields ≤ 2 per table.

Recommended: Add created_at and updated_at timestamps to each table.

Recommended: For log tables, use time‑based horizontal partitioning and archive old data.

Mandatory: Prohibit ORDER BY RAND() due to poor performance.

Reference: Use BLOB for VARCHAR(N) > 5000.

Reference: VARCHAR(N) length counts characters, not bytes; keep N small to stay within 65535‑byte row limit.

6. Field Design Guidelines

Mandatory: Define fields as NOT NULL with default values to avoid NULL‑related inefficiencies.

Mandatory: Replace ENUM with TINYINT.

Mandatory: Avoid TEXT and BLOB unless record count is very low.

Mandatory: Store phone numbers as VARCHAR(20).

Mandatory: Store monetary values as integer cents, not floating‑point.

Mandatory: Use DECIMAL for precise floating‑point numbers instead of FLOAT/DOUBLE.

Recommended: Use UNSIGNED for non‑negative integers.

Recommended: Store IPv4 as INT UNSIGNED and convert with INET_ATON / INET_NTOA:

SELECT INET_ATON('192.168.172.3'); -- 3232279555
SELECT INET_NTOA(3232279555); -- 192.168.172.3

Recommended: Allocate field lengths based on actual needs; avoid overly large capacities.

Recommended: Keep core table column count minimal; split large fields.

Recommended: Consider denormalization and add redundant fields to reduce JOINs.

Recommended: Store monetary amounts as integer cents (multiply by 100).

Recommended: Use VARBINARY for case‑sensitive strings or binary data.

Reference: INT always occupies 4 bytes; display width does not affect storage.

Reference: Prefer TIMESTAMP over DATETIME for automatic init/update and smaller size.

Reference: Example of automatic timestamp columns:

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

7. Index Design Guidelines

Recommended: Limit indexes per table to ≤5 for balanced read/write performance.

Mandatory: Do not index low‑cardinality or frequently updated columns.

Mandatory: Place high‑selectivity columns first in composite indexes.

Recommended: Use prefix indexes for VARCHAR > 128 characters.

Mandatory: Every table must have a primary key (auto_increment, NOT NULL, appropriate unsigned type).

Mandatory: Never use frequently updated columns or strings as primary keys.

Mandatory: Avoid UUID/MD5/HASH as primary keys.

Recommended: Prefer unique keys as primary keys.

Recommended: Use auto‑increment or sequence generators for primary keys.

Recommended: Favor covering indexes for core queries.

Reference: Avoid redundant or duplicate indexes.

Reference: Evaluate index density and query/update ratio when designing indexes.

Mandatory: Do not perform arithmetic or function operations on indexed columns.

Recommended: Use EXPLAIN and hints when index selectivity is poor.

Recommended: Use unique indexes wherever possible.

Recommended: For repeated field patterns, create a composite index with reordered columns.

Recommended: Specify index length for VARCHAR indexes.

Recommended: For long VARCHAR, add a CRC32/MD5 hash column and index the hash.

Recommended: Avoid indexing low‑selectivity columns such as gender or status.

Recommended: Use FORCE INDEX when necessary.

Mandatory: Do not create separate indexes for every column.

Mandatory: Ensure indexed VARCHAR fields have explicit length specifications.

8. SQL Usage Guidelines

Mandatory: Prohibit SELECT *; fetch only required columns.

Mandatory: Prohibit INSERT INTO table VALUES(...); always specify column list.

Mandatory: Use appropriate types in WHERE to avoid implicit conversion that disables indexes.

Mandatory: Do not use functions or expressions on indexed WHERE columns.

Mandatory: Disallow negative pattern queries and leading‑wildcard LIKE '%...'.

Mandatory: Application must catch SQL exceptions and handle them.

Recommended: Break large SQL statements into smaller ones.

Recommended: Keep transactions short.

Mandatory: Avoid arithmetic or function calculations inside the database.

Recommended: Replace OR with IN() when possible.

Reference: Limit IN() list to ≤1000 items.

Recommended: Optimize LIMIT pagination; rewrite large offsets:

SELECT id FROM t LIMIT 10000,10;
-- rewrite as
SELECT id FROM t WHERE id > 10000 LIMIT 10;

Recommended: Prefer UNION ALL over UNION.

Reference: Avoid large‑table JOINs.

Recommended: Batch updates instead of single massive updates.

Recommended: Reduce round‑trips to the database with efficient SQL.

Reference: Use performance tools like EXPLAIN, SHOW PROFILE, mysqlsla.

Recommended: Avoid NOT IN; it can return unexpected NULL results.

Recommended: For large result sets, paginate wisely; avoid huge LIMIT offsets.

Mandatory: Prohibit running massive queries on production.

Mandatory: Disallow a single SQL statement from updating multiple tables.

Recommended: Use COUNT(*) for row counts, not COUNT(primary_key) or COUNT(1).

Recommended: Batch INSERT statements (e.g., INSERT INTO table VALUES (),(),()) but keep batch size reasonable.

Recommended: Fetch large data in chunks (< 2000 rows per batch, result < 1 MB).

Recommended: Use prepared statements or ORM frameworks (e.g., MyBatis) to improve performance and prevent SQL injection.

Mandatory: Prohibit cross‑database queries to preserve sharding boundaries.

Recommended: Prefer joins over subqueries; subqueries often prevent index usage.

Mandatory: Limit joins to at most three tables; ensure joined columns are indexed and type‑compatible.

Recommended: Aim for range or better query plan levels; ref is acceptable, const is ideal.

Recommended: Use logical deletes (e.g., delete_flag) instead of physical deletes.

Mandatory: Return early when COUNT is zero to avoid unnecessary pagination queries.

Mandatory: Use distinct database accounts for different databases.

Recommended: Use ISNULL() to test for NULL values.

9. Operational Behavior Guidelines

Mandatory: Do not manually access production databases using credentials stored in application config files.

Mandatory: Only DBAs may perform write operations on production; changes must go through a ticket and be tested.

Mandatory: Prohibit running performance tests against production databases.

Mandatory: Disallow direct connections from test or development environments to production.

Mandatory: Do not run analytics on the primary database; use a replica for reporting.

10. Process Guidelines

Mandatory: Table creation must be preceded by a review of the associated query workload.

Mandatory: Indexes must be defined before a table goes live.

Mandatory: Any schema change or index addition must be communicated to DBAs with the affected queries.

Mandatory: New tables or columns require at least three days of advance notice for DBA review.

Mandatory: Bulk import/export operations must be audited by DBAs and monitored during execution.

Mandatory: No application account should have SUPER privileges.

Mandatory: New features or promotional activities must be announced to DBAs for traffic assessment.

Mandatory: Avoid bulk updates or queries during peak business hours.

Mandatory: Isolate production from non‑production environments; developers must not access production databases.

Mandatory: Perform large‑table schema changes during off‑peak hours using tools like pt-online-schema-change to avoid locks and replication lag.

Mandatory: Core business database changes must run at night.

Recommended: Enable audit logging on summary databases for traceability.

Mandatory: Passwords for granted permissions must be MD5‑hashed (≥16 characters) and default to SELECT‑only with table‑level restrictions.

Recommended: If data loss occurs due to human error, notify DBAs immediately with timestamps and offending statements.

Mandatory: Bulk data modifications (UPDATE/DELETE) require DBA review and live monitoring.

Mandatory: Bugs affecting database services must be reported to DBAs promptly.

Mandatory: Every production change must include a rollback plan.

Mandatory: Bulk data cleaning should be coordinated between developers and DBAs, executed off‑peak, and monitored.

Mandatory: Before deleting or modifying records, perform a SELECT to confirm correctness.