2022년 7월 1주차 SQL 코딩의 기술

Chapter 1. 데이터 모델 설계

• 데이터 모델을 잘못 설계하면 '효율적'인 SQL 작성은 시작도 할 수 없다.
• BETTER WAY 1.모든 테이블에 기본키가 있는지 확인하자
  • 기본키가 없으면 데이터를 걸러 낼 때 일치하는 로우가 없거나 딱 한 개인 조건은 보장할 수 없다.
  • 기본키가 없는 테이블 간의 관계를 모델링하는 것은 일반적으로 불가능하다.
  • 테이블에 기본키가 없으면 반복적이고 일관성 없는 데이트가 쌓여 쿼리 수행 속도가 느리고, 부정확한 정보를 조회하는 등 모든 종류의 문제를 일으킬 수 있다.
  • <기본키의 좋은 후보 자격> 
    • 유일한 값을 가져야 한다.
    • NULL 값을 가질 수 없다
    • 값을 갱신할 필요가 없어야 한다.
    • 가능한 한 간단한 형태여야 한다(문자나 부동소수점보다는 정수형, 여러 컬럼보다는 단일 컬럼이 낫다)
  • 참조 무결성을 준수한다는 것은 NULL이 아닌 외래키가 설정된 자식 테이블의 각 레코드와 일치하는 레코드가 부모테이블에 존재한다는 것을 의미한다.
  • 테이블 간에 참조 무결성을 유지하려면 변경된 기본키 값을 이와 관련 있는 테이블과 모든 자식 레코드에 전파해야 한다.
  • 갱신이 전파되면  관련 테이블에 Lock(잠금)이 설정되어 높은 동시성을 지원하는 다중 사용자 DB에 심각한 문제를 일으킬 수 있다.
  • 텍스트 기반 컬럼을 기본키로 사용하는 이유는 중복 값 입력을 막을 수 있기 때문이라는 의견이 지배적이다.
  • 텍스트 기반 컬럼에 유일 인덱스를 추가하는 것으로 효율적으로 사용 가능하다.
  • 자동 생성된 숫자 값을 기본키로 사용할 때 참조 테이블과 조인할 필요가 없으므로 SQL문이 좀 더 간략하다.
  • 핵심은 기본키로 사용하는 컬럼은 반드시 유일한 값을 가져야 한다는 점
  • 복합 기본키를 사용하지 않는 2가지 이유
    • 1. 기본키를 정의할 때 대부분의 DB 시스템은 해당 컬럼에 유일 인덱스를 같이 만든다. 컬럼 두 개 이상에 유일 인덱스를 만들면 DB 시스템에 할 일만 더 많아진다.
    • 2. 일반적으로 기본키로 조인을 수행하는데, 기본키가 여러 컬럼으로 구성되어 있으면 쿼리가 좀 더 복잡하고 느려진다.
  • 물론 특정 상황에서는 여러 컬럼을 기본키로 사용하는 것이 마땅하다. ex) ProductID와 VendorID의 조합
• BETTER WAY 2. 중복으로 저장된 데이터 항목을 제거하자
  
  • 데이터를 중복으로 저장하면 일관되지 않은 데이터, 비정상적인 삽입/갱신/삭제 처리, 디스크 공간 낭비 등 많은 문제를 일으킨다.
  • 정규화는 중복 데이터를 저장하면서 일으키는 문제점을 없애려고 정보를 주제별로 분할하는 프로세스를 의미한다.
  • 사용자가 동일한 데이터를 한 군데 이상에서 입력하는 상황을 중복이라고 일컫는다.
  • 데이터베이스를 다루는 사람이라면 정규화를 철저히 이해해야 한다
  • 정규화의 한 가지 목표는 한 데이터베이스에서 동일한 테이블이든 다른 테이블이든 반복되는 데이터를 최소화 하는 것
• BETTER WAY 3. 반복 그룹을 제거하자
  
  • 영향도(비용) 측면에서 컬럼은 비싸고 로우는 싸다.
  • 향후 입력될 유사한 데이터로 컬럼을 추가하거나 제거하도록 테이블을 설계했다면 마음속으로 적색 경보를 울려야 한다.
  • 필요할 때 로우를 추가하거나 제거하도록 설계하는 것이 훨씬 바람직하다.
  • 반복 그룹을 처리할 때는 UNION 쿼리를 사용한다.
  • 이런 저런 이유로 적절하게 정규화를 적용한 설계를 할 수 없다면, UNION을 사용해 읽기 전용 뷰를 만들어 데이터를 정규화 할 수 있다.
  • UNION을 사용하려면 각 SELECT 문에서 사용되는 컬럼의 데이터 타입이 동일해야 하며 나열 순서도 같아야 한다. UNION은 맨 첫 번째 SELECT 문에 명시된 컬럼 이름을 취한다.
  • 데이터에 따라 길이가 0인 문자열이나 스페이스(' ')처럼 출력이 필요 없는 값을 제외해야할 수도 있다.
• BETTER WAY 4. 컬럼당 하나의 특성만 저장하자
  • 올바른 테이블 설계는 개별 특성을 자체 컬럼에 할당한다. 한 컬럼에 여러 특성이 포함되어 있으면 검색이나 그루핑 작업이 가능하다고 해도 어렵기 때문이다.
  • 일부 애플리케이션에서는 주소나 전화번호 같은 컬럼의 데이터 일부를 걸러 내려면 최소 수준의 데이터 조각으로 분할해야 한다.
  • 보고서나 목록을 뽑으려고 특성들을 재결합할 때는 SQL의 문자열 연결 기능을 사용한다. 
