Mysql 쿼리 힌트

쿼리 힌트

Mysql 서버에서 사용 가능한 쿼리 힌트는 2가지로 구분 할수 있다

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

참고로 Mysql 5.7 버전까지는 힌트를 쓰더라도 옵티마이저가 힌트 외의 실행계획을 평가 하기 때문에 실행계회을 세우는 오버로드를 줄여 주지는 못한다. 다만 옵티마이저의 잘못된 실행계획을 방지할 순 있다. 8.0 부터는 힌트 작성시 옵티마이저가 실행계획을 만들지 않기 때문에 오버로드 측면에서는 도움이 된다.

 

 

인덱스 힌트

인덱스 힌트는 Mysql 5.6 이전까지 사용했었고 그 이후부터는 옵티마이저 힌트가 추가됨에 따라 옵티마이저 힌트 사용을 권고하고 있다. 인덱스 힌트를 사용하게 되면 ANSI SQL문법에 위배되며 Mysql 이 아닌 타 DBMS에서는 에러가 발생한다. 하지만 옵티마이저 힌트는 타 DBMS에서는 주석으로 처리되기 때문에 옵티마이저 힌트 사용을 추천하게 된것이다.

STRAIGHT_JOIN

STARIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다. STARIGHT_JOIN은 SELECT 키워드 뒤에 위치한다.아래의 쿼리는 조인 순서를 FROM절에 명시한대로 EMPLOYEE → DEPARTMENT 순으로 읽게 된다.

SELECT STRAIGHT_JOIN
       e.first_name, d.dept_name
  FROM EMPLOYEE e, DEPARTMENT d
 WHERE e.dept_no = d.dept_no


SELECT /*! STRAIGHT_JOIN */
       e.first_name, d.dept_name
  FROM EMPLOYEE e, DEPARTMENT d
 WHERE e.dept_no = d.dept_no

 

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

• JOIN_FIXED_ORDER : STARIGHT_JOIN와 동일함
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

---

USE INDEX / FORCE INDEX / IGNORE INDEX

인덱스 힌트들은 STARIGHT_JOIN과 달리 힌트를 테이블 뒤에 명시해야 한다. 대체로 옵티마이저가 인데스를 잘 사용하면 편인데 3~4개 이상의 컬럼을 포함하는 비슷한 인덱스가 여러개 존재하는 경우에는 가끔 옵티마이저가 실수를 하는데 이런 경우에는 강제로 특정 인덱스를 사용하도록 힌트를 추가해야 한다.

인덱스 힌트 사용시 해당 인덱스가 없거나 괄호안에 아무것도 존재하지 않으면 쿼리의 문법 오류로 처리된다.

• USE INDEX
  가장 자주 사용되는 인덱스 힌트로 MYSQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트 정도로 생각하면 된다. 대부분의 경우 인덱스 힌트가 주어지면 옵티ㅏ이저는 사용자의 힌트를 채택하지만 항상 그 인덱스를 사용하는 것은 아니다.
• FORCE INDEX
  INDEX와 비교해서 다른 점은 없으며, USE_INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트로 생각하면 된다. USE_INDEX 사용만으로도 충분해 보이지만 이게 안되면 FORCE_INDEX를 쓰면된다.
• IGNORE INDEX
  특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트다. 때로는 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 IGNORE_INDEX 힌트를 사용할 수도 있다.

위의 인덱스는 인덱스의 용도를 설정 해줄 수 있다.

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

SELECT * FROM EMPLOYEE WHERE EMP_NO = 1000;

SELECT * FROM EMPLOYEE FORCE INDEX(primary) WHERE EMP_NO = 1000;

SELECT * FROM EMPLOYEE USE INDEX(primary) WHERE EMP_NO = 1000;

SELECT * FROM EMPLOYEE IGNORE INDEX(primary) WHERE EMP_NO = 1000;

SELECT * FROM EMPLOYEE FORCE INDEX(ix_firstname) WHERE EMP_NO = 1000;

 

SQL_CALC_FOUND_ROWS

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

