왜 인덱스를 쓰지 않는건데?!

예상과 달리 인덱스가 사용되지 않거나, 오히려 성능을 저하시키는 경우가 있습니다. 이번 포스팅에서는 성능 개선 중 상황을 통해 이러한 현상을 분석해보겠습니다.
예상과 달리 인덱스가 사용되지 않아 성능이 저하되는 경우가 있었습니다. 왜 인덱스가 사용되지 않는지 분석해보겠습니다!

상황 소개

steadies 테이블에는 약 300만 건의 데이터가 있으며, 다음과 같은 구조를 가지고 있습니다:

create table steadies
(
    id                     bigint auto_increment,       primary key,
    promoted_at            timestamp     not null,
    view_count             int           null,
    like_count             int           not null,
    ...
);

특정 조건(promoted_at > '2024-12-31 10:12:30')에 해당하는 데이터는 약 25만 건입니다. 아래 쿼리는 약 20초가 소요되었습니다:

SELECT *
FROM steadies s
WHERE promoted_at > '2024-12-31 10:12:30'
ORDER BY like_count DESC, view_count DESC
LIMIT 3;

쿼리 실행 계획을 분석해 보면, MySQL 옵티마이저는 테이블 풀 스캔을 선택했습니다:

//explain analize
-> Limit: 3 row(s)  (cost=319246 rows=3) (actual time=10775..10775 rows=3 loops=1)
    -> Sort: s.like_count DESC, s.view_count DESC, limit input to 3 row(s) per chunk  (cost=319246 rows=2.9e+6) (actual time=10775..10775 rows=3 loops=1)
        -> Filter: (s.promoted_at > TIMESTAMP'2024-12-31 10:12:30')  (cost=319246 rows=2.9e+6) (actual time=2.77..10668 rows=249342 loops=1)
            -> Table scan on s  (cost=319246 rows=2.9e+6) (actual time=1.56..10272 rows=3e+6 loops=1)

성능 개선을 위해 promoted_at 컬럼에 인덱스를 추가하고 쿼리를 실행했습니다.

create index steadies_promoted_at_index
    on steadies (promoted_at);

-> Limit: 3 row(s)  (cost=319761 rows=3) (actual time=11352..11352 rows=3 loops=1)
    -> Sort: s.like_count DESC, s.view_count DESC, limit input to 3 row(s) per chunk  (cost=319761 rows=2.9e+6) (actual time=11352..11352 rows=3 loops=1)
        -> Filter: (s.promoted_at > TIMESTAMP'2024-12-31 10:12:30')  (cost=319761 rows=2.9e+6) (actual time=0.113..11236 rows=249342 loops=1)
            -> Table scan on s  (cost=319761 rows=2.9e+6) (actual time=0.0867..10817 rows=3e+6 loops=1)

하지만 여전히 인덱스를 활용하지 않는 모습인데요

왜 인덱스를 활용하지 않는것일까요?

RealMySQL8.0 에서 Random I/O의 비용을 Sequence I/O의 5배정도로 계산한다고 나와있어 300만건에서 25만건은 충분히 인덱스를 활용할 가치가 있다고 생각했습니다. 물론 이후에 정렬조건이 있지만 promoted_at 인덱스를 활용하면 더 빠를것이라고 예상했습니다.. 하지만 예상은 틀렸고.. 인덱스를 사용하는것이 더 느려서 그런가? 라는 생각에 쿼리힌트를 통해 강제 인덱스를 사용하게 변경 후 쿼리를 실행해봤습니다

쿼리힌트를 사용한 인덱스 강제

비용이 높음에도 불구하고, 특정 상황에서 인덱스를 강제로 사용하고자 할 때 쿼리힌트를 사용할 수 있습니다. 다음은 인덱스 강제 사용을 위한 쿼리힌트 예시입니다:

SELECT *
FROM steadies s  FORCE INDEX (steadies_promoted_at_index)
WHERE promoted_at > '2024-12-31 10:12:30'
ORDER BY like_count DESC, view_count DESC
LIMIT 3;

