SQL Study

SQL

목록 보기

9/9

오늘 학습해 볼 내용은 서브쿼리.

❗ Sub Query란 ?

Query안에 들어가는 또다른 쿼리를 의미합니다.
--e.g.)
SELECT *
FROM TABLE_NAME
WHERE CONDITION = (
	SELECT ...
    FROM TABLE_NAME
); 
보통 실무에서는 아래와 같은 목적으로 사용합니다.

특정 조건값을 동적으로 구하기 (평균보다 큰 값)

중간 결과를 만든 뒤 재사용 (최신 데이터만 사용)

존재 여부 확인 (특정 조건이 적용된 데이터만 조회)

제외 조건 처리

순위/최신값 추출

그렇다면, 서브쿼리의 종류에는 어떤 것들이 있을까?

1. 스칼라 서브쿼리

결과가 1행 1열만 나오는 서브쿼리. (SELECT 혹은 WHERE 절에 적용)
--e.g.) 평균 C보다 큰 데이터 1개만 추출하기
SELECT *
FROM TABLE
WHERE C > (
	SELECT AVG(C)
    FROM TABLE
);

2. In 서브쿼리

결과가 여러 개인 경우 IN을 사용 (WHERE 절에만 적용)
SELECT *
FROM TABLE
WHERE A IN (
	SELECT A
    FROM TABLE
    WHERE D = 'CONDITION'
);

3. EXISTS 서브쿼리

값 목록을 비교하는 것이 아닌, 존재 여부를 확인 (WHERE 절에만 적용)
SELECT *
FROM TABLE AS S
WHERE EXISTS (
	SELECT 1
    FROM TABLE AS A
    WHERE A.ID = S.ID
    	AND A.D = 'CONDITION'
);
이떄 예제에 사용되는 SELECT 1이란, EXISTS 내부에서 "조건을 만족하는 행이 존재하는지만 확인하겠다"는 의미

EXISTS는 보통 대용량 테이블에서 매칭 여부 확인 또는 존재 여부만 확인할 때 사용

NOT EXISTS는 보통 제외되는 조건에 적용하거나 NULL 이슈를 피할 때 사용

4. 상관 서브쿼리

바깥 서브쿼리 값을 안쪽 쿼리가 참조하는 구조 (WHERE 절에만 적용)

-- e.g.) ID 별 평균 VALUE보다 큰 RESULT만 조회.
SELECT
	A.ID
    , A.VALUE
    , A.RESULT
FROM TABLE AS A
WHERE A.VALUE > (
	SELECT AVG(B.VALUE)
    FROM TABLE AS B
    WHERE B.ID = A.ID
)

5. 인라인 뷰

FROM 절 안에 들어가는 서브쿼리로, 테이블 형태로 결과가 출력되며 중간 결과 생성 시 활용할 수 있음.

-- e.g.) ID 및 ITEM별 가장 최근 최근 결과만 조회
SELECT 
	A.ID,
    A.ITEM,
    A.RESULT
FROM (
	SELECT
    	ID,
        ITEM,
        RESULT,
        ROW_NUMBER() OVER (
        	PARTITON BY ID, ITEM
            ORDER BY DATE DESC
        ) AS ranking 
    FROM TABLE
) A
WHERE ranking = 1;

❗SQL 실행계획 (1)

DB가 SQL을 어떻게 실행할 지 보여주는 지도라고 생각하면 됌.

SQL 튜닝에서는 결과가 올바른가 보다 다음 과정을 확인해야 함.

이 쿼리가 데이터를 어떻게 읽는지?

어떤 순서로 조인하는지?

어디서 많이 줄어드는지?

어디서 불필요한 정렬이 이루어지는지?

예상 행 수가 실제 행 수와 일치하는지?

1. Full Table Scan

테이블 전체를 읽는 방식으로 Oracle에선 보통 TABLE ACCESS FULL 이런 식으로 확인할 수 있음.

단순히 테이블 전체를 읽는 방식이면 무조건 안 좋은 것 아닌가? 라는 생각이 들 수 있으나, 항상 그런 건 아니다.

테이블 용량이 작거나, 전체 데이터 대부분을 조회할 경우, 대량 집계를 진행할 경우엔 활용되도 사실 문제는 없다.

