MySQL Performance Optimization: Data Volume, Concurrency, Index Design, and SQL Tuning

MySQL Performance

MySQL has no hard limit on the number of rows per table; the limit is imposed by the operating system's file size constraints. The Alibaba Java Development Manual suggests that tables exceeding 5 million rows or 2 GB should consider sharding, but the real performance factors are hardware, MySQL configuration, table design, and index optimization.

Maximum Data Volume

In a test with over 400 million rows, a paginated query retrieving the latest 20 records took 0.6 seconds using the following SQL:

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

Although acceptable at that moment, continued growth will eventually make the query too slow, so sharding should be a last resort after hardware upgrades and data archiving.

Maximum Concurrency

Concurrency is controlled by max_connections (maximum instance connections, up to 16384) and max_user_connections (per‑user limit). Each connection consumes memory, so the ratio of used connections to max connections should stay above 10%:

max_used_connections / max_connections * 100% ≈ 3%

Typical configuration in my.cnf :

[mysqld]
max_connections = 100
max_used_connections = 20

Query Latency (0.5 s)

It is recommended to keep a single query execution time under 0.5 seconds, which corresponds to the user‑experience rule that the whole UI response should be within 3 seconds.

Implementation Principles

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

Use indexes wisely; they consume disk and CPU.

Avoid database functions for formatting; let the application format data.

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

In write‑heavy scenarios, avoid unique indexes and enforce uniqueness in the application.

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

Split long‑running transactions into smaller ones.

Forecast growth of critical tables (e.g., orders) and optimize early.

Data Table Design

Data Types

Select the simplest and smallest data type that satisfies the requirement:

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 values.

Prefer timestamp over datetime to save space and handle time zones automatically.

Avoid NULL Values

NULL columns still occupy space and complicate index statistics; replace NULLs with meaningful defaults whenever possible.

Text Type Optimization

Large text fields should be moved to a separate table and linked by the business primary key to keep the main table slim.

Index Optimization

Index Types

Normal index – basic single‑column index.

Composite index – index on multiple columns for multi‑condition queries.

Unique index – enforces uniqueness, allows NULL.

Composite unique index – combination of columns must be unique.

Primary key index – special unique index, no NULLs.

Full‑text index – for massive text search (MySQL 5.6+), though many prefer Elasticsearch.

Index Tuning Tips

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

Limit the number of indexes per table to ≤5 and columns per index to ≤5.

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

Avoid indexing low‑cardinality columns such as boolean flags.

Example of a covering index:

select login_name, nick_name from member where login_name = ?

Creating a composite index on login_name and nick_name speeds up the query compared to a single‑column index.

SQL Optimization

Batch Processing

Large updates (e.g., disabling expired coupons) should be processed in batches to avoid blocking other queries. Example pseudo‑code:

int pageNo = 1;
int PAGE_SIZE = 100;
while (true) {
    List
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 prevents index usage. For a query selecting orders where amount is not 100, rewrite using UNION of two range queries:

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

OR Optimization

OR conditions on different columns can’t use a composite index. Split the query with UNION:

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

IN Optimization

When the sub‑query returns a small set, replace IN with a JOIN for better performance:

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

Avoid Column Operations

Functions on indexed columns (e.g., date_format ) disable index usage. Rewrite the condition to a range on the raw column:

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

Avoid SELECT *

Only retrieve needed columns to prevent full‑table scans.

LIKE Optimization

Leading wildcards prevent index usage. Use a trailing wildcard when possible, or consider full‑text indexes or Elasticsearch for flexible matching.

JOIN Optimization

Drive the join with the smaller result set and add as many ON‑conditions as possible.

Index the join columns of the driven table.

Increase join_buffer_size if indexes cannot be created.

Avoid joining more than three tables; use denormalization if needed.

LIMIT Optimization

Deep pagination degrades performance. Reduce the scanned range by first selecting the relevant IDs:

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

This approach brings the execution time down to about 0.5 seconds compared with several seconds for naive offset pagination.

Other Databases

Backend developers should master MySQL or SQL Server as the core storage engine while also staying aware of mature NoSQL solutions that can address specific performance bottlenecks.