2023/02/02 ~ 2023/02/07

11.4.10.2 FROM 절에 사용된 서브쿼리

MySQL 5.7 이전에는 FROM 절에 서브쿼리가 사용되면 항상 서브쿼리의 결과를 임시 테이블로 저장하고 필요할 때 다시 임시 테이블을 읽는 방식으로 처리
MySQL 5.7 부터는 옵티마이저가 FROM 절의 서브쿼리를 외부 쿼리로 병합하는 최적화를 수행하도록 개선됨
병합 예시 (p. 109)
- EXPLAIN 명령을 실행한 후 SHOW WARNINGS 명령을 실행하면, 서버가 서브쿼리를 병합해서 재작성한 쿼리내용 확인 가능
FROM 절에 사용된 뷰(View)의 경우에도 뷰 쿼리와 외부 쿼리를 병합해서 최적화된 실행 계획을 사용함
FROM 절의 서브쿼리가 외부 쿼리로 병합되지 못하는 경우
- 집합 함수 사용(SUM(), MIN(), MAX(), COUNT() 등)
- DISTINCT
- GROUP BY 또는 HAVING
- LIMIT
- UNION(UNION DISTINCT) 또는 UNION ALL
- SELECT 절에 서브쿼리가 사용된 경우
- 사용자 변수 사용(사용자 변수에 값이 할당되는 경우)
FROM 절의 서브쿼리를 외부 쿼리로 병합하는 최적화는 optimizer_switch 시스템 변수로 제어 가능 (9.3.1.16 참고)

11.4.10.3 WHERE 절에 사용된 서브쿼리

SELECT 절이나 FROM 절보다는 다양한 형태(연산자)로 사용될 수 있음

11.4.10.3.1 동등 또는 크다 작다 비교

MySQL 5.5 이전 버전까지는 서브쿼리 외부의 조건으로 쿼리를 실행하고, 최종적으로 서브쿼리를 체크조건으로 사용함
- 이 경우 풀 테이블 스캔이 필요한 경우가 많음
MySQL 5.5 버전부터는 서브쿼리를 먼저 실행한 후 상수로 변환한 뒤, 상숫값으로 서브쿼리를 대체해서 나머지 쿼리 부분을 처리함
단일 값 비교가 아닌 튜플 비교 방식이 사용되면 서브쿼리가 먼저 실행되어 상수화되긴 하지만 외부 쿼리는 풀 테이블 스캔을 실행함
- 따라서 8.0 버전이라고 하더라도 튜플 형태의 비교는 주의해서 사용할 것 (p.112 ~ 113)

11.4.10.3.2 IN 비교 ( IN (subquery) )

세미 조인(Semi-Join): 실제 조인은 아니지만 테이블의 레코드가 다른 테이블의 레코드를 표현식과 일치하는지 체크하는 형태
- IN 형태의 조건을 조인의 한 방식인 세미 조인이라고 봄
- ex) SELECT * FROM employee e WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de ... )
MySQL 5.5 버전까지는 대부분 풀 테이블 스캔을 했으나 5.6 버전 부터는 세미 조인의 최적화가 많이 개선됨
세미 조인 최적화 전략 (5개. 선택적으로 사용. 9.3.1.9절 참고)
- 테이블 풀-아웃(Table Pull-out)
- 퍼스트 매치(Firstmatch)
- 루스 스캔(Loosescan)
- 구체화 (Materialization)
- 중복 제거(Duplicated Weed-out)

11.4.10.3.3 NOT IN 비교 ( NOT IN (subquery) )

IN (subquery)와 비슷한 형태이지만 이 경우는 안티 세미 조인(Anti Semi-Join)이라고 함
안티 세미 조인 최적화 전략 (2개)
- NOT EXISTS
- 구체화 (Materialization)
두 전략 모두 성능 향상에 그다지 도움이 되지 않으므로 최대한 다른 조건을 활용해서 데이터 검색 범위를 좁히도록 하는 것이 좋음
WHERE 절에 단독으로 안티 세미 조인 조건만 있다면 무조건 풀 테이블 스캔

11.4.10 서브쿼리

서브쿼리를 사용하면

단위 처리별로 쿼리를 독립적으로 작성할 수 있다
쿼리의 가독성 향상
복잡한 쿼리도 손쉽게 작성

5.6 버전까지는 서브쿼리를 최적으로 실행하지 못할 때가 많았지만

8.0 버전 부터는 서브쿼리 처리가 많이 개선됨

