Interface Performance Optimization Techniques for Backend Development

Interface performance optimization is a common challenge for backend developers, with causes ranging from missing indexes to inefficient remote calls. This article summarizes practical methods to improve response times.

Indexing : Verify index existence and effectiveness using show index from \(table\); and explain ; add or drop indexes with ALTER TABLE ... ADD INDEX or CREATE INDEX , noting that modification requires delete‑then‑add.

SQL Optimization : After indexing, apply techniques such as avoiding SELECT *, using proper join order, limiting result sets, and leveraging covering indexes; the article lists 15 tips (details omitted for brevity).

Remote Call Optimization : Replace serial calls with parallel execution using CompletableFuture.supplyAsync and a thread pool, reducing total latency from the sum to the longest call. Alternatively, introduce data redundancy (e.g., caching user‑related data in Redis) to eliminate remote calls, accepting possible consistency trade‑offs.

Avoiding Duplicate Calls : Replace per‑item database queries in loops with batch queries ( userMapper.getUserByIds(ids) ) and guard against dead loops or infinite recursion by adding depth limits or using proper termination conditions.

Asynchronous Processing : Off‑load non‑core logic (e.g., sending notifications, writing logs) to a thread pool or message queue (MQ) so the main thread focuses on core business, improving throughput while noting retry limitations of raw thread pools.

Avoid Large Transactions : Minimize the scope of @Transactional , move read‑only selects outside transactions, avoid remote calls inside transactions, and consider asynchronous or non‑transactional execution for auxiliary work.

Lock Granularity : Prefer block‑level synchronized over method‑level to reduce contention; in distributed environments use Redis‑based distributed locks (SET with NX/PX) or database row locks, choosing row locks for highest concurrency.

Pagination Handling : For large ID collections, split into batches (e.g., using Guava’s Lists.partition ) and process them synchronously or asynchronously with CompletableFuture to keep each remote call under latency thresholds.

Caching : Use Redis as a primary cache; for higher performance add a second‑level in‑memory cache (Caffeine) with expiration and size limits, being aware of consistency challenges in multi‑node deployments.

Sharding (分库分表) : Apply vertical sharding to isolate services or horizontal sharding to distribute data by ID range, modulo, or consistent hashing; sharding alleviates connection pressure, disk I/O, and large‑table query costs.

Auxiliary Features : Enable MySQL slow‑query log ( slow_query_log=ON , long_query_time=2 ) to spot problematic SQL; monitor system metrics with Prometheus (QPS, latency, CPU, memory, DB); trace end‑to‑end request paths with SkyWalking to identify latency contributors across services, Redis, and DB.