MySQL 옵티마이저가 하는 일

웹서비스를 개발하다 보면 실제 운영서버에 반영하기 전에 만들어둔 쿼리에 대한 테스트 및 성능확인이 필요합니다.

주로 EXPLAIN 명령어를 통해 쿼리의 성능을 평가했는데, 그냥 인덱스를 잘 타는구나~ 이 정도만 확인했던 기억이 있습니다.

 

그래서 옵티마이저가 인덱스를 어떻게 확인해서 쿼리에 적용하는지 더 나은 인덱스를 태우려면 어떻게 해야하는지 궁금해서

MySQL 옵티마이저에 대해 공부 및 정리해보려고 합니다.

(Real MySQL을 보고 공부한 내용을 다룹니다.)

쿼리의 실행 절차

1. SQL 파싱

사용자로 부터 쿼리요청이 들어오면 'SQL 파서 모듈'에서 문장을 적절하게 쪼개어 MySQL서버가 이해할 수 있는 수준으로 분리합니다.

(파싱 트리 작업)

2. 최적화 및 실행계획 수립 (옵티마이저 일하는 단계)

파싱 트리 작업에서 분리된 내용을 확인하여 적절한 테이블과 인덱스를 선택합니다.

이 단계에서 불필요 조건을 제거하고 복잡한 연산을 단순화 합니다.

임시테이블 필요유무도 이 단계에서 결정됩니다.

3. 레코드 읽기

결정된 인덱스를 사용해 스토리지 엔진으로 부터 데이터를 읽어옵니다.

 

옵티마이저의 기본적인 데이터 처리 방식

옵티마이저가 언제 풀을 타는지, 언제 인덱스를 타는지

각각의 데이터 조회방식과 그 방식들이 선택되는 이유에 대해서 알아보겠습니다. 

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

• 테이블의 레코드 수가 적어서 인덱스보다 풀 테이블 스캔이 더 빠를 때 ( 보통 테이블이 페이지 1개로 구성되어 있는 경우 )
• WHERE나 ON 조건에서 인덱스를 이용할 수 없을 때
• 인덱스 레인지 스캔을 하더라도 조건 일치 레코드가 너무 많을 때

풀 테이블 스캔, 풀 인덱스 스캔이 시작되면 처음 몇개의 데이터 페이지는 포그라운드 스레드에서 읽고 특정 시점부터는 백그라운드 스레드에서 작업을 하게됩니다.

백그라운드 스레드의 작업 시점부터는 점진적으로 읽어오는 페이지 수를 4개 8개.. 점점 증가시켜 최대 64개 까지 버퍼풀에 한번에 저장해둡니다.

이렇듯 InnoDB엔진은 특정 테이블의 데이터가 연속해서 읽히면 백그라운드 스레드에서 앞으로 필요할 것 같은 데이터들을 예상해서 미리 디스크에서 버퍼풀로 데이터를 가져다 두고 이를 리드 어헤드 작업이라고 합니다.

임시테이블 (Internal Temporary Table)에 대해

MySQL엔진이 스토리지 엔진으로 부터 데이터를 정렬(ORDER BY)하거나 그룹핑(GROUP BY)할 때 내부적인 임시 테이블을 사용합니다.

임시테이블은 최초 메모리에 생성되었다가 테이블의 크기가 커지면 디스크로 옮겨지며, 쿼리의 처리가 완료되면 자동으로 삭제됩니다.

메모리에 생성되는 임시 테이블은 TempTable (기본값 1GB)이라는 스토리지 엔진을 사용하고,

디스크에 저장되는 임시 테이블은 InnoDB스토리지 엔진을 사용합니다.

 

임시 테이블이 메모리에 생성되어 있는지, 디스크에 생성되어 있는지 확인하기 위해서는 아래와 같은 명령어로 확인 가능합니다.

Created_tmp_disk_tables : 디스크에 존재하는 임시 테이블 수

Created_tmp_files : 임시 테이블 생성 횟수

Created_tmp_tables : 디스크 + 메모리에 존재하는 임시 테이블 수 

SHOW SESSION STATUS LIKE 'Created_tmp%';

 

 

쿼리에서 임시테이블이 사용되는 경우

• ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리 (임시 유니크 인덱스 사용)
• ORDER BY나 GROUP BY에 명시된 컬럼이 JOIN의 순서 상 첫번째 테이블이 아닌 쿼리 (임시 유니크 인덱스 사용)
• DINSTINCT나 ORDER BY가 동시에 있거나 DISTINCT가 인덱스로 처리되지 못하는 쿼리 (임시 유니크 인덱스 사용)
• UNION, UNION DISTINCT가 사용된 쿼리 (select_type 컬럼이 UNION RESULT인 경우) (임시 유니크 인덱스 사용)
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리 (임시 유니크 인덱스 사용 X)

임시 테이블은 메모리에 먼저 생성되지만 예외적으로 바로 디스크에 생성되는 경우

• UNION이나 UNION ALL에서 SELECT되는 컬럼 중 길이가 512바이트 이상인 컬럼이 있는 경우
• GROUP BY, DISTINCT 컬럼 중 길이가 512바이트 이상인 컬럼이 있는 경우
• 메모리 임시 테이블의 크기가 temporable_max_ram 변수보다 큰 경우

 

옵티마이저와 ORDER BY 처리

EXPLAIN 명령어로 실행 계획을 확인하다 보면 EXTRA 컬럼에  using filesort라는 문구를 종종 확인 할 수 있습니다.

이는 filesort 방식으로 정렬처리가 되었다는 뜻인데, 즉 인덱스를 타지 않는 방식으로 정렬된 것입니다.

옵티마이저는 크게 3가지 방법으로 정렬을 처리합니다.

 

추가로 ORDER BY에서 인덱스를 사용하게 되면 MySQL에서 '스트리밍 방식'으로 데이터를 처리하여 빠른 응답이 가능하지만

인덱스를 사용하지 않는 경우에는 '버퍼 방식'으로 데이터를 전달하기에 속도에 차이가 있습니다.

(하지만 JDBC는 기본적으로 '버퍼 방식'을 사용하기에 MySQL에서 스트리밍 방식으로 전달해주어도 JDBC를 통해 응답받게 되면 결국 '버퍼 방식'입니다.)

1. 인덱스를 이용한 처리

인덱스를 이용한 정렬 처리입니다.

EXTRA 컬럼에 별도 내용이 표기되지 않습니다.

인덱스를 통해 읽는 경우에는 실제 인덱스의 값이 정렬되어 있기에 순서대로 데이터를 읽어오면 됩니다. (정렬을 위한 별도 작업 X)

 

B-Tree인덱스에서만 유효하고,

ORDER BY에 명시된 컬럼이 드라이빙 테이블에 속하고, 인덱스가 생성된 순서대로 ORDER BY의 정렬조건에 있어야합니다.

또한 WHERE조건의 첫번째 컬럼과 ORDER BY는 같은 인덱스를 탈 수 있어야 합니다.

여러 테이블이 조인되는 상황에서는 Nested-Loop 방식에서만 유효합니다.

2. Using filesort를 이용한 처리

filesort 방식은 정렬 작업이 쿼리 실행 시 실행되므로 레코드수가 적으면 인덱스 정렬보다는 빠르지만

레코드가 많다면 속도가 느립니다.

 

filesort 방식을 사용하는 시점은 아래와 같습니다.

• 정렬 기준이 많아서 모든 요건별 인덱스 생성이 불가능한 경우
• GROUP BY의 결과나 DISTINCT 처리결과를 정렬해야하는 경우
• 임시테이블의 결과를 정렬해야하는 경우 (union등)
• 랜덤하게 레코드를 가져와야하는 경우

MySQL은 정렬 시 정렬 버퍼(sort buffer)라는 메모리 공간을 사용하는데,

버퍼의 크기보다 많은 양의 데이터를 읽을때는 버퍼에 담지 못한 데이터를 임시로 디스크에 기록해둡니다.

즉 모든 데이터를 정렬하기 위해서는 계속해서 디스크 읽기가 발생하게됩니다.

(이 작업을 Multi Merge라고 하며 이 횟수는 Sort_merge_passes 변수에 기록되며 SHOW STATUS VARIABLE 명령어로 확인 가능합니다.)

 

filesort 시 옵티마이저가 사용하는 정렬 방식

 

1. sort_key, row_id (투 패스 방식)

정렬하는 key와 row_id만 가져와서 정렬하는 방식입니다.

