SQL Antipattern -Avoiding the Pitfalls of Database Programming- 을 읽고

읽게 된 경위

SQL을 다뤄본 지 얼마 되지 않았고 체계적으로 배워본 적도 없는지라 평소에 비효율적인 쿼리를 실행하는 일이 많던 차에 상사에게 추천받아 읽게 되었다. 사실 회사 생활 하면서 DB를 관리하는 엔지니어가 아니더라도 A/B 테스트 결과 분석, 유저 경향 분석 등 시스템 개선을 위한 의사결정에 꼭 필요한 데이터를 다루기 위해서는 SQL에 능숙한 편이 좋다는 것을 깨닫고 있다.

 

우리 회사에는 SQL에 굉장히 능숙한 마케팅 팀장님(엔지니어도 아니고 다른 프로그래밍 언어도 사용할 줄 모름)이 계시는데 이 분은 본인이 데이터를 만지면서 여러 분석을 통해 다음 마케팅 방향을 잡는다. 이걸 보면서 나는 엔지니어인데 이 분만큼도 SQL을 다루지 못하는 것에 부끄러움도 느꼈지만 다른 비 엔지니어 팀원이 데이터 분석을 위해 일일이 엔지니어를 찾는 것을 보고 요즈음 시대에 SQL을 다루는 것은 필수 조건이 되어가고 있다는 것을 깨달았다.

다 읽고 난 간략한 감상

'SQL Antipattern' 은 SQL을 어느 정도 다룰 줄 알고 (최소한 LEFT JOIN, RIGHT JOIN 에 대해서 제대로 이해하고 쓰고 있는 수준이어야 할 듯) 읽어야 하는 책이다. 때문에 아직 그 원리나 기능을 제대로 이해하고 있지 못한 나는 나중에 한 번 더 읽어야 할 듯 싶다.

 

이와 상관 없이 이 책은 데이터를 다룬다 하는 분들에게는 꼭 추천하고 싶다. 왜냐 하면, '이렇게 하는게 효율적이니까 이렇게 해'가 아니라 '이렇게 쓰고 있지 않아? 근데 이런 문제가 일어나지 않아? 혹은 이런 상황에 문제가 생길 것 같지 않아?' 하며 먼저 한 번쯤 접하는 문제를 제시한다. 그 다음 '이게 왜 나쁜지 한 번 보자' 하면서 독자를 납득시키고 나서야 '그럼 이렇게 고치는 것이 낫겠지?'하고 설득을 한다. 굉장히 스무스하게 문제에 대해 이해하고 해결법에 대해 납득한다.
특히 책 초반부터 끝까지 문제 상황은 다 다르지만 같은 테이블, 같은 데이터를 가지고 이야기를 풀어가기 때문에 상황을 파악하기도 쉽다.

우리 팀에 적용해서 생각할 수 있었던 패턴

제이워크 (신호 무시)

안티패턴: 속성값을 리스트 문자열로 갖고 있는 경우
이것이 안티패턴인 이유는 아래와 같다.

• 특정 값에 한해서 쿼리를 쓰기가 힘들다
• 구분 기호가 속성 문자열에 포함되어 있는지 확인하지 않으면 에러가 난다
• 리스트 길이를 미리 정해야 하기 때문에 격납할 수 있는 속성 개수가 한정되어 있다
• 그런데 문자열이기 때문에 어떤 한 속성 길이가 길어지면 남는 자리가 없어서 각 값마다 다른 격납 가능 최대 수가 적용된다

해결법: 교차 테이블 작성

• 테이블을 새로 작성해서 하나의 속성만을 격납

EAV

제이워크 상황과 비슷하게 생각 된 것이 EAV(Entity/Attribute/Value)이다.

안티패턴: 속성명만을 갖는 열과 속성만을 갖는 열을 각 엔티티마다 두고 값을 격납하는 방식
이것이 안티패턴인 이유는 아래와 같다.

• 새로운 속성에 대응하기 힘들다
• 어떤 엔티티에 어떤 속성명을 가진 속성이 격납되어있는지 제한하기 힘들다
• 속성을 취득해야 할 때 어떤 엔티티에는 없고 어떤 엔티티에는 있어서 쿼리가 복잡해진다
  • 내외부 결합을 해야하는 경우가 많아진다
• SQL에서 지원하는 데이터형식을 사용할 수 없다
  • 따라서 잘못된 데이터형식으로 격납되어도 금방 알아차리기 힘들다

해결법: 속성 별로 모델링을 잘 하자

• 싱글 테이블 계승: 각 속성명을 열 이름으로 지정해서 관리
  • 하지만 이 방법은 속성이 늘어날 때마다 열을 추가해주어야만 하기 때문에 관리가 힘들다
