Ray`s Tech Lab

Subquery란

SQL 쿼리 내에서 또다른 SELECT절을 사용하는 문법을 쓸 때, 내부에 포함되는 SELECT절을 서브쿼리(Subquery)라 부른다. SELECT 절에서 사용되는 서브쿼리를 스칼라 서브쿼리(Scala Subquery), FROM절에서 사용되는 서브쿼리를 인라인뷰(Inline View), WHERE절에서 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 부른다.

Scala Subquery란

SELECT절에서 사용되는 서브쿼리로, 반드시 하나의 record(행)을 리턴해야하며 그 행도 하나의 컬럼값만 가지고 있어야 한다. (즉 단일행, 단일 컬럼을 반환해야 한다)

예제로, 다음과 같은 두 개의 테이블이 있다고 하자. DEPARTMENTS는 회사에 있는 부서들의 부서id와 부서명을, EMPLOYEES는 임직원들의 이름과 소속 부서id 등을 가진다.

이 때, 임직원들의 이름과 그들이 속한 부서의 이름을 함께 조회하고 싶다고 하고, 스칼라 서브쿼리를 사용해본다고 하자. 이때, 스칼라 서브쿼리의 FROM절에서 DEPARTMENTS를 참조하는가, EMPLOYEES를 참조하는가를 참조하는가를 주의해야 한다. 

스칼라 서브쿼리의 FROM절에서 DEPARTMENTS를 참조할 경우의 쿼리와 결과는 다음과 같다.

SELECT EMP_NAME, (
    SELECT DEPT_NAME
    FROM DEPARTMENTS d
    WHERE d.DEPT_ID = e.DEPT_ID) 
FROM 
    EMPLOYEES e;

스칼라 서브쿼리의 FROM절에서 EMPLOYEES를 참조할 경우의 쿼리와 결과는 다음과 같다.

SELECT (
        SELECT EMP_NAME
        FROM EMPLOYEES e
        WHERE d.DEPT_ID = e.DEPT_ID
    ), DEPT_NAME 
FROM 
    DEPARTMENTS d;

서브쿼리의 FROM절에서 DEPARTMENTS를 참조하는 경우, d.DEPT_ID = e.DEPT_ID를 만족하는 레코드가 하나만 나오기 때문에 문제가 되지 않는다. 하지만 서브쿼리의 FROM절에서 EMPLOYEES를 참조하는 경우, d.DEPT_ID = e.DEPT_ID를 만족하는 레코드가 다수가 나오기 때문에 문제가 되고, 위와 같은 오류가 생기는 것이다.

즉, 스칼라 서브쿼리는 반드시 하나의 레코드와 하나의 컬럼값을 리턴해야 한다.

스칼라 서브쿼리의  성능 측면에서의 문제점 & LEFT OUTER JOIN으로 변환

쿼리는 FROM(과 JOIN) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순으로 실행된다. 즉 특정 테이블에서 특정 조건에 맞는 레코들들을 모두 뽑아오고, 해당 레코드들에 대해 select절에서 특정 컬럼들을 뽑아내는 식으로 동작한다.  스칼라 서브쿼리는 건수만큼 반복해서 수행된다. 데이터가 많아질수록 성능 저하의 주범이 될 수 있는 것이다.

따라서, LEFT OUTER JOIN과 Inline View를 활용하는 식으로 쿼리를 바꿔 성능을 개선시킬 수 있다. 다음 쿼리를 살펴보자.

SELECT DEPT_NAME, (
    SELECT COUNT(*)
    FROM EMPLOYEES e
    WHERE e.DEPT_ID = d.DEPT_ID
) NUMS
FROM DEPARTMENTS d;

부서별로 부서명과 소속 인원들의 이름을 보는, 스칼라 서브쿼리를 쓰는 쿼리다. 이를 LEFT OUTER JOIN과 Inline View을 활용하는 형태로 다음과 같이 바꿔쓸 수 있다.

SELECT DEPT_NAME, c.NUMS
FROM DEPARTMENTS d
LEFT OUTER JOIN (
    SELECT e.DEPT_ID, COUNT(*) AS NUMS
    FROM EMPLOYEES e
    GROUP BY e.DEPT_ID
) c
ON d.DEPT_ID = c.DEPT_ID;

기존에는 해당 서브쿼리가 건수만큼 반복돼서 수행됐지만, 이렇게하면 한 번만 수행되고 끝난다. 

하지만 늘상 스칼라 서브쿼리를 LEFT OUTER JOIN으로 바꾼다고 성능 향상이 되는 건 아니다(그러면 스칼라 서브쿼리 아무도 안 쓰지..). 상황별로 스칼라 서브쿼리를 쓰는게 이득일 때도 있다. 다만 스칼라 서브쿼리를 쓸 때는 속도를 고려하는 습관을 가지자.

다음과 같은 테이블이 있다고 하자.

SALES_HISTORY 테이블은 특정 상품들과 해당 상품들이 판매된 시간, 판매된 가격 정보를 가진다.

1. GROUP BY를 통한 집계

GROUP BY를 통해 특정 컬럼(들)을 기준으로 그룹핑한 다음 집계함수를 활용한 합이나 평균값의 산출이 가능하다. 본 글에서는 SUM을 활용한 상품들의 일자별 매출액을 조회하는 상황을 가정한다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY;

2. WITH ROLLUP을 가미한 집계

WITH ROLLUP을 가미하면, GROUP BY로 보여지는 결과물 뿐만 아니라 그룹핑한 각각의 소그룹별 집계와 전체 집계를 볼 수 있다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY WITH ROLLUP;

NULL값으로 표기된 부분들이 WITH ROLLUP을 통해 산출된 집계값이다. 1000번 상품 그룹에 대한 집계와 1001번 상품에 대한 집계, 1002번 상품에 대한 집계와 더불어 전체 집계 또한 나온 것을 확인할 수 있다. 

MySQL, 그리고 InnoDB의 Index

인덱스(Index)는 DB에서 테이블의 데이터를 빠르게 검색하기 위해 사용되는 보조적인 자료구조로, 인덱스와 실제 데이터는 따로 보관 및 관리된다. 전통적으로 인덱스를 위해 가장 많이 사용되는 자료구조는 B-tree (Balanced Tree)로, Leaf Node는 실제 데이터 레코드를 찾아갈 수 있는 주솟값을 가지고 있다.

MySQL 역시 B-tree 기반 인덱스를 사용하며, InnoDB 엔진의 경우 클러스터링 인덱스(클러스터링 테이블이라고도 부름)를 사용하는데 이는 테이블에서 PK(Primary Key)값이 비슷한 레코드들끼리 묶어 저장하는 걸 의미한다. 즉 스토리지 엔진으로 InnoDB를 사용한다면 아무 것도 하지 않아도 알아서 PK값들에 의해 B-tree 구조로 테이블의 데이터가 저장된다(각 Leaf Node가 레코드의 모든 컬럼 값을 가짐). 즉 PK값에 따라 레코드의 저장 위치가 결정되는 것이며, 만약 PK값이 변경된다면 해당 레코드가 저장되는 물리적 위치 또한 바뀌게 됨을 의미한다. 따라서 이렇게 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르나 레코드의 저장이나 PK 변경은 상대적으로 느리다.

이 특성 때문에, 세컨더리 인덱스에 대한 B-tree를 기준으로 볼 때 스토리지 엔진으로 MyISAM을 사용할 경우 Leaf Node가 실제 레코드의 물리적인 주소들을 가지는 반면 InnoDB는 Leaf Node가 PK를 가진다. 즉 InnoDB를 사용할 경우 세컨더리 인덱스를 통해 데이터를 읽을 때 데이터를 바로 찾아가는 게 아니라, Leaf Node에 저장된 PK를 이용해 클러스터링 인덱스에서 찾아가게 된다.

본 포스트에서는 Index를 사용하지 않는 Full Table Scan방법과 Index를 사용하는 4가지 Scan 방법에 대해 다룬다.

Full Table Scan (=Table Full Scan)

말 그대로 테이블 전체 데이터를 순차적으로 scan하는 방식이다(배열에서 모든 데이터를 선형탐색으로 읽는 것에 비유할 수 있다). InnoDB가 데이터(인덱스도 포함)들을 저장하는 기본적인 단위는 "페이지"인데, Full Table Scan은 테이블의 데이터가 담긴 페이지들을 모두 읽어와서 읽게 된다. 레코드가 많은 테이블의 경우, Full Table Scan은 성능 저하의 주범이 되는 경우가 많다. 일반적으로 인덱스가 없거나, 또는 인덱스를 활용할 수 없거나, 아니면 테이블의 레코드가 너무 적어서 인덱스를 사용하지 않고 테이블 전체를 읽는 게 더 빠를 경우 이 방법이 사용된다.

Index Full Scan

인덱스의 Leaf 노드 전체(즉 인덱스의 처음부터 끝까지)를 순차적으로 scan하지만 실제 테이블을 참조하지는 않는 방식을 말한다. 쿼리가 인덱스에 명시된 컬럼들만으로 조건을 처리가능한 경우에 주로 이 방법이 사용된다.(B-tree의 각 노드가 인덱스로 명시된 컬럼값과 child node의 위치를 키-값 형태로 가지고 있음을 상기할 것) 인덱스 뿐만이 아닌 레코드까지 읽어야 한다면 절대 이 방식을 사용하지 않으며, 실제 테이블을 참조하지는 않으니 Full Table Scan보다는 효율적(왜냐면 인덱스의 전체 크기가 테이블 자체의 사이즈보다 작아서 Disk I/O가 줄어들기 때문)이다.

참고로 이렇게 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능할 때 "커버링 인덱스"라고 부른다.

Index Range Scan

특정 범위만큼의 인덱스를 스캔한 뒤 이에 대응하는 레코드들을 읽는 방식이다. 쿼리에서 BETWEEN이나 부등호 등이 사용되어 검색해야 할 인덱스의 범위(range)가 결정됐을 때 사용된다. 스캔의 시작점이 되는 리프 노드만 찾으면 그때부터는 종료 지점까지의 리프 노드들을 쫙 스캔한 후(B+ tree는 리프 노드끼리도 서로 연결돼있음을 상기), 필요하다면 읽은 리프 노드들이 가리키는 레코드들을 읽어온다(따라서 Random I/O가 수반되게 된다). 쿼리가 인덱스에 명시된 컬럼들만으로 조건을 처리가능하다면(즉 커버링 인덱스) Index Full Scan처럼 테이블 레코드들을 읽어오는 과정은 없다. 

Loose Index Scan 

인덱스에서 필요한 부분만 선택적으로 스캔한 뒤 이에 대응하는 레코드들을 읽는 방식이다. Index Range Scan과 비슷하게 동작하나, 필요한 인덱스 키와 불필요한 인덱스 키를 구분한 뒤 불필요한 인덱스 키는 무시하는 식으로 동작한다. 보통 GROUP BY, MAX, MIN 등이 사용된 쿼리를 최적화할 때 사용된다.

Index Skip Scan

인덱스는 두 개 이상의 컬럼에 대해서도 만들어줄 수 있는데(멀티 컬럼 인덱스 등으로 부름), 이 때 컬럼 순서에 따라 인덱스가 어떤 기준으로 정렬되는지가 결정된다.(예를 들어 A, B 순서로 인덱스를 만들었다면 A에 대해 먼저 정렬된 다음 B에 대해 정렬됨) 이때 WHERE절에 멀티 컬럼 인덱스의 첫 컬럼이 없으면 원래 인덱스를 사용할 수 없으나, 첫 컬럼이 없음에도 불구하고 인덱스를 사용할 수 있는 스캔 방식이 이 Index Skip Scan이다. 

예로, student테이블에 대해 다음과 같이 gender, address에 대해 인덱스가 걸려있다고 해보자.

ALTER TABLE students ADD INDEX ix_gender_address (gender, address);

이때다음 쿼리는 원래는 Full Table Scan 또는 Index Full Scan(커버링 인덱스인 경우)으로 수행될 것이다.

SELECT gender, address
FROM students
WHERE address LIKE "경기도%";

하지만 Index Skip Scan을 사용하면 WHERE절에서 쓰이지 않은 인덱스의 선행 컬럼에 대해 가능한 값들을 구한 다음, 해당 컬럼의 조건을 추가해 쿼리를 다시 실행하는 형태로 처리하게 된다. 즉 여기서는 WHERE절에서 쓰이지 않은 gender라는 컬럼에 대해, 해당 컬럼에서 유니크한 값들을 모두 뽑은 다음 다음과 같은 형태의 쿼리를 다시 실행하게 된다.

SELECT gender, address
FROM students
WHERE gender = "M" AND address LIKE "경기도%";

SELECT gender, address
FROM students
WHERE gender = "F" AND address LIKE "경기도%";

참고로, Index Skip Scan은 WHERE절에서 쓰이지 않는 선행컬럼의 유니크한 값의 개수가 적어야 하고(많으면 오히려 쿼리를 처리하는 속도가 더 느려질 수 있기 때문), 커버링 인덱스를 만족해야 한다는 단점이 있다.

쿼리 실행 계획(Query Plan)이란

말 그대로 DBMS가 주어진 SQL 쿼리를 어떻게 처리하겠다고 짜는 계획을 말한다. 같은 쿼리는 같은 결과를 내뱉지만, 내부적으로 그 결과를 만들어내는 방법은 다양하다. 그 결과를 만들기 위한 여러 Plan 중, 가장 최소한의 비용이 드는 Plan을 수립해야 한다. MySQL을 비롯한 대부분의 DBMS에서는 "옵티마이저"가 이러한 역할을 담당한다.

옵티마이저(Optimizer)란

가장 효율적인 방법으로 SQL을 수행할 최적의 경로, 즉 최고의 플랜을 생성해주는 DBMS의 핵심 엔진을 말한다. DB 서버에서의 두뇌 역할을 담당한다고 볼 수 있으며, 다음 두 가지 종류로 나눌 수 있다.

• 규칙 기반 최적화 : 옵티마이저에 내장된 우선 순위를 기준으로 실행 계획을 수립하는 옵티마이저. 같은 쿼리는 같은 실행 계획을 만들게 되고, 우선순위만 잘 활용한다면 내가 원하는 대로 실행 계획이 수립되게끔 유도할 수 있으나 테이블의 레코드 수나 컬럼 값의 분포도 등을 고려하지 않으므로 지금은 잘 안 쓰는 옵티마이저이기도 하다
• 비용 기반 최적화 : 쿼리를 처리 가능한 여러 방법을 만들고, 각 단위 작업의 비용 정보와 통계 정보(테이블 레코드 수, 컬럼 내부 NULL값 분포도, 테이블아 차지하는 블록 개수 등..)를 이용해 실행 계획별 비용을 산출한 후, 가장 최소의 비용이 드는 실행 계획을 선택하는 옵티마이저를 말한다

실행 계획 보는 방법 (MySQL 기준)

가장 대표적인 방법은 "EXPLAIN"을 사용하는 것이다. 다음과 같이 실행 계획을 확인하고 싶은 쿼리의 앞에 EXPLAIN만 붙여주면 된다. 사용된 쿼리는 고객별로 Meatlovers 또는 Vegetarian이라는 이름의 피자 당 주문한 갯수를 조회하는 쿼리이다.

EXPLAIN
SELECT customer_id, pizza_name, count(*) as order_count
FROM customer_orders
INNER JOIN pizza_names ON customer_orders.pizza_id = pizza_names.pizza_id
WHERE pizza_name in ("Meatlovers", "Vegetarian")
GROUP BY customer_id, pizza_name;

"EXPLAIN FORMAT"을 통해 트리(또는 json( 형태로 실행 계획을 볼 수도 있다.

EXPLAIN FORMAT=TREE
SELECT customer_id, pizza_name, count(*) as order_count
FROM customer_orders
INNER JOIN pizza_names ON customer_orders.pizza_id = pizza_names.pizza_id
WHERE pizza_name in ("Meatlovers", "Vegetarian")
GROUP BY customer_id, pizza_name;

"EXPLAIN ANALYZE"를 사용하면 실행 계획 뿐만 아니라 단계별 소요된 시간 정보도 확인할 수 있다. 즉, EXPLAIN은 실행 계획을 만들기만 하고 실제 쿼리 실행은 하지 않는 반면, EXPLAIN ANALYZE는 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여주는 것이다. 참고로 이 커맨드는 항상 트리 형태로 실행 계획을 보여준다.

트리 형태를 기준으로, 실제 실행 순서는 위에서 아래로 다음과 같이 읽으면 된다.

• 들여쓰기(indent)가 같으면, 위쪽에 있는 계획이 먼저 실행된다
• 들여쓰기(indent)가 다르면, 가장 안쪽에 있는 계획이 먼저 실행된다

즉 위 실행 계획은 다음 순서로 실행된다.

1. Table scan on pizza_names
2. Filter: (pizza_names.pizza_name in ('Meatlovers', 'Vegetarian'))
3. Table scan on customer_orders
4. Hash
5. Inner hash join (customer_orders.pizza_id = pizza_names.pizza_id)
6. Aggregate using temporary table
7. Table scan on <temporary>

한국어로 해석해보면,

1. pizza_names 테이블을 쭉 읽으면서
2. pizza_name칼럼값이 'Meatlovers' 또는 'Vegetarian'인 레코드만 가져온다
3. 그 다음엔 customer_orders테이블을 쭉 읽으면서
4. Hash 테이블을 정한 뒤
5. customer_orders와 pizza_names 간 해시 조인을 수행한다
6. 임시 테이블에 결과를 저장하며 GROUP BY 집계를 수행하고
7. 임시 테이블 결과를 읽어서 반환한다

그리고 실행 계획의 각 스텝마다 actual time이 2개씩 적혀있는 걸 확인할 수 있는데, 첫 번째 값은 첫 번째 레코드를 가져오는데 걸린 평균 시간(밀리세컨드)을 의미하고 두 번째 값은 마지막 레코드를 가져오는데 걸린 평균 시간(밀리세컨드)를 의미한다. 또한 rows는 해당 테이블에서 읽은 테이블의 평균 레코드 수를 의미하고, loops는 해당 테이블의 레코드를 찾는 작업이 반복된 횟수를 의미한다. (이 loop 때문에 레코드를 가져오는 "평균" 시간, 테이블의 "평균" 레코드 수라고 하는 것이다)

다음과 같은 테이블이 있다고 하자.

이때 rowstore는 다음과 같이 데이터를 행 단위로 저장하는 것을 말한다.

insert, update, delete를 수행할 경우 row단위로 묶어서 빠르게 처리를 해줄 수 있어서 효율적이다. 반면 모든 사람의 평균 나이처럼 특정 컬럼에 대한 집계가 필요한 경우는 필요없는 컬럼에 대한 read도 하게 되므로 비효율적이다.

즉, rowstore는 OLTP에 좀 더 적합하다.

colomnstore는 다음과 같이 열 단위로 저장하는 것을 말한다.

특정 컬럼(들)에 대한 집계가 필요할 시 필요한 컬럼들이 담긴 디스크 블록들만 읽을 수 있으므로(즉 disk I/O가 감소) 효율적이다. 또한 각 블록들이 가지고 있는 값들은 같은 타입이기 때문에 큰 압축률을 기대할 수 있다는 장점도 있다. 반면 insert, update, delete 수행시 적절한 위치를 찾기 위해 모든 데이터를 순회하므로 비효율적이다. 예를 들어, insert를 할 경우 새로운 레코드의 각 컬럼 값들이 삽입될 적절한 위치를 찾는 과정이 필요하다.

즉, columnstore는 OLAP에 좀 더 적합하다.