query-performance-safety

Query Performance Safety Guidelines

When code involves batch queries to databases or remote services, prevent high-frequency performance traps such as N+1, overly long IN clauses, and unbounded recursion.

In conjunction with multi-tenant-safety : this skill focuses on "query efficiency"; tenant isolation follows the latter.

---

Trap #1: Calling a single query inside a loop → N+1

Scenario: for / stream / forEach directly calls repo.findById(...) or an equivalent single remote call

Root cause

Each loop iteration triggers one SQL/HTTP call; N elements mean N round trips. 100 elements = 100 SQL calls, 1000 elements = 1000 calls. With little data in local development it is not noticeable, but in production it directly brings down the endpoint.

Sniffing signals

Whenever you see any of the following patterns in a service / handler method, immediately suspect N+1:

// ❌ 模式 1：显式 for
for (Order o : orders) {
    User u = userRepository.findById(o.getUserId()).orElse(null);
}

// ❌ 模式 2：stream 链
orders.stream()
      .map(o -> userRepository.findById(o.getUserId()))
      .collect(...);

// ❌ 模式 3：辅助方法被循环调用
for (Item i : items) {
    enrichItem(i);  // 里面又 findById 一次
}

// ❌ 模式 4：嵌套调用
List<X> list = repoA.findByY(y);
for (X x : list) {
    x.setZ(repoB.findById(x.getZId()).orElse(null));
}

Correct approach

「Fetch in batch first → put into a Map → look up the Map inside the loop" three-step process:

// ✅ 1. 收集所有外键 ID
List<Long> userIds = orders.stream()
        .map(Order::getUserId)
        .filter(Objects::nonNull)
        .distinct()
        .toList();

// ✅ 2. 一次 IN 查询，落到 Map
Map<Long, User> userMap = userRepository
        .findByTenantIdAndIdIn(tenantId, userIds)
        .stream()
        .collect(Collectors.toMap(User::getId, u -> u));

// ✅ 3. 循环内只查 Map，零额外 SQL
for (Order o : orders) {
    User u = userMap.get(o.getUserId());
}

Batch methods the Repository must provide

Single method	Must be paired with	Purpose
findById	findAllById / findByIdIn	Known primary key set
findByTenantIdAndId	findByTenantIdAndIdIn	Multi-tenant scenario batch
findByFooId	findByFooIdIn	Known foreign key set

When adding a method with INmustappear as a pair with the single method, to avoid forcing upper layers to write N+1.

Implicit N+1: Calling a DTO conversion function inside a loop

The most insidious kind of N+1 ——convertToDTO() / toResponseDto() / enrichEntity() helper methods like this appear to "get everything done in one function", but internally they may hide 4-5 findById; calling one inside a loop means N×K SQL calls.

// ❌ 看起来无害，实际触发 24000 次 SQL（6000 商品 × 4 次内部查询）
public Map<String, List<ProductDTO>> findDuplicates() {
    List<Product> products = productRepository.findByTenantId(tenantId);
    Map<String, List<ProductDTO>> groups = new HashMap<>();
    for (Product p : products) {
        String key = normalize(p.getProductName() + p.getSpecification());
        groups.computeIfAbsent(key, k -> new ArrayList<>())
              .add(convertToDTO(p));  // ❌ 内部每次都查 category/supplier/image/price
    }
    return groups;
}

private ProductDTO convertToDTO(Product p) {
    return ProductDTO.builder()
        .categoryName(categoryRepository.findById(p.getCategoryId())...)     // +1
        .supplier(productSupplierRepository.findByProductId(p.getId())...)   // +1
        .imageUrl(productImageRepository.findByProductId(p.getId())...)      // +1
        .priceTiers(priceTierRepository.findByProductId(p.getId())...)       // +1
        .build();
}

Fix strategies

Strategy A: Two-phase processing(recommended, suitable for "filter first, then display")

// ✅ 阶段 1：用 entity 做筛选/分组，零额外查询
Map<String, List<Product>> grouped = new HashMap<>();
for (Product p : products) {
    grouped.computeIfAbsent(key, k -> new ArrayList<>()).add(p);  // entity，不转 DTO
}

// 只对真正要展示的（如重复组 ≥ 2）做后续处理
grouped.entrySet().removeIf(e -> e.getValue().size() < 2);

// 阶段 2：批量预加载关联数据
Set<Long> categoryIds = grouped.values().stream()
    .flatMap(List::stream)
    .map(Product::getCategoryId)
    .filter(Objects::nonNull)
    .collect(Collectors.toSet());
