잘 사용되진 않는 범위 조건 중에 IN 이 있습니다.
일반적으로 BETWEEN, LIKE, <> 에 비해서는 하나씩 모든 Key를 입력해야 되기 때문에 애플리케이션에서 작업양이 추가되어 선호되진 않는데요.
이 IN 절을 통해 여러 성능 개선들이 가능해서 생각보다 활용하기에 따라 많은 성능 개선을 이룰 수 있습니다.
이번 시간에는 IN 을 통한 2가지 개선 방법을 소개 드리겠습니다.
0. 소개
MySQL의 IN 절은 UNION으로 처리됩니다.
즉, eq 조건을 여러번 나눠서 실행하는 것과 같은 효과를 가지는데요.
이미 다들 아시겠지만 MySQL은 범위 조건에서는 인덱스 효과를 제대로 보지 못합니다.
정확히는 인덱스 앞의 컬럼을 범위 조건으로 사용하는 경우 뒤의 인덱스 컬럼은 효과를 보지 못하는 것인데요.
제대로 효과를 보기 위해서는 앞의 컬럼들은 모두 동등조건(eq) 를 사용해야만 합니다.
이러다보니 범위 조건이지만 eq를 여러번 사용하는 것과 같은 IN은 기존의 MySQL 이 가지고 있던 범위 조건의 비효율을 회피할 수 있습니다.
자 그럼 하나씩 알아보겠습니다.
1. 범위 검색 개선
첫번째 IN 절을 통한 개선 사례는 서두에서 말씀드린것처럼 범위 검색의 개선입니다.
1-1. 테스트
아래와 같이 2개의 인덱스가 잡혀있다고 가정해보겠습니다.
- UNI_TX_ITEM_SUM_1:
tx_date, settle_code, give_cycle_code, customer_id - IDX_TX_ITEM_SUM_1:
settle_code, customer_id, tx_date
인덱스 컬럼들과 순서를 주의깊게 봐주시면 됩니다.
기존의 아래와 같은 쿼리를 실행해보면 둘 중 어느 인덱스를 사용할까요?
잠깐 고민해보시고 결과를 보겠습니다.
아마 쉽게 생각하면 인덱스 컬럼이 모두 포함된 UNI_TX_ITEM_SUM_1를 사용하지 않을까 생각이 들텐데요.
실제로는 인덱스 효율이 떨어지는 IDX_TX_ITEM_SUM_1 를 선택하게 됩니다.
그래서 수행시간 33분의 비효율의 쿼리가 실행되게 됩니다.
이유는 범위 검색인 BETWEEN을 최대한 효율적으로 사용하기 위해 tx_date가 가장 뒷편인 인덱스를 사용하도록 했기 때문입니다.
물론
force index를 통해 강제로UNI_TX_ITEM_SUM_1를 사용해도 됩니다만 ,JPA/Hibernate에서는 Query Hint 조정이 불가능합니다.
실제로 JPA/Hibernate에서의 힌트는 Hibernate의 작동 방식에 대한 Hint이지 Query Hint가 아닙니다
이 부분을 아래와 같이 IN으로 다 변환 해보겠습니다.
그럼 아래와 같이 가장 효율이 좋은 인덱스 선택과 실행 결과를 가져오게 됩니다.
결과적으로 이렇게 BETWEEN을 IN 으로 변환후 33분 -> 0.1초 로 수백배의 성능 개선이 되었습니다.
1-2. Tip
만약 datetime으로만 선언된 컬럼만 사용한다면 중복된 컬럼이라고 느껴지지만 date도 함께 사용하는것도 고려해보면 좋습니다.
이럴 경우 3/1 ~ 3/31 까지 라는 조회 조건이 필요할 경우 3/1 00:00:00 ~ 3/31 23:59:59.99999 로 BETWEEN 이나 <> 사용을 피할 수가 없는데요.
만약 date 컬럼이 있다면 (애플리케이션이 조금 복잡하더라도) IN절을 통해 인덱스 설계나 사용에 있어 활용도가 높기 때문에 범위 검색이 많고, 일자별 검색이 있는 테이블이라면 date 컬럼도 함께 사용해보시길 추천드립니다.
2. Loose Index Scan
두번째 개선사례는 Loose Index Scan 입니다.
MySQL은 B+Tree 인덱스를 기본으로 사용하는데요.
이 인덱스 구조는 첫번째 인덱스 컬럼이 사용되지 않으면 인덱스 사용이 불가능 하다는 단점이 있는데요.
만약 첫번째 인덱스 컬럼이 불필요한 조회조건에서는 어떻게 인덱스를 사용하도록 강제화할 수 있을까요?
이때 IN 절을 한번 고려해보면 좋습니다.
2-1. 테스트
예를 들어 다음과 같은 인덱스 컬럼이 있다고 가정해보겠습니다.
- IDX_GIVE_1:
settle_code, cycle_date
그리고 실제 사용되는 쿼리는 아래와 같은데요.
인덱스로 잡힌 settle_code, cycle_date 중 뒤에 있는 cycle_date만 필요한 경우입니다.
이럴 경우 실제 실행 계획을 보시면 당연하게 테이블 풀스캔이 발생합니다.
이럴 경우 어떻게든 인덱스를 태우려면 앞의 컬럼을 IN으로 모두 포함시키는 방식으로 인덱스 선택이 가능합니다.
실제 필터를 해야하는 조회 조건에서 앞의 컬럼은 전체가 포함되어 인덱스 효과는 상대적으로 떨어집니다.
다만, 테이블 풀 스캔에 비해서는 월등하게 성능이 개선될 수 있습니다.
그래서 강제로 인덱스 스캔이 가능하도록 아래와 같이 settle_code 상태값들을 모두 IN에 넣어서 사용합니다.
그럼 아래와 같이 인덱스 레인지 스캔이 되고 원했던 인덱스를 사용하는 것을 볼 수 있습니다.
2-2. Tip
위와 같이 특정 상태값과 같은 기준정보들을 Enum으로 관리하시면 Loose Index Scan 를 구현하기가 용이합니다.
별도의 테이블로 상태를 관리한다면 위와 같은 경우 select * from settle_code 와 같이 한번은 메타 테이블의 전체값을 조회해서 IN절에 다시 한번 넣는 식으로 구현을 해야하는 것에 반해 Enum일 경우 values() 를 통해 쉽게 쿼리 구현이 가능하기 때문입니다.
물론 모든 상태값들을 다 Enum으로 관리해야하는 것은 아닙니다.
상황에 따라 적절하게 선택하시길 추천드립니다.
3. 적정 개수
그렇다면 IN 에는 몇개의 값까지 적절할까요?
먼저 아래의 글을 한번 참고해보시면 좋습니다.
간략하게 말씀드리면 eq_range_index_dive_limit 옵션으로 인해서 실행 계획 선택 방식이 IN절 개수에 따라 달라지는데요.
기본적으로 아래와 같은 기준으로 기준값을 초과할 경우, index dive 방식이 아니라 인덱스 통계 정보를 바탕으로 실행계획을 세울 수 있도록 하는 index statistics 를 사용합니다.
- MySQL 5.6:
10 - MySQL 5.7.4:
200
통계 정보가 정확할때도 있지만, 아직까지 100% 신뢰할 수는 없기 때문에 200개를 초과할 경우 생각지 못한 성능 저하가 발생할 수 있습니다.
물론, 정확하게 통계가 반영될 수도있기 때문에
IN의 값으로 100~200개로 하면 수백~수천번의 쿼리 실행이 필요할 경우
위와 같은 경우에는 IN 의 개수를 서서히 늘려가면서 성능 테스트를 해보시길 추천드립니다.
3-1. JPA & Batch
위 eq_range_index_dive_limit 옵션에 맞춰 각각의 애플리케이션 프레임워크들도 설정들을 조정하면 좋습니다.
- JPA/Hibernate를 쓸 경우
hibernate.default_batch_fetch_size - Spring Batch 를 쓸 경우
chunkSize
이들의 값을 eq_range_index_dive_limit 와 맞춰서 지정하시면 좋고, 그게 아니더라도 1000개 이상은 성능 테스트와 함께 사용하시길 추천드립니다.
통계 정보가 어떻게 될지 모르기 때문입니다.
4. 5.7 업데이트시 주의사항
MySQL 5.6에서 5.7로 업데이트시 아래와 같이 IN 절 개수는 똑같은데 버전업만으로 테이블 풀스캔이 발생할 수 있습니다.
이는 5.7에 새롭게 추가된 range_optimizer_max_mem_size 때문인데요.
쿼리 자체를 메모리에 올리는 것 또한 메모리 제한이 되도록 제한한 옵션입니다.
결론적으로는 해당 옵션이 만약 0이 아닌 값이 셋팅되어 있다면, 그 값을 초과했을 경우 테이블 풀스캔 혹은 이외의 인덱스를 태우게 됩니다.
그래서 5.6에서 5.7로 올릴 경우 IN절을 과하게 사용하는 쿼리가 있다면 해당 쿼리를 꼭 사전 성능 테스트를 수행해보고 테이블 풀스캔이 발생한다면 range_optimizer_max_mem_size 을
- 0으로 변경 (설정 OFF)
IN에 맞게 사이즈 조절
중 하나를 선택해서 설정 하시길 추천드립니다.