클라이언트 쿼리에서 정렬key와 row_id 외 다른 컬럼이 있다면 정렬 후 추가로 필요한 컬럼을 다시 디스크에서 가져와야하기에 추가 작업이 필요합니다.

BLOB이나 TEXT 컬럼이 있다면 MySQL은 반드시 해당 방식으로 정렬합니다.

정렬할 레코드가 적다면 효율적인 방식입니다.

 

2. sort_key, additional_fields (싱글 패스 방식)

정렬하는 key와 레코드 전체를 가져와서 정렬하는 방식입니다.

레코드의 컬럼들은 고정사이즈로 메모리에 저장됩니다.

정렬할 레코드가 많다면 효율적인 방식입니다.

 

3. sort_key, packed_additional_fields (싱글 패스 방식)

정렬하는 key와 레코드 전체를 가져와서 정렬하는 방식입니다.

레코드의 컬럼들은 가변사이즈로 메모리에 저장됩니다.

정렬할 레코드가 많다면 효율적인 방식입니다.

 

옵티마이저에서 EXTRA 컬럼에 나타나는 file sort 방식은 크게 2가지로 구분됩니다.

2-1. Using filesort (조인에서 드라이빙 테이블만 정렬)

• 드라이빙 테이블의 컬럼만으로 ORDER BY 절의 조건 작성

드라이빙 테이블만으로 ORDER BY에 조건을 주게되면

먼저 드라이빙 테이블에서 데이터를 가져온 뒤 검색결과를 ORDER BY의 조건을 기준으로 filesort합니다.

2-2. Using temporary; Using filesort (조인에서 조인결과를 임시테이블로 저장 후 정렬)

• 임시 테이블에 저장된 데이터를 정렬할 때

쿼리에 따라서 join된 테이블들이 임시 테이블에 저장될 수 있는데,

마지막에 쿼리 결과를 보여주기 위해 정렬 할 때 그 임시테이블을 정렬해야하기에 가장 느린 방법입니다.

임시테이블에 저정된 데이터를 정렬하게 될 때 Using tempoary가 사용됩니다.

 

마지막으로 아래와 같은 명령어를 통해 정렬 관련한 MySQL의 상태를 확인 할 수 있습니다

SHOW STATUS LIKE 'Sort%';

 

옵티마이저와 GROUP BY 처리

GROUP BY또한 ORDER BY와 같이 인덱스를 사용할수도 사용하지 못할수도 있습니다.

사용하지 못한다면 임시 테이블을 사용합니다.

(ORDER BY와 동일하게 인덱스를 사용하면 스트리밍 방식, 사용하지 않으면 버퍼 방식입니다.)

추가로 HAVING 에 사용된 조건절은 인덱스를 탈 수 없습니다.

인덱스 스캔을 이용하는 GROUP BY (타이트 인덱스 스캔)

• 조인의 드라이빙 테이블에 속한 컬럼만을 조건으로 GROUP BY 조건을 주었을 때

GROUP BY가 인덱스를 통해 처리되어도 그룹 함수 등의 그룹값을 처리해야 할 때에는 임시테이블이 필요할때도 있습니다.

ORDER BY와 동일하게 인덱스를 통해 처리되면 EXTRA 컬럼에 별도의 문구가 뜨지 않습니다.

 

루스 인덱스 스캔(Loose index scan)을 이용하는 GROUP BY

• 단일 테이블에서만 수행되는 GROUP BY에서만 적용됩니다.
• Prefix Index (컬럼 앞쪽 일부만 사용하는 인덱스)에서는 사용이 불가능합니다.

Loose index scan은 인덱스의 레코드를 건너뛰며 필요한 레코드들을 가져오는 방식입니다.

분포도가 좋지 않을수록 좋은 성능을 낼 수 있습니다.

이때는 EXTRA컬럼에 Using index for group-by 라고 표시됩니다.

 

임시 테이블을 이용하는 GROUP BY

• 인덱스를 사용하지 못할 때 사용되는 방식입니다.

EXTRA 컬럼에 Using Temporary라는 문구가 표시됩니다.

MySQL에서는 GROUP BY가 필요할 때 GROUP BY절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어 중복제거와 집합함수 연산을 수행합니다.

그 후 join의 결과를 가져와 한건씩 임시테이블에서 중복체크를 하며 별도의 정렬작업 없이 INSERT, UPDATE 작업을 진행 후 반환합니다.

 

