MySQL Optimize with 공식문서 (3)

https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.3 Index Merge Optimization

 

인덱스 병합 최적화

인덱스 병합이란?

• 단일 테이블에서 여러 인덱스를 동시에 사용해 결과를 병합하는 방식
• OR, AND 조건에서 자주 사용
• 합집합(UNION), 교집합(INTERSECT), 정렬 합집합(SORT UNION)

 

사용 예시

-- 여러 인덱스에 걸쳐 검색
SELECT * FROM tbl WHERE key1 = 10 OR key2 = 20;

-- 병합 + 추가 조건
SELECT * FROM tbl WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

 

 

교차(INTERSECT) 방식

-- key1 AND key2 조건 (모두 만족)
SELECT * FROM tbl WHERE key1 = 1 AND key2 = 2;

• 여러 인덱스를 AND로 결합
• 모든 인덱스 조건을 만족하는 공통 행만 추출

 

합집합(UNION) 방식

-- key1 OR key2 OR key3 조건 (하나라도 만족)
SELECT * FROM tbl WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

• 여러 인덱스를 OR로 결합
• 각 인덱스에서 나오는 행들을 모아서 반환

 

정렬 합집합(SORT UNION)

-- 정렬 필요할 경우 사용
SELECT * FROM tbl WHERE key1 < 10 OR key2 < 20;

• 인덱스로 찾은 결과들을 정렬한 후 병합
• 일반 UNION이 안 되는 경우 사용

 

제약 사항

• 단일 테이블에서만 사용 가능
• FULLTEXT 인덱스엔 적용 불가
• 복잡한 AND/OR 중첩 시 옵티마이저가 잘못 판단할 수 있음 → 조건 분해 권장

 

동작 제어 방법

• optimizer_switch 변수로 켜고 끌 수 있음
  • index_merge, index_merge_union, index_merge_intersection, index_merge_sort_union
• 기본값: 전부 ON

 

EXPLAIN에서 확인

• type = index_merge
• key에는 사용된 인덱스 목록
• Extra에는:
  • Using union(...)
  • Using intersect(...)
  • Using sort_union(...)

 

해시 조인 최적화

해시 조인

• 인덱스가 없는 조인 조건에서도 빠르게 조인 가능
• 기존의 느린 Block Nested Loop(BNL) 조인을 대체

 

사용

• 기본값으로 자동 사용됨 (8.0.18 이상)
• 조인 조건에 인덱스 없음, 동등 조인 또는 비동등 조인, 외부 조인 포함
• EXPLAIN에서 Using join buffer (hash join) 또는 hash join 출력됨

 

적용 가능한 조인 유형

유형 예시 설명

내부 조인	ON t1.c1 = t2.c1 또는 ON t1.c1 < t2.c1
세미 조인	WHERE t1.c1 IN (SELECT ...)
안티 조인	WHERE NOT EXISTS (SELECT ...)
외부 조인	LEFT JOIN, RIGHT JOIN (내부적으로 LEFT로 변환됨)
데카르트 곱	조인 조건 없는 JOIN

 

예시

-- 동등 조인
SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1;

-- 비동등 조인
SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1;

-- 세미조인
SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c2 FROM t2);

-- 안티조인
SELECT * FROM t2 WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1);

 

 

EXPLAIN 확인

• FORMAT=TREE 사용 시 Inner hash join, Left hash join, Hash semijoin, Hash antijoin 등으로 표시됨

 

해시 조인 제어

• 더 이상 힌트 HASH_JOIN, NO_HASH_JOIN 사용 X
• 제어 변수: optimizer_switch, join_buffer_size, open_files_limit

 

메모리 사용 관련

• join_buffer_size 이상의 메모리 사용 불가 → 초과 시 디스크 임시파일 사용
• join_buffer_size 증가 또는 open_files_limit 증가로 해결 가능
• 8.0.20 이상부터 외부 조인도 점진적 메모리 할당 → 부담 감소

 

외부 조인 최적화

외부 조인이

• LEFT JOIN, RIGHT JOIN 포함
• 왼쪽(LEFT) 테이블에 맞는 행이 오른쪽(RIGHT) 테이블에 없으면
  → 오른쪽 테이블 컬럼은 NULL로 채워짐

 

옵티마이저가 자동으로 처리하는 최적화

1. 불필요한 외부 조인 → 내부 조인으로 변환

-- t2에 조건이 있고, NULL 허용 안 되면 내부 조인으로 바꿈
SELECT * FROM t1 LEFT JOIN t2 ON (...) WHERE t2.col = 5;
→ SELECT * FROM t1 JOIN t2 ON (...) WHERE t2.col = 5;

2. 사소한 WHERE 조건 조기 제거

-- 0=1 같은 조건은 준비단계에서 제거됨
SELECT * FROM t1 LEFT JOIN t2 ON cond1 WHERE cond2 OR 0 = 1;
→ SELECT * FROM t1 LEFT JOIN t2 ON cond1 WHERE cond2;
→ 내부 조인으로 단순화 가능

3. IS NULL 조건 → 검색 단축

-- LEFT JOIN 후 IS NULL 체크 시, 첫 번째 NULL만 찾고 중단
SELECT * FROM t1 LEFT JOIN t2 ON (...) WHERE t2.id IS NULL;

4. RIGHT JOIN → LEFT JOIN으로 재작성

• 내부적으로 RIGHT JOIN은 LEFT JOIN으로 변환해서 처리

 

외부 조인 단순화

• RIGHT JOIN은 쿼리 해석 단계에서 LEFT JOIN으로 자동 변환
• INNER JOIN은 WHERE 조건에 묶여 단순화
  → 옵티마이저가 더 자유롭게 조인 순서를 조정

 

외부 조인을 내부 조인으로 바꾸는 조건

조건이 “null-rejected”일 때 외부 조인을 내부 조인으로 변환

null-rejected

• 외부 조인에서 NULL이 채워진 행이 주어졌을 때 조건 결과가 무조건 FALSE 또는 UNKNOWN이 되는 경우

예: null-rejected 조건

WHERE t2.col IS NOT NULL  
WHERE t2.col > 3  
WHERE t2.col < 2 OR t2.col2 > 1

예: null-rejected 아님

WHERE t2.col IS NULL  
WHERE t2.col > 3 OR t1.col < 5  

 

단순화 예시

-- 원본 쿼리
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.value > 10;

-- null-rejected 조건이므로 내부 조인으로 변환됨
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t2.value > 10;

 

중첩된 조인도 단계적으로 단순화됨

-- 원본
SELECT * FROM t1 
LEFT JOIN (t2 LEFT JOIN t3 ON t3.b = t2.b)
ON t2.a = t1.a
WHERE t3.c > 0;

-- t3 조건이 null-rejected → 내부 조인으로 바뀜
SELECT * FROM t1 
LEFT JOIN (t2 JOIN t3 ON t3.b = t2.b)
ON t2.a = t1.a
WHERE t3.c > 0;

 

 

옵티마이저가 조인 순서 더 자유롭게 조정 가능

• 외부 조인이 내부 조인으로 바뀌면
  → 조인 순서가 유연해짐
  → 필터 조건이 일찍 적용되어 성능 향상