Ray`s Tech Lab

쿼리 실행 계획(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에 좀 더 적합하다.

둘 다 DB에서 데이터를 삭제하는 방식에 관한 것이다. Hard delete는 물리 삭제라고도 부르고 Soft delete는 논리 삭제라고도 부르는데, 이름에서 유추할 수 있듯이 Hard delete는 물리적으로 데이터 자체를 DB에서 없애는 것이고 Soft delete는 논리적인 범주에서만 없애는 것이다. 즉 Soft delete는 데이터 자체가 물리적으론 DB에 남아있다

논리적으로 삭제한다는 것 = Soft하게 삭제한다는 것이 어떤 의미일까? 삭제한 것처럼 여기겠다 라는 것이다. 테이블에 삭제여부를 나타내는 컬럼을 추가해 삭제여부가 표기돼있으면 삭제됐다고 보고, 삭제여부가 표기돼있지 않으면 삭제가 안 된 데이터로 본다는 것,

그럼 이 Soft delete라는 것을 적용할 때 얻을 수 있는 장점은 뭘까?

당연히 뒷배가 있다는게 최고의 장점이다. 삭제처리된 데이터들을 따로 보고 싶을 때, 논리적으론 삭제됐지만 물리적으로는 데이터가 남아있는 셈이니 삭제된 데이터들을 따로 볼 때도 유용할 것이고, 실수로 인한 삭제가 발생했을 시 복원도 가능하다. 또한 물리적 삭제의 경우 delete라는 SQL을 쓰고 논리적 삭제는 update라는 SQL을 쓰는데, update가 delete에 비해 좀 더 빠른 속도를 낸다고도 한다.

하지만 Soft delete는 단점도 있다. 우선 물리적으로 데이터를 하드디스크에서 덜어내는 게 아니기 때문에 시간이 지날수록 물리적으로 쌓여있는 데이터들이 많아지며 DB 용량 자체가 커진다. 또 다른 단점은 where절 등으로 데이터들을 뽑아낼 때 삭제여부에 대한 처리도 반드시 함께 해줘야 하는 것. 이는 쿼리문을 직관적으로 이해하는 것에도 불편을 줄 가능성이 존재한다

따라서 이런 장단점들을 비교해보면서, 서비스 운영에 맞게 Soft delete를 사용할지 Hard delete를 사용할지 잘 고려해야 할 필요가 있을 것이다.