Map<Long, String> categoryNameMap = categoryRepository.findAllById(categoryIds).stream()
    .collect(Collectors.toMap(ProductCategory::getId, ProductCategory::getCategoryName));

// 阶段 3：用 Map 转 DTO，零额外查询
Map<String, List<ProductDTO>> result = new HashMap<>();
grouped.forEach((k, list) -> {
    result.put(k, list.stream()
        .map(p -> toLightweightDTO(p, categoryNameMap))
        .toList());
});

Strategy B: Scenario-specific lightweight DTO converters(recommended, combined with Strategy A)

Use different DTO conversion functions for different scenarios. The full version for detail endpoints, the lightweight version for list/duplicate-check endpoints:

// ✅ 列表/查重：只填实际需要的字段
private ProductDTO toLightweightDTO(Product p, Map<Long, String> categoryNameMap) {
    return ProductDTO.builder()
        .id(p.getId())
        .skuCode(p.getSkuCode())
        .productName(p.getProductName())
        .specification(p.getSpecification())
        .categoryId(p.getCategoryId())
        .categoryName(p.getCategoryId() == null ? null : categoryNameMap.get(p.getCategoryId()))
        .status(p.getStatus())
        .build();
    // 不填 supplier/image/priceTiers，列表场景用不到
}

// 详情：用完整 convertToDTO
public ProductDTO getDetail(Long id) {
    return convertToDTO(productRepository.findById(id).orElseThrow());
}

Strategy C: Batch version of convertToDTO(suitable for list endpoints that must return full DTOs)

public List<ProductDTO> convertToDTOs(List<Product> products) {
    // 一次性预加载全部关联
    Set<Long> categoryIds = products.stream().map(Product::getCategoryId).filter(Objects::nonNull).collect(Collectors.toSet());
    Set<Long> productIds = products.stream().map(Product::getId).collect(Collectors.toSet());

    Map<Long, ProductCategory> categoryMap = categoryRepository.findAllById(categoryIds).stream()
        .collect(Collectors.toMap(ProductCategory::getId, c -> c));
    Map<Long, List<ProductImage>> imageMap = productImageRepository.findByProductIdIn(productIds).stream()
        .collect(Collectors.groupingBy(ProductImage::getProductId));
    // ... 其他关联

    // 转换时只查 Map
    return products.stream()
        .map(p -> toDTOWithPreloaded(p, categoryMap, imageMap, ...))
        .toList();
}

Sniffing signals

During code review, immediately suspect any of the following patterns:

• Calling inside any loop (for/stream/forEach) convertToDTO(...) / toResponseDto(...) / to...DTO(...) / enrich...(...)
• Helper methods that look like they "get it done in one line" but actually span multiple Repositories
• Endpoint response time grows linearly with data volume, but no obvious slow point is visible with a single record
• Number of database SQL calls = list size × some constant

