옵티마이저와 힌트 (2/2)

쿼리 힌트

MySQL 서버는 우리가 서비스하는 비즈니스를 100% 이해하지 못하기 때문에 서비스 개발자나 DBA보다 MySQL 서버가 부족한 실행 계획을 수립할 때가 있을 수 있다. 이런 경우 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요하기 때문에 이런 목적으로 옵티마이저 힌트가 제공된다.

MySQL 서버에서 사용 가능한 쿼리 힌트는 다음과 같이 2가지로 구분할 수 있다.

• 인덱스 힌트
  • "STRAIGHT_JOIN"과 "USE INDEX" 등을 포함한 인덱스 힌트들은 SELECT 명령과 UPDATE 명령에서만 사용할 수 있다.
• 옵티마이저 힌트

 

STRAIGHT JOIN

STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드이기도 하다. STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다. 

 

위 쿼리는 3개의 테이블을 조인하는데 옵티마이저는 그때그때 각 테이블의 통계 정보와 쿼리의 조건을 기반으로 가장 최적으로 판단되는 순서로 조인한다. 실행 계획을 보면 레코드가 적은 테이블인 departments 테이블을 드라이빙 테이블로 선택했고, 두 번째 dept_emp 테이블을 읽은 뒤에 마지막으로 employees 테이블을 읽었음을 알 수 있다.

 

이 쿼리의 조인 순서를 변경하려는 경우에는 STRAIGHT_JOIN 힌트를 사용할 수 있다. STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도하는데, 이 쿼리의 실행 계획을 보면 FROM 절에 명시된 테이블의 순서대로 employees -> dept_emp -> departments 조인을 수행한다는 것을 알 수 있다. 

STRAIGHT_JOIN을 사용해야하는 경우에는 테이블 전체 레코드 건수가 아닌 WHERE 조건까지 포함해서 그 조건을 만족하는 레코드 건수가 적은 것을 드라이빙되게 하는 것이 좋다.

STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음과 같은 것들이 있다.

• JOIN_FIXED_ORDER
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

JOIN_FIXED_ORDER 옵티마이저 힌트는 STRAIGHT_JOIN 힌트와 동일한 효과를 내고, 나머지 3개의 옵티마이저 힌트는 STRAIGHT_JOIN과 달리 일부 테이블의 조인 순서에 대해서만 제안하는 힌트다.

 

USE INDEX / FORCE INDEX / IGNORE INDEX

조인의 순서를 변경하는 것 다음으로 자주 사용되는 인덱스 힌트인데, STRAIGHT_JOIN 힌트와는 달리 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다. 가끔 3 ~ 4개 이상의 컬럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우에는 옵티마이저가 실수를 하는데, 이런 경우 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.

• USER INDEX: 가장 자주 사용되는 인덱스 힌트로, MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트 정도로 생각하면 된다. 대부분의 경우 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만 항상 그 인덱스를 사용하는 것은 아니다.
• FORCE INDEX: USE INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트이지만 USE INDEX 힌트만으로 옵티마이저에게 미치는 영향은 충분히 크기 때문에 거의 사용할 필요가 없다.
• IGNORE INDEX: USE INDEX나 FORCE INDEX와는 반대로 특정 인덱스를 사용하지 못하게 하는 용도의 힌트다. 떄로는 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 IGNORE INDEX 힌트를 사용할 수도 있다.

위 3종류의 인텍스 힌트 모두 용도를 명시해 줄 수 있다. 용도는 선택 사항이며, 특별히 인덱스 힌트에 용도가 명시되지 않으면(사용 가능한 경우) 주어진 인덱스를 3가지 용도로 사용한다.

• USE INDEX FOR JOIN: 여기서 JOIN이라는 키워드는 테이블 간의 조인뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어다. MySQL 서버에서는 하나의 테이블로부터 데이터를 검색하는 작업도 JOIN이라고 표현하기 때문에 FOR JOIN이라고 이름을 붙인 것이다.
• USE INDEX FOR ORDER BY: 명시된 인덱스를 ORDER BY 용도로만 사용할 수 있게 제한한다.
• USE INDEX FOR GROUP BY: 명시된 인덱스를 GROUP BY 용도로만 사용할 수 있게 제한한다.

 

