옵티마이저는 통계 정보와 옵션을 결합해서 실행계획을 수립한다.
옵티마이저 옵션은 초기부터 제공되는 옵션과 5.5버전부터 지원되는 옵티마이저 스위치가 있으며 기본 값이 설정되어 있지만 조인이 많이 사용되는 서비스에 따라서는 옵션 값이 각각 어떤 동작을 하는지 알고 있을 필요가 있다.

옵티마이저 스위치 옵션

optimizer_switch 시스템 변수에 묶음으로 옵션을 설정하는 방식을 사용하고 있으며 아래와 같은 옵션이 있다.

스위치 이름	기본값	설명
batched_key_access	off	BKA조인 알고리즘 사용 여부 설정
block_nested_loop	on	Block Nested Loop 조인 알고리즘 사용 여부 설정
engine_condition_pughdown	on	engine_condition_pughdown 기능 사용 여부 설정
index_condition_pushdown	on	index_condition_pushdown 기능 사용 여부 설정
use_index_extensions	on	use_index_extensions 최적화 사용 여부 설정
index_merge	on	index_merge 최적화 사용 여부 설정
index_merge_intersection	on	index_merge_intersection 최적화 사용 여부 설정
index_merge_sort_union	on	index_merge_sort_union 최적화 사용 여부 설정
index_merge_union	on	index_merge_union 최적화 사용 여부 설정
mrr	on	mrr 최적화 사용 여부 설정
mrr_cost_based	on	mrr_cost_based 최적화 사용 여부 설정
semijoin	on	semijoin 최적화 사용 여부 설정
firstmatch	on	firstmatch 최적화 사용 여부 설정
loosescan	on	loosescan 최적화 사용 여부 설정
materialization	on	materialization 최적화 사용 여부 설정
subquery_materializaion_cost_based	on	subquery_materializaion_cost_based 최적화 사용 여부 설정

MRR(Multi Range Read)

조인을 사용할때 드라이빙 테이블에 연결되는 드리븐 테이블 데이터를 가져오게 된다. 이때 드리븐 테이블 데이터를 한번에 가져오면 성능이 떨어지게 된다. 따라서 이를 방지하기 위해 조회된 드라이빙 테이블 조인을 바로 실행하지 않고 조인 버퍼에 담아두었다가 가득 차면 한번에 요청하는 방법이다.

블록 네스티드 루프 조인

조인되는 컬럼이 모두 인덱스일 경우 사용되는 조인 방식을 네스티드 루프 조인이라 한다.

드리븐 테이블에 인덱스를 사용하지 않는 조인 방식에서는 드라이빙 테이블에 대상 갯수만큰 드리븐 테이블을 풀스캔해야하는데 이를 방지하기 위한 방식이 블록 네스티드 루프 조인이다.

블록 네스티드 루프 조인은 아래와 같은 단계를 거쳐 조인이 된다.
1. 드라이빙 테이블에 인덱스를 이용해 레코드 조회
2. 조인버퍼에 해당 레코드 적재
3. 드리븐 테이블 전체 조회(풀스캔)
4. 조인버퍼에 있는 레코드를 드리븐 테이블에 조인하여 결과 반납

위에 과정을 자세히 살펴보면 드리븐 테이블을 조회하고 그 결과에 드라이빙 테이블을 붙여서 반납한다는 사실을 알 수 있다. 이는 일반적인 조인과 반대로 진행된다 보일 수 있는데 여기서 가장 중요한 점은 조인 버퍼가 가장 중요하다. 조인 버퍼를 사용하는 조인 방식에서는 드리븐 테이블에 드라이빙 테이블을 붙이는 방식이기에 결과가 드라이빙 테이블 기준으로 정렬이 되어있지 않을수 있다는 점이다.

인덱스 컨디션 푸시다운

조건절에서 인덱스를 이용할려면 동등, 범위 조건과 같은 방법이 사용되어야 한다 배웠다.

select *
from table
where index1 = "인덱스" and index2 like "%인덱스"

위와 같이 다중 인덱스에서 첫번째 인덱스는 동등 검사를 하지만 두번째 조건에서 like '%검색어'와 같이 두번째 조건이 인덱스를 사용할 수 없는 조건일 경우 실제 레코드를 가져와 비교하는데 해당 옵션을 이용하면 인덱스를 이용해서 두번째 조건까지 검사하고 모든 조건에 맞는 실제 레코드를 가져온다. 따라서 모든 조건에 맞는 레코드만 가져오기 때문에 성능상 이점을 가질 수 있다.

인덱스 확장

