SQL AntiPatterns 개발자가 알아야할 25가지 SQL 함정과 해법

기억 재생을 위한 간략한 정리이다. 실질적으로 왜 해당 항목이 안티 패턴인지에 대한 구체적 이유, 구체적인 해결책은 무엇인지 등에 대해서는 책을 참조한다.

• 목표 : 관련된 값의 집합을 한 칼럼에 저장한다.
• 안티패턴 : 칼럼을 VARCHAR로 만들고 여러개의 값을 쉼표로 구분하여 넣어서 DB 변경을 최소화 한다.
• 해법 : 다대다 관계로 교차 테이블을 생성하라.
• Tip : 각 값은 자신의 칼럼과 행에 저장하라.

• 목표 : 재귀적 관계를 갖는데이터를 트리나 계층적 구조로 만든다.
• 안티패턴 : parent_id 칼럼을 추가하라.
• 해법 : 다른 트리 모델을 사용하라.(생략)
• Tip : 계층 구조에는 항목과 관계가 있다. 작업에 맞도록 이 둘을 모두 모델링해야 한다.

• 목표 : 목표는 모든 테이블이 PK를 갖도록 하는 것이지만, PK의 본질을 혼동하면 안티패턴을 초래할 수 있다.
• 안티패턴 : 모든 테이블에 id 칼럼 추가.
• 해법 : 상황에 따라 다르다.
  • PK 칼럼의 이름을 “id”로 짓지 말라. bugs 테이블이라면 “bug_id” 형태로 지어라.
  • 가상키(surrogate key, auto increment나 sequence등을 통해 생성되는 키)에 집착할 필요는 없다. 자연키가 적합하면 자연키를 사용하라.
  • 복합키가 적합하면 복합키를 사용하라.
• Tip : 관례는 도움이 될 때만 좋은 것이다.

• 목표 : 데이터베이스 아키텍츠를 단순화하고 싶다.
• 안티패턴 : FK 제약조건 생략.
  • 이 경우 참조 정합성을 보정하는 코드를 직접 작성하는 책임을 져야 한다.
  • FK를 사용하지 않으면 성능이 좋아질것 처럼 말하지만, FK를 사용하지 않으면 성능 문제를 포함해 문제가 해결되기보다는 늘어난다.
• 해법 : 참조 정합성을 강제하는 FK를 사용하라. 데이터 정합성 오류를 찾아내 정정하는 대신, 처음부터 잘못된 데이터가 입력되지 않도록 하라.
• Tip : 제약조건을 사용해 데이터베이스에서 실수를 방지하라.

• 목표 : 가변 속성 지원(보통 엔티티의 상속 구조를 표현할 때 이런 경우가 발생한다)
  • 여러 객체의 계산이나 비교를 간단히 하기 위해 객체를 하나의 데이터베이스 테이블에 행으로 저장하고 싶다. 또한 객체의 각 서브타입이 베이스 타입이나 다른 서브타입에는 적용되지 않는 속성 칼럼을 저장하는 것도 허용해야 한다.
• 안티패턴 : 범용 속성 테이블을 사용한다.
  • 이 말은, 키-값 쌍을 저장하는 테이블을 두는 것을 뜻한다. 값은 어떤 형태가 들어올지 알 수 없으므로 충분한 크기의 문자열로 지정한다.
