[Real Mysql] 실행계획 - 쿼리 동작 방식

MySQL 엔진에서 처리하는데 시간이 오래 걸리는 작업의 원리를 알아두자

 

풀 테이블 스캔

인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어 요청된 작업을 처리한다.

 

풀테이블 스캔을 하는 경우

- 레코드 수가 너무 작아서 인덱스 보다 풀테이블 스캔을 하는게 빠른 경우

- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 조건이 없을때

- 인덱스 레인지가 가능하더라도 옵티마이저가 판단하기에 너무 많은 수의 건수인 경우

 

ORDER BY

정렬을 크게

- 인덱스를 이용하는 방법

- FileSort 라는 별도 처리 방법

을 이용하는 방법이 있ㅎ다.

 

인덱스를 사용하지 않는다면 실행계획 extra컬럼에 Using filesort 가 표시된다.

 

- 소트 버퍼

 

정렬을 수행하기 위해 별도로 할당받은 메모리 공간

정렬해야 할 레코드 건수가 소트 버퍼로 할당된 공간 보다 크다면 임시 저장을 위해 디스크를 사용하게 된다.

 

- 정렬 알고리즘

- Single PASS : SELECT 되는 컬럼 전부 담아 정렬을 수행

- Two PASS: 정렬 대상 컬럼과 PK를 담아 정렬하고, 정렬된 순서대로 PK를 이용해 SELECT를 다시 하는 알고리즘

 

일반적으론 Single PASS 사용, 특정상황에서 Two PASS 사용됨

 

- 정렬 처리 방식

order by 는 3가지 처리 방식이 있다.

 

1. 인덱스를 이용할 정렬

 - order by 에 명시된 컬럼이 제일 먼저 읽는 테이블에 속해야 하고 (join 시), order by 순서대로 인덱스가 있어야 한다.

 

2. 드라이빙 테이블만 정렬

 - join을 사용할 경우 첫번째 테이블의 레코드를 먼저 정렬하고 조인을 실행

 

3. 임시 테이블을 이용한 정렬

- 조인 결과를 임시테이블에 놓고 정렬하는 작업을 수행

 

최소 드라이빙 테이블을 이용하게 정렬하는게 좋다.

 

 

 

GROUP BY

group by 에 있는 조건절은 인덱스를 사용할수없다. HAVING 절을 튜닝 할 수 없음

 

- group by 처리 방식

 

1. 인덱스를 사용한 경우

 - 드라이빙 테이블에 속한 컬럼을 이용해서 group by 를 할 때 인덱스가 동일한 순서로 있다면, 인덱스를 차례대로 읽으면서 그룹핑 작업 수행 후 조인 처리

 

2. 루스 인덱스 사용

 - 인덱스를 건너 뛰면서 사용하는 방법

 

3. 임시 테이블을 이용

 - group by 의 컬럼이 인덱스를 전혀 사용하지 못하는 경우 사용된다.

 

 임시 테이블

MySQL 에서 임시테이블은 처음에 메모리에 생성이 됐다가, 크기가 커지면 디스크로 옮겨가게 된다.

 

특정 조건에서는 사이즈와 관계없이 디스크에 생성이 되기도 한다.

- 임시 테이블에 저장하는 내용이 BLOB이나 TEXT 같은 대용량 컬럼이 있을때

- select 컬럼 중에서 길이가 512 바이트 이상인 크기의 컬럼이 있을때

- group by  나 distinct 컬럼에서 512바이트 이상인 컬럼이 있을때

- 저장할 데이터 크기가 tmp_table_size 나 max_heap_table_size 설정 값보다 클 경우

 

임시 테이블이 디스크에 생성되면 성능의 문제가 있을 수 있다.

따라서 select 하는 컬럼은 최소화 하고 데이터 타입 선정도 가능한 작게 해주는게 좋다.

 

 

테이블 조인

조인의 종류는 크게

 

- INNER JOIN

- OUTER JOIN

 

으로 나뉜다.

 

inner join 은 테이블 순서가 바껴도 결과가 바뀌지 않으므로 옵티마이저가 순서를 조절해서 최적화를 할 수 있지만

outer join 은 반드시 outer 테이블 먼저 읽기 때문에 조인 순서를 옵티마이저가 선택 할 수 없다.

 

어떤 방식으로든 테이블 풀 스캔이나 인덱스 풀 스캔을 피할 수 없다면,

옵티마이저는 드라이빙 테이블의 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다.

이 메모리 캐시를 조인 버퍼라고 하고 조인이 완료되면 이 버퍼는 바로 해제 된다.

 

일반저긍로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만

조인 버퍼가 사용될때는 결과의 정렬 순서가 보장되지 않는다.

order by 를 사용해야 정렬을 보장 할 수 있다.