09. 옵티마이저와 힌트-3

9.4 쿼리 힌트

옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려주는 기능

 

MySQL 서버에서 사용 가능한 쿼리 힌트

• 인덱스 힌트
• 옵티마이저 힌트

인덱스 힌트

• "STRAIGHT_JOIN"과 "USE INDEX" 등 인덱스 힌트는 옵티마이저 힌트가 도입되기 전에 사용되던 기능이다.
• SQL 문법에 맞게 사용해야 하기 때문에 ANSI-SQL 표준 문법을 준수하지 못하는 단점이 있다. (인덱스 힌트도 주석 형태로 표기할 수 있지만 일반적으로 SQL의 일부 형태로 사용)
• 5.6 버전부터 추가되기 시작한 옵티마이저 힌트들은 모두 MySQL 서버를 제외한 다른 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다고 볼 수 있다. -> 가능하면 인덱스 힌트보다 옵티마이저 힌트를 사용할 것
• SELECT 와 UPDATE 명령에서만 사용 가능

STRAIGHT_JOIN

STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드

SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.

 

 

위 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙 테이블이 되고, 드리븐 테이블이 될지 알 수 없다.

옵티마이저가 그때그때 각 테이블의 통계 정보와 쿼리의 조건을 기반으로 가장 최적이라고 판단되는 순서로 조인한다.

 

 

• 테이블을 읽는 순서: departments -> dept_emp -> employees
• 일반적으로 조인을 하기 위한 컬럼들의 인덱스 여부로 조인의 순서가 결정되고, 조인 컬럼의 인덱스에 아무런 문제가 없는 경우(WHERE 조건이 있는 경우 WHERE 조건을 만족하는) 레코드가 적은 테이블을 드라이빙으로 선택한다.
• 이 쿼리의 경우 departments 테이블이 레코드 건수가 가장 적어서 드라이빙으로 선택됐을 것으로 추측된다.

이 쿼리의 조인 순서를 변경하기 위해 STRAIGHT_JOIN 힌트를 사용할 수 있다.

 

표기법만 다르고 동일한 쿼리

 

두 예제 모두 STRAIGHT_JOIN 키워드를 SELECT 키워드 바로 뒤에 사용했다.

인덱스 힌트는 사용해야 하는 위치가 정해져 있으므로 그 외 다른 위치에는 사용하지 못한다.

STRAIGHT_JOIN 힌트는 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도하고 있다. 

 

 

• 실행 계획 상의 조인 순서가 employees(e) -> dept_emp(de) -> departments(d) 로 바뀌었다.

 

다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

• 임시 테이블(인라인 뷰 또는 파생 테이블)과 일반 테이블의 조인
  • 이 경우 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다.
  • 일반 테이블의 조인 컬럼에 인덱스가 없는 경우에는 레코드 건수가 작은 쪽을 드라이빙으로 선택하는 것이 좋은데, 대부분 옵티마이저가 적절한 조인 순서를 선택하기 때문에 쿼리를 작성할 때부터 힌트를 사용할 필요는 없다. 옵티마이저가 실행 계획을 제대로 수립하지 못해서 심각한 성능 저하가 있는 경우에만 힌트를 사용하면 된다.
• 임시 테이블끼리 조인
  • 임시 테이블(서브쿼리로 파생된 테이블)은 항상 인덱스가 없기 때문에 어느 테이블을 드라이빙으로 읽어도 무관하므로 크기가 작은 테이블을 드라이빙으로 선택하는 것이 좋다.
• 일반 테이블끼리 조인
  • 양쪽 테이블 모두 조인 컬럼에 인덱스가 있거나 양쪽 테이블 모두 조인 컬럼에 인덱스가 없는 경우에는 레코드 건수가 적은 테이블을 드라이빙으로 선택하는 것이 좋다.
  • 그 외에는 조인 컬럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋다.

여기서 언급한 레코드 건수는 인덱스를 사용할 수 있는 WHERE 조건까지 포함해서 그 조건을 만족하는 레코드 건수를 의미한다.

 

STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트

• JOIN_FIXED_ORDER
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

