🪴 hayoung

RealMySQL 옵티마이저와 힌트

21 min read

개요

쿼리 실행 절차

  1. SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
    • SQL 파스 트리가 만들어진다.
  2. SQL의 파싱 정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
    • 불필요한 조건 제거 및 복잡한 연산 단순화
    • 여러 테이블의 조인이 있는 경우 순서 결정
    • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
    • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
  3. 결정된 테이블 순서나 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

옵티마이저의 종류

비용 기반 최적화(Cost-based optimizer, CBO)

  • 쿼리를 처리하기 위한 여러가지 방법을 만들고, 각작업의 비용과 예측된 통계 정보를 이용해 실행 계획 별 비용을 산출하고, 최소로 소요되는 처리 방식을 선택한다. 규칙 기반 최적화(Rule-based optimizer, RBO)
  • 테이블의 레코드 수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식

기본 데이터 처리

풀 테이블 스캔과 풀 인덱스 스캔

풀 테이블 스캔을 하는 경우

  • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
  • WHERE절이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 사용할 수 있는 쿼리지만, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

Read ahead

  • 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것
  • InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 Read ahead 작업이 이루어진다.
  • 데이터 웨어하우스용으로 MySQL을 사용한다면 innodb_read_ahead_threshold 값을 낮은 값으로 설정해서 더 빨리 Read ahead가 시작되게 유도하는 것도 좋은 방법이다.

병렬 처리

  • innodb_parallel_read_threads 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있다.
  • 병렬 처리용 스레드 개수가 CPU 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있다.

ORDER BY

다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하기란 불가능하므로 Filesort 정렬을 사용한다.

  • 정렬 기준이 많아서 요건 별로 모두 인덱스를 생성하는 것이 불가능한 경우
  • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우

Sort buffer

  • 정렬을 수행하기 위한 별도의 메모리 공간
  • 정렬해야 할 레코드의 건수가 Sort buffer 공간 보다 크다면 레코드를 여러 조각으로 나누고 임시 저장을 위해 디스크를 사용한다.
    • 정렬 결과를 디스크에 임시 기록하고 병합한다. (Multi-merge)
  • Sort buffer 크기를 크게 설정한다고 성능상의 큰 차이는 없고 오히려 메모리가 부족할 수 있다.
    • 디스크의 읽기와 쓰기 사용량은 줄일 수 있다.

정렬 알고리즘

  • single pass
    • sort buffer에 정렬 기준 컬럼을 포함해 SELECT 대상 모든 컬럼을 담아서 정렬을 수행하는 방식
  • two pass
    • 정렬 대상 컬럼과 PK 값만 sort buffer에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 PK로 테이블을 읽는 방식
  • two pass 방식은 테이블을 두 번 읽어야 하기 때문에 일반적으로 single pass 정렬 방식을 주로 사용한다.
  • two pass 방식을 사용하는 경우
    • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 큰 경우
    • BLOB이나 TEXT 컬럼이 SELECT 대상에 포함되는 경우

정렬 처리 방법

인덱스를 이용한 정렬

  • ORDER BY에 사용한 컬럼은 가장 먼저 조회되는 테이블(드라이빙 테이블)의 컬럼이어야 한다.
  • ORDER BY에 적힌 컬럼 순서 그대로 인덱스가 만들어져 있어야 정렬 없이 인덱스를 사용할 수 있다.
  • WHERE 절이 있다면, WHERE 조건과 ORDER BY가 같은 인덱스를 함께 사용할 수 있어야 한다.
  • B-Tree 인덱스만 정렬에 사용 가능하며, 해시 인덱스, 전문 검색 인덱스, R-Tree 인덱스는 사용할 수 없다.

조인의 드라이빙 테이블만 정렬

  • 조인에서 드라이빙 테이블의 컬럼만으로 ORDER BY를 작성해야 한다.

임시 테이블을 이용한 정렬

  • 하나의 테이블을 정렬하거나 드라이빙 테이블만 정렬하는 것이 아니라면 2개 이상의 테이블이 조인되면서 정렬할 경우 항상 조인의 결과를 임시 테이블에 저장하고, 결과를 다시 정렬한다.

정렬 처리 방법의 성능 비교

  • ORDER BY나 GROUP BY 같은 작업은 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 해야만 LIMIT으로 건수를 제한할 수 있기 때문에 쿼리가 느려지는 경우가 많다.
  • 스트리밍 방식
    • 서버 쪽에서 조건에 일치하는 레코드가 검색될 때마다 클라이언트로 전송해주는 방식
    • LIMIT처럼 결과 건수를 제한하는 조건들은 실행 시간을 줄여줄 수 있다.
  • 버퍼링 방식
    • 결과를 모아서 서버에서 일고라 가고애야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다.
    • LIMIT처럼 결과 건수를 제한해도 성능 향상에 도움이 되지 않는다.
  • 인덱스를 사용한 정렬 방식만 스트리밍 방식이고 나머지는 모두 버퍼링 후에 정렬된다.