• 구상 테이블 계승: 각 속성별로 테이블을 생성
  • 하지만 하나의 엔티티에 대해 어떤 속성이 있는지 알고 싶을 경우 모든 테이블을 결합해야만 하기 때문에 쿼리가 복잡해진다
• 클래스 테이블 계승
  • 오브젝트 지향 프로그래밍의 클래스 계승을 모방하는 형태로 부모 테이블과 자식 테이블을 만든다
  • 속성이 존재하지 않을 경우 NULL이 반환된다
• 반구조화 데이터
  • 속성을 XML이나 JSON형식으로 갖는 방식이다

우리 팀이 DynamoDB를 사용했을 때에는 속성값에 JSON형식으로 격납했었지만 RDS로 옮기면서 EAV형식으로 바꿨다.
이 책에는 안티패턴이라고 소개되었지만 우리 팀은 일부러(자세한 경위는 잘 모른다. 나는 당시 테이블 설계 회의에 참가하지 않았다) EAV를 택했다고 한다. 그 이유를 추측해보자면, 우리 팀이 관리하는 데이터는 반드시 모든 엔티티가 같은 속성명의 속성을 갖기 때문에 EAV형태여도 관리하기 쉽다.
속성명은 문자열이기 때문에 '잘못 입력하면 어쩌지?' 할 수 있지만 사람이 직접 그 값을 입력하는 경우는 없다(없어야 한다). 또한 속성 종류가 많지 않다.

Polymorphic

앞서 적은 패턴과 비슷한 패턴이라 적어본다.

안티패턴: 해당 열에 둘 중 하나만 격납할 수 있도록 설계한 경우
이것이 안티패턴인 이유는 아래와 같다.

• 외부키(Foreign key)에서 테이블은 하나만 지정해야하지만 어떤 값이 들어갈지 모르기 때문에 지정하지 못한다(참조정합성제약을 정의할 수 없다)
• JOIN하는 테이블은 동적으로 지정할 수 없다
  • 쿼리가 복잡해진다
• 예를 들어, 여기서 말하는 두 값이 주문 상품을 출하하는 곳과 수령하는 곳이 같은 주소라 할지라도 두 가지가 같다고 관련지을 수 없다

해결법: 참조를 반대로 한다(교차 테이블을 만든다)

• 두 값을 참조하는 테이블이 아니라 각 값을 격납하는 테이블 두 개를 만든다
  • 외부키를 지정할 수 있다
• 하지만 이 방법은 허용하고 싶지 않은 관계가 허용될 수 있다는 점이다
  • 이럴 때는 UNIQUE로 강제할 수 있다
• 공통되는 값을 갖는 테이블을 작성한다
  • 오브젝트 지향적인 설계를 한다
• 참조하는 테이블과 참조되는 테이블이 항상 각각 하나만 존재해야 한다는 점을 잊지 말자

Multi Column Attribute

이 패턴도 속성을 관리하는 방법에서 볼 수 있다.

안티패턴: 속성만큼의 열을 추가했을 경우
이것이 안티패턴인 이유는 아래와 같다.

• 값을 추가하고 삭제하려면 열을 추가하고 삭제해야만 한다
• UPDATE할 경우 하나의 열만을 변경하려고 해도 어떤 열이 비어있는지 확인하기 힘들다
• 값이 들어있지 않은 경우 NULL이 들어가는데 이 경우 행을 변경할 수 없다
• 일의성을 보장하지 못한다

해결법: 종속 테이블을 작성한다

인덱스 숏건

쿼리 실행 속도가 느린게 인덱스가 있어서다 없어서다라며 회의 때 의견이 왔다 갔다 했는데 나는 전혀 그 이유가 파악이 안되었다. 그 당시를 떠올리면 이 문제에 관련된 것 같아 적어본다.

안티패턴: 퍼포먼스를 최적화하고 싶다고 무작정 인덱스를 사용할 경우
이것이 안티패턴인 이유는 아래와 같다.

• 인덱스를 갱신하려고 데이터베이스에 오버헤드(지연)가 생길 수 있다
  • 인덱스를 활용하는 쿼리를 쓰지 못할 경우 굳이 인덱스로 지정할 필요가 없다
    • 예를 들어 이름이 '철수'인 사람을 찾을 때에는 많은 사람의 이름이 '철수'일 가능성이 있어 인덱스를 활용할 수 없다
    • 선택지가 적으면 인덱스 유효성도 떨어진다: 단, MySQL이 아닌 DB일 경우 정 반대이다

인덱스를 지정해도 되는 경우는 아래와 같다.

• 주키(Primary key)

인덱스를 지정하지 않아도 되는 경우는 아래와 같다.

