MariaDB 서브쿼리(subquery)를 활용해서 데이터를 빠르게 조회하기

MariaDB의 서브쿼리(SubQuery)를 활용해서 데이터를 빠르게 조회해 보자

 

 

나는 개발자가 되기를 결심하고 나서 처음 접한 데이터베이스는 Microsoft SQL Server였다.

대학교 때 공부할 당시에는 성능 위주의 쿼리를 작성하기보다는 경험 및 기능을 이해하기 위해 쿼리를 작성했었다.

 

하지만, 개발자로 첫 취업 후에는 데이터베이스의 쿼리 하나가 정말 중요하다고 느꼈다.

쿼리 하나 때문에 시스템이 응답하지 않고,  최악에는 시스템 다운이 되기도 했었던 경험을 느꼈기 때문이다.

 

그중 선배들로부터 SQL Server에서는 서브쿼리(Sub Query)는 가급적 사용하지 말라고 하였다.

실제로 서브쿼리보다는 조인 구문을 통해서 대부분 대체 가능했고, 쿼리 성능도 훨씬 좋았다.

 

그리고 나서 다음 회사에서는 주로 MariaDB를 이용하였다.

당연히 SQL Server를 이용하듯이 쿼리를 작성하였다. 

이상하게 SQL Server일 때는 별 문제없던 쿼리가 MariaDB에서는 더 빠른 수행 시간을 가지는 현상도 있었고, 훨씬 더 오래 지연되는 현상도 있었다.

그때 알게 된 것은, DBMS의 종류 및 버전마다 옵티마이저의 성능이 달랐고, DBMS마다의 특징이 있었다.

 

개인적으로는 서브쿼리를 좋아하진 않았지만, 이번에 MariaDB의 쿼리를 많이 작성하게 되면서 서브쿼리가 뭔지, 서브쿼리를 이용해서 데이터를 빠르게 조회하는 방법에 대해서 공유해보고자 한다.

 

서브쿼리(subquery)란?

SQL 문 안에 포함된 또 다른 SQL 문을 의미한다.

마치 함수처럼 사용되어 주 쿼리의 결과를 제한하거나 특정 값을 계산하는 데 활용된다.

즉, 하나의 쿼리 안에 여러 개의 쿼리가 중첩되어 실행되는 구조다.

 

서브쿼리의 활용

조건절(WHERE)에서 사용

• 특정 값이 다른 테이블의 값과 일치하는지 확인
• 집계 함수의 결과와 비교
• 서브쿼리의 결과를 IN, EXISTS, ANY, ALL 연산자와 함께 사용

FROM 절에서 사용

• 서브쿼리의 결과를 가상 테이블처럼 사용
• 복잡한 조인을 대체

SELECT 절에서 사용

• 다른 칼럼의 값을 기반으로 새로운 값 계산
• 집계 함수의 결과를 표시

 

서브쿼리의 사용 예시

테이블 생성

사용 예시를 보기 전에 간략히 테이블 정보를 설계하도록 하겠다.

 

간단하게 회원 테이블과 부서 테이블이 있다.

회원 데이터에는 부서 테이블의 PK 인 부서일련번호가 들어가는 1:1 매핑 구조이다.

CREATE TABLE `user` (
	`ID`	    INT	NOT NULL	COMMENT '회원일련번호' AUTO_INCREMENT,
	`USER_NM`	VARCHAR(20)	NULL	COMMENT '이름',
	`AGE`	    TINYINT	NULL	COMMENT '나이',
	`EMAIL`	    VARCHAR(200)	NULL	COMMENT '이메일',
	`SEX`	    VARCHAR(1)	NULL	COMMENT '성별',
	`DEPT_ID`	INT	NOT NULL	COMMENT '부서일련번호',
	`REG_ID`	VARCHAR(100)	NOT NULL	COMMENT '등록자',
	`REG_DATE`	DATETIME	NOT NULL	COMMENT '등록일',
	`STATUS`	TINYINT	NOT NULL	COMMENT '상태',
	PRIMARY KEY (ID)
);

CREATE TABLE `dept` (
	`ID`	    INT	NOT NULL	COMMENT '부서일련번호' AUTO_INCREMENT,
	`DEPT_NM`	VARCHAR(20)	NULL	COMMENT '이름',
	`REG_ID`	VARCHAR(100)	NOT NULL	COMMENT '등록자',
	`REG_DATE`	DATETIME	NOT NULL	COMMENT '등록일',
	`STATUS`	TINYINT	NOT NULL	COMMENT '상태',
	PRIMARY KEY (ID)
);

 

조건절(WHERE)에서 사용

#평균 나이보다 크거나 같은 회원
SELECT * FROM user a
WHERE AGE >= (SELECT AVG(AGE) FROM user);