여러 위치에 서브쿼리를 사용할 수 있는데, 대표적으로 SELECT, FROM, WHERE 절에서 사용

11.4.10.1 SELECT 절에 사용된 서브쿼리

서브쿼리가 적절히 인덱스를 사용할 수 있다면 크게 주의할 사항 없음

내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 실행하게 만들지는 않기 때문

일반적으로 SELECT 절에 서브쿼리를 사용하면 항상 칼럼과 레코드가 하나인 결과를 반환해야 한다

NULL이든 아니든 관계없이 레코드가 1건이 존재해야함

MySQL에서는 이 체크 조건이 조금은 느슨하다

SELECT emp_no, (SELECT dept_name FROM departments WHERE dept_name='Sales1')
FROM dept_emp LIMIT 10;

해당 서브쿼리는 항상 결과가 0건이지만 에러가 발생하지 않고

서브쿼리의 결과가 NULL로 채워져서 반환됨

SELECT emp_no, (SELECT dept_name FROM departments)
FROM dept_emp LIMIT 10;

서브쿼리가 2건 이상의 레코드를 반환하는 경우에는 에러가 나면서 쿼리가 종료

SELECT emp_no, (SELECT dept_no, dept_nmae FROM departments WHERE dept_name='Sales1')
FROM dept_emp LIMIT 10;

SELECT 절에 사용된 서브쿼리가 2개 이상의 칼럼을 가져오려고 할 때도 에러가 발생

결국 SELECT 절의 서브쿼리에는 로우 서브쿼리를 사용할 수 없고, 오로지 스칼라 서브쿼리만 사용할 수 있다

서브쿼리가 만들어내는 결과에 따라 스칼라 서브쿼리와 로우 서브쿼리로 나뉘어진다

스칼라 서브쿼리

레코드의 칼럼이 각각 하나인 결과를 만들어 내는 서브쿼리

로우 서브쿼리(레코드 서브쿼리)

스칼라 서브쿼리보다 레코드 건수가 많거나 칼럼 수가 많은 결과를 만들어 내는 서브쿼리

조인으로 처리해도 되는 쿼리를 SELECT 절의 서브쿼리를 사용해서 작성할 때도 있는데

서브쿼리로 실행될 때 보다 조인으로 처리할 때가 조금 더 빠르다

106p 쿼리를 보면 두 예제 쿼리 모두 employees 테이블을 두 번씩 PK 를 이용해 참조하는 쿼리이고

첫 번째 쿼리는 emp_no PK라서 조인이나 서브쿼리 중 어떤 것을 사용해도 둘 다 같은 결과가 나온다

서브 쿼리를 사용한 첫 번째 쿼리는 평균 0.78초

조인을 사용한 두 번째 쿼리는 0.65초가 나왔는데

처리해야 하는 레코드 건수가 많아질수록 성능차이가 커져서 가능하면 조인으로 쿼리를 작성하는 방법을 권장

SELECT 절에 서브쿼리가 사용되는 경우 동일한 서브쿼리가 여러 번 사용되기도 한다

107p 쿼리에서 LIMIT 1 조건 때문에 salaries 테이블을 조인으로 사용할 수가 없었는데

MySQL 8.0 버전부터 도입된 래터럴 조인을 이용하여 동일한 서브쿼리를 남용하지 않아도 된다

107p 래터럴 조인으로 변경된 쿼리는 salaries 테이블을 한 번만 읽어서 쿼리를 처리할 수 있다

8.0 버전의 래터럴 조인이 가지는 문제점

서브쿼리를 사용하는 쿼리와 래터럴 조인을 사용한 쿼리를 실행했을 때 MySQL 서버의 상태 값 변화를 보면

래터럴 조인은 내부적으로 임시 테이블을 생성하기 때문에 Handler_write 값과 Handler_read_key 값이 증가할 수 있다

서브쿼리를 사용한 경우 테이블을 여러 번 읽기 때문에 Handler_read_key 값이 증가할 수 있는 실행 계획이 맞음

그런데 래터럴 조인을 사용한 쿼리에서 handler_read_next 값이 6이 된 것은 잘못된 것

실행 계획에서 Using filesort가 표시됨
인덱스를 이용해 충분히 정렬된 결과를 가져올 수 있었음
그럼에도 불구하고 정렬을 실행하여 해당 값이 6으로 증가
8.0 버전의 버그로 식별됐고, 아직 해결되지 않은 상태

mojh7 / real-mysql-study