• VARCHAR(80)정도 되는 긴(사이즈가 큰) 문자열을 격납할 경우 검색 혹은 정렬할 일이 거의 없을 것이기 때문에 인덱스를 두지 않아도 된다
• 시간 데이터에 관해서도 똑같이 적용할 수 있다
• 복합 인덱스는 여러 장점이 있지만 많은 경우 사용 빈도가 적고 순서가 아주 중요해지기 때문에 검색 조건, 결합 조건, 정렬 순서 등을 왼쪽 -> 오른쪽으로 고려해서 실행해야만 한다

해결법: 'MENTOR'원칙에 따라 효과적으로 인덱스를 관리하자

• M: Measure(측정)
  • 쿼리 성능을 측정할 때에는 쿼리 결과 캐쉬를 전부 무효화하자
  • 실행하는데 가장 시간이 걸리는 쿼리라 하더라도 실행 빈도가 적은 경우에는 어플리케이션에서 최대 비용이 드는 쿼리라 할 수 없기 때문에 실행 빈도가 높은 쿼리에 주목해야 한다
• E: Explain(해석)
  • QEP 분석 결과 레포트를 취득한다
    • LIKE는 테이블 전체를 스캔하기 때문에 인덱스를 작성해 LIKE 대신 전문 검색 기능을 사용하는 것이 좋다
• N: Nominate(지명)
  • 원스텝만에 정보를 얻을 수 있게 하자
  • 얻고 싶은 정보가 순서대로 기재되어 있고 어떠한 엔티티 속성으로 값을 넣어두면 한 번에 찾을 수 있다
• T: Test(테스트)
  • 쿼리 프로파일링을 통해 성능을 측정한다
• O: Optimize(최적화)
  • 메모리에 있는 인덱스에 접근하는 것이 디스크에 있는 데이터를 읽는 것보다 시간이 단축된다
  • 사용 빈도가 높은 데이터 혹은 인덱스를 캐쉬로 격납하는 것을 데이터베이스에 의존하지 말고 미리 인덱스를 캐쉬 메모리에 로드 해 놓는 것이 좋다: LOAD INDEX INTO CACHE
• R: Rebuild(재구축)
  • 정기적으로 관리한다

리더블 패스워드

우리 팀은 패스워드를 DB로 관리할 일은 없어서 당장 쓸 지식은 아니지만 엔지니어로 일하려면 필요한 지식일 것 같다.

안티패턴: 패스워드를 그대로 격납한다
이것이 안티패턴인 이유는 아래와 같다.

• SQL문을 공격자가 읽을 경우 패스워드가 그대로 노출된다
• SQL 쿼리 로그에서 찾을 수 있다
• 백업 파일이 노출될 수 있다
• 패스워드 그대로 메일에 보낼 경우 메일이 해킹당하면 패스워드가 노출된다

해결법: 해쉬값으로 격납한다

• 길이가 고정되어있기 때문에 해쉬에서 원문을 찾기는 힘들다
• 해쉬값은 불가역 특성을 갖는다
• 치환자를 사용한다
• 유저가 암호를 잃어버렸다면 해당 패스워드를 버리고 한번 쓰고 버릴 패스워드를 생성해준다

Diplomatic Immunity

안티패턴: SQL만 특별취급 하는 경우

• SQL만 버전관리 안하거나
• SQL만 테스트 코드가 없거나
• SQL만 도큐멘트를 작성하지 않거나

해결법: 버전관리 하고 유닛 테스트 작성하고 도큐멘트도 성실히 작성한다

 

우리 팀은 Python 속에서 쿼리를 실행하는 경우와, SQL을 적어서 수동으로 쿼리를 실행하는 경우로 나뉜다. Python 속에서 실행하는 경우에는 DDD 패턴에 맞춰서 리포지토리로 관리하고 있다. SQL파일은 같은 내용이더라도 그 날 실행할 쿼리를 파일로 보존해 버전 관리를 하고 있다. 하지만 이 경우 테스트 코드는 없기 때문에 개발 환경에서 실행해보고 결과 확인하는 프로세스가 필요하다. 도큐멘트는 작성하고 있다.

모래성

안티패턴: 가능성을 상정하지 못한 경우
해결법: 되도록 많은 패턴을 상정해두고 백업도 성실히 해 둔다

엄청 기초적이지만 나는 몰랐던 사실

라운딩 에러

안티패턴: 정수 대신 FLOAT데이터형을 사용하는 경우
이것이 안티패턴인 이유는 아래와 같다.

• 지수부를 2진수로 표현하기 때문에 10진수 값을 나타낼 때에는 항상 어느 정도의 오차가 존재한다
• 데이터베이스에서는 FLOAT열에 있는 값이 반드시 유한 정도로 표현되리라는 보장이 없기 때문에 어플리케이션에서 대상의 열이 되는 모든 값의 소수점이 제대로 표현되지 않았을 수 있다
• 부동소수점이 굉장히 작은 범위로 차가 날 경우 이것을 실질적으로 같은 값으로 처리해야한다
• 초반엔 작았던 오차가 예를 들어 이자 복리계산 때에는 곱셈을 여러번 해서 커지게 된다

