[유선생] SQL 개발자 (2)

 

SQL을 공부하다 보면 정규화 및 반정규화라는 개념이 나오는데, 이는 상당히 어려운 개념이다. 

데이터베이스만 잡고 살지 않는 이상 한 번의 공부로 익히기엔 양도 꽤 되고 적절한 예시를 찾아도 잘 이해가 되지 않기 때문이다.   이번 챕터는 정규화, 반정규화에 대해서 공부할 것이다.

 

[정규화]

 

* 엔터티 : 사용되는 데이터를 용도별로 그룹화된 식별할 수 있는 객체

 

엔터티를 정확성과 일관성을 유지하기 위해 작은 단위로 분리하는 과정이다. 

 

정규화 시 얻는 이점은 다음과 같은데 조회 성능 향상, 입력, 수정, 삭제 성능의 향상을 기대할 수 있다. 

정규화에는 규칙이 존재한다. 따라서 규칙에 맞는 엔터티를 정규화되었다고 표현할 수 있을 것이다.

 

1) 제1 정규형

 - 모든 속성은 반드시 하나의 값만 가져야 한다. 

 

실제 공부하는 입장에서는 칼럼에 값이 당연히 한 개만 들어가는 알 텐데, 가끔 레거시 시스템의 데이터베이스를 보면 여러 구분자로 여러 값이 들어 있는 경우가 존재하며 사용하는 애플리케이션에서 구분자로 구별해 값을 꺼내는 경우가 존재한다.  (아마 이는 테이블 수를 줄이기 위한 방법으로 어떠한 제약이 있을 것이다.)

 

현재 우리 레거시는 "|"를 사용하여 여러 값을 넣는데, 데이터를 구분하는 과정에서 구분하여 값을 꺼낸 함수를 호출하거나, 입력할 때 "|"를 넣어주는 과정이 생겨 가독성을 떨어뜨린다. 관심사를 분리하여 처리한다 해도 결국 애플리케이션에서 자원을 사용해야 하는 것이다. 

 

 

2) 제2 정규형 

 

* 일반 속성 : 구성 방식에 따른 분류로 PK, FK을 제외한 나머지 속성을 뜻한다.

 

- 엔터티의 모든 일반 속성은 반드시 모든 주식 별자에 종속되어야 한다.

 

쉽게 말해 복합 식별자( 엔터티를 식별하는 식별키가 하나의 칼럼이 아닌 여러 칼럼으로 이루어진 경우)인 경우 일반 속성이 주식 별자의 일부에만 종속될 수 있다. 

 

단일 식별자인 경우에는 발생하지 않는다. 이는 제1 정규형에서 동일 속성을 전부 제거한 후 제2정규화로 넘어왔기 때문에 각 속성이 엔터티에서 필요한 속성이며 단 하나의 값을 가지고 있음을 보장하는 상태이고, 그렇기에 모두 주식별자에 종속된다. (만약 일반 속성끼리 종속된다면 그것은 제3정규화에서 처리해야 한다.)

 

주문내역 테이블을 예시로 들어보자.

주문번호

음료코드

수량

음료명

(주문번호 음료 코드) : 주식별자

 

수량이라는 속성은 주식별자에 전부 속한다. 다만 음료명 속성은 주식별자에 전부 속할까? 

그렇지 않다. 음료명과 음료 코드는 값은 다르지만 엔터티에서 의미적으로 같은 속성이어서 중복된다.  하나의 인스턴스에 같은 내용이 중복된다고 할 수 있으며 제2 정규형을 만족하기 위해선 엔터티를 더 쪼개야 한다.

 

[주문내역 테이블]

주문번호

음료코드

수량

 

[음료 테이블]

음료코드

음료명

 

 

3) 제3 정규형

 

- 주식별자가 아닌 모든 속성 간에는 서로 종속될 수 없다. 

 

이는 일반 속성에서 종속되는 관계를 파악하고 별도의 엔터티를 추출해야 하는 과정이다. 

 

