Why Tame Slow SQL? Proven MySQL Optimization Strategies

Why Manage Slow SQL

From the database perspective, each SQL consumes I/O resources; a slow SQL occupying 30% of resources for one minute blocks other SQLs, leading to longer wait times and poor user experience in OLTP applications.

Prioritization

Master‑slave databases

Read‑write separation: reads on slaves, writes on master.

Heavy writes on master increase replication lag.

Prioritize frequently executed SQL.

If a class of SQL heavily accesses a table, address it first.

MySQL Execution Principle

The execution consists of two main steps: parsing and execution. For example, com_query dispatches alloc_query to allocate a query buffer, then parses.

Parsing: lexical analysis → syntax analysis → logical plan → query optimization → physical execution plan.

Execution checks user and table permissions, acquires a shared read lock, fetches data to the query cache, and releases the lock.

Influencing Factors

Ignoring MySQL parameters and hardware I/O, the main factors affecting SQL efficiency are I/O and CPU consumption.

Data volume: larger data requires more I/O.

Data retrieval method.

Data in cache vs on disk.

Whether an index can be used for fast lookup.

Data processing method.

Sorting, subqueries require temporary tables, increasing I/O and CPU.

Solution Ideas

Store data in faster locations (e.g., application cache, Redis, or NoSQL when appropriate).

Merge I/O where possible (e.g., separate SELECT c1 FROM t1 and SELECT c2 FROM t1 instead of SELECT c1,c2 FROM t1).

Leverage distributed architecture to spread data and I/O across multiple hosts.

Case (MySQL high CPU issue diagnosis and optimization)

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

Query a 100‑million‑row table with 50 concurrent queries

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

Result: average 0.075 seconds per query.

50 000 concurrent queries on the same table

mysqlslap ... --concurrency=5000 ... --number-of-queries=100 ...

Result: average 6.285 seconds per query.

Optimization directions and cautions

CPU optimization

Use at least 8‑core CPUs and SSD storage.

Design proper indexes and optimize SQL.

Read‑write separation to offload read traffic.

Scale application architecture and instance specs.

Cache frequent query results with Memcached or Redis.

MySQL performance testing

System parameters: disk scheduler, NUMA, ext4/xfs.

Log flushing settings: innodb_flush_neighbors, innodb_deadlock_detect, sync_binlog, innodb_flush_log_at_trx_commit.

Concurrency parameters: innodb_buffer_pool_instances, innodb_thread_concurrency.

CPU‑memory coordination issues on some servers.

When indexes are not used (development guidelines)

No WHERE condition or condition lacks an index → full table scan.

Result set > 25 % of table → optimizer may skip index.

Index becomes ineffective due to stale statistics; rebuild if needed.

Functions or arithmetic on indexed columns prevent index usage.

Implicit type conversion can disable index.

Operators <>, NOT IN, and LIKE with leading wildcard often avoid index.

Database considerations

Important SQL must be indexed (WHERE columns, ORDER BY/GROUP BY/DISTINCT fields).

MySQL does not support functional indexes.

Non‑equality conditions ( <>, !=) cannot use indexes.

Functions on filter columns (e.g., ABS(col)) disable index.

Join columns with mismatched types prevent index usage.

LIKE with leading % cannot use index.

BLOB/TEXT columns only support prefix indexes.

SQL best practices

Keep statements simple; split large queries.

Keep transactions short and commit promptly.

Limit a transaction to ≤ 10 000 rows.

Avoid triggers, functions, stored procedures.

Design for scale‑out and sharding.

Avoid heavy arithmetic in SQL.

Prefer explicit column lists over SELECT *.

Rewrite OR as IN() where possible.

Keep IN() lists ≤ 500 items; consider EXISTS.

Use LIMIT wisely; rewrite large offsets as range conditions.

Use LIMIT 1 when only one row is needed.

Fetch large result sets in batches (< 10 000 rows, < 1 MB).

Avoid large‑table JOINs; use GROUP BY for aggregation.

Never compare numeric columns to string literals.

Avoid leading‑% LIKE patterns.

Choose UNION ALL vs UNION appropriately.

Do not run full‑table scans in OLTP workloads.

Prefer prepared statements to reduce parsing overhead and injection risk.

Avoid ORDER BY RAND().

Do not update multiple tables in a single statement.

Schedule bulk updates/ALTERs outside peak hours.

Run heavy aggregations on replicas, not the primary.