GROUP BY

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

  • 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY 컬럼으로 인덱스가 있다면 인덱스를 차례대로 읽으면서 그루핑하고, 결과로 조인을 처리한다.

Loose 인덱스 스캔을 이용하는 GROUP BY

  • Loose 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다.

DISTINCT

내부 임시 테이블 활용

임시 테이블이 필요한 쿼리

  • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORODER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

고급 최적화

옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션(스위치)을 결합하여 최적의 계획을 수립한다.

옵티마이저 스위치 옵션

  • optimizer_switch 시스템 변수를 이용해 제어한다.
  • MRR, Batched Key Access, Index Merge, Condition Pushdown 등 다양한 최적화 기능의 활성화 여부를 설정할 수 있다.

MRR과 배치 키 액세스 (MRR & Batched Key Access)

  • MRR (Multi-Range Read)
    • 드라이빙 테이블의 레코드를 읽어 조인 대상을 버퍼링(조인 버퍼)한 후, 스토리지 엔진에 한 번에 요청하는 방식
    • 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근하여 디스크 읽기를 최소화한다.
    • 읽은 인덱스 값들을 Join Buffer에 모아서 PK 순서대로 정렬한 뒤 레코드에 순차적으로 읽어서 속도를 높이는 기법이다.
  • BKA (Batched Key Access) 조인
    • MRR을 응용해서 실행되는 조인 방식
    • 부가적인 정렬 작업이 필요해 성능에 악영향을 줄 수도 있어 기본 비활성화되어 있다.

Block Nested Loop Join

  • 조인 조건에 인덱스를 사용할 수 없는 경우, 드라이빙 테이블의 레코드를 메모리(조인 버퍼)에 캐시한 후 드리븐 테이블과 조인하는 방식
  • MySQL 8.0.20 버전부터는 Block Nested Loop Join이 더 이상 사용되지 않고 해시 조인(Hash Join)으로 대체되었다.

ICP(Index Condition Pushdown)

  • 인덱스 범위 제한 조건으로 사용하지 못하는 조건이라도 인덱스에 포함된 칼럼이라면 스토리지 엔진으로 전달하여 최대한 필터링한 후 테이블을 읽도록 최적화한다.
  • 불필요한 테이블 데이터 읽기(랜덤 I/O)를 줄여 성능을 향상시킨다.

인덱스 확장 (Use Index Extensions)

  • InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션
  • InnoDB의 세컨더리 인덱스 리프 노드에는 프라이머리 키가 포함되어 있다는 점을 활용한다.
  • 옵티마이저가 세컨더리 인덱스의 마지막에 자동으로 추가된 프라이머리 키 칼럼까지 인지하고 실행 계획(Key_len, 정렬 등)에 활용한다.

인덱스 머지 (Index Merge)

  • 하나의 테이블에 대해 2개 이상의 인덱스를 이용하여 쿼리를 처리하는 방식이다.
  • 교집합 (Intersection)
    • 여러 인덱스 검색 결과의 교집합을 반환(AND)
  • 합집합 (Union)
    • 여러 인덱스 검색 결과의 합집합을 반환(OR)
    • 각 인덱스의 결과가 이미 정렬되어 있으므로 별도의 정렬 없이 중복 제거가 가능하다.
      • Priority Queue
  • 정렬 후 합집합 (Sort Union)
    • 각 인덱스의 검색 결과가 정렬되어 있지 않은 경우, 결과를 정렬한 후 중복을 제거하고 합집합을 반환한다.

Semi Join

  • 다른 테이블과 실제 조인을 수행하지 않고, 조건 일치 여부만 체크하는 형태의 쿼리를 최적화한다.
    • 주로 IN 서브 쿼리

Table Pull-out

  • semi join의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화

First Match

  • IN 형태를 EXISTS처럼 처리한다.
  • 조인 수행 중 일치하는 첫 번째 레코드를 찾으면 검색을 중단하고 반환한다.

Loose Scan

  • 인덱스를 사용하는 GROUP BY처럼 서브쿼리 테이블의 인덱스를 루스 인덱스 스캔으로 읽어 중복을 제거하며 조인한다.

구체화(Materialization)

  • 서브쿼리를 통째로 실행하여 내부 임시 테이블로 만든 후 조인하는 방식

중복 제거 (Duplicate Weed-out)

  • 세미 조인을 INNER JOIN으로 실행하고, 마지막에 중복된 레코드를 제거하는 방식