• 해법 : 서브타입 모델링 기법을 사용한다.
  • 단일 테이블 상속(Single Table Inheritance)
    • 관련된 모든 타입을 하나의 테이블에 저장하고, 각 타입에 있는 모든 속성을 별도의 칼럼으로 가지도록 한다.
    • 속성 하나는 행의 서브타입을 나타내는 용도로 사용해야 한다.
    • 많은 속성이 특정 서브 타입에만 필요하고, 해당 속성이 적용되지 않는 객체를 저장하는 경우 이런 칼럼에는 NULL을 넣어준다.
    • 서브 타입의 개수가 적고, 특정 서브타입에만 속하는 속성의 개수가 적을 때, 그리고 액티브 레코드와 같은 단입 테이블 데이터베이스 접근 패턴을 사용해야 할 때 가장 좋다.
  • 구체 테이블 상속(Concrete Table Inheritance)
    • 서브 타입별로 별도의 테이블을 만든다.
    • 각 테이블에는 베이스 타입에 있는 공통 속성뿐 아니라 특정 서브타입에만 필요한 속성도 포함된다.
    • 새로운 공통 속성이 추가되면 모든 서브 타입 테이블을 변경해야 한다.
    • 모든 서브타입을 한꺼번에 조회할 필요가 거의 없을 경우에 적합.
  • 클래스 테이블 상속(Class Table Inheritance)
    • 서브타입에 공통인 속성을 포함하는 베이스 타입을 위한 테이블을 하나 만든다.
    • 각 서브타입에 대한 테이블을 만든다.
    • 서브타입 테이블의 PK는 베이스타입 테이블에 대한 FK이다.
    • 모든 서브타입에 대한 조회가 많고 공통 칼럼을 참조하는 경우가 많을 때 적합하다.
  • 반구조적 데이터(Semistructured Data)
    • 단일 테이블 상속과 유사하지만 서브타입의 송석을 칼럼으로 저장하지 않는다.
    • 데이터의 속성 이름과 값을 XML또는 JSON 형식으로 부호화해 TEXT혹은 CLOB 칼럼으로 저장.
    • 서브타입의 개수를 제한할 수 없고, 어느 때고 새로운 속성을 정의 할 수 있는 완전한 유연성이 필요할 때 적합하다.
• Tip : 메타데이터를 위해서는 메타데이터를 사용하라.

• 목표 : 여러 부모 참조
  • 댓글 엔티티가 있을 때, 이 댓글 엔티티가 Bugs 테이블과도 일대다 관계를 맺고, FeatureRequests 테이블과도 일대다 관계를 맺고 싶은 경우.
• 안티패턴 : 이중 목적의 FK 사용. 이를 다형성 연관(Polymorphic Associations)라고 부른다. 한 컬럼이 다른 두개 이상의 테이블의 PK값을 저장하게 만든다. 따라서 해당 컬럼에는 FK 속성을 줄 수 없다.
• 안티 패턴이 합당한 경우 : ORM을 사용할 경우 이 안티패턴 사용이 불가피 할 수 있다. 이 경우에는 ORM이 참조 정합성을 체크하므로 그나마 괜찮다.
• 해법 : 관계 단순화
  • 역참조
    • 교차 테이블 생성 : 각 부모에 대해 별도의 교차 테이블을 생성하고 교차 테이블에는 각 부모 테이블에 대한 FK뿐 아니라 댓글에 대한 FK도 포함시킨다.
    • 신호등 설치 : 댓글은 항상 하나의 부모에 의해서만 참조 될 수 있으므로 교차 테이블에서 comment_id에 대해 unique 제약 조건을 건다.
  • 공통 수퍼테이블 생성
    • 모든 부모테이블이 상속할 베이스 테이블을 생성한다.
    • 댓글은 베이스테이블의 ID와 일대다 관계를 맺는다.
    • 각 부모테이블은 베이스 테이블로 부터 PK를 부여 받는다.
• Tip : 모든 테이블 관계에는 참조하는 테이블 하나, 참조되는 테이블 하나가 있다.

• 목표 : 다중 값 속성 저장. 속성이 한 테이블에 들어가야 할 것처럼 보이는데, 여러개의 값을 가진다.
• 안티패턴 : 여러개의 칼럼 생성. 아래 테이블에서 tag가 여러개 발생하는 경우.

  CREATE TABLE Bugs (
    bug_id SERIAL PRIMARY KEY,
    description VARCHAR(1000),
    tag1 VARCHAR(20),
    tag2 VARCHAR(20),
    tag3 VARCHAR(20)
    );

• 해법 : 다중 값 속성을 위한 칼럼을 하나 가지는 종속 테이블 생성. 여러 개의 값을 여러 개의 칼럼 대신 여러 개의 행에 저장하는 것이다.

  CREATE TABLE Tags (
    bug_id BIGINT UNSIGNED NOT NULL,
    tag VARCHAR(20),
    PRIMARY KEY (bug_id,tag),
    FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id)
  );

• Tip : 같은 의미를 가지는 각각의 값은 하나의 테이블에 저장하라.