첫 번째부터 세 번째까지의 쿼리는 모두 employees 테이블의 프라이머리 키를 이용해 동일한 실행 계획으로 쿼리를 처리한다. 인덱스 힌트가 주어지지 않아도 "emp_no=10001" 조건이 있기 때문에 프라이머리 키를 사용하는 것이 최적이라는 것을 옵티마이저도 인식하고 있다.

네 번째는 일부러 인게슬르 사용하지 못하게 힌트를 추가했고, 다섯 번째는 전혀 관계없는 인덱스를 사용하도록 FORCE INDEX 힌트를 사용했더니 프라이머리 키는 버리고 풀 테이블 스캔을 하는 형태로 실행 계획이 출력됐다.

  

SQL_CALC_FOUND_ROWS

MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많다고 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춘다. 하지만 SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우에는 LIMIT을 만족하는 수만큼의 레코드를 찾았다고 하더라도 끝까지 검색을 수행한다. 그리고 찾아오는 레코드 건수마다 랜덤 I/O가 발생하기 때문에 사용하지 않는 방향으로 추천한다.

 

옵티마이저 힌트

• 인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
• 테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
• 쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서, 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
• 글로벌(쿼리 전체): 전체 쿼리에 대해서 영향을 미치는 힌트

옵티마이저 힌트는 영향 범위에 따라 위 4개 그룹으로 나누어 볼 수 있다. 모든 인덱스 수준의 힌트는 반드시 테이블명이 선행되어야 한다.

힌트 이름			설명				영향 범위
MAX_EXECUTION_TIME			쿼리의 실행 시간 제한				글로벌
RESOURCE_GROUP			쿼리 실행의 리소스 그룹 설정				글로벌
SET_VAR			쿼리 실행을 위한시스템 변수 제어				글로벌
SUBQUERY			서브쿼리의 세미 조인 최적화(MATERIALIZATION과 INTOEXISTS) 전략 제어				쿼리 블록
BKA, NO_BKA			BKA(Batched Key Access) 조인 사용 여부 제어				쿼리 블록, 테이블
BNL, NO_BNL			블록 네스티드 루프 조인(BNL) 사용 여부 제어(MySQL 8.0.18 이전까지는 블록 네스티드 루프 조인 제어, MySQL 8.0.20 부터는 해시 조인 사용 여부 제어)				쿼리 블록, 테이블
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN			외부 쿼리의 조건을 서브쿼리로 옮기는 최적화 사용 여부 제어				쿼리 블록, 테이블
HASH_JOIN, NO_HASH_JOIN			해시 조인 사용 여부 제어(MySQL 8.0.18 버전에서만 사용 가능)				쿼리 블록, 테이블
JOIN_FIXED_ORDER			FROM 절에 명시된 테이블 순서대로 조인 실행				쿼리 블록
JOIN_ORDER			힌트에 명시된 테이블 순서대로 조인 실행				쿼리 블록
JOIN_PREFIX			힌트에 명시된 테이블을 조인의 드라이빙 테이블로 조인 실행				쿼리 블록
JOIN_SUFFIX			힌트에 명시된 테이블을 조인의 드리븐 테이블로 조인 실행				쿼리 블록
QB_NAME			쿼리 블록의 이름 설정을 위한 힌트				쿼리 블록
SEMIJOIN, NO_SEMIJOIN			서브쿼리의 세미 조인최적화(DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION) 전략제어				쿼리 블록
MERGE, NO_MERGE			FROM 절의 서브쿼리나 뷰를 외부 쿼리 블록으로 병합하는 최적화를 수행할지 여부 제어				테이블, 인덱스
INDEX_MERGE, NO_INDEX_MERGE			인덱스 병합 실행 계획 사용 여부				테이블, 인덱스
MRR, NO_MRR			MRR(Multi-Range Read) 사용 여부 제어				테이블, 인덱스
NO_ICP			ICP(인덱스 컨디션 푸시다운) 최적화 전략 사용 여부 제어				테이블, 인덱스
NO_RANGE_OPTIMIZATION			인덱스 레인지 액세스를 비활성화(특정 인덱스를 사용하지 못하도록 하거나, 쿼리를 풀 테이블 스캔 방식으로 처리)				테이블, 인덱스
SKIP_SCAN, NO_SKIP_SCAN			인덱스 스킵 스캔 사용 여부 제어				테이블, 인덱스
INDEX, NO_INDEX			GROUP BY, ORDER BY, WHERE 절의 처리를 위한 인덱스 사용 여부 제어				인덱스
GROUP_INDEX, NO_GROUP_INDEX			GROUP BY 절의 처리를 위한 인덱스 사용 여				인덱스
JOIN_INDEX, NO_JOIN_INDEX			WHERE 절의 처리를 위한 인덱스 사용 여부 제어				인덱스
ORDER_INDEX, NO_ORDER_INDEX			ORDER BY 절의 처리를 위한 인덱스 사용 여부 제어				인덱스

 

 

 

