[테코톡] MySQL 옵티마이저의 실행계획

유튜브를 통해 매일 조금씩 공부하기...!

 

https://www.youtube.com/watch?v=gcsu7ni3tBc&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH

---

실행계획이란?

MySQL 서버 구성

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

쿼리 실행 과정

• MySQL 엔진: SQL parser -> SQL 옵티마이저 -> SQL 실행기
• 스토리지 엔진: 데이터 읽기/쓰기

실행계획이란?

• 옵티마이저가 결정한 최적의 쿼리 실행방법
  • 쿼리 앞에 'EXPLAIN'을 붙여 확인 가능

 

실행계획 분석

테스트 환경 세팅

Field	Type	Null	Key
id	bigint	NO	PRI
nickname	varchar(255)	NO	UNI
age	int	NO	MUL
address	varchar(255)	NO	MUL

 

10,000,000건의 데이터

인덱스: 기본키와 유니크 키의 경우 기본으로 설정, 나이에 대하여 단일 인덱스 설정

 

실행계획 컬럼

• key: possible_keys 컬럼에 적힌 후보 인덱스 중 실제로 선택한 인덱스
• rows: 쿼리를 실행하기 위해 조사해야 하는 행의 수
  • rows 값을 줄이기는 것이 쿼리 튜닝의 핵심
  • 옵티마이저가 통계값을 바탕으로 추정한 값이라 정확하지 않음
• type: 각 테이블의 레코드를 어떤 방식으로 조회했는지
  • const: 일치하는 행이 하나인 경우. 조건절에 PK / Unique 컬럼 이용. -> 매우 빠름
  • ref: PK / Unique가 아닌 컬럼의 인덱스. 동등 조건으로 검색할 때 선택.
  • range: 인덱스 레인지 스캔 - 인덱스를 활용해 범위 형태의 데이터를 조회한 경우.
  • index: 인덱스 테이블 풀 스캔 - 인덱스에 포함된 컬럼만으로 테이블 전체를 스캔하는 경우.
  • all: 테이블 풀 스캔. -> 성능이 좋지 않음. 인덱스 추가를 통해 피할 수 있음.
• Extra: 주로 내부적인 처리 알고리즘.
  • Using filesort: 별도의 공간 소트 버퍼에서 처리. 정렬을 위한 적절한 인덱스를 찾지 못한 경우
    • EXPLAIN SELECT * FROM member ORDER BY address;
  • Using temporary: 쿼리를 병합할 때 공간을 사용함
    • EXPLAIN (SELECT * FROM member WHERE id < 20)
      UNION (SELECT * FROM member WHERE id > 20);
  • Using index: 데이터 파일을 읽지 않고 인덱스 테이블만 읽어 쿼리를 처리할 수 있을 때

오른쪽에 위치할 수록 성능이 낮음