Essential MySQL DBA Best Practices: 20+ Rules for Reliable Database Management

DBA Operation Standards

Business‑related data modifications or deletions must be approved by the business owner and CTO via email, and a backup must be taken beforehand to allow rollback.

All production changes must be submitted through the ticketing system; verbal requests are not valid.

When altering large tables, schedule the change after 00:00 during low‑traffic periods and use pt-online-schema-change to avoid table locks and reduce replica lag. Example:

# pt-online-schema-change \
    --alter="add index IX_id_no(id_no)" \
    --no-check-replication-filters \
    --recursion-method=none \
    --user=dba --password=123456 \
    D=test,t=t1 --execute

All production databases must be deployed with MHA high‑availability architecture to eliminate single points of failure.

When granting permissions, passwords must be MD5‑hashed (minimum 16 characters). Default access is SELECT with table‑level restrictions unless special needs arise.

Delete accounts that have empty passwords:

delete from mysql.user where user='' and password='';
flush privileges;

Enable audit logging on summary databases to provide traceability during incidents.

Behavior Norms

Do not host multiple business databases on a single MySQL instance; use separate instances per business to reduce coupling and simplify fault isolation.

Avoid running heavy administrative or statistical queries on the primary server, as they increase CPU usage and can affect business workloads.

Data cleaning should be a joint effort between developers and DBAs, executed outside peak hours with continuous monitoring.

Promotional activities must be coordinated with DBAs in advance for capacity planning (e.g., adding memory or scaling architecture a week ahead).

Never perform stress testing on production databases.

Basic Norms

Never store plaintext passwords in the database.

Use the InnoDB storage engine for its transactional support, row‑level locking, better recovery, and superior performance under concurrency.

Set the default character set of all tables to UTF8 to avoid garbled text.

Provide Chinese comments for every table and column to aid future maintainers.

Do not store large binary objects (images, files) in the database; store them in a distributed file system (e.g., GFS) and keep only URLs.

Table Design Norms

Every table must have a primary key, preferably an auto‑increment integer; avoid UUIDs because they cause random inserts and degrade performance.

Do not use MySQL partitioned tables. Implement sharding or application‑level table splitting, using UNION ALL for historical data queries and dumping old partitions to standby servers.

Field Design Norms

Use DECIMAL instead of FLOAT / DOUBLE for precise numeric values. Example:

CREATE TABLE t3 (c1 FLOAT(10,2), c2 DECIMAL(10,2));
INSERT INTO t3 VALUES (999998.02, 999998.02);
SELECT * FROM t3;  -- c1 shows 999998.00, c2 shows 999998.02

Replace ENUM with TINYINT to simplify schema changes and reduce storage.

Allocate field lengths based on actual needs; prefer smaller types (e.g., INT for IDs, TINYINT for status flags) to save space, I/O, and bandwidth.

Define NOT NULL columns with sensible default values to simplify application logic and improve index statistics.

Avoid TEXT and BLOB columns unless absolutely necessary, as they increase storage and slow reads.

Index Norms

Create indexes only where they provide measurable benefit; excessive indexes hurt write performance and consume disk.

Index columns used in WHERE clauses, JOIN conditions, and frequent filters.

Do not perform arithmetic or function operations on indexed columns (e.g., WHERE YEAR(date) >= 2016). Rewrite as WHERE date >= '2016-01-01'.

Avoid indexing low‑cardinality columns such as gender.

Do not use leading wildcards in LIKE patterns; rewrite LIKE '%abc' as LIKE 'abc%' for index usage.

Avoid NOT IN / NOT LIKE patterns that prevent index use.

Eliminate redundant or duplicate indexes; a composite index (a,b,c) already covers (a) and (a,b).

SQL Design Norms

Never use SELECT *; specify only required columns to reduce CPU, I/O, and network traffic and enable covering indexes.

Replace multiple OR conditions with IN for better performance. Example: SELECT * FROM t WHERE LOC_ID IN (10,20,30); Ensure data type consistency in predicates (e.g., WHERE id = 19 instead of comparing a string to an integer).

Batch DML statements to reduce round‑trips. Examples:

INSERT INTO t (id,name) VALUES (1,'A'),(2,'B'),(3,'C');
UPDATE t SET col=val WHERE id IN (1,2,3);
ALTER TABLE t ADD COLUMN c1 ..., ADD COLUMN c2 ...;

Break large, complex queries into smaller, focused statements to improve readability and execution planning.

Avoid ORDER BY RAND(); use a deterministic alternative such as:

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;