옵티마이저 힌트

MAX_EXECUTION_TIME

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

  SELECT /*+ MAX_EXECUTION_TIME(100)*/
  FROM employees
  ORDER BY last_name LIMIT 1;

SET_VAR

• 옵티마이저 힌트뿐만 아니라 MySQL 서버의 시스템 변수들 또한 쿼리의 실행 계획에 상당한 영향을 미친다.
  • 대표적으로 조인 버퍼의 크기를 설정하는 join_buffer_size 시스템 변수의 경우, MySQL 서버의 옵티마이전느 조인 버퍼의 공간이 충분하면 조인 버퍼를 활용하는 형태의 실행 계획을 선택할 수 있다.
  • 혹은 옵티마이저 힌트로 부족한 경우 optimizer_switch 시스템 변수를 제어해야 할 수도 있다.
• SET_VAR 힌트는 다음 용도로 사용할 수 있다.

  • 실행 계획을 바꾸는 용도

  • 조인 버퍼나 정렬용 버퍼(소트 버퍼)의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도

    SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off')*/ *
    FROM employees
    WHERE ...

• 모든 시스템 변수를 SET_VAR 힌트로 조정할 수는 없다는 점은 주의하자.

SEMIJOIN & NO_SEMIJOIN

SEMIJOIN

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

• 세미 조인의 최적화에는 여러 세부 전략이 있는데, SEMIJOIN 힌트로 어떤 세부 전략을 사용할지 제어할 수 있다.
• Table Pull-out 최적화 전략은 그 전략을 사용할 수 있다면 항상 더 나은 성능을 보장하기 때문에, 별도로 힌트를 사용할 수 없다.
• 세미 조인 최적화 인트는 외부 쿼리가 아니라 서브 쿼리에 명시해야 한다.

  SELECT *
  FROM departments d
  WHERE d.dept_no IN
  	(SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ de.dept_no
  	FROM dept_emp de);

• 다른 방법으로는 우선 서브쿼리에 쿼리 블록 이름을 정의하고 실제 세미 조인 힌트는 외부 쿼리 블록에 명시하는 방법도 있다.

  SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)*/ *
  FROM departments d
  WHERE d.dept_no IN
  	(SELECT /*+ QB_NAME(subq1)*/ de.dept_no
  	FROM dept_emp de);

NO_SEMIJOIN

• 특정 세미 조인 최적화 전략을 사용하지 않게 할 때 NO_SEMIJOIN 힌트를 명시한다.
• Table Pull-out 최적화 전략을 제외한 다른 최적화 전략들은 상황에 따라 다른 최적화 전략으로 우회하는 것이 더 나은 성능을 낼 수도 있기 때문에 NO_SEMIJOIN 힌트도 제공된다.

SELECT *
FROM departments d
WHERE d.dept_no IN
	(SELECT /*+ NO_SEMIJOIN(DUPSWEEDOUT, FIRSTMATCH)*/ de.dept_no
	FROM dept_emp de);

SUBQUERY

• 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법이다.
• 세미 조인 최적화 힌트와 비슷한 형태로, 다음 2가지 방법 중 하나로 사용하면 된다.
  • 서브쿼리에 힌트를 사용하거나
  • 서브쿼리에 쿼리 블록 이름을 지정해서 외부 쿼리 블록에서 최적화 방법을 명시
• 주로 안티 세미 조인 최적화에 사용된다.
  • 세미 조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만 안티 세미 조인의 최적화에는 사용될 수 없기 때문이다.
• 서브쿼리 최적화 전략은 사용할 기회가 그다지 많지 않다.
• 다음 2가지 형태로 최적화할 수 있다.

최적화 방법	힌트
IN-to-EXISTS	SUBQUERY(INTOEXISTS)
Materialization	SUBQUERY(MATERIALIZATION)

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

HASHJOIN & NO_HASHJOIN

• MySQL 8.0.18 버전에서만 유효하며, 그 이후 버전에서는 효력이 없다.

BNL & NO_BNL

• MySQL 8.0.20 버전부터 해시 조인 알고리즘이 블록 네스티드 루프 조인까지 대체하도록 개선됐다.
• MySQL 8.0.20 버전부터는 BNL 힌트를 사용하면 해시 조인을 유도하는 힌트로 용도가 변경됐다.

  SELECT /*+ BNL(e, de)*/ *
  FROM employees e
  	INNER JOIN dept_emp de ON de.emp_no=e.emp_no;

