Real Mysql 8.0 : Hint

Hint

언제 사용하는가?
옵티마이저가 우리가 사용하는 서비스의 비즈니스를 잘 이해하지 못하고 실행 계획을 수립할 때 사용

MySQL 5.6이전에는 Index Hint를 사용, 이후부터는 Optimizer Hint 사용
Index Hint는 SQL 문법에 맞게 사용해야해서 ANSI-SQL 표준 문법을 준수하지 못하나
Optiomizer Hint는 ANSI-SQL을 준수하는 다른 RDBMS처럼 주석으로 사용

MySQL에서 사용 가능한 힌트

• Index Hint
• Optimizer Hint

---

Index Hint

🐰 STRAIGHT_JOIN

Join 순서를 명시해도 옵티마이저에 의해 join 순서가 변경이 됩니다.
드라이빙, 드리븐 테이블을 예상할 수 없으며 통계정보와 쿼리 조건을 기반으로 분석한 옵티마이저의 최적화 결과가 매우 좋지 않다면 사용합니다.

SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 순서를 고정합니다.

STRAIGHT_JOIN을 언제 사용할까?

• 임시 테이블과 일반 테이블 조인
  임시 테이블을 드라이빙 테이블로 사용

• 임시 테이블끼리 조인(서브쿼리로 파생된 테이블)
  임시 테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 읽어도 무관,
  레코드가 작은 테이블을 드라이빙으로 선택하기

• 일반 테이블끼리 조인
  양쪽 테이블 모두 조인 컬럼에 인덱스가 있거나 혹은 둘다 없으면 건수가 적은 테이블을 드라이빙으로 선택
  그외의 경우 인덱스가 없는 테이블을 드라이빙으로 선택

읽어야 할 레코드가 작은 것은
🙆‍♂️모든 조건에 부합하는 레코드를 의미
❌조건이 없는 모든 레코드를 의미하는 것이 아님

Join에 왜 임시 테이블을 생성하지?
자문자답 - on절의 컬럼에 인덱스가 없는 경우 생성할 것 같다.
💬 왜? 학습 필요

🐰 USE INDEX / FORCE INDEX / IGNORE INDEX

조인의 순서 다음으로 많이 사용하는 힌트이며
대체로 MySQL 옵티마이저는 인덱스 선택을 잘 하는편이지만 3~4개 이상의 컬럼을 포함하는 비슷 한 인덱스가 여러 개 존재하는 경우 실수를 합니다.

사용 예제

SELECT * FROM employees FORCE INDEX(primary) WHERE emp.no = 10001;

USE INDEX

• 가장 자주 사용
• 옵티마이저에게 인덱스 사용을 권장하는 힌트, 대부분의 경우 힌트 인덱스를 사용하지만 늘 사용하는 것은 아님

FORCE INDEX

• USE INDEX 보다 옵티마이저에게 미치는 영향이 더 강함
• USE INDEX만으로도 충분히 영향력이 크기 때문에 거의 사용이 안된다.

IGNORE INDEX

• 이름 그대로 인덱스 사용을 못하게하는 용도
• 풀 테이블 스캔을 유도하기 위해 사용할 수 있음

위의 힌트를 FOR JOIN, FOR INDEX FOR ORDER BY, USE INDEX FOR GROUP BY를 사용하여 용도를 지정할 수 있음
하지만 용도는 옵티마이저가 대부분 최적으로 선택

특징

• 위의 사용 예제 처럼 primaryKey를 사용 하 것이 최적이지만 이상한 IGORE INDEX(primary) 같은 힌트도 과거 5버전에서는 먹혔습니다.

• 전문 검색이나 Primary Key같은 인덱스는 선택 시 가중치를 두고 실행 계획을 수립

• 인덱스 사용법이나 좋은 실행 계획을 판단하기 힘든 상황이면 힌트를 사용하지 말자.

• 오늘 좋은 인덱스도 내일에는 안 좋을 수 있다. 옵티마이저는 통계를 보고 인덱스를 선택합니다.

🐰 SQL_CALC_FOUND_ROWS

페이징 쿼리에서 사용되는 기능

• LIMIT을 사용하는 경우 조건절에 부합하는 레코드가 LIMIT보다 많더라도 LIMIT조건에서 검색을 종료.
• SQL_CALC_FOUND_ROWS를 사용하는 경우 LIMIT조건에 부합하는 만큼 찾더라도 멈추지 않고 끝까지 검색을 수행
• FOUND_ROWS()를 사용하는 경우 SQL_CALC_FOUND_ROWS가 사용된 쿼리의 LIMIT을 제외한 조건을 만족하는 레코드의 수를 찾음

• 성능 향상이 아니라 개발자의 편의를 위해 만들어진 힌트

일반적인 경우 SQL_CACL_FOUND_ROWS보다는 일반 페이징 쿼리가 빠르다. 왜?

📗SQL_CACL_FOUND_ROWS

일단 SQL_CALC_FOUND_ROWS는 LIMIT이상의 조건에 부합하는 모든 레코드를 읽어야 함.
실제 조회 데이터를 위해 조건에 부합하는 레코드가 100개라면 100번 랜덤 I/O가 일어남

📗2개로 나누어 실행

100개의 레코드에서 실제 조회 데이터를 위해 조건에 부합하는 10개만 보여준다면 10번의 랜덤I/O가 생김