InnoDB 스토리엔진으로 생성된 테이블은 프라이머리 키가 클러스터링 인덱스로 생성되며 그 외에 모든 세컨더리 인덱스는 프라이머리 키를 참조한다.
이러한 특징으로 인핸 세컨더리 인덱스와 프라이머리 인덱스에 해당하는 컬럼을 조건절에 사용하면 세컨더리 인덱스가 프라이머리 인덱스를 가지고 있기에 하나의 인덱스처럼 동작하고 정렬 작업도 인덱스를 이용해서 할 수 있다는 장점이 있다.

인덱스 머지

하나의 테이블에 여러개의 인덱스가 존재 할 수 있다. 일반적으로 여러 인덱스를 사용할 수 있는 쿼리가 있다면 인덱스들 중 성능이 가장 좋은 인덱스를 사용한다 생각하지만 두개의 인덱스를 합쳐서 사용하는 방식을 지원한다.

인덱스 확장과 굉장히 비슷한 방법처럼 보이지만 인덱스 확장은 세컨더리 키가 프라이머리키를 참조하는 구조적인 방법이고 인덱스 머지는 옵티마이저가 쿼리에 사용되는 두개의 쿼리를 같이 사용하는게 성능상 빠르다고 판단되면 사용하는 방식이라는 점에서 차이가 있다.

해당 방식을 지원하는 3가지의 방법에 대해서 알아보자.

index_merge_intersection(교집합)

쿼리에 2개의 인덱스를 사용한다면 두개의 인덱스를 사용해 조건에 해당하는 인덱스를 추려내 해당하는 레코드만을 가져오는 방식이다. 인덱스 확장과 가장 비슷하게 동작하며 인덱스 확장을 사용하도록 유도하고 싶다면 현재 커넥션에 대해서만 해당 기능을 비활성화하는 방법을 고려해볼수 있다.

index_merge_union(합집합)

2개 이상의 인덱스에 대해 동등 조건을 사용하며 각 조건에 대해 or 연산자를 사용하는 경우 동작하는 방식이다.
해당 방법으로 조회되면 양쪽 조건에 해당하는 레코드는 두번 출력되지 않는데 그 이유는 세컨더리 인덱스가 프라이머리 인덱스를 참조하고 있고 프라이머리 인덱스를 기준으로 정렬되어 있으며 그러한 프라이머리 키를 기준으로 두 집합의 결과를 중복제거하여 최종 결과를 만들기 때문이다. 이러한 방식을 우선순위 큐라고도 한다.

index_merge_sort_union(정렬 후 합집합)

2개 이상의 인덱스에 대해 범위 조건을 사용하며 각 조건에 대해 or 연산자를 사용하는 경우 동작하는 방식이다.

정렬을 한번 하는 이유는 세컨더리 인덱스에 대해 범위 조건을 사용하게 되는 경우인데 이는 인덱스가 왼쪽부터 순차적으로 정렬된다는 특징때문이다.

세미조인

실제 조인을 수행하지 않고 where절에 from절과 다른 테이블인 서브쿼리가 사용되는 경우를 말한다. 이러한 경우 MySQL서버는 from절에 해당하는 전체 데이터를 읽어와 where절과 비교했는데 이를 최적화하기 위한 방법들을 알아보겠다.

테이블 풀-아웃

세미 조인의 서브쿼리를 조인절로 바꿔서 동작하는 방식으로 IN(subquery) 형태에서 많이 사용되는 방식이다.

select *
from table1 t1
where t1.column1 in (select t2.column1 from table2 t2 where t2.column2 = 'value')

위와 같은 형식이며 실행계획을 확인해보면 Extra에 별도의 문구가 출력되지는 않고 id 값이 1로 같은 값이 나오는것을 확인할 수 있다. 여기서 id 값은 쿼리에서 식별된 select문의 번호인데 서브쿼리 부분도 같은 1로 나온다는것은 두 개의 쿼리가 하나로 합쳐졌다는 것을 의미한다.

여기서 중요한점은 서브쿼리를 조인으로 풀어서 사용하라는 가이드가 많은데 굳이 풀어서 사용할 필요는 없다는 점이다. 단 조건절에서 사용되는 한에서이다.

퍼스트 매치

IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행되는 방식을 말한다. 해당 조건에 해당하는 첫번째 값이 있는지만 확인하기에 불필요한 작업을 최소화 해준다. 또한 아래와 같은 특징이 있다.

