Optimizer

MySQL 서버로 요청된 쿼리는 더 효율적인 방법으로 처리하려고 노력한다. 주로 어떤 방법이 최적이고 최소 비용이 소모될지 결정하는 식으로 산출해낸다.

쿼리 실행 절차

1. 사용자로부터 요청된 SQL을 토큰 단위로 잘라서 분리한다 - ParseTree
2. SQL의 파싱 정보를 확인하면어 어떤 테이블을 읽고, 어떤 인덱스를 이용해서 테이블을 읽을지 선택한다.
3. 결정된 테이블을 읽어서 스토리지 엔진으로부터 데이터를 가져온다.

1. 파싱

SQL 파서 모듈로 처리한다. 문법적 에러는 여기서 걸러진다. 여기서 ParseTree가 만들어진다.

2. 최적화 및 실행 계획 수립( 옵티마이저에서 처리 )

• 불필요한 조건 제거 및 복잡한 연산의 단순화
• 여러 테이블에 조인이 있다면 어떤 순서로 테이블을 읽을지 결정
• 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
• 가져온 레코드들을 임시 테이블에 넣고 다시 한 번 가공해야 하는지 결정

옵티마이저의 종류

대부분의 DBMS는 CBO(Cost-based optimizer)를 채택했다. 초기에는 RBO(Rule-base optimizer)을 사용했다.

RBO

테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선 순위에 따라 실행 계획을 수립하는 방식을 의미한다. 같은 쿼리를 던지면 거의 항상 같은 실행 방법을 만들어낸다.

CBO

쿼리를 처리하기 위한 여러 가지 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해서 실행 계획별 비용을 산출한다.

기본 데이터 처리

풀 테이블 스캔, 풀 인덱스 스캔

옵티마이저는 아래의 조건일 때 주로 풀 테이블 스캔(전체 검색)을 선택한다.

1. 테이블의 레코드 건수가 너무 작아서 인덱스를 읽는 것보다 효율적인 경우
2. WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
3. 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

InnoDB는 특정 테이블의 연속된 페이지를 읽으면 백그라운드 쓰레드에 의해서 리드 어헤드(Read ahead) 작업이 자동으로 시작되어 미리 필요한 데이터를 읽어서 버퍼 풀에 가져다 둔다. 즉, 처음 몇 개만 foreGroundThread가 읽기를 처리하고 나머지는 그 동안 backgroundThread가 읽어와서 적재한 데이터를 가져다 쓴다. 이는 innodb_read_ahead_threshold로 변경할 수 있으며, 인덱스 풀 스캔에서도 동일하게 작동된다.

ORDER BY(Using filesort)

정렬을 처리하는 방법은 인덱스를 사용하거나 Filesort하는 방법으로 나눌 수 있다.

레코드 정렬은 filesort, index 둘 다 가능하다. 인덱스를 이용하는게 웬만한 경우에 더 유리하다. 그러나 항상 인덱스를 태워서 정렬하기는 어렵다.

1. 정렬 기준이 많아서 모두 인덱스로 생성하는 것은 어렵다.
2. GROUP BY 결과 또는 DISTINCT 같은 처리 결과를 정렬해야 하는 경우
3. UNION 결과와 같이 임시테이블 결과를 다시 정렬해야 하는 경우
4. 랜덤하게 결과 레코드를 가져와야 하는 경우

SortBuffer

MySQL은 별도 메모리 공간을 할당받아서 사용하는데 이 메모리 공간을 소트 버퍼라고 한다. 소트 버퍼는 정렬이 필요한 경우 할당된다. 레코드 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다.

만일 소트 버퍼 할당 크기보다 정렬해야하는 레코드가 더 크면 여러 조각으로 나눠서 처리하는데, 임시 저장을 위해서 디스크를 사용한다. 정렬하고 디스크에 임시 기록하고 이 과정을 반복한다. 여기서 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하고 정렬을 수행해야 한다. 이 병합 작업을 multi-merge라고 표현한다. 이 작업들이 모두 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아진다.

여기서 sort_buffer_size가 크면 좋지 않을까하는 의문을 가질 수 있지만 sortBuffer는 세션 메모리 영역에 해당된다. 즉, 클라이언트가 공유해서 사용할 수 있는 영역이 아니다. 커넥션이 많거나, 정렬 작업이 많으면 소트 버퍼로 소비되는 메모리 공간티 커진다. 그래서 linux에서는 OOM Killer가 강제 종료를 감행할 수도 있다.