• 목표 : 확장 적응성 지원. 쿼리 성능을 향상시키고 지속적으로 크기가 늘어나는 테이블을 지원하도록 데이터베이스를 구성하는 것이다.
• 안티패턴 : 값으로 들어가야 할 것을 테이블 이름이나 컬럼 이름으로 만드는 행위
  • 많은 행을 가진 큰 테이블을 여러개의 작은 테이블로 분리한다. 작은 테이블의 이름은 테이블 속성 중 하나의 값을 기준으로 해서 짓는다.(예: Bugs_2009, Bugs_2010, Bugs_2011,..)
  • 하나의 칼럼을 여러 개의 칼럼으로 분리한다. 칼럼 이름은 다른 속성의 값을 기준으로 해서 짓는다.(예: bugs_fiexed_2008 INT, bugs_fixed_2009 INT, …)
• 해법 : 파티션과 정규화
  • 테이블 수평 분할 : 행을 여러 파티션으로 분리하는 규칙과 함께 논리적 테이블을 하나 정의하면 나머지는 데이터베이스가 알아서 해준다.
    • 해시 파티션 : PARTITION BY HASH(YEAR(date_reported)) PARTITIONS 4
    • 시간별로 데이터를 분할하는 경우에는 range 파티션을 사용하는 것이 일반적이다. 이 경우 시간이 지남에 따라 새로운 파티션을 추가해야 한다.
  • 테이블 수직 분할 : 칼럼으로 테이블을 나눈다. BLOG, TEXT 칼럼 같은 것을 분리한다.
  • 칼럼의 경우에는 종속 테이블을 만들어서 해당 칼럼 값을 따로 저장한다.
• Tip : 데이터가 메타데이터를 낳도록 하지 말라.

• 목표 : 정수가 아닌 수를 저장하고 이를 산술 연산에 사용한다. 산술 연산의 결과가 정확해야 한다.
• 안티패턴 : FLOAT,REAL,DOUBLE PRECISION 데이터 타입 사용
  • 반올림 오류가 발생한다.
• 해법 : NUMERIC이나 DECIMAL 타입을 사용하라.

  ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9,2);

  • NUMERIC과 DECIMAL은 유리수가 반올림되지 않고 저장된다.
• Tip : 가능하면 FLOAT을 사용하지 말라.

• 목표 : 칼럼의 값을 고정된 집합의 값으로 제한하고, 해당 칼럼이 유효하지 않은 항목을 절대로 포함하지 않는다고 보장한다.
• 안티패턴 : 칼럼 정의에 값지정
  • CHECK 제약조건 사용
  • MySQL에서 ENUM 사용
• 안티 패턴 사용이 합당한 경우 : 값의 집합이 변할 가능성이 거의 없을 경우.
• 해법 : 각 값을 행으로 저장하는 색인 테이블을 만들고, 해당 행을 FK 제약조건으로 참조하도록 한다.
• Tip : 고정된 값의 집합에 대한 유효성 확인을 할 때는 메타데이터를 사용하라. 유동적인 값의 집합에 대한 유효성을 확인할 때는 데이터를 사용하라.

• 목표 : 이미지 또는 이진 데이터 저장
• 안티패턴 : 이미지를 파일 시스템에 파일로 저장하고 파일에 대한 경로만 VARCHAR로 데이터베이스에 저장한다.
• 해법 : 아래에 대해 모두 답할 수 있게 애플리케이션을 설계했다면 파일을 사용하고, 그렇지 못하다면 BLOB을 사용하라.
  • 데이터의 백업과 복원 절차에서 데이터베이스와 이미지 파일 관계를 제대로 복원할 수 있는가?
  • 이미지가 계속쌓이는가? 필요하지 않으면 삭제되는가? 삭제는 자동인가 수작업인가?
  • 이미지에 대한 권한 검사가 필요한가? 권한이 없는 이미지를 사용자가 요청하면 뭘보게 되는가?
  • 이미지에 대한 변경을 취소할 수 있는가? 그렇다면, 애플리케이션이 이전 상태의 이미지로 복원해야 하는가?
• Tip : 데이터베이스 밖의 리소스는 데이터베이스가 관리하지 않는다.

