본문 바로가기
DB

Real MySQL 9장 옵티마이저와 힌트 #2 (9.3~)

seonghun 2022. 7. 10. 21:22

9.3 고급 최적화

MySQL 옵티마이저는 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립한다. 여기서 옵티마이저 옵션은 크게 조인과 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있다.

 

9.3.1 옵티마이저 스위치 옵션

  • 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어한다.
  • 9.3.1절 하위의 옵션들을 on, off, default로 설정하여 사용할 수 있다.
  • 9.3.1.1을 제외한 모든 설정 옵션은 기본값이 on이다.

9.3.1.1 MRR과 배치 키 액세스(mrr & batched_key_access)

  • MRR은 Multi Range Read를 줄여서 부르는 말이다. MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것이다. 그리고 조인 버퍼에 레코드가 가득 차면 그때 한 번에 스토리지 엔진으로 요청한다. 이렇게 하면 읽어야 할 레코드 들을 데이터 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화 할 수 있다. 이러한 읽기 방식을 MRR이라 하고 이를 응용해서 실행하는 조인 방식을 BKA라 한다.

9.3.1.2 블록 네스티드 루프 조인(block_nedsted_loop)

  • MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인이다.
  • 프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 Nested Loop Join이다.
    • 블록 네스티드 루프 조인은 조인 버퍼가 사용되는 것과 드라이빙 테이블, 드리븐 테이블의 처리 순서에 따라서 구분된다.
  • MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입되어 이 방식은 사용되지 않는다.

9.3.1.3 인덱스 컨디션 푸시다운(index_condition_pushdown)

Alter table employees add index ix_lastname_firstname (last_name, first_name);

select * from employees where last_name='Acton' and first_name like '%sal';
  • 위의 Select 쿼리에서 index_condition_pushdown을 off 로 설정하면 last_name을 기준으로 인덱스를 타고 first_name like '%sal' 조건은 레코드에 직접 접근해서 검사를 한다.
  • 그러나 index_condition_pushdown을 on으로 설정하면 index 내에서 비교를 수행하고 비교한 대상 레코드에만 접근해서 데이터를 가져올 수 있다.

9.3.1.4 인덱스 확장(use_index_extensions)

  • 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.
  • InnoDB 스토리지 엔진에서 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키를 포함한다.
  • 프라이머리 키가 a 이고 세컨더리 인덱스가 b 로 되어있으면 실제로 세컨더리 인덱스는 (b, a)로 작동한다.

9.3.1.5 인덱스 머지(index_merge)

  • 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
  • 하나의 인덱스를 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 하나의 인덱스만 활용하는 것이 효율적이다. 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상 될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
  • 인덱스 머지는 다음의 3개의 세부 실행 계획으로 나누어 볼 수 있다.
    • index_merge_intersection
    • index_merge_union
    • index_merge_sort_union

9.3.1.6 인덱스 머지 - 교집합(index_merge_intersection)

  • 이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합을 반환한다.
  • 실행계획의 Extra 칼럼에 Using intersect로 표시됨을 확인할 수 있다.
select * from employees where first_name='George' and emp_no between 10000 and 20000;
  • 위의 쿼리에서 두 조건에 대한 인덱스가 모두 존재하고 모두 상대적으로 많은 레코드를 가져와서 처리해야 한다면 인덱스를 각각 검색해서 교집합 하는 방식이 더 효율적일 수 있다. (물론 아닐 수도 있다.)

9.3.1.7 인덱스 머지 - 합집합(index_merge_union)

  • 이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 합집합을 반환한다.
  • 실행계획의 Extra 칼럼에 Using union로 표시됨을 확인할 수 있다.