정렬 알고리즘

정렬 알고리즘은 Single-pass, Two-pass로 나뉠 수 있다. Single-pass는 레코드를 통으로 가져와 정렬하는 방식이다. Two-pass는 PK만 읽어서 정렬하고 정렬된 순서대로 다시 테이블을 읽어오는 방식이다.

Single-pass는 두 번 읽을 필요가 없지만 정렬 시 더 많은 버퍼가 필요하다. 반대로 Two-pass는 두 번 읽지만 소트 버퍼 가용성이 높다.

1. 레코드 크기가 max_length_for_sort_data보다 클 때
2. Blob, Text 컬럽이 select 될 때

이 경우에만 Two-pass를 사용한다.

정렬 처리 방법

1. 인덱스를 이용한 정렬 ORDER BY에서 명시된 컬럼이 제일 먼저 읽는 테이블(조인)이고, ORDER BY 순서대로 생성된 인덱스가 있어야 한다. 또한 B-Tree만 가능하다. 여러 테이블이 조인 된다면 Nested-loop 조인에서 사용한다. 그러나 실행 계획에 조인 버퍼가 사용되면 순서가 흐트러질 수도 있다.

2. 조인 드라이빙 테이블만 정렬 먼저 정렬하고 조인하는게 차선책이 될 것이다.

3. 임시 테이블을 이용한 정렬 2개 이상을 조인하고 정렬하려면 임시테이블이 필요할 수도 있다. 위의 셋 중 가장 느린 정렬 케이스다.

LIMIT

ORDER BY, GROUP BY는 WHERE을 만족하는 LIMIT 만큼 가져와서 처리할 수 없다. 모두 가져와서 정렬하고 LIMIT으로 끊어야 하기 때문이다. 이에 대한 처리 방식으로

1. 스트리밍 방식
   • 서버 쪽에서 요청한 데이터가 얼마인지 관계없이 조건에 일치하는 레코드가 검색되면 바로바로 클라이언트로 전송하는 방식
   • 마지막 레코드를 받을 기약이 없지만 첫번째는 찾자 마자 바로 던질 계획이다. ( 건수 상관 없이 빠른 응답 시간을 보장한다. )
2. 버퍼링
   • ORDER BY, GROUP BY는 스트리밍이 불가하다. 모든 레코드를 가져온 후 정렬 혹은 그루핑해서 차례대로 보내야 하기 때문이다.
   • 검색 - 정렬이 과정 사이에 클라이언트는 기다려야만 한다.

Join은 어느 테이블이 먼저 드라이빙이 되는지도 중요하지만 어떤 정렬 방식으로 처리되는지도 중요하다. 최대한 인덱스를 사용하도록 유도하고 그렇지 못하면 최소한 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 방법이다.

GROUP BY

GROUP BY에서는 HAVING을 사용할 수 있다. HAVING은 GROUP BY 결과에 대해서 필터링 역할을 수행한다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 할 필요는 없다.

1. 인덱스를 사용
   • 인덱스 스캔
   • 루스 인덱스 스캔
2. 인덱스를 사용하지 못하는 경우
   • 임시테이블

인덱스 스캔 사용한 GROUP BY

GROUP BY 컬럼에 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑을 수행한다. 그러나 인덱스를 사용해서 처리하더라도 그룹 함수 등의 그룹 값을 처리해야 한다면 임시 테이블이 필요할 수도 있다.

루스 인덱스 스캔 사용한 GROUP BY

인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다. Using index for group-by 코멘트가 표시된다. 루스 인덱스 스캔은 단일 테이블에 대해서 수행되는 GROUP BY에만 처리할 수 있다. 루스 인덱스 스캔은 인덱스 레인지 스캔과 반대로 유니크한 값이 적을수록 성능이 향상된다. (스캔 분포도가 좋지 않을 경우 스킵할 대상이 많다.)

임시 테이블을 사용한 GROUP BY

GROUP BY 기준 컬럼이 드라이빙에 있든 드리븐에 있든 관계없이 인덱스를 전혀 사용하지 못할 때 이 방식으로 처리된다. Extra에는 Using temporary가 표시된다.

DISTINCT

DISTINCT 처리가 인덱스를 사용하지 못 할 떄 항상 임시 테이블을 사용한다. Extra에 Using temporary가 출력되지는 않는다.

