11 Proven Techniques to Boost API Performance by 100×

1. Index Optimization

Check the WHERE and ORDER BY columns for missing indexes, verify index existence with SHOW INDEX FROM `order`, and add indexes using ALTER TABLE `order` ADD INDEX idx_name (name) or CREATE INDEX idx_name ON `order` (name). To modify an index you must drop it first ( ALTER TABLE `order` DROP INDEX idx_name or DROP INDEX idx_name ON `order`).

1.1 No Index

When data volume grows, missing indexes become a bottleneck; add them as shown above.

1.2 Index Not Effective

Use EXPLAIN to view the execution plan and confirm index usage. Common reasons for index loss are shown in the accompanying diagram.

1.3 Wrong Index Chosen

If MySQL picks the wrong index, force the desired one with FORCE INDEX.

2. SQL Optimization

Beyond indexing, refine SQL statements. The author references a separate article listing 15 SQL tuning tricks.

3. Remote Call Optimization

Serial remote calls add up (e.g., 200 ms + 150 ms + 180 ms = 530 ms). Convert to parallel calls using CompletableFuture in 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();
    return userInfo;
}

Remember to use a thread pool to avoid excessive threads.

3.2 Data Duplication

Cache frequently accessed data (e.g., user profile, points, growth) in Redis to eliminate remote calls, while being aware of potential consistency issues.

4. Duplicate Calls

Batch database queries instead of looping over individual 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);
}

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

5. Asynchronous Processing

Separate core business logic from non‑essential tasks (notifications, logging) and execute the latter asynchronously via thread pools or MQ.

5.1 Thread Pool

Wrap non‑core work in a custom executor to prevent data loss on server restarts.

5.2 MQ

Publish messages to a queue; consumers handle the actual work, keeping the API response fast.

6. Avoid Large Transactions

Reduce the scope of @Transactional, move reads outside the transaction, avoid remote calls inside, and limit the amount of data processed per transaction.

7. Lock Granularity

Prefer fine‑grained locks. Use method‑level synchronized only when necessary; otherwise lock a small code block. For distributed environments, employ Redis or Zookeeper distributed locks, ensuring only the critical section (e.g., directory creation) is locked.

8. Pagination

Split large data fetches into smaller batches. For synchronous jobs:

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

For asynchronous APIs, use CompletableFuture to fetch batches concurrently and aggregate results.

9. Caching

Use Redis (or Memcached) for hot data. Example Redis fetch:

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

Introduce a second‑level in‑memory cache (e.g., Caffeine) to further reduce latency:

@Cacheable(value = "category", key = "#categoryKey")
public CategoryModel getCategory(String categoryKey) {
    String json = jedis.get(categoryKey);
    if (StringUtils.isNotEmpty(json)) {
        return JsonUtil.toObject(json);
    }
    return queryCategoryTreeFromDb();
}

10. Sharding (Database Partitioning)

When a single database becomes a bottleneck, split it horizontally (sharding) or vertically. Common sharding strategies include modulo, range, and consistent hashing. Choose vertical split for business domains, horizontal split for large tables.

11. Auxiliary Tools

11.1 Slow Query Log

Enable MySQL slow query logging via SET GLOBAL slow_query_log='ON', set log file and threshold (e.g., SET GLOBAL long_query_time=2).

11.2 Monitoring

Deploy Prometheus to monitor response times, third‑party latency, slow SQL, CPU, memory, disk, and DB metrics.

11.3 Distributed Tracing

Use SkyWalking to trace end‑to‑end request paths via traceId, revealing latency across services, databases, and caches.