JOIN_FIXED_ORDER 는 STRAIGHT_JOIN 와 동일한 효과를 낸다. 한 번 사용되면 FROM 절의 모든 테이블에 대해 조인 순서가 결정된다.

나머지 3개는 일부 테이블의 조인 순서에 대해서만 제안하는 힌트다.

USE INDEX / FORCE INDEX / IGNORE INDEX

조인 순서를 변경하는 STRAIGHT_JOIN 와 달리 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.

• 키워드 뒤에 사용할 인덱스의 이름을 괄호에 명시한다.
• 괄호 안에 아무것도 없거나 존재하지 않는 인덱스 이름을 사용할 경우 쿼리 문법 오류로 처리된다.
• 별도로 사용자가 부여한 이름이 없는 프라이머리 키는 "PRIMARY"라고 명시하면 된다.
• 선택적으로 용도를 제한할 수 있다. [ USE | FORCE | IGNORE ] INDEX FOR [ JOIN | ORDER BY ]

 

USE INDEX

• 가장 자주 사용되는 인덱스 힌트
• 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
• 대부분의 경우 옵티마이저는 사용자의 힌트를 채택하지만 항상 그 인덱스를 사용하는 것은 아니다.

FORCE INDEX

• USE INDEX 보다 옵티마이저에 미치는 영향이 더 강한 힌트
• 그러나 USE INDEX 힌트만으로도 옵티마이저에 대한 영향력이 충분히 크기 때문에 거의 사용할 일이 없다.
• 경험적으로 USE INDEX 힌트를 부여해도 그 인덱스를 사용하지 않는 경우, FORCE INDEX 힌트를 사용해도 그 인덱스를 사용하지 않았다.

IGNORE INDEX

• 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트
• 때로는 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 사용할 수 있다.

 

 

• 첫 번째부터 세 번째까지의 쿼리는 모두 employees 테이블의 프라이머리 키(emp_no)를 이용해 동일한 실행 계획으로 쿼리를 처리한다.
• 인덱스 힌트가 없는 첫 번째 쿼리도 "emp_no=10001" 조건이 있기 때문에 프라이머리 키를 사용하는 것이 최적이라는 것을 옵티마이저도 인식하기 때문이다.
• 네 번째 쿼리는 IGNORE INDEX 힌트를 통해 프라이머리 키를 사용하지 못하도록 했다. 
• 다섯 번째 쿼리는 FORCE INDEX 힌트를 통해 전혀 관계없는 인덱스를 사용하도록 했다.

 

옵티마이저는 프라이머리 키나 전문 검색(Full Text search) 인덱스 > 일반 보조 인덱스(B-Tree 인덱스) 순으로 가중치를 두고 실행 계획을 수립한다.

인덱스의 사용법이나 좋은 실행 계획이 어떤 것인지 판단하기 힘든 상황이라면 힌트를 사용해 강제로 옵티마이저의 실행 계획에 영향을 미치는 것은 피하는 것이 좋다. 옵티마이저가 쿼리 실행 시점의 통계 정보를 가지고 선택하는 것이 가장 좋다.

 

가장 훌륭한 최적화는 그 쿼리를 서비스에서 없애 버리거나 튜닝할 필요가 없게 데이터를 최소화하는 것이며, 그것이 어렵다면 데이터 모델의 단순화를 통해 쿼리를 간결하게 만들고 힌트가 필요치 않게 하는 것이다. 

SQL_CALC_FOUND_ROWS

LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 많다고 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춘다.

SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리는 LIMIT을 만족하는 수만큼의 레코드를 찾았다고 하더라도 끝까지 검색을 수행한다. 물론 실제 반환하는 결과 레코드 수는 LIMIT에 제한된다.

 

 

• SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리가 실행된 경우, FOUND_ROW() 함수를 이용해 LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇 건인지 알아낼 수 있다.

 

SQL_CALC_FOUND_ROWS 를 사용한 페이징 처리

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE first_name='Georgi' LIMIT 0, 20;
mysql> SELECT FOUND_ROWS() AS total_record_count;

 