DINSTINCT 처리

DISTINCT는 임시테이블과 함께 사용될 때에도 Using Temporary 문구가 나오지 않습니다.

추가로 SELECT에서 유니크한 레코드만 가져오려고 할 때 DISTINCT를 사용하면 GROUP BY와 동일한 방식을 통해 MySQL에서 데이터를 가져옵니다.

 

아래와 같이 DISTINCT를 사용하면 user_id만 DISTINCT되서 나오지 않고 user_id, user_name SELECT절의 모든 컬럼에 대해서 적용됩니다.

DISTINCT는 함수가 아니기에 뒤에 괄호를 자체적으로 제거합니다.

SELECT DISTINCT(user_id), user_name FROM EMP

 

하지만 아래와 같이 집합함수 내에서 사용한다면 해당 컬럼에 대해서만 적용됩니다.

SELECT COUNT(DISTINCT user_id) FROM EMP

 

 

조인 버퍼 (Join Buffer)

옵티마이저에서 Join을 사용할 때 어떠한 방식으로도 Full Table Scan이나 Index Full Scan을 피할 수 없다면

드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 캐시한 메모리를 Join하는 형태로 처리합니다.

이 때 사용하는 메모리 캐시를 조인 버퍼(Join Buffer)라고 합니다.

조인 버퍼는 사용후 바로 해제됩니다.

추가로 MySQL구조에서 조인처리는 MySQL엔진에서 처리하지만 실제 데이터를 읽는곳은 스토리지 엔진입니다.

 

Nested Loop Join (조인 버퍼 사용 X)

Nested Loop Join은 드라이빙 테이블의 레코드를 한건 읽어서 드리븐 테이블의 일치하는 레코드들을 찾아서 조인을 수행하는 방식입니다. (일반적인 2중 for문 방식이라고 생각하시면 편할 것 같습니다.)

Join을 위한 인덱스나 정렬과 같은 부가적인 기능을 따로 사용하지 않습니다.

 

Block Nested Loop Join (조인 버퍼 사용 O)

MySQL에서 사용되는 대부분의 Join방식은 Block Nested Loop Join입니다.

Join의 연결이 되는 조건 컬럼에 인덱스가 있는 경우 사용되는 방식입니다.

 

MySQL은 조인 대상 테이블 중 하나(드라이빙 테이블 혹은 레코드가 작은 테이블)로부터 레코드를 읽어서 그대로 조인버퍼에 버퍼링합니다.

즉 드라이빙 테이블의 레코드를 읽어서 바로 join하지 않고 조인버퍼에 조인 대상을 버퍼링을 해둡니다.

조인버퍼가 가득 차면 그 때 MySQL엔진은 버퍼링된 레코드를 스토리지엔진으로 한번에 요청합니다.

 

EXTRA 컬럼을 통해 Join방식을 확인 할 수 있습니다 (8.0.20 버전 부터는 Block Nested Loop Join이 사용되지 않고 Hash Join 방식이 사용되어 block nested loop 방식 및 EXTRA 컬럼에 해당 방식이 나오지 않을 수 있습니다.)

 

인덱스 머지 (index_merge)

대부분 옵티마이저는 테이블당 하나의 인덱스만 사용하도록 실행계획을 수립합니다.

하지만 종종 하나의 테이블에 대해 2개 이상의 인덱스를 사용해서 쿼리를 사용합니다.

쿼리에 사용된 각 조건이 서로다른 인덱스를 사용 할 수 있고, 그 조건을 만족하는 레코드가 많을것으로 예상될 때 MySQL서버는 인덱스 머지를 사용합니다.

 

Index_merge_intersection (교집합)

EXTRA 컬럼에 Using intersect 가 표시된다면 '인덱스 머지 교집합'이 사용된겁니다.

쿼리가 여러개의 인덱스를 각각 검색하여 그 결과의 교집합만 반환했다는 의미입니다.

 

 

 

Index_merge_union (합집합)

WHERE절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR연산자로 연결된 경우에 사용하는 최적화 방식입니다.

각 조건에서 사용할 수 있는 인덱스의 각각의 검색결과를 Union 알고리즘으로 병합했다는 의미입니다.

