Backend Performance Optimization: Common Issues, Root Causes, and Practical Solutions

01. Background

After completing functional development in early 2021, the system entered the promotion phase and began receiving both praise and performance complaints. Monitoring revealed over 20 slow interfaces, with several exceeding 5 seconds and one over 10 seconds, prompting a focused effort on performance optimization.

02. Problem Solving

The following sections outline typical causes and corresponding solutions.

03. Slow Queries (MySQL)

MySQL pagination is usually written as: select name, code from student limit 100,20 When the offset grows large (e.g., limit 1000000,20), MySQL must scan millions of rows, which is inefficient. A better approach is to use a primary‑key condition:

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

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

04. Deep Pagination

Deep pagination suffers from the same issue; using a range condition or keyset pagination mitigates the problem.

05. Missing Indexes

Run show create table <table_name> to inspect existing indexes. Add indexes only when the column has sufficient selectivity; otherwise the index will not be effective. Adding indexes may lock the table, so perform the operation during low‑traffic periods.

06. Index Invalidations

Indexes can become ineffective for several reasons, such as type mismatches, functions on indexed columns, or low cardinality. Use force index(XXXXXX) to test whether an index improves performance.

07. Excessive Joins or Subqueries

Avoid excessive joins and replace subqueries with joins when possible. Too many joined tables can cause MySQL to spill to disk, dramatically slowing queries. Consider fetching data in multiple steps and assembling results in application code.

08. Too Many IN Elements

Large IN lists degrade performance. Split the list into smaller batches or limit the number of elements (e.g., 200) and enforce this limit in code:

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

09. Large Data Volume

When data volume alone causes slowness, consider sharding, partitioning, or migrating to a database designed for big data workloads. This requires extensive planning, migration scripts, and coordination with downstream services.

10. Complex Business Logic

Complex calculations that are independent per item can be parallelized using a thread pool. Example:

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

Parallel version using ExecutorService and Future improves throughput.

11. Loop Calls

Loop‑based independent tasks can be executed concurrently with a shared thread pool, reducing total execution time.

12. Sequential Calls

When calls are independent but sequential, CompletableFuture can run them in parallel:

CompletableFuture<A> futureA = CompletableFuture.supplyAsync(() -> doA());
CompletableFuture<B> futureB = CompletableFuture.supplyAsync(() -> doB());
CompletableFuture.allOf(futureA, futureB).join();
// later combine results

13. Poor Thread Pool Design

Improper core size, max size, or queue capacity can cause tasks to wait or be rejected. Ensure each business domain has an appropriately sized pool and avoid sharing pools across unrelated services.

14. Poor Lock Design

Using a coarse‑grained synchronized block or the wrong lock type (e.g., exclusive lock instead of read‑write lock) reduces concurrency. Narrow the lock scope to only the critical section.

15. Machine Issues (Full GC, Restarts, Thread Saturation)

Full GC pauses, thread leaks, or resource‑exhaustion can degrade performance. Monitor JVM metrics, split large transactions, and adjust thread‑pool parameters accordingly.

16. Generic Solutions

When specific optimizations are exhausted, consider "silver‑bullet" approaches such as redesigning the service, introducing asynchronous processing, or offloading work to dedicated systems.

17. Caching

Cache frequently accessed data using in‑memory structures (e.g., Map, Guava), local caches, or distributed caches like Redis, Tair, or Memcached. Proper key design is crucial for hit‑rate.

18. Callbacks or Reverse Lookups

Implement fast‑success patterns: return success to the caller after essential validation, then perform slow downstream operations asynchronously and notify the caller via a callback or message queue (e.g., Kafka).

19. Conclusion

The article summarizes a wide range of performance‑related issues encountered in backend services, offering practical diagnostics and remediation techniques. Readers are encouraged to discuss, share experiences, and apply the suggested methods to improve system reliability and responsiveness.