[MySQL] Optimizer의 최적화 방식에 대해 알아보자

Optimizer

1. Optimizer란 무엇일까?

mysql server의 mysql engine에 위치하며

통계 정보를 활용해서 가장 적은 비용이 드는 실행 계획을 세우는 역할을 한다.

 

2. 통계 정보란 무엇일까? 

mysql 5.7까지는 테이블과 index 정보를 가지고 실행 계획을 세웠지만

mysql 8.0부터는 histogram이 도입되어서 column의 분포도를 활용할 수 있게 되었다.

 

histogram은 2가지 타입이 있다.

column 값별로 record 건수를 관리하는 singleton 방식과

column 값의 범위를 균등한 갯수로 구분하여 record 건수를 관리하는 equi-height 방식이 있다.

singleton 방식은 유니크한 값의 갯수가 적은 경우에 사용된다.

 

column 분포도의 경우 index를 사용하는 경우와 index를 사용하지 않는 경우로 나뉜다.

index를 사용하지 않는 경우 ANALYZE TABLE 명령어가 수행될 때마다 histogram 정보가 수집된다. 

index를 사용하는 경우 쿼리가 실행될 때 index tree를 sampling해서 분포도를 수집하는데 이를 index dive라고 한다.

 

mysql 5.6 이전까지는 통계 정보를 메모리에서만 관리했었지만

mysql 5.6부터 테이블로 관리할 수 있도록 개선되었다.

index 정보는 mysql.index_stats 테이블에 저장되고

테이블 정보는 mysql.table_stats 테이블에 저장되고

histogram 정보는 information_schema.column_statistics 테이블에 저장된다.

 

3. Optimizer와 관련된 변수는 어떤 게 있을까?

innodb_stats_auto_recalc

- 활성화하면 테이블 데이터가 10퍼센트 이상 변경될 때 통계 정보가 자동으로 갱신된다. 

  비동기적으로 실행되므로 대규모 데이터 변경이 생길 경우

  수동으로 ANALYZE TABLE 명령어를 입력해서 즉시 변경하는 것이 좋다.

- 이 옵션을 통해 자동으로 수집되는 정보는 테이블과 index 정보이며

   histogram 정보는 자동으로 갱신되지 않는다. ANALYZE TABLE 명령어를 통해 수동으로 갱신해야 한다.

 

innodb_stats_persistent_sample_pages

- 통계 정보 갱신할 때 이 값만큼의 page 갯수를 sampling해서 분석한다. 

- 이 옵션을 통해 수집되는 정보는 테이블과 index 정보이다.

 

histogram_generation_max_mem_size

- histogram 정보를 저장하는 최대 공간 크기로 이 값이 클수록 samling을 많이 하게 된다.

 

Join

1. Nested Loop Join

driving table의 각 row마다 driven table을 탐색해서

join 조건에 맞는 row를 찾는 방식이다.

join column에 index가 걸려있어서 이를 통해 driven table을 탐색한다.

 

2. Block Nested Loop Join

driving table을 읽어서 join buffer라는 공간에 저장하고

driven table을 읽으면서 join buffer와 비교해서

join 조건에 맞는 row를 찾는 방식이다.

3. Hash Join

mysql 8.0.18부터 도입되었으며

mysql 8.0.20부터는 nested loop join이 사용되지 못하는 경우

block nested join 대신 사용된다.

 

hash join은 2단계로 이루어지는데

Build 단계에서는 join 대상 테이블 중 build 테이블을 선정해서 hash table을 생성한다.

join key에 hash 함수를 적용한 값으로

hash table에서 hash chain을 찾고 데이터를 연결한다.  

Probe 단계에서는 나머지 테이블을 스캔하면서 hash table과 비교한다.

 

정렬

1. Index를 이용한 정렬

order by 절에서 index를 사용하려면 다음 조건을 충족시켜야한다.

1. index가 order by 절에 명시된 column 순서로 생성되어야 하고 정렬 방향이 일치해야 한다. 

2. where 절에서 index를 사용한다면 index 순서를 깨뜨리면 안된다.

3. where 절에서 범위조건을 사용한다면 해당 column이 order by 절에 있어야 한다.

 

2. Driving Table만 정렬