내부 임시 테이블 활용

레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다. 여기서는 CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과 다르기 때문에 internal이라고 표현한다. 일반적으로 임시 테이블은 처음에 메모리에 생성됐다가 테이블 크기가 커지면 디스크로 옮겨진다. 몇몇 케이스는 바로 디스크에 만들어지기도 한다.

#### 임시 테이블이 필요한 쿼리

1. ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
2. ORDER BY, GROUP BY에 명시된 컬럼이 조인 순서상 첫 번째 테이블이 아닌 쿼리
3. DISTINCT, ORDER BY가 동시에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
4. UNION, UNION DISTINCT가 사용된 쿼리
5. 쿼리 실행 계획에 select_type이 DERIVED인 쿼리

#### 임시 테이블이 디스크에 생성되는 경우

1. UNION이나 UNION ALL에서 SELECT되는 컬럼 중 길이가 512바이트 이상인 크기의 컬럼이 있는 경우
2. GROUP BY나 DISTINCT 컬럼에서 512 바이트 이상인 크기의 컬럼이 있는 경우
3. 메모리 임시 테이블의 크기가 tmp_table_size, max_heap_table_size 보다 크거나 temptable_max_ram보다 큰 경우

고급 최적화

옵티마이저가 실행 계획을 세울 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 된다. 옵티마이저 옵션은 조인관련과 옵티마이저 스위치로 구분할 수 있다. 이 옵션은 SET GLOBAL optimizer_switch='~';로 전역으로 가능하고 SET SESSION optimizer_switch='~';로 커넥션 별로 마지막으로 SELECT /*+SET_VAR(optimizer_switch='!')*/로 쿼리에만 옵티마이저 힌트를 줄 수 있다.

옵티마이저 스위치 옵션

1. MRR과 배치 키 액세스(mrr & batched_key_access) : 이중 for 같은 방식으로 조인하는 NestedLoop의 단점을 극복하기 위해서 드라이빙을 읽어서 드리븐과 조인을 즉시하지 않고 조인 대상을 버퍼링하고 조인버퍼에 차면 버퍼링된 레코드를 한 번에 요청해서 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지를 읽기를 최소화 하는 방식이다.MRR(Multi-Ranged Read) 이 방식을 응용한 방식이 BKA(Batched Key Access) 조인이라고 한다.

2. 블록 네스티드 루프 조인 : 네스티드 루프 조인은 레코드를 읽어서 다른 공간에 저장하지 않고 즉시 드리븐 테이블의 레코드를 찾아서 반환한다. 블록 네스티드 루프 조인은 조인 버퍼가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐에 따라서 갈린다. Extra에 Using join buffer( block nested loop )로 확인할 수 있다.

3. 인덱스 컨디션 푸시 다운 : 인덱스를 범위 제한 조건으로 사용하지 못하더라도 인덱스에 포함된 컬럼의 조건이 있다면 스토리지 엔진으로 전달할 수 있게 하는 설정이다. Extra에 Using index condition이 출력된다.

4. 인덱스 확장 : 세컨더리 인덱스에 자동으로 추가된 PK를 활용할 수 있게 할지를 결정하는 옵션이다. 이 옵션이 켜지면 PK가 세컨더리 인덱스에 포함돼 있으므로 정렬 작업도 이를 통해 처리한다. (OrderBy시 덕분에 Using filesort가 출력되지 않는다.)

5. 인덱스 머지 : 인덱스 머지를 사용하면 하나의 테이블에 대해서 2개 이상의 인덱스를 이용해서 쿼리를 처리하게 할 수 있다. 원래는 WHERE에 조건이 여러 개 있어도 인덱스에 포함된 컬럼에 대한 조건만 인덱스를 검색하고 나머지는 레코드에 대해서 체크하는 형태로만 사용된다.

• index_merge_intersection : 두 개 이상의 인덱스를 사용해서 쿼리를 처리하기로 결정한 경우 (인덱스의 교집합을 반환) Extra에 Using intersection 반환
• index_merge_union : 인덱스를 이용하되 OR 연산자로 결합된 경우에 하는 최적화. Extra에 Using union 표기
• index_merge_sort_union : 집합의 중복을 제거하기 위해서 정렬된 결과를 필요로 하는 경우 Sort union 알고리즘을 사용한다.

세미 조인

실제 조인을 수행하는 것이 아니라 다른 테이블에 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 semi-join이라고 한다.