해결법: NUMERIC 혹은 DECIMAL데이터형을 사용하자

• NUMERIC, DECIMAL형은 고정소수점을 가진다

Fear of The Unknown

안티패턴: 결손값을 채우려고 NULL을 일반값처럼 사용하는 경우
이것이 안티패턴인 이유는 아래와 같다.

• NULL은 0가 아니다
• NULL은 ''가 아니다
• NULL은 False가 아니다
• NULL은 NULL이기 때문에 비교대상으로 사용할 수 없다

해결법: NULL을 일의적인 값으로 사용한다

• 논리 연산이 아니라 IS를 사용한다
• 열에 NOT NULL 제약을 선언한다
• 동적인 기본값을 설정해둔다

Pseudokey Near-Freek

Fear of The Unknown과 비슷한 패턴이다.
안티패턴: 결손값을 메꾸는 경우
이것이 안티패턴인 이유는 아래와 같다.

• 원래 부여했었던 번호를 다시 설정해야 한다
• 무엇이 결손된 것인지 모른다
• 예를 들어, 유저가 서비스를 탈퇴했다고 해당 아이디를 메꾸면 그 때마다 유저의 아이디가 바뀌어 관리하기 힘들다

해결법: 의사키로 결손치를 메꾸지 않는다

• 행에 숫자를 붙인다
  • 주키와 혼동하지 않는다
• GUID를 사용한다
  • 연속된 값이 아니기 때문에 결손값을 신경쓰지 않아도 된다
  • 하지만 값에서 패턴을 추측하거나 대소관계를 알 수 없다
  • 16바이트만큼 필요하다

내 쿼리를 효율적이게 할 때 필요한 지식

Ambiguous Group

안티패턴: 그룹 내에서 최대치를 찾고 싶은데 그룹화 되지 않은 열을 참조하는 경우
이것이 안티패턴인 이유는 아래와 같다.

• 단일값 원칙에 반한다
• MySQL은 에러가 아니라 해당 그룹의 가장 첫번째 값을 반환한다
• SQLite도 에러가 아니라 해당 그룹의 가장 마지막 값을 반환한다

해결법: 애매하지 않은 열을 사용하자

• 함수 종속성이 있는 열만을 가지고 쿼리를 실행한다
• 상관 서브쿼리를 사용한다
• 도출 테이블을 사용한다
• JOIN을 사용한다
• 다른 열에 대해서도 집약함수를 사용한다
• 그룹별로 모든 값을 연결한다

Poor Mans Search Engine

전에 로그 테이블 특정 열에 격납되어있는 긴 문자열 속에서 필요한 정보만을 빼내는(이 테이블이 딱 제이워크 패턴에 해당한다) 쿼리를 적어야 했다. 당시 조건도 그 긴 문자열을 가지고 해야 했다. 당시 조건 문에 정규표현을 사용해서 엄청 느렸던 기억이 난다.
당시를 회상하며 키워드를 정확하고 빠르게 찾는 쿼리를 쓰기 위한 법을 적어보겠다.

(참고로 당시에 최종적으로 선택한 것은 LIKE였다)

 

안티패턴: 패턴 매치 명령어를 사용해 전문 검색을 할 경우
이것이 안티패턴인 이유는 아래와 같다.

• 패턴 매치는 테이블에 있는 모든 레코드를 스캔하기 때문에 인덱스의 장점을 살릴 수 없다

해결법: 적절한 툴을 사용하자

• SQL이 아니라 전문 검색 엔진을 사용한다
  • MySQL의 경우 MyISAM이라는 스토리지 엔진이 있는데 이것이 full text index를 제공한다
  • 인덱스에 격납된 텍스트에서 키워드를 검색할 때에는 MATCH함수를 사용한다
• Oracle에서는 CONTAINS, CTXCAT, CTXXPATH, CTXRULE연산자를 사용할 수 있다
• Microsoft SQL Server에서 CONTAINS로 전문 검색한다
• PostgreSQL에서는 TSVECTOR, GIN연산자로 검색한다
• SQLite에서는 FTS로 전문검색한다
• 서드 파티 검색 엔진으로 Sphinx Search, Apache Lucene을 사용할 수도 있다

스파게티 쿼리

안티패턴: 한번에 모든 결과를 얻으려고 장황하게 쿼리를 작성하는 경우
이것이 안티패턴인 이유는 아래와 같다.

• 의도하지 않은 결과를 얻을 수 있다
  • Cartesian product가 일어날 수 있다
• 쿼리를 수정하기 힘들다
• 버그 찾기가 힘들다

해결법: 분할한다

• 한번에 끝내려는 생각을 버린다
• UNION을 사용한다
• CASE식과 SUM함수를 함께 사용한다