쿼리힌트를 사용한 후의 쿼리 실행 계획을 분석해 보면, 옵티마이저가 인덱스를 사용하여 쿼리를 처리하며, 이 경우의 비용을 살펴볼 수 있습니다:

이 경우, 인덱스를 사용함으로써 발생하는 랜덤 I/O의 비용이 전체 쿼리 비용을 증가시키는 주요 요인이 됩니다. 그럼에도 불구하고, 쿼리의 특정 요구 사항이나 데이터의 특성에 따라 인덱스 강제 사용이 전체 성능에 긍정적인 영향을 미칠 수 있습니다.

옵티마이저의 선택

옵티마이저는 비용 기반으로 실행 계획을 선택합니다. 인덱스를 사용하는 경우와 사용하지 않는 경우를 비교했을 때, 옵티마이저는 인덱스를 사용하지 않는 테이블 풀 스캔을 더 낮은 비용으로 판단했습니다:

sqlCopy code
// 인덱스를 사용하지 않는 경우
"query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "315090.09"
    }, ...
        "cost_info": {
          "read_cost": "262133.49",
          "eval_cost": "52956.60",
          "prefix_cost": "315090.09",
          "data_read_per_join": "13G"
        },

// 인덱스를 사용하는 경우
"query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "421520.14"
    }, ...
        "cost_info": {
          "read_cost": "368563.54",
          "eval_cost": "52956.60",
          "prefix_cost": "421520.14",
          "data_read_per_join": "13G"
        },

인덱스를 사용하면 read_cost가 증가하여 전체 비용이 상승합니다. 이는 인덱스를 통한 랜덤 액세스가 연속된 테이블 스캔보다 비용이 더 많이 들 수 있음을 의미합니다.

순차 I/O vs 랜덤 I/O

• 순차 I/O(Sequential I/O): 데이터를 디스크에서 연속적으로 읽거나 쓰는 작업입니다. 순차 I/O는 일반적으로 빠르고 효율적이며, 테이블 풀 스캔과 같은 연산에서 주로 발생합니다.
• 랜덤 I/O(Random I/O): 디스크 상의 불연속적인 위치에서 데이터를 읽거나 쓰는 작업입니다. 인덱스를 통한 데이터 접근은 대체로 랜덤 I/O를 발생시키며, 이는 순차 I/O에 비해 상대적으로 비용이 높습니다.

옵티마이저는 이러한 I/O 비용을 고려하여 쿼리의 실행 계획을 결정합니다. 데이터의 크기가 클 경우, 랜덤 I/O로 인한 비용이 순차 I/O를 수행하는 것보다 더 클 수 있으며, 이는 인덱스를 사용하지 않는 결정으로 이어질 수 있습니다.

해결 방안

성능을 개선하기 위해 ORDER BY에 사용되는 컬럼에 대한 복합 인덱스를 추가했습니다:

create index steadies_like_count_view_count_index
    on steadies (like_count desc, view_count desc);

이 인덱스를 추가한 후 쿼리 실행 계획을 분석해 보면, 옵티마이저가 인덱스를 사용하여 효율적으로 쿼리를 처리하는 것을 확인할 수 있습니다:

-> Limit: 3 row(s)  (cost=1.46 rows=2.92) (actual time=0.0919..0.114 rows=3 loops=1)
    -> Filter: (s.promoted_at > TIMESTAMP'2024-12-31 10:12:30')  (cost=1.46 rows=2.92) (actual time=0.0912..0.113 rows=3 loops=1)
        -> Index scan on s using steadies_like_count_view_count_index  (cost=1.46 rows=16) (actual time=0.0597..0.109 rows=13 loops=1)

결론

MySQL 옵티마이저는 다양한 요소를 고려하여 최적의 실행 계획을 선택합니다. 때로는 인덱스가 쿼리의 성능을 개선하지 못하고, 오히려 비용을 증가시키는 경우도 있습니다. 이러한 상황에서는 쿼리와 인덱스 전략을 재검토하고, 필요에 따라 다른 방식으로 최적화를 시도해야 합니다. 본 사례에서는 ORDER BY 절에 사용되는 컬럼에 대한 복합 인덱스를 추가함으로써 성능을 크게 향상시킬 수 있었습니다.