두 인덱스에서 중복된 레코드가 가져와 질 수 있는데 테이블의 PK와 우선순위큐를 이용해서 데이터를 병합하기에 별도의 중복 제거 작업은 하지 않습니다.

 

Index_merge_sort_union (정렬 후 합집합)

인덱스 머지 작업 중 정렬이 필요한 경우는 반드시 존재합니다. (pk로 필터링 불가 등)

그럴 경우에는 중복 제거를 위해 정렬을 해야하는데 Index_merge_sort_union 방식의 sort_union 알고리즘을 사용하며

EXTRA 컬럼에 sort_union 방식이 사용되었다고 표기됩니다.

 

세미 조인 (semijoin)

실제 join을 수행하지 않고, 다른 테이블에서 조건에 일치하는 레코드의 유무를 체크하는 형태의 쿼리를 의미합니다.

 

아래와 같은 쿼리에서 원래 MySQL은 employees 테이블을 조회하고 그 후 조건에 있는 IN절의 결과와 비교해서 데이터를 처리합니다.

하지만 실제로 데이터를 조회 할 때에는 semijoin 최적화 기능으로 인하여 IN 절의 조건 dept_emp 테이블을 먼저 조회하고 employees 테이블과 비교합니다.

 

테이블 풀-아웃 최적화(Table Pull-out)

• 세미조인 서브쿼리에서만 사용 가능합니다.
• 서브쿼리 부분이 유니크 인덱스, 프라이머리 키 룩업으로 결과가 1건 일 때 사용 가능합니다.
• 서브 쿼리의 테이블을 아우터 쿼리로 가져와서 최적화를 수행하기에, 모든 서브쿼리 테이블이 아우터 쿼리로 가져와 질 수 있다면, 서브쿼리 자체는 사라집니다.
• '서브쿼리를 최대한 풀어서 사용하라'는 튜닝 가이드가 많은데, Table Pull-out최적화는 해당 가이드를 그대로 실행합니다.
• EXTRA 컬럼에 별도로 표기되지 않습니다. 실행계획의 id값으로 해당 최적화 사용 여부를 확인 가능합니다.

세미조인 상황에서 서브쿼리에 사용된 테이블을 아우터 쿼리로 꺼낸 후 쿼리를 join쿼리로 재작성하는 최적화 방식입니다.

아래와 같은 세미조인 형태의 쿼리의 실행계획을 확인해 보았을 때 실행계획의 id값이 동일하다면 서브쿼리 방식이 아닌 join방식으로 처리되었다는 뜻이기에 결국 테이블 풀-아웃 최적화 방식이 사용되었다는 의미로 이해할 수 있습니다.

 

보다 정확한 확인을 위해서는 SHOW WARNINGS 명령어로 옵티마이저가 재작성한 쿼리를 직접 확인하는 방법도 있습니다.

 

 

퍼스트 매치 (FirstMatch)

• 서브쿼리에서 하나의 레코드만 검색되면 이후 탐색을 멈추는 방식이기에, 그 서브쿼리가 참조하는 모든 아우터 테이블이 조회된 후 실행됩니다.
• EXTRA 컬럼에는 FirstMatch(table-N)이 표시됩니다.
• 최적화는 상관 서브쿼리에도 적용 가능합니다.
• GROUP BY나 집합함수가 사용된 최적화에서는 사용 할 수 없습니다.

In형태의 세미조인을 EXIST 형태로 튜닝한 것과 비슷한 방식으로 최적화를 진행합니다.

외부 테이블의 각 행에 대해 서브쿼리의 테이블과 비교해가며 조건이 맞는 행을 찾으면 더 이상 서브쿼리의 테이블을 조회하지 않고

외부 테이블의 다른 행을 기준으로 다시 서브쿼리의 테이블과 비교하는 최적화 방식입니다.

 

루스 스캔(Loose scan)

• EXTRA 컬럼에서 LooseScan으로 표시됩니다.

Loose Index scan으로 서브쿼리 테이블을 읽고, 그 다음 아우터 테이블을 드리븐 테이블로 사용해서 조인을 수행합니다.

아래와 같은 쿼리에서 dept_no로 그룹핑해서 서브쿼리의 테이블의 데이터를 읽어온 다음 departments 테이블과 join을 수행합니다.

 

 

 

구체화 (Materialization)