#인사팀이라는 명칭의 부서에 속한 회원
SELECT * FROM user
WHERE DEPT_ID IN (SELECT ID FROM dept WHERE DEPT_NM = '인사팀');

 

조인절(JOIN)에서 사용

#일반 조인
SELECT a.* FROM user a
INNER JOIN dept b ON b.DEPT_ID = a.DEPT_ID
WHERE b.DEPT_NM = '인사팀'

#조인절에서 서브쿼리 사용
SELECT a.* FROM user a
INNER JOIN
(
    SELECT ID, DEPT_NM FROM dept
    WHERE DEPT_NM = '인사팀'
) b ON b.DEPT_ID = a.DEPT_ID

 

SELECT절에서 사용

SELECT 
    a.*
    (SELECT DEPT_NM FROM dept b WHERE b.ID = a.DEPT_ID) AS DEPT_NM
FROM user a

 

서브쿼리의 주의사항

성능

• 서브쿼리는 조인절마다 성능이 떨어질 수 있다.
• 서브쿼리 사용 시 인덱스를 적절히 활용해야 한다.

예전엔 상식적으로 생각해서 100개의 로우를 반환되는 쿼리가 있을 때, SELECT절에 서브쿼리를 사용한다면 로우 수만큼 서브쿼리가 실행되지 않을까? 생각하면서 당연히 성능이 안 좋다고 생각했다.

 

하지만, PK와 인덱스를 활용한다면 전혀 그렇지 않다. 

SELECT절에서 서브쿼리 사용 시 인덱스를 활용해서 색인한다면 빠르게 색인이 가능하다.

 

예를 들어 주문마스터(order), 주문상세(order_detail) 테이블이 있다고 생각해 보자.

요구사항은 주문마스터별, 주문상세 데이터 건수를 조회하는 쿼리를 작성해야 했을 때 1) 조인절에서 서브쿼리를 사용했을 때 2) SELECT절에서 서브쿼리를 사용했을 때가 있을 것이다.

 

1) 조인절에서 서브쿼리를 사용했을 때 

SELECT
    a.ID, IFNULL(b.DETAIL_CNT, 0) AS DETAIL_CNT  
FROM order a
LEFT OUTER JOIN
(
    SELECT ORDER_ID, COUNT(*) AS DETAIL_CNT FROM order_detail
    GROUP BY ORDER_ID
) b ON b.ORDER_ID = a.ID

 

위와 같은 형태의 데이터를 뽑을 때, 대부분 조인절에서 서브쿼리를 사용해서 쿼리를 많이 작성했었다.

하지만, 점점 데이터가 축적이 되어 늘어났을 때 쿼리 성능이 좋진 않았다.

 

2) SELECT절에서 서브쿼리를 사용했을 때

SELECT
    a.ID,
    (SELECT COUNT(*) FROM order_detail b WHERE b.ORDER_ID = a.ID) AS DETAILC_NT
FROM order a

 

1) 위 쿼리를 2) 형태로 변경해 보자! 데이터양이 많아져도 빠르게 색인이 되었다.

물론, 두 방법 다 order_detail 테이블에 ORDER_ID가 인덱스로 설정되어 있을 때를 가정하였다.

 

별칭

• 서브쿼리마다 테이블 별칭을 모두 다르게 설정해야 한다.

SELECT
    a.ID,
    a.USER_NM,
    (SELECT DEPT_NM FROM dept b WHERE b.ID = a.DEPT_ID) AS DEPT_NM,
    (SELECT b.ID FROM dept b WHERE b.ID = a.DEPT_ID) AS DEPT_ID
FROM user a

 

SELECT절에서 서브쿼리에 대해 같은 별칭을 사용했기에 실행계획을 봤을 때 구분하기 힘들다.

 

서브쿼리에서 동일한 별칭 b를 각각 다르게 설정하면 아래와 같이 구분하기 편하다.

 

기타

• SELECT절에서 서브쿼리를 사용 시 반환되는 칼럼과 로우는 반드시 1건씩이어야 한다. 집계함수나 LIMIT 1 건만 나오도록 한다.
• 서브쿼리는 아무래도 가독성이 좋지 않기에, 적절한 들어쓰기 및 주석을 이용해서 가독성이 좋게 해야 유지보수가 편할 수 있다.

 

결론 및 느낀 점

이전엔 서브쿼리는 무조건 안 좋다고 생각했지만, 인덱스를 사용해 서브쿼리를 사용한다면 SELECT절에 써도 좋은 성능을 나타낸다.

그렇다고 무분별하게 많이 사용하진 말자.

또한, 쿼리를 작성 후 반드시 실행계획을 확인해서 데이터베이스 옵티마이저가 쿼리를 어떻게 실행할지 꼭 보면서 쿼리를 짜도록 하자!