• BETTERWAY 5. 왜 계산 데이터를 저장하면 좋지 않은지 이해하자
  • 많은 시스템에서 테이블을 정의할 때 계산 컬럼을 정의할 수 있지만 성능을 고려해야 한다. 특히 비결정적 표현식이나 함수를 사용할 때는 더욱 그렇다.
  • 트리거를 사용해 계산 컬럼을 일반 컬럼처럼 정의할 수 있지만 작성해야 할 코드가 복잡하다.
  • 계산 컬럼은 DB 시스템에 추가적인 부하를 일으키므로 계산 컬럼으로 얻는 혜택이 부하를 일으키는 비용보다 클 때만 사용한다.
  • 대부분의 경우 저장 공간이 증가하고 데이터 갱신이 느린 대신 일부 혜택을 보려고 계산 컬럼에 인덱스를 만들고 싶을 것이다.
  • 인덱스 적용이 어려울 때는 테이블에 계산 결과를 저장해 놓는 방법 대신 뷰을 이용해 수행할 계산을 정의하는 방법을 종종 사용한다.
• BETTER WAY 6. 참조 무결성을 보호하려면 외래키를 정의하자
  
  • 명시적으로 외래키를 만들면 부모 테이블에 없는 로우를 가리키는 자식 테이블 로우가 없음을 보장할 수 있으므로 관련된 테이블 간에 데이터 무결성을 확인하는데 좋다.
  • 이미 데이터가 있는 테이블에 FOREIGN KEY 제약 조건을 추가할 때 이 제약 조건을 위반하는 데이터가 있다면 제약 조건을 생성하는 작업은 실패할 것이다.
  • 일부 시스템에서는 FOREIGN KEY 제약 조건을 정의하면 자동으로 인덱스를 만들어 주므로 조인 성능이 향상될 수 있다. 다른 시스템은 FOREIGN KEY 제약 조건이 걸린 컬럼에서 수동으로 인덱스를 만들어야 한다. 일부 시스템은 인덱스 없이도 옵티마이저가 해당 컬럼을 특별 취급해 더 나은 쿼리 실행 계획을 세우기도 한다.
• BETTER WAY 7. 테이블 간 관계를 명확히 하자
  • 컬럼이 비슷한 테이블을 서로 병합해 관계를 간소화하는 것이 정말 타당한지 면밀히 검토한다.
  • 데이터 타입이 일치한다면(또는 묵시적 타입 변환이 가능하다면) 두 테이블에 있는 컬럼 간 조인을 생성할 수 있는데, 이 관계는 해당 컬럼이 같은 도메인에 있을 때만 성립한다.
  • 데이터 모델에서 실제로 정형 데이터를 다루고 있는지 확인한다.
  • 다루는 데이터가 반정형이라면 필요한 대응책을 마련한다.
  • 일반적으로 데이터 모델의 목표를 명확히 식별하면, 주어진 설계의 간소화와 데이터 모델을 사용하는 애플리케이션의 설계에 기인한 복잡성이나 이상 동작을 정당화하는지 판단하는 데 도움이 된다.
• BETTER WAY 8. 제3정규화로도 부족하다면 더 정규화하자
  • 대부분의 데이터 모델에는 이미 더 높은 정규화가 적용되어 있을 가능성이 높다. 따라서 더 높은 정규화 형식을 명확히 위반하는지 면밀히 관찰해야 한다. 특히 복합키를 사용하거나 여러 다대다 관계에 참여하는 테이블일 때는 더욱 그렇다.
  • 한 엔터티에서 관계가 없는 두 속성으로 가능한 모든 조합을 해당 엔터티에 열거해야 하는 특수한 경우에는 제4정규화를 위반할 수 있다. 
  • 제5정규화는 후보키가 모든 조인 의존성을 함축하는지 확인하는 것이다. 즉, 개별 요소에 근거해 후보키에 유효한 값이 무엇인지 제약할 수 있어야 한다는 말이다. 이것은 키가 복합키일 때만 발생한다.
  • 제6정규화는 일반적으로 관계를 키가 아닌 속성 하나로만 줄이는 것이다. 따라서 테이블 개수가 급격히 늘어나지만 널 허용 컬럼을 정의할 필요가 없다.
  • 무손실 분할 테스트는 해당 테이블이 더 높은 정규화 형식을 위반하는지 감지하는 효과적인 도구가 될 수 있다.
• BETTER WAY 9. 데이터 웨어하우스에는 역정규화를 사용하자
  • 중복으로 저장할 데이터와 그 이유를 정한다.
  • 데이터를 일치된 상태로 유지할 계획을 세운다.
  • 역정규화된 필드를 사용하도록 쿼리를 리팩토링한다.

 

 

<추천 문헌>

- 가장 쉬운 데이터베이스 설계책 : 적절한 데이터베이스 디자인을 위한 지침서 <비제이 퍼블릭, 2014>

- 마이클 헤르난데즈 - <Database Design for Mere Mortals, Third Edition>

- Handbook of Relational Database Design(Addison-Wesley,1989)

 

<참고 사이트>

- 식별자의 정의와 종류 : https://dog-foot-story.tistory.com/60

[SQL] 식별자의 정의와 종류(후보키, 기본키, 대체키, 복합키, 대리키)

- 정규화의 종류와 정의 :

https://dataonair.or.kr/db-tech-reference/d-lounge/expert-column/?mod=document&uid=52803 

아는 만큼 보이는 데이터베이스, 정규화의 실무적용