select * from employees where first_name='Matt' or hire_date='2022-07-13';
  • 이 경우 인덱스 머지 방식을 사용할 때 중복을 제거해주어야 한다.
    • 각각의 인덱스로 조회 시에 emp_no(primary key)를 기준으로 정렬이 되어있으므로 인덱스 머지를 수행할 때 각 집합에서 하나씩 가져와 비교하며 죽복된 레코드를 걸러낼 수 있다.(우선순위 큐 알고리즘)

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

  • 위의 Union 알고리즘에서 정렬된 결과로 중복제거를 하는데 정렬이 이미 되어있으므로 필요하지 않았다.
  • 하지만 도중에 정렬이 필요한 경우에는 Sort union 알고리즘을 사용한다.

9.3.1.9 세미 조인

select *
from employees e
where e.emp_no in
( select de.emp_no from dept_emp where de.from_date= '1995-01-01' );
  • 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라 한다.
    • Table Pull-out
    • Duplicate Weed-out
    • First Match
    • Loose Scan
    • Materialization

9.3.1.10 테이블 풀-아웃(Table Pull-out)

  • 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화다.
    • IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리
  • 실행계획의 Extra 칼럼에 특별한 문구가 출력되지 않는다. 따라서, table pullout 최적화가 사용됐는지 확인하려면 Show warings 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 살펴보면 된다.
    • 이때, IN(subquery) 형태는 사라지고 join으로 재작성된 것을 확인할 수 있다.

9.3.1.11 퍼스트 매치(firstmatch)

  • IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
  • extra 칼럼에 firstmatch() 문구가 출력된다.

9.3.1.12 루스 스캔(loosescan)

  • Loose Index Scan과 비슷한 읽기 방식을 사용한다.

9.3.1.13 구체화(Materialization)

  • Mataerialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다.
    • 여기서 구체화(Materialization)는 쉽게 말해 내부 임시 테이블을 만드는 것을 의미한다.
  • optimizer_switch 시스템 변수에서 semijoin, materialization 옵션이 모두 on인 경우에 활성화 된다.(defualt)

9.3.1.14 중복 제거(Duplicated Weed-out)

  • 세미 조인 서브쿼리를 일반적인 Inner Join 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.
select * from employees e
where e.emp_no in (select s.emp_no from salaries s where s.salary > 150000);
  • 위의 쿼리는 다음과같이 바꿔서 실행된다.
select e.*
from employees e, salaries s
where e.emp_no = s.emp_no and s.salary>150000
group by e.emp_no;

9.3.1.15 컨디션 팬아웃(condition_fanout_filter)

  • 조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다. MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.
  • 실행계획에는 쿼리 실행시 읽게 될 rows의 갯수와 실행 결과 rows의 비율인 filtered 칼럼이 있다.
    • rows * filtered / 100 이 쿼리 실행 결과 나오게 될 rows 수이다.
  • 옵티마이저는 condition_fanout_filter 최적화 기능을 활성화하여 보다 정교한 계산을 할 수 있다.
    • WHERE 조건절에 사용된 칼럼에 인덱스가 있는 경우
    • WHERE 조건절에 사용된 칼럼에 히스토그램이 존재하는 경우

9.3.1.16 파생 테이블 머지(derived_merge)

  • MySQL 5.7버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐다.
  • derived_merge 최적화 옵션을 통해 이러한 최적화를 활성화할지 여부를 결정한다.
select * from (
	select * from employees where first_name='Matt'
) dervied_table
where derived_table.hire_date='2022-07-01'
  • 위의 쿼리 실행 계획을 보면 from 절에 사용된 서브쿼리를 파생 테이블이라 부른다. 이러한 임시 테이블이 외부 쿼리로 병합된 경우 show warnings 명령으로 옵티마이저가 작성한 쿼리를 보자.
select *from employees
where employees.hiredate = '2022-07-01' and employees.first_name = 'Matt'

9.3.1.17 인비저블 인덱스(use_invisible_indexes)

  • MySQL 8.0 버전부터는 인덱스의 가용 상태를 제어할 수 있는 기능이 추가됐다.
    • 이를 통해 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어할 수 있다.
ALTER TABLE ... ALTER INDEX ... [ VISIBLE | INVISIBILE ]

