Comprehensive Guide to Identifying and Solving Backend Interface Performance Issues

Our system was completed in early 2021 and entered the promotion phase, quickly receiving both praise and many performance complaints. After a week of monitoring we discovered over 20 slow interfaces, several exceeding 5 seconds, and overall stability below 99.8%, prompting a deep dive into performance optimization.

Which problems cause interface performance issues?

The causes are numerous and must be analyzed per business scenario. The most common categories are:

Database slow queries

Deep pagination

Missing indexes

Index loss

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

1. Slow queries (MySQL)

1.1 Deep pagination

MySQL pagination with LIMIT fetches all rows up to the offset, which becomes extremely slow for large offsets. select name,code from student limit 100,20 For large offsets, rewrite the query to use a primary‑key condition:

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

1.2 Missing indexes

Check existing indexes with: show create table xxxx When adding indexes, ensure the indexed column has sufficient selectivity and perform the operation during low‑traffic periods to avoid locking.

1.3 Index loss

Indexes may become ineffective due to low cardinality, functions on indexed columns, or MySQL’s optimizer choosing a full scan. Use FORCE INDEX to test forced usage:

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

1.4 Excessive joins or sub‑queries

Prefer joins over sub‑queries and limit the number of joined tables to 2‑3. For large joins, consider fetching data in separate queries and assembling results in application code.

1.5 Too many IN elements

If an IN clause is slow despite proper indexing, split the values into smaller batches or limit the total number:

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

Enforce a limit in code:

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

1.6 Pure data volume

When a table grows to billions of rows, consider sharding, partitioning, or migrating to a database designed for big data workloads.

2. Complex business logic

2.1 Loop calls

Parallelize independent calculations with a thread pool:

List<Model> list = new ArrayList<>();
for (int i = 0; i < 12; i++) {
    Model model = calOneMonthData(i);
    list.add(model);
}
// Parallel version
ExecutorService pool = 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++) {
    futures.add(pool.submit(() -> calOneMonthData(i)));
}
List<Model> list = new ArrayList<>();
for (Future<Model> f : futures) {
    list.add(f.get());
}

2.2 Sequential calls

When calls are independent, use CompletableFuture to run them concurrently:

CompletableFuture<A> futureA = CompletableFuture.supplyAsync(() -> doA());
CompletableFuture<B> futureB = CompletableFuture.supplyAsync(() -> doB());
CompletableFuture.allOf(futureA, futureB);
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);
return doResult(futureD.join(), futureE.join());

3. Thread‑pool design issues

Key parameters are core size, maximum size, and queue capacity. An undersized core pool prevents parallelism; a shared pool can be blocked by long‑running tasks from other services; an overloaded queue leads to task backlog.

4. Lock design issues

Using a coarse‑grained synchronized block for unrelated operations 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, restarts, thread saturation)

These require monitoring, workload splitting, and proper thread‑pool sizing to avoid resource exhaustion.

6. General “silver‑bullet” solutions

6.1 Caching

Cache frequently read, rarely changed data using in‑memory maps, Guava, or distributed caches like Redis, Tair, or Memcached. Proper key design is crucial for hit rate.

6.2 Callback / async processing

Return a fast success response after validation and persistence, then process slow downstream calls (e.g., bank APIs) asynchronously, notifying the caller via callbacks or message queues such as Kafka.

Source: juejin.cn/post/7043423820543164453