하나의 SQL 문장에서 SELECT 키워드는 여러 번 사용될 수 있는데 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록이라고 한다. 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 "QB_NAME()" 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 한다. 위 쿼리는 특정 쿼리 블록(서브쿼리)에 대해 "subq1"이라는 이름을 부여하고, 그 쿼리 블록을 힌트에 사용한다.

 

MAX_EXECUTION_TIME

옵티마이저 힌트 중에서 유일하게 쿼리의 실행 계획에 영향을 미치지 않는 힌트이며, 단순히 쿼리의 최대 실행 시간을 설정하는 힌트다. MAX_EXECUTION_TIME 힌트에는 밀리초 단위의 시간을 설정하는데, 쿼리가 지정된 시간을 초과하면 쿼리는 실패한다.

 

SET_VAR

옵티마이저 힌트뿐만 아니라 MySQL 서버의 시스템 변수들 또한 쿼리의 실행 계획에 상당한 영향을 미친다. SET_VAR 힌트는 실행 계획을 바꾸는 용도뿐만 아니라 조인 버퍼나 정렬용 버퍼(소트 버퍼)의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로 사용할 수 있다.

 

SEMIJOIN & NO_SEMIJOIN

최적화 전략	힌트
Duplicate Weed-out	SEMIJOIN(DUPSWEEDOUT)
First Match	SEMIJOIN(FIRSTMATCH)
Loose Scan	SEMIJOIN(LOOSESCAN)
Materializtion	SEMIJOIN(MATERIALIZATION)
Table Pull-out	없음

 

쿼리가 다른 최적화 전략을 사용하도록 세미 조인 힌트를 사용할 수 있고, 세미 조인 최적화 힌트는 외부 쿼리가 아닌 서브쿼리에 명시해야 한다. 다른 방법으로 쿼리 블록 이름을 정의하고 실제 세미 조인 힌트는 외부 쿼리 블록에 명시하는 방법이 있다. 상황에 따라 다른 최적화 전략으로 우회하는 것이 더 나은 성능을 낼 수도 있기 때문에 NO_SEMIJOIN 힌트도 제공된다. 

 

SUBQUERY

• IN-to-EXISTS: SUBQUERY(INTOEXISTS)
• Materialization: SUBQUERY(MATERIALIZATION)

서브쿼리 최적화는 세미 조인 최적호가 사용되지 못할 때 사용하는 최적화 방법으로 위 2가지 형태로 최적화할 수 있다. 세미 조인 최적화는 주로 IN (subquery) 형태의 쿼리에 사용될 수 있지만 안티 세미 조인(Anti Semi-Join)의 최적화에는 사용될 수 없어 위의 2가지 최적화가 사용된다.

 

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

MySQL 8.0.19 버전까지는 블록 네스티드 루프 조인 알고리즘을 사용했지만 MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인까지 해시 조인 알고리즘이 사용되도록 개선됐다. 그래서 MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인이 사용되지 않는다.

 

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

