MySQL High Performance Optimization Guidelines and Best Practices

This article is part of a "MySQL High Performance Optimization" series that provides detailed recommendations for designing, developing, and operating MySQL databases in production environments.

Database Command Standards

Use lowercase letters and underscores for all object names; avoid MySQL reserved keywords.

Names should be meaningful and no longer than 32 characters.

Temporary tables must be prefixed with tmp_ and suffixed with a date; backup tables with bak_ and a timestamp.

Columns storing the same data must have identical names and types to prevent implicit conversion and index loss.

Basic Table Design Standards

All tables should use the InnoDB storage engine (unless a special requirement dictates otherwise).

Use UTF‑8 (or UTF8MB4 for emoji) as the default character set to avoid conversion issues.

Add comments to every table and column using the COMMENT clause.

Keep single‑table row counts below 5 million to simplify maintenance, backup, and recovery.

Use partition tables cautiously; prefer physical sharding for very large data sets.

Separate hot and cold data to reduce table width and improve cache hit rates.

Avoid placeholder columns and storing large binary objects (images, files) directly in the database.

Never run performance tests on a production database and never connect production directly from development or test environments.

Field Design Standards

Choose the smallest appropriate data type; for example, store IP addresses as integers using inet_aton() and display them with inet_ntoa() .

Prefer UNSIGNED integers for non‑negative values (e.g., auto‑increment IDs, IPs).

Remember that VARCHAR(N) counts characters, not bytes; UTF‑8 characters may occupy up to three bytes each.

Avoid TEXT and BLOB columns when possible; if necessary, place them in separate extension tables and avoid SELECT * .

Do not use ENUM types; they are hard to modify and perform poorly in ORDER BY operations.

Define columns as NOT NULL to save index space.

Store timestamps with TIMESTAMP (4 bytes) when the range fits; otherwise use DATETIME (8 bytes).

Use DECIMAL for precise monetary values; avoid floating‑point types for financial data.

Index Design Standards

Limit each table to no more than five indexes; excessive indexes increase optimizer planning time and can degrade performance.

Do not create an index on every column; prioritize columns used in WHERE , ORDER BY , GROUP BY , and join conditions.

Every InnoDB table must have a primary key; avoid using frequently updated columns, UUIDs, or long strings as primary keys—use an auto‑increment integer instead.

When building composite indexes, place the most selective (highest cardinality) and shortest columns on the leftmost position.

Avoid redundant or duplicate indexes (e.g., PRIMARY KEY(id) plus INDEX(id) ).

Prefer covering indexes that contain all columns needed by a query to eliminate the need for a secondary lookup.

Foreign‑Key and Constraint Guidelines

Avoid foreign‑key constraints in high‑traffic tables; enforce referential integrity at the application layer instead.

SQL Development Standards

Use prepared statements to reduce parsing overhead and prevent SQL injection.

Avoid implicit type conversions; they can invalidate indexes (e.g., comparing an integer column to a quoted string).

Leverage existing indexes; avoid patterns like LIKE '%value%' which prevent index usage.

Only one column of a composite index can be used for a range query; place range‑filtered columns on the right side of the index.

Prefer LEFT JOIN or NOT EXISTS over NOT IN for better index utilization.

Limit the number of tables joined in a single query (MySQL allows up to 61, but keeping it ≤5 is recommended).

Batch multiple operations together to reduce round‑trips to the database.

Replace multiple OR conditions on the same column with an IN list (keep the list under 500 items).

Never use ORDER BY RAND() on large result sets; generate random values in the application instead.

Avoid applying functions or calculations to indexed columns in the WHERE clause; rewrite conditions such as: where date(create_time)='20190101' to where create_time >= '20190101' and create_time < '20190102'

Use UNION ALL when duplicate rows are not a concern to avoid the overhead of deduplication.

Split very large, complex SQL statements into smaller ones to enable parallel execution.

Database Operation Standards

For batch writes affecting more than one million rows, split the operation into smaller chunks to prevent master‑slave lag and excessive binary log generation.

Avoid large transactions that lock many rows and cause connection exhaustion.

Use tools like pt‑online‑schema‑change for online schema modifications on big tables to avoid locking.

Never grant SUPER privileges to application accounts; reserve them for DBA use only.

Apply the principle of least privilege: limit each application account to a single database and avoid DROP permissions.

By following these conventions, developers can build MySQL databases that are easier to maintain, perform better under load, and remain secure.