Comprehensive Summary of Interface Performance Issues and Optimization Strategies

Background: After a system entered the promotion phase, the author received many performance complaints, discovering over 20 slow interfaces, several with response times exceeding 5 seconds and stability below 99.8 %.

What can cause interface performance problems?

Database slow queries

Complex business logic

Improper thread‑pool design

Improper lock design

Machine issues (full GC, restarts, thread saturation)

Generic "quick‑fix" approaches

Solutions:

1. Slow queries (MySQL)

① Deep pagination

Typical pagination query: select name,code from student limit 100,20 When the offset becomes large, MySQL must scan and discard many rows, causing severe slowdown.

Solution: add a condition to use the primary‑key index:

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

② Missing index

Check a table’s indexes with: show create table xxxx Add appropriate indexes, but consider index selectivity.

③ Index ineffective

Use force index when MySQL ignores an index:

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

④ Excessive joins or sub‑queries

Reduce join count, replace sub‑queries with joins, or split queries in code and assemble results manually.

⑤ Too many IN elements

Large IN lists can be split into batches or limited (e.g., max 200 IDs):

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

⑥ Large data volume

When data size is massive, consider sharding, separate databases, or moving to a data‑warehouse solution.

2. Complex business logic

① Loop calls

Parallelize independent month‑by‑month calculations with 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++) { futures.add(commonThreadPool.submit(() -> calOneMonthData(i))); }

② Sequential calls with independent steps

Use CompletableFuture to run A and B in parallel, then combine results:

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

Core size too small, shared pool contention, or task overload can cause queues to fill and tasks to be rejected. Adjust core/max sizes, separate pools per business, and monitor queue depth.

4. Lock design issues

Using a coarse‑grained synchronized block for unrelated operations reduces concurrency. 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, thread leaks, or resource exhaustion can degrade performance; monitor and isolate such issues.

6. Generic "quick‑fix" methods

Cache frequently accessed data (local map, Guava, Redis, Tair).

Fast‑success pattern with asynchronous callbacks or Kafka notifications for long‑running downstream calls.

Conclusion: By systematically diagnosing the root causes—ranging from SQL inefficiencies to concurrency bottlenecks—and applying targeted fixes such as proper indexing, thread‑pool tuning, lock refinement, sharding, and caching, interface latency can be dramatically reduced.