Master MySQL Performance: 5 Key Optimization Strategies You Must Know

1. Connection Configuration Optimization

1.1 Server-side Settings

Increase max_connections (default 151) to allow more concurrent clients and reduce idle connections by lowering wait_timeout (default 28800 seconds) if appropriate.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

Modify MySQL parameters only when you understand their impact.

1.2 Client-side Settings

Use a connection pool (e.g., DBCP, C3P0, Alibaba Druid, Hikari) to reuse connections. Oversizing the pool creates many server threads, causing costly context switches. Hikari recommends CPU_cores*2+1 (e.g., 9 for a 4‑core server).

2. Architectural Optimization

2.1 Caching

Cache slow, infrequently changing queries (e.g., daily reports) for the cache TTL to reduce database load.

2.2 Read‑Write Splitting (Master‑Slave Replication)

Separate write traffic to a master node and distribute read traffic across replica nodes. The master records writes to the binary log ( binlog); replicas pull the log via an I/O thread to stay synchronized.

MySQL now prefers the terms source and replica instead of master / slave .

2.3 Sharding (Database and Table Partitioning)

When a single table becomes a bottleneck, split data across multiple nodes.

Vertical Partitioning : Separate databases or tables by business domain (e.g., user data vs. order data).

Horizontal Partitioning : Distribute rows of a single table across many tables or databases based on a sharding key.

2.3.1 Vertical Database Splitting

2.3.2 Vertical Table Splitting

Example: split a product table into a hot table (price, name) and a cold table (large description) to speed up list queries.

2.3.3 Horizontal Table Splitting

2.3.4 Horizontal Database Splitting

2.4 Message Queue for Traffic Shaping

During traffic spikes, enqueue requests in a message queue (e.g., RabbitMQ, Kafka) and let workers consume them at a controlled rate to prevent the database from being overwhelmed.

3. Optimizer – SQL Analysis and Tuning

3.1 Slow Query Log

Enable the slow query log to capture queries exceeding a threshold (default 10 s). Check variables:

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |
+---------------------+--------------------------------------+

Adjust the threshold via long_query_time (e.g., set to 2 s) and enable the log:

mysql> set @@global.slow_query_log=1;
mysql> set @@global.long_query_time=2;

Analyze the log with mysqldumpslow:

# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT sleep(N)

3.2 Viewing Running Threads

Run show full processlist to see all threads, their IDs, users, hosts, commands, execution time, and SQL snippet. Use KILL <thread_id> to terminate problematic threads.

3.3 Server Status

Use SHOW GLOBAL STATUS LIKE 'com_select'; for global counters and SHOW ENGINE INNODB STATUS; for InnoDB internals.

3.4 EXPLAIN Execution Plan

Run EXPLAIN SELECT … (available for UPDATE, DELETE, INSERT since MySQL 5.6.3) to see whether indexes are used, if a table scan occurs, and how joins are processed.

3.5 SQL and Index Optimization

3.5.1 SQL Refactoring

Drive large tables with small tables; rewrite subqueries as joins; replace OR with UNION.

Reduce row count on the driving side and ensure indexed columns on the driven side.

For large OFFSET LIMIT, filter first then limit.

Example comparing large offset vs. indexed filter:

-- Large offset (slow)
SELECT * FROM user_innodb LIMIT 9000000,10;   -- 8.18 sec

-- Indexed filter (fast)
SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;   -- 0.02 sec

3.5.2 Index Usage

Create appropriate indexes for slow queries and verify their usage with EXPLAIN.

4. Storage Engine and Table Structure

4.1 Choosing a Storage Engine

InnoDB is the default and works for most workloads. Use MyISAM for read‑heavy tables, Memory for temporary tables, and keep InnoDB for high‑concurrency write workloads.

4.2 Field Optimization

4.2.1 Integer Types

Pick the smallest integer type that fits the data (e.g., tinyint for boolean flags instead of bigint).

4.2.2 Character Types

Use char for fixed‑length strings and varchar for variable length. Avoid overly large varchar definitions when a smaller size suffices.

4.2.3 NOT NULL

Prefer NOT NULL with sensible defaults; NULL columns incur extra storage and performance overhead.

4.2.4 Avoid Foreign Keys, Triggers, Views

Handle data integrity in application code to improve readability, reduce testing complexity, and avoid performance penalties.

4.2.5 Large Objects

Store large files (images, audio, video) outside the database and keep only their URLs.

4.2.6 Large Field Splitting and Redundancy

Apply vertical partitioning to move bulky columns out of frequently accessed tables. Limited redundancy (e.g., storing customer name alongside ID) can speed up lookups.

5. Business‑Level Optimizations

Adjust business processes to reduce database load, such as pre‑sale traffic shaping, graceful degradation during peak events, and encouraging faster payment methods that rely on low‑latency internal services.