MySQL 고급 최적화

MySQL 은 여러가지 최적화 기능을 제공해준다.

 

이 기능의 활성화 여부에 따라서 실행 계획에도 영향을 주기도 하는데 하나씩 살펴보자. 

 

---

MRR 과 배치 키 엑세스 (Multi Range Read & Batched_key_access):

MRR 은 스토로지 엔진이 데이터를 가지고 올 때 가지고 올 값을 한번에 요청해서 Disk Linear I/O 로 최적화로 읽도록 하는 방법이다.

 

이는 주로 조인 작업에서 사용될 수 있는데, 드라이빙 테이블의 레코드는 드리븐 테이블과 조인을 위해서 한 건씩 스토로지 엔진에 요청된다.

 

이렇게 할 경우 매번 Disk Random I/O 가 발생하게 된다.

 

MRR 을 사용할 경우 이렇게 하지말고 Join Buffer 에 조인 칼럼들을 저장해놓고 한번에 드리븐 테이블의 레코드들을 가지고 오라고 요청을 한다. 

 

그러면 스토로지 엔진은 가지고 와야 할 조인 칼럼들을 보면서 Linear I/O 로 최적화해서 가지고 올 수 있다.

 

BKA 는 MRR 을 응용한 솔루션이며 기본적으로 비활성화 되어있다.

 

블록 네스티드 루프 조인 (Block Nested Loop Join) 과 해시 조인 (Hash Join):

Block Nested Loop Join 과 Hash Join 은 유사한 점이 많아서 같이 소개하겠다.

 

일단 MySQL 8.0.20 부터는 Block Nested Loop Join 이 모두 Hash Join 으로 대체되었다.

 

Block Nested Loop Join 은 조인을 할 때 Outer Table (드라이빙 테이블) 과 Inner Table (드리븐 테이블) 이 있다면, Inner Table 의 값을 모두 조인 버퍼에 저장해둬서 Outer Table 만을 읽으면서 처리해나가는 방식이다.

 

Hash Join 은 조인을 하는 두 테이블 중 레코드가 더 적은 쪽을 조인 버퍼에 올려놓고, 나머지 테이블을 읽으면서 조인을 처리해나가는 방식이다.

 

Hash Join 을 사용할 떄 주의해야 할 사항으로는 항상 이게 일반적으로 사용하는 조인 알고리즘인 Nested Loop Join 보다 우월하다고 착각하지 않는 것이다.

 

Nested Loop Join 은 결과 집합의 첫 레코드를 찾는 시간이 Hash Join 보다 더 빠르다. 그래서 OLTP 용도로 더 적합하다.

 

그에 반해서 Hash Join 은 조인의 처리량이 Nested Loop Join 보다 우월하다. 매번 조인을 하지 않아도 되고 한 쪽 테이블만을 쭉 읽어나가면 되니까.

 

Hash Join 의 조인 버퍼의 기본 크기는 256KB 인데, 필요하다면 조인 버퍼를 해당 세션에서 더 늘려서 사용하는 것도 좋을 수 있다.

 

그리고 Hash Join 을 할 때 조인 버퍼에 올라가는 빌드 테이블을 보고 싶다면 EXPLAIN FORMAT=TREE 또는 EXPLAIN ANALYZE 명령을 통해서 볼 수 있다.

 

인덱스 컨디션 푸쉬다운 (Index Condition Pushdown):

Index Condition Pushdown 은 WHERE 절에 사용되는 인덱스 칼럼을 인덱스 레벨에서 적극적으로 필터링을 하는 최적화이다.

 

이 최적화가 없다면 실제로 테이블 레코드를 MySQL 엔진으로 가지고와서 필터링을 할 것이다.

 

Index Condition Pushdown 최적화를 off 한 예시를 보자:

