SQL 옵티마이저 - 1

 

sql은 선언형 질의언어이다.

 

즉, 무엇을 할지만 사용자가 기술한다면, 어떻게 할지는 데이터베이스 엔진이 결정한다.

 

따라서 우리가 SQL문을 작성하면 DBMS 내부 엔진이 어떻게 해당 결과를 만들어낼 지 프로시저(절차)를 작성한다.

이때 해당 프로시저를 작성하는 것이 SQL 옵티마이저이다. 때문에 SQL 옵티마이저가 어떻게 프로시저를 작성했냐에 따라 쿼리의 성능이 달라질 수 있다.

sql옵티마이저에 대한 다양한 내용 중 실제로 옵티마이저가 어떤 기준으로 실행계획을 선택하는지, 사용자가 특정 실행 계획을 선택할 수 있는지 Mysql 예제를 통해 알아보는 것을 목표로 한다.

1.예제 데이터

처음에는 H2를 사용하려고 했지만, H2의 explain 키워드의 경우 쿼리의 실행 비용을 보여주지 않기 때문에 Mysql을 사용하기로 했다.

 

먼저, 다음과 같은 테이블을 만들었다.

해당 데이터에서 아래와 같은 두개의 인덱스를 추가로 만들어, 조회에 사용해보자.

이때, '윤지우' 라는 학생을 찾아서 학생 정보를 조회하는 다음의 쿼리에서, 옵티마이저는 어떤 실행계획을 가지고 있을까?

다음과 같은 sql문을 실행시켰고

EXPLAIN FORMAT=JSON SELECT * FROM student_info WHERE sname = '윤지우';

응답 결과는 다음과 같았다.

{
  "query_block": {
    "select_id": 1,
    "cost": 0.00345856,
    "nested_loop": [
      {
        "table": {
          "table_name": "student_info",
          "access_type": "ref",
          "possible_keys": ["idx_name"],
          "key": "idx_name",
          "key_length": "152",
          "used_key_parts": ["sname"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00345856,
          "filtered": 100,
          "index_condition": "student_info.sname = '윤지우'"
        }
      }
    ]
  }
}

내용을 보니, idx_name 인덱스를 사용해서 조회를 한다는 것을 알 수 있다. 그렇다면

CREATE INDEX idx_sname_height ON student_info(sname,height);를 통해 새로운 인덱스를 추가하면 어떻게 될까?

{
  "query_block": {
    "select_id": 1,
    "cost": 0.00345856,
    "nested_loop": [
      {
        "table": {
          "table_name": "student_info",
          "access_type": "ref",
          "possible_keys": ["idx_name", "idx_sname_height"],
          "key": "idx_name",
          "key_length": "152",
          "used_key_parts": ["sname"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00345856,
          "filtered": 100,
          "index_condition": "student_info.sname = '윤지우'"
        }
      }
    ]
  }
}

사용 가능한 인덱스가 두 개로 늘었지만, 여전히 idx_name 인덱스를 사용한다.

2. 옵티마이저 힌트

사용자가 특정 인덱스를 사용하라고 옵티마이저에 지시할 순 없을까?

위의 예제에서 내가 만약 idx_sname_height 인덱스를 사용해서 조회하고 싶다면 어떻게 해야할까?

아래와 같이 SQL 옵티마이저가 쿼리를 최적화하는 방법에 영향을 미치도록 사용자가 제공하는 지시 사항을 옵티마이저 힌트라고 한다.

3. 굳이 옵티마이저 힌트를 사용할 필요가 있는가??

이에 대한 대답을 하기 전에, 책 친절한 SQL 튜닝에 나오는 최적화 단계를 요약하면 다음과 같다.

1. 사용자로부터 전달 받은 쿼리를 수행 할 수 있는 실행계획을 찾는다.
2. 해당 계획들의 예상 비용을 산정한다.(오브젝트 통계, 시스템 통계정보 등 활용)
3. 가장 낮은 비용의 실행계획을 선택한다.

위에 절차를 보면, 대부분의 경우에서 sql 옵티마이저가 선택하는 인덱스가 비용이 가장 적게 드는 방법일 것이라 예측 할 수 있다.

다만 인덱스를 역순으로 볼 지, 최적화의 목표 지점이 어디인지 등은 현재 나의 어플리케이션의 목표와 상황에 따라 다를 것이다.

즉, 적절한 힌트를 사용해서 현재 어플리케이션의 실행 상황에 맞는 실행계획을 선택 할 수 있다는 점에서 옵티마이저 힌트가 도움이 될 것이다.