MySQL Optimize with 공식문서 (4)

Ref.

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

MySQL :: MySQL 8.0 Reference Manual :: 10.2.1 Optimizing SELECT Statements

 

조건 필터링

• 조인에서 앞 테이블에서 다음 테이블로 넘길 "prefix rows" 수가 많을수록 성능이 나빠짐
• 옵티마이저는 WHERE 조건을 분석해 실제 넘길 행 수를 더 정확히 예측하려고 함

 

동작 방식

• 인덱스로 가져온 행 수 = rows
• 나머지 WHERE 조건으로 줄어드는 비율 = filtered (%)
• prefix row 수 = rows × filtered

예: rows = 8, filtered = 16.31% → prefix row = 1.3

 

 

필터링 조건이 되기 위한 요건

• 현재 테이블의 조건이어야 함
• 상수 or 앞 테이블의 값에 의존해야 함
• 인덱스 접근에서 이미 반영되지 않은 조건이어야 함

 

EXPLAIN에서 확인

+----+----------+------+----------+-----+------+-------+----------+
| id | table    | ...  | rows     | ... | filtered |
+----+----------+------+----------+-----+------+-------+----------+
| 1  | employee | ...  | 8        | ... | 16.31    |

• filtered = 100 → 조건 없음 (또는 모든 행 통과)
• filtered < 100 → WHERE 조건이 추가로 필터링

예시

SELECT * 
FROM employee 
JOIN department ON employee.dept_no = department.dept_no
WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

• first_name = 'John'은 인덱스 접근
• hire_date BETWEEN ...은 추가 조건 → 조건 필터링 적용됨

 

옵티마이저 설정 관련

• 조건 필터링은 기본적으로 활성화됨
• 끄려면:

SET optimizer_switch = 'condition_fanout_filter=off';

• 쿼리 힌트로도 끌 수 있음:

SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ ...

 

 

주의점

• 조건 필터링 효과를 과대추정하면 잘못된 실행 계획이 선택될 수도 있음
  → 이럴 땐 힌트, 인덱스 추가, 통계(histogram) 생성 등으로 조정 가능

 

상수 폴딩 최적화

• 상수와 컬럼 비교 조건을 실행 전에 미리 평가해서 단순화
• → 쿼리 실행 중 행마다 비교하지 않아도 됨 → 성능 향상

예1: 범위를 벗어난 상수

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

SELECT * FROM t WHERE c < 256;
→ WHERE 1  (항상 참이므로 쿼리 단순화)

예2: 상한 또는 하한일 때

SELECT * FROM t WHERE c >= 255;
→ WHERE c = 255 (상한 경계값이므로 변환됨)

예3: 소수 비교

-- DECIMAL(3,1) 열과 비교
SELECT * FROM t WHERE f >= 10.13;
→ WHERE f > 10.1 (소수 자리수 보정 후 연산자 조정)

 

 

타입별 처리 방식

컬럼 타입 상수 타입 처리 방식

INT	INT	범위 초과면 WHERE 1 or IS NOT NULL
INT	FLOAT	반올림 후 정수처럼 처리
DECIMAL	DECIMAL	소수 자릿수 맞춰 자름 또는 비교 연산자 보정
FLOAT	DECIMAL	정밀도 초과 시 잘라냄 / 오버플로 시 조건 제거
문자열	정수/실수로 해석 가능하면 변환해서 비교	해석 불가면 → REAL로 시도

 

 EXPLAIN

• SHOW WARNINGS 명령어로 쿼리 리라이트 여부 확인 가능

Message: ... where (`t`.`c` = 255)

 

 

적용되지 않는 경우

• BETWEEN, IN 사용한 조건
• BIT, DATE, TIME 타입 컬럼
• Prepared Statement에서는 실행 시에만 적용

 

IS NULL 최적화

• col IS NULL도 col = 상수처럼 인덱스 사용 가능
• → 즉, NULL 비교도 빠르게 처리 가능

 예시

-- 인덱스 사용 가능
SELECT * FROM tbl WHERE key_col IS NULL;

-- 동등 연산자 사용한 NULL 비교도 가능
SELECT * FROM tbl WHERE key_col <=> NULL;

-- 여러 값 OR NULL도 최적화됨
SELECT * FROM tbl
WHERE key_col = val1 OR key_col = val2 OR key_col IS NULL;

 

옵티마이저가 자동으로 무시하는 조건

-- key_col이 NOT NULL인데 IS NULL 조건이 있다면?
SELECT * FROM tbl WHERE key_col IS NULL;
→ 무의미한 조건 → **최적화로 제거됨**

단, LEFT JOIN 등으로 NULL이 될 수 있는 컬럼이면 제거하지 않음

 

ref_or_null 최적화

• EXPLAIN에서 type: ref_or_null이면 이 최적화가 적용된 것
• 1단계: 상수/키 기반 조회
• 2단계: NULL 값만 따로 조회

예시:

-- 컬럼 a에 인덱스 있을 때
SELECT * FROM t1 WHERE t1.a = expr OR t1.a IS NULL;
→ ref_or_null 사용됨

 

 

조인에서도 적용 가능

-- t2.a 또는 t2.b가 NULL일 수도 있을 때
SELECT * FROM t1, t2
WHERE (t1.a = t2.a OR t2.a IS NULL)
  AND (t1.b = t2.b OR t2.b IS NULL);