제2 정규형에선 일반 속성이 복합 식별자의 부분에만 종속되어 해당 부분을 추출한 것과는 달리 이는 일반 속성에서 종속관계가 파악된 것이다.

 

[사원 테이블]

사번(PK)

부서코드

부서명

 

부서 코드와 부서명은 하나의 엔터티에서 일반 속성이다. 하지만 부서 코드 == 부서명을 의미하여 부서명이 부서 코드에 종속되는 상황이다. 

조인을 줄이기 위해 반정규화 과정을 진행한 경우가 아니라면 반드시 분리해야 하는 종속성이다. 

 

그 이유는 다음과 같다. 부서 코드에 매핑되는 부서명이 변경되었다. 

그럼 사원 테이블에서 해당 부서코드에 매칭 되는 모든 부서명을 업데이트하면 된다. 

그런데 만약 연도별로 부서명이 바뀌고 과거의 부서명까지 저장해야 하는 경우에는 당신은 어떻게 대응할 것인가?

업데이트하는 순간 과거는 저장할 수 없고 모든 부서명이 현재 연도 부서명으로 업데이트된다.

 

해결방법은 다음과 같이 테이블을 분리하는 것이다.

 

[사원 테이블]

사번(PK)

부서코드

 

[부서 테이블]

연도(PK)

부서코드(PK)

부서명

 

 

 

+ 추가적으로 3.5NF, 4NF, 5NF 정규형이 존재한다.  제3 정규형까지 공부했다면 이후는 이해가 잘될 것이다.  

정규화의 법칙은 이전 단계를 만족해야 다음 단계로 넘어간다는 점이다.

 

정규화의 목적은 데이터 중복성을 줄여 하나의 테이블에는 단 한 번의 데이터만 저장해야 한다는 목적을 가지고 만들어진 디자인 기술입니다.  마치 GOF의 디자인 패턴처럼 이는 데이터를 분할하여 데이터베이스를 설계하거나 성능적으로 처리할 때 사용될 수 있는 하나의 기술로 취급될 수 있습니다.

 

 

 

[반정규화]

 

반정규화는 정규화 과정에서 발생하는 수많은 테이블들을 반정규화를  통해 데이터의 중복을 허용하거나, 데이터를 묶는 과정이다. 이는  "조회의 성능"을 높이기 위해 사용하며, 수정, 삭제, 입력 성능은 저하될 수 있는 여지가 있다.

 

 

 

첫 번째로 테이블 병합 단계를 알아보자.

 

데이터를 조회하는 경우 테이블이 많아지면 JOIN문이 상당히 많아지며 이로 인해 조회 성능에 악영향을 끼칠 수 있다. 

실무에서 처음으로 SQL문을 보았을 때 수많은 조인과 서브 쿼리에 SQL문을 해독하기 위해 애썼던 기억이 난다.

 

다만 테이블 병합에서 고려할 상황은  1:1 관계는 합치면 되지만 1:N 관계에서  1에 해당하는 테이블의 속성이 많다면 중복 데이터가 정말 많아진다.   기존 하나의 테이블에 한 개의 인스턴스가 들어갔지만 1:N 병합을 통해 1*N개의 데이터가 들어가기 때문이다.

 

두 번째는 테이블 분할이다.

 

1) 수직 분할 : 엔터티의 속성의 값이 null이나 자주 사용되는 속성이 아닌 경우 하나의 추상화를 통해 테이블을 분할한다.

테이블을 조회했을 때 자주 사용하는 속성과 덜 사용되는 속성을 반으로 갈라 저장한다고 생각하면 편하다.

 

 

분할 전

 

SELECT * FROM TEMP_TABLE

잘 사용되는 속성1

잘 사용되는 속성1

잘 사용되는 속성1

잘 사용되는 속성1

드물게 사용 속성1

드물게 사용 속성2

분할 후

 

SELECT * FROM TEMP_TABLE_A

잘 사용되는 속성1

잘 사용되는 속성1

잘 사용되는 속성1

잘 사용되는 속성1

SELECT * FROM TEMP_TABLE_B

