: 인덱스 힌트, 옵티마이저 힌트 등이 존재
1. 인덱스 힌트
: STRAIGHT_JOIN, USE_INDEX 등을 포함한 인덱스 힌트는 옵티마이저 힌트가 도입되기 전에 사용되던 기능들로 SELECT와 UPDATE 명령에서만 사용할 수 있음
1.
STRAIGHT_JOIN
: STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드이기도 하며 STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우, 조인 순서를 고정하는 역할
: 다음 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙 테이블이 되고 어느 테이블이 드리븐 테이블이 될지 알 수 없다.
SELECT
*
FROM
t1, t2, t3
WHERE
...
SQL
복사
: 조인을 하기 위한 칼럼들의 인덱스 여부로 조인의 순서가 결정되며, 그 중 레코드가 적은 테이블을 드라이빙 테이블로 선택한다.
: STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도
SELECT
straight_join
FROM
t1, t2, t3
WHERE
...
SQL
복사
: STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음과 같은 것들이 존재
•
JOIN_FIXED_ORDER
•
JOIN_ORDER
•
JOIN_PREFIX
•
JOIN_SUFFIX
2.
USE INDEX / FORCE INDEX / IGNORE INDEX
: 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 함
: 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우에 가끔 옵티마이저가 실수 할 수 있다. 이런 경우 강제로 특정 인덱스를 사용하도록 힌트를 추가
•
USE INDEX : 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
•
FORCE INDEX : USE INDEX와 비슷하지만 더 강하게 사용하도록 요구하는 힌트 (거의 사용하지 않음)
•
IGNORE INDEX : 반대로 특정 인덱스를 사용하지 못하게 하는 힌트
: 인덱스 용도 명시 : 특정 용도로 사용할 수 있게 제한
•
USE INDEX FOR JOIN : JOIN 키워드는 JOIN과 레코드 검색까지 포함
•
USE INDEX FOR ORDER BY
•
USE INDEX FOR GROUP BY
3.
SQL_CALC_FOUND_ROWS
: MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많더라도 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색을 멈춤, 하지만 SQL_CALC_FOUND_ROWS 힌트가 포함된
쿼리는 LIMIT을 만족하는 수만큼 레코드를 찾았더라도 끝까지 검색을 수행한다.
•
추가적으로 FOUND_ROWS() 함수를 이용해 전체 몇 건이었는지 알 수 있음
SELECT SQL_CALC_FOUND_ROWS
*
FROM
employees
LIMIT
5;
SELECT found_rows() AS total_record_count;
SQL
복사
2. 옵티마이저 힌트
1.
옵티마이저 힌트 종류
•
인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
•
테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
•
쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
•
글로벌(쿼리 전체): 전체 쿼리에 대해서 영향을 미치는 힌트
: 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수도 있지만 외부 쿼리 블록에서 사용할 수도 있음, 이처럼 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 QB_NAME()
힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 함
2.
MAX_EXECUTION_TIME
: 옵티마이저 힌트 중 유일하게 실행 계획에 영향을 미치지 않는 힌트로, 단순히 쿼리의 최대 실행 시간을 설정하는 힌트다. 지정된 시간을 초과하게 되면 쿼리는 실패하게 됨
3.
SET_VAR
: SET_VAR 힌트는 실행 계획을 바꾸는 용도뿐만 아니라 조인 버퍼나 정렬용 버퍼의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로 사용할 수 있다.
EXPLAIN
SELECT
/*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ *
FROM
employees
WHERE
first_name = 'Georgi'
AND
emp_no
BETWEEN
10000 AND 20000;
SQL
복사
4.
SEMIJOIN & NO_SEMIJOIN
: SEMIJOIN 힌트는 세미 조인 최적화의 어떤 세부 전략을 사용할지 제어하는데 사용할 수 있다. 세미 조인의 최적화 전략은 다음과 같다.
•
Duplicate Weed-out
•
First Match
•
Loose Scan
•
Materialization
•
Table Pull-out
: 이때 SEMIJOIN 힌트의 경우 SEMIJOIN(최적화 전략)의 형태로 사용할 수 있다.
: Table Pull-out의 경우 별도의 힌트를 사용할 수 없는데, Table Pull-out 전략을 사용하는 경우 항상 더 나은 성능을 보장하기 때문이다.
: 세미 조인 힌트는 서브 쿼리에 명시하거나 서브 쿼리에 쿼리 블록 이름을 정의하고 외부 쿼리 블록에 명시해야 한다.
-- 서브쿼리에 세미조인 힌트 명시
EXPLAIN
SELECT
*
FROM
departments d
WHERE
d.dept_no
IN
(
SELECT
/*+ SEMIJOIN(MATERIALIZATION) */ de.dept_no
FROM
dept_emp de
);
// 쿼리 블록 이름 정의 및 외부 쿼리 블록에 세미조인 힌트 명시
EXPLAIN
SELECT
/*+ SEMIJOIN(@subq1 MATERIALIZATION) */ *
FROM
departments d
WHERE
d.dept_no
IN
(
SELECT
/*+ QB_NAME(subq1) */ de.dept_no
FROM
dept_emp de
);
SQL
복사
5.
JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX
: MySQL 서버는 조인의 순서를 결정하기 위해 STRAIGHT_JOIN 힌트를 사용, 하지만 이는 쿼리 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움이 존재하며 또한 일부
조인 순서를 강제하고 나머지는 옵티마이저에게 맡기는 것도 불가능
: 이 단점을 보완하기 위해 옵티마이저 힌트에서는 다음과 같이 4개의 힌트를 제공
•
JOIN_FIXED_ORDER
◦
FROM 절의 테이블 순서대로 조인을 실행
•
JOIN_ORDER
◦
FROM 절에 사용된 테이블 순서가 아니라 힌트에 명시된 테이블 순서대로 조인 실행
•
JOIN_PREFIX
◦
조인에서 드라이빙 테이블만 강제
•
JOIN_SUFFIX
◦
조인에서 드리븐 테이블만 강제
//FROM 절에 나열된 테이블의 순서대로 조인 실행
SELECT/*+ JOIN_FIXED_ORDER() */ *FROM employees e
INNERJOIN dept_emp deON de.emp_no = e.emp_no
INNERJOIN departments dON d.dept_no = de.dept_no;
// 일부 테이블에 대해서만 조인 순서를 나열
SELECT/*+ JOIN_ORDER(d, de) */ *FROM employees e
INNERJOIN dept_emp deON de.emp_no = e.emp_no
INNERJOIN departments dON d.dept_no = de.dept_no;
// 조인의 드라이빙 테이블에 대해서만 조인 순서를 나열
SELECT/*+ JOIN_PREFIX(e, de) */ *FROM employees e
INNERJOIN dept_emp deON de.emp_no = e.emp_no
INNERJOIN departments dON d.dept_no = de.dept_no;
// 조인의 드리븐 테이블에 대해서만 조인 순서를 나열
SELECT/*+ JOIN_SUFFIX(de, e) */ *FROM employees e
INNERJOIN dept_emp deON de.emp_no = e.emp_no
INNERJOIN departments dON d.dept_no = de.dept_no;
SQL
복사
6.
MERGE & NO_MERGE
: 이전 MySQL 서버에서는 FROM 절에 사용된 서브 쿼리를 항상 내부 임시 테이블로 생성해 불필요한 자원을 소모, 따라서 MySQL 5.7 이상 버전에서는 임시 테이블을 사용하지 않게 FROM 절의 서브 쿼리를
외부 쿼리와 병합하는 최적화를 도입
: 때로는 임시 테이블을 생성하는 것이 나은 선택이 될 수도 있기 때문에 옵티마이저가 최적의 방법을 선택하지 못했을 때는 MERGE 또는 NO_MERGE 옵티마이저 힌트를 사용하면 된다.
// 외부 쿼리와 병합
EXPLAIN
SELECT/*+ MERGE(sub)*/ *FROM (SELECT *FROM employees
WHERE first_name='Matt1') sub LIMIT 10;
// 임시 테이블 사용
EXPLAIN
SELECT/*+ N0_MERGE(sub)*/ *FROM (SELECT *FROM employees
WHERE first_name='Matt') sub LIMIT 10;
SQL
복사
7.
INDEX_MERGE & NO_INDEX_MERGE
: MySQL 서버는 가능하면 테이블 당 하나의 인덱스만을 이용해 쿼리를 처리하려고 한다. 이때 인덱스를 통해 검색된 레코드의 교집합 또는 합집합만을 구해 결과를 반환하고 하나의 테이블에 대해 여러 개의
인덱스를 동시에 사용하는 것을 인덱스 머지
: INDEX_MERGE와 NO_INDEX_MERGE는 인덱스 머지 실행 계획 사용 여부를 제어하고자 할 때 사용
EXPLAIN
SELECT */*+ INDEX_MERGE(employees ix_firstname, PRIMARY) */ *FROM employees
WHERE first_name='Georgi'AND emp_noBETWEEN 10000AND 20000
EXPLAIN
SELECT/*+ NO_INDEX_MERGE(employees PRIMARY) */ *FROM employees
WHERE first_name='Georgi'AND emp_noBETWEEN 10000AND 20000;
SQL
복사
8.
NO_ICP
: 인덱스 컨디션 푸시다운 최적화는 항상 성능 향상에 도움이 되므로 옵티마이저는 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립한다. 따라서 MySQL 옵티마이저에서는
ICP(Index Condition Pushdown) 힌트는 제공하지 않고 인덱스 컨디션 푸시다운으로 인한 잘못된 실행 계획 수립 시 인덱스 컨디션 푸시다운 최적화를 비활성화하는 힌트인 NO_ICP만을 제공한다.
EXPLAIN
SELECT/*+ NO_ICP(employees ix_lastname_firstname) */ *FROM employees
WHERE last_name='Acton'AND first_nameLIKE '%sar';
SQL
복사
9.
SKIP_SCAN & NO_SKIP_SCAN
: 인덱스 스킵 스캔은 인덱스의 선행 칼럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스를 사용할 수 있게 해주는 최적화 기능으로 허나 조건이 누락된 선행 컬럼의 유니크 값의 개수가 많아진다면
오히려 성능이 떨어지므로 옵티마이저가 비효율적인 인덱스 스킵 스캔을 선택한 경우 NO_SKIP_SCAN 힌트로 이를 제어할 수 있다.
// 인덱스 스킵 스캔 비활성화
EXPLAIN
SELECT/*+ NO_SKIP_SCAN(employees ix_gender_birthdate) */ gender, birth_date
FROM employees
WHERE birth_date>='1965-02-01';
SQL
복사
10.
INDEX & NO_INDEX
: 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
// 인덱스 힌트 사용
EXPLAIN
SELECT *FROM employees USE INDEX(ix_firstname)
WHERE first_name='Matt';
// 옵티마이저 힌트 사용
EXPLAIN
SELECT/*+ INDEX(employees ix_firstname) */ *FROM employees
WHERE first_name='Matt';
SQL
복사
