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번 상품에 대한 집계와 더불어 전체 집계 또한 나온 것을 확인할 수 있다. 

Persistence와 Persistence Framework

영속성은 프로그램이 종료되도 데이터가 보존되는 것을 말한다. 자바에서는 JDBC(Java Database Connectivity)라 불리는 기술을 통해 메모리에 있는 객체들에게 Persistence를 줄 수 있다(즉 데이터로 저장할 수 있다는 말).

자바 앱에서 JDBC API를 호출하고, 내부적으로 JDBC driver manager를 이용해 DB와 상호작용하는 것을 도식화한 그림이다. 참고로 MySQL, SingleStore 등 저마다의 DBMS들은 자신들에 맞는 JDBC driver를 제공한다.

그러나, 이렇게 JDBC만을 사용하는 것은 매번 JDBC로 DB와 커넥션을 맺고, 쿼리를 날려 수행하고, 결과를 받은 다음 커넥션을 끊는 작업을 프로그래밍해야 한다는 문제가 있었다. 이런 과정을 단순화하여 간단하게 DB와 상호작용하기 위해, 내부적으로 JDBC API를 활용하게끔 하여 개발자의 수고를 덜어주는 Persistence Framework가 등장했으며 대표적으로 SQL Mapper와 ORM이 있다.

SQL Mapper

개발자가 직접 작성한 SQL 쿼리의 실행 결과를 객체로 바인딩해주는 기술. 대표적으로는 MyBatis가 있다.

SQL에 의존성이 높은 방법으로, MyBatis의 경우 xml파일을 통해 SQL 쿼리를 별도 관리하며 동적 쿼리 작성(실행 중 사용자 입력을 통해 들어오는 파라미터 값에 따라 다른 쿼리가 실행되게 하는 것)을 통해 복잡한 쿼리도 처리할 수 있다는 장점이 있다. DBA같은 밥먹고 DB만 파오신 전문가 분들께 복잡한 쿼리 작성을 짬때리는게 가능해진다(?)는 장점도 있다. 그러나 어찌됐건 개발자가 직접 SQL을 작성해야 한다는 것에서 결국은 공수가 드는 것이며, 데이터 모델 패러다임 불일치(자바는 클래스와 객체를 통해 데이터를 모델링하나 DB는 테이블과 컬럼을 통해 데이터를 모델링한다는 것에서 오는 차이)가 발생한다는 단점이 있다. 즉, 객체지향적인 관점에서의 프로그래밍이 어렵다.

ORM (Object Relation Mapping)

객체와 DB의 데이터를 자동으로 매핑해주는 기술. 대표적으로는 JPA가 있다.

설정된 관계를 기반으로 자동으로 SQL 쿼리가 생성되며, DBMS에 의존적이지 않아 개발자가 비즈니스 로직에 좀 더 집중할 수 있는 효과를 줄 수 있다. 또한 DB 데이터를 객체로 매핑해주기 때문에 좀 더 객체지향적인 관점에서의 프로그래밍이 용이하다. 자체적으로 복잡한 쿼리의 자동생성은 어렵기 때문에 OLAP성 업무보다는 OLTP성 업무가 많이 쓰일 때 JPA를 활용하면 좋다.

참고로 JPA는 자바 진영에서 제공하는 ORM을 위한 표준 기술로 그 자체는 "인터페이스"다. 즉, JPA를 사용하려면 해당 인터페이스를 구현한 구현체를 별도로 사용해야 하며, 대표적으로 Hibernate가 가장 유명하다.

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

도커 컴포즈(Docker-Compose)란

쉽게 말하면, "여러 컨테이너를 하나의 서비스로 묶어주는 관리 환경을 제공해주는 도구"다.

도커는 웹 서버 - WAS - DB 등과 같은 3 tier service부터 MSA 등 분산된 컴포넌트를 실행 가능한 환경을 가지고 있으며, 각 컴포넌트들이 자신만의 컨테이너에서 실행되면서 도커가 표준 네트워크 프로토콜을 통해 이들을 엮어낸다. 도커 컴포즈를 통해 이렇게 여러 컨테이너를 통해 돌아가는 애플리케이션을 정의하고 관리할 수 있게 된다.

한 마디로 도커 컴포즈가 없다면 애플리케이션을 구성하는 컨테이너를 직접 하나하나 docker run을 때려가며 구동시켜야 했지만, 도커 컴포즈라는 툴 통해 컨테이너들을 하나로 묶어서 관리할 수 있다는 것.

앞서 말했듯, 도커 컴포즈는 도커에서 기본적으로 제공하는 "기능"이 아닌 여러 컨테이너를 엮고 관리하게 해주는 "툴"이기 때문에 별도의 설치가 필요하다. 도커 컴포즈는 docker-compose.yml파일을 작성한 후, docker-compose up 명령어를 통해 실행 가능하다.

docker-compose.yml

되새김질 차원에서 기존에 docker run으로 컨테이너를 돌리던 커맨드를 봐보면 다음과 같다.

docker run --name mongodb -v ~/data:/data/db -d -p 27017:27017 mongo

• --name mongodb : mongodb라는 컨테이너명으로 돌리겠다
• -v ~/data:/data/db : 호스트의 ~/data를 실행될 컨테이너 내부의 /data/db로 마운트하겠다
• -d : 컨테이너를 백그라운드에서 실행하겠다(detached mode)
• -p 27017:27017 : 호스트의 27017 포트를 컨테이너의 27017포트로 포워딩하겠다
• mongo : mongo라는 docker image를 사용하겠다

이를 docker-compose.yml파일로 옮겨 적으면 다음과 같이 적을 수 있다

version: '3'

services:
  mongodb:
    image: mongo
    volumes:
      - ~/data:/data/db
    ports:
      - "27017:27017"
    restart: always

본 예제에서는 컨테이너를 하나만 적어주긴 했으나, 보다시피 도커 컴포즈 파일은 애플리케이션의 모든 컴포넌트가 실행되고 있을 때 어떤 상태여야 하는지를 기술하는 파일이다. docker run으로 컨테이너를 돌릴 때 사용하던 옵션들을 한 곳에 모아둔 단순한 형식이며, version에 도커 컴포즈 파일 형식의 버전을 작성하고, services 아래로 엮어줄 컨테이너들의 정보를 작성하는 형태이다(위 예시에선 몽고디비 컨테이너만 기재했으나, 당연히 다른 컨테이너들도 쭈루룩 쓸 수 있는 것이다)

재밌는 점은, 도커 컴포즈는 이렇게 단순히 컴포넌트들을 동시 실행하도록 해주는 것 뿐만 아니라 컨테이너 간의 의존관계도 정해줄 수 있다는 것이다. 다음 docker-compose.yml파일을 보자.

version: '3'

services:
  mongodb:
    image: mongo
    volumes:
      - ~/data:/data/db
    ports:
      - "27017:27017"
    restart: always
    
  ray:
    image: ray/ray-ray-image
    ports:
      - "8010:80"
    depends_on:
      - mongodb

docker-compose는 컨테이너를 구동할 때 docker-compose.yml에 작성된 순서대로 컨테이너를 돌리지 않고, 병렬로 즉 동시에 실행시키려고 한다. 이 때 위 예시처럼 depends_on을 활용하여 컨테이너 간 의존성을 명시해 컨테이너가 구동되는 순서를 맞춰줄 수 있다. ray컴포넌트는 mondodb컨테이너가 구동된 뒤 실행되게 된다.