드물게 사용 속성1

드물게 사용 속성2

 

 

2) 수평 분할 :  인스턴스를 특정 기준(연도, 시간, 일, 월...)으로 분할한다. 예전 N2 샤딩관련 글을 읽었을 때 보았던 개념인 거 같은데, 특정 테이블에 과도하게 데이터가 많이 쌓이면 아무리 좋은 리소스를 갖는다 해도 조회 성능에 문제가 발생한다. 따라서 로우 데이터를 특정 기준으로 분할하여 저장하는 방식을 취하는 것이다.

 

분할 전 

 

[TABLE1]

연도	월	속성
....	...	...
120억개의 로우존재

 

분할 후

 

[TABLE1], [TABLE2], [TABLE3],.... [TABLE12]

연도	월	속성
....	...	...
10억개의 로우 존재

 

"월 속성" 기준으로 테이블을 분할하여 저장하는 방식으로 개선함으로써 120억 개의 데이터를 각각 분리하여 10억 개의 데이터로 분할하여 조회 성능을 높였다.라고 상상해볼 수 있다. ㅋㅋ

 

 

세 번째는 테이블 추가이다.

 

중복을 감안해도 성능상 반드시 필요하다고 판단되는 경우 테이블을 추가한다.

 

실무에서 직원들의 발령 데이터를 관리하는 부분이 존재한다. 이때 발령 테이블에서 이전 발령내역을 조회하는 경우 데이터 로우가 많아져 문제가 발생할 수 있는데, 이때 발령 테이블에는 최근 발령데이터를 넣고

발령 이력 같은 경우에는 별도의 테이블을 설계하여 처리함으로써 성능을 높였다. 

 

사실 가장 어려운 부분인 게 성능 문제가 발생한 경우 주변에 선배 개발자를 보면 1차로 쿼리의 성능 검사를 진행한다. 서브 쿼리, 조인 등을 검사한 후  2차로 인덱스를 검사한다.  사실 DBA가 아니고서야 전문적으로 검사할 수 없지만 각자 판단을 가지고 진행하는 것으로 보인다. 3차로 DB옵션을 고려한다. 

그리고 테이블 추가하는 걸 생각하는 개발자는 아직 본 적이 없다.  앞으로 난 여러 상황을 고려할 수 있게 되었다. 역시 유선 배 SQL 개발자 책이다. 

 

 

* TIP

칼럼의 반정 규화도 존재한다. 파생 칼럼을 추가하는 방법은 상당히 유용할 듯 싶다. 연말정산관련 로직을 SQL로 계산을 돌리는 Stored Procedure가 있는데 데이터베이스 함수를 사용해 계산을 진행하고 조건절에서도, 조인절에서도 사용되고 있는 것을 본적이 있다.  이러한 조건문에서는 함수를 사용하는 것은 인덱스를 쓸모 없게 만드는 방법으로 알고 있지만 함부로 고칠 수 없는 입장이라 난처할 때도 많다. 

하여튼 컬럼을 추가해 계산된 값을 입력할 때 넣으면 조회할 때 성능 개선을 할 수 있을 것 같다.

 

 

 

-----------

 

1 챕터를 마무리했다. 

 

이제 실제로 SQL문을 하나하나 작성하면서 신중히 고민할 것이다. 바쁜 업무시간에 구글링 하면서 하나하나 성능 관련해 검색해서 개발을 진행할 수는 없다. 이번에 주말 혹은 이른 아침에 조금씩 신중히 확장해가면서 하나하나 공부해 나가는 것이 SQL의 기초를 탄탄히 하는 밑거름이 될 것이라고 생각한다. 

 

이 책에서는 여러 문제들이 존재하는데 이러한 문제에 대한 해설을 유튜브로 해설강의를 올려주시니 참고하여 공부하면 좋겠다.

https://www.youtube.com/c/SQL%EC%A0%84%EB%AC%B8%EA%B0%80%EC%A0%95%EB%AF%B8%EB%82%98

SQL전문가 정미나