SQL을 잘한다는 기준은 무엇일까

(주)엔코아 최정현 수석 컨설턴트

SI 프로젝트에서 항상 시스템 오픈일이 가까워져서 많은 난리통을 겪게 된다. 어플리케이션 기능 개발이 제대로 이루어지지 못해서인 경우가 많지만 이와 더불어 결과 데이터의 품질과 시스템 성능 문제를 항상 겪게 된다.

(프로젝트가 힘들게 진행될 수 밖에 없는 근본적인 원인에는 갑-을-병-정… 으로 내려가는, 항상 개발자가 최약자일 수 밖에 없는 SI 산업의 계약구조 속에서 명확하지 않은 개발 범위, 마지막까지도 줄어들지 않는 고객의 변경 요구사항 등등... 이러한 안타까운 이 업계의 현실을 우리는 이미 너무 잘 알고 있으므로 여기서는 이러한 얘기는 배제하기로 하자)

아무리 좋은 서버를 도입하고 시스템 설계를 잘 하더라도 결국 User가 느끼는 시스템의 품질은 개발된 어플리케이션 화면 상에서의 데이터의 품질과 시스템 성능이 된다.이와 관련하여 중요한 요소를 꼽으라 한다면 SQL 개발 능력과 인덱스라 할 수 있으며 결국 개발자들의 역할이 매우 중요할 수 밖에 없다.

그런데 매 프로젝트마다 느끼는 것이지만, 특정 어플리케이션 Language 에는 전문가이나 SQL 개발에 익숙치 못한 개발자들이 많다.이는 10년 이상의 경험을 가지고 있는 고급 개발자들도 예외가 아니다.

“어떻게 하면 SQL을 잘 할 수 있을까요” 이런 질문을 개발자들로부터 종종 듣곤 한다.그러고보니 대학에서나 신입사원 때나 SQL교육을 받긴 했었던 것 같지만 단지 SELECT, FROM, WHERE 절 등의 구문을 어떻게 사용하는지에 대한 내용이었지, 어떻게 해야 좋은 SQL을 잘 작성할 수 있는지에 대한 교육을 받아본 적이 없는 것 같다.

SQL을 잘 한다는 기준은 무엇일까

새로운 Analytic Function 을 많이 알고 있는 것
수백~수천 라인의 SQL 을 작성하는 것
수행 성능이 좋은 SQL을 작성하는 것

필자는 SQL을 잘 한다는 것의 기준을 이렇게 얘기하고 싶다.

“고객의 요구사항에 맞는 결과 데이터 집합을 명확하게 추출 또는 처리하도록 SQL을 작성할 줄 아는 것이 SQL을 잘 하는 것이다.”결국 원하는 결과 데이터를 정확하게 처리하도록 SQL 을 작성하는 것이 중요하다. SQL 성능은 둘째 문제다.정확한 데이터를 처리하도록 SQL 을 작성하는 것은 기본중의 기본이 아니냐고 얘기하겠지만 실제 프로젝트에서 개발자들이 작성한 수많은 SQL 을 접해 보면 결과 데이터 집합이 모호한 SQL 들을 자주 만나게 된다.

한 예로 아래와 같은 유형의 SQL 을 보자.

SELECTDISTINCTA.COL1, A.COL2, A.COL3, B.COL4, B.COL5, B.COL6, C.COL7, D.COL8, ……
FROMTABLE_AA,
TABLE_BB,
TABLE_CC,
TABLE_DD
WHERE……

SQL 튜닝을 하다 보면 자주 접하게 되는 SQL 유형이다. SELECT 절에 필요한 컬럼을 모두 나열해 놓고 앞에 DISTINCT 를 사용한 것은 아래와 같은 이유들이 있을 것이다.

■1 : M 관계의 테이블들을 조인한 후 결과 데이터 집합을 다시 1 집합으로 줄여야 하는데 GROUP BY 를 사용하지 않고 DISTINCT 를 사용한 경우
■SQL 내 조인 컬럼 중 일부가 누락되는 등의 여러 이유로 인해 M : N 조인이 발생됨으로써 데이터의 중복이 발생하여 이를 제거하기 위해서 DISTINCT 를 사용한 경우

결과 데이터 집합에 중복이 발생한 원인을 찾지 않고 이렇게 DISTINCT 를 사용하여 중복을 제거하는 것은 결과 데이터 집합의 Key 레벨이 정확히 무엇인지 (예를 들어, 고객번호 단위로 Unique 한 집합인지, 계약번호단위로 Unique 한 집합인지 등) 파악하기 어려우며 더 큰 문제는 결과 데이터의 왜곡이 발생할 수 있다는 점이다.

