MySQL Passive Performance Optimization Principles and Practices

Performance optimization can be divided into active and passive categories. Active optimization occurs without external prompts, while passive optimization is triggered by observed issues such as server lag or abnormal hardware metrics.

Optimization Principles

Regardless of the approach, optimization must preserve correctness, safety, and stability of the service. The recommended strategy is to prioritize preventive measures and use passive optimization as a supplement.

MySQL Passive Performance Optimization

The article focuses on three common MySQL performance problems and provides detailed analysis and solutions.

Problem 1: Single SQL Query Is Slow

Typical causes are missing or improperly used indexes and excessively large tables.

Solution 1: Proper Index Creation and Usage

Check that indexes exist and that queries can actually use them. Avoid constructs that prevent index usage, such as != or <> in WHERE , leading wildcards like '%XX' , OR conditions without indexes on all columns, and expressions on indexed fields.

Solution 2: Data Partitioning

When table size is large, split the data:

Vertical partitioning – separate rarely used columns or large TEXT/BLOB fields into another table.

Horizontal partitioning – distribute rows across multiple tables (e.g., sharding by user ID modulo).

Additional table‑level optimizations include using the smallest suitable data types, preferring INT over VARCHAR for numeric data, defining NOT NULL , limiting the number of columns, and using TIMESTAMP instead of DATETIME .

Problem 2: Some SQL Queries Are Slow

First locate the slow queries using the MySQL slow‑query log, then apply the solutions from Problem 1.

Enable Slow Query Log

Check the status with:

mysql> show variables like '%slow_query_log%';

Enable it temporarily:

mysql> set global slow_query_log=1

For permanent activation, add to my.cnf :

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log

Problem 3: Entire SQL Workload Is Slow

When the database reaches its capacity limit, adopt read‑write splitting to offload read traffic.

Read‑Write Splitting Solutions

Application‑level routing (e.g., Spring MVC + MyBatis with AOP or annotations) – flexible but adds coupling.

Middleware solutions (e.g., MySQL Proxy, Amoeba, Atlas) – decouples from the application but introduces an extra service.

Extended Knowledge: SQL Explain

Use EXPLAIN SELECT * FROM t WHERE id=5; to analyze execution plans. Important fields include type (e.g., ALL , index , range , ref , eq_ref , const ) which indicate how the optimizer accesses data.

Conclusion

The article presents MySQL performance optimization principles, emphasizing that both active and passive optimizations must maintain correctness, safety, and stability. The recommended approach is preventive optimization combined with targeted passive measures for single‑query slowness, partial slowness, and overall workload degradation.