[Real Mysql] 실행 계획

DBMS 에서 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계정보를 참조하며,

이런 기본 데이터를 비교해서 최적의 실행 계획을 수립하는 작업이 필요한데 이걸 옵티마이저가 담당한다.

explain 명령어로 실행 계획을 확인 할 수 있다.

 

옵티마이저

쿼리가 실행되는 과정을 크게 3가지로 나눌 수 있는데

1. 사용자로부터 요청된 SQL 문장을 잘라서 MySQL 서버가 이해 할 수 있는 수준으로 분리

2. 정보를 확인해서 어떤 테이블로부터 어떤 인덱스를 이용해 테이블을 읽을지 선택

3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해서 스토리지 엔진으로부터 데이터를 가져온다.

 

여기서 두번째 단계를 옵티마이저가 처리한다.

 

 

옵티마이저 종류

- 규칙 기반 최적화: 옵티마이저 내장된 우선 순위에 따라 실행계획을 수립, 레코드 건수등 고려하지 않는다.

- 비용 기반 최적화: 쿼리를 처리하기 위한 여러 방법을 만들고 통계를 바탕으로 비용을 예측해서 ,

최소 비용이 소요되는 처리 방식을 선택

 

통계 정보 

- MySQL 은 비용 기반 최적화 옵티마이저를 쓰고 있다.

MySQL 의 통계정보는 그리 다양하지 않고, 레코드 건수가 많지 않으면 통계정보가 부정확한 경우가 많아서

ANALYZE 명령어로 통계정보를 강제 갱신 할 때도 있다.

다만 ANALYZE 를 실행하는 동안 InnoDB에서 읽기, 쓰기 모두 불가능해져서

서비스 도중 실행하지 않는것이 좋다.

 

 

실행 계획

explain 했을때 나오는 실행 결과

 

- id 컬럼

SELECT 쿼리별로 부여되는 식별자 값

 

- select_type 컬럼

- SIMPLE: UNION 및 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리

- PRIMARY: UNION 및 서브 쿼리를 사용하는 쿼리에서 가장 바깥쪽 쿼리

- SUBQUERY: FROM 절에서 사용되지 않은 서브 쿼리

- DERIVED: FROM 절에 사용된 서브 쿼리

 

-table 컬럼

테이블이 표시되며, 별칭이 부여된 경우는 별칭을 표시

 

-type 컬럼

 

중요 정보로 테이블의 접근 방식으로 이해하면 된다.

 

cosnt: PK 나 unique column 을 사용하는 WHERE 조건, 1건만 반환

ref: 조인을 할때 키 값이 PK 나 Unique column 이 아닌경우

range: 인덱스 레인지 스캔

index: 인덱스 풀 스캔

ALL: 풀 테이블 스캔

 

-key 컬럼

 

최종 선택된 실행 계획에서 사용하는 인덱스

 

-key_len 컬럼

MySQL이 인덱스에 얼마나 많은 바이트를 쓰고 있는지.

 

-ref 컬럼

key 컬럼에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼이 사용되었는지를 나타낸다.

 

-rows 컬럼

원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할 지 예측값

 

-extra 컬럼

using index: 인덱스 자료구조를 이용해서 데이터를 추출

using where: where 조건으로 데이터를 추출, type 이 ALL 이나 index 였다면 성능이 좋지 않다.

using filesort: 데이터 정렬이 필요한 경우

using temporary: 쿼리 처리 시 내부적으로 temporary table 이 사용되는 경우