SQL 내 SELECT 절에 나열된 컬럼 중에 본질식별자 또는 실질식별자에 해당하는 컬럼들이 모두 포함되어 있지 않은 경우 DISTINCT 로 인해 서로 다른 데이터가 한건으로 줄어드는 데이터 오류가 발생될 수 있다. 이런 SQL 들로 인해 데이터 왜곡이 발생된다면 그 원인 파악 또한 매우 어려워진다.

그럼 어떻게 하면 결과 데이터의 집합이 명확하도록 SQL 을 잘 작성할 수 있을까 이를 위해 다음과 같은 방법을 제안해보고자 한다.

SQL 을 한번에 작성하여 수행 결과를 검증하려 하지 말고 테이블을 하나씩 조인할 때마다 결과 데이터 집합의 Key 레벨이 유지되는지 확인하고 결과 데이터 건수의 변화를 확인하라.

좀 더 상세하게 얘기하면,

■SQL 작성 전 먼저 원하는 결과 데이터 집합의 Key 레벨을 명확히 결정한다. 결과 데이터 집합을 하나의 테이블로 생성한다고 가정할 때 Primary Key 컬럼이 무엇이 되어야 하는지 명확하게 결정한다.
■입력되는 조건이 많은 주요 테이블부터 (여러 테이블에 상수조건이 존재한다면 1 : M 관계의 테이블들 중 M 집합의 테이블부터) FROM 절에 포함하고 최종 결과와 동일한 Key 레벨의 집합을 먼저 만든다.
■M 집합의 테이블로부터 1 집합의 테이블을 하나씩 조인하는 방향으로 SQL을 작성하되 상수조건이 존재하는 테이블을 먼저 FROM 절에 포함한다.
■FROM 절에 조인 테이블을 하나씩 추가할 때마다 조인 결과 데이터 집합의 Key 레벨이 변동되지 않는지 확인한다. 확인 방법은 조인 결과 건수를 Count 하여 건수의 변화를 검토하고, 필요하면 조인할 때마다 조인 결과 집합의 Key 컬럼을 기준으로 Group by ~ Having Count(*) > 1 를 수행하여 중복 데이터의 발생이 없는지 확인한다.M 집합을 기준으로 1 집합을 조인하므로 조인 결과 집합의 건수는 동일하거나 줄어들어야 한다.
■결과 데이터 집합의 추출을 먼저 명확하게 한 다음 최종적으로 SELECT 절에 포함될 컬럼을 추가한다.
요지는 SQL 을 작성할 때, 테이블을 하나씩 조인할 때마다 결과 건수를 Count 해보고 건수의 변화를 확인하면서원하는 결과 데이터의 집합 레벨이 변경되지 않는지 확인해 가며 SQL 을 작성한다는 것이다. 이해를 돕기 위해 아래 간단한 데이터모델을 예로 보자.

“고객” 과 “주문” 테이블을 조인하면 그 결과 데이터 집합의 Key 레벨은 “주문” 테이블과 같다. 여기에 다시 “주문내역” 을 조인에 추가하면 그 결과 데이터 집합의 Key 레벨은 “주문내역” 과 같아진다. 그러나 “상품” 테이블을 더 조인하더라도 결과 데이터 집합의 Key 레벨은 변동 없이 “주문내역” 과 같다.

이렇게 1 : M 관계의 테이블 간 조인의 결과는 M 집합이 되고, 1 집합은 조인이 되어도 결과 집합의 Key 레벨이 변경되지 않는다는 것은 너무나 상식적인 내용이다. 만약 여기에 다시 “고객연락처” 를 조인 한다면 이는 “주문내역” 과 “고객연락처” 의 M x N 집합이 발생된다. 그러나 이러한 M : N 조인의 결과를 보고자 하는 SQL 은 OLTP성 업무시스템에서 볼 수 있는 흔한 유형이 아니며 1 : M 조인으로 처리되는 것이 일반적이다.

그럼 위의 데이터모델에서 SQL 작성 절차를 예를 들어보자.