하지만 해당 힌트를 사용하면 limit을 만족하는 수만큼 레코드를 찾았다고 하더라도 끝까지 검색을 수행한다. 주로 웹 프로그램에서 페이징 기능을 이용하기 위해 사용하는 경우가 간혹 있을수 있다.

SELECT SQL_CALC_FOUND_ROWS * 
  FROM EMPLOYEE E
 WHERE E.FIRST_NAME = 'AAA'
 LIMIT 0, 20

SQL_CALC_FOUND_ROWS 힌트는 사실상 장점보다는 단점이 더 많은 힌트기 때문에 해당 힌트는 사용하지 않는것을 추천한다.

https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

MySQL :: MySQL 5.7 Reference Manual :: 8.9.4 Index Hints

 

 

옵티마이저 힌트

옵티마이저 힌트는 다음 범위 레벨에서 적용 된다.

• GLOBAL : 전체 쿼리 블록에 영향을 미치는 힌트
• QUERY BLOCK : 특정 쿼리 블록에 사용할 수 있는 힌트로서 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 힌트
• TABLE-LEVEL : 특정 테이블의 이름을 사용할 수 있는 힌트
• INDEX-LEVEL : 특정 인덱스의 이름을 사용할 수 있는 힌트

 

Mysql 5.7 버전의 힌트

 

Mysql 6.0 버전의 힌트

 

옵티마이저 힌트 작성 법

옵티마이저 힌트구문은 오라클에서 쓰던것 처럼 쓰면 될듯하다.

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */


-- 쿼리 시작 부분
SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...


-- 쿼리 블록 부분
(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...

 

NO_ICP

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

따라서 ICP힌트는 제공하지 않는다. 하지만 ICP를 통해 실행꼐획의 비용계산이 잘못 된다면 결과적으로 잘못된 실행계획을 수립하게 될 수도 있다.

SELECT /*+ NO_ICP(E IX_LAST_NAME) */ *
  FROM EMPLOYEE E
 WHERE LAST_NAME = 'AAA'

 

BNL, NO_BNL

블록 네스티드 루프 조인을 사용하게 하는 힌트

SELECT /*+ BNL(E, D) */ *
  FROM EMPLOYEES E
 INNER JOIN DEPT_EMP D 
 ON D.DEPT_NO = E.DEPT_NO

 

SEMIJOIN, NO_SEMIJOIN

세미조인의 최적화는 여러가지 세부 전략이 있다.

• Duplicate Weed-out : SEMIJOIN(DUPSWEDOUT)
• First Match : SEMIJOIN(FIRSTMATCH)
• Loose Scan : SEMIJOIN(LOOSESCAN)
• Materialization : SEMIJOIN(MATERIALIZATION)
• Table Pull-out : 없음

SELECT *
  FROM DEPARTMENT D
 WHERE D.DEPT_NO IN (
                      SELECT /*+ SEMIJOIN(MATERIALIZATION) */SQ.DEPT_NO
                        FROM DEPT_EMP SQ
                    )
;

SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */
       *
  FROM DEPARTMENT D
 WHERE D.DEPT_NO IN (
                      SELECT /*+ QB_NAME(subq1) */SQ.DEPT_NO
                        FROM DEPT_EMP SQ
                    )
;

특정 세미조인을 사용하지 않기 위해서는 NO_SEMIJOIN 힌트를 사용할 수 있다.

SELECT *
  FROM DEPARTMENT D
 WHERE D.DEPT_NO IN (
                      SELECT /*+ NO_SEMIJOIN(DUPSWEEDOUTR, FRISTMATCH) */SQ.DEPT_NO
                        FROM DEPT_EMP SQ
                    )
;

 

MAX_EXCUTION_TIME

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

SELECT /*+ MAX_EXCUTION_TIME(100) */ *
  FROM EMPLOYEE E
ORDER BY LAST_NAME 
 LIMIT 1

 

SUBQUERY

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

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

서브쿼리 최적화는 사용할 기회가 그다지 많지 않다.

 

옵티마이저 힌트 참고 자료

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

MySQL :: MySQL 5.7 Reference Manual :: 8.9.3 Optimizer Hints