UPDATE reservations 
JOIN screenings using (screening_id)
SET status = 'CANCELLED'
WHERE start_time < NOW() and status = 'PENDING';

지난 시간에 이 쿼리의 실행계획이 all이란것을 확인했다

CREATE INDEX idx_reservations_status ON reservations (status);

인덱스를 추가해서 성능을 높여보자

풀테이블스캔이 인덱스보다 빠르다

{
  "query_block": {
    "select_id": 1,
    "cost": 21.05509636,
    "nested_loop": [
      {
        "table": {
          "table_name": "reservations",
          "access_type": "ALL",
          "possible_keys": [
            "FK_screenings_TO_reservations_1",
            "idx_reservations_status_screening",
            "idx_reservations_status"
          ],
          "loops": 1,
          "rows": 39083,
          "cost": 6.572859,
          "filtered": 41.3837204,
          "attached_condition": "reservations.`status` = 'PENDING'"
        }
      },
      {
        "table": {
          "table_name": "screenings",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["screening_id"],
          "ref": ["movie_db.reservations.screening_id"],
          "loops": 16174,
          "rows": 1,
          "cost": 14.48223736,
          "filtered": 100,
          "attached_condition": "screenings.start_time < <cache>(current_timestamp())"
        }
      }
    ]
  }
}

db는 여전히 풀테이블 스캔을 시전하고 있다.

그 이유는 "filtered": 41.3837204에 있다

쉽게 말해서 전체 데이터에 41%나 되는 데이터가 PEDNING인데 풀 테이블 스캔이 더 효율적이라고 옵티마이저가 판단한 것이다.

FORCE INDEX를 통해서 억지로 인덱스 스캔하게 만들면어떻게 될까

{
  "query_block": {
    "select_id": 1,
    "cost": 31.88580784,
    "nested_loop": [
      {
        "table": {
          "table_name": "reservations",
          "access_type": "ref",
          "possible_keys": ["idx_reservations_status"],
          "key": "idx_reservations_status",
          "key_length": "1022",
          "used_key_parts": ["status"],
          "ref": ["const"],
          "loops": 1,
          "rows": 16174,
          "cost": 17.40357048,
          "filtered": 100,
          "attached_condition": "reservations.`status` = 'PENDING'"
        }
      },
      {
        "table": {
          "table_name": "screenings",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["screening_id"],
          "ref": ["movie_db.reservations.screening_id"],
          "loops": 16174,
          "rows": 1,
          "cost": 14.48223736,
          "filtered": 100,
          "attached_condition": "screenings.start_time < <cache>(current_timestamp())"
        }
      }
    ]
  }
}

비교 항목	(Index Scan)	(Full Scan)
Access Type	ref (인덱스 참조)	ALL (전체 스캔)
읽어들인 행 (Rows)	16,174건	39,083건
총 비용 (Total Cost)	31.88	21.05

왜 읽은 행이 줄었는데 시간은 더 걸리는 걸까

순차IO와 랜덤IO의 의한 차이로 보인다.

이 부분은 나중에 블로그글로 다루어보겠다

그렇다면 index를 안쓰는게 좋은건가?

{
  "query_block": {
    "select_id": 1,
    "cost": 13.96739672,
    "nested_loop": [
      {
        "table": {
          "table_name": "reservations",
          "access_type": "ref",
          "possible_keys": [
            "FK_screenings_TO_reservations_1",
            "idx_reservations_status"
          ],
          "key": "idx_reservations_status",
          "key_length": "1022",
          "used_key_parts": ["status"],
          "ref": ["const"],
          "loops": 1,
          "rows": 7000,
          "cost": 7.68911352,
          "filtered": 100,
          "attached_condition": "reservations.`status` = 'PENDING'"
        }
      },
      {
        "table": {
          "table_name": "screenings",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["screening_id"],
          "ref": ["movie_db.reservations.screening_id"],
          "loops": 7000,
          "rows": 1,
          "cost": 6.2782832,
          "filtered": 100,
          "attached_condition": "screenings.start_time < <cache>(current_timestamp())"
        }
      }
    ]
  }
}

그렇지 않다

시간이 지날수록 PENDING 데이터의 양은 줄어들고 PENDING이지 않은 데이터는 증가한다.

즉 랜덤IO가 속도를 확보할만큼의 비율이 결국 다시찾아온다는 뜻이다.

운영기간이 충분히 길어지면 이런 문제가 생기지 않으리라 생각된다.

테스트 데이터 확보를 위해서 PENDING 데이터를 비정상적으로 많이 투입하여 정상적이지 않은 비율로 PENDING 데이터의 분포가 설정되었기 때문에 발생한 일이다.

인덱스 설계에 있어서 카디널리티와 선택도가 중요하다는 것을 시사한다.