MySQL 서버에서는 조인의 순서를 결정하기 위해 전통적으로 STRAIGHT_JOIN 힌트를 사용했다. 하지만 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움이 있다. 이 같은 단점을 보완하기 위해 STRAIGHT_JOIN과 동일한 힌트까지 포함해서 다음과 같이 4개의 힌트를 제공한다.

• JOIN_FIXED_ORDER: STRAIGHT_JOIN 힌트와 동일하게 FROM 절의 테이블 순서대로 조인을 실행하게 하는 힌트
• JOIN_ORDER: FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트
• JOIN_PREFIX: 조인에서 드라이빙 테이블만 강제하는 힌트
• JOIN_SUFFIX: 조인에서 드리븐 테이블(가장 마지막에 조인되어야 할 테이블)만 강제하는 힌트

 

MERGE & NO_MERGE

예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리를 항상 내부 임시 테이블로 생성하고 이를 파생 테이블(Derived table0이라고 하는데, 이는 불필요한 자원 소모를 유발한다. 따라서 임시 테이블을 사용하지 않게 FROM 절의 서브쿼리를 외부 쿼리와 병합하는 최적화를 도입했다. MySQL 옵티마이저가 최적의 방법을 선택하지 못할 경우에 MERGE 또는 NO_MERGE 옵티마이저 힌트를 사용하면 된다.

 

INDEX_MERGE & NO_INDEX_MERGE

MySQL 서버는 가능하다면 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려고 한다. 하지만 하나의 인덱스만으로 검색 대상 범위를 충분히 좁힐 수 없다면 사용 가능한 다른 인덱스를 이용하기도 한다. 여러 인덱스를 통해 검색된 레코드로부터 교집합 또는 합집합만을 구해서 그 결과를 반환하는데 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용하는 것을 인덱스 머지(Index Merge)라고 한다. 인덱스 머지 실행 계획의 사용 여부를 제어하고자 할 때, INDEX_MERGE와 NO_INDEX_MERGE 옵티마이저 힌트를 이용하면 된다.

 

NO_ICP

인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되므로 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립한다. 하지만 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못된다면 결과적으로 잘못된 실행 계획을 수립하게 될 수 있으므로 인덱스 컨디션 푸시다운 최적화만 비활성화해서 조금 더 유연하고 정확하게 실행 계획을 선택하게 할 수 있다.

 

SKIP_SCAN & NO_SKIP_SCAN

인덱스 스킵 스캔은 인덱스의 선행 컬럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스를 사용할 수 있게 해주는 매우 훌륭한 최적화 기능이다. 하지만 조건이 누락된 선행 컬럼이 가지는 유니크한 값의 개수가 많아진다면 인덱스 스킵 스캔의 성능은 오히려 더 떨어질 수 있다. MySQL 옵티마이저가 유니크한 값의 개수를 제대로 분석하지 못하거나 잘못된 경로로 인해 비효율적인 인덱스 스킵 스캔을 선택하면 NO_SKIP_SCAN 옵티마이저 힌트를 이용해 인덱스 스킵 스캔을 사용하지 않게 할 수 있다.

 

INDEX & NO_INDEX

인덱스 힌트	옵티마이저 힌트
USE INDEX	INDEX
USE INDEX FOR GROUP BY	GROUP_INDEX
USE INDEX FOR ORDER BY	ORDER_INDEX
IGNORE INDEX	NO_INDEX
IGNORE INDEX FOR GROUP BY	NO_GROUP_INDEX
IGNORE INDEX FOR ORDER BY	NO_ORDER_INDEX

INDEX와 NO_INDEX 옵티마이저 힌트는 예전 MySQL 서버에서 사용되던 인덱스 힌트를 대체하는 용도로 제공된다. 인덱스 힌트를 대체하는 옵티마이저 힌트는 위과 같다. 인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열했는데 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 한다.

 

 

 

출처

Real MySQL 8.0 1권 : 개발자와 DBA를 위한 MySQL 실전 가이드