[SQL AntiPattern] 2부 물리적 데이터베이스 설계 안티패턴

September 15, 2023 - 6 minute read -
book sql anti pattern database sql

SQL 을 효과적으로 활용하기 위해 SQL AntiPattern 의 내용을 정리한다.
2부에서는 데이터를 효율적으로 관리하는 방법에 대해 알아본다.


10장 반올림 오류: 정수 대신 소수 사용

정수가 아닌 수를 저장하고 수를 저장해야 하는데,
산술 연산도 수행하고 그 결과가 정확하도록 관리하고자 한다.

안티패턴: FLOAT 데이터 타입 사용

  • 소수 데이터가 필요한 경우, FLOAT 데이터 타입을 사용
    • IEEE 754 표준에 따라 실수를 이진 형식으로 부호화
  • 필요에 의한 반올림
    • 십진수로 표현된 모든 수를 이진수로 표현할 수 없음
    • 부동 소수점에 대해 정확하게 저장되지 않고 오차가 발생될 수 있음
  • SQL 에서 FLOAT 사용
    • 특정 데이터베이스에서는 부정확한 값을 보정해 스스로 의도한 값으로 표시됨
    • 특정 값으로 검색을 해도 조회가 안될 수 있음
    • 합계나 곱을 하는 경우 부동 소수점 수의 누적으로 더 큰 오차가 발생될 수 있음

사용이 합당한 경우

  • INTEGER 나 NUMERIC 타입이 지원하는 것보다 큰 범위의 실수 값을 사용하는 경우

해법: NUMERIC 또는 DECIMAL 데이터 타입 사용

  • NUMERIC, DECIMAL 두 타입은 동일하게 동작
    • 가능하면 FLOAT 은 사용하지 말자
  • 컬럼 정의에서 지정한 정도까치 수치를 정확하게 표현
  • 스케일 지정 가능
  • 유리수가 반올림되지 않고 저장
  • 산술 연산을 해도 기대한 값으로 표시됨


11장 31가지 맛: 칼럼을 특정 값으로 제한하기

칼럼의 값을 고정된 집합의 값으로 제한하고자 한다.
유효하지 않은 값은 데이터베이스에서 거부하는 것이 이상적이다.

안티패턴: 칼럼 정의에 값 지정

  • 칼럼을 정의할 때 유효한 데이터 값 지정
    • ex) CHECK 제약조건 또는 ENUM 타입 사용
    • MySQL 의 ENUM 은 값을 문자열로 선언하지만, 내부적으로 서수(ordinal number)로 저장
  • 중간에 있는 게 뭐지?
    • 해당 컬럼에서 허용되는 값의 목록을 얻기 어려움
      • MySQL 의 경우, INFORMATION_SCHEMA 시스템 뷰의 COLUMN_TYPE 에서 파싱하여 추출
      • DISTINCT 를 사용해도 현재 데이터에 있는 값만 반환
  • 새로운 맛 추가하기
    • ENUM 이나 CHECK 제약조건에 값을 추가하려면 컬럼 재정의 필요
    • 이전 정의에서 사용 되던 값 조회가 필요할 수 있음
    • ETL(extract, transform, load) 작업이 필요할 수 있음
      • 해당 작업동안 테이블 접근 불가
  • 예전 맛은 절대 없어지지 않는다
    • 특정 값을 더 이상 사용되지 않게 만들면, 과거 데이터가 망가질 수 있음
      • 과거 값을 유지하면, 입력하지 못하도록 설정 불가
  • 포팅이 어렵다
    • 체크 제약조건, 도메인, 사용자 정의 타입은 모든 데이터베이스 제품에서 균일하게 지원되지 않는 기능
      • ex) ENUM 은 MySQL 고유 기능