• 쿼리 한 번으로 필요한 정보 2가지를 모두 가져오는 것처럼 보이지만 쿼리를 각각 실행해야 한다.
• first_name='Georgi' 조건을 처리하기 위해 employees 테이블의 ix_firstname 인덱스를 레인지 스캔으로 실제 값을 읽어오는데, 이 조건을 만족하는 레코드가 253건이라고 가정한다.
• LIMIT 조건이 있지만 SQL_CALC_FOUND_ROWS 힌트에 따라 조건을 만족하는 레코드를 전부 읽어야 한다.
• ix_firstname 인덱스를 통해 실제 데이터 레코드를 찾아가는 작업 253번 => 랜덤 I/O 253번

기존 2개의 쿼리로 쪼개어 실행하는 방법

mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
mysql> SELECT * FROM employees WHERE first_name='Georgi' LIMIT 0, 20

 

• 첫 번째 쿼리의 WHERE 조건절에서도 ix_firstname 인덱스를 레인지 스캔한다. 실제 레코드 데이터가 아닌 건수만 가져오면 되기 때문에 랜덤 I/O는 발생하지 않는다. -> 커버링 인덱스(Covering index) 쿼리
• 두 번째 쿼리는 ix_firstname 인덱스를 레인지 스캔으로 접근하고, 실제 데이터 레코드를 읽어오기 위해 LIMIT 제한만큼 랜덤 I/O를 20번만 실행한다. 

전기적 처리인 메모리나 CPU의 연산 작업에 비해 기계적 처리인 디스크 작업이 얼마나 느린 작업인지 고려하면 비교할 수 없을 만큼 SQL_CALC_FOUND_ROWS를 사용하는 경우가 느리다.

SELECT 문장이 UNION(또는 UNION DISTINCT)으로 연결된 경우 SQL_CALC_FOUND_ROWS 힌트를 사용해도 FOUND_ROWS() 함수로 정확한 레코드 건수를 가져올 수 없다는 문제점도 있다.

 

SQL_CALC_FOUND_ROWS 는 성능 향상을 위해 만들어진 힌트가 아니라 개발자의 편의를 위해 만들어진 힌트이며, 레코드 카운터용 쿼리와 데이터를 조회하는 쿼리는 분리하는 것이 효율적이다.

옵티마이저 힌트

옵티마이저 힌트 종류

옵티마이저 힌트는 영향 범위에 따라 다음 4개 그룹으로 나뉜다.

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

이 구분으로 힌트의 사용 위치가 달라지는 것은 아니다.

 

 

참고: 모든 인덱스 수준의 힌트는 인덱스 이름을 명시할 때 그 인덱스를 가진 테이블명을 먼저 명시해야 한다.

 

• INDEX(employees ix_firstname)
• NO_INDEX(employees ix_firstname)

옵티마이저 힌트가 문법에 맞지 않을 경우 다음과 같이 경고 메시지가 표시된다.

일반적으로 EXPLAIN 명령은 파싱된 쿼리의 재조립된 결과를 보여주는 용도로 1개의 경고 메시지를 출력한다.

 

 

쿼리 블록 옵티마이저 힌트

• 하나의 SQL 문장에서 SELECT 키워드는 여러 번 사용될 수 있다. 이때 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록이라고 한다.
• 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수 있지만 외부 쿼리 블록에서도 사용할 수 있다.
• 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 "QB_NAME()" 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 한다.

 

• QB_NAME(subq1): 특정 쿼리 블록(서브쿼리)에 "subq1"이라는 이름을 부여하고, 그 쿼리 블록을 외부 힌트에 사용한다.
• JOIN_ORDER(e, s@subq1): 서브쿼리에 사용된 salaries 테이블이 세미 조인 최적화를 통해 조인으로 처리될 것을 예상하고 JOIN_ORDER 힌트를 사용한 것이며, 조인의 순서로 외부 쿼리 블록의 employees 테이블과 서브 쿼리 블록의 salaries 테이블을 순서대로 조인하게 했다. 

MAX_EXECUTION_TIME

옵티마이저 힌트 중에 유일하게 쿼리의 실행 계획에 영향을 미치치 않는 힌트이며, 단순히 쿼리의 최대 실행 시간을 설정한다.