• 서브쿼리에서 하나의 레코드만 검색되면 검색을 멈추는 단축 실행 경로 방식이기 때문에 아우터 테이블이 조회된 이후에 실행된다.
• Extra 컬럼에 FirstMatche(e)라는 문구가
• 상관 서브쿼리에서도 사용될 수 있다.
• GROUP BY 또는 집합 함수가 사용된 서브쿼리에 최적화에는 사용할 수 없다.

루스 스캔

서브 쿼리로 사용하는 테이블에 다중 프라이머리 키 값중 맨 처음 키값과 아우터 테이블에 필드와 비교하는 경우 사용되는 방식으로 서브쿼리에 프라이머리 첫번째 키 값은 정렬되어 있는 상태이기 때문에 중복되는 값중 한개의 값만 사용되는 방식이다.

다만 옵티마이저가 앞에 나온 퍼스트 매치나 다음에 나올 구체화 방식으로도 실행할 수 있어 해당 방식은 우선순위에서 밀리는것 같다.

구체화

세미 조인에 사용된 서브쿼리를 통체로 구체화하여 실행하는 방식으로 내부 임시 테이블을 사용하는 방식을 말한다.

아래는 구체화 최적화의 특징이다.

• 서브쿼리를 이용해 내부 임시 테이블을 생성한 후 아우터 테이블과 조인한다.
• 서브쿼리가 상관 서브쿼리가 아니여야 한다. (상관 서브쿼리는 서브쿼리내에 아우터 테이블 컬럼을 참조하는 서브쿼리)
• 다른 최적화와 달리 group by절에도 사용 가능하다.

컨디션 팬아웃

쿼리에서 조인을 실행할때 테이블의 순서가 중요하다. 이는 드라이빙 테이블에 수가 많아진다면 드리븐 테이블과 많은 조인이 일어난다. 해당 옵션은 이러한 경우에 더욱 효율적인 실행계획을 제공한다.

드라이빙 테이블 인덱스 조건, 일반 컬럼 조건이 있는 쿼리를 해당 옵션이 꺼져있는 상태에서 실행한다면 옵티마이저는 다음과 같은 실행계획은 세운다.
1. 드라이빙 테이블에 인덱스 조건에 해당하는 레코드 수를 예상한다.
2. 드리븐 테이블에 대해 조인과 그 외 나머지 조건을 적용한 레코드 수를 예상한다.

하지만 해당 옵션이 켜져있다면 다음과 같이 실행계획을 세우게 된다.
1. 드라이빙 테이블에 적용되는 모든 조건에 해당하는 레코드 수를 예상한다.
2. 드리븐 테이블에 대해 조인과 적용되는 조건을 적용한 레코드 수를 예상한다.

이는 드라이빙 테이블에 갯수가 줄어들어 무조건적인 성능상 이점이 있어보이지만 드라이빙 테이블에 대해 더욱 정교한 예측을 하기에 많은 비용이 발생할 수 있다. 또한 해당 옵션을 적용하기 전 실행계획에 적중률이 높다면 옵션에 따른 성능 체크를 하고 적용하는 것이 좋다.

파생 테이블 머지

인라인뷰 형태로 사용된 서브쿼리에 대한 최적화로 인라인 뷰에 대한 임시 테이블을 생성하지 않고 인라인뷰에 조건을 쿼리 본문 조건으로 적용하고 from절에는 테이블만 있는 일반 쿼리처럼 변경하는 옵션이다.
아래에 경우는 적용이 안되니 되도록 외부 쿼리로 작성하여 적용하도록 하자.

• 집계 함수, 윈도우 함수가 적용된 서브쿼리
• DISTINCT가 사용된 서브쿼리
• GROUP BY, HAVING이 사용된 서브쿼리
• LIMIT이 사용된 서브쿼리
• UNION, UNION ALL이 사용된 서브쿼리
• SELECT절에 사용된 서브쿼리
• 값이 변경되는 변수가 사용된 서브쿼리

인비저블 인덱스

인덱스를 삭제하지 않고 옵티마이저가 인덱스를 사용하지 못하도록 하는 설정이다.

alter table 테이블명 alter index 인덱스명 [VISIBLE | INVISIBLE]

위에 쿼리를 사용해 설정할 수 있다.

인덱스 정렬 선호

쿼리에서 사용할 수 있는 인덱스가 여러개 있을 경우 상황에 따라서는 프라이머리 인덱스를 사용하기 보다 세컨더리 인덱스를 쓰는것이 효율적일때도 있다. 정렬에 프라이머리 인덱스를 사용하는 경우가 많은데 이때 prefer_ordering_index 옵션을 해당 세션 또는 쿼리에서만 꺼두는것도 방법이 될 수 있다.