- 최근 서비스 모니터링툴을 살펴보다가 조회 쿼리임에도 2초 이상 소요되는 것을 발견했다
분석 중에 만난 using index condition 에 대해서 알아보았다- 분석 결과를 먼저 말해보자면 ICP(Index Condition Pushdown) 가 적용되는 경우는 크게 느리지 않고 적용 안되는 경우만 느렸다
첫번째 개선 :: 조인조건 개선
조인할 때 범위를 좁혀서 조인 데이터를 줄일 수 있는 비즈니스적 조건을 추가했다
처음엔 covering index 등 기술적으로 접근했는데 비즈니스 조건 사용해서 개선한게 훨씬 빨랐다
테스트겸 여러 케이스들에 적용해보았다
기존에는 곧이 곧대로 조건이 반영되어 있었다면 요걸 데이터가 중복이 적어 검색에 좀더 효율적인 다른 조건을 사용해서 같은 상황을 만들었다
결과
-
기존 쿼리
-
개선 쿼리
-
개선 쿼리 + dto로 조회
- 기존 : 3s 308ms → join 조건 개선 : 502ms → dto 로 조회 : 326ms
- 이후 5번씩 조회 시
- 기존 : 523ms 258ms 292ms 347ms 278ms ⇒ 평균 339.6ms
- join 조건 개선 : 353ms 188ms 240ms 198ms 201ms ⇒ 평균 236ms
- dto 로 조회 : 158ms 156ms 176ms 224ms 175ms ⇒ 평균 177.8ms
위의 결과처럼 속도가 개선되었다
그런데 기존 슬로우 쿼리가 늘 느린건 아니었는데, 느리지 않은 경우에는 실행계획 extra에 using index condition이 표시되었다 using index 도 아니고 뭐지?
그리고 내가 예상했던 index가 아닌 다른 컬럼을 키로 잡았다
EXPLAIN ANALYZE
select *
from a
inner join b
on b.status = {status} and a.b_id = b.id
where b.type = {type} and b.reserve_at between {reserve_at} and {reserve_at}
요런 식의 쿼리였는데 b 테이블에 대한 key로 reserve_at 이 잡혀있었다
(왜 where 절의 조건을 join 할 때 넣지 않았을까? 의문이었던 기존 쿼리, 지금 보니 ICP를 염두했던 쿼리인건가..?)
보통 join -> where 절 순서인데 마치 where 절로 필터링 후 join이 된것처럼 보였다
-> Nested loop inner join (cost=551 rows=3.7) (actual time=6.02..9.52 rows=1 loops=1)
-> Nested loop inner join (cost=547 rows=3.58) (actual time=5.98..9.48 rows=1 loops=1)
-> Filter: (b.type = :type) (cost=164 rows=71.6) (actual time=0.0748..2.66 rows=236 loops=1)
-> Index range scan on b using idx_reserve_at over ('2024-11-29 00:00:00' <= reserve_at <= '2024-11-29 23:59:59'), with index condition: (b.reserve_at between '2024-11-29 00:00:00' and '2024-11-29 23:59:59') (cost=164 rows=364) (actual time=0.05..2.54 rows=364 loops=1)
-> Filter: a 테이블에 대한거
...
using index condition이 뭐길래?
일단 알고있는 사항 먼저,
- Mysql은 Mysql engine과 storage engine으로 구성되어 있다
- storage engine에서는 memory나 disk에서 데이터를 조회하고,
- 이 때 범위를 제한하는 조건은 storage engine에서 index로 데이터를 찾으면서 사용한다
- Index 범위 조건 : BETWEEN, >, < 등 범위를 지정하는 조건
- Index 조건 : =, IN, 또는 NULL 체크 등 index가 적용되는 다른 조건
- 찾은 데이터를 Mysql engine에게 넘겨 조건문에 포함되는 연산이나 가공을 MYSQL engine에서 실행한다
- Mysql engine은 범위를 제한하지 못하는 체크 조건을 처리하게 된다
- 이 체크 조건에 부합하지 않은 레코드는 버리게 되는데, storage engine과 Mysql engine은 tcp 통신을 하기 때문에 체크 조건에 맞지않는 레코드를 전달하는 것은 낭비다
=> storage engine에서 범위를 제한하는 조건에 한해 index를 사용해 걸러진 데이터 중에서 MySQL engine이 한번 더 걸러야되는 조건이 있다면 using where이 발생한다
Index Condition Pushdown (ICP)
- 도입 : MySQL 5.6 (InnoDB에 5.1버전부터 들어갔는데, extra 칼럼에 Using Index Condition이라고 표시된 건 5.6버전)
- 작동 방식
-
MySQL은 인덱스 범위 조건이 아니더라도 쿼리에서 사용되는 조건이 index와 함께 사용될 때, index를 사용할 수 있는 조건을 storage engine에 전달하는 것
-> storage engine이 index의 조건을 미리 필터링해 검색 효율성을 높임= 즉, index에서 조건에 맞는 데이터만 읽고, 이후 테이블에서 나머지 데이터를 처리
-
- 성능 향상:
- 이를 통해 인덱스 조건을 효율적으로 처리하기 때문에 table scan을 줄여 데이터 검색 성능을 향상
이번에 알게 된 사항은
- MySQL은 쿼리 실행 시 index를 활용하여 성능을 높이는데, 이때 index 조건을 storage engine에 전달하는 과정을 Index Condition Pushdown이라고 한다
- index 범위 조건이 아니더라도 쿼리에서 사용되는 조건이 index와 함께 사용될 때, index를 MYSQL engine에서 사용한다면 그때 using index condition이 발생한다
(MYSQL engine에서 사용한다는 것은 MySQL이 쿼리를 최적화하는 과정에서 index 조건을 생성하고 이를 storage engine에 전달하여 처리하게 한다는 의미)
이를 기반으로 storage engine은 데이터를 효율적으로 검색한다
간단하게 index 범위가 아닌 조건도 추가해서 storage engine에서 처리해준다고 이해했다
storage engine이 체크 조건까지 처리하도록 최적화가 된것
WHERE 절의 index를 이용한 조건(꼭 index를 사용한 조건이어야 함)에 체크 조건이 있을 경우 체크 조건 처리를 storage engine이 하도록 전달하는 것이다
의도한 방향은 아니었지만 요 ICP가 개선 전 쿼리에서 발생하고 있었는데, 그렇다고 모든 케이스에서 동일하게 적용되는것은 아니었다
같은 쿼리임에도 특정 조건과 상황에서만 동작해서 ICP가 가끔만 적용되는 이유가 궁금했다
여러 케이스를 테스트해보았을 때, (옵티마이저의 마음은 정확히 알 수 없지만) 예약일 컬럼에 대해서 데이터가 많지 않을 때 (= 예약일 카디널리티가 높을 때) ICP가 적용되는 경향이 있었다
ICP가 적용되는 케이스
테스트 결과, ICP가 적용되는 경우와 적용되지 않는 경우에서 각각 예정일만 변경했을 때, EXTRA 결과도 바뀌었다
이를 통해 예정일 관련해서 ICP 적용을 유추하였다
이사예정일이 한참 뒤다보니 실제로 해당일에 데이터가 훨씬 적은편이었고, 카디널리티가 높아 옵티마이저가 더 높게 친듯
정리
기본적으로 between 등의 범위 조건은 storage engine이 범위 필터링을 하지만, optimizer가 ICP 사용을 판단하면
where 절의 범위 조건을 join 시점에 필터링하여 join 이후 불필요한 데이터가 다시 필터링되는 것을 줄여준다
- ICP는 join 이전에 index 검색이 이루어지기 때문에 범위 조건을 미리 적용 가능
- 그래서 between 조건이 먼저 적용되어 데이터 범위가 좁아졌고 그 후 join 진행된것으로 파악
이렇게 되면 특히 큰 테이블 간의 JOIN 시 성능 향상을 가져올 수 있다
Real MySQL에서 분명 읽었는데,, 역시 겪어봐야 더 잘 와닿는다
Reference