MAX_EXECUTION_TIME 힌트는 밀리초 단위의 시간을 설정하는데, 쿼리가 지정된 시간을 초과하면 다음과 같이 쿼리는 실패한다.

 

SET_VAR

옵티마이저 힌트 뿐만 아니라 MySQL 서버의 시스템 변수들도 쿼리의 실행 계획에 큰 영향을 미친다.

ex) join_buffer_size, optimizer_switch 등

쿼리의 실행 계획을 수립하는 시점에 시스템 변수를 제어하기 위해 SET_VAR 힌트를 이용할 수 있다.

 

mysql> EXPLAIN
	SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ *
	FROM employees
	WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

 

SET_VAR 힌트는 실행 계획을 바꾸는 용도 외에 조인 버퍼나 소트 버퍼의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로도 사용할 수 있다.

다양한 형태의 시스템 변수를 조정할 수 있지만 모든 시스템 변수를 조정할 수 있는 것은 아니다.

SEMIJOIN & NO_SEMIJOIN

세미 조인 최적화는 여러 세부 전략이 있다. SEMIJOIN 힌트는 어떤 세부 전략을 사용할지 제어하는 데 사용한다.

 

 

Table Pull out 최적화 전략은 별도로 힌트를 사용할 수 없다. 그 전략을 사용할 수 있다면 항상 더 나은 성능을 보장하기 때문이다.

다른 최적화 전략들은 상황에 따라 다른 최적화 전략으로 우회하는 것이 더 나은 성능을 낼 수도 있기 때문에 NO_SEMIJOIN 힌트도 제공된다.

 

예) 세미 조인 최적화 전략으로 FirstMatch 를 사용하는 쿼리

 

세미 조인 힌트 사용법

• 서브쿼리에 세미 조인 최적화 힌트 명시하기
• 서브쿼리에 쿼리 블록 이름을 정의하고 실제 세미 조인 힌트는 외부 쿼리 블록에 명시하기

 

서브쿼리에 명시

 

외부 쿼리에 명시 (쿼리 블록 이름 사용)

 

특정 세미 조인 최적화 전략을 사용하지 않게 하려면 NO_SEMIJOIN 힌트를 명시한다.

 

NO_SEMIJOIN()

SUBQUERY

서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법으로, 서브쿼리는 다음 2가지 형태로 최적화할 수 있다.

 

 

세미 조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만 안티 세미 조인(Anti Semi-Join)의 최적화에는 사용될 수 없다. 

그래서 주로 안티 세미 조인 최적화에 위 2가지 최적화가 사용된다.

서브쿼리에 힌트를 사용하거나 서브 쿼리에 쿼리 블록 이름을 지정해서 외부 쿼리 블록에서 최적화 방법을 명시한다.

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

8.0.18 버전부터 도입된 해시 조인(Hash Join) 알고리즘이 블록 네스티드 루프(Block Nested Loop) 조인을 대체하여

8.0.20 버전부터 블록 네스티드 루프 조인은 MySQL 서버에서 더 이상 사용되지 않는다. 

• BNL은 8.0.20 버전부터 해시 조인을 사용하도록 유도하는 힌트로 용도가 변경되었다.
• HASHJOIN & NO_HASHJOIN: 8.0.18 버전에서만 유효하며, 그 이후 버전에는 효력이 없다.
• 8.0.20 버전에서 해시 조인을 유도하거나 해시 조인을 사용하지 않게 하고자 한다면 BNL과 NO_BNL 힌트를 사용해야 한다.

참고: MySQL 서버에서는 조인 조건이 되는 컬럼의 인덱스가 적절히 준비돼 있다면 해시 조인은 거의 사용되지 않는다. 위의 예제 쿼리에서도 힌트를 사용하긴 했지만 실제 실행 계획에서는 해시 조인이 아닌 네스티드 루프 조인을 실행하게 될 것이다. 해시 조인 알고리즘을 사용하게 하려면 조인 조건이 되는 emp_no 컬럼의 인덱스를 employees 테이블과 dept_emp 테이블에서 모두 제거하거나 사용하지 못하게 해야 한다.

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

MySQL 서버에서는 조인의 순서를 결정하기 위해 전통적으로 STRAIGHT_JOIN 힌트를 사용해왔다.

 

