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

쿼리 실행 계획(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 때문에 레코드를 가져오는 "평균" 시간, 테이블의 "평균" 레코드 수라고 하는 것이다)

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상에 있던 테이블과 내용물들이 잘 들어가있는 걸 볼 수 있었다!