MySQL Optimize with 공식문서 (2)

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

 

SELECT 문 최적화

쿼리 최적화 주요 사항

느린 SELECT - WHERE 쿼리의 속도를 개선

• 인덱스 추가 가능 여부를 체크
• 인덱스 적용 시 조건 평가, 필터링, 결과 조회 속도 향상 가능 -> 조인 및 외래키를 통한 테이블 참조 쿼리에 유효
• 디스크 공간 낭비는 피해야함
  • 쿼리의 공통적으로 도움이 되는 인덱스를 최소한으로 사용

 

과도한 시간이 걸리는 쿼리는 분리하여 튜닝

• 쿼리 구조에 따라 특정 함수가 각 행마다 or 전체 행마다 호출될 수 있음
• 이는 전체 성능에 큰 영향

 

큰 테이블의 경우 쿼리에서 전체 테이블 스캔 횟수를 최소화

 

주기적으로 테이블 통계를 최신화

• ANALYZE TABLE 최적화를 통해 효율적인 실행 계획 정보 획득 가능

 

각 테이블에 맞는 설정을 확인

• 각 테이블에 사용된 스토리지 엔진에 따라 구성
• 엔진에 맞는 튜닝 기법, 인덱싱 기법 및 구성 매개변수 확인

 

트랜잭션 최적화

• InnoDB의 기술을 통해 단일 쿼리 트랜잭션 최적화 가능

 

쿼리를 불필요하게 복잡하게 변형하지 말것

• 쿼리 옵티마이저가 자동으로 최적화 수행 중
• 이를 수동으로 반복 시 오히려 이해하기 어렵고 유지보수가 힘들어질 수 있음

 

EXPLAIN 계획을 통해 조사해볼것

• 인덱스, WHERE, 조인을 조정하여 특정 쿼리의 내부 세부 사항 조사

 

MySQL의 캐싱 영역을 조정하자

• InnoDB 버퍼 풀, MyISAM 키 캐시, MySQL 쿼리 캐시를 효율적으로 사용하자

 

가능하면 캐시 메모리 사용량을 줄이자

• 애플리케이션의 확장성과 관련이 있으므로 될 수 있으면 줄이자

 

동시 접속에 의한 잠금 문제를 점검하자

• 쿼리 성능이 예상보다 느릴 경우, 다른 세션의 영향일 수 있음
• 동시성 이슈로 쿼리가 대기하거나 병목 발생 가능

 

WHERE 절 최적화

WHERE 절의 최적화는 SELECT를 포함한 모든 명령문에서 동일

불필요한 괄호 제거

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

 

상수 폴딩

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

 

상수 조건 제거

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6

• Optimizer가 최적화를 자동으로 수행하므로, 쿼리를 가능한 단순한 형태로 유지하자
• 특정 최적화 작업이 쿼리 최적화 단계가 아닌, 준비 단계에서 수행 -> OUTER JOIN 단순화
• 인덱스의 상수 표현식은 한 번만 평가됨

 

숫자형 컬럼 상수 비교 최적화 (8.0.16+)

# TINYINT UNSIGNED 는 최대값 255
SELECT * FROM t WHERE c < 256;
→ SELECT * FROM t WHERE 1;

• 범위를 벗어난 조건 → 항상 참 또는 거짓으로 단순화

 

실행 불가능한 SELECT 조기 감지

• 상수 조건이 FALSE인 경우 실행 전에 감지

SELECT * FROM t WHERE 5 = 6;
→ 실행 전 판단: 결과 없음

 

 

WHERE로 병합 가능 시 HAVING 제거

• GROUP BY나 집계 함수(COUNT(), MIN() 등)를 사용하지 않으면,
  → HAVING 조건이 자동으로 WHERE으로 병합됨
• 불필요한 HAVING은 사용하지 않는 것이 좋음

 

조기 WHERE 평가로 JOIN 단순화

• 각 테이블마다 가능한 빨리 WHERE 조건 평가
  → 불필요한 행 JOIN 전에 건너뜀

 

상수 테이블을 통한 먼저 읽기

• 상수 테이블 = 다음 조건 중 하나
  • 빈 테이블 / 행 1개짜리 테이블
  • PRIMARY KEY, UNIQUE, NOT NULL + 상수 조건 있는 테이블

SELECT * FROM t WHERE primary_key = 1;
SELECT * FROM t1, t2 WHERE t1.id = 1 AND t2.ref = t1.id;

 

최적 조인 순서 계산

• 가능한 모든 조합 시도해서 가장 빠른 조인 경로 선택
• ORDER BY, GROUP BY의 열이 한 테이블에 몰려 있으면 그 테이블 먼저

 

정렬 시 임시 테이블 최소화

• ORDER BY나 GROUP BY에 외부 테이블 열이 포함되면 임시 테이블 생성
• SQL_SMALL_RESULT 사용 시 → 메모리 기반 임시 테이블 사용

 

인덱스 vs 테이블 스캔 자동 판단

• 예전엔 “30% 넘으면 스캔” 같은 기준이 있었음
• 지금은 테이블 크기, 행 수, 블록 크기 등 여러 요소 기반으로 동적 판단

 

커버링 인덱스 (index-only scan)

• 쿼리에 필요한 모든 컬럼이 인덱스에 있으면 → 데이터 파일 접근 없이 처리

 

HAVING 조건 행 출력 전에 제거

• HAVING 조건과 일치하지 않는 행은 출력 전에 필터링

 

매우 빠른 쿼리 예시

SELECT COUNT(*) FROM tbl_name;
SELECT MIN(key), MAX(key) FROM tbl_name;
SELECT MAX(col2) FROM tbl_name WHERE col1 = 1;
SELECT * FROM tbl ORDER BY col1, col2 LIMIT 10;

 

인덱스로 정렬된 결과 바로 가져오기

SELECT * FROM tbl ORDER BY key1, key2;
→ 정렬 없음, 인덱스로 바로 순차 접근

 

범위 최적화

범위 스캔 기본

• 인덱스를 사용해 값의 구간(범위) 내 데이터를 빠르게 조회
• =, >, <, >=, <=, BETWEEN, LIKE 'abc%', IN (...) 등 사용 시 적용

단일 인덱스 조건 예시

key_col > 1 AND key_col < 10
key_col IN (1, 2, 3)
key_col LIKE 'abc%'

 

다중 인덱스(복합 인덱스)

• key1 = val AND key2 > val2 같은 식으로 앞에서부터 조건 만족해야 함
• 앞 조건이 범위면 → 그 이후 조건은 무시될 수도 있음

 

OR 조건도 범위로 합쳐짐

key = 1 OR key = 2 → key IN (1,2)

 

Skip Scan (8.0.13+)

• 복합 인덱스의 첫 번째 컬럼에 조건이 없어도 다음 컬럼 범위로 분리 검색
• 예: f2 > 40 → f1 = 1 AND f2 > 40, f1 = 2 AND f2 > 40 … 반복 스캔

---

행 생성자 최적화

(col1, col2) IN (('a', 'b'), ('c', 'd'))
→ 범위 스캔으로 처리됨 (LIKE AND 조건처럼 분해됨)

 

메모리 제한 (range_optimizer_max_mem_size)

• 범위 최적화에 쓰일 메모리 제한 설정
• 초과 시 → 전체 테이블 스캔으로 대체됨

 

인덱스 다이브 (eq_range_index_dive_limit)

• IN (...)에서 각 값마다 인덱스를 직접 탐색하여 행 수 추정
• 값이 많으면 통계 기반으로 전환됨