[ Real MySQL 8.0. 1 ] 7주차 스터디 (9장)
위 포스트는 "Real MySQL 8.0 1" 책 Chapter 9.1~9.3.1장 내용을 학습한 후 정리한 글입니다.
Real MySQL 8.0 1권 : 알라딘
《Real MySQL》을 정제해서 꼭 필요한 내용으로 압축하고, MySQL 8.0의 GTID와 InnoDB 클러스터 기능들과 소프트웨어 업계 트렌드를 반영한 GIS 및 전문 검색 등의 확장 기능들을 추가로 수록했다.
www.aladin.co.kr
Chapter 09. 옵티마이저와 힌트
9.1 개요
9.1.1 쿼리 실행 절차
MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다.
1. SQL 파싱(Parsing)
- SQL 문장이 문법적으로 잘못됐다면 걸러지는 단계
- 사용자가 보낸 SQL 문장을 MySQL 서버가 이해할 수 있는 내부 구조로 변환
- MySQL 서버의 "SQL 파서"라는 모듈로 처리, 이 단계에서 "SQL 파스 트리"가 만들어짐
- MySQL 서버는 SQL 문장 그 자체가 아니라, SQL 파스 트리를 이용해 쿼리를 실행
2. 최적화 및 실행 계획 수립(Optimization)
MySQL 서버의 "옵티마이저"에서 처리하며, 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 아래의 내용을 처리한다.
- 불필요한 조건 제거, 복잡한 연산의 단순화
- 조인의 순서 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
- 정렬(SORT), GROUP BY, LIMIT 등의 처리가 필요한지 판단
3. 실행 계획 (Execution)
- 옵티마이저에서 수립한 계획에 따라 스토리지 엔진으로부터 데이터를 읽어 결과를 생성
- 레코드를 조인하거나 정렬하는 작업 수행
- MySQL 엔진 + 스토리지 엔진 동시 참여
1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 읽고, 어떤 인덱스를 이용해 테이블을 읽을지 선택
3.두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴
9.1.2 옵티마이저의 종류
옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당하고,
방법으론 규칙 기반 최적화 방법(Rule-based optimizer:RBO), 비용 기반 최적화 방법(Cost-based optimizer:CBO)이 있다.
- 규칙 기반 최적화
- 미리 정의된 규칙(우선순위)에 따라 실행 계획을 결정
- 통계 정보(레코드 수, 인덱스 선택도 등)를 사용하지 않음
- 항상 일정한 실행 계획을 수립하지만, 최적의 계획은 아닐 수 있음
- MySQL에서는 현재 사용되지 않음 (과거 일부 DBMS에서 사용)
- 비용 기반 최적화
- 다양한 실행 계획을 시뮬레이션하고, 각각의 비용(cost) 을 계산
- 테이블의 행 수, 인덱스 선택도, 디스크 I/O 예상 횟수 등을 바탕으로 비용을 비교
- 비용이 가장 적은 실행 계획을 선택
- MySQL은 기본적으로 비용 기반 옵티마이저를 사용함
9.2 기본 데이터 처리
9.2.1 풀 테이블 스캔과 풀 인덱스 스캔
풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 것을 의미한다.
MySQL 옵티마이저의 풀 테이블 스캔 선택 조건
- 테이블의 레코드 건수가 너무 작아서, 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우 (일반적으로 페이지 테이블이 1개로 구성된 경우)
- WHERE절이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 (인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
대부분의 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있는데,
MySQL의 경우, 스토리지 엔진에 따라 차이가 있다.
MyISAM 엔진은 일반적으로 페이지를 디스크에서 하나씩 순차적으로 읽지만,
InnoDB 엔진은 Read-Ahead(선행 읽기)기능을 통해 향후 필요할 것으로 예측되는 여러 페이지를 버퍼 풀에 미리 로딩한다.
즉, MySQL이 항상 "하나씩" 페이지를 읽는다고 생각하는 것은 부분적으로만 맞는 설명이며,
InnoDB의 경우는 훨씬 더 지능적인 페이지 읽기 전략을 사용한다.
InnoDB 스토리지 엔진에서의 풀 테이블 스캔
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면,
백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작된다.
** 리드 어헤드(Read ahead)
- 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서, 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미
MySQL 서버에서는 innodb_read_ahead_threshold 시스템 변수를 이용해서
InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다.
InnoDB의 처리 흐름:
- 초기 몇 페이지는 포그라운드 스레드(클라이언트 스레드)가 직접 읽는다
- 일정 시점 이후에는 백그라운드 스레드로 전환
- 백그라운드 스레드는 4~8페이지씩 묶어서 읽기 시작하고, 이후 점점 묶음 크기를 증가시킨다
- 이는 디스크 I/O를 줄이고, 버퍼 풀 활용도를 높임
풀 인덱스 스캔
풀 인덱스 스캔은 인덱스를 처음부터 끝까지 스캔하는 것을 의미한다.
SELECT COUNT(*) FROM employees;
위와 같이 단순히 레코드의 건수만 필요로 하는 쿼리를 사용하는 경우 풀 인덱스 스캔을 사용한다.
이는 디스크 읽기 횟수를 줄이기 위해 용량이 작은 인덱스를 선택하는 것이 도움되기 때문이다.
일반적으로 인덱스는 테이블의 2~3개 칼럼만으로 구성되는 특징을 가지고 있기에, 테이블 자체보다는 용량이 작아 더 도움된다.
다만 레코드에만 있는 칼럼이 필요한 쿼리의 경우에는 풀 테이블 스캔을 사용하게 된다.
9.2.2 병렬 처리
MySQL 8.0부터는 MySQL 서버에서도 쿼리의 병렬 처리 가 가능해졌다. (다만, 용도가 한정되어 있기는 하다)
** 쿼리의 병렬 처리
하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미
이는 innodb_parallel_read_threads 라는 시스템 변수를 이용해서
하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있다.
쿼리의 병렬 처리 (MySQL 8.0 버전 기준)
-- InnoDB 병렬 읽기 비활성화: 디스크에서 데이터를 읽을 때 1개의 스레드만 사용
SET SESSION innodb_parallel_read_threads = 1;
SELECT COUNT(*) FROM salaries;
-- InnoDB 병렬 읽기 활성화: 디스크에서 데이터를 읽을 때 최대 2개의 스레드를 사용
SET SESSION innodb_parallel_read_threads = 2;
SELECT COUNT(*) FROM salaries;
-- InnoDB 병렬 읽기 활성화: 디스크에서 데이터를 읽을 때 최대 3개의 스레드를 사용
SET SESSION innodb_parallel_read_threads = 3;
SELECT COUNT(*) FROM salaries;
MySQL 8.0에서는 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리에만 병렬로 처리할 수 있다.
그리고 2, 3, 4처럼 2 이상으로 스레드를 설정해야 병렬 읽기를 InnoDB가 시도할 수 있다.
위의 쿼리 실행 결과를 보면 병렬 처리용 스레드 개수가 늘어날수록, 쿼리 처리에 걸리는 시간이 줄어든다.
하지만 병렬 처리용 스레드 개수를 아무리 늘리더라도,
서버에 장착된 CPU 코어 개수를 넘어서는 경우는 오히려 성능이 떨어질 수 있다.
9.2.3 ORDER BY 처리 (Using filesort)
정렬을 처리하는 방법은 인덱스를 사용하거나, 쿼리가 실행될 때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.
MySQL 정렬 특성을 이해하면 쿼리를 튜닝할 때 어떻게 하면 조금이라도 더 빠른 쿼리가 될지 쉽게 판단할 수 있다.
| 장점 | 단점 | |
| 인덱스 이용 | - INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬되어 있기 때문에, 순서대로 읽기만 하면 되어 매우 빠름 | - INSERT, UPDATE, DELETE 작업 시 부가적 인덱스 추가/삭제 작업이 필요해서 속도가 느림 - 인덱스 때문에 디스크 공간이 더 많이 필요 - 인덱스 개수가 늘어날 수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요 |
| Filesort 이용 | - 인덱스를 생성하지 않아도 됨 - 정렬해야 할 레코드가 많지 않으면, 메모리에서 Filesort가 처리되므로 충분히 빠름 | - 정렬 작업이 쿼리 실행 시 처리되므로, 레코드 대상 건수가 많아질 수록 쿼리의 응답 속도가 느림 |
레코드를 정렬하기 위해 항상 "Filesort"라는 정렬 작업을 거쳐야 하는 것은 아니다.
다만 다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능하다.
- 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
- GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
- UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
- 랜덤하게 결과 레코드를 가져와야 하는 경우
인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는
실행 계획의 Extra 칼럼에 "Using filesort" 메시지가 표시되는지 여부로 판단할 수 있다.
9.2.3.1 소트 버퍼 (Sort buffer)
- 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이때의 메모리 공간을 의미함
- 정렬이 필요한 경우에만 할당
- 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size 시스템 변수로 설정
- 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가
- 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납
정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 클 경우, 정렬에서 문제가 발생한다.
정렬 대상이 수십 MB ~ 수 GB 이상이면 메모리에 다 못 담기에
디스크에 임시 파일을 만들어 정렬 작업을 나눠서 "디스크 기반 정렬"을 수행한다.
이때 사용되는 개념이 멀티 머지(Multi-Merge) 이다!
단계 1: 여러 개의 정렬된 임시 덩어리 생성
- MySQL은 메모리에 올릴 수 있는 만큼 데이터 청크(chunk)를 정렬해서 디스크에 임시 파일로 저장
- 이 단계는 여러 번 반복됨
[Disk File 1: 1, 5, 9] ← 메모리의 sort_buffer로 정렬된 결과
[Disk File 2: 2, 4, 8]
[Disk File 3: 3, 6, 7]
단계 2: Multi-Merge (멀티 머지)
- 디스크에 있는 여러 임시 파일에서 조금씩 데이터를 불러옴
- 각 파일의 가장 작은 값을 비교하면서 하나의 정렬된 결과를 만들어냄
[1, 2, 3, 4, 5, 6, 7, 8, 9] ← 디스크 파일들을 병합해 최종 정렬
즉, 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하는 것을 멀티 머지라고 한다.
멀티 머지는 디스크를 반복해서 읽고 비교하는 작업이므로 I/O 비용이 크다.
이때 sort_buffer_size는 멀티 머지를 줄이는 데는 효과적이다.
정렬할 데이터를 메모리에 더 많이 담을 수 있으니,
→ 임시 정렬 청크의 수가 줄고,
→ 멀티 머지 횟수도 줄어들 수 있음
→ 결과적으로 디스크 I/O도 줄어듦
다만 너무 크게 크기를 설정하면 성능이 더 좋아지지는 않기에, 현실적으로는 적절한 크기로 조정해야한다.
- 멀티 머지 발생 여부 확인: 값이 0 이상이면 멀티 머지가 수행된 정렬이 있었다는 뜻
SHOW STATUS LIKE 'Sort_merge_passes';
9.2.3.2 정렬 알고리즘
레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담아서 정렬할지, 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라
싱글 패스와 투 패스 정렬로 나눌 수 있다.
쿼리가 어떤 정렬 모드를 수행하는지는 다음과 같이 옵티마이저 트레이스 기능으로 확인할 수 있다.
SELECT * FROM information_schema.optimizer_trace
...
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
정렬 모드는 아래 3가지로 나뉘어진다.
- <sort_key, row_id> : 정렬 키와 레코드의 로우 아이디만 가져와서 정렬
- <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬. 레코드 칼럼은 고정 사이즈로 메모리에 저장
- <sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬. 레코드 칼럼은 가변 사이즈로 메모리에 저장
첫 번째 방식은 투 패스 정렬방식이고 나머지 두 방식은 싱글 패스 방식이다.
1️⃣ 싱클 패스 정렬 방식 (Single-pass sort)
- 정렬이 필요한 모든 데이터를 한 번에 소트 버퍼(Sort Buffer)에 적재하여 정렬과 SELECT 대상 추출을 동시에 수행하는 방식
- 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보임
동작 방식
- 소트 버퍼에 정렬에 필요한 칼럼뿐만 아니라, SELECT 대상의 모든 칼럼을 함께 넣고 정렬을 수행
- 정렬된 결과를 그대로 클라이언트에 반환하면 끝난다. → 즉, 테이블을 한 번만 읽는다.
장점
- 테이블 접근이 한 번이므로 빠르다.
- 최근 MySQL 버전에서는 일반적으로 이 방식을 더 많이 사용한다.
단점
- 정렬 대상이 많거나, 각 레코드가 클 경우, 소트 버퍼를 많이 소모함
- 버퍼 공간 초과 시 디스크 I/O 발생 가능
사용 제한 조건
- SELECT 대상 중에 BLOB, TEXT와 같은 큰 컬럼 타입이 포함되거나,
- 정렬 대상 데이터의 크기가 max_length_for_sort_data 시스템 변수보다 클 경우
위 조건을 만족하면, 옵티마이저는 투 패스 정렬 방식을 선택하게 된다.
🧪 참고: max_length_for_sort_data
- MySQL 시스템 변수로, 싱글 패스 정렬 시 한 레코드당 소트 버퍼에 담을 수 있는 최대 바이트 수를 지정한다.
- 기본값은 1024바이트
- 이 값을 초과하면, 옵티마이저는 자동으로 투 패스 정렬 방식을 사용한다.
SHOW VARIABLES LIKE 'max_length_for_sort_data';
2️⃣ 투 패스 정렬 방식 (Two-pass sort)
- 정렬과 데이터 추출을 두 번에 나눠서 수행하는 방식
- 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적
- 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행
동작 방식
- 1차 패스:
- 소트 버퍼에 정렬 대상 칼럼과 프라이머리 키(PK)만 담아서 정렬 수행
- 2차 패스:
- 정렬된 PK를 이용해 테이블을 다시 읽고, SELECT 대상 칼럼을 가져옴 → 결국 테이블을 두 번 읽는다.
| 구분 | 싱글 패스 정렬 | 투 패스 정렬 |
| 테이블 접근 횟수 | 1회 | 2회 |
| 소트 버퍼 사용량 | 많음 (모든 칼럼 포함) | 적음 (정렬 칼럼 + PK만 포함) |
| 성능 | 빠름 (버퍼 충분 시) | 느림 (두 번 읽음) |
| 사용 조건 | 일반적으로 우선 사용됨 | BLOB, TEXT 포함 또는 데이터 크기 클 경우 |
| 예외 처리 기준 | max_length_for_sort_data | 동일 |
9.2.3.3 정렬 처리 방법
쿼리에 ORDER BY가 사용되면 반드시 아래 3가지 처리 방법 중 하나로 정렬이 처리된다.
인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)한다.
- 인덱스를 이용한 정렬 : 실행 계획의 Extra 컬럼 => 별도 표기 없음
- 조인의 드라이빙 테이블만 정렬 => Using filesort
- 조인 결과를 임시 테이블에 저장 후 정렬 => Using temporary; Using filesort
- 인덱스 이용
- 인덱스를 이용 x : 레코드를 정렬 버퍼에 넣고 정렬 수행
- 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
- 조인이 끝나고 결과 레코드 모두를 가져와서 정렬을 수행
1️⃣ 인덱스를 이용한 정렬
정렬이 필요한 쿼리를 실행할 때, 이미 인덱스에 정렬된 순서로 데이터가 저장되어 있다면 굳이 다시 정렬하지 않아도 된다.
MySQL은 이런 경우 인덱스를 그대로 따라 읽기만 하면 되므로 정렬 비용이 거의 0에 가깝다.
조건
- ORDER BY절의 컬럼이 인덱스의 컬럼 순서와 정확히 일치해야 함
- ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속해야 함 (조인이 사용된 경우엔 드라이빙 테이블)
- WHERE 절에서 사용하는 조건도 인덱스의 앞쪽 컬럼이면 더 효율적
- B-Tree 인덱스가 키 값으로 정렬되어 있는 경우
- 여러 테이블이 조인되는 경우에는 네스티드-루프(Nested-loop) 조인이 사용된 경우
예시
CREATE TABLE employees (
emp_no INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
INDEX idx_hire_date (hire_date)
);
-- 이 쿼리는 인덱스 정렬 사용
SELECT emp_no, first_name, hire_date
FROM employees
ORDER BY hire_date;
- hire_date에 인덱스(idx_hire_date)가 있음
- ORDER BY hire_date는 인덱스 순서와 일치
- 테이블 스캔 시 인덱스만 따라 읽으면 되므로, 정렬이 생략됨
- EXPLAIN 결과: Using index 또는 Using index; Using where가 표시됨
이 경우 hire_date에 이미 인덱스가 있으므로,
MySQL은 Using index 또는 Using index; Using where라고 EXPLAIN에서 보여주며 정렬 작업 자체를 건너뜀
❓ "이거 PK랑 관련 있어야 하는 거 아냐?" → 정확한 설명
아니, 꼭 PK(Primary Key) 가 아니어도 된다. 중요한 건:
- 정렬 대상 컬럼에 대한 인덱스가 존재해야 하고,
- 그 인덱스가 ORDER BY의 컬럼 순서와 정확히 일치해야 하며,
- 조인이라면 해당 테이블이 먼저 읽히는 테이블이어야 한다.
✔️ 단, PK도 B-Tree 인덱스이고 자동으로 정렬되어 있기 때문에,
만약 ORDER BY emp_no 같은 경우에는 당연히 정렬 생략이 가능함 (PK니까)
하지만 이건 PK이기 때문에 가능한 게 아니라,
"정렬 대상 컬럼이 인덱스의 첫 컬럼이고 B-Tree로 정렬돼 있기 때문"이야.
즉, PK든 일반 인덱스든 조건만 맞으면 정렬은 생략 가능해.
2️⃣ 조인의 드라이빙 테이블 만 정렬
** 드라이빙 테이블
- 두 개 이상의 테이블을 조인할 때, MySQL이 가장 먼저 읽는 특정 테이블 (옵티마이저가 판단)
- 조인 쿼리에서 어떤 테이블이 드라이빙 테이블(먼저 읽히는 테이블)인지 아는 방법은 명확하게 EXPLAIN으로 확인
FROM에 먼저 나오는 테이블이 드라이빙 테이블이 된다고 오해할 수 있지만,
실제로는 MySQL 옵티마이저가 성능 기준으로 조인 순서를 자유롭게 재조정한다.
MySQL 옵티마이저는 다음 기준을 참고해서 드라이빙 테이블을 정한다:
- WHERE 조건에 잘 걸리는 테이블 (= 선택도가 높은 조건)
- 작은 테이블
- 인덱스가 잘 잡혀 있는 테이블
- 조인 키의 유니크 여부
- 쿼리 힌트 (STRAIGHT_JOIN 등)
즉, 옵티마이저는 가장 효율적인 쿼리 실행 계획을 고르기 위해 테이블 순서를 자동으로 정한다.
조인 쿼리에서 ORDER BY가 있는데, 이 정렬 조건이 드라이빙 테이블에만 해당되는 컬럼이면,
MySQL은 드라이빙 테이블을 정렬하면서 데이터를 읽고, 나머지 테이블들은 그냥 붙이기만 하면 된다.
즉, 조인 전에 정렬이 끝난다는 의미이며, 굳이 조인 다 하고 나서 정렬을 한 번 더 안 해도 된다.
과정
- 드라이빙 테이블을 조건에 따라 필터링
- 해당 테이블을 ORDER BY 기준으로 정렬
- 정렬된 순서대로 나머지 테이블과 조인
- 최종 결과는 이미 정렬된 상태이므로 추가 정렬 없음
예시(1) 인덱스 사용되는 경우
CREATE TABLE employees (
emp_no INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
INDEX idx_hire_date (hire_date)
);
SELECT e.emp_no, e.first_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_no = d.dept_no
ORDER BY e.emp_no;
여기서 employees 테이블이 드라이빙 테이블이라면, ORDER BY e.emp_no는 드라이빙 테이블만 정렬하면 충분하다.
departments 테이블은 조인만 하면 되므로 추가 정렬이 필요 없다.
- employees 테이블을 emp_no 순서대로 정렬하면서 읽는다.
- 각 레코드에 대해 departments 테이블을 조인한다.
- 조인된 결과를 그냥 그대로 내보낸다.
→ 정렬은 이미 드라이빙 테이블에서 끝났기 때문
예시(2) Filesort 사용되는 경우
SELECT *
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 100001 AND 100010
ORDER BY e.last_name;- ORDER BY절에 명시된 칼럼은 employees 테이블의 PK와 무관 -> 인덱스를 이용한 정렬 x
- ORDER BY절의 정렬 기준 칼럼이 드라이빙 테이블에 포함된 칼럼임을 확인
- 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행
- 그 결과와 salaries 테이블을 조인
1. 인덱스를 이용해 "emp_no BETWEEN 100001 AND 100010" 조건을 만족하는 9건을 검색
2. 검색 결과를 last_name 칼럼으로 정렬을 수행(Filesort) - 인덱스를 이용한 정렬이 아니니까
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴.
3️⃣ 임시 테이블을 이용한 정렬
임시 테이블은 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 할 때 사용된다.
앞서 살펴본 "조인의 드라이빙 테이블만 정렬" 패턴의 쿼리를 제외하고는 항상 조인의 결과를 임시 테이블에 저장하고,
거기서 그 결과를 다시 정렬하는 과정을 거친다.
다음과 같은 상황에서는 임시 테이블을 반드시 사용한다:
- 두 개 이상의 테이블을 조인한 경우
- 정렬 기준이 드라이빙 테이블이 아닌 다른 테이블의 컬럼일 때
- 인덱스를 이용한 정렬이 불가능할 때
해당 방법은 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.
예제
SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary;
- 이 쿼리는 employees와 salaries 테이블을 조인하고 있음
- 정렬 기준은 salaries.salary인데, salaries는 드라이빙 테이블이 아님
- 이 경우 MySQL은 인덱스를 이용한 정렬이 불가능하므로, 조인 결과를 임시 테이블에 저장하고 거기서 다시 정렬함
실행 계획 (EXPLAIN) 결과
Extra: Using temporary; Using filesort
- Using temporary → 임시 테이블 사용
- Using filesort → 메모리 또는 디스크에서 정렬 (인덱스 사용 못 함)
| 상황 | 정렬 처리 방식 | 속도 |
| 인덱스로 정렬 가능 | 인덱스를 바로 사용 | 빠름 |
| 드라이빙 테이블만 정렬 대상일 때 | 메모리에서 정렬 | 보통 |
| 조인 결과 전체를 정렬해야 할 때 | 임시 테이블 + filesort | 느림 |
4️⃣ 정렬 처리 방법의 성능 비교
MySQL에서 정렬이 들어간 쿼리는 단순히 ORDER BY만 본다고 해서 성능이 예측되는 게 아니다.
인덱스 사용 여부, JOIN의 복잡성, LIMIT 존재 여부에 따라 MySQL 옵티마이저는 정렬 처리방식을 선택한다
일반적으로 ORDER BY나 GROUP BY는 WHERE 조건을 만족하는 전체 레코드를 정렬하거나 그룹화 후 LIMIT을 적용할 수 있다.
LIMIT으로 테이블의나 처리 결과의 일부만 가져오기 때문에, 그덕에 MySQL 서버는 처리해야 할 작업량이 줄어든다.
다만 ORDER BY와 LIMIT이 같이 쓰인다고 해서 항상 전체 정렬을 먼저 수행해야 하는 것은 아니다.
인덱스 정렬이 가능한 경우나 LIMIT이 있는 쿼리에서는 전체 정렬 없이도 일부 결과만 효율적으로 가져올 수 있다.
해당 경우에는 스트리밍 방식을 사용하고, 나머지의 경우는 버퍼링 방식을 사용한다.
MySQL은 정렬된 결과를 클라이언트에게 언제, 어떻게 전달할지를 결정하는 아래 두 가지 전략을 가진다.
해당 방식에 따라 결과의 응답 속도와 자원 소비가 완전히 달라진다.
스트리밍 방식
정렬된 데이터를 즉시 클라이언트에 전송하는 방식이다.
즉. 정렬된 결과를 한 줄씩 처리하면서 바로 클라이언트에 보낸다.
이에 따라 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에,
동시에 데이터의 가공 작업을 시작할 수 있다.
[정렬됨]
→ 1번 레코드 → 바로 클라이언트에게 전송
→ 2번 레코드 → 전송
→ 3번 레코드 → 전송
...
- MySQL이 정렬 결과를 전체 다 만들기 전에 일단 먼저 보내기 시작함
- LIMIT이나 빠른 응답이 필요한 쿼리에 유리함
- 인덱스를 활용
- 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간 보장
- 웹 서비스와 같은 OLTP 환경
SELECT * FROM employees ORDER BY hire_date LIMIT 10;
→ 10개만 필요한 거니까 굳이 전체 정렬 안 해도 됨
→ 앞에서부터 정렬해가면서 바로 10개 추출
이 방식은 빠르지만, LIMIT 없이 전체를 보내는 경우는 중간에 데이터 빠질 수도 있고 불안정할 수 있음.
버퍼링 방식
정렬된 결과를 모두 메모리나 디스크에 저장해 놓고, 정렬이 끝난 다음에 클라이언트에게 데이터를 보낸다.
즉 결과를 모아서 일괄 가공해야 하므로 LIMIT과 같은 결과 건수를 제한하는 조건은 성능 향상에 도움이 되지 않는다.
[ WHERE 조건 필터링 ]
↓
[ 전체 결과 임시 테이블에 저장 ]
↓
[ 전체 정렬 (메모리 or 디스크) ]
↓
[ 정렬 완료 후 한꺼번에 전송 ]
- 전체 결과에 대해 정확한 정렬 가능
- 응답 시간 느림 (정렬 완료까지 대기)
- 메모리/디스크 사용량 큼
- LIMIT 있어도 전부 정렬 후 잘라내기에 효과 적음
SELECT * FROM employees ORDER BY salary;
→ 모든 직원의 급여를 정렬해야 하므로, 전 레코드가 정렬 대상
→ Using temporary; Using filesort와 같은 실행 계획 힌트가 뜰 수 있다.
| 항목 | 스트리밍 방식 | 버퍼링 방식 |
| 처리 방식 | 정렬 중 바로 전송 | 전체 정렬 후 전송 |
| 응답 속도 | 빠름 | 느림 |
| 메모리 사용 | 적음 | 많음 (디스크 사용 가능성 있음) |
| 정렬 정확성 | 낮음 (LIMIT에 적합) | 높음 (전체 정렬에 적합) |
| 인덱스 활용 | 가능 | 어려움 |
| LIMIT 효율 | 높음 | 낮음 |
💡성능을 높이기 위해 고려해야 할 내용
- ORDER BY에 사용하는 컬럼에 인덱스를 걸기
- LIMIT을 사용하면 스트리밍 방식으로 유도할 수 있어 빠른 응답 가능
- 불필요한 조인을 줄이면 임시 테이블 생성과 디스크 정렬을 피할 수 있음
- EXPLAIN 명령어로 정렬 방식이 어떻게 동작하는지 꼭 확인
🔍 EXPLAIN 예시
EXPLAIN SELECT *
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
ORDER BY s.salary;| id | select_type | table | type | key | Extra |
| 1 | SIMPLE | e | ALL | NULL | |
| 1 | SIMPLE | s | ref | emp_no | Using where; Using temporary; Using filesort |
Using temporary; Using filesort는 버퍼링 방식으로 정렬이 일어난다는 증거이다.
9.2.3.4 정렬 관련 상태 변수
MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장한다.
정렬 관련 상태 변수 조회 명령
FLUSH STATUS;
SHOW STATUS LIKE 'Sort%';- Sort_merge_passes : 멀티 머지 처리 횟수
- Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수 (정렬 작업 횟수 누적하고 있는 상태)
- Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수 (정렬 작업 횟수 누적하고 있는 상태)
- Sort_rows : 지금까지 정렬한 전체 레코드 건수
9.2.4 GROUP BY 처리
GROUP BY는 ORDER BY와 비슷하게 스트리밍 방식의 데이터 처리를 사용할 수 없게 만든다.
GROUP BY 작업은 다음과 같이 나눌 수 있다.
- 인덱스 이용
- 인덱스 스캔 : 인덱스를 차례로 읽는 방식
- 루스 인덱스 스캔 : 인덱스를 건너뛰면서 읽는 방식
- 인덱스를 이용 x : 임시 테이블 사용
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
: 조인의 드라이빙 테이블에 속한 칼럼만을 이용해 그룹핑
: 인덱스를 완전히 활용하여 정렬이나 임시 테이블 없이 그룹화하는 방식
: 그 결과로 조인을 처리함
🔹 특징
- 인덱스에 정렬된 데이터를 그대로 읽으며 그룹핑한다
- 정렬 필요 없음, 임시 테이블도 안 씀
- 가장 빠르고 효율적인 방식
🔹 사용 조건
- GROUP BY 대상 컬럼이 인덱스의 가장 왼쪽부터 정렬된 순서와 일치해야 함
- 인덱스가 covering index일수록 효과적
- 타이트 인덱스 스캔은 Using index : 실행 계획을 선택할 수 있음
루스 인덱스 스캔을 이용하는 GROUP BY
: 그룹별로 필요한 값만 뛰어가며 건너뛰는 방식의 스캔
: 인덱스 기반으로 중복 그룹을 건너뛰면서 최소한의 스캔만으로 GROUP BY를 처리하는 고속 방식
일반 인덱스 스캔 (타이트 인덱스 스캔)
- 인덱스를 따라 모든 행을 읽으며 그룹핑
→ D001 (10001), D001 (10002), D002 (10003)... 전부 순회
루스 인덱스 스캔
- 중복된 dept_no는 건너뛰고, 부서별 처음 값만 읽음
→ D001 (10001), D002 (10003), D003 (10006) 만 읽음
→ 즉, 같은 그룹은 하나만 보고 다음 그룹으로 점프
| 조건 | 이유 |
| GROUP BY 대상 컬럼이 인덱스에 있어야 함 | 정렬 순서를 알아야 점프 가능 |
| SELECT 절에 다른 컬럼이 없어야 함 | 점프만 하려면 다른 데이터는 불필요해야 함 |
| 집계 함수 (COUNT, AVG) 같은 건 거의 못 씀 | 전체 값을 다 안 보기 때문 |
- 루스 인덱스 스캔은 Using index for group-by: 실행 계획을 선택할 수 있음
임시 테이블을 사용하는 GROUP BY
- 인덱스를 활용하지 못할 경우, 임시 테이블을 만들어서 정렬 후 그룹화
- 가장 일반적이지만, 가장 느릴 수 있음
- GROUP BY 대상이 인덱스 조건과 다르거나, ORDER BY가 상충하면 자동으로 이 방식 사용됨
SELECT dept_id, AVG(salary)
FROM employees
WHERE hire_date >= '2015-01-01'
GROUP BY dept_id
ORDER BY AVG(salary) DESC;
위 쿼리는 AVG(salary) 때문에 정렬이 필요하므로,
MySQL은 Using temporary; Using filesort 같은 실행 계획을 선택할 수 있음.
9.2.5 DISTINCT 처리
특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.
DISTINCT 처리는 크게 2가지로 나뉠 수 있다.
- 집합함수(MIN,MAX,COUNT)와 함께 사용되는 경우
- 그렇지 않은 경우
SELECT DISTINCT ...
단순히 SELECT되는 레코드 중에 유니크한 레코드만 가져오고자 하면,
SELECT DISTINCT 형태의 쿼리 문장을 사용한다.
이 경우 GROUP BY와 동일한 방식으로 처리된다.
SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;
// GROUP BY를 수행하는 쿼리에 ORDER BY절이 없으면 정렬을 사용하지 않음
특정 칼럼만 유니크하게 조회하는 것이 아닌, SELECT하는 레코드(튜플)를 유니크하게 SELECT하는 것이다.
아래의 쿼리에서 SELECT하는 결과는 first_name만 유니크한 것을 가져오는 것이 아닌,
(first_name, last name) 조합 전체가 유니크한 레코드를 가져온다.
SELECT DISTINCT first_name, last_name FROM employees;
그리고 SELECT절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다.
집합 함수와 함께 사용된 DISTINCT
집합 함수 내에서 DISTINCT 키워드가 사용될 수 있는데, 이는 SELECT DISTINCT와 다른 형태로 해석된다.
이는 집합 함수의 인자로 전달된 칼럼값이 유니크 한 것들을 가져온다.
EXPLAIN SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;
위 쿼리는 내부적으로는 "COUNT(DISTINCT s.salary)"를 처리하기 위해 임시 테이블을 사용한다.
| 처리 단계 | 설명 |
| 1 | s.salary 값을 임시 공간에 모음 |
| 2 | 그 안에서 중복 제거 (DISTINCT) |
| 3 | 남은 유니크한 값들의 개수를 셈 |
다만 쿼리의 실행 계획에서는 임시 테이블을 사용한다는 메시지는 표시되지 않는다.
9.2.6 내부 임시 테이블 활용
내부적인 임시 테이블(Internal temporary table)은
MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때 사용한다.
CREATE TEMPORARY TABLE 명령으로 만들어진 임시 테이블 과는 달리,
내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.
메모리 임시 테이블과 디스크 임시 테이블
- 메모리는 TempTable이라는 스토리지 엔진을 사용한다. (MySQL 8.0 이후부터)
- 최대 사용 가능한 메모리 크기는 1GB이다. 1GB가 초과하는 경우 디스크로 기록하게 된다.
- 이 때 두 가지 방식으로 저장할 수 있다.
- MMAP 파일로 기록 (디폴트, InnoDB 테이블로 전환하는 것 보다 오버헤드가 적음)
- InnoDB 테이블로 기록
- 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다. (MySQL 8.0 이후부터)
임시 테이블이 필요한 쿼리
- 다음과 같은 쿼리는 MySQL 엔진에서 별도의 데이터 가공이 필요하므로 내부 임시 테이블을 생성하는 대표적인 케이스이다.
- Order by와 group by에 명시된 칼럼이 다른 쿼리
- Order by와 group by에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
- distinct와 order by가 동시에 쿼리에 존재하는 경우 또는 distinct가 인덱스로 처리되지 못하는 쿼리
- union이나 union distinct가 사용된 쿼리(select_type이 union result인 경우)
- 쿼리의 실행 계획에서 select_type이 derived인 쿼리
- 임시 테이블을 사용하는 지 여부는 실행 계획의 Extra 칼럼에 "Using temporary" 메시지가 표시되는지 확인하면 알 수 있다.
임시 테이블이 디스크에 생성되는 경우
- 내부 임시 테이블은 기본적으로 메모리에 만들어진다.
- 하지만 다음의 조건을 만족하면 디스크 기반의 임시 테이블을 사용한다.
- Union이나 union all에서 select되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
- group by나 distinct 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
- 메모리 임시 테이블의 크기가 temp_table_max_ram 시스템 변수 값보다 큰 경우
임시 테이블 관련 상태 변수
- 일반적으로 실행 계획에서 "Using temporary"가 표시된 것으로 임시 테이블이 사용되었다는 것을 알 수 있다.
- 하지만 임시 테이블을 하나 사용했는지 여러개 사용했는지, 메모리에 생성됐는지 디스크에 생성됐는지는 다음의 상태 변수로 확인할 수 있다.
- show session status like 'Created_tmp%';
- Created_tmp_tables : 내부 임시 테이블의 개수를 누적하는 상태 값
- Created_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수를 누적하는 상태 값
9.3 고급 최적화
MySQL 서버의 옵티마이저가 실행 계획을 수립할 때는
통계 정보 + 옵티마이저 옵션 => 최적의 실행 계획을 수립한다.
9.3.1 옵티마이저 스위치 옵션
옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어하는데,
optimizer_switch 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용한다.
- 9.3.1절 하위의 옵션들을 on, off, default로 설정하여 사용할 수 있다.
- 9.3.1.1을 제외한 모든 설정 옵션은 기본값이 on이다.
사용 예시
SET optimizer_switch='batched_key_access=on';| 옵티마이저 스위치 이름 | 기본값 | 설명 |
| batched_key_access | off | BKA 조인 알고리즘을 사용할지 여부 설정 |
| block_nested_loop | on | Block Nested Loop 조인 알고리즘을 사요할지 여부 설정 |
| engine_condition_pushdown | on | Engine Condition Pushdown 기능을 사용할지 여부 설정 |
| index_condition_pushdown | on | Index Condition Pushdown 기능을 사용할지 여부 설정 |
| use_index_extensions | on | Index Extension 최적화를 사용할지 여부 설정 |
| index_merge | on | Index Merge 최적화를 사용할지 여부 설정 |
| index_merge_intersection | on | Index Merge Intersection 최적화를 사용할지 여부 설정 |
| index_merge_sort_union | on | Index Merge Sort Union 최적화를 사용할지 여부 설정 |
| mrr | on | MRR 최적화를 사용할지 여부 설정 |
| mrr_cost_based | on | 비용 기반의 MRR 최적화를 사용할지 여부 설정 |
| semijoin | on | 세미 조인 최적화를 사용할지 여부 설정 |
| firstmatch | on | FirstMatch 세미 조인 최적화를 사용할지 여부 설정 |
| loosescan | on | LooseScan 세미 조인 최적화를 사용할지 여부 설정 |
| materialization | on | Materialization 최적화를 사용할지 여부 설정 (Materialization 세미 조인 최적화 포함) |
| subquery_materialization_cost_based | on | 비용 기반 Materialization 최적화를 사용할지 여부 설정 |
9.3.1.1 MRR과 배치 키 액세스 ( mrr & batched_key_access )
MRR (Multi-Range Read)
- MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다.
- 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것이다.
- 그리고 조인 버퍼에 레코드가 가득 차면 그때 한 번에 스토리지 엔진으로 요청한다.
- 읽어야 할 레코드들을 데이터 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화하는 방식
BKA (Batched Key Access) 조인
- MRR을 응용해서 실행되는 조인 방식
- BKA 조인 최적화는 기본적으로 비활성화 됨
- BKA 조인을 사용하게 되면 부가적인 정렬 작업이 필요해지면서 오히려 성능에 안 좋은 영향을 미치기도 함
9.3.1.2 블록 네스티드 루프 조인 ( block_nested_loop )
- MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인
- 프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 Nested Loop Join
- 블록 네스티드 루프 조인은 조인 버퍼가 사용되는 것과 드라이빙 테이블, 드리븐 테이블의 처리 순서에 따라서 구분됨
- MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입되어 이 방식은 사용되지 x
9.3.1.3 인덱스 컨디션 푸시다운 ( index_condition_pushdown )
Alter table employees add index ix_lastname_firstname (last_name, first_name);
Select * from employees where last_name='Acton' and first_name like '%sal';- 위의 Select 쿼리에서 index_condition_pushdown을 off 로 설정하면 last_name을 기준으로 인덱스를 타고 first_name like '%sal' 조건은 레코드에 직접 접근해서 검사를 한다.
- 그러나 index_condition_pushdown을 on으로 설정하면 index 내에서 비교를 수행하고 비교한 대상 레코드에만 접근해서 데이터를 가져올 수 있다.
9.3.1.4 인덱스 확장 ( use_index_extensions )
- 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.
- InnoDB 스토리지 엔진에서 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키를 포함한다.
- 프라이머리 키가 a 이고 세컨더리 인덱스가 b 로 되어있으면 실제로 세컨더리 인덱스는 (b, a)로 작동한다.
9.3.1.5 인덱스 머지 ( index_merge )
- 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
- 하나의 인덱스를 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 하나의 인덱스만 활용하는 것이 효율적이다.
- 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상 될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
- 인덱스 머지는 다음의 3개의 세부 실행 계획으로 나누어 볼 수 있다.
- index_merge_intersection
- index_merge_union
- index_merge_sort_union
9.3.1.6 인덱스 머지 - 교집합 ( index_merge_intersection )
- 이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합을 반환한다.
- 실행계획의 Extra 칼럼에 Using intersect로 표시됨을 확인할 수 있다.
select * from employees where first_name='George' and emp_no between 10000 and 20000;- 위의 쿼리에서 두 조건에 대한 인덱스가 모두 존재하고 모두 상대적으로 많은 레코드를 가져와서 처리해야 한다면 인덱스를 각각 검색해서 교집합 하는 방식이 더 효율적일 수 있다. (물론 아닐 수도 있다.)
9.3.1.7 인덱스 머지 - 합집합 ( index_merge_union )
- 이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 합집합을 반환한다.
- 실행계획의 Extra 칼럼에 Using union로 표시됨을 확인할 수 있다.
select * from employees where first_name='Matt' or hire_date='2022-07-13';
이 경우 인덱스 머지 방식을 사용할 때 중복을 제거해주어야 한다.
- 각각의 인덱스로 조회 시에 emp_no(primary key)를 기준으로 정렬이 되어있으므로 인덱스 머지를 수행할 때 각 집합에서 하나씩 가져와 비교하며 죽복된 레코드를 걸러낼 수 있다.(우선순위 큐 알고리즘)
9.3.1.8 인덱스 머지 - 정렬 후 합집합 ( index_merge_sort_union )
- 위의 Union 알고리즘에서 정렬된 결과로 중복제거를 하는데 정렬이 이미 되어있으므로 필요하지 않았다.
- 하지만 도중에 정렬이 필요한 경우에는 Sort union 알고리즘을 사용한다.
9.3.1.9 세미 조인 ( semijoin )
- 다른 테이블과 실제 조인을 수행하지는 않고,
- 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라 한다.
- Table Pull-out
- Duplicate Weed-out
- First Match
- Loose Scan
- Materialization
select *
from employees e
where e.emp_no in
( select de.emp_no from dept_emp where de.from_date= '1995-01-01' );
9.3.1.10 테이블 풀-아웃 ( Table Pull-out )
- 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화다.
- IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리
- 실행계획의 Extra 칼럼에 특별한 문구가 출력되지 않는다. 따라서, table pullout 최적화가 사용됐는지 확인하려면 Show warings 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 살펴보면 된다.
- 이때, IN(subquery) 형태는 사라지고 join으로 재작성된 것을 확인할 수 있다.