cc-query-performance-safety

Query Performance Safety Guidelines

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

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

---

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

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

Root cause of the problem

Each loop iteration triggers one SQL/HTTP round trip; N elements mean N round trips. 100 elements = 100 SQL queries, and 1,000 elements = 1,000. It is hard to notice in local development with little data, but in production it directly brings down the endpoint.

Sniffing signals

Whenever any of the following patterns appears 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

「Batch-fetch first → put into a Map → look up in the Map inside the loop" three-step approach:

// ✅ 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 that the Repository must provide

Single-record 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 an IN clauseMustappear as a pair with the single-record 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, and calling one inside a loop means N×K SQL queries.

// ❌ 看起来无害，实际触发 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 is for detail endpoints; the lightweight version is for list/deduplication 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 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...(...)
• A helper method that looks like it "gets it done in one line" but actually spans multiple Repositories
• Endpoint response time grows linearly with data volume, but no obvious slow point is visible for a single record
• Number of database SQL queries = list size × some constant

Checklist (all scenarios of Trap #1)

• Search service methods for findById( / getOne( / getReferenceById( / findOne(, whether they are in a loop or stream chain
• For each list-aware method, whether the number of SQL queries 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
• For deduplication / 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 list DTO" vs "full detail DTO" is distinguished
• Does the batch scenario provide convertToDTOs(List<T>) version

---

Trap #2: IN clause not batched

Scenario: a large list is passed directly to WHERE id IN (...), with no upper limit protection

Root cause

• 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 overly long single SQL statement also bypasses the query plan cache

Incorrect example

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

Correct approach

Fixed 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

• Whether all lists passed into IN are batched (grep IN ( / findByIdIn / findAllById）
• Whether the project-level IN_BATCH_SIZE constant is uniformly 500 or smaller
• Aggregate queries (SUM/COUNT) are also batched
• Whether entry parameters (such as batch delete APIs) limit the maximum list length

---

Trap #3: BFS/recursive traversal with no total node limit

Scenario: recursive structures such as invitation trees, org charts, folders, comment trees, etc., limited by depth but not by totalNodes

Root cause

depth ≤ 3 may look safe, but the number of nodes per level can explode: level 1=1000 → level 2=100×1000=100,000 → level 3 may be in the millions. Loading all of them into memory means 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 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 really needed → use SUM/COUNT aggregate SQL, do not pull the full set into memory and then .size()/.stream().mapToLong()

Checklist

• Whether recursion/BFS has both MAX_DEPTH and MAX_TOTAL_NODES
• Whether truncation log.warn leaves a trace (basis for production troubleshooting)
• Whether API comments/docs explain the "maximum number of returned nodes" semantics
• Whether statistical scenarios prefer aggregate SQL over full loading

---

Trap #4: Nested service calls causing implicit duplicate queries

Scenario: calling getDetail() + countMembers() + getMembers()within the same API, each internally running a full BFS / large table query

Root cause

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 "calling 1 API", but the backend actually runs 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 that returns a list, and run it only once at the upper layer:

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

• Whether the same controller method calls the same underlying heavy query multiple times (grep call count)
• Whether there is "countXxx + listXxx" coexisting and both running the full query
• When splitting sub-services, whether the reused underlying query is promoted to a common method
• Whether old methods marked @Deprecated are removed within a reasonable iteration

---

Trap #5: Missing index on large table WHERE / IN

Scenario: Add a findByXxx method, but the xxx column has no index; or add a sourceUserId IN (...) aggregation, but the column has only been used by 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 decide whether the API lives or dies.

How to check

Every time you add a Repository query method, cross-check it against the entity class's @Index annotation + the 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 a findByXxx , does the xxx column have an index (the composite index includes tenant_id)
• Does the WHERE condition hit the index (verify with EXPLAIN , don't guess)
• Are range query fields placed last in the composite index (e.g. idx (tenant_id, status, created_at)）
• Does the soft-delete field (deleted_at) also need to be included in the index when required

---

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

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

Wrong 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 Is the key stable (does not contain unstable parameters such as Pageable or Date.now)
• In multi-tenant scenarios, does the key include tenantId
• Do write operations have a matching @CacheEvict
• Does the cache TTL match the business consistency requirement (if strong consistency is needed, don't use caching)

---

Performance budget quick reference

Query pattern	Acceptable number of SQL queries	Warning line
Single entity query API	1–3	> 5
List query (N foreign key joins)	1 + number of foreign key types	Unacceptable if correlated with N
BFS/recursion (≤ MAX_TOTAL_NODES)	depth × 1 + number of IN batches	Linearly correlated with node count
Aggregate statistics	1 (SUM) / number of batches	Loading everything into memory for computation

---

Multilingual examples

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

---

Rule provenance

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