Why Optimize Slow SQL and Practical MySQL Performance Tuning Techniques

From a database perspective, each SQL consumes I/O resources; a slow query that occupies 30% of resources for one minute blocks other queries, while from an application view, long execution times degrade user experience.

Prioritization for mitigation includes focusing on master‑to‑slave replication lag, handling high‑frequency or high‑concurrency queries first, and addressing queries that heavily access a single table.

MySQL execution principle consists of parsing (lexical, syntax, logical plan, optimization, physical plan) and execution (permission checks, shared read lock, data retrieval, lock release). The process may involve query cache checks and creation of temporary tables.

Key influencing factors are data volume, data location (memory vs. disk), index availability, and data processing methods such as sorting or sub‑queries that increase I/O and CPU usage.

Solution ideas include storing hot data in faster storage or caches (Redis, Memcached), merging I/O by selecting only needed columns, and distributing load across multiple hosts.

Case study: MySQL high‑CPU issue

Enable slow query log

slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.4
log_queries_not_using_indexes

Run benchmark with mysqlslap

mysqlslap --defaults-file=/etc/my.cnf \
  --concurrency=50 --iterations=1 \
  --create-schema='oldboy' \
  --query="select * from oldboy.t_100w where k2='FGCD'" \
  --engine=innodb --number-of-queries=10 -uroot -pZHOUjian.22 --verbose

Results show execution time reduction after adding an index: alter table t_100w add index idx(k2); Further benchmarks with higher concurrency (5000) illustrate scaling limits.

Optimization directions

Use CPUs with ≥8 cores and SSDs.

Design proper indexes and table structures.

Apply read‑write separation.

Cache frequent queries with Memcache/Redis.

Adjust system parameters (innodb_buffer_pool_instances, innodb_thread_concurrency, etc.).

Common pitfalls include missing indexes, queries returning >25% of a table, index statistics out‑of‑date, functions on indexed columns, implicit type conversion, and misuse of LIKE with leading wildcards.

SQL best practices cover using explicit column lists, limiting transaction size, avoiding triggers/functions, preferring IN over OR, limiting IN list size (<500), using LIMIT 1 when only one row is needed, and avoiding ORDER BY RAND().