• 사용된 임시테이블의 select_type에는 Materlized 라고 표시됩니다.
• 다른 서브쿼리 최적화와 다르게, 서브쿼리 내에 GROUP BY절이 있어도 사용 가능합니다.
• In 절에서 서브쿼리는 상관 서브쿼리가 아니어야합니다.

세미조인에 사용된 서브쿼리를 임시테이블을 사용하여 통째로 구체화하여 최적화 하는 방식입니다.

사용된 임시테이블의 select_type에는 Materlized 라고 표시됩니다.

 

중복제거(Duplicated Weed-out)

• 상관 서브쿼리라도 사용 가능합니다.
• 서브쿼리가 GROUP BY, 집합함수가 사용되었으면 사용 할 수 없습니다.
• 서브쿼리의 테이블을 JOIN으로 처리하기에 최적화 할 수 있는 방법이 많습니다.

세미조인 서브쿼리를 INNER JOIN 쿼리로 바꾸어 실행하고 마지막에 중복된 레코드를 제거하는 방식입니다.

INNER JOIN + GROUP BY를 실행하는것과 동일한 작업으로 쿼리를 처리합니다.

임시테이블을 사용하기에 start tempoary , end tempoary 문구가 사용되며 그 두 구간 사이에서 중복제거 최적화가 일어났다고 생각하면 됩니다.

 

 

컨디션 팬아웃 (condition_fanout_filter)

드라이빙 테이블에서 주어진 조건들에 대해서 드리븐 테이블의 행들이 얼마나 일치할지 예측하는 컬럼입니다.

옵티마이저가 정확한 예측을 할 수 있다면 더 빠른 실행계획을 만들 수 있고,

condition_fanout_filter 최적화 옵션이 활성화 되어있다면 아래 두 경우에 대해서 보다 정확한 예측을 할 수 있습니다.

• WHERE조건에 사용된 컬럼에 인덱스가 있는 경우
• WHERE조건에 사용된 컬럼에 히스토그램이 존재하는 경우

옵티마이저는 레인지 옵티마이저, 히스토그램, 인덱스의 통계 정보, 추측 순서대로 우선도를 두어 예측을 진행합니다.

 

파생 테이블 머지 (derived_merge)

아래와 같이 FROM절에 사용되는 서브쿼리를 외부쿼리와 병합하는 최적화입니다.

SHOW WARNINGS 명령어로 최적화된 쿼리를 확인 할 수 있습니다.

하지만 몇몇 상황에 대해서는 derived_merge가 사용될 수 없기에 아래와 같은 경우에는 수동으로 병합하는것이 좋습니다.

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

 

스킵 스캔 (skip scan)

인덱스를 사용하려면 인덱스가 생성된 순서대로 쿼리를 작성하여야합니다.

인덱스 스킵 스캔은 제한적이긴 하지만 앞서 언급한 인덱스의 제약을 뛰어 넘을 수 있는 최적화 방법입니다.

즉 인덱스의 후행컬럼만을 조건으로 사용해도 인덱스를 사용하여 쿼리 개선을 할 수 있습니다.

대신 인덱스의 선행컬럼이 소수의 유니크한 값을 가질때만 적용됩니다.

 

해시 조인 (Hash Join)

해시조인은 Best Throughput 전략에 적합하고 네스티드 루프 조인은 Best Response-time 전략에 적합합니다.

그렇기에 일반적인 웹서비스에서는 네스티드 루프 조인이 더 좋은 방식일때가 많지만,

분석과 같은 서비스에서는 해시조인이 더 좋은 방식일 수 있습니다.

 

MySQL에서는 조인 컬럼이 인덱스가 없거나, 조인 대상 테이블 중 일부의 레코드건수가 매우 적을 때에만 해시조인 알고리즘을 사용합니다.

해시조인은 빌드(build)단계와 프로브(probe)단계로 나뉘어집니다.

 

빌드 단계 (build phase)

조인 대상 테이블 중 레코드 건수가 적어 해시테이블로 만들기 적합한 테이블을 골라서 메모리에 해시테이블을 생성하는 작업입니다.

만들어진 해시 테이블은 조인버퍼에 저장됩니다.

 

프로브 단계 (probe phase)

나머지 테이블의 레코드를 읽어 만들어둔 해시테이블과 일치하는 레코드를 찾는 과정입니다.

이 때 나머지 테이블을 프로브 테이블이라 합니다.