MySQL Performance Optimization and Best Practices

MySQL Performance

The author reports frequent slow‑SQL alerts on an Alibaba Cloud MySQL instance, with queries taking up to five minutes due to missing indexes and lack of pagination.

Maximum Data Volume

MySQL imposes no hard limit on table rows; limits are set by the OS file size. The Alibaba Java Development Manual suggests splitting tables when rows exceed 5 million or size exceeds 2 GB, though this is a guideline, not a rule.

Example of a fast pagination query on a table with >400 million rows:

select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20

Maximum Concurrency

Concurrency is controlled by max_connections (instance‑wide) and max_user_connections (per‑user). The ratio of used connections to max should exceed 10 %.

show variables like '%max_connections%';
show variables like '%max_user_connections%';

To change the limit in my.cnf:

[mysqld]
max_connections = 100
max_used_connections = 20

Query Time Recommendation

Keep single query execution time under 0.5 seconds (one‑sixth of the 3‑second user‑experience rule).

Implementation Principle

Prefer letting the application handle more work while the database does less; avoid heavy operations directly on MySQL.

Use indexes wisely; they consume disk and CPU.

Avoid database functions for formatting—handle in application code.

Prefer application‑enforced data integrity over foreign keys.

For write‑heavy scenarios, avoid unique indexes and enforce uniqueness in code.

Consider redundant fields or intermediate tables to trade space for speed.

Split large transactions into smaller ones.

Forecast table load and growth, and optimize proactively.

Data Type Selection

Prefer smaller integer types (tinyint, smallint, mediumint) when possible.

Use char for fixed‑length strings.

Avoid text if varchar suffices.

Use decimal or bigint for high‑precision numbers.

Prefer timestamp over datetime for space efficiency.

Index Optimization

Types of indexes: ordinary, composite, unique, composite‑unique, primary key, full‑text. Recommendations include:

Keep pagination queries under 30 % of total rows; otherwise MySQL may ignore the index.

Limit a table to ≤5 indexes, each with ≤5 columns.

Use prefix indexes (5‑8 characters) for long strings.

Avoid indexing low‑cardinality columns (e.g., gender, deleted flag).

Example of a covering index query:

select login_name, nick_name from member where login_name = ?

SQL Optimization

Batch Processing

Break large updates into batches to avoid blocking other queries.

int pageNo = 1;
int PAGE_SIZE = 100;
while (true) {
    List<Integer> batchIdList = queryList("select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}");
    if (CollectionUtils.isEmpty(batchIdList)) { return; }
    update("update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}");
    pageNo++;
}

Operator Optimizations

<>

often cannot use indexes; consider rewriting with UNION if data distribution is skewed.

For OR conditions, split into separate queries combined with UNION to leverage indexes.

Prefer EXISTS over IN when the sub‑query returns many rows.

Avoid functions on indexed columns (e.g., date_format); rewrite using range predicates.

Never use SELECT * in production queries; specify needed columns.

For LIKE, avoid leading wildcard ( '%keyword') to allow index usage; use full‑text or external search engines for true fuzzy search.

Join Optimization

Use Nested Loop Join efficiently: drive table should have restrictive conditions; index join columns on the driven table; avoid joining more than three tables when possible.

Limit Optimization

Deep pagination degrades performance. Reduce scan range by selecting the start ID first, then fetching the page:

select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10

When only primary key is needed, query by ID range directly.

Other Databases

Backend developers should master relational databases like MySQL or SQL Server while also being aware of mature NoSQL solutions for specific performance scenarios.

END