MySQL Execution Process and Comprehensive Optimization Best Practices

Understanding MySQL's execution process is essential for effective SQL optimization. The workflow includes client query submission, cache lookup, parsing, preprocessing, execution plan generation, engine execution, and result caching.

Database Naming Conventions

Use lowercase letters with underscores for object names.

Avoid MySQL reserved keywords.

Keep names meaningful and under 32 characters.

Prefix temporary tables with tmp_ and backup tables with bak_ , followed by a date.

Ensure columns storing the same data have identical names and types.

Storage Engine

Prefer InnoDB for all tables unless a special feature (e.g., column store) requires MyISAM. InnoDB offers transactions, row‑level locking, better recovery, and higher concurrency.

Primary Key Guidelines

Avoid frequently updated columns and multi‑column primary keys.

Do not use UUID, MD5, HASH, or string columns as primary keys.

Prefer auto‑increment integer IDs.

Character Set

Use UTF8 (or utf8mb4 for emoji) uniformly across databases and tables to avoid conversion overhead and index invalidation.

Query Writing Tips

Avoid SELECT * ; specify required fields.

Do not use OR in WHERE clauses when it can cause index loss; rewrite with separate conditions or UNION ALL .

Prefer numeric columns over strings for comparisons.

Use VARCHAR instead of CHAR for variable‑length data.

Store monetary values with DECIMAL for precision.

Avoid ENUM due to maintenance and ordering costs.

Minimize columns in DISTINCT statements; they increase CPU usage.

Replace NULL checks with default values when possible to keep indexes usable.

Avoid != or <> as they often invalidate indexes.

Join Strategies

Prefer INNER JOIN when results are identical; keep the left table small for LEFT JOIN . Limit the number of joined tables (ideally ≤5) and indexes per table (≤5).

GROUP BY Optimization

Filter rows before grouping to reduce data processed. Example:

SELECT job, AVG(salary) FROM employee WHERE job='develop' OR job='test' GROUP BY job;

TRUNCATE vs DELETE

Use TRUNCATE TABLE for fast full‑table deletion without logging, but it cannot be used on tables with foreign‑key constraints or indexed views; otherwise, use DELETE with WHERE .

Batch Operations

Insert multiple rows in a single statement to reduce transaction overhead:

INSERT INTO user (id, username) VALUES (1, '哪吒编程'), (2, '妲己');

When deleting or updating large datasets, add LIMIT or process in batches to lower lock contention and CPU usage.

UNION vs UNION ALL

Prefer UNION ALL when duplicate removal is unnecessary, as it avoids the extra sorting step.

IN Clause

Keep the number of values in IN reasonable; for large ranges use BETWEEN or join tables.

Index Design

Do not create an index for every column; excessive indexes hurt insert/update performance.

Use covering indexes that contain all columns needed by a query to avoid extra lookups.

Order composite index columns by selectivity, then by length, then by usage frequency.

Follow the left‑most prefix rule; partial use of a composite index still benefits the query.

Force a specific index with FORCE INDEX when the optimizer chooses a sub‑optimal one.

Prepared Statements

Use pre‑compiled statements to reuse execution plans and prevent SQL injection.

Transaction Size

Avoid large transactions; split massive DELETE , UPDATE , or INSERT operations into smaller batches to reduce lock time and prevent timeouts.

Partitioning and Sharding

For very large data sets, consider read/write splitting, sharding, and partitioning, but use them judiciously as they add complexity.

Pagination

Instead of deep LIMIT offset, count , paginate using the last retrieved ID:

SELECT id, name FROM user WHERE id > 100000 LIMIT 20;

Table Size Management

Keep single table rows under ~5 million; archive old data, use sharding, and separate hot/cold data.

Large Objects

Store BLOB/TEXT data in separate tables or external storage; use prefix indexes for these columns.

Additional Tips

Use LIMIT 1 when only one row is needed.

Minimize unnecessary sorting.

Add comments to tables and columns for documentation.

Maintain consistent keyword case and indentation.

Backup data before destructive operations.

Prefer EXISTS over IN for subqueries.

Avoid implicit type conversions in WHERE clauses.

Define columns as NOT NULL when possible to save space.