1. semi-join
   • 세미 조인 최적화(Table Pull-out, Duplicate Weed-out, First Match, Loose Scan, Materialization)
   • IN-to-EXISTS 최적화
   • MATERIALIZATION 최적화
2. anti semi-join
   • IN-to-EXISTS 최적화
   • MATERIALIZATION 최적화

Table Pull-out

세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 빼내고 조인으로 재작성하는 형태의 최적화이다. Table pullout은 Extra에 Using table pullout이 출력되지는 않는다. 대신 EXPLAIN 후 SHOW WARNINGS로 재작성한 쿼리를 확인하는 것으로 알 수 있다.

• 세미 조인 서브쿼리에만 사용 가능
• 서브 쿼리 부분이 UNIQUE 인덱스, PK 룩업으로 결과가 1건인 경우에만 사용 가능
• 만약 서브쿼리의 모든 테이블을 아우터로 빼낼 수 있다면 서브쿼리는 사라진다.
• table pullout은 서브쿼리는 조인으로 바꿀 수 있다면 바꾸라는 가이드를 그대로 따르는 최적화 방법이다.

FirstMatch

IN 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다. FirstMatch([tableName])이라는 문구가 출력된다.

1. 장점
   • 여러 테이블이 조인되는 경우 원래 쿼리에는 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 한다. FirstMatch는 조인 형태로 처리되기 때문에 서브쿼리 뿐만 아니라 아우터 쿼리의 테이블까지 전파될 수 있다.
   • FirstMatch는 서브 쿼리의 모든 테이블에서 FirstMatch를 수행할지 아니면 일부 테이블에 대해서만 수행할지 취사 선택할 수 있다.
2. 제한 사항 및 특징
   • FirstMatch는 단축실행경로(ShortCutPath)이기 때문에 FirstMatch 최적화에서 서브 쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다.
   • Extra에 FirstMatch(table-N)이 표시된다.
   • 상관 서브 쿼리(Correlated Subquery)에서도 사용될 수 있다.
   • GroupBY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없다.

LooseScan

Using index for group-by의 루스 인덱스 스캔과 비슷한 읽기 방식을 사용한다. Extra에 LooseScan이라는 문구가 표시된다.

• LooseScan은 서브쿼리 테이블을 looseScan으로 읽고 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다.

Materialization

세미 조인에 사용된 서브쿼리를 통쨰로 구체화해서 쿼리를 최적화한다. 즉, 내부 임시 테이블을 생성한다는 것을 의미한다. select_type에 MATERIALIZED를 표기한다. Materialization에도 몇 가지 제한 사항과 특징이 있다.

• IN(subquery)에서 서브쿼리는 상관 쿼리가 아니어야 한다. (상관 쿼리란 부모-자신 간의 일정 관계를 맺는 경우를 의미한다.)
• 서브 쿼리는 GROUP BY, 집합 함수들이 사용되도 구체화가 가능하다.
• 구체화가 되면 내부 임시 테이블을 사용한다.

DuplicatedWeed-out

세미조인 서브쿼리를 일반적인 INNER JOIN으로 바꾸고 마지막에 중복된 레코드를 제거하는 방법으로 처리하는 최적화 알고리즘이다. 실제로 Duplicate Weedout 은 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리한다. Duplicate Weedout이라는 문구가 별도 표기되지는 않지만 Start temporary, End temporary가 표기된다. JOIN, 저장 하는 과정에서 발생하는 일이다.

• 상관 서브쿼리라고 해도 할 수 있는 최적화다.
• GROUP BY나 집합 함수가 사용된 경우 불가능하다.
• Duplicate Weedout은 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화할 수 있는 방법이 많다.

ConditionFanout

조인 실행 시 테이블 순서는 쿼리의 성능에 큰 영향을 미친다. MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 행한다. 이러기 위해서 조인을 위한 레코드 건수를 추정해야만 한다. condition_fanout_filter가 활성화되면

1. WHERE 조건절에 사용된 컬럼에 대해 인덱스가 있는 경우
2. WHERE 조건절에 사용된 컬럼에 대해서 히스토그램이 존재하는 경우

레코드 비율을 계산할 수 있다. 이를 통해서 분포 값을 살펴보고 filtered 수치를 예측한다.

옵티마이저는 Index 통계 정보 뿐만 아니라