■업무요건 .
2015년도에 주문된 주문 목록 중에 상품코드 A 를 주문한 목록과 고객명을 추출하라■1단계.최종 결과 데이터 집합의 Key 레벨을 결정한다.
위의 업무요건에 대한 최종 결과 데이터 집합은 “주문번호 별로 Unique 한 집합” 이 되어야 한다.■2단계.“주문” 테이블을 시작으로 SQL 을 작성한다.SELECTCOUNT(*)
FROM주문A
WHERREA.주문일자 BETWEEN ‘20150101’ AND ‘20151231’주요 조건이 “주문” 과 “주문내역” 테이블에 분산되어 있으므로 어느 테이블을 기준으로
먼저 시작해도 상관은 없으나 최종 결과 집합이 “주문” 과 동일한
Key 레벨이므로 “주문” 테이블을 기준으로 시작한다.
그리고 결과 건수를 먼저 COUNT 해 놓는다.■3단계.“주문내역” 테이블을 조인한다.SELECTCOUNT(*)
FROM(SELECTA.주문번호
FROM주문A
,주문내역B
WHERREA.주문일자 BETWEEN ‘20150101’ AND ‘20151231’
ANDA.주문번호 = B.주문번호
ANDB.상품코드 = ‘A’
GROUP BYA.주문번호
)A
“주문” 과 “주문내역” 은 1 : M 이므로 “주문내역” 테이블을 FROM 절에서
직접 조인하면 M 집합이 되므로 이를 다시 최종 결과 집합과 동일한
“주문” 레벨로 변경하려면 조인 후 주문번호를 기준으로 GROUP BY 를 해야 한다.조인한 “주문내역” 테이블에서 SELECT 해야 할 컬럼이 없다면
아래와 같이 서브쿼리를 이용하여 조인을 할 수도 있다.SELECTCOUNT(*)
FROM주문A
WHERREA.주문일자 BETWEEN ‘20150101’ AND ‘20151231’
ANDEXISTS(SELECT‘X’
FROM주문내역B
ANDA.주문번호 = B.주문번호
ANDB.상품코드 = ‘A’
)FROM 절에 “주문내역” 을 직접 조인하면 결과 집합의 Key 레벨이 변하므로
GROUP BY 작업이 필요했지만 이렇게 서브쿼리를 이용하면 GROUP BY 가 필요 없어진다.
서브쿼리는 항상 1 집합을 보장하기 때문에 서브쿼리 형태로 조인을 하게 되면
메인 집합의 Key 레벨은 변동이 없기 때문이다.
서브쿼리에 EXISTS 가 아닌 IN 을 이용해도 무방하다.다시 결과 건수를 COUNT 해보면 조인한 “주문내역” 에 상품코드 조건이
존재하므로 1 단계에서의 결과 건수와 동일하거나 줄어들어야만 한다.■4단계.“고객” 테이블을 조인에 추가한다.SELECTCOUNT(*)
FROM주문A
,고객C
WHERREA.주문일자 BETWEEN ‘20150101’ AND ‘20151231’
ANDA.고객번호 = C.고객번호
ANDEXISTS(SELECT‘X’
FROM주문내역B
ANDA.주문번호 = B.주문번호
ANDB.상품코드 = ‘A’
)1 집합인 “고객” 테이블을 조인하였으므로 조인 결과 집합의 Key 레벨은 변경이 없으며
“고객” 테이블에 상수조건이 없으므로 조인한 결과 건수도 변함이 없어야 한다.
만약 조인 후에 결과 건수가 줄어들었다면 이는 고객번호가 없는 주문 데이터가
존재하는 것으로 데이터의 정합성이 깨진 것이다.
만약 이러한 오류 데이터를 보정할 수 없는 상황이고 이러한 주문 데이터도
추출이 되어야 한다면 데이터모델과는 맞지 않지만 부득이 INNER JOIN 이
아닌 OUTER JOIN 으로 변경을 고려해야 할 것이다.조금 더 나아가서 만약, 위에서 추출한 주문 목록에 해당 고객의 연락처를
같이 추출하고 싶다면, 이는 “주문” 과 “고객연락처” 간의 M x N 의 집합이
생성되므로, 최종 결과 데이터 집합의 Key 레벨을 유지한 채로 고객 연락처도
보여주고 싶다면 “고객연락처” 집합을 조인 연결고리인 고객번호 컬럼을
기준으로 GROUP BY 를 해서 1 집합으로 만들어 조인을 해야 한다.
다시 말하면 특정 고객의 연락처가 여러건이 존재하더라도
그 중 1건만 취하여 조인을 해야 하는 것이다.
이때 어떤 연락처를 1건 취할 것인지는 업무적으로 다시 판단을 해야 할 것이다.SELECTCOUNT(*)
FROM주문A
,고객C
,(SELECT고객번호
,MAX(전화번호)전화번호
FROM고객연락처
GROUP BY고객번호
)D
WHERREA.주문일자 BETWEEN ‘20150101’ AND ‘20151231’
ANDA.고객번호 = C.고객번호
ANDC.고객번호 = D.고객번호 (+)
ANDEXISTS(SELECT‘X’
FROM주문내역B
WHEREA.주문번호 = B.주문번호
ANDB.상품코드 = ‘A’
)여기서 주의할 점은 데이터모델 상에서 “고객” 과 “고객연락처” 간의 관계를 보면,
고객연락처가 없는 고객이 존재할 수 있다는 점이다.이 때문에 “고객연락처” 집합을 조인할 때
INNER JOIN 을 한다면 고객연락처가 없어 결과 집합에서 누락되는 데이터가 발생할 수 있다는 점이다.
따라서 결과 데이터 집합의 변동이 없게 하기 위해서는 OUTER JOIN 을 해야 한다.
가능한 데이터모델을 보면서 관계를 정확히 파악해 가며 SQL을 작성하는 것이 좋다.
■5단계.SELECT 절에 필요한 컬럼을 나열한다
최종 결과 데이터 집합을 추출하기 위한 SQL 구조가 완성되고 나면 SELECT 절에 필요한 컬럼을 나열한다.

