최근 Real MySQL 8.0 책 스터디를 하다보니 쿼리 최적화하는데 관심이 생겼다.
그러다보니 그 전까지는 신경쓰지 않았던 쿼리들 실행계획을 살펴보며 쿼리를 최적화하고있다.
주로 실행계획에서 인덱스를 사용하지 않고 fullscan 하는 쿼리들을 튜닝하고 인덱스를 생성하는 작업을 하고 있는데,
where 절 컬럼 인덱스를 생성해도 fullscan을 하는 쿼리를 발견했다.
서브쿼리를 에서 SELECT 한 PRIMARY 키를 메인 쿼리 IN 절로 조회하는 쿼리 였다.
당연히 PK 로 IN 절에서 조회하므로 인덱스를 사용해 rows 수가 서브쿼리에서 조회된 수만 나올줄 알았는데 메인쿼리의 모든 row 수가 나왔다.
이 과정을 이해하려면 옵티마이저에서 작동하는 서브쿼리 처리에 대해서 이해해야한다.
-- 서브쿼리절 사용
select * from employees where emp_no in (select max(emp_no) from dept_emp group by dept_no);
-- 명시적 숫자 사용
select * from employees where emp_no in (49998,49999,49997,49992);
IN 절에 서브쿼리를 사용한 경우와 비교값이 명시적으로 주어진 경우를 비교해서 살펴보자.
서브쿼리 절에서는 9개의 행이 조회되었고 그 값들이 PK 값임에도 불구하고 인덱스를 사용하지 않고 풀스캔 하였다.
명시적 조건 사용한 IN 절의 경우 PK를 사용해서 `인덱스 레인지 스캔`을 수행하였다.
1. 옵티마이저와 서브쿼리
서브쿼리가 있는경우 이 결과 값은 동적으로 생성되며 고정된 값이 아니다.
옵티마이저는 서브쿼리 결과를 캐시 할 수 없으므로, 각 비교마다 상위 케이블의 모든 행을 스캔할 수도 있다.
employees 테이블의 emp_no 값이 분포가 넓고 서브쿼리 결과가 많지 않다면, 옵티마이저는 전체 테이블을 스캔하는 것이 더 빠르다고 판단할 수 있다.
2. 인덱스 사용조건
인덱스를 사용하려면 where 절 조건이 인덱스 키를 포함하거나, 조건의 값이 고정된 값이나 단순한 범위 조건이어야 한다.
서브쿼리 결과는 고정된 값이 아니어서 인덱스를 사용하기 어렵다.
3. 개선방법
왠만하면 where 절 서브쿼리를 사용하지 않는것이 좋다. 대신 서브쿼리를 join 으로 변경했다.
explain
select * from employees a
join (select max(emp_no) as max_emp_no from dept_emp group by dept_no) b
on a.emp_no = b.max_emp_no;
그러면 서브 쿼리 결과값으로 나온 행만 제한해서 JOIN 하기 때문에 fullscan을 수행한다 해도 rows 수를 줄일 수 있다.
다만 실행계획을 보면 select_type 에 DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성한다.
실행 계획을 확인하고 가능 하면 DERIVED 형태의 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.