STRAIGHT_JOIN

• 쿼리의 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움이 있다.
• 한 번 사용되면 FROM 절에 명시된 모든 테이블의 조인 순서가 결정되기 때문에 일부는 조인 순서를 강제하고 나머지는 옵티마이저에게 순서를 결정하게 하는 것이 불가능하다.

이러한 단점을 보완하는 4개의 옵티마이저 힌트를 제공한다.

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

MERGE & NO_MERGE

예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리를 항상 내부 임시 테이블로 생성했다.

이는 불필요한 자원 소모를 유발하기 때문에 5.7, 8.0 버전에서는 가능하면 임시 테이블을 사용하지 않게 FROM 절의 서브쿼리를 외부 쿼리와 병합하는 최적화를 도입했다.

 

FROM 절에 사용된 서브쿼리 처리

• 내부 임시 테이블 생성: 파생 테이블(Derived Table) 사용 -> NO_MERGE
• 내부 쿼리를 외부 쿼리와 병합 -> MERGE

옵티마이저가 최적의 방법을 선택하지 못할 때 MERGE 또는 NO_MERGE 옵티마이저 힌트를 사용하면 된다.

 

INDEX_MERGE & NO_INDEX_MERGE 

MySQL 서버는 가능하면 테이블당 하나의 인덱스만을 사용해 쿼리를 처리하려고 한다. 하지만 하나의 인덱스만으로 검색 대상 범위를 충분히 좁힐 수 없다면 사용 가능한 다른 인덱스를 이용하기도 한다.

 

인덱스 머지(Index Merge)

하나의 테이블에 대해 여러 개의 인덱스를 사용해서 검색한 레코드의 교집합 또는 합집합을 반환하는 방법

• INDEX_MERGE
• NO_INDEX_MERGE 

NO_ICP

인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되므로 옵티마이저는 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립한다.

그래서 ICP 힌트는 제공하지 않는다.

 

참고: MySQL 5.6 이전 버전에서는 인덱스를 범위 제한 조건으로 사용하지 못하는 경우 MySQL 엔진이 WHERE 필터링 조건을 스토리지 엔진에 전달조차 하지 않아서 직접 데이터 레코드를 읽어와야 했다. (불필요한 데이터 읽기)

-> 인덱스 컨디션 푸시다운 (인덱스를 이용해 필터링을 가능하게 함)

 

테이블의 데이터 분포는 항상 균등한 것이 아니기 때문에 쿼리 검색 범위에 따라 효율적인 인덱스가 바뀔 수 있다. 이 같은 경우에는 인덱스 컨디션 푸시다운 최적화만 비활성화해서 좀 더 유연하고 정확하게 실행 계획을 선택하게 할 수 있다.

 

SKIP_SCAN & NO_SKIP_SCAN

인덱스 스킵 스캔은 인덱스의 선행 컬럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스를 사용할 수 있게 해주는 최적화 기능이다.

하지만 조건이 없는 선행 컬럼의 유니크한 값의 개수가 많아지면 성능이 오히려 떨어진다.

옵티마이저가 유니크한 값의 개수를 제대로 분석하지 못하거나 잘못된 경로로 인해 비효율적인 인덱스 스킵 스캔을 선택하면 NO_SKIP_SCAN 옵티마이저 힌트를 이용해 인덱스 스킵 스캔을 사용하지 않게 할 수 있다.

 

INDEX & NO_INDEX

예전 MySQL 서버에서 사용되던 인덱스 힌트를 대체하는 용도로 제공되는 옵티마이저 힌트.

 

인덱스 힌트를 대체하는 옵티마이저 힌트

 

인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열했다.

하지만 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 한다.

 

-- // 인덱스 힌트 사용 (특정 테이블 뒤)
mysql> EXPLAIN
	SELECT * FROM employees USE INDEX(ix_firstname)
	WHERE first_name='Matt';
    
-- // 옵티마이저 힌트 사용 (SELECT 절 주석, 테이블명 명시)
mysql> EXPLAIN
	SELECT /*+ INDEX(employees ix_firstname) */ * 
	FROM employees 
	WHERE first_name='Matt';