9.3.1.18 스킵 스캔(skip_scan)

  • 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다.
  • (A, B, C)로 구성된 인덱스가 있을 때 B와 C 칼럼에 대한 조건을 가지고 있다면 인덱스를 활용할 수 없다.
    • 인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이러한 제약을 해소하는 최적화 기법이다.
ALTER TABLE EMPLOYEES ADD INDEX IX_GENDER_BIRTHDATE (GENDER, BIRTH_DATE);

위의 인덱스에서 이 경우 select * from employees where birth_date >= '1992-01-11'; 쿼리를 실행할 때 인덱스를 사용할 수 있을까?

  • MySQL 8.0 버전 부터는 인덱스 스킵 스캔 최적화가 도입되어 후행 칼럼만으로 인덱스를 이용한 쿼리 성능 개선이 가능하다.
    • 단, 선행 칼럼이 소수의 유니크한 값을 가질 때

9.3.1.19 해시 조인(hash_join)

  • 많은 사용자들이 해시 조인 기능을 기대하는 이유가 기존의 네스티드 루프 조인보다 해시 조인이 빠르다고 생각해서이다. 하지만 항상 그렇지는 않다.
  • 해시 조인 쿼리는 최고 스루풋 전략(첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 많이 걸리지 않는다.)에 적합하고, 네스티드 루프 조인(첫 번째 레코드를 찾는 것은 빠르지만 마지막 레코드를 찾는 데에 시간이 많이 걸린다.)은 최고 응답 속도 전략에 적합하다. 해시 조인 절차
  • 빌드 단계 : 조인 대상 테이블 중에서 레코드 건수가 적은 테이블을 골라서 해시 테이블을 생성하는 작업 수행
  • 프로브 단계 : 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

  • MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행한다.
    • 옵티마이저의 이런 가중치 부여하지 않게 하기위해 prefer_ordering_index 옵션이 추가되었다. 이를 off 로 설정하면 된다.

9.3.2 조인 최적화 알고리즘

  • MySQL의 조인 쿼리의 실행 계획 최적화를 위한 알고리즘은 다음 두 가지이다.
  • 아래의 예제쿼리에 대해 각 알고리즘이 어떻게 접근하는지 살펴보자
select *
from t1, t2, t3, t4
where ...

9.3.2.1 Exhaustive 검색 알고리즘

  • MySQL 5.0 이전에 사용되던 조인 최적화 기법이다.
  • FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법
  • 테이블이 N개라면 N! 개의 조인 조합이 가능하다.

9.3.2.2 Greedy 검색 알고리즘

  • MySQL 5.0부터 도입된 조인 최적화 기법 

  • 절차
    1. 전체 N개의테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
    2. 1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
    3. 2번에서 선정된 실행 계획의 첫 테이블을 부분 실행 계획의 첫 번째 테이블로 선정
    4. 전체 N-1개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
    5. 4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 부분 실행 계획에 대입해 실행 비용을 계산
    6. 5번의 비용 계산 결과 최적의 실행 계획에서 두 번째 테이블을 3번에서 새성된 부분 실행 계획의 두 번째 테이블로 선정
    7. 남은 테이블이 모두 없어질 때까지 4~6번까지의 과정을 반복
    8. 최종적으로 부분 실행 계획이 테이블의 조인 순서로 결정됨
  • 시스템 변수
    • optimizer_search_depth : Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정
    • optimizer_prune_level : Heuristic 검색이 작동하는 방식 제어

9.4 쿼리 힌트

  • MySQL 서버는 우리가 서비스하는 비즈니스를 100% 이해하지 못하기 때문에 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요하다.
  • MySQL 서버에서 사용 가능한 쿼리 힌트는 다음의 두 가지로 분류할 수 있다.
    • 인덱스 힌트
    • 옵티마이저 힌트

9.4.1 인덱스 힌트

  • MySQL 인덱스 힌트들은 ANSI-SQL 표준 묹법을 준수하지 못한다.
    • MySQL 5.6 버전부터 추가되기 시작한 옵티마이저 힌트들은 ANSI-SQL 표준을 준수하여 다른 RDBMS에서는 주석으로 해석된다.
    • 따라서 가능하면 인덱스 힌트보다는 옵티마이저 힌트를 사요할 것을 추천한다.
  • 인덱스 힌트는 SELECT 명령과 UPDATE 명령에서만 사용 가능하다.