• 단, IS NULL 최적화는 1개만 적용 가능
  → 위 예시에서는 a에만 적용되고, b엔 적용 안 됨

 

ORDER BY 최적화

인덱스로 정렬 처리 가능한 경우

정렬 컬럼이 인덱스 순서와 맞으면 filesort 없이 정렬 가능

인덱스 사용 예시

-- 인덱스: (key1, key2)
SELECT * FROM t ORDER BY key1, key2;

-- key1 = 상수 → key2 기준 정렬
SELECT * FROM t WHERE key1 = 10 ORDER BY key2;

-- DESC 정렬도 방향만 맞으면 가능
SELECT * FROM t ORDER BY key1 DESC, key2 DESC;

-- 혼합 정렬도 인덱스가 있으면 가능
SELECT * FROM t ORDER BY key1 DESC, key2 ASC;

SELECT *로 인해 인덱스 외 컬럼도 필요할 경우

• 옵티마이저가 인덱스 사용 안 하고 filesort 선택할 수 있음

 

인덱스를 사용할 수 없는 경우 (filesort 발생)

인덱스 못 쓰는 경우

• ORDER BY가 인덱스 순서와 다름
• ORDER BY에 계산식/함수 사용

ORDER BY ABS(col), ORDER BY -col

• 다른 인덱스를 동시에 사용
• 조인에서 ORDER BY 대상이 첫 테이블이 아님
• GROUP BY와 ORDER BY가 다름
• 인덱스로는 정렬이 불가능한 컬럼 타입 (예: HASH, 인덱스 prefix만 사용한 CHAR 등)

 

filesort 작동 방식

• 메모리에 sort buffer를 필요할 만큼만 점진적으로 할당 (MySQL 8.0.12+)
• 정렬 데이터가 메모리를 초과하면 디스크에 임시 파일 생성

메모리/디스크 튜닝

변수 설명

sort_buffer_size	정렬 버퍼 크기 (큰 값일수록 메모리 정렬 유리)
max_sort_length	문자열 컬럼 최대 정렬 길이
read_rnd_buffer_size	디스크에서 랜덤 읽기할 때 버퍼 크기
tmpdir	임시 파일 경로 (디스크 분산 가능)
Sort_merge_passes	디스크 병합 횟수 확인용 상태 변수

 

EXPLAIN & Optimizer Trace에서 확인

• EXPLAIN 결과의 Extra에:
  • Using filesort → 정렬에 인덱스 안 쓰고 filesort 사용
  • 없으면 → 인덱스 정렬 처리

 

Optimizer Trace 예시

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

• peak_memory_used: 최대 사용 메모리
• sort_mode: 정렬 버퍼에 저장된 형식
  • <sort_key, rowid>: rowid로 테이블 다시 읽음
  • <sort_key, additional_fields>: 필요한 컬럼 같이 저장
  • packed_additional_fields: 컬럼을 더 압축해서 저장

 

GROUP BY 최적화

• 일반적으로는 모든 행을 스캔 → 임시 테이블 생성 → GROUP BY 수행
• 하지만 조건이 맞으면 인덱스를 활용해 임시 테이블 없이도 그룹핑 가능!

 

인덱스 기반 GROUP BY 최적화

Loose Index Scan	인덱스를 활용해 그룹별 대표 키만 읽음	가장 효율적
Tight Index Scan	인덱스를 범위 스캔하며 필터 후 그룹핑	느리지만 임시 테이블은 피함

 

Loose Index Scan

조건을 만족하면 그룹 수 만큼만 인덱스를 읽음

사용 조건

• 단일 테이블이어야 함
• GROUP BY 컬럼이 인덱스의 왼쪽부터 차례대로
• MIN(), MAX() 또는 COUNT(DISTINCT), SUM(DISTINCT)만 사용
• GROUP BY 외의 인덱스 컬럼은 상수 조건이어야 함
• 인덱스가 전체 컬럼을 포함해야 함 (prefix 인덱스는 X)

예시

-- index(c1, c2, c3) 가 있을 때
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT c1, MIN(c3) FROM t1 GROUP BY c1;
SELECT COUNT(DISTINCT c1) FROM t1;

EXPLAIN 결과

• Extra 컬럼에 Using index for group-by 표시됨

 

Loose Index Scan이 불가능한 예

-- SUM은 안 됨
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

-- GROUP BY가 왼쪽부터 아님
SELECT c2, c3 FROM t1 GROUP BY c2, c3;

-- GROUP BY 이후 컬럼 조건이 없음
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
-- → WHERE c3 = const 있으면 가능

 

 

Tight Index Scan

Loose 조건은 안되지만 범위 스캔으로 인덱스를 읽고 정렬 없이 GROUP BY 처리

특징

• WHERE 조건으로 중간 키값이 고정되면 인덱스 prefix 완성 가능
• 임시 테이블 없이 처리되지만, Loose보다 성능 낮음

예시

-- index(c1, c2, c3)
-- c2 고정 → c1, c3로 GROUP BY 가능
SELECT c1, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

-- c1 고정 → c2, c3로 GROUP BY 가능
SELECT c2, c3 FROM t1 WHERE c1 = 'b' GROUP BY c2, c3;