[MySQL] SQL문 최적화를 위해 알아야 할 기본적인 2가지

작성일: 2023-01-13 (금)

오늘의 주제는 “옵티마이저와 인덱스 힌트” 입니다.

관계형 데이터베이스는 기본적으로 인덱스가 생성된 컬럼이 아닌 일반 컬럼을 조건으로 SELECT 명령문을 수행할 경우 테이블 Full Scan 방식을 통해 데이터를 조회합니다.

그렇기 때문에 DBA는 엔지니어들의 쿼리 검수 과정에서 플랜을 확인하고
과도한 I/O가 발생하지는 않는지 확인하여 튜닝 작업을 진행해야 합니다.

DBA가 없는 경우 엔지니어들의 무분별한 인덱스 추가 작업으로 기존에 잘 동작하던 쿼리의 플랜이 깨져 Slow Query 가 발생하는 경우도 존재합니다.

DBA는 위와 같은 케이스가 존재하기 때문에라도 항상 사전 검수 후, 쿼리 튜닝 및 인덱스 추가 작업을 진행해야 합니다.

앞서 설명한 내용을 조금 더 자세히 설명하기에 앞서 우리는 “옵티마이저” 를 알아야 합니다.

Optimizer

• 사용자가 SQL문을 통해서 데이터를 조회하면
  내부적으로 DBMS에 존재하는 옵티마이저가 실행계획을 생성하게 됩니다.
• 옵티마이저는 크게 2가지로 분류
  • RBO (Rule Based Optimizer)
    • 미리 정해둔 규칙대로 쿼리 수행
    • 인덱스가 존재할 경우 무조건 인덱스를 통해 수행
    • 예측이 가능하기에 설계 난이도가 낮지만, 그로 인해 쿼리 작성 난이도가 상승
  • CBO (Cost Based Optimizer)
    • 통계 정보(레코드 수, 인덱스 컬럼 개수 등)를 기반으로 옵티마이저가 생각하는 가장 효율적인 실행계획을 통해 쿼리 수행
    • 인덱스가 존재하더라도 Table Scan 방식 비용이 더 낮을 경우 Table Scan 수행
    • 예측이 힘들기 때문에 작성자가 생각한 실행계획가 달라 Slow Query 발생 가능성이 높음

옵티마이저 이외에도 많은 내용이 존재하지만,
해당 포스트에서는 맨처음 언급한 기존 플랜이 깨져 Slow Query 가 발생하는 경우에 대해 이야기를 하려고 합니다.

위 쿼리는 정상적인 인덱스 키를 통한 데이터 조회했을 때의 실행계획입니다.

기존 플랜이 깨져 Slow Query가 발생하는 경우는 다양합니다.
무분별한 인덱스 추가로 인해 옵티마이저가 적절한 인덱스가 아닌 엉뚱한 인덱스를 스캔하거나, 테이블 Full Scan 방식의 비용이 더 낮다고 판단하는 경우도 있을 것입니다.

이러한 경우 INDEX HINT 를 통해서 사용자가 원하는 인덱스를 옵티마이저가 스캔할 수 있도록 지정할 수 있습니다.

INDEX HINT

• SELECT 쿼리를 작성할 때 사용자가 원하는 인덱스를 옵티마이저가 채택할 수 있도록 도움을 주는 기능
• 특정 인덱스를 사용하고자 할 때 USE / FORCE / IGNORE 예약어 사용
  • USE INDEX
    • 옵티마이저에게 지정한 인덱스를 사용하라고 [권장]
    • 해당 옵션을 사용하면 옵티마이저는 다른 인덱스를 스캔하는 비용이 더 적다고 판단하거나, 테이블 Full Scan 비용이 더 적다고 판단하면 해당 방식을 통해 스캔합니다.
    • 작성 방법

      SELECT *
        FROM dept USE INDEX (`인덱스명`)
       WHERE deptno > 10
      ;

  • FORCE INDEX
    • 옵티마이저에게 지정한 인덱스를 사용하라고 [명령]
    • 해당 옵션을 사용하면 사용자가 지정한 인덱스보다 낮은 비용의 방식이 있더라도 무조건 지정된 인덱스를 통해 스캔합니다.
    • 작성 방법

      SELECT *
        FROM dept FORCE INDEX (`인덱스명`)
       WHERE deptno > 10
      ;

  • IGNORE INDEX
    • 지정한 인덱스 이외에 다른 인덱스만을 사용하도록 지정
    • 작성 방법

      SELECT *
        FROM dept IGNORE INDEX (`인덱스명`)
       WHERE deptno > 10
      ;

INDEX HINT를 사용하기 위해서는 다양한 부분을 고려해야 합니다.
특히나 FORCE INDEX 힌트의 경우 꼭 필요한 상황이 아닌 이상 권장하지 않는 방법입니다.

기본적으로 옵티마이저가 작성한 실행계획에 의해 조회 방식이 결정되기 때문에
FORCE INDEX 힌트는 예기치 못한 사이드 이펙트를 발생시킬 수 있다는 것을 명심해야 합니다.