• 최근 서비스 모니터링툴을 살펴보다가 조회 쿼리임에도 2초 이상 소요되는 것을 발견했다
    요 병목은 분석이 필요해보였고, 그래서 시작한 슬로우 쿼리 개선 시도 중 만난 using index condition 에 대해서도 알아보았다


첫번째 개선 :: 조인조건 개선

요건 좀 비즈니스적인 요소여서 자세히 말하긴 어렵지만, 조인할 때 범위를 좁혀서 조인 데이터를 줄일 수 있는 조건을 추가했다
기존에는 곧이 곧대로 조건이 반영되어 있었다면 요걸 데이터가 중복이 적어 검색에 좀더 효율적인 다른 조건을 사용해서 같은 상황을 만들었다

결과는 요렇게 개선되었다

  • 기존 쿼리

    스크린샷 2024-10-10 오후 3 29 48

  • 개선 쿼리

    스크린샷 2024-10-10 오후 3 28 54

  • 개선 쿼리 + dto로 조회

    스크린샷 2024-10-10 오후 3 32 23

  • 기존 : 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 = :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이 뭐길래?



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은 Mysql engine과 storage engine으로 구성되어 있다
storage engine에서는 메모리나 디스크에서 데이터를 조회하고, 이 때 범위를 제한하는 조건은 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이 발생한다


이번에 알게 된 사항은
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가 적용되는 경향이 있었다

// 작성중



Reference


업데이트:

참고