Backend Interface Performance Optimization Techniques

1. Index Optimization

Adding appropriate indexes is the cheapest way to improve query performance. Check whether the columns used in WHERE or ORDER BY have indexes, create them with ALTER TABLE … ADD INDEX or CREATE INDEX, and verify effectiveness with EXPLAIN. Remember that modifying an index requires dropping and recreating it.

show index from `order`;
show create table `order`;
ALTER TABLE `order` ADD INDEX idx_name (name);
CREATE INDEX idx_name ON `order` (name);
ALTER TABLE `order` DROP INDEX idx_name;
DROP INDEX idx_name ON `order`;

1.1 Index Not Added

When a query lacks an index on the filter or sort columns, performance degrades as data grows. Adding the missing index resolves the issue.

1.2 Index Not Effective

Use EXPLAIN to see if an index is used. Common reasons for index loss include functions on indexed columns, type mismatches, or low selectivity.

1.3 Wrong Index Chosen

MySQL may select a sub‑optimal index; you can force the desired index with FORCE INDEX.

2. SQL Optimization

After indexing, further reduce execution time by rewriting inefficient SQL. The article references 15 small SQL‑tuning tricks (details omitted).

3. Remote Calls

Serial remote calls accumulate latency. Parallelize them with Callable (pre‑Java 8) or CompletableFuture (Java 8+). Example using CompletableFuture to fetch user, bonus, and growth data concurrently.

public UserInfo getUserInfo(Long id) throws InterruptedException, ExecutionException {
    final UserInfo userInfo = new UserInfo();
    CompletableFuture userFuture = CompletableFuture.supplyAsync(() -> {
        getRemoteUserAndFill(id, userInfo);
        return Boolean.TRUE;
    }, executor);
    CompletableFuture bonusFuture = CompletableFuture.supplyAsync(() -> {
        getRemoteBonusAndFill(id, userInfo);
        return Boolean.TRUE;
    }, executor);
    CompletableFuture growthFuture = CompletableFuture.supplyAsync(() -> {
        getRemoteGrowthAndFill(id, userInfo);
        return Boolean.TRUE;
    }, executor);
    CompletableFuture.allOf(userFuture, bonusFuture, growthFuture).join();
    userFuture.get();
    bonusFuture.get();
    growthFuture.get();
    return userInfo;
}

3.1 Parallel Invocation

Parallel remote calls reduce total latency to the longest single call.

3.2 Data Duplication

Cache aggregated data (e.g., user profile) in Redis to avoid repeated remote calls, but be aware of consistency issues.

4. Repeated Calls

Looping database queries for each item is costly. Batch queries using IN or a dedicated method that accepts a collection of IDs.

public List<User> queryUser(List<User> searchList) {
    if (CollectionUtils.isEmpty(searchList)) {
        return Collections.emptyList();
    }
    List<Long> ids = searchList.stream().map(User::getId).collect(Collectors.toList());
    return userMapper.getUserByIds(ids);
}

4.1 Loop Query

Replace per‑item queries with a single batch query.

4.2 Infinite Loop

Improper use of while(true) can cause dead loops, especially in CAS spin locks.

4.3 Infinite Recursion

Recursive methods must have a depth guard to prevent stack overflow.

5. Asynchronous Processing

Separate core business logic (synchronous) from non‑critical tasks (notifications, logging) and execute the latter asynchronously via thread pools or message queues.

5.1 Thread Pool

Submit non‑core tasks to a custom executor to avoid blocking the main request thread.

5.2 Message Queue

Publish a message to MQ; a consumer processes the notification or log later, further reducing request latency.

6. Large Transactions

Avoid long‑running @Transactional scopes. Keep read‑only queries outside transactions, limit the amount of data processed, and move non‑essential work out of the transaction.

7. Lock Granularity

Fine‑grained locking improves concurrency. Use method‑level synchronized only when necessary; prefer block‑level locking or distributed locks (Redis, Zookeeper, DB) for multi‑node environments.

7.1 Synchronized

Lock only the critical section (e.g., directory creation) to reduce contention.

7.2 Redis Distributed Lock

Acquire a lock with SET key value NX PX ttl, perform the protected operation, then release it.

public void doSave(String path, String fileUrl) {
    try {
        String result = jedis.set(lockKey, requestId, "NX", "PX", expireTime);
        if ("OK".equals(result)) {
            if (!exists(path)) {
                mkdir(path);
                uploadFile(fileUrl);
                sendMessage(fileUrl);
            }
            return true;
        }
    } finally {
        unlock(lockKey, requestId);
    }
    return false;
}

7.3 Database Locks

Prefer row‑level locks over table‑level locks for higher concurrency.

8. Pagination

Split large batch requests into smaller pages. Use synchronous pagination for background jobs and asynchronous parallel calls for latency‑sensitive APIs.

List<List<Long>> allIds = Lists.partition(ids, 200);
for (List<Long> batchIds : allIds) {
    List<User> users = remoteCallUser(batchIds);
}

final List<User> result = Lists.newArrayList();
allIds.forEach(batchIds -> {
    CompletableFuture.supplyAsync(() -> {
        result.addAll(remoteCallUser(batchIds));
        return Boolean.TRUE;
    }, executor);
});

9. Caching

Introduce Redis cache for read‑heavy data, and optionally a second‑level in‑memory cache (Caffeine) to avoid network round‑trips.

String json = jedis.get(key);
if (StringUtils.isNotEmpty(json)) {
    CategoryTree categoryTree = JsonUtil.toObject(json);
    return categoryTree;
}
return queryCategoryTreeFromDb();

Configure Caffeine with expiration and size limits, then annotate service methods with @Cacheable.

10. Sharding (Database Partitioning)

When a single database becomes a bottleneck, split data across multiple databases or tables using modulo, range, or consistent‑hash routing. Horizontal sharding reduces connection pressure and I/O latency.

11. Auxiliary Tools

Enable MySQL slow‑query logs, deploy Prometheus for metrics and alerts, and use SkyWalking for distributed tracing to quickly locate performance hotspots.