• 해당 쿼리에서 인덱스는 (last_name, first_name) 으로 이뤄져있다.
• 이 쿼리에서 first_name 은 인덱스 레벨에서 필터링을 하지 못한다.
• 그래서 last_name=Action 에 해당하는 레코드를 다 가지고와서 MySQL 엔진에서 필터링을 한다. 실행 계획의 extra 칼럼에 있는 Using Where 은 그 뜻이다.

 

그러나 Index Condition Pushdown 최적화를 활성화 하면 first_name 의 조건을 이용해서 인덱스 레벨에서 필터링을 해줘서 MySQL 엔진으로 가지고 오는 레코드 수는 더 적을 것이다.

 

인덱스 확장 (Use Index Extension):

먼저 세컨더리 인덱스 (Secondary Index) 를 만들면 내부적으로 PK 도 같이 붙는다는 사실을 알아야한다. (PK 값이 붙어야지 실제 레코드에 엑세스 할 수 있다.)

 

Index Extension 은 Secondary Index 에 붙은 PK 를 활용해서 인덱스 레벨에서 필터링을 할 수 있도록 하는 기능이다.

 

예시를 살펴보자:

• 아래 쿼리의 PK 는 dept_no 로 이뤄져있고, Secondary Index 는 from_date 로 이뤄져있다.
• 실행 계획의 key_len 타입은 인덱스에서 총 사용한 칼럼의 바이트 수를 의미하는데, 여기서 19 라고 적혀있는 건 PK 까지 포함된 것이다.

 

 

인덱스 머지 (Index_merge):

일반적으로 SQL 문은 하나의 인덱스만을 이용해서 처리해나간다.

 

그러나 WHERE 절에 사용할 수 있는 인덱스 칼럼이 많고, 하나의 인덱스만을 이용하면 탐색해야하는 레코드 수가 많다면 여러 인덱스를 통해 검색하고 이들을 병합하는 식으로도 처리할 수 있다.

 

Index Merge 는 이런식으로 여러개의 인덱스를 이용하고 병합하는 식으로 처리하는 최적화를 말한다.

 

병합은 크게 '교집합' 과 '합집합' 이 있다.

 

세미조인 최적화 (Semijoin)

세미조인이란 WHERE 절에 서브쿼리를 사용한 형태의 쿼리를 말하며, 실제 조인을 수행하지는 않고 결과만 있는지 확인하는 용도로 사용한다.

 

MySQL 5.7 에서는 세미조인의 최적화가 상당히 부실했는데 MySQL 8.0 으로 오면서 최적화 기능이 좀 들어왔다.

 

여기서 살펴본 세미조인 최적화는 다음과 같다:

• Table Pull-out
• First Match
• Loose Scan
• Materialization
• Duplicated Weed-out

 

Table Pull-out

Table Pull-out 은 서브쿼리에 사용한 테이블을 Outer 테이블로 빼내서 조인으로 처리하는 방식으로 바꾸는 최적화이다.

 

만약 이 최적화가 없다면 Outer 테이블을 풀스캔하면서 한 건씩 서브쿼리를 실행하는 형태가 될 것이다.

 

다음 예시를 보자:

• 실행 계획의 id 칼럼이 같은 값인 건 조인을 사용했다는 뜻이다.

 

 

 

First Match

First Match 는 Outer 테이블과 서브쿼리가 조인으로 처리되며, 서브쿼리를 탐색할 때 조건에 일치한 레코드를 찾으면 이후 탐색을 하지 않아도 되서 Skip 하면서 처리해나가는 방식이다.

 

예시를 보자:

• 실행 계획을 보면 id 칼럼의 값이 같으니 다음 쿼리도 조인으로 처리된다.
• Titles 테이블이 드리븐 테이블로써 검색될텐데 조건에 맞는 값이 있다면 스캔을 더 이어나가지 않고 다음 드라이빙 테이블 레코드 값으로 조인을 이어나간다.

 

 

 

Loose Scan

서브쿼리를 Index Loose Scan 으로 검색하고 해당 레코드를 서브쿼리 바깥의 Outer 테이블과 조인하는 방식으로 처리한다.

 