count 쿼리는 둘 다 커버링 인덱스를 활용할 것으로 예상
페이징 쿼리에서 특정 조건에 부합하는 레코드(Index가 걸린!)의 총 개수를 읽는 경우 Secondary Index Table의 개수만으로 결과를 만들어짐 (커버링 인덱스 활용), 실제 레코드를 찾는 랜덤I/O가 발생 X

---

Optimizer Hint

종류
인덱스, 테이블, 쿼리 블록. 글로벌

예시

SELECT /*+ JOIN_ORDER(e, s@subq1) */
COUNT(*)
FROM employees e
WHERE e.first_name='Matt'
AND e.emp_no IN (SELECT / *+ QB_NAME(subq1) */ s.emp_no
				 FROM salaries s
                 WHERE s.salary BETWEEN 50000 AND 50500);

사용한 옵티마이저 힌트
1. /+ JOIN_ORDER(e, s@subq1) /
2. /+ QB_NAME(subq1) /

QB_NAME은 쿼리 블록의 이름 설정을 위한 힌트이며 쿼리블록의 영향범위를 가집니다.
JOIN_ORDER는 힌트에 명시된 테이블 순서대로 조인을 실행하며 QB_NAME과 동일한 영향범위를 가집니다.

1. MAX_EXECUTION_TIME

• 힌트 중 유일하게 쿼리의 실행 계획에 영향을 미치지 않는 힌트
• 단순히 쿼리 최대 실행 식나을 설정하는 힌트

2. SET_VAR

• 옵티마이저 힌트뿐만 아니라 시스템 변수들 또한 쿼리의 실행 계획에 상당한 영향을 줌, join_buffer_size같은 경우 값을 변경하면 조인 버퍼를 활용하는 실행 계획을 선택할 수도 있음
• 모든 시스템 변수를 조정할 수는 없음

3. SEMIJOIN & NO_SEMIJOIN

• 세부 전략 사용을 제어하는 데 사용

4. SUBQUERY

IN-to-EXISTS , Meterialization 2가지 방법이 존재

• 서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법
• 서브쿼리 최적화 전략은 사용할 기회가 그다지 많지 않음

5. BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

• MySQL 8.0.20 버전 부터는 해시 조인 알고리즘이 사용된다.
  BNL, NO_BNL 힌트를 사용하면 해시 조인 알고리즘을 유도
• HASHJOIN, NO_HASHJOIN 힌트는 8.0.18버전에서만 유효
  그 이후에는 효력이 없다.

해시 조인 알고리즘을 사용하려면 조인 조건에 인덱스를 사용하지 못하게 해야 함

6. JOIN_FIXED & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

• 기존에는 STRAIGHT_JOIN을 사용했으나 조인 순서에 맞게 테이블의 순서를 변경해야하는 단점이 존재, 일부만 강제시키고 나머지는 옵티마이저에게 순서를 맞기는 방식이 불가능 하다.
• STRAIGHT_JOIN의 단점을 보강하기 위해 나온 힌트

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

7. MERGE & NO_MERGE

• 예전 버전에서 FROM 절의 서브쿼리는 항상 내부 임시 테이블을 생성(파생 테이블)
• 5.7, 8.0버전에서는 가능하면 임시 테이블을 사용하지 않게 FROM절의 서브쿼리를 외부 쿼리와 병합하는 최적화를 도입
• 임시 테이블을 사용하는 것, 외부 쿼리와 병합하는 것 때에 따라서 나은 선택은 다르다.
  서브쿼리 방식을 강제로 지정하기 위해 사용

8. INDEX_MERGE & NO_INDEX_MERGE

• MySQL서버는 가능하다면 테이블당 하나의 인덱스만 이용해서 쿼리를 처리하려고 한다. 하지만 하나의 인덱스만으로 검색 대상 범위를 좁힐 수 없다면 다른 인덱스도 사용
• 여러 인덱스를 통해 검색된 레코드로부터 교집한, 합집합만을 구해서 결과를 반환
• 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용하는 것
• 때로는 성능 향상에 도움이 되지만 항상 그렇지는 않음

9. NO_ICP

• NO_ICP는 성능 향상에 도움이 되므로 옵티마이저는 최대한 사용하는 방향으로 계획을 수립, 그래서 ICP힌트는 제공되지 않음
• Extra에 Using index condition은 ICP(Index Condition Pushdown) 최적화를 선택한 것
• ICP로 인해 결과적으로 잘못된 실행 계획을 수립할 수도 있음

10. SKIP_SCAN & NO_SKIP_SCAN

• 인덱스의 선행 컬럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스를 사용하게 해주는 매우 훌륭한 최적화 기능
• 조건이 누락된 선행 컬럼이 가지는 유니크 값 개수가 많아지면 오히려 성능이 떨어짐
• 사용하면 Extra에 Using index for skip scan이 노출

11. INDEX & NO_INDEX

• 예전에 사용하던 인덱스 힌트는 대체
• 기능은 USE INDEX -> 해당 인덱스를 사용처럼 이름에서 직관적으로 유추할 수 있음
• USE INDEX, USE INDEX FOR GROUP BY, USE INDEX FOR ORDER BY, IGNORE INDEX, IGNORE INDEX FOR GROUP BY.. 존재
• 테이블 명과 인덱스 이름을 함께 명시