9.4.1.1 STRAIGHT_JOIN

  • 다음 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙 테이블이 되고 어느 테이블이 드리븐 테이블이 될지 알 수 없다.
select *
from t1, t2, t3
where ...
  • 조인을 하기 위한 칼럼들의 인덱스 여부로 조인의 순서가 결정되며, 그 중 레코드가 적은 테이블을 드라이빙 테이블로 선택한다.
  • STRAIGHT_JOIN 힌트는 옵티마이저가 from 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.
select straight_join
from t1, t2, t3
where ...
-- or
select /*! straight_join */
from t1, t2, t3
where ...
  • STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음과 같은 것들이 있다.
    • JOIN_FIXED_ORDER
    • JOIN_ORDER
    • JOIN_PREFIX
    • JOIN_SUFFIX

9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

  • 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.
  • 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우에 가끔 옵티마이저가 실수 할 수 있다. 이런 경우 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.
  • 인덱스 힌트는 크게 다음 3종류가 있다.
    • USE INDEX : 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
    • FORCE INDEX : USE INDEX 와 비슷하지만 더 강하게 사용하도록 요구하는 힌트 (거의 사용하지 않음)
    • IGNORE INDEX : 반대로 특정 인덱스를 사용하지 못하게 하는 힌트
  • 인덱스 용도 명시 : 특정 용도로 사용할 수 있게 제한한다.
    • USE INDEX FOR JOIN : JOIN 키워드는 JOIN과 레코드 검색까지 포함한다.
    • USE INDEX FOR ORDER BY
    • USE INDEX FOR GROUP BY

9.4.1.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;
  • 이 힌트는 사용하면 안 된다!
select SQL_CALC_FOUND_ROWS * from employees where first_name='Georgi' limit 0, 20;

select found_rows() as total_record_count;
  • 위의 경우 LIMIT 조건으로 20건만 가져오도록 했지만 SQL_CALC_FOUND_ROWS 힌트 때문에 전체 레코드를 전부 읽어야 한다. (랜덤 I/O 발생)
  • 다음의 쿼리 2번을 수행하면 전체 건수는 실제 레코드를 찾는 랜덤 I/O가 아닌 커버링 인덱스쿼리로 가져올 수 있다.
select count(*) from employees where first_name = 'Georgi';
select * from employees where first_name='Georgi' limit 0, 20;

9.4.2 옵티마이저 힌트

9.4.2.1 옵티마이저 힌트 종류

  • 옵티마이저 힌트는 영향 범위에 따라 다음 4개 그룹으로 나누어 볼 수 있다.
    • 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
      • 모든 인덱스 수준의 힌트는 반드시 테이블 명이 선행돼야 한다.
    • 테이블 : 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
    • 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트 (힌트가 명시된 쿼리 블록에 대해서만)
    • 글로벌 : 전체 쿼리에 대해서 영향을 미치는 힌트
  • 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수도 있지만 외부 쿼리 블록에서 사용할 수도 있다. 이처럼 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 QB_NAME() 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 한다.
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);

9.4.2.2 MAX_EXECUTION_TIME

  • 단순히 쿼리의 최대 실행 시간을 설정하는 힌트이다.
    • 밀리초 단위의 시간을 설정
    • 지정된 시간을 초과하면 ERROR 3024 : Query execution was interrupted, maximum statement execution time exceeded 발생

9.4.2.3 SET_VAR

  • MySQL 서버의 시스템 변수들 또한 쿼리의 실행 계획에 상당한 영향을 미친다.
  • SET_VAR 힌트는 실행 계획을 바꾸는 용도뿐만 아니라 조인 버퍼나 정렬용 버퍼의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로도 사용할 수 있다.