Checklist (all scenarios of Trap #1)

• Search within service methods for findById( / getOne( / getReferenceById( / findOne(, whether it is in a loop or stream chain
• Whether the number of SQL calls in each list-aware method is decoupled from list size (ideally O(1) or O(log N) rather than O(N))
• Whether enrichment helper methods are called in a loop (implicit N+1)
• Whether cross-service calls (A.getDetail() → B.findX()) are inside a loop
• Duplicate-check / list / statistics endpoints: whether DTO conversion is called inside a loop
• Inside DTO conversion functions findXxx Whether the number of calls × list size is acceptable
• Whether "lightweight DTO for lists" vs "full DTO for details" is distinguished
• Does the batch scenario provide convertToDTOs(List<T>) Version

---

Pitfall #2: IN clause is not batched

Scenario: Take a large list and pass it directly to WHERE id IN (...), with no upper limit protection

Root cause of the problem

• MySQL max_allowed_packet(default 16M) exceeding the limit will fail
• Oracle IN at most 1000 parameters per call
• Although PostgreSQL has no hard limit, the parsing and plan generation cost of a single SQL statement grows linearly with the number of parameters
• An excessively long single SQL statement also bypasses the query plan cache

Incorrect example

// ❌ 5000 个 ID 直接塞进 IN
List<User> users = userRepository.findByTenantIdAndIdIn(tenantId, allIds);

Correct approach

Fix IN_BATCH_SIZE = 500, query in batches and then merge:

private static final int IN_BATCH_SIZE = 500;

private List<User> findUsersByIds(Long tenantId, List<Long> ids) {
    List<User> result = new ArrayList<>(ids.size());
    for (int i = 0; i < ids.size(); i += IN_BATCH_SIZE) {
        List<Long> batch = ids.subList(i, Math.min(i + IN_BATCH_SIZE, ids.size()));
        result.addAll(userRepository.findByTenantIdAndIdIn(tenantId, batch));
    }
    return result;
}

Batching for aggregate SUM/COUNT/AVG：

private long sumByBatches(Long tenantId, List<Long> ids) {
    long total = 0L;
    for (int i = 0; i < ids.size(); i += IN_BATCH_SIZE) {
        List<Long> batch = ids.subList(i, Math.min(i + IN_BATCH_SIZE, ids.size()));
        Long val = repository.sumByIds(tenantId, batch);
        if (val != null) total += val;
    }
    return total;
}

JPA users can also use Lists.partition (Guava) or CollectionUtils.partition (Apache Commons)。

Checklist

• Are all lists passed into IN batched (grep IN ( / findByIdIn / findAllById）
• Is the project-level IN_BATCH_SIZE constant uniformly set to 500 or less
• Have aggregate queries (SUM/COUNT) also been batched
• Do entry parameters (such as batch delete API) limit the maximum list length

---

Pitfall #3: BFS/recursive traversal has no total node upper limit

Scenario: Recursive structures such as invitation trees, organizational structures, folders, comment trees, etc., limited by depth but not by totalNodes

Root cause of the problem

depth ≤ 3 looks safe, but the number of nodes per level can explode: level 1=1000 → level 2=100×1000=100k → level 3 may be in the millions. Loading all of it into memory is OOM.

Incorrect example

// ❌ 只限 depth，不限总数
public List<User> collectDescendants(Long tenantId, WxUser self) {
    Map<Long, WxUser> visited = new LinkedHashMap<>();
    visited.put(self.getId(), self);
    List<Long> currentLevelIds = List.of(self.getId());
    for (int depth = 1; depth <= MAX_INVITE_DEPTH && !currentLevelIds.isEmpty(); depth++) {
        List<WxUser> nextLevel = repo.findByInviterIdIn(tenantId, currentLevelIds);
        // 没有总数限制，nextLevel 可能 10w+
        ...
    }
}

Correct approach

Add MAX_TOTAL_NODES hard upper limit + log.warn truncation + document the API semantics:

private static final int MAX_INVITE_DEPTH = 3;
private static final int MAX_TOTAL_NODES = 5000;

public List<User> collectDescendants(Long tenantId, WxUser self) {
    Map<Long, WxUser> visited = new LinkedHashMap<>();
    visited.put(self.getId(), self);

    List<Long> currentLevelIds = List.of(self.getId());
    for (int depth = 1; depth <= MAX_INVITE_DEPTH && !currentLevelIds.isEmpty(); depth++) {
        if (visited.size() >= MAX_TOTAL_NODES) {
            log.warn("BFS 节点达上限 {}，截断: root={}, depth={}",
                     MAX_TOTAL_NODES, self.getId(), depth);
            break;
        }
        List<User> nextLevel = repo.findByInviterIdIn(tenantId, currentLevelIds);
        List<Long> nextIds = new ArrayList<>(nextLevel.size());
        for (User u : nextLevel) {
            if (visited.size() >= MAX_TOTAL_NODES) break;
            if (visited.putIfAbsent(u.getId(), u) == null) {
                nextIds.add(u.getId());
            }
        }
        currentLevelIds = nextIds;
    }
    return new ArrayList<>(visited.values());
}

When to switch to streaming / pagination / async:

• Expected node count > 80% of MAX_TOTAL_NODES → consider switching to paginated/streaming traversal to avoid loading everything at once
• If full statistics are truly needed → use aggregate SQL with SUM/COUNT, do not pull the full set into memory and then .size()/.stream().mapToLong()

Checklist

• Does recursion/BFS have both MAX_DEPTH and MAX_TOTAL_NODES
• When truncating, is log.warn logged (basis for production troubleshooting)
• Do the API comments/documentation explain the "maximum number of returned nodes" semantics
• Do statistical scenarios prefer aggregate SQL over full loading

---

Pitfall #4: Nested service calls cause implicit duplicate queries

Scenario: Within the same API, call getDetail() + countMembers() + getMembers(), and each internal call runs a complete BFS / large table query

Root cause of the problem

Each public service method encapsulates a set of queries, and each looks reasonable on its own; when called in combination, the same BFS runs 3 times. The frontend treats it as "querying 1 API", but the backend is actually 3 times the SQL.

Incorrect example

public TeamDetailVO getMyTeamDetail(Long tenantId, Long userId) {
    int memberCount = teamMemberService.countMembers(tenantId, userId);  // BFS #1
    Long totalPoints = teamMemberService.sumPoints(tenantId, userId);    // BFS #2（内部又调 collectDescendants）
    List<Member> members = teamMemberService.listMembers(tenantId, userId); // BFS #3
    return ...;
}

Correct approach

Extract a shared low-level method returning a list, and the upper layer runs it only once:

public TeamDetailVO getMyTeamDetail(Long tenantId, Long userId) {
    // 单次 BFS 拿到全量队员
    List<User> members = teamMemberService.collectDescendants(tenantId, user);

    int memberCount = members.size();
    Long totalPoints = pointsRepository.sumBySourceUserIds(tenantId,
            members.stream().map(User::getId).toList());
    return TeamDetailVO.builder()
            .memberCount(memberCount)
            .totalPoints(totalPoints)
            .members(toVO(members))
            .build();
}

// countMembers / listMembers 标 @Deprecated 或直接删除，避免外部继续踩坑

Checklist

• Is the same low-level heavy query called multiple times within the same controller method (grep call count)
• Does "countXxx + listXxx" coexist and both run the full query
• When splitting sub-services, has the reused low-level query been promoted to a common method
• Mark @Deprecated ; are the old methods deleted within a reasonable iteration

---

Pitfall #5: Large table WHERE / IN missing index

Scenario: Add findByXxx method, but the xxx column has no index; or add sourceUserId IN (...) aggregation, but the column has only been used in SELECT

Root cause

SUM/IN might still hold up with 1000 rows, but with 5000 rows it goes straight to a full table scan and CPU spikes to 100%. Indexes show no difference on small tables, but at production data scale they determine whether an API lives or dies.

How to check

Every time you add a Repository query method, check it against the entity class's @Index annotation + Flyway migration script:

// ✅ 实体上声明索引
@Entity
@Table(name = "points_pending", indexes = {
    @Index(name = "idx_pending_source_user", columnList = "tenant_id, source_user_id"),
    @Index(name = "idx_pending_confirmed_at", columnList = "tenant_id, confirmed_at"),
})
public class PointsPending { ... }

-- ✅ Flyway 显式建索引（实体注解不会自动建索引到已存在的表）
CREATE INDEX idx_pending_source_user ON points_pending (tenant_id, source_user_id);

Checklist

• When adding findByXxx , does the xxx column have an index (composite index includes tenant_id)
• Whether the WHERE condition hits the index (verify with EXPLAIN , don't guess)
• Range query fields go last in the composite index (e.g. idx (tenant_id, status, created_at)）
• Soft-delete field (deleted_at) also needs to be included in the index when required

---

Pitfall #6: Cache / second-level cache anti-pattern

Scenario: Used @Cacheable but the parameters include Pageable or large objects, causing the cache key to almost never hit; or tenant-sensitive data was cached but the key doesn't include tenantId

Incorrect example

// ❌ key 包含 Pageable，每次翻页都是新 key
@Cacheable("products")
public Page<Product> list(Pageable pageable, String keyword) { ... }

// ❌ 跨租户缓存污染
@Cacheable("user")
public User findById(Long id) { ... }

Correct approach

// ✅ key 显式列出稳定字段 + 缓存粒度匹配业务
@Cacheable(value = "products", key = "#tenantId + ':' + #keyword + ':' + #page")
public List<Product> list(Long tenantId, String keyword, int page) { ... }

// ✅ 租户隔离的缓存 key
@Cacheable(value = "user", key = "#tenantId + ':' + #id")
public User findById(Long tenantId, Long id) { ... }

Checklist

• @Cacheable whether the key is stable (does not contain unstable parameters such as Pageable, Date.now)
• whether the key includes tenantId in multi-tenant scenarios
• whether write operations are paired with @CacheEvict
• whether the cache TTL matches the business consistency requirements (if strong consistency is needed, don't use caching)

---

Performance budget quick reference

Query pattern	Acceptable SQL count	Warning line
Single-entity query API	1–3	> 5
List query (N foreign key joins)	1 + number of foreign key types	Correlating with N is unacceptable
BFS/recursion (≤ MAX_TOTAL_NODES)	depth × 1 + number of IN batches	Linear correlation with node count
Aggregate statistics	1 (SUM) / number of batches	Full load into memory for computation

---

Multi-language examples

For complete Java / Go / Python / Node.js implementations, see references/multi-lang-examples.md。

---

Rule provenance

> 📋 本回复遵循：`query-performance-safety` - [章节名]