Interface Performance Optimization: Indexes, SQL Tuning, Remote Calls, Asynchronous Processing, Caching, and More

Preface

Interface performance optimization is a common concern for backend developers regardless of the programming language. The causes are varied—sometimes a missing index solves the problem, other times code refactoring, caching, middleware (e.g., MQ), sharding, or service splitting is required. This article summarizes effective methods for optimizing interface performance.

1. Indexes

Adding or fixing indexes is usually the cheapest way to improve query speed. Use show index from `order`; to view indexes, ALTER TABLE `order` ADD INDEX idx_name (name); or CREATE INDEX idx_name ON `order` (name); to add them. To modify an index you must drop it first with ALTER TABLE `order` DROP INDEX idx_name; or DROP INDEX idx_name ON `order`; . Verify index usage with EXPLAIN and address common reasons for index failure (e.g., missing columns, wrong index selection, or forced index usage).

1.1 Missing Index

Often the WHERE clause or ORDER BY fields lack an index, especially as data volume grows.

1.2 Index Not Effective

Use EXPLAIN to check if the index is used. Common causes of ineffective indexes are shown in the accompanying diagrams.

1.3 Wrong Index Selected

MySQL may choose a sub‑optimal index; you can force a specific index with FORCE INDEX .

2. SQL Optimization

If indexing does not help, rewrite the SQL. The article references 15 small SQL‑tuning tricks (details in a linked article).

3. Remote Calls

Serial remote calls add up latency (e.g., 530 ms = 200 ms + 150 ms + 180 ms). Parallelize calls using Callable (pre‑Java 8) or CompletableFuture (Java 8+):

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;
}

Remember to use a thread pool to avoid excessive thread creation.

3.2 Data Duplication

Store frequently accessed composite data (e.g., user profile, points, growth) in Redis to eliminate remote calls, but be aware of consistency issues.

4. Repeated Calls

Repeated database queries inside loops cause many remote calls. Batch queries reduce calls dramatically:

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

Limit batch size (e.g., ≤ 500) to avoid overwhelming the DB.

4.2 Infinite Loops & Recursion

Improper loops or recursive calls can cause deadlocks or stack overflows. Set recursion depth limits to prevent infinite recursion.

5. Asynchronous Processing

Separate core business logic from non‑critical tasks (notifications, logging) using multithreading or MQ. This reduces request latency.

5.1 Thread Pool

Submit non‑critical tasks to a dedicated thread pool.

5.2 MQ

Publish messages to MQ; consumers handle the actual work asynchronously.

6. Avoid Large Transactions

Large @Transactional scopes can cause timeouts. Recommendations: limit transaction size, move reads outside transactions, avoid remote calls inside transactions, and consider async handling for non‑critical work.

7. Lock Granularity

Coarse‑grained synchronized locks degrade performance. Prefer fine‑grained locking or distributed locks (Redis, Zookeeper, DB). Example of reducing lock scope:

public void doSave(String path, String fileUrl) {
    synchronized (this) {
        if (!exists(path)) {
            mkdir(path);
        }
    }
    uploadFile(fileUrl);
    sendMessage(fileUrl);
}

For distributed environments, use Redis lock patterns:

public boolean tryLock() {
    try {
        String result = jedis.set(lockKey, requestId, "NX", "PX", expireTime);
        if ("OK".equals(result)) {
            return true;
        }
    } finally {
        unlock(lockKey, requestId);
    }
    return false;
}

8. Pagination

Split large batch requests into pages (e.g., 200 IDs per page) and process synchronously or asynchronously using Lists.partition and CompletableFuture .

9. Caching

Use Redis or Memcached for hot data, and a second‑level in‑memory cache (Caffeine) for ultra‑fast reads. Example Caffeine configuration and @Cacheable usage are provided.

10. Sharding (Database Partitioning)

When a single database becomes a bottleneck, split data across multiple databases or tables using modulo, range, or consistent‑hash strategies. Choose vertical or horizontal sharding based on concurrency and data volume.

11. Auxiliary Tools

Enable MySQL slow‑query logs, monitor with Prometheus, and trace distributed calls with SkyWalking to quickly locate performance bottlenecks.

For further reading, see the linked articles at the end of the original document.