9.4.2.4 SEMIJOIN & NO_SEMIJOIN (9.3.1.9절 세미 조인 참조)

  • Duplicate weed-out : SEMIJOIN(DUPSWEEDOUT)
  • first match : SEMIJOIN(FIRSTMATCH)
  • loose scan : SEMIJOIN(LOOSESCAN)
  • materialization : SEMIJOIN(MATERIALIZATION)
  • table pull-out : 없음
  • 세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브쿼리에 명시해야 한다.

9.4.2.5 SUBQUERY

  • 서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법이다.
  • 다음 2가지 형태로 최적화할 수 있다.
    • IN-to-EXISTS : SUBQUERY(INTOEXISTS)
    • materialization : SUBQUERY(materialization)
  • 세미 조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만 안티 세미 조인의 최적화에는 사용될 수 없다. 그래서 주로 안티 세미 조인 최적화에는 위의 2가지 최적화가 사용된다.

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

  • 8.0.19 버전까지는 블록 네스티드 루프 조인 알고리즘이 사용되었지만 그 후부터는 해시 조인 알고리즘으로 대체되었다.
  • 그럼에도 BNL, NO_BNL 힌트로 여전히 사용 가능하다.

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

  • MySQL 서버에서는 조인의 순서를 결정하기 위해 전통적으로 STRAIGHT_JOIN 힌트를 사용해왔다.
    • 하지만 STRAIGHT_JOIN 힌트는 우선 쿼리의 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움이있다.
    • 또한 한 번 사용되면 FROM절에 명시된 모든 테이블의 조인 순서가 결정되기 때문에 일부는 조인 순서를 강제하고 일부는 옵티마이저에게 순서를 결정하게 맡기는 것이 불가능했다.
  • 이를 보완하기 위해 옵티마이저 힌트에서는 다음 4개의 힌트를 제공한다.
    • JOIN_FIXED_ORDER : STRAIGHT_JOIN 힌트와 동일
    • JOIN_ORDER : FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 수행
    • JOIN_PREFIX : 드라이빙 테이블만 강제
    • JOIN_SUFFIX : 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들)만 강제

9.4.2.8 MERGE & NO_MERGE

  • FROM 절에 사용된 서브쿼리를 내부 임시 테이블로 생성할지 외부 쿼리와 병합하는 최적화를 수행할지를 정하는 힌트

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

  • 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용하는 것을 인덱스 머지(INDEX_MERGE)라 한다.
    • MySQL 서버는 가능하면 테이블당 하나의 인덱스 만을 이용해 쿼리를 처리하려고 한다. 하지만 검색 대상을 충분히 좁힐 수 없다면 여러 인덱스를 통해 검색된 레코드로부터 교집합 또는 합집합을 구해서 그 결과를 반환한다.

9.4.2.10 NO_ICP

  • 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 최적화는 항상 성능향상에 도움되므로 옵티마이저는 항상 사용하는 방향으로 실행 계획을 수립한다.
    • 따라서 ICP 힌트는 제공되지 않는다.
  • 실행 계획 비용 계산이 잘못 되었을 경우 이를 비활성화 하여 유연하게 실행 계획을 선택하여 비용 계산을 하게 할 수 있다.(??)

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

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

9.4.2.12 INDEX & NO_INDEX

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

  • 인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열했지만 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 한다.
-- // 인덱스 힌트 사용
select *
from employees USE INDEX(ix_firstname)
where first_name='Matt';

-- // 옵티마이저 힌트 사용
select /*+ INDEX(employees ix_firstname) */ *
from employees
where first_name='Matt';

'DB' 카테고리의 다른 글

Real MySQL 10장 실행계획  (0) 2022.07.27
Real MySQL 9장 옵티마이저와 힌트 #1 (~9.2)  (0) 2022.06.21
Real MySQL 8장 인덱스 #2 (8.6~)  (0) 2022.06.10
커넥션풀과 데이터소스 이해  (0) 2022.06.05
JDBC란? (SQL Mapper, ORM)  (0) 2022.06.03

'DB' Related Articles

티스토리툴바