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

MySQL Performance

Maximum Data Volume

MySQL does not impose a hard limit on the number of rows in a table; the limit is determined 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 actual limit depends on hardware, MySQL configuration, schema design, and indexing.

Example of a pagination query on a table with hundreds of millions of rows:

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

Although this query performed acceptably at 0.6 seconds for the latest 20 rows, growth will eventually make it untenable, prompting a careful evaluation of sharding versus hardware upgrades.

Maximum Concurrency

Concurrency is governed by max_connections (maximum instance connections, up to 16384) and max_user_connections (per‑user limit). Setting these values too high can exhaust memory; too low underutilizes resources. A practical rule of thumb is to keep the used‑connections ratio above 10%.

Typical commands to view and set these parameters:

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

[mysqld]
max_connections = 100
max_used_connections = 20

Query Time Recommendation (≤0.5 s)

Keeping a single query under 0.5 seconds aligns with the 3‑second user‑experience rule, allocating roughly one‑sixth of total response time to the database.

Implementation Principles

Compared with NoSQL, MySQL is more fragile: scaling, capacity, and concurrency are limited, so the guiding principle is to let the application handle most work while the database does as little as possible.

Use indexes wisely but avoid over‑indexing.

Prefer application‑level data formatting over database functions.

Avoid foreign‑key constraints; enforce data integrity in code.

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 large transactions into smaller ones.

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

Table Design

Data Types

Select the simplest and smallest type that satisfies the requirement: use tinyint , smallint , or mediumint instead of int when possible; use char for fixed‑length strings; avoid text if varchar suffices; use decimal or bigint for precise monetary values; prefer timestamp over datetime for space efficiency.

Avoid NULL Values

NULL consumes storage and complicates index statistics; updating NULL to a non‑NULL value cannot be done in‑place, potentially causing index fragmentation. Replace NULLs with meaningful defaults whenever possible.

Text Type Optimization

Large text columns increase table size and degrade performance; store such data in a separate table and link via a business key.

Index Optimization

Index Types

Normal index – basic index.

Composite index – indexes multiple columns for multi‑column queries.

Unique index – enforces uniqueness, allows NULL.

Composite unique index – unique combination of columns.

Primary key – special unique index, no NULLs.

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

Index Tuning Guidelines

Pagination queries are crucial; if the scanned rows exceed 30 % of the table, 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 such as flags or gender.

Example of a covering index:

select login_name, nick_name from member where login_name = ?

SQL Optimization

Batch Processing

Large updates (e.g., disabling expired coupons) should be broken into batches to prevent blocking other queries.

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. When the distribution is skewed, consider rewriting with 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 on different columns disables 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 is suitable when the master table is large and the sub‑table is small; often interchangeable with EXISTS after optimizer improvements. Example using JOIN instead of IN:

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 ) invalidate the index. Rewrite using range conditions 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 *

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

LIKE Optimization

Leading wildcards prevent index usage. Use a trailing wildcard (e.g., 'keyword%' ) or full‑text index if both sides are needed.

JOIN Optimization

Prefer driving the join with the smaller result set, place join columns under indexes, increase join_buffer_size if necessary, and avoid joining more than three tables.

LIMIT Optimization

Deep pagination degrades performance. Reduce the scanned range by first selecting the target IDs, then fetching the rows.

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

When the condition is a primary key range, use BETWEEN for efficient retrieval.

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

Other Databases

Backend developers should master relational databases such as MySQL or SQL Server while also being aware of mature NoSQL solutions that can address specific performance bottlenecks.