Common Interface Performance Optimization Techniques

Background: the system was built and after promotion received many performance complaints, with over 20 slow interfaces, some exceeding 5‑10 seconds and stability below 99.8%.

Common causes listed: database slow queries, complex business logic, unreasonable thread‑pool design, lock design issues, machine problems, and generic “quick fix” approaches.

Database issues:

Deep pagination – using SELECT name,code FROM student LIMIT 1000000,20 forces MySQL to scan millions of rows; solution is to add a condition on indexed column, e.g., SELECT name,code FROM student WHERE id>1000000 LIMIT 20.

Missing index – check with SHOW CREATE TABLE xxxx and add appropriate indexes, considering selectivity.

Index invalidation – use EXPLAIN to diagnose; force index with

SELECT name,code FROM student FORCE INDEX(XXXXXX) WHERE name='天才'

when necessary.

Too many joins or subqueries – avoid excessive joins, replace subqueries with joins, and split large joins into multiple queries processed in application code.

Large IN clauses – limit elements (e.g.,

SELECT id FROM student WHERE id IN (1,2,3,…,1000) LIMIT 200

) and enforce limits in code.

Huge data volume – may require sharding, partitioning, or moving to a database designed for big data.

Business‑logic complexity can be mitigated by parallelizing independent calculations using thread pools, e.g., creating a shared ExecutorService and submitting tasks, or using CompletableFuture to run A and B concurrently and then combine results.

Thread‑pool misconfiguration: core size too small, shared pools causing contention, or task overload leading to queueing; adjust core/max threads, queue size, or create dedicated pools.

Lock design issues: using coarse‑grained synchronized blocks or wrong lock types; narrow the lock scope or use read‑write locks appropriately.

Machine problems such as full GC, restarts, or thread exhaustion also affect latency and require monitoring and capacity planning.

General “quick‑fix” solutions include caching (in‑memory maps, Guava, Redis, Tair), asynchronous callbacks (fast‑success pattern with background processing and Kafka notifications), and other techniques to reduce response time.

Conclusion: the article summarizes the author’s experience with interface performance optimization, encouraging discussion and sharing.