A Comprehensive Summary of Our Backend Interface Performance Optimization Journey

Background

I completed the functional construction of the system in early 2021 and entered the promotion phase. While receiving many praises, we also got numerous performance complaints. When the complaints started, we felt frustrated; after a week of monitoring, we discovered over 20 slow interfaces, with several exceeding 5 seconds and one over 10 seconds, and stability below 99.8 %.

Realizing that interface performance must be prioritized, we began a long journey of optimization, which this article summarizes.

What Problems Cause Interface Performance Issues?

Database slow queries

Deep pagination

Missing indexes

Index loss

Too many joins

Too many sub‑queries

Too many values in IN clause

Large data volume

Complex business logic

Loop calls

Sequential calls

Unreasonable thread‑pool design

Unreasonable lock design

Machine problems (full GC, machine restart, thread saturation)

Problem Solutions

1. Slow Queries (MySQL)

1.1 Deep Pagination

MySQL pagination typically uses LIMIT offset, count. For small offsets this works, but large offsets cause MySQL to scan and discard many rows, leading to poor performance. select name,code from student limit 100,20 When the offset grows to a million, MySQL still reads 1,000,020 rows before discarding the first million. select name,code from student limit 1000000,20 A better approach is to use a condition on an indexed column, e.g., the primary key:

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

This forces MySQL to use the primary‑key index and jump directly to the required rows, but it requires the caller to pass the last maximum id.

1.2 Missing Indexes

Check existing indexes with: show create table xxxx When adding indexes, consider selectivity; low‑selectivity indexes may be ineffective and can lock tables during ALTER, so perform them during low‑traffic periods.

1.3 Index Loss

Even if an index exists, MySQL may not use it due to query patterns. Common reasons include low column selectivity, functions on indexed columns, or implicit type conversion. You can force index usage:

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

1.4 Too Many Joins or Sub‑queries

Prefer joins over sub‑queries and keep the number of joined tables to 2‑3 unless data volumes are tiny. Large joins may cause MySQL to create temporary tables on disk, drastically slowing performance. A typical mitigation is to fetch data in two steps: first query the primary table, then batch‑fetch related data and assemble in application code.

1.5 Excessive IN Elements

If an IN clause is slow despite proper indexing, the element count may be too large. Split the IN list into smaller batches or limit the size in code:

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

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

1.6 Pure Large Data Volume

When data size itself is huge, simple query tuning is insufficient. Solutions involve sharding, partitioning, or migrating to a database designed for big data, accompanied by thorough design, testing, and migration plans.

2. Complex Business Logic

2.1 Loop Calls

When the same logic is executed repeatedly without inter‑dependency (e.g., initializing 12 months of data), parallel execution via a thread pool can improve throughput.

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

Parallel version:

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

If tasks are independent, they can also be parallelized using CompletableFuture:

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. Unreasonable Thread‑Pool Design

Common issues include too few core threads, shared pools causing contention, and task queues filling up. Adjust core size, max size, and queue capacity, or create separate pools per business domain.

4. Unreasonable Lock Design

Two typical problems: using the wrong lock type (e.g., exclusive lock where read‑write lock is appropriate) and overly coarse lock granularity. Refactor to narrow the synchronized block:

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

5. Machine Problems (Full GC, Restarts, Thread Saturation)

Root causes vary: oversized scheduled tasks causing full GC, thread leaks leading to high RSS memory, etc. Solutions involve splitting large transactions, redesigning thread pools, and thorough monitoring.

6. General‑Purpose Solutions

6.1 Caching

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

Simple Map Guava local cache

Redis / Tair / Memcached

6.2 Callback / Reverse Lookup

For slow downstream services (e.g., bank APIs), return a fast “processing” response and notify the caller later via callback or message queue (Kafka) once the downstream result is ready.

In summary, a combination of query optimization, concurrency tuning, lock refinement, machine health monitoring, and caching can dramatically improve backend interface performance.