1. 레인지 옵티마이저를 이용한 예측
2. 히스토그램을 이용한 예측
3. 인덱스 통계를 이용한 예측
4. 추측에 기반한 예측 을 사용할 수도 있다.

DerivedMerge

과거에는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 임시테이블로 만든 다음 외부 쿼리 부분을 처리했다. 그래서 FROM 절에 사용된 서브쿼리를 파생 테이블(Derived Table) 이라고 부른다. 내부적으로 생성되는 임시 테이블은 처음에는 메모리에 생성되지만, 임시 테이블에 저장될 레코드 건수가 많아지면 결국 디스크로 다시 기록돼야 한다. 그래서 임시 테이블이 메모리에 올릴 만큼 크기가 작으면 상관 없지만 그렇지 않으면 레코드를 복사고 읽는 오버헤드로 쿼리 성능은 많이 느려질 것이다.

5.7 버전부터 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브 쿼리 부분을 제거하는 최적화가 도입됐다. derived_merge 최적화 옵션을 활성화하면 select_type에서 DERIVED가 사라지고 서브쿼리 없이 단순 실행 계획으로 변경된다. 그러나

1. SUM(), MAX(), MIN() 같은 집계 함수와 윈도우 함수가 사용된 서브쿼리
2. DISTINCT가 사용된 서브쿼리
3. GROUP BY, HAVING이 사용된 서브쿼리
4. LIMIT이 사용된 서브쿼리
5. UNION, UNION ALL이 사용된 서브쿼리
6. SELECT 절에 사용된 서브쿼리
7. 값이 변경되는 사용자 변수가 사용된 서브쿼리

의 경우에는 외부 쿼리로 병합을 할 수 없다. 따라서 웬만하면 병합해서 작성하는 것이 좋다.

InvisibleIndex

인덱스를 삭제하지 않고 사요하지 못하게 해야하는 경우도 있다. ALTER TABLE ... ALTER INDEX [ VISIBLE| INVISIBLE ]로 가용 상태를 변경할 수 있다.

SkipScan

인덱스는 정렬돼 있다는게 핵심이다. (A, B, C)로 인덱스를 잡으면 A정렬, B는 A에 대해 정렬, C는 B에 대한 정렬이 된다. WHERE에 B,C 조건만 있다면 인덱스를 활용할 수 없다. SkipScan은 제한적이긴 하지만 이 제약 사항을 뛰어넘을 수 있는 최적화 기법이다.

8.0부터는 후행 칼럼 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능하다. 그래서 선행 컬럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵스캔 최적화를 사용한다.

세션에서 활성화 SET optimizer_switch=’skip_scan=on’;

