Root Causes of API Performance Bottlenecks and Practical Fixes

What problems cause interface performance issues?

Database slow queries

Deep pagination

Missing index

Index invalidation

Too many joins

Too many subqueries

IN clause with excessive values

Large raw data volume

Complex business logic

Loop calls

Sequential calls

Unreasonable thread‑pool design

Unreasonable lock design

Machine problems (full GC, server restarts, thread saturation)

Problem solutions

1. Slow queries (MySQL)

1.1 Deep pagination

MySQL pagination retrieves rows by offset, which becomes inefficient for large offsets because the engine must scan and discard all preceding rows. select name,code from student limit 100,20 When the offset grows to millions, performance degrades sharply. Adding a condition on the primary key avoids scanning:

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

This forces index usage but requires the caller to pass the last retrieved ID.

1.2 Missing index

Identify missing indexes with show create table xxxx and add appropriate indexes, ensuring the indexed column has sufficient selectivity; low‑cardinality indexes may not help and can lock tables during creation.

1.3 Index invalidation

Indexes may become ineffective due to low column selectivity, improper query patterns, or optimizer choices. Examples of low selectivity include columns with only a few distinct values, many NULLs, or heavily skewed distributions. If the optimizer still ignores a useful index, force index (XXXXXX) can be used for testing.

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

1.4 Excessive joins or subqueries

Too many joins or subqueries increase memory usage and may cause MySQL to create temporary tables on disk, dramatically slowing execution. Refactor subqueries into joins where possible, limit the number of joined tables (typically 2‑3), and consider fetching data in multiple steps and assembling it in application code.

1.5 IN clause with many elements

Large IN lists can be slow even with indexes. Split the list into smaller batches or use multithreading. Enforce a reasonable limit in code, e.g., reject queries with more than 200 IDs.

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

if (ids.size() > 200) { throw new Exception("Single query cannot exceed 200 items"); }

1.6 Purely large data volume

When the dataset itself is massive, simple query tuning is insufficient; consider sharding, moving to a purpose‑built data store, or redesigning the storage architecture with thorough planning and migration strategies.

2. Complex business logic

2.1 Loop calls

Repeated independent calculations (e.g., generating data for 12 months) can be parallelized using a shared thread pool.

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("Error", e); }

2.2 Sequential calls

When tasks are independent, use CompletableFuture to run them concurrently.

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

3. Unreasonable thread‑pool design

Key parameters—core size, maximum size, and queue capacity—must be tuned. A too‑small core pool prevents parallelism; shared pools can be blocked by long‑running tasks; an overloaded queue leads to latency. Adjust these settings or create dedicated pools per business domain.

4. Unreasonable lock design

Two common issues: using the wrong lock type (e.g., exclusive lock where a read‑write lock is appropriate) and overly coarse locking. Refactor to narrow the synchronized region or use read‑write locks where reads dominate.

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

5. Machine problems (full GC, restarts, thread saturation)

Root causes include oversized scheduled tasks causing full GC, thread leaks leading to high RSS memory, and other resource exhaustion. Diagnose with monitoring and split large transactions, redesign thread pools, etc.

6. Generic “silver‑bullet” 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.

6.2 Callback / async follow‑up

For slow downstream calls (e.g., payment gateways), return a fast success with an intermediate “processing” state, then invoke the downstream service asynchronously and notify the caller via callback or message queue.