• 목표 : 성능 최적화. 데이터베이스의 성능을 향상시키는 가장 좋은 방법은 인덱스를 잘 활용하는 것이다.
• 안티패턴 :
  • 인덱스를 불충분하게 정의하거나 또는 아예 정의하지 않는다.
  • 너무 많은 인덱스를 정의하거나 도움이 되지 않는 인덱스를 정의한다.
  • 어떤 인덱스도 도움이 될 수 없는 쿼리를 실행한다.
• 해법 : 인덱스를 MENTOR하라. 인덱스 샷건은 적절한 이유 없이 인덱스를 생성하거나 삭제하는 것에 대한 안티패턴이다.
  • Measure 측정 : 느린 쿼리를 찾아라.
  • Explain 실행 계획 확인
  • Nominate 지명 : 쿼리 추적 통계 정보를 모으고 쿼리를 위해 있으면 좋은 새로운 인덱스 생성 권고 등의 변경 제안
  • Test 테스트 : 인덱스를 생성한 후, 쿼리를 다시 프로파일링해야 한다. 변경으로 인해 차이가 생겼고 작업이 제대로 됐다는 것을 확인하는 것이 중요하다.
  • Optimize 최적화 : 캐시 등.
  • Rebuild 재구성 : 주기적으로 인덱스 정비. MySQL에서 ANALYZE TABLE 또는 OPTIMIZE TABLE
  • 인덱스에 대한 맹목적 추측은 좋은 전략이 아니다.
• Tip : 데이터를 알고, 쿼리를 알고, 인덱스를 MENTOR하라.

• 목표 : NULL을 포함하는 칼럼에 대한 쿼리 작성
• 안티패턴 : NULL을 일반 값처럼 사용
• 안티 패턴 사용이 합당한 경우 : NULL을 사용하는 것은 안티패턴인 아니다. NULL을 일반적인 값처럼 사용하거나 일반적인 값을 NULL처럼 사용하는 것이 안티패턴이다.
• 해법 : 유일한 값으로 NULL을 사용하라.
  • NULL이 연관된 연산은 대부분 결과가 NULL임을 기억할 것
  • IS NULL/IS NOT NULL/IS DISTINCT FROM(피연산자가 NULL이더라도 true/false리턴, MySQL에서는 ⇔)
  • 어떤 칼럼이 논리적인 디폴트 값을 가지지 않더라도 NOT NULL 제약조건이 필요한 경우는 정당하고 흔하다.
  • COALESCE() 함ㅜ : 가변인수를 받아서 NULL이 아닌 첫 인수 리턴
• Tip : 어떤 데이터 타입에 대해서든 누락된 값을 뜻하는 데는 NULL을 사용하라.

• 목표 : 그룹의 최댓값(또는 최솟값 또는 평균 값)뿐 아니라 해당 값을 찾은 행의 다른 속성도 포함하도록 쿼리를 작성하는 것이다.
• 안티패턴 : group 되지 않은 칼럼을 참조한다.
• 해법 : 단일 값 규칙(SELECT 목록에 있는 모든 칼럼은 그룹당 하나의 값을 가져야 한다). 여러가지 타계책은 책에.
  • MySQL의 GROUP_CONCAT() 함수
• Tip : 모호한 쿼리 결과를 피하기 위해 단일 값 규칙을 따라라.

• 목표 : 임의(random)의 샘플 데이터만 리턴하는 효율적인 SQL 쿼리 작성
• 안티패턴 : 데이터를 임의로 정렬해서 가져오기.
  • RAND()와 같은 비결정적 수식으로 정렬하면 인덱스를 활용할 수 없게 된다.
• 안티 패턴 사용이 합당한 경우 : 목록 크기도 적당하고 데이터가 늘어날 일이 거의 없는 경우라면 괜찮다.
• 해법 : 테이블 전체 정렬을 피하라.
  • 1과 PK 최대값 사이에서 임의의 값 선택. - 단, 1과 최댓값 사이에 빈 값이 없어야 함.
  • 1과 최대값 사이에서 임의의 값을 골라 그 다음으로 큰 값 사용. - 빈틈이 드물게 존재하고 모든 키 값이 동일한 빈도로 선택되는 것이 중요하지 않을 때.
  • 모든 키 값의 모록을 구하고서 임의로 하나 고르기. - 애플리케이션 코드 이용
  • 오프셋을 이용해 임의로 고르기. 오프셋 값을 랜덤으로 생성해서 LIMIT 1 OFFSET :offset으로
  • 벤더 종속적 방법
