Master MySQL Performance: Practical Soft and Hard Optimization Techniques

Introduction

Database optimization aims to identify bottlenecks, improve overall MySQL performance, and design schemas and parameters that speed up user responses while conserving system resources.

Overview Diagram

Soft Optimization

Query Statement Optimization

Use EXPLAIN or DESCRIBE (abbreviated DESC) to analyze execution details of a query. DESC SELECT * FROM `user` The output shows indexes used and the number of rows examined.

Subquery Optimization

Prefer JOIN over subqueries because subqueries create temporary tables, incurring higher overhead.

Index Usage

Indexes are crucial for query speed. Key points:

LIKE with a leading ‘%’ cannot use an index.

Both columns in an OR condition must be indexed for the query to use an index.

Multi‑column indexes require left‑most prefix matching.

Table Partitioning and Intermediate Tables

Split tables with rarely used columns into separate tables, create intermediate tables to reduce join cost, and add redundant fields to avoid expensive joins.

Analyze, Check, Optimize Commands

Use ANALYZE, CHECK, and OPTIMIZE to maintain table health.

ANALYZE TABLE user; – shows operation, message type, and text.

CHECK TABLE user [option]; options include QUICK, FAST, CHANGED, MEDIUM, EXTENDED.

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user; works for VARCHAR, BLOB, TEXT and adds a read lock.

Hard Optimization

Hardware Improvements

Multi‑core, high‑frequency CPUs for parallel threads.

Large memory to increase buffer cache and reduce disk I/O.

Fast disks or distributed storage to boost I/O throughput.

MySQL Configuration Parameters

key_buffer_size – size of the index buffer.

table_cache – number of tables that can be opened simultaneously.

query_cache_size / query_cache_type – size of the query cache and its toggle.

sort_buffer_size – buffer for sorting operations.

Sharding and Read‑Write Splitting

When load is high, split a database into multiple instances (sharding) and separate write (master) and read (slave) traffic. This distributes load across servers.

Cache Cluster

Introduce a caching layer to handle high read concurrency, allowing the database to focus on writes while the cache serves most reads, dramatically increasing request capacity with fewer resources.

Conclusion

A comprehensive high‑concurrency architecture inevitably includes custom infrastructure and sophisticated design; this article provides a concise collection of core MySQL optimization ideas.