CS 스터디 (DB) 16 - IN 절에 들어갈 수 있는 최대 항목 수에 대해 알고 계실까요?

IN절 항목 수가 중요한 이유

- IN 절의 항목 수는 단순한 문법 문제가 아니라, 쿼리 성능이나 실행 계획, JDBC 바인딩, 캐시 활용, 메모리 사용량 등과 밀접하게 연결되어 있습니다.

- IN절의 문자열 길이에 대한 명확한 제한은 없지만, 인덱스 사용 여부를 결정하는 옵티마이저 기준과 JDBC 바인딩 수 제한이 있기 때문에 실무에서는 항목 수를 신중히 관리해야 합니다.

 

 

MySQL 옵티마이저의 동작과 eq_range_index_dive_limit

- MySQL 옵티마이저는 IN 조건이 있는 컬럼에 대해 인덱스를 사용할지 여부를 eq_range_index_dive_limit 값으로 판단합니다.

- 기본값은 200이며, 이를 초과하면 인덱스를 탐색하지 않고 통계 정보(index statistics)만으로 비용을 추정합니다.

- 통계 정보가 부정확하면, 옵티마이저는 Index Range Scan 대신 Full Table Scan을 선택할 수 있습니다.

 

 

메모리 제한 (range_optimizer_max_mem_size)

- 옵티마이저가 Index Range Scan을 사용할 수 있을지를 결정할 때, 조건 탐색에 사용되는 메모리량도 고려합니다.

- range_optimizer_max_mem_size는 옵티마이저가 범위 조건 최적화를 수행할 때 사용할 수 있는 최대 메모리 한도를 정의합니다.

- eq_range_index_dive_limit이 조건 개수 기준이라면, 이 설정은 메모리 사용량 기준으로 index dive 수행 여부를 제한합니다.

 

range_optimizer_max_mem_size vs eq_range_index_dive_limit 비교표

항목	eq_range_index_dive_limit	range_optimizer_max_mem_size
제한 기준	조건 개수 기준	사용되는 메모리 크기 기준
기본값	200	8MB (8388608 바이트)
역할	IN 조건이 많을 때 index dive 여부 결정	복잡한 조건에서 최적화 대상 제한
초과 시 동작	통계 기반 비용 추정으로 전환, 인덱스 사용 제한 가능성	메모리 초과 시 정밀 탐색 중단, Full Scan 가능성

 

- 옵티마이저는 조건이 많은 쿼리에서 index dive 수행 여부를 조건 개수와 메모리 사용량 기준으로 함께 평가합니다.

- 두 설정을 적절히 조정하면, 복잡하거나 조건 수가 많은 쿼리에서도 불필요한 Full Scan을 줄일 수 있습니다.

 

 

JPA / Hibernate 환경에서의 고려 사항

- JPA나 Hibernate 같은 ORM 환경에서도 IN 절 항목 수는 성능에 직접적인 영향을 미칠 수 있습니다.

- Hibernate는 JDBC 드라이버를 통해 SQL을 생성하는데, IN 파라미터 수가 많을 경우 쿼리 길이가 길어지고 JDBC의 바인딩 한도에 도달할 수 있습니다. 이로 인해 캐시 분산이나 실행 실패가 발생할 수 있습니다.
- Hibernate 5.2 이상에서는 hibernate.query.in_clause_parameter_padding=true 옵션을 통해 문제를 완화할 수 있습니다.

hibernate.query.in_clause_parameter_padding=true

- 이 옵션은 IN 항목 수를 2의 제곱 단위(예: 128, 256 등)로 패딩하여, 다양한 파라미터 수를 가진 쿼리라도 동일한 쿼리 구조로 처리되도록 만들어줍니다. 예를 들어, 129개의 파라미터가 있으면 256개로 패딩됩니다. 이를 통해 Plan Cache 재사용률을 높일 수 있습니다.

- 단, IN 절이 지나치게 클 경우 여전히 옵티마이저가 Plan Cache를 폐기하고 실행 계획을 새로 계산해야 하므로, 파라미터 수는 일정 수준 이하로 유지하거나, 분할 처리 혹은 임시 테이블 활용이 필요합니다.

 

 

IN 절 파라미터 수에 따른 쿼리 구조 분산 문제

select ... from example where id in (1001, 1001) -- ids = [1001]
select ... from example where id in (1001, 1002) -- ids = [1001, 1002]
select ... from example where id in (1001, 1002, 1003, 1003) -- ids = [1001, 1002, 1003]
select ... from example where id in (1001, 1002, 1003, 1004, 1005, 1005, 1005, 1005) -- ids = [1001, 1002, 1003, 1004, 1005]

- 항목 수가 달라질 때마다 SQL 구조도 달라지고, 이로 인해 DB는 서로 다른 실행 계획을 생성합니다.

 

 

Hibernate의 IN 절 패딩 전략

// 첫 번째 요청 
List<Long> ids = List.of(1L, 2L, 3L); // 실제 3개
List<Long> ids = List.of(1L, 2L, 3L, 4L, 5L); // 실제 5개

- Hibernate는 아래처럼 쿼리 구조를 고정합니다

SELECT * FROM user WHERE id IN (?, ?, ?, ?, ?, ?, ?, ?)

- 이처럼 파라미터 개수를 2의 제곱수 단위(예: 8, 16, 32...)로 패딩합니다.

- 쿼리 텍스트는 동일, 바인딩 값만 달라지므로 JDBC와 DB 모두 캐시 재사용 가능

 

 

참고: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_eq_range_index_dive_limit

MySQL :: MySQL 8.4 Reference Manual :: 7.1.8 Server System Variables

 

 

 

결론

IN 절은 단순 조건처럼 보이지만, 항목 수에 따라 옵티마이저의 인덱스 사용 여부와 실행 계획이 달라질 수 있어 성능에 큰 영향을 줍니다. MySQL에서는 eq_range_index_dive_limit과 range_optimizer_max_mem_size를 기준으로 index dive 여부를 판단하며, Hibernate 환경에서는 IN 절 항목 수가 자주 바뀔 경우 실행 계획이 쪼개지고 캐시가 분산되는 문제가 생기는데, 이를 해결하기 위해 Hibernate 5.2부터 제공하는 in_clause_parameter_padding 옵션을 사용하면 쿼리 구조를 고정시켜 JDBC, DB 모두에서 Plan Cache를 효율적으로 재사용할 수 있게 됩니다.

실무에서는 IN 조건이 많은 쿼리는 항목 수를 관리하고, 캐시 재사용과 안정적인 실행 계획을 유도하는 방향으로 설계하는 것이 중요하다고 생각합니다.