시진엽
SELECT 절에 사용된 서브쿼리
- 내부적으로 임시테이블을 만들거나 쿼리를 비효율적으로 실행되지 않도록 만들기 때문에 서브쿼리가 적절히 인덱스를 타게되면 좋은 성능을 보임
- SELECT 절에 서브쿼리 사용 시 항상 칼럼과 레코드가 하나인 결과를 반환함
다만, 그 값이 NUll이든 관계없이 레코드가 1건이 존재해야함
1번) NUll값을 사용 / 2번) 1건이 아닌 10건을 가져와서 에러남 / 3번) 컬럼이 2개 이상이라 에러남
- SELECT 절의 서브쿼리는 로우 서브쿼리를 사용할 수 없고, 스칼라 서브쿼리만 사용가능함
-스칼라 서브쿼리란, 레코드의 칼럼이 각각 하나이 결과를 만들어난 서브쿼리
-로우 서브쿼리(레코드 서브쿼리)란, 레코드 건수가 많거나 칼럼수가 많은 결과를 만들어낸 서브쿼리 - 조인을 사용할 수 있는 쿼리를 SELECT 절에 서브쿼리를 사용할 수 있음
하지만, 서브쿼리 사용보다 조인이 좀더 빠르기 때문에 조인으로 쿼리를 작성하는것이 좋음
FROM절에 사용된 서브쿼리
- FROM 절의 서브쿼리 사용 불가능 할때
집합 함수 사용(SUM, MIN, MAX, COUNT등)
DISTINCT
GROUP BY, HAVING
LIMIT
UNION
SELECT 절에 서브쿼리 사용
WHERE 절에 사용된 서브쿼리
- 동등비교, 크다작다 비교
예전에는 dept_emp 테이블을 풀스캔 하면서 서브쿼리의 조건에 일치하는지 확인
지금은 서브쿼리를 상숫값으로 변환 후 나머지 쿼리문을 진행함
- IN 비교
세미조인이란, 테이블의 레코드가 다른 테이블의 레코드를 이용한 표현식
즉, WHERE 절에 IN 형태가 세미조인
예전의 세미조인은 테이블 풀스캔 진행하여 성능저하라는 평가가 많았음
지금은 최적화가 많이 되서 사용해도 괜찮음
- NOT IN 비교 안티 세미조인
IN과 다르게 NOT IN은 인덱스를 제대로 활용 할 수 없어서 성능향상에 도움이 많이 되지는 않음
WHERE 절에 NOT IN 만 사용 할 경우 테이블 풀스캔 진행
CTE(common table expression)
- CTE란, 임시테이블 쿼리가 종료되면 자동으로 임시테이블은 삭제됨
비 재귀적 CTE
- WITH 절을 이용하여 CTE를 정의함
WITH cte1 AS (SELECT * FROM departments)
SELECT * FROM CTE1;
SELECT * FROM (SELECT * FROM departments) cte1
1번) 임시테이블을 생성하여 사용한것
두 쿼리문은 동일한 쿼리문이라고 볼 수있음
여러개의 임시테이블 생성 후 사용
임시테이블로 만든 테이블을 다시 임시테이블에서 참조 할 수 있음
(이때 순서 중요, 뒤에선 앞의 임시테이블을 참조 할수 없음)
- CTE 임시테이블은 재사용이 가능하므로 FROM 절의 서브쿼리보다 효율적임
- CTE 는 다른 CTE에서 참조 가능함
- CTE와 본 쿼리문을 분리 할 수 있어 가독성이 높음
재귀적 CTE
- mysql 8.0 부터 재귀 쿼리가 가능함
재귀적으로 사용되는 CTE의 예제
- 재귀적 CTE쿼리는 비재귀적 쿼리와 재귀적 쿼로 구분됨
- UNION, UNION ALL로 연결하는 형태로 반드시 쿼리를 작성해야함
- UNION ALL 위의 SELECT 쿼리는 비재귀적 파트, 아래는 재귀적 파트
- 비재귀적 파트는 처음 한번만 실행되고, 재귀적 파트는 결과가 없을 때까지 반복 실행됨
- CTE 임시테이블의 구조(칼럼명과 데이터 타입)는 비재귀적 파트의 결과로 결정됨
비재귀, 재귀 파트의 구조가 다를경우 비재귀적 파트의 구조를 따라감 - 비재귀 파트는 초기데이터와 테이블 구조를 준비하고, 재귀적 파트는 데이터를 생성하는 역할을 수행함
- 재귀적 파트를 실행 할 때는 지금까지의 모든 단계에서 만들어진 결과 셋이 아니라 직전단계의 결과만 재귀쿼리의 입력으로 사용됨
❓ 이게 무슨말이죠..? 책 119 하단 부분..
- 반복실행의 종료 조건
재귀 파트의 쿼리의 결과가 0건 일 때 종료됨
❓ WHERE 조건이 있으면 조건을 따라서 종료되고 그게 없으면 결과가 0건이면 종료된다는 걸까요?
- 무한루프 탈출 방법
종료조건을 만족하지 못해서 무한루프를 돌 경우를 대비해 cte_max_recursion_depth 시스템 변수를 이용해 최대 반복 횟수를 제한함
기본값은 1000이라 너무 큼;;
그래서 SET_VAR 힌트를 이용해 반복호출 횟수를 정하는것도 좋은방법