사용이 합당한 경우

  • 값의 집합이 변하지 않는 경우
    • 집합 조회가 필요하다면 애플리케이션의 코드에서 유지하면 됨
  • 상호 배타적인 두 값 중 하나를 나타내는 경우
    • ex) LEFT/RIGHT, ACTIVE/INACTIVE, ON/OFF, INTERNAL/EXTERNAL

해법: 데이터로 값을 지정하기

  • 각 값을 행으로 하는 색인 테이블 생성
    • 해당 테이블을 FK 제약조건으로 선언
    • 값 제한 가능
  • 값의 집합 쿼리하기
    • 메타데이터로 저장되지 않고 데이터로 저장되기 때문에 SELECT 문으로 조회 가능
  • 색인 테이블의 값 갱신하기
    • INSERT 문으로 값 추가 가능
    • 접근 제한, ETL 작업, 컬럼 재정의 불필요
    • 이전 정의했던 값 조회할 필요가 없음
  • 더 이상 사용하지 않는 값 지원하기
    • 속성 컬럼을 추가하여 사용되지 않는 값 표시 가능
      • ex) ACTIVE/INACTIVE
  • 포팅이 쉽다
    • 표준 SQL 기능(FK 제약조건을 이용한 참조 정합성)만 사용
    • 무한한 개수의 값 저장 가능


12장 유령 파일: 이미지 또는 벌크 미디어 저장

이미지나 다른 미디어에 대해 데이터베이스 엔티티와 연관을 갖도록 저장하고자 한다.

안티패턴: 파일을 사용해야 한다고 가정

파일을 별도로 저장하고 경로만 저장하는 방식은 위험이 있음

  • DELETE 문제
    • 이미지 경로를 포함하는 행을 삭제할 때 자동으로 삭제 하는 방법이 없음
    • 고아 이미지 파일이 누적될 위험
  • 트랜잭션 문제
    • 트랜잭션이 커밋되지 않아도 변경된 내용이 보여질 수 있음
  • ROLLBACK 문제
    • 에러가 발생되어도 지워진 이미지 파일이 복구되지 않음 (업로드된 이미지도 삭제되지 않음)
  • 백업 문제
    • 데이터베이스에는 백업 도구를 제공
    • 외부 이미지 파일에 대해서도 백업하기 위한 도구 필요
      • 백업했어도 제대로 동기화 된 상태인지도 파악이 어려움
  • SQL 접근 권한 문제
    • 외부 파일은 SQL 문으로 접근권한 적용 불가
  • SQL 데이터 타입 문제
    • 저장된 경로는 문자열일 뿐이기 때문에 유효한 검증이 어려움
    • 경로가 존재하는지 검증이 어려움
    • 이동, 삭제에 대해 자동으로 업데이트되지 않음

사용이 합당한 경우

  • 보통 이미지는 크기가 크기 때문에 외부에 저장해야 성능에 영향을 미치지 않음
  • 이미지를 제외해야 데이터베이스 백업 속도가 빨라짐
  • 외부에 저장된 이미지는 미리보기나 편집이 쉬워짐
    • 배치 편집이 필요한 경우 유용

해법: 필요한 경우에는 BLOB 데이터 타입 사용

  • BLOB 칼럼에 저장하면 좋은 이유
    • 이미지가 데이터베이스에 저장
      • 이미지를 로드하기 위한 별도의 단계가 필요 없음
      • 파일 경로가 잘못될 위험 없음
    • 행이 삭제되면 이미지도 자동 삭제
    • 커밋하기 전까지 이미지 변경이 적용되지 않음
    • 롤백되면 이전 상태로 자동 복원
    • 행을 업데이트하면 잠금이 걸리므로 동시 업데이트 방지
    • 데이터베이스 백업에 모든 이미지가 자동 포함
    • SQL 을 통해 이미지에 대한 접근 제어 가능
  • BLOB 의 최대크기는 데이터베이스 종류마다 다름
    • ex) MySQL 의 MEDIUMBLOB 은 16MB
  • 기존에 파일로 존재하던 이미지를 데이터베이스 BLOB 컬럼으로 로드 가능
    • ex) MySQL 의 LOAD_FILE() 함수
  • BLOB 컬럼의 내용을 파일로 저장 가능
    • ex) MySQL 의 SELECT ... INTO DUMPFILE 문


