Comprehensive Guide to Backend Interface Performance Optimization

Background

The system completed functional development in early 2021 and entered the promotion phase, receiving both praise and performance complaints. Over a week of monitoring, more than 20 slow interfaces were identified, with several exceeding 5 seconds and one over 10 seconds, prompting a deep dive into optimization.

Typical Causes of Interface Performance Issues

Database slow queries

Deep pagination

Missing indexes

Index invalidation

Too many joins

Too many sub‑queries

Excessive IN values

Large data volume

Complex business logic

Loop calls

Sequential calls

Improper thread‑pool design

Improper lock design

Machine issues (full GC, restarts, thread saturation)

Problem Solutions

MySQL Slow Query – Deep Pagination

Typical pagination query: select name, code from student limit 100, 20 When the offset becomes large, MySQL must scan many rows, causing slowdown. A better approach is to use a primary‑key condition:

select name, code from student where id > 1000000 limit 20

This forces index usage but requires the caller to pass the last max id.

Missing Index

Check table definition with: show create table xxxx Add appropriate indexes, ensuring the indexed column has sufficient selectivity and performing the operation during low‑traffic periods.

Index Invalid

When MySQL does not use an expected index, you can force it:

select name, code from student force index(XXXXXX) where name = '天才'

Too Many Joins or Sub‑queries

Prefer joins over sub‑queries and limit the number of tables joined (generally 2‑3). For large joins, consider fetching data in multiple steps and assembling results in application code.

Excessive IN Elements

Split large IN lists into smaller batches or limit the number of elements (e.g., 200). Example:

select id from student where id in (1,2,3, ... 1000) limit 200

Programmatic guard:

if (ids.size() > 200) {
    throw new Exception("单次查询数据量不能超过200");
}

Large Data Volume

When data size alone causes slowness, consider sharding, moving to a more suitable database, or redesigning the storage architecture.

Complex Business Logic – Loop Calls

Example of sequential month calculations:

List<Model> list = new ArrayList<>();
for (int i = 0; i < 12; i++) {
    Model model = calOneMonthData(i);
    list.add(model);
}

Parallelize with a thread pool:

public static ExecutorService commonThreadPool = new ThreadPoolExecutor(
    5, 5, 300L, TimeUnit.SECONDS,
    new LinkedBlockingQueue<>(10), commonThreadFactory,
    new ThreadPoolExecutor.DiscardPolicy());

List<Future<Model>> futures = new ArrayList<>();
for (int i = 0; i < 12; i++) {
    Future<Model> future = commonThreadPool.submit(() -> calOneMonthData(i));
    futures.add(future);
}

List<Model> list = new ArrayList<>();
for (Future<Model> f : futures) {
    list.add(f.get());
}

Complex Business Logic – Sequential Calls

Original sequential code:

A a = doA();
B b = doB();
C c = doC(a, b);
D d = doD(c);
E e = doE(c);
return doResult(d, e);

Parallel version using CompletableFuture:

CompletableFuture<A> futureA = CompletableFuture.supplyAsync(() -> doA());
CompletableFuture<B> futureB = CompletableFuture.supplyAsync(() -> doB());
CompletableFuture.allOf(futureA, futureB).join();
C c = doC(futureA.join(), futureB.join());
CompletableFuture<D> futureD = CompletableFuture.supplyAsync(() -> doD(c));
CompletableFuture<E> futureE = CompletableFuture.supplyAsync(() -> doE(c));
CompletableFuture.allOf(futureD, futureE).join();
return doResult(futureD.join(), futureE.join());

Thread‑Pool Design Issues

Improper core size, queue saturation, or max‑thread limits can cause tasks to wait or be rejected. Adjust core/max sizes, separate pools per business domain, and monitor queue depth.

Lock Design Issues

Using a coarse‑grained synchronized block can block unrelated operations. Refactor to narrow the lock scope:

public void doSome() {
    File f = null;
    synchronized (this) {
        f = calData();
    }
    uploadToS3(f);
    sendSuccessMessage();
}

Machine Problems

Full GC, thread leaks, or resource exhaustion can degrade performance; address by profiling, limiting task sizes, and isolating thread pools.

Universal Remedies

Caching

Use in‑memory or distributed caches (e.g., map, Guava, Redis, Tair, Memcached) to store frequently accessed data, reducing database load.

Callback / Async Processing

For long‑running external calls (e.g., payment gateways), return a fast success response and notify the caller later via callbacks or message queues (Kafka).

Conclusion

The author shares a concise summary of performance‑optimization experiences, inviting discussion and further knowledge exchange.