서브쿼리 전체를 탐색해볼 필요가 없고, 일부만 루스 스캔으로 탐색해보면 되는 경우에 대해서 사용하는 최적화이다.

 

예시는 다음과 같다:

• dept_emp 의 PK 는 (dept_no, emp_no) 로 이뤄져있다.
• departments 의 전체 개수는 9 개라서 dept_emp 를 전체 스캔해보지 않아도 된다. 9개의 값만 루스 스캔으로 탐색하면 된다.

SELECT * FROM departments d 
WHERE d dept_no IN (SELECT de.dept_no FROM dept_emp de); 

 

 

Materialization

서브쿼리의 결과를 먼저 임시 테이블에 기록해놓고 이를 읽으면서 Outer 테이블과 조인을 하는 방법을 말한다.

 

이렇게 하지 않으면 Outer 테이블을 먼저 풀스캔 하면서 조인을 해야할 수 있기 때문에 나온 최적화이다.

• Explain 에 3개의 행이 리턴되었다는 건 3개의 테이블을 읽었다는 뜻인데 그 중 하나가 임시 테이블이다. (table 칼럼의 <subquery2> 로 명시된 게 임시테이블이다.)

 

 

Duplicated Weed-out

Duplicated Weed-out 는 서브쿼리를 INNER JOIN 으로 풀어서 Outer 테이블과 조인을 시키고 조인의 결과로 나온 행에서 중복을 제거하는 기법이다.

 

중복을 없애는 방법은 내부적으로 GROUP BY 절을 작성시켜서 없앤다.

 

예시는 다음과 같다:

 

 

컨디션 팬아웃 (condition fanout filter):

condition fanout filter 는 인덱스로 검색했을 때 몇 건의 레코드가 걸러질 지 알려줘서 실행 계획을 수립하는데 도움을 주는 최적화이다.

 

condition fanout filter 를 사용하려면 다음 조건이 충족되어야한다;

• WHERE 절에서 사용되는 인덱스가 있는 경우
• WHERE 절에서 사용되는 칼럼이 히스토그램이 있는 경우 (히스토그램은 칼럼 값의 분포도를 말한다.)

 

MySQL 은 실행 계획을 수립할 때 다음 순서대로 정보들을 이용한다. 통계 정보는 꽤 후순위다:

• 1. 레인지 옵티마이저를 이용한 예측:
  • 실제 조건절에 사용할 인덱스를 통해서 소량의 데이터를 스캔을 해보면서 데이터 수를 예측
  2. 히스토그램을 통한 예측
  3. 인덱스 통계를 이용한 예측
  4. 추측에 기반한 예측

 

파생 테이블 머지 (derived_merge):

이 최적화가 없었을 때 MySQL 에서는 FROM 절에 서브쿼리가 있다면 이를 임시 테이블에 기록하고, 임시 테이블을 바탕으로 실제 주 쿼리가 수행되도록 되었다:

 

즉 임시 테이블로의 데이터를 복사하는 비용이 생긴다. 

 

Derived Merge 최적화가 생기면서 서브쿼리는 그냥 주 쿼리와 병합해서 사용되도록 최적화를 할 수 있다.

 

인비저블 인덱스 (use_invisible_index):

MySQL 8.0 에서는 인덱스를 가용/비가용 상태로 변경하는 기능이 있다.

 

인덱스 스킵 스캔 (Skip Scan):

MySQL 8.0 이전에는 복합 인덱스의 경우 선행 칼럼이 조건절에 없다면 사용이 안된다는 제한이있었다.

 

그러나 Index Skip Scan 최적화가 되면서 인덱스의 선행 칼럼이 없더라도 사용할 수 있다:

 

이는 선행 칼럼이 있는듯이 사용되는데, 실제 선행 칼럼으로 올 수 있는 모든 값이 IN() 절로 들어오게 되는 쿼리가 작성되면서 선행 칼럼이 사용된다.

 