• Tip : 어떤 쿼리는 최적화 할 수 없다. 이 경우에는 다른 접근방법을 취해야 한다.

• 목표 : 전체 텍스트 검색
• 안티패턴 : 패턴 매칭 사용
  • LIKE '%crash%'
  • REGEXP 'crash'
  • 패턴 매칭 연산자의 주요 단점 : 성능이 안 좋다.
• 안티 패턴 사용이 합당한 경우 : 필요할 때만 가끔 사용하는 쿼리
• 해법 :
  • SQL(또는 관계형 이론)의 기본 원리 중 하나는 칼럼에 들어 있는 값이 원자적이어야 한다는 것이다. 즉, 한 값을 다른 값과 비교할 수 있지만, 비교를 할 때는 항상 전체 값과 비교해야 한다. SQL에서 부분문자열을 비교하는 것은 비효율적이거나 부정확하다.
  • DBMS가 제공하는 풀텍스트 인덱싱 기능
  • 써드파티 검색엔진 : Sphinx Search - DB 연동 가능, Apache Lucene
  • 인덱스 테이블 직접 만들기
• Tip : 모든 문제를 SQL로 풀어야 하는 것은 아니다.

• 목표 : SQL 프로그래머들이 일하면서 가장 흔하게 수렁에 빠지는 경우 중 하나가 “이걸 어떻게 하나의 쿼리로 할 수 있을까?”하고 생각할 때다.
• 안티패턴 : 모든 결과를 하나의 쿼리로 만들려고 한다.
• 해법 : 여러 쿼리로 나눠라.
  • 그냥 나눠.. 뭘 고민해.
  • UNION 연산 사용
  • SQL로 SQL을 자동 생성해서 실행
• Tip : 하나의 SQL로 복잡한 문제를 풀 수 있을 것처럼 보이더라도, 확실치 못한 방법의 유혹에 넘어가면 안된다.

• 목표 : 타이핑 줄이기.
  • * 기호는 모든 칼럼을 뜻한다. 따라서 칼럼 목록은 명시적이 아니라 암시적이다.
• 안티패턴 : 칼럼 이름 지정 없이 와일드카드(*) 사용하기
• 안티 패턴 사용이 합당한 경우 : 임시로 사용하고 버리는 SQL
• 해법 : 와일드카드나 암묵적 칼럼 목록에 의지하기보다는, 항상 필요한 칼럼을 나열해야 한다.
• Tip : 원하는 대로 가져가라. 그러나 가져간 건 다 먹어야 한다.

• 목표 : 패스워드를 복구하거나 재설정하기
  • 패스워드를 평문으로 저장하는 것 뿐만 아니라 평문으로 네트워크(DB 접속 포함)로 전달하는 것도 안전하지 않다.
• 안티패턴 : 패스워드를 평문으로 저장하거나 복호화 가능하게 저장하기
• 안티 패턴 사용이 합당한 경우 : 패스워드로 다른 써드파티에 접속해야 할 필요가 있을 경우인데, 이때에도 평문이 아니라 복호화 가능한 방식으로 암호화하여 저장해야 한다.
• 해법 : 패스워드의 소금(SALT) 친 해시 값을 저장한다.
  • 일방향 해시 함수를 사용해 패스워드를 부호화한다. 원래 문자열로 복호화 불가능한 알고리즘을 사용한다.
  • 권장 알고리즘 : SHA-256(SHA2),SHA-384,SHA-512 …
  • MD5,SHA-1 비권장
  • 단순히 패스워드만 해시하지 말고 각 사용자별로 따로 생성한 SALT를 더해서 해싱하라.
  • DB에 SQL 전송할 때 패스워드를 넣지 않도록 하라.(미리 해싱한 값을 전송)
  • 브라우저에서 웹서버로 패스워드를 보낼 때는 HTTPS 같은 보안 프로토콜 사용. → JavaScript로 RSA 암호화 로그인
  • 패스워드 복구 금지, 이메일로 패스워드 전송 금지.
• Tip : 당신이 패스워드를 읽을 수 있으면, 해커도 읽을 수 있다.