[MySQL] 옵티마이저

MySQL 아키텍처

• MySQL엔진: SQL 문장 분석 / 최적화
• 스토리지 엔진: 데이터 저장 / 조회

 

쿼리 실행 과정 1

• SQL 파서: SQL문을 잘게 쪼개 MySQL 서버가 이해할 수 있는 수준으로 분리
• SQL 옵티마이저: 파스 트리를 확인하며 쿼리 최적화 및 실행계획 수립
• 데이터 읽기/쓰기: 결정된 정보를 통해 스토리지 엔진에서 필요한 데이터 가져오기

 

실행 계획이란?

• 옵티마이저가 결정한 최적의 쿼리 실행 방법

  EXPLAIN 
  SELECT *
  FROM member
  WHERE id = 1;

   

 

실행계획 분석하기

1. 실행계획 컬럼 key: possible_keys 컬럼에 적힌 후보 인덱스 중 실제로 선택한 인덱스
2. 실행계획 컬럼 rows
   1. 쿼리를 실행하기 위해 조사해야 하는 행의 수
   2. rows 값을 줄이는 것이 쿼리 튜닝의 핵심
   3. 추정값이라 정확하지 않음
3. 실행계획 컬럼 type: 각 테이블의 레코드를 어떤 방식으로 조회했는지

오른쪽으로 갈수록 성능이 낮은 타입

 

실행계획 컬럼 type: const

• 일치하는 행이 하나
• 조건절에 PK / Unique 컬럼 이용
• 매우 빠름

EXPLAIN
SELECT * 
FROM member
WHERE nickname = 'whatever';

 

 

실행계획 컬럼 type: ref

• PK / unique 아닌 컬럼의 인덱스
• 동등(equal) 조건으로 검색할 때

EXPLAIN
SELECT * 
FROM member
WHERE age = 25;

 

 

실행계획 컬럼 type: range

• 인덱스 레인지 스캔
• 인덱스를 활용해 범위 형태의 데이터를 조회한 경우
• 범위 형태: <, >, IS NULL, BETWEEN, IN, LIKE 등

EXPLAIN
SELECT *
FROM member
WHERE age < 5;

 

 

실행계획 컬럼 type: index

• 인덱스 테이블 풀 스캔
• 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우 (커버링 인덱스)
• 인덱스를 사용하여 테이블 전체를 스캔하는 경우

EXPLAIN
SELECT age, id
FROM member;

 

age 인덱스 테이블

age	PK (id)
1	2
1	5

 

실행계획 컬럼 type: all

• 테이블 풀 스캔
• 성능 좋지 않음
• 인덱스 추가를 통해 피할 수 있음

EXPLAIN
SELECT * 
FROM member
WHERE address LIKE '대전%';

 

실행계획 컬럼: Extra

• 주로 내부적인 처리 알고리즘
• 조금 더 깊이 있는 내용

실행계획 컬럼 Extra: Using filesort

EXPLAIN
SELECT * 
FROM member
ORDER BY address;

• 별도의 공간 소트 버퍼에서 처리
• 정렬을 처리하기 위한 적절한 인덱스를 찾지 못한 경우 사용되는 정렬 방식

실행계획 컬럼 Extra: Using temporary

EXPLAIN
(SELECT *
 FROM member
 WHERE id < 20)
UNION
(SELECT *
 FROM member
 WHERE id > 20);

 

실행계획 컬럼: Using index

• 데이터 파일을 읽지 않고 인덱스 테이블만 읽어 쿼리를 처리할 수 있을 때 (커버링 인덱스)

 

보완

• 옵티마이저가 항상 정답은 아님
  • 통계 정보 업데이트
  • 옵티마이저 힌트 사용
  • 쿼리 단순화

 

Reference

• 우아한 테크톡
• https://www.youtube.com/watch?v=gcsu7ni3tBc&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH