Master MySQL Performance: Practical Tuning, Indexes, and Scaling Strategies

Introduction

After covering the first three parts of MySQL, we now dive into the final and most practical segment: MySQL performance tuning. Understanding and overcoming performance bottlenecks is essential for using MySQL fluently.

Performance Basics and Testing

MySQL is easy to start with, but deep performance tuning is challenging. In a typical 8‑core, 16 GB machine running MySQL 5.5, a 1 million‑row table can achieve about 2 000 TPS for sequential writes and roughly 5 000 TPS for reads when indexes are effective.

Optimization Strategies

Performance tuning can be approached from three dimensions:

SQL statement optimization : rewrite queries, add or adjust indexes, and use execution plans.

Frequency control : enable read caches and write buffers to reduce load.

Scale : when data size becomes a bottleneck, split databases/tables (sharding).

Identifying Slow Queries

MySQL’s slow‑query log records statements whose execution time exceeds a threshold (default 10 seconds). The log is disabled by default and must be enabled manually.

Index Impact

Indexes dramatically improve lookup speed, reducing complexity from O(n) to O(log n). For example, scanning 1 million rows without an index requires full table traversal, while an indexed query may touch only a few thousand rows.

Pagination Performance

Using large OFFSET values (e.g., OFFSET 10000 LIMIT 10) forces MySQL to scan many rows sequentially, resulting in O(n) time and many random I/O operations. Two mitigation approaches are proposed:

Previous/Next page navigation : avoid large offsets by remembering the last primary‑key and fetching the next/previous page directly.

Index covering : ensure the secondary index contains all needed columns (including the primary key) so the engine can satisfy the query without touching the clustered index.

Count Optimization

Three common solutions for expensive COUNT(*) queries:

Cache the count in Redis and periodically reconcile with the database.

Create a composite index that covers the WHERE clause, allowing the count to be obtained directly from the secondary index without a table lookup.

Maintain a separate counting table updated atomically within transactions for high‑precision, read‑heavy scenarios.

Sharding and Partitioning

When a single table reaches its performance ceiling, split it into multiple tables. Two main approaches:

Vertical sharding : separate columns into different tables based on usage patterns.

Horizontal sharding : divide rows across identical tables based on a key (e.g., user ID) or data volume.

Implementation can be done via a shared library that encapsulates sharding logic, avoiding extra middleware but requiring client updates when the library changes.

Migration Steps for Existing Large Tables

For a table already containing tens of millions of rows, a safe migration can be performed in four phases:

Dual‑write (old) : write to both old and new tables via middleware to keep data consistent.

Dual‑write with gray traffic : gradually route a portion of reads to the new table while still checking against the old table.

Dual‑write (new) : after verification, read exclusively from the new table while continuing dual writes for safety.

Write‑new only : finally stop writing to the old table and optionally archive it.

Conclusion

MySQL performance testing and tuning are high‑level skills that enable engineers to handle real‑world workloads efficiently. The next topic in the interview series will explore computer networks.