Common Causes of Backend Interface Performance Issues and Their Optimization Strategies

Introduction

When a large number of performance complaints arrive, we realize that API performance must be prioritized. After a week of monitoring, we discovered over 20 slow interfaces, with several exceeding 5 seconds and one over 10 seconds, and overall stability below 99.8%.

1. Problems that cause interface performance issues

Database slow queries

Deep pagination

Missing indexes

Index invalidation

Too many joins or sub‑queries

IN clause with too many elements

Large data volume

Complex business logic

Improper thread‑pool design

Poor lock design

Machine problems (full GC, restarts, thread saturation)

1.1 Slow query (MySQL)

1.1.1 Deep pagination

Typical MySQL pagination: select name,code from student limit 100,20 When the offset grows to millions, MySQL must scan millions of rows before returning the required page, which is very slow. A better approach is to add a condition that uses the primary key:

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

This forces the use of the primary‑key index, but requires the caller to pass the last seen ID.

1.1.2 Missing indexes

Check a table's indexes with: show create table xxxx Add appropriate indexes, but ensure the indexed column has good selectivity; otherwise the index will not be effective. Adding indexes may lock the table, so perform it during low‑traffic periods.

1.1.3 Index invalidation

Reasons for index loss include data type mismatches, functions on indexed columns, or the optimizer choosing not to use the index. You can force index usage:

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

1.1.4 Excessive joins or sub‑queries

Too many joins or sub‑queries can cause MySQL to create temporary tables on disk, dramatically slowing queries. A common mitigation is to split the query: first fetch IDs, then retrieve related data in application code.

1.1.5 IN clause with many elements

If an IN clause with many values is still slow despite proper indexing, split the elements into smaller groups or limit the size (e.g., max 200 elements). Example limit:

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

Enforce the limit in code:

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

1.1.6 Large data volume

When a single table holds billions of rows, simple indexing is insufficient. Solutions include sharding, moving to a database designed for big data, or redesigning the storage architecture.

2. Complex business logic

2.1 Loop calls

When processing independent monthly data, the loop can be parallelized:

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

Parallel version using a shared 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<>();
try {
    for (Future<Model> f : futures) {
        list.add(f.get());
    }
} catch (Exception e) {
    LOGGER.error("出现错误：", e);
}

2.2 Sequential calls without dependency

Even sequential independent calls can be parallelized 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());

3. Thread‑pool design issues

A thread pool has three key parameters: core size, maximum size, and queue capacity. If core threads are too few, parallelism suffers. If the queue fills, non‑core threads are created; if the pool reaches its max, further tasks are rejected according to the rejection policy.

4. Lock design problems

4.1 Inappropriate lock type

Using a mutex where a read‑write lock would be better can degrade performance when reads dominate writes.

4.2 Overly coarse lock

Locking a whole method that performs unrelated work (e.g., calculation, file upload, messaging) wastes time. Refactor to lock only the critical section:

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

5. Machine problems

Full GC, long‑running tasks, or thread leaks can cause the JVM to pause or restart, drastically affecting response times.

6. General “silver‑bullet” solutions

6.1 Caching

Cache frequently read data in memory or fast storage. Options include simple Map, Guava cache, or distributed caches like Redis, Tair, Memcached.

6.2 Asynchronous callbacks / polling

For slow downstream operations (e.g., payment gateway), return a fast success response and later notify the caller via a callback or message queue (Kafka) once the external process completes.

Conclusion

Most interface performance problems can be tackled by identifying the root cause—whether it is a database query, business logic, thread‑pool, lock, or machine issue—and applying the appropriate optimization technique such as query rewrite, indexing, multithreading, proper pool sizing, lock refinement, caching, or asynchronous processing.