Condition Fanout Filter

  • 조인 시 어떤 테이블을 먼저 처리하느냐에 따라 전체 성능이 크게 달라지기 때문에, 옵티마이저는 단순히 인덱스 사용 여부만 보지 않는다.
  • 인덱스를 적용할 수 없는 조건들에 대해서도 레코드가 얼마나 걸러질지를 filtered 값으로 추정하고, 정보를 종합해 가장 적은 데이터가 흐르도록 조인 순서를 결정한다.

파생 테이블 머지 (Derived Merge)

  • FROM 절에 사용된 서브쿼리(파생 테이블)를 외부 쿼리와 병합하여 서브쿼리(임시 테이블 생성)를 제거하는 최적화다.
  • 집계 함수, DISTINCT, LIMIT 등이 사용된 경우 자동 병합이 불가능할 수 있다.

Invisible Index

  • 인덱스를 삭제하지 않고 옵티마이저가 사용하지 못하게 숨기는 기능
  • 인덱스 삭제 전 영향도를 테스트할 때 유용하다.

Skip Scan

  • 복합 인덱스에서 선행 칼럼에 대한 조건이 없어도 인덱스를 사용할 수 있게 하는 최적화
  • 선행 칼럼의 유니크한 값의 개수가 적을 때 효율적이다.

Hash Join

  • Nested Loop Joi호n을 사용할 수 없는 경우(인덱스 부재 등) 사용되는 차선책이다.
    • 조인 조건의 인덱스가 없거나 레코드 건수가 매우 적은 경우에만 사용된다.
  • Hash Join은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코들르 찾는 데까지는 시간이 많이 걸리지 않는다.
    • Best Throughput 전략에 적합
  • Nested Loop Join은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만 첫 번째 레코드를 찾는 것은 상대적으로 훨씬 빠르다.
    • Best Response-time 전략에 적합ㄴ
  • 빌드 단계: 레코드 건수가 적은 테이블을 읽어 메모리에 해시 테이블을 생성한다.
  • 프로브 단계: 나머지 테이블을 읽으며 해시 테이블을 검색해 조인한다.
  • MySQL 8.0.20부터 Block Nested Loop Join을 대신하여 사용된다.

인덱스 정렬 선호

  • ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 하는 옵션

조인 최적화 알고리즘

  • Exhaustive 검색
    • 모든 테이블 조인 조합에 대해 비용을 계산 후 비용이 가장 낮은 것을 선택하는 알고리즘
    • 항상 최적의 실행 계획을 찾아내지만 테이블이 많으면 시간이 오래 걸린다.
    • 5.0 이전에 사용되던 알고리즘
  • Greedy 검색
    • 모든 경우를 계산하지 않고 당장 최적의 조인 순서를 결정한다.
      • optimizer_search_depth만큼의 깊이만 탐색
        • default : 62
    • Exhaustive 검색의 문제점을 해결하기 위해 5.0부터 도입

쿼리 힌트

MySQL 옵티마이저가 비즈니스를 완벽히 이해하지 못해 부족한 실행 계획을 수립할 때 힌트를 사용한다.

인덱스 힌트

  • ANSI-SQL 표준 문법은 아니며 SELECT, UPDATE 명령에서만 사용 가능하다.
  • STRAIGHT_JOIN: SELECT 바로 뒤에 사용하여 조인 순서를 고정한다 (FROM 절 명시 순서대로 조인).
  • USE INDEX: 특정 인덱스를 사용하도록 권장한다.
  • FORCE INDEX: USE INDEX보다 강력하게 사용을 유도한다.
  • IGNORE INDEX: 특정 인덱스를 사용하지 못하게 한다.
  • SQL_CALC_FOUND_ROWS: LIMIT을 사용한 경우에도 전체 조건을 만족하는 레코드 수를 계산하도록 한다. (성능상 비효율적이므로 사용을 지양하고 COUNT(*) 쿼리 분리를 권장)

옵티마이저 힌트

  • MySQL 5.6부터 도입되었으며 주석(/*+ ... */) 형태로 사용하여 ANSI-SQL 표준을 준수한다.
  • 영향 범위에 따라 글로벌, 쿼리 Block, 테이블, 인덱스 수준으로 나뉜다.
  • 주요 힌트
    • MAX_EXECUTION_TIME: 쿼리 실행 시간 제한
    • SET_VAR: 쿼리 실행 동안 특정 시스템 변수 설정
    • MERGE / NO_MERGE: 파생 테이블 병합 여부 제어
    • JOIN_PREFIX / JOIN_SUFFIX: 조인 순서 제어 (드라이빙/드리븐)
    • INDEX_MERGE / NO_INDEX_MERGE: 인덱스 머지 사용 제어
    • SKIP_SCAN / NO_SKIP_SCAN: 인덱스 스킵 스캔 제어

Graph View