특정 테이블에 대해서 활성화 SELECT /** SKIP_SCAN() / COUNT()….

해시 조인

해시 조인이 8.0.18부터 추가 됐다. NestedLoopJoin 보다 HashJoin이 더 빨라 보이지만 항상 그런건 아니다. 잠시 간단히 정리하면 nestedLoop은 2중 for를 사용했다고 생각하면 되고, hash는 map으로 정리하고 for로 순회하면 꺼내 쓰는 방식이다.

성능에 대해서 살펴보면 nested가 첫번째 레코드를 먼저 찾지만 중첩 for를 도느라 실행 기간이 길게 들어 더 늦게 끝난다. hash는 해시 작업이 필요하기에 첫 번째 레코드를 찾는게 더 느리다 그러나 중첩 작업이 없어서 더 빨리 끝난다.

결론적으로 해시 조인은 최고 스루풋(BestThroughput)에 적합하며, 네스티드 루프 조인은 최고 응답 속도(Best Response-time) 전략에 적합하다는 것을 알 수 있다. 그래서 조인 조건의 컬럼이 인덱스가 없거나 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 해시 조인 알고리즘을 사용하도록 설계돼 있다. 즉 해시 조인 최적화는 네스티드 루프 조인이 사용되기에 적합하지 않은 경우를 위한 차선책 정도로 보는게 좋다. 최적화되면 Extra에 hash join을 확인할 수 있다.

과정

해시 조인은 build, probe로 나뉜다. build는 해시 테이블로 만들기 용이한 테이블을 골라서 메모리에 빌드하는 작업을 한다. probe는 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 의미한다. build에 join_buffer_size로 크기를 제어할 수 있는 조인 버퍼를 사용한다. 이 크기보다 테이블이 크다면 나머지 레코드를 디스크에 청크로 구분해서 저장한다. 조인 후 청크를 다시 빌드해서 이 작업을 진행한다.

메모리에서 빌드가 모두 가능하면 Classic hash join을 사용하고, 그렇지 않으면 Grace hash join을 하이브리드하게 활용하도록 구현돼 있다.

인덱스 정렬 선호

MySQL 옵티마이저는 ORDER BY, GROUP BY를 인덱스를 사용해 처리 가능한 경우 쿼리의 실행 계획에서 이 인덱스 가중치를 높게 설정해서 실행된다. 그러나 항상 인덱스를 사용하는게 옳은건 아니다. 레코드 건수가 상당히 많은데도 인덱스 활용으로 가닥을 잡는 경우가 있다. 옵티마이저가 잘못된 실행 계획을 선택했을 가능성이 높다. 이 경우 IGNORE INDEX 힌트를 사용하면 된다. 8.0.21부터는 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 라는 옵티마이저 옵션이 추가됐다.

조인 최적화 알고리즘

조인 쿼리의 실행 계획 최적화를 위한 알고리즘이 2개 있다.

1. Exhaustive (완전 탐색)

모튼 테이블 조합에 대해서 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다.

2. Greedy (탐욕법)

각 단계에서 최적의 조건을 선택해 나가는 방법이다.

조인 최적화를 위한 변수로 optimizer_prune_level, optimizer_search_depth가 제공된다.

1. optimizer_search_depth는 Greedy, Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정하는 시스템 변수다. 조인에 사용된 테이블의 개수가 optimizer_search_depth보다 크면 optimizer_search_depth만큼 Exhaustive가 되고 나머지는 Greedy가 진행된다.

2. Heuristic 검색이 작동하는 방식을 제어한다. Heuristic은 다양한 조인 순서의 비용을 계산하는 도중 이미 계산했던 조인 순서의 비용보다 큰 경우 언제든지 중간에 포기할 수 있다는 것이다.

쿼리 힌트

시간이 갈수록 통계정보, 옵티마이저의 최적화 방법들이 더 다양해지면서 쿼리의 실행 계획 최적화가 성숙해지고 있다. 그럼에도 아직 개입할 여지가 많다.

1. 인덱스 힌트
2. 옵티마이저 힌트

인덱스 힌트

인덱스 힌트는 ANSIBLE하지 않다. 그래서 인덱스 힌트보다는 옵티마이저 힌트 사용이 더 추천된다. 또한, 인덱스 힌트는 SELECT, UPDATE에만 사용할 수 있다.

STRAIGHT_JOIN

SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.

EXPLAIN SELECT /*! STRAIGHT_JOIN */ * FROM employee e, dept_emp de, departments d WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

아래의 경우에만 STRAIGHT_JOIN으로 조인 순서를 조정하는 것이 좋다.

1. 임시 테이블과 일반 테이블의 조인 : 보통 임시테이블을 드라이빙으로 두는 것이 좋다. 일반 테이블에 인덱스가 없다면 레코드가 적은 쪽을 드라이빙을 두는 것이 좋다.
2. 임시 테이블 간 조인 : 항상 인덱스가 없기에 레코드가 적은 테이블을 드라이빙으로 선택하자
3. 일반 테이블 간 조인 : 양쪽 모두 인덱스가 있으면 레코드가 적은 것을 드라이빙으로 둘 중 하나만 있다면 인덱스가 없는 테이블을 드라이빙으로 두는 것이 좋다.

STRAIGHT_JOIN과 비슷한 역할을 하는 옵티마이저 힌트는

• JOIN_FIXED_ORDER
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

USE INDEX/ FORCE INDEX/ IGNORE INDEX

인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다. 강제로 특정 인덱스를 사용하도록 하는 역할을 한다.

• USE INDEX : 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 정도
• FORCE INDEX : USE INDEX 보다 더 강하다.
• IGNORE INDEX : 특정 인덱스를 사용하지 못하게 하는 용도로 사용한다. 풀스캔을 유도하도록 사용할 수 있다.

추가로 용도를 명시할 수도 있다.

• USE INDEX FOR JOIN
• USE INDEX FOR ORDER BY
• USE INDEX FOR GROUP BY

옵티마이저 힌트

옵티마이저 힌트는 영향 범위에 따라 4개의 그룹으로 나눌 수 있다.

• 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
• 테이블 : 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
• 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트
• 글로벌(쿼리 전체) : 쿼리 전체에 대해서 영향을 미치는 힌트