• MySQL 서버에서는 조인 조건이 되는 칼럼의 인덱스가 적절히 준비돼 있다면 해시 조인은 거의 사용되지 않는다.

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

STRAIGHT_JOIN 힌트의 단점

• MySQL 서버에서는 조인의 순서를 결정하기 위해 전통적으로 STRAIGHT_JOIN 힌트를 사용해왔다.
• STRAIGHT_JOIN 힌트에는 다음과 같은 단점이 있다.
  • 우선 쿼리의 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움이 있다.
  • 한 번 사용되면 FROM 절에 명시된 모든 테이블의 조인 순서가 결정되기 때문에 일부는 조인 순서를 강제하고 나머지는 옵티마이저에게 순서를 결정하게 맞기는 것이 불가능하다.

조인 순서와 관련된 옵티마이저의 힌트

• STRAIGHT_JOIN의 단점을 보완하기 위해 옵티마이저 힌트에서는 다음 4개의 힌트를 제공한다.

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

• 사용법은 다음과 같다.

  • 이 이외의 쿼리 블록의 이름까지 사용하면서 복잡하게 사용할 수도 있다.

    -- FROM 절에 나열된 테이블의 순서대로 조인 실행
    SELECT /*+ JOIN_FIXED_ORDER()*/ *
    FROM employees e
    	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    	INNER JOIN departments d ON d.dept_no=de.dept_no;
    
    -- 일부 테이블에 대해서만 조인 순서를 나열
    SELECT /*+ JOIN_ORDER(d, de)*/ *
    FROM employees e
    	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    	INNER JOIN departments d ON d.dept_no=de.dept_no;
    	
    -- 조인의 드라이빙 테이블에 대해서만 조인 순서를 나열
    SELECT /*+ JOIN_PREFIX(e, de)*/ *
    FROM employees e
    	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    	INNER JOIN departments d ON d.dept_no=de.dept_no;
    	
    -- 조인의 드리븐 테이블에 대해서만 조인 순서를 나열
    SELECT /*+ JOIN_SUFFIX(de, e)*/ *
    FROM employees e
    	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    	INNER JOIN departments d ON d.dept_no=de.dept_no;

MERGE & NO_MERGE

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

  SELECT /*+ MERGE(sub)*/ *
  FROM (SELECT *
  			FROM employees
  			WHERE first_name='Matt') sub
  LIMIT 10;

INDEX_MERGE & NO_INDEX_MERGE

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

  SELECT /*+ INDEX_MERGE(employees ix_firstname, PRIMARY)*/ *
  FROM employees
  WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;
  
  SELECT /*+ NO_INDEX_MERGE(employees PRIMARY)*/ *
  FROM employees
  WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

NO_ICP

• MySQL 옵티마이저는 최대한 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 기능을 사용하는 방향으로 실행 계획을 수립한다.
  • 인덱스 컨디션 푸시다운 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되기 때문이다.
  • 따라서 인덱스 컨디션 푸시다운을 사용하도록 하는 힌트는 제공되지 않는다.
• 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못된다면 결과적으로 잘못된 실행 계획을 수립하게 될 수도 있다.
  • 테이블의 데이터 분포는 항상 균등한 것이 아니기 때문에 쿼리 검색 범위에 따라 효율적인 인덱스가 달라질 수 있다.
• 이 같은 경우 인덱스 컨디션 푸시다운 최적화만 비활성화해서 조금 더 유연하고 정확하게 실행 계획을 선택하게 할 수 있다.

  SELECT /*+ NO_ICP(employees ix_lastname_firstname)*/ *
  FROM employees
  WHERE last_name='Action' AND first_name LIKE '%sal';

SKIP_SCAN & NO_SKIP_SCAN

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

SELECT /*+ NO_SKIP_SCAN(employees ix_gender_birthdate)*/ gender, birth_date
FROM employees
WHERE birth_date >= '1965-02-01';

INDEX & NO_INDEX

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

인덱스 힌트	인덱스 힌트를 대체하는 옵티마이저 힌트
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

• 옵티마이저 힌트를 사용할 때는 테이블명과 인덱스 이름을 함께 명시해야 한다.

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

    -- 옵티마이저 힌트 사용
    SELECT /*+ INDEX(employees ix_firstname)*/ *
    FROM employees
    WHERE first_name='Matt';
    
    -- 인덱스 힌트 사용
    SELECT *
    FROM employees USE INDEX(ix_firstname)
    WHERE first_name='Matt';

Reference

참고 서적

📔 Real MySQL 8.0