SQL문 분석
학습목표 : 실행 계획을 분석해보고 SQL 작성 방법 및 쿼리 최적화 방법을 익힌다.
1번쿼리
SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
FROM salaries s INNER JOIN employees e ON e.emp_no=s.emp_no
WHERE s.emp_no IN (100001, 100002)
GROUP BY s.emp_no
HAVING AVG(s.salary) > 1000
ORDER BY AVG(s.salary) LIMIT 10;
++ salaries의 PK는 emp_no이고, employees의 PK도 emp_no이다.
=> 스트리밍 방식으로 처리한다.
=> 그룹화를 평균 salary가 1000 초과인 것들만 한다.
=> salary로 정렬한다.
=> LIMIT 적용
**여기서 잠깐
Storage 엔진(InnoDB) : 데이터의 탐색, 저장을 맡는다.
MySQL엔진 : Storage 엔진이 탐색한 데이터셋에서 필터링을 맡는다.
만약 MySQL엔진이 필터링을 했다면, Extra 칼럼에 Using where이 표시된다.(filtered에 체크 조건으로 몇 row가 필터링 됐는지 볼 수 있음)
스트리밍 방식 : Storage 엔진이 데이터를 찾았다면, 바로 MySQL엔진이 가공 후 클라이언트에게 보내주는 방식.
WHERE절 중
범위 조건 : Storage 엔진이 인덱스나, 클러스터링을 이용해 범위를 찾는다.
체크 조건: 범위 조건을 필터링한다.
Using where: 범위 조건이 아닌 체크조건이 적용되었을때 표시
Using filesort: ORDER BY가 적절한 인덱스로 처리하지 못할때, 표시. 적절한 인덱스를 두는 최적화가 필요하겠다.
Using temporary: MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아두기 위해 임시 테이블을 사용했을 때 표시.
=>Using temporary가 표시되지 않아도, 임시테이블을 사용할 때가 있다. 아래는 임시 테이블을 사용하는 예시이다.
=> FROM 절에 사용된 서브쿼리
=> COUNT(DISTINCT col1)에서 인덱스를 사용할 수 없을 때.
=> 인덱스를 사용하지 못하는 정렬 작업(정렬 작업의 범위가 커질 때, 디스크에 임시 테이블을 저장하기 때문에 조심해야한다. 매우 느리다.)
분석
1.스트리밍 방식으로 처리한다.
EXPLAIN ANALYZE에서 WHERE절과 JOIN이 생략되었음을 볼 수 있다. 즉, 이 두 절은 스트리밍으로 퉁 쳤다는 건데 무슨 관계가 있을까? ==> WHERE과 JOIN이 스트리밍 방식으로 처리된다. 때문에, 최적화 처리하지 않아도 매우 빠름을 볼 수 있다. 또한, 둘 다 클러스터링 인덱스 조인과 클러스터링 레인지 스캔으로 WHERE절을 처리하고 있기 때문에, JOIN은 INDEX LOOKUP, WHERE절은 범위 조건으로 매우 빠르다.
2. 그룹화를 평균 salary가 1000 초과인 것들만 한다.
3. salary로 정렬한다.
4 LIMIT 적용
2번쿼리
SELECT e.emp_no, e.first_name, s.from_date, s.salary
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
LIMIT 10;
=> 커버링 인덱스 스캔을 통해 e 테이블의 PK를 확인한다.(이때, ix_firstname을 사용)
=> 커버링 인덱스 스캔으로 읽어온 e 테이블의 PK를 이용해 emp_no=e.emp_no를 NLJ로 조인
=> LIMIT 실행
분석
1. 커버링 인덱스 스캔을 통해 e 테이블의 PK를 확인한다.(이때, ix_firstname을 사용)
JOIN을 위해서는 NLJ가 필요하다. 이 말은 드라이빙 테이블은 범위 테이블 스캔이 필요하다는 것이다. 이때, WHERE 절로 범위를 줄이는 조건이 없다면, 풀 테이블 스캔이나, 풀 인덱스 스캔이 필요하다. 하지만, 풀 테이블 스캔은 풀 인덱스 스캔보다 느리기 때문에 풀 인덱스 스캔을 선택해야 했다. 옵티마이저는 풀 인덱스 스캔을 사용하기 위해, ix_firstname을 사용했다.
2. 커버링 인덱스 스캔으로 읽어온 e 테이블의 PK를 이용해 emp_no=e.emp_no를 NLJ로 조인
1번에서 읽은 PK들을 NLJ로 조인한다.
3. LIMIT 실행
LIMIT을 통해 NLJ는 단 10개의 rows를 읽었다.
3번쿼리
SELECT e.*
FROM salaries s, employees e
WHERE e.emp_no=s.emp_no
AND s.emp_no BETWEEN 10001 AND 13000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10;salaries 인덱스 PK(emp_no), (salary)
employees 인덱스 PK(emp_no)
=> 옵티마이저가 조인을 시도
=> 두 테이블 다 클러스터링 인덱스 탐색이 가능
=> row를 보고 결정할 것
=> e를 드라이빙 테이블로 선택해 스캔
=> 3000개의 클러스터링 레인지 스캔
=> 등가 조인으로 s.emp_no BETWEEN 10001 AND 13000을 e.emp_no BETWEEN 10001 AND 13000으로 변형 실행
=> 조인 완료, temporary 테이블 생성 저장
=> GROUP BY 실행
=> ORDER BY 실행
=> LIMIT 10 실행
**즉, JOIN 수행 후 GROUP BY 와 ORDER BY를 수행했다.
=> JOIN 수행 이전에 GROUP BY와 ORDER BY를 수행하게 만들어야 한다.
=> 대부분의 쿼리 최적화는 조인 이전에, GROUP BY, ORDER BY를 이용해 조인 처리 행 수를 줄이는 것!
이 경우, 서브 쿼리를 이용하여 최적화 할 수 있겠다.
SELECT *
FROM (
SELECT s.emp_no
FROM salaries s
WHERE s.emp_no BETWEEN 10001 AND 13000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10
) x, employees e
WHERE e.emp_no=x.emp_no;=> 지연된 조인을 이용해, 조인 이전에 ORDER BY와 GROUP BY를 써주게 했다.