1 집합 쪽에서 M 집합 쪽으로 (고객 -주문 - 주문내역) 순서대로 SQL을 작성할 수 있지만 조인할 때마다 조인 결과 집합의 COUNT 가 증가되므로 SQL 작성 과정에서 조인 컬럼의 누락이나 데이터 오류로 누락되는 데이터 등을 판단하기가 어려우므로 가능한 M 집합 쪽에서 1 집합쪽으로 조인을 하면서 중간 집합의 Key 레벨과 데이터의 건수 검증을 하는 것이 더 수월하다.

물론 위에 언급한 절차가 수많은 복잡 다양한 SQL 을 작성하는데 모두 적합할 수는 없다.“고객번호 A에 대한 모든 주문 내역을 추출하라” 와 같이 주요 조건이 “고객” 테이블에 존재하여 1 집합에서 M 집합 쪽으로 SQL 을 작성하는 것이 더 논리적인 경우도 있을 것이다.SQL 을 한번에 작성하든, 1 집합에서 M 집합의 순으로 작성하든간에 결국 얘기하고자 싶은 한마디는 SQL 의 결과 데이터 집합의 Key 레벨이 명확하도록 SQL 을 작성하라는 것이다.

일단 SQL 작성 시에 성능을 고려하지는 말자.SQL 튜닝에 익숙한 사람들은 SQL을 작성 시에 성능까지 고려해가며 작성하겠지만 각 테이블의 인덱스 현황, 통계정보 등 다양한 원인으로 실행계획이 변경되어 SQL 성능은 변할 수 있으므로 해당 업무의 요구사항에 맞는 정확한 결과 데이터 집합을 추출하는데 최선을 다하자.

수백~수천 라인의 SQL 작성하고서 자랑스러워 하는 개발자를 보면 먼저 그 SQL 의 결과 집합이 정확하다는 것을 보장할 수 있을까 하는 생각이 든다.SQL 내의 수많은 테이블들이 조인되고 GROUP BY 되고 DISTINCT 처리가 되면서 그때마다 결과 데이터 집합의 Key 레벨이 어떻게 변경되고 있는지를 명확히 설명하지 못한다면 그 SQL 의 최종 결

SQL 작성 시에 DISTINCT 는 아예 사용하지 않는 것으로 습관을 들이자. DISTINCT 가 아닌 GROUP BY 를 사용하여 결과 집합이 정확히 어떤 컬럼을 기준으로 Unique 한 집합이라는 것을 명시적으로 알 수 있도록 하자.또한, GROUP BY 를 사용할 때에도 GROUP BY 절에는 가능한 결과 데이터 집합의 Primary Key 에 해당하는 컬럼만 포함하고 나머지는 SELECT 절에서 MIN/MAX 등을 취하는 습관을 들이자.더 이상 온갖 테이블들을 모두 조인해 놓고 정확한 결과 데이터 집합의 Key 레벨이 무엇인지 모른채 DISTINCT 처리를 하는 무책임한() SQL 을 작성하지 않기를 기대해본다.