Ray`s Tech Lab

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

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

배경

다음과 같은 관계를 갖는 테이블이 있다고 하자.

member는 회원들의 회원번호와 가입일을,

sales는 회원별 물품을 주문한 정보를,

menu는 각 상품 정보를 가지는 테이블이다.

이때, 현재 members, sales, menu에 다음과 같이 데이터들이 적재돼있다고 하자.

1. members

2. sales

3. menu

주어진 문제

각 고객들이 구입한 첫 물건은?

sales 테이블에는 각 회원들이 특정 물품을 구입한 날에 대한 데이터들이 적재돼있으니, 이 테이블에서 적절한 쿼리를 활용해볼 수 있을 것이다. 이 때 처음 물품을 구매한 날짜에 여러 물품을 구매했다면, 해당 물품들의 정보를 모두 출력해야 한다.

풀이1 : GROUP BY를 활용한 접근

sales테이블에 있는 구매한 물품들에 대한 상세정보는 sales와 menu의 product_id들을 INNER JOIN을 걸어 알아낼 수 있다. 그러나 회원별로 가장 처음 구매한 물품들에 대한 정보만 필요하다. sales에서 customer_id로 GROUP BY를 걸고, 그 안에서 MIN함수를 통해 가장 낮은 order_date를 뽑아내면 다음과 같이 회원별로 물품을 처음 구매한 날짜를 추출할 수 있다.

SELECT customer_id, min(order_date)
FROM sales
GROUP BY customer_id;

위 결과를 WHERE절에서 활용, sales테이블에서 다음과 같이 회원별로 해당 날짜에 대한 데이터들을 추출 가능하다.

SELECT *
FROM sales
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

그러나 위 결과에서 4, 5번 레코드를 보면 중복되는 레코드들임을 알 수 있다. C는 2021년 1월 1일에 3번 물품을 2번 구매했기 때문에 이런 결과가 생기는 것이다. 따라서 다음과 같이 DISTINCT를 걸어줘서 중복을 제거할 수 있다.

✅ DISTINCT

: SQL에서 중복된 값을 제거하여 결과 집합에서 고유한 값을 반환하는 데 사용되는 키워드. DISTINCT 뒤에 2개 이상의 컬럼을 사용하면, 해당 컬럼들로 구성되는 조합들에 대한 중복을 제거하는 식으로 동작한다.

(즉, 아래 예제에서는 customer_id, order_date, product_id로 이뤄지는 조합에서 중복이 없어진다)

SELECT DISTINCT customer_id, order_date, product_id
FROM sales
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

이제 menu와 INNER JOIN을 걸어 다음과 같이 처음 구매한 물품의 상세정보를 뽑아낼 수 있다.

SELECT DISTINCT customer_id, order_date, sales.product_id, product_name, price
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id 
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

쿼리를 좀만 다듬으면(사실 SELECT절에서 필요한 정보만 뽑도록 하면), 다음과 같이 회원 아이디와 상품명만 추출 가능하다.

SELECT DISTINCT customer_id, product_name
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id 
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

풀이 2 : PARTITION BY를 이용한 접근

그러나 이 문제는 PARTITION BY를 이용해서도 풀 수 있다.

✅ PARTITION BY?

: 특정 그룹 내에서의 집계 또는 순위를 매길 때 사용하는 함수.

(집계함수 : MIN, MAX, SUM 등을 말함)

(순위함수 : ROW_NUMBER, RANK 등을 말함)

🧐 GROUP BY와의 차이점?

: GROUP BY, PARTITION BY 모두 특정 컬럼을 기준으로 레코드들을 그룹핑한다는 공통점이 있다. 그러나 GROUP BY는 그룹핑되는 과정에서 레코드들의 상세 정보들이 없어지는 반면(레코드들이 집약되기 때문), PARTITION BY는 그룹핑돼도 레코드들의 상세 정보가 남아있게 된다.

sales테이블에 대해 회원별로 구매 횟수를 알고 싶다고 할 때, GROUP BY와 PARTITION BY에 대한 다음 결과를 비교해보면 더욱 잘 이해할 수 있다.

1) GROUP BY로 그룹핑해서 계산하는 경우

SELECT customer_id, count(*) as purchased_count
FROM sales
GROUP BY customer_id;

sales 테이블이 갖고 있던 레코드들이 나오는게 아니라, 집계 결과만 나오는 모습을 확인 가능하다. 만약 order_date나 product_id를 SELECT에서 찍어서 보려고 하면, 1055 ERROR가 난다 (GROUP BY가 사용된 경우, GROUP BY로 그룹핑할 때 사용된 컬럼만 SELECT에서 명시가 가능하기 때문)

2) PARTITION BY로 그룹핑해서 계산하는 경우

SELECT customer_id, count(*) OVER (PARTITION BY customer_id) as purchased_count
FROM sales;

sales테이블이 갖고 있는 레코드들이 살아있음을 확인할 수 있다. SELECT절에서도 order_date, product_id컬럼값을 뽑아낼 수 있으며 에러가 나지 않는다.

SELECT customer_id, order_date, product_id, count(*) OVER (PARTITION BY customer_id) as purchased_count
FROM sales;

RANK순위 함수와 ORDER BY를 사용하면 그룹핑된 섹션 내에서 order_date를 기준으로 다음과 같이 순위를 매길 수 있다.

SELECT
    customer_id,
    order_date,
    product_name,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_date) as order_date_rank
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id;

그룹핑된 각 섹션 내에서 order_date값이 같으면 같은 rank가 매겨진다. RANK와는 달리 DENSE_RANK를 사용하면 동일한 등수가 발생해도 다음번 등수는 이전 등수 + 1이 된다.

위 결과를 WHERE절에서 활용, order_date_rank가 1인 레코드만 추출하면 원하는 결과를 가져올 수 있다. 

WITH ranked_date as (
    SELECT
        customer_id,
        order_date,
        product_name,
        RANK() OVER (PARTITION BY customer_id ORDER BY order_date) as order_date_rank
    FROM sales
    INNER JOIN menu ON sales.product_id = menu.product_id
)

SELECT DISTINCT customer_id, product_name
FROM ranked_date
WHERE order_date_rank = 1;

더 적합한 방식은?

위에서 소개한 GROUP BY와 PARTITION BY 모두 같은 결과를 뱉는다. 그러면 둘 중 어느 것이 좀 더 "적합"할까? 어느 것이 더 효율적으로 동작하는가? 지금은 sales테이블에 데이터가 끽해야 15개 남짓이지만, x억개 있는 상황이라면 뭐가 더 나을까?

쿼리 실행 계획을 살펴보며 어떤 것이 더 나을지 살펴보자.

문제 출처 : 8 week SQL Challenge

https://8weeksqlchallenge.com/

mysqldump?

쉽게 말해 mysql db의 상태를 덤프를 떠서 그대로 백업뜨는 걸 말한다.

sql파일이 만들어지며, dump를 뜬 시점에서 db가 가지고 있던 테이블들과 내용들에 대한 create, insert문들이 들어가있다.

이를 활용해 로컬에서 다루던 db를 그래도 배포 환경에 옮기는 작업, 배포 환경에 있던 db를 로컬로 옮기는 작업 등을 수행할 수 있다.

본 포스트에선 AWS RDS에 있는 mysql 데이터를 덤프떠서 로컬로 가져와본다.

AWS RDS에 넣어뒀던 data들을 dump를 떠서 로컬로 가져오려고 한다.

DB 관리를 위해 띄워뒀던 Bastion에 접속해 다음과 같은 커맨드를 날리면 된다

mysql -h {RDS 엔드포인트} -u {유저명} -p {dump할 DB명} > {경로와 생성할 sql파일의 이름}.sql

이때 permission denied 에러가 날 수 있는데, 이는 생성할 sql파일의 경로를 ~와 같이 자신에게 권한이 부여된 경로를 작성하면 된다. 내 경우 다음과 같이 커맨드를 날렸다.

mysqldump -h somsatangdb.c7jz8i0idkiy.ap-northeast-2.rds.amazonaws.com -u swtAdmin -p wooyoungsoo > ~/petdori.sql

그 다음 로컬에서 scp 커맨드를 통해 EC2에 위치한 dump파일을 로컬로 가져온다

scp -i {pem파일 경로} "{EC2 사용자명}@{EC2 ip}:{다운할 dump파일의 경로와 이름}" {다운받을 파일의 로컬에서의 경로 및 이름}

별도 설정을 하지 않았다면, EC2 사용자명은 ubuntu는 ubuntu, amazon linux는 ec2-user다.

또한, 로컬에서 커맨드를 날리는 것이므로 EC2에 적절한 보안그룹을 설정해줘야 한다는 사실을 잊으면 안 된다.

내 경우 다음과 같이 커맨드를 날렸다

scp -i ./sst.pem "ubuntu@13.124.138.97:~/petdori.sql" ./petdori.sql

다음과 같이 dump파일이 잘 다운받아진 걸 확인 가능하다.

다음과 같이 rds에서 가지고 있던 테이블과 내용물들에 대한 쿼리가 작성돼있는 걸 볼 수 있다.

그 다음, 나는 로컬에서 도커 컨테이너로 mysql을 사용했던만큼, 컨테이너 내부로 dump파일을 옮겨야 했다. 다음 형태로 커맨드를 입력하면 된다.

docker cp {로컬에서의 dump경로 및 이름} {컨테이너명}:{dump파일을 둘 컨테이너 내 경로}

내 경우 다음과 같이 입력했다.

docker cp ./Downloads/petdori.sql mysqlDB:/

이제 docker exec을 가미해 다음과 같은 형태의 커맨드를 날리면 된다.

mysql -u {사용자명} -p {DB명} < {덤프파일}

이렇게 하면 지정한 DB에 덤프파일에 있던 쿼리들이 수행된다. 내 경우 다음과 같이 커맨드를 날렸다.

mysql -u root -p wooyoungsoo < petdori.sql

다음과 같이 RDS상에 있던 테이블과 내용물들이 잘 들어가있는 걸 볼 수 있었다!