RDBMS의 조회과정, 쿼리실행계획 중요성
반갑습니다. RDBMS와 쿼리실행계획의 중요성을 주제로 기술세미나 발표를 한 정지용 입니다.
[1. 개요]
요즘 자바와 ORM을 기반으로 개발을 하게되어 직접적으로 쿼리를 보는 일이 예전보다 현저히 줄어들었다고는 하나 아직도 데이터가 저장되는 저장소는 대부분 데이터베이스입니다.
결국 그 말은 즉슨 아무리 데이터베이스에 의존을 최소화 하더라도 어쩔 수 없이 데이터베이스에 대해 잘 알아야 한다는 이야기이기도 합니다.
이번 시간에는 RDBMS에서 다양한 기능을 많이 지원하지만 가장 많이 쓰이는 조회과정과 이와 연관되어있는 쿼리실행계획의 중요성을 알아보는 시간을 가져보고자 합니다.
[2. RDBMS란?]
진행하기 앞서, 데이터베이스(Database, 이하 DB)란 무엇일까요?
짧고 명료하게 이야기 하자면 집합과 명제를 근간으로 하는 체계적인 데이터 모음입니다.
RDBMS (관계형 데이터베이스 관리 시스템 :: Relational Database Management System)는 이름에서도 유추가 되듯 데이터베이스의 관리 시스템입니다.
이 RDBMS에선 보통 일반적으로 4가지의 명령어를 지원합니다.
DDL(Data Definition Language)은 데이터 정의어로 DB 구조를 정의하거나 변경하는데 쓰입니다.
DML(Data Manipulation Language)은 데이터 조작어로 등록(insert) 조회(select) 수정(update) 삭제(delete)를 수행합니다.
DCL(Data Control Language)은 데이터 제어어로 DB에 접근하는 사용자의 권한관리를 담당합니다.
TCL(Transaction Control Language)은 트랜잭션 제어어로 작업수행의 대한 승인(commit), 철회(rollback)을 담당합니다.
이 중 우리가 알아볼 명령어는 DML에 속하는 SELECT이며 DBA가 아닌 이상 우리같은 개발자는 보통 DML과 TCL을 세트로 많이 사용합니다.
DML의 가장 큰 특징은 DDL과 다르게 수행을 한 뒤 데이터가 즉시 DB에 반영되지 않으므로 TCL을 통해 최종 수행여부를 결정 해야합니다.
[3. SELECT의 조회 과정]
DBMS의 명령어는 직관적인 편이며 SELECT의 문법도 이와 마찬가지입니다.
SELECT, 선택한다. 무엇을? 컬럼을.
FROM, 어디로부터? TABLE에서
WHERE, 어떤 기준으로? 조건에 따라
GROUP BY 어떻게 묶어? 컬럼에 맞춰서
HAVING 어떤 기준으로? 조건에 따라
ORDER BY 정렬은? 오름차순 / 내림차순
위의 이미지에서 노란 박스안의 문법들은 옵션을 의미합니다.
옆의 예제 SQL문법이 한눈에 읽히신다면 여러분들은 SELECT 쿼리를 쓸 줄 아시게 된겁니다.
문법을 알아보았으니 이제 실행 과정을 살펴볼건데 이해하기 쉽게 뷔페에서 요리 담는 것으로 비유를 해봤습니다.
FROM → 뷔페에 모든 요리들이 잔뜩 있는 상태입니다.
WHERE → 내가 먹고 싶은 음식을 생각해봅시다.
GROUP BY → 에피타이저, 메인, 디저트처럼 종류에 따라 나눠봅니다.
HAVING → 나눈 음식중에 생각해보니 국수랑 튀김은 아니다 싶어 빼기로 했어요.
SELECT → 이제 먹을 음식을 다 골랐고, 접시에 담았습니다.
ORDER BY → 이 음식을 순서대로 먹습니다.
결과로 보면 아래와 같은 이미지처럼 흐르게됩니다.
[4. SELECT의 활용]
SELECT는 활용도가 아주 높은 DML이고, 서브쿼리(sub-query)라는 특별한 기능을 제공합니다.
서브쿼리란 쿼리 안에 또 쓰이는 다른 쿼리를 뜻합니다.
DML에선 포괄적으로 쓰일 수 있고, DDL과의 혼용도 일부 지원을 합니다.
대표적으로 자주 쓰이는 응용 3가지를 예시로 설명드리겠습니다.
첫째로 백업, CREATE 명령어와 조합하면 테이블 백업으로도 쓸 수 있습니다.
노란박스의 조건을 보면 1은1로 할 경우 데이터까지 복사
1은0으로 하면 테이블 스키마만 복사합니다.
둘째로 필터링입니다. 예를 들어 INSERT명령어와 조합하면 조건에 맞는 데이터만 뽑아 넣을 수 있습니다.
셋째로 SELECT안에 SELECT를 써서 임시 테이블, 즉 버추얼 뷰처럼 쓸 수 있는데 이것을 인라인 뷰라고 합니다.
위 이미지에 보이는 쿼리에서 첫번째는 조인을 이용한 쿼리 실행이고, 두번째는 인라인 뷰를 이용한 쿼리 실행 입니다.
둘이 같은 결과를 보여주는 쿼리인데 어느 쪽이 더 빠르게 될까요? 쿼리를 다루게 된다면 항상 고민하게되는 포인트입니다.
지금껏 이야기 한 SELECT 과정을 실제 RDBMS에서 수행되는 과정을 풀어보면 아래와 같은 이미지처럼 나오게 됩니다.
DBMS에 관심이 있어 자세히 알아가고자 한다면 아래의 내용에 대해서도 공부해봄직합니다. 🫡
[5. 쿼리실행계획]
우리의 입장에서 이것보다 더 먼저 알아야 할 것은 SELECT 쿼리가 실행 될 때 발생하는 실행계획(query-plan) 이란 것이 있습니다.
이 실행계획은 말 그대로 쿼리가 실행 할 때 어떠한 계획을 가지고 수행하는가를 나타내는 계획서입니다. 이 실행계획의 각 단계를 COST, 즉 비용이라고 부르는데 말그대로 수행에 필요한 비용을 뜻합니다.
우리같은 서민은 가성비를 좋아하듯, 쿼리의 성능도 가성비가 중요합니다. 🙃 보통 다른 환경이 동일하다는 가정하에 코스트가 낮을수록 결과를 빠르게 가져옵니다.
같은 결과를 뽑더라도 이 실행 계획에 따라 성능이 좌지우지되므로 얼마나 중요한지 알려드리고자 아주 극단적이지만 심플한 예시를 통해 말씀드리겠습니다.
[6. 실행계획을 직접 확인해보자]
자 우리가 취업을 하게되서 회사에서 ‘통계 쿼리를 만들어라’ 업무 지시를 받았다고 시나리오를 두고 위와 같은 구조를 가진 회원 정보 테이블이 있다 가정합시다.
이 데이터를 토대로 전체 회원 수, 월 가입자 수, 6개월 이상 미접속자 수 3개를 가져오고자 합니다.
쿼리를 써본지 얼마 안된 초보자의 입장에선 방금전 문법을 알아가는 과정을 통해 SELECT를 가지고 세가지의 값을 가져와야 한다는 것은 인지 할 것입니다.
그런데 각각 성격이 다른 값을 어떻게 하나로 묶어야할지 고민이 될겁니다.
일단 각각의 결과를 뽑는 쿼리를 써보자면 보시는 그림과 같이 나올 것입니다.
이제 이것을 하나의 결과로 보여주기위해 FROM절엔 테이블이 들어가야 한다는 문법을 생각해서 다음과 같이 작성했다고 가정해봅시다.
FROM절에 아까 작성한 각각의 결과를 가져오는 쿼리를 넣어주고, SELECT절에서 값을 가져오게 했습니다. 문법상 아주 정확하고, 올바른 결과를 나타냅니다.
앞으로 이와 같은 두번의 과정이 더 있는데 결과는 모두 동일함을 미리 말씀드립니다.
작성한 쿼리 앞에 실행 계획을 확인하는 EXPLAIN을 넣고 다시 쿼리를 실행해보면 다음과 같이 실행 계획이 6개나 잡혀있는 것을 확인 할 수 있습니다.
FROM절에 나온 서브쿼리 SELECT가 3번 사용되어 같은 테이블에 3번 요청을 하고, SELECT절에서도 3번의 결과를 가져온 꼴이 되어 결과적으로 3 더하기 3 = 6이 나왔습니다.
회사에서 쿼리를 이렇게 짜고 보고를 하면 어떻게 될까요? 아이고 고생많았으니 얼른 퇴근하세요 ~ 라고 할까요? 안타깝게도 칼퇴는 커녕 야근으로 직행입니다.🫠
자, 야근을 피하기 위해 이번엔 SELECT절 처음부터 각각의 값을 가져오도록 바꿔보았습니다.
위의 코드에서 MYSQL은 문법상 보이진 않지만 FROM절이 있다고 가정을 해서 보는 것이 맞습니다.
참고로 오라클에선 문법 가독성을 위해 FROM DUAL이라는 것을 지원합니다.
FROM절을 가상의 테이블로 취급하여 1개, 서브쿼리로 뽑아오는 부분 3개로 아까보다 무려 2개나 줄었으니 이번엔 칼퇴각일 것 같죠??
여러분들께서 이 쿼리를 보고 만족한다면 안타깝게도 또 하루 야근의 길로 당첨입니다.
그럼 여기서 또 어떻게 줄여야할까요?
DBMS에서 지원하는 SUM 함수와 자바의 IF와 비슷한 CASE, WHEN, THEN 문법을 이용해서 단 한번으로 데이터를 가져오도록 바꿔보았습니다.
와우 코스트가 단 하나로 바뀌었네요. 이정도면 고생했단 칭찬과 함께 칼퇴 할 만 합니다.
이렇게해서 같은 결과를 보여도 쿼리를 어떻게 짜느냐에 따라 효율이 올라가는지, 그 효율을 보는 실행계획은 어떻게 보는지 아주 기초적인 내용으로 알려드렸습니다.