그러므로 선행 칼럼이 유니크 값의 분포가 많다면 인덱스 스킵 스캔의 성능은 잘 나오지 않을 것이다.

 

인덱스 정렬 선호 (prefer_ordering_index):

ORDER BY 나 GROUP BY 를 사용하고 있다면 해당 칼럼의 인덱스를 사용하려는 경향이 강하다. 설령 WHERE 절에 다른 칼럼이 사용되고 있다 하더라도.

 

옵티마이저는 정렬이나 집계에 인덱스를 사용하는 것을 더 중요하다고 판단하기 때문임.

 

MySQL 8.0.21 부터는 옵티마이저가 ORDER BY 나 GROUP BY 를 위한 인덱스에 너무 많은 가중치를 부여하지 않도록 하기 위해서 prefer_ordering_index 설정도 제공해준다.

 

조인 최적화 알고리즘:

여러 테이블들을 조인할 때 실행 계획이 오래걸리는 문제가 발생하기도 한다.

 

여기서는 이런 문제가 발생하는 알고리즘과 이를 해결해줄 수 있는 방법의 알고리즘에 대해서 살펴본다.

Exhaustive 검색 알고리즘:

조인을 해야하는 여러 테이블들이 있다면 이 테이블들을 이용해서 모든 조인 순서 경우의 수를 구성해서 실행 계획을 수립하는 알고리즘이다.

 

즉 Brute Force 와 같다. 4개의 테이블로 모든 조인 집합을 만든다면 4! 가 될 것이다.

 

Greedy 검색 알고리즘:

Exhaustive 검색 알고리즘의 문제를 해결하기 위해서 등장한 알고리즘이다.

 

Greedy 라는 의미에 맞게 현 시점에서 최적의 선택을 하지만, 모든 조인 순서가 결정되었을 때 항상 최선의 값을 보장해주지 않는다.

이 알고리즘의 방식을 예시로 보자:

• T1, T2, T3, T4 테이블이 있고 이 테이블들을 조인해야한다고 가정해보자.
• 가장 먼저 두 테이블을 조인을 해야하는데 이 중에서 가장 비용이 낮은 조인을 먼저 결정한다.
  • 예시로 T1 -> T2, T1 -> T3, T1 -> T4 … T4 -> T2, T4 -> T3 로 가는 조인 중 가장 비용이 낮은 것을 선택한다.
  • T1 -> T2 가 비용이 가장 낮다고 결정된다면 이제 T2 에서 T3, T4 로 가는 테이블 중 가장 비용이 낮은 것을 선택한다.
  • 이 과정을 통해서 조인 순서가 결정된다.

 

MySQL 에서는 위의 두 가지 조인 실행 계획 알고리즘를 조정하기 위한 시스템 변수가 존재한다.

• optimizer_prune_level:
  • 이 방식의 핵심은 조인 실행 비용을 따지다가 이전에 계산한 최소 조인 비용보다 크다고 판단하면 현재 조인 실행 비용을 따지는 것을 멈추는 것이다.
  • 이렇게 행동하지 않았다면 끝까지 비용을 따지고 다음 조인 실행 계획에 대한 비용을 따지는 것이다.
  • 즉 중간에 멈추는 기능이 있는 것.
  • 이 값은 활성화 해두는 것이 낫다. 0으로 하지 말 것. (= 비활성화)
• optimizer_search_depth:
  • 이 값보다 조인에 사용되는 테이블 개수가 작다면 Exhaustive 알고리즘이 작동된다.
  • 이 값보다 조인에 사용되는 테이블 개수가 크다면 전체 알고리즘은 Greedy 방식으로 동작하나 이 값만큼은 Exhaustive 검색 알고리즘이 작동된다.
  • 기본 값은 62인데, 4-5 로 정하는 것이 낫다.

 

 

References:
https://www.yes24.com/Product/Goods/103415627