Master MySQL Performance: Practical Tips for Scaling and Optimization

The author, responsible for projects using Alibaba Cloud MySQL, encountered frequent slow‑SQL alerts with execution times up to five minutes, caused mainly by missing indexes and lack of pagination.

MySQL Performance

Maximum Data Volume

MySQL imposes no hard limit on the number of rows in a table; the limit depends on the operating system's file‑size constraints.

The Alibaba Java Development Manual suggests splitting tables only when rows exceed 5 million or the table size exceeds 2 GB, though actual performance depends on hardware, MySQL configuration, table design, and index optimization.

Example of a pagination query that fetched the latest 20 rows in 0.6 seconds:

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

As data grows, this approach will eventually become a bottleneck; consider sharding only after exhausting hardware upgrades and schema optimizations.

Maximum Concurrency

Concurrency is controlled by max_connections (instance‑wide limit, up to 16384) and max_user_connections (per‑user limit). Each connection consumes buffers, so setting these values too high can exhaust memory, while setting them too low underutilizes resources.

max_used_connections / max_connections * 100% = 3/100 * 100% ≈ 3%

Check current settings:

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

Adjust in my.cnf:

[mysqld]
max_connections = 100
max_used_connections = 20

Query Time Threshold

Keep single‑query execution time under 0.5 seconds, which aligns with the 3‑second user‑experience rule (database should consume roughly one‑sixth of the total response time).

Implementation Principles

Compared with NoSQL, MySQL is more fragile; therefore, the guiding principle is let the database do less work and let the application do more work .

Use indexes wisely; they consume disk and CPU.

Avoid database functions for formatting—handle it in the application.

Prefer application‑level data integrity over foreign‑key constraints.

In write‑heavy scenarios, enforce uniqueness in the application rather than using unique indexes.

Introduce redundant fields or intermediate tables to trade space for time.

Split long‑running transactions into smaller ones.

Anticipate load and growth for critical tables (e.g., orders) and optimize proactively.

Table Design

Data Types

Select the simplest and smallest data type that satisfies requirements.

Prefer tinyint, smallint, or mediumint over int 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.

Avoid NULL Values

NULL consumes space and complicates index statistics; replace NULL with meaningful defaults when possible.

Text Type Optimization

Large text columns increase table size and degrade performance; store them in a separate table linked by a business key.

Index Optimization

Index Types

Normal index – basic.

Composite index – covers multiple columns.

Unique index – enforces uniqueness, allows NULL.

Composite unique index – combination must be unique.

Primary key – special unique index, no NULL.

Full‑text index – for massive text search (often replaced by Elasticsearch).

Index Tuning Tips

Paginated queries should stay under 30 % of the table; otherwise MySQL may ignore the index.

Limit a table to ≤5 indexes and ≤5 columns per index.

Use prefix indexes for strings (5–8 characters).

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

Example of a covering index:

select login_name, nick_name from member where login_name = ?

SQL Optimization

Batch Processing

Large updates should be broken into batches to avoid blocking other queries.

update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;

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 <> Optimization

The <> operator often cannot use indexes. For highly selective values, consider rewriting with UNION:

select id from orders where amount > 100
union all
select id from orders where amount < 100 and amount > 0;

OR Optimization

OR may prevent composite index usage; rewrite using UNION:

(select id, product_name from orders where mobile_no = '13421800407')
union
(select id, product_name from orders where user_id = 100);

IN Optimization

IN works well when the main table is large and the sub‑table is small; otherwise, consider JOIN:

select id from orders where user_id in (select id from user where level = 'VIP');

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

Avoid Column Functions

Functions like date_format disable index usage. Rewrite using a range condition:

select id from `order` where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

Avoid SELECT *

Fetching all columns forces a full table scan; select only needed columns.

LIKE Optimization

Leading wildcards prevent index usage. Use a trailing wildcard or full‑text index when necessary.

SELECT column FROM table WHERE field LIKE 'keyword%';

JOIN Optimization

Prefer driving the smaller table, add indexes on join columns, increase join buffer size if needed, and avoid joining more than three tables.

LIMIT Optimization

Deep pagination degrades performance; narrow the scan range first.

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

When querying by primary key, use a BETWEEN range for better performance:

select id from orders where id between 1000000 and 1000010 order by id desc;

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.

Reference

https://www.jianshu.com/p/6864abb4d885