13장 인덱스 샷건: 성능 최적화

데이터베이스 성능을 향상 시키는 좋은 방법은 인덱스를 잘 활용하는 것이다.
인덱스를 언제, 어떻게 사용해야 하는지 알아보고자 한다.

안티패턴: 무계획하게 인덱스 사용하기

  • 인덱스를 잘못 사용하는 예
    • 불충분한 인덱스 또는 정의하지 않음
    • 너무 많은 인덱스 또는 도움 되지 않는 인덱스 정의
    • 인덱스를 사용하지 않는 쿼리 실행
  • 없는 인덱스
    • INSERT, UPDATE, DELETE 사용할 때마다, 인덱스 데이터 구조 업데이트 필요
      • 오버헤드가 발생되지만, 상쇄할 정도의 이득이 있음
    • UPDATE 또는 DELETE 문에도 행을 찾을 때 인덱스가 도움이 될 수 있음
  • 너무 많은 인덱스
    • PK 에 인덱스를 정의할 필요 없음
    • 긴 문자열 타입에 인덱스가 크므로 컬럼 전체 검색이나 정렬할 쿼리가 없다면 필요 없음
    • 복합 인덱스에서는 컬럼 순서가 중요함
      • 검색 조건, 조인 조건, 정렬 순서에 맞춰 왼쪽에서 오른쪽 정렬
    • 많은 인덱스는 오버헤드만 발생시킴
  • 인덱스가 도움이 되지 않을 때
    • 복합 인덱스를 정의했는데 정렬 순서가 다른 경우
    • 인덱스 컬럼이 변형된 경우
      • ex) WHERE MONTH(date) = 4
    • 조회하는 데이터가 예측 불가능하게 흩어져 있는 경우
    • 검색할 문자열이 뒤에 있는 LIKE 검색하는 경우(LIKE '%??')
    • 검색하는 데이터 타입이 다른 경우
    • NOT-EQUAL 비교된 경우
      • ex) <>, NOT IN, NOT BETWEEN

사용이 합당한 경우

  • 경험에 근거해 가능한 최적의 추측하여 인덱스 정의 필요

해법: 인덱스를 MENTOR하라

MENTOR(Measure, Explain, Nominate, Test, Optimize, Rebuild) 사용

  • Measure(측정)
    • 쿼리가 실행된 시간을 로그로 남겨 많은 비용이 들어간 작업을 식별하여 최적화
    • 측정할 때에는 캐싱 기능 비활성화 필요
    • 측정이 끝난 다음에는 오버헤드가 발생될 수 있으니 프로파일링 빈도를 줄이거나 비활성화 필요
  • Explain(실행 계획 확인)
    • 쿼리 실행 계획 확인 (QEP, Query Execution Plan)
  • Nominate(지명)
    • 쿼리에서 인덱스를 사용하지 않고 테이블에 접근하는 부분 확인
      • ex) MySQL Enterprise Query Analyzer
      • 쿼리 실행 계획을 살펴봐도 확인 가능
  • Test(테스트)
    • 인덱스 생성 후 확인
  • Optimize(최적화)
    • 시스템 메모리의 일정량을 캐시에 할당
    • 인덱스를 캐시 메모리에 미리 로딩
      • ex) MySQL 의 LOAD INDEX INTO CACHE 문
  • Rebuild(재구성)
    • 주기적으로 인덱스 재정비
      • 파일 시스템이 단편화되는 것처럼 인덱스도 균형을 잃을 수 있음
      • ex) MySQL 의 ANAALYZE TABLE 또는 OPTIMIZE TABLE

관련 코드는 깃허브 참고

출처

  • SQL AntiPatterns