index를 사용하지 못하는 경우

sort buffer를 사용해서 driving table을 검색하고 정렬한다.

 

sort buffer를 사용해서 정렬하는 방법은 2가지가 있다.

첫 번째로 single pass 방식이다.

정렬 기준 column을 포함해 select 대상이 되는 column 전부를 읽고

sort buffer에 담아서 정렬하는 방식이다.

두 번째로 two pass 방식이다.

정렬 대상 column과 primary key만 읽고

sort buffer에 담아서 정렬하고 정렬 순서대로 다시 select 대상이 되는 column을 읽는 방식이다.

 

일반적으로 single pass 방식을 사용하지만 아래와 같은 경우 two pass 방식을 사용한다.

1. record의 크기가 max_length_for_sort_data에 설정된 값보다 큰 경우

2. select 대상 column의 타입이 BLOB이거나 TEXT인 경우

 

3. 임시 테이블을 사용한 정렬

2개 이상의 테이블을 join한 결과를 정렬해야한다면 임시 테이블이 필요하다.

 

그룹

1. Index를 사용한 그룹화

group by 절에서 index를 사용하려면 다음 조건을 충족시켜야한다.

1. index가 group by 절에 명시된 column 순서로 생성되어야 한다.

2. where 절에서 index를 사용한다면 index 순서를 깨뜨리면 안된다.

3. where 절에서 범위조건을 사용한다면 해당 column이 group by 절에 있어야 한다.

 

loose index scan를 사용해서 그룹화를 할 수도 있다.

index tree에서 record를 건너뛰면서 탐색하는 방식이다.

 

2. 임시 테이블 사용한 그룹화

index를 사용하지 못하는 경우 임시 테이블을 사용한다.

group by 절의 column들로 구성된 unique index를 가진 임시 테이블을 만들어서

중복을 제거하고 집합 함수 연산을 수행한다.

 

mysql 8.0 이전까지는 group by 절의 column들로 묵시적인 정렬까지 수행했었지만

mysql 8.0 부터는 해당 기능이 제거되었다.

 

Index

1. Index Condition Pushdown

mysql 5.6부터 도입된 기능으로 index_condition_pushdown을 사용하면

record를 읽어오기 전에 index에서 where 조건을 적용해서 필요한 record만 읽어올 수 있다.

 

2. Index Extension

innodb storage 엔진을 사용하는 경우 모든 secondary index는 leaf node에 primary key 값을 가진다.

use_index_extensions를 사용하면 leaf node의 primary key를

활용해서 index를 탐색할 수 있다.

 

3. Index Merge

하나의 테이블에 2개 이상의 index를 사용할 수 있다. 

 

index_merge_intersection을 사용하면 각각 index를 탐색해서 리스트를 구하고 교집합 연산을 한다.

교집합에 해당하는 record만 읽어오면 된다.

 

index_merge_union을 사용하면 각각 index를 탐색해서 리스트를 구하고 합집합 연산을 한다.

합집합에 해당하는 record만 읽어오면 된다.

 

Semi Join

1. Semi Join이란 무엇일까?

subquery의 결과에 존재하는 데이터만 main query에서 filtering하는 join방식이다.

다음과 같은 형태가 있고 이를 최적화하는 기법들이 있다.

1. = (subquery)

2. IN (subquery)

3. EXISTS (subquery)

 

2. Table Pull Out

subquery에서 사용된 테이블을 바깥으로 꺼낸 후에

inner join을 사용해서 재작성하는 최적화 방법이다.

 

3. First Match

외부 테이블의 row마다 내부 테이블에서 조건을 만족하는 첫 번째 row를 찾으면 다음으로 넘어간다.

 

4. Loose Scan

index를 건너뛰면서 그룹별로 record를 탐색하는 최적화 방법이다.

subquery에서 사용된 테이블이 driving table로 사용되고 바깥의 테이블을 driven table로 사용한다. 

 

5. Materialization

subquery 결과를 임시 테이블로 생성하고 join을 수행하는 최적화 방법이다.

 

6. Duplicated Weed Out

inner join을 수행하고 중복되는 결과를 제거하는 최적화 방법이다.

inner join과 group by 절로 바꿔서 실행하는 것과 동일한 작업으로 처리한다.