2. Index Scan

인덱스를 이용해 데이터를 찾는 방식으로 조건 선택도와 인덱스 구조가 가장 중요하다.
보통 INDEX UNIQUE/RANGE/FULL SCAN 과 같은 표기가 나온다.

INDEX UNIQUE SCAN : 고유 인덱스로 1건 또는 극소량을 조회하는 방법

INDEX RANGE SCAN : 범위를 조건으로 조회하는 방법. (튜닝에서 가장 중요)

INDEX FULL SCAN : 인덱스 전체를 순서대로 읽음

INDEX FAST FULL SCAN : 인덱스를 테이블처럼 빠르게 전체 스캔

3. Join 방식

Nested Loop Join : 작은 테이블에서 한 건 씩 가져와 큰 테이블을 반복 조회
(선행 테이블 결과가 적은 경우에 적합함.)

Hash Join : 한 테이블을 해시 테이블로 만들어 他 테이블과 매칭하는 방식

Sort Merge Join : 양쪽 테이블의 데이터를 정렬 후 병합하는 방식

이때 실행 계획 단계에서 sorting 시 정렬 비용 즉 계산 비용이 발생하게 되는데
대표적인 함수는 ORDER BY, GROUP BY, DISTINCT, UNION, 윈도우 함수 등이 있다.

❗SQL 실행계획 (2)

어제에 이어서 "DB가 SQL을 실제로 어떻게 처리하는지 보여주는 설계도"인 실행 계획에 대해 더 깊게 파악해보고자 합니다.

실행 계획에서 가장 먼저 확인해야 할 사항은 약 7가지로, 아래와 같습니다.

확인 항목 의미 중요도
Access Method 테이블을 어떻게 읽는가: Full Scan, Index Scan 등 매우 높음
Join Method 조인을 어떻게 수행하는가: Nested Loop, Hash Join, Merge Join 매우 높음
Join Order 어떤 테이블부터 읽는가 매우 높음
Rows / Cardinality 옵티마이저가 예상한 행 수 매우 높음
Cost 옵티마이저가 계산한 상대 비용 중간
Predicate WHERE 조건이 어디서 적용되는가 매우 높음
Sort / Temp 사용 여부 정렬, 그룹핑, 임시 공간 사용 여부 높음

주요 핵심 내용은 5가지다.

Table Access Full (전체 조회)

Index Scan (인덱스 기반 조회)

Cost (예상 작업량)

Cardinality & Rows (예상 결과의 row 수)

Join Method (테이블 연결시 사용 방법, LEFT, RIGHT 등.)

실행 계획을 보는 기본 명령어
-- Oracle
EXPLAIN PLAN FOR 
SELECT *
FROM TABLE
WHERE Column = 'Coniditon';

SELECT *
FROM TABLE(DBMX_XPLAN.DISPLAY);

-- MySQL 
EXPLAIN 
SELECT *
FROM TABLE
이처럼 실행 계획을 확인할 때 가장 먼저 확인해야 하는 사항은 크게 4가지다.

FULL SCAN을 수행하는지?, INDEX를 사용하는지?, 예상되는 행이 몇갠지?, JOIN(작은 테이블부터 연결하는지?)

결과적으로 쿼리 실행 계획을 효과적으로 달성하기 위해선,
원하는 결과를 얻기 이전에 어떻게 쿼리를 작성해야 효과적인 결과를 얻을 수 있을 지 고민을 하고 작성하는 습관을 들여야 한다.

확인 항목	의미	중요도
Access Method	테이블을 어떻게 읽는가: Full Scan, Index Scan 등	매우 높음
Join Method	조인을 어떻게 수행하는가: Nested Loop, Hash Join, Merge Join	매우 높음
Join Order	어떤 테이블부터 읽는가	매우 높음
Rows / Cardinality	옵티마이저가 예상한 행 수	매우 높음
Cost	옵티마이저가 계산한 상대 비용	중간
Predicate	WHERE 조건이 어디서 적용되는가	매우 높음
Sort / Temp 사용 여부	정렬, 그룹핑, 임시 공간 사용 여부	높음