Mastering MySQL Passive Performance Optimization: Principles & Practical Solutions

“Youth knows not the pain of optimization; only when encountering pitfalls does one realize its difficulty.”

Performance Optimization Principles and Classification

Performance optimization can be divided into:

Active optimization – self‑initiated improvements without external prompts.

Passive optimization – improvements triggered by observed slowdowns, errors, or abnormal hardware metrics.

Both approaches must obey three principles:

Do not alter the service logic; maintain correctness.

Ensure safety throughout the optimization process and results.

Preserve stability; never sacrifice reliability for speed (e.g., do not disable Redis persistence for speed).

The guiding insight is to prioritize preventive measures and use passive optimization as a supplement.

MySQL Passive Performance Optimization

This section focuses on passive optimization techniques for MySQL, addressing three common problems.

Problem 1: Single SQL Query Runs Slowly

Analysis

Typical causes are missing or misused indexes and excessive table data volume.

Solution 1: Create and Properly Use Indexes

Check that indexes exist and that queries can leverage them. Avoid:

Using != or <> in WHERE clauses, which forces full table scans.

Leading wildcard patterns like '%value' or '%value%', which prevent index usage.

OR conditions that prevent index usage unless each column involved has an index.

Applying functions or expressions to indexed columns in WHERE clauses.

Additional tips:

Prefer primary‑key queries to avoid back‑table lookups.

Simplify complex statements; break them into smaller queries to reduce lock time.

Use numeric fields for numeric data; avoid storing numbers as strings.

Replace IN with EXISTS when appropriate.

Avoid IS NULL or IS NOT NULL on indexed columns.

Back‑table query: after using a non‑unique index, MySQL may need to fetch the primary key to retrieve full rows.

Solution 2: Data Partitioning

If a table grows too large, split it to improve query speed.

Vertical Partitioning

Separate frequently accessed columns into one table and less‑used columns into another; also move large TEXT / BLOB fields to auxiliary tables.

Horizontal Partitioning

Distribute rows across multiple tables (e.g., sharding by user ID modulo) when row count exceeds a threshold such as two million.

Other Table Optimizations

Use the smallest suitable data types.

Prefer INT over VARCHAR for numeric data.

Prefer TINYINT, SMALLINT, MEDIUMINT over INT.

Define columns as NOT NULL when possible to save space.

Minimize use of TEXT; consider splitting if unavoidable.

Prefer TIMESTAMP over DATETIME.

Keep the number of columns per table reasonable (around 20).

Problem 2: Partial SQL Queries Run Slowly

Analysis

Identify the slow queries first, then apply the indexing solutions from Problem 1.

Solution: Slow Query Log Analysis

Enable MySQL’s slow‑query log to capture statements exceeding long_query_time (default 10 seconds). Check if the log is enabled: mysql> show variables like '%slow_query_log%'; If the value is OFF, the log is disabled.

Enable Slow Query Log

Temporarily enable with:

mysql> set global slow_query_log=1

For a permanent change, edit my.cnf:

slow_query_log = 1 slow_query_log_file = /tmp/mysql_slow.log

After enabling, review the log file to locate and optimize each slow statement.

Problem 3: Entire SQL Workload Is Slow

Analysis

When overall performance degrades, the database has reached its capacity limits.

Solution: Read‑Write Splitting

Separate read‑heavy traffic from writes by using a master‑slave architecture: the master handles writes, slaves handle reads.

Two common approaches:

Application‑Level Routing

Use frameworks (e.g., Spring MVC + MyBatis) with AOP or annotations to direct queries to the appropriate data source.

Middleware Solution

Deploy MySQL middleware such as MySQL Proxy, Amoeba, or Atlas to manage master‑slave routing, decoupling it from application code.

Extended Knowledge: SQL Statement Analysis

Use the EXPLAIN command to inspect query execution plans, revealing fields like id, select_type, type, key, and rows. The type column indicates access method, ranging from ALL (full table scan) to CONST (constant lookup).

Conclusion

The article presented MySQL performance optimization principles, distinguishing active and passive methods while emphasizing correctness, safety, and stability. It highlighted preventive and passive strategies and detailed solutions for three typical slow‑SQL scenarios, aiming to help readers improve MySQL reliability and efficiency.