[Real mysql] 쿼리 작성 및 최적화

쿼리들이 어떻게 처리가 되는지를 알아보자

SELECT

 

select 는 여러 테이블을 조합해서 빠르게 가져와야 하기 때문에 여러 테이블을 어떻게 읽을 것인지

많은 주의를 기울여야 한다.

 

인덱스를 적절히 사용하면 order by 나 group by 가 있어도 해당 실행 순서가 skip 되기도 한다.

 

쿼리 실행 순서

 

GROUP BY

 

group by 절에 명시한 컬럼과 인덱스 컬럼 순서와 위치 모두 동일해야 한다.

인덱스 순서와 위치가 중요

 

예를들어 컬럼이 col1, col2, col3, col4 가 있다면

 

group by col2, col1 

group by col1, col3

group by col1, col2, col3, col4, col5

 

등 처럼 순서가 맞지 않고 특정 컬럼이 빠져있다면 인덱스를 타지 않는다.

 

대신 

 

group by col1

group by col1, col2

 

처럼 인덱스 앞부분이 일치하다면 인덱스를 타게 된다.

 

 

ORDER BY

 

group by 와 거의 흡사. 다만 정렬 방식 (ASC, DESC) 가 일치하는 경우에만 사용 가능하다.

 

인덱스 컬럼 : col1, col2, col3, col4

 

아래 같은 경우 인덱스를 탈 수 없다.

order by col2, col3
order by col1, col2 DESC, col3

 

select 쿼리 결과의 순서는 처리 절차에 따라 달라질 수 있기 때문에

order by 절이 없다면 어떤 정렬도 보장되지 않는다.

 

WHERE

 

WHERE 절에서 인덱스를 태우려면

- 컬럼 값 자체 변경 없이 그대로 사용

- 비교 조건에서 양쪽 데이터 타입 일치

- mysql 에서는 null 값이 포함된 레코드도 인덱스로 관리한다.

 

 

GROUP BY, ORDER BY, WHERE 조합

 

WHERE 절과 ORDER BY 절 동시에 인덱스가 있다면

 

- 빠른 검색 가능

- 서로 다른 인덱스라면 둘 중 한가지의 방법으로 처리 된다.

 

WHERE 절만 인덱스가 있다면

 

- order by 절은 인덱스를 탈 수 없고 where 절만 탈 수 있다면,

인덱스를 통해 검색된 결과를 별도의 정렬 과정을 거쳐 정렬을 수행한다.

 

ORDER BY 절만 인덱스를 사용

 

- order by 절만 인덱스가 있고 where 절은 인덱스를 못탈때

order by 절은 순서대로 인덱스를 읽으면서 레코드 한 건 씩 where 조건을 일치하는지 비교

 

GROUP BY + ORDER BY

 

- group by와 order by 에 명시된 컬림 순서와 내용이 모두 같다면 인덱스를 사용가능,

둘 중 하나라도 인덱스를 탈 수 없다면 둘 다 인덱스를 사용 할 수 없다.

 

 

 

 

Join 쿼리

 

join 과 인덱스 레인지 스캔

 

먼저 조인 쿼리를 튜닝하기 전에, 인덱스 레인지 스캔으로 레코드를 읽는 작업을 생각해 보아야 한다.

 

1. 인덱스 탐색: 인덱스에서 조건을 만족하는 위치를 찾는다.

2. 인덱스 스캔: 찾은 위치부터 필요한 만큼 인덱스를 읽는다.

3. 스캔한 인덱스 키와 주소를 이용해서 저장된 데이터를 가져온다.

 

인덱스 풀 스캔이나 테이블 풀 스캔은 전체를 읽기 때문에 인덱스 탐색은 오래걸리지 않지만,

전체 데이터를 읽어야 하므로 부하가 심하다.

반면 인덱스 레인지 스캔은 인덱스 탐색과정이 상대적으로 부하가 심하고 스캔 작업이 적게 걸린다.

 

조인 작업에서는 드라이빙 테이블을 읽을때 인덱스 탐색 작업을 한번 수행하고,

드리븐 테이블에서는 드라이빙 테이블에서 읽은 레코드 수 만큼, 드리븐 테이블에서 인덱스 탐색 작업과

스캔 작업을 반복한다.

따라서 옵티마이저는 드리븐 테이블을 최적화 할 수 있게 실행 계획을 수립한다.

 

 

SELECT *

FROM first, second

WHERE first.test = second.test;

 

- 두 테이블 모두 index 가 있다면 통계 정보에 따라 모두 드라이빙 테이블이 될 수 있다.

- first 에만 index가 있다면 인덱스가 있는 테이블을 반복적으로 읽는게 유리하므로 first 가 드리븐 테이블로 선택 

- 둘 다 없다면 통계정보에 따라 드라이빙 테이블이 결정되고 어느쪽이든 드리븐 테이블을 읽을때 풀 테이블 스캔이 걸린다.

 

즉, 두 테이블 모두 인덱스가 없을때만 풀 테이블 스캔이 발생한다.

 

 

서브 쿼리

 

서브쿼리가 사용되는곳

 

SELECT 에서의 서브쿼리 

 

select 절에서는 서브쿼리가 내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 만들지 않기 때문에

서브 쿼리가 인덱스를 적절하게 사용한다면 크게 문제될건 없다.

 

다만, 조인으로 사용해도 될걸 서브 쿼리로 사용하는 경우가 있는데, 조인이 훨씬 빠르기 때문에

이럴땐 서브쿼리보다 조인으로 작성하는 것이 좋다.

 

WHERE 절에 비교를 위해 사용된 서브쿼리

MySQL 에서 서브쿼리를 매번 실행해서 서브 쿼리가 포함된 조건이 참인지 비교하기 때문에

WHERE IN (서브쿼리) 같은건 가능하면 JOIN 으로 변경하는게 좋다.

 

FROM 절에 사용된 서브 쿼리

 

FROM 절의 서브쿼리는 임시테이블을 사용하므로 최적화가 안되는 경우가 많다.

가능하면 join으로 변경하는게 좋다.