검색 속도가 빨라지는 데이터베이스 설계

※ 일본의 한 블로그 글을 번역한 포스트입니다. 오역 및 의역, 직역이 존재할 수 있으며 틀린 내용은 지적해주시면 감사하겠습니다.
 

우리 회사의 검색 DB에 대한 간단한 설명

---

 먼저 전제로서 우리 회사에서 다루고 있는 데이터에 대해서 가볍게 소개하고자한다.
 

다루는 데이터의 복잡도

 예를 들어, 여행 회사용 어플리케이션이라면 숙박 요소 정보로 다음과 같은 것들을 보유하고 있다.

•  호텔 정보 (약 2만건)
• 플랜 정보 (조식 여부 등) (0~1500건/시설)
• 고객 정보 (~100건/시설)
• 재고 (~366일분)
• 요금 (~366일 제곱 x 10인수 패턴 세제곱 x 5패턴의 숙박하는 사람의 구분 세 제곱 / 플랜)

  전개하면 요금 데이터로는 10~100 건의 주문이므로 복잡하다.
1. 1개의 객실이 여러 개의 플랜으로 판매되므로, 재고는 플랜을 걸쳐 공유하고 있다.
2. 날에 따라 요금이 다르다(성수기는 비싸지는 등 시즌에 따르는 경우).
3. 숙박 인수에 따라 요금이 달라진다(2명 1객실 요금은 1명 1객실의 2배가 아니다).
4. 숙박하는 사람 구분 ( 어른 / 어린이/ 유아/ 시니어 등) 에 따라 요금이 다르다. 또한 "이 연령의 어린이는 어른을 ㅗ카운트한다'라는 로직이 플랜마다 존재한다.
5. 연속으로 몇 박을 숙박하는 경우 할인이 들어가는 플랜도 존재한다.
 

검색의 종류

 이러한 복잡한 데이터에 대해 다양한 축으로 검색하고 있다. 방금의 여행계 어플리케이션의 예로 들자면, 다음과 같이 정리할 수 있다.

• 선택한 일정의 판매 가능여부 판다(재고 검색)
  • 플랜의 판매 기간 내인가 (설정기간, 판매 중지등)
  • 각 날짜마다 재고가 존재하는가 (어른 2명, 5살의 어린이 한 명)
• 선택 조건으로 요금 검색
  • 예산의 범위 (5만원이상, 15만원 이하)
  • 집약 표시 (동일 설비임에도 날에따라 플랜에 따라 요금이 다르므로 요금을 집약하여 최저 ~ 최대 금액 폭으로 표시)
  • 날짜 미지정(캘린더 형식)으로 요금 표시
  • 계산 후의 요금의 저렴한 순서, 비싼 순서 정렬
• 조건에 맞는 검색(속성 검색)
  • 숙박 지역
  • 객실의 고집 조건 (금연실 등)
  • 호텔의 고집 조건 (역에서 도보 5분등)
  • 플랜의 고집 조건 (조식포함 등)
• 시설명이나 키워드 지정으로 검색 (모든 문자 검색)
• 지도 검색(표시 영역내에서 검색)

 더욱이 교통수단이나 지역 패키기 투어 검색도 다루는 경우 더 복잡해진다.
 이러한 "5월 3일 ~ 5일, 어른 4명, 서울시내, 역에서 5분이내, 조식 포함, 예산 X만원이내" 와 같은 다양한 조건을 포함한 검색을 엔드 유저가 쾌적하고 빠르게 사용하기 위해서는 기술적으로 많은 궁리를 해야했다.
 

데이터의 갱신 방법, 라이프 사이클

DB의 갱신도 조금 특수하다.
하루에 몇 번이고 배치 처리를 하여, 고객의 기반 시스템에서 연계된 모든 데이터를 읽어 들여, 검색, DB를 전부 재구축하고 있다.

• INSERT나 UPDATE가 이닌, DROP TABLE > CREATE TABLE
• 연걔된 시점의 데이터를 검색시 특화한 테이블 구성으로 변환
• RDMS로 PostgreSQL을 채용

 
 

검색이 빨라지는 데이터 베이스 설계

---

 그럼 지금부터 궁리한 내용을 구체적으로 소개하도록 하겠다.
 

노하우 1 : 검색용 테이블을 분리한다.

 우리 회사에서는 마스터 테이블과 다르게, 검색에 특화된 테이블 (아래부터는 '서치 테이블' 이라고 부르도록 하겠다)을 만든다. 서치 테이블에는 검색시에 참고하는 정보만을 집약하여 불필요한 데이터는 두지 않음으로써 공유 버퍼에 효율 좋게 캐시되기 쉽다.
 예를 들어, 호텔을 검색하기 위한 테이블이 있으며, 서치 테이블에는 호텔이나 지역의 번호나 키워드용 문구, 고집 조건, 나열 순등을 저장한다. 한편으로, 전화 번호, 주소 문자열, 시설의 안내, 이미지 URL등 표시용 정보는 마스터 테이블로 분리하고 있다.
 또한, 판매기간 외 등 검색 결과에 표시되지 않는 레[코드는 서치 테이블 생성 시에 제외한다. 이렇게 만든 테이블에 대해서, 검색시에는 아래의 흐름으로 온라인 쿼리를 실행한다. 

• 먼저 서브 쿼리 내 등에서 서치 테이블 상으로 좁혀 건수를 줄여나간다(WHERE과 LIMIT).
• 좁힌 후의 데이터에 대해 index를 붙인 마스터 테이블을 결합한다(JOIN).  

 최종적으로 "조건에 맞는 호텔에 대해 표시에 필요한 정보를 마스터 테이블에서 획득한다"는 것은 동일하지만, 미리 거대한 테이블을 메모리에 올릴 필요가 없어져서 대폭으로 퍼포먼스가 향상된다.
 

노하우2 : 비정규화

 DB설계시 테이블을 적절히 분리하고, DB의 확장화를 배제하고 효율적으로 정보를 유지하는 "정규화"에 대해 많이 들어봤을 것이다.  그러나 정규화하지 않는 방법도 고려할 수 있다.

• 온라인에서의 테이블 결합 횟수를 줄이기 위해, 굳이 중복된 정보를 여러 테이블에 저장한다.
• 서치 테이블의 레코드 수를 줄이기 위해 굳이 테이터를 배열로 저장한다.

 PostgreSQL는 행 지향의 DB로, 어떤 행을 카운트하는 것은 서투르기 때문에, 특히 집계 처리등에서도 배열을 사용하여 레코드 수를 줄이는 쪽이 퍼포먼스면에서 유익하다.
 물론 이대로는 PostgreSQL에서 다루기 힘드므로, 유저 정의 함수를 사용하여 배열 그대로 고속 처리할 수 있도록 하고 있다.
 무엇보다 무작정 결합 횟수만 줄여도 테이블이 비대해져 버리기 때문에 두 가지 요소를 고려해야한다. 

• 테이블의 레코드수와의 밸런스 (행의 수가 많은 테이블은 칼럼 수를 너무 늘리지 않는다)
• 그 컬럼이 어떤 검색에 필요한지 

 

노하우3 : 경량 데이터형/ 독자 데이터형의 이용

 서치 테이블에는 굉장히 적은 경량 데이터형이 준비되어 있다. 예를 들어 서치 테이블의 정보 중 ID나 지역등에는 번호를 부여하여 상태를 저장한다. 아래와 같은 이미지이다.

마스터 테이블

호텔 코드(text)	호텔 이름(text)	지방(text)	도시(text)	구(text)
a130354	신주쿠 호텔	관동	도쿄도	신주쿠

서치 테이블

호텔 인덱스(smallint)	지역 인덱스(smallint)
139	1305

 또한, 임베디드형보다 한층 더 작고 효율적으로 정보를 유지하기 위해서 사내에서는 독자적인 데이터형을 정의하고 있다.
 예를 들어, 여행업계용의 어플리케이션에서는 날마다 인수마다 재고나 요금 데이터를 효율적으로 저장할 수 있는 데이터형을 자체적으로 제작하였다. 
 한편, 전문 상사의 세계에서는 날마다 요금 차이가 없지만, 기업 제약(이용 기업마다 요금을 나누어 내는 등)이 필요하므로, 요금 그룹을 저장할 수 있는 형태를 만들고 있다. 
 각 업계마다 고객의 데이터 특성이나 검색시의 처리에 맞춘 형태를 고려하는 것으로 데이터를 집약하여 레코드 수를 감소하거나, 하나의 레코드의 데이터 사이즈를 압축하고 있다.
 상세한 구현까지는 설명하지 않겠지만, 이러한 독자적인형태에는 고객의 도메인이나 데이터 구조의 이해를 바탕으로 만들 필요가 있다.
 

노하우 4 : 유저 정의 함수를 활용

 동일하게 사내에서는 독자 유저 정의 함수도 사용하여 퍼포먼스를 개선하고 있다. PostgreSQ에는 풍부한 내장 함수가 준비되어있지만, 유저 스스로가 함수를 만들어 확장시킬 수도 있다.
 이때, SQL는 물론 다른 다른 언어도 사용할 수 있다. C언어를 사용하는 것도 가능하므로, 사내에서는 이러한 함수가 큰 활약을 하고 있다.
 예를 들어, 확장 이미지는 다음과 같은 형태로 정의하고 있다.

# include "postgres.h"
PG_FUNCTION_INFO_V1(plus_one);
Datum plus_one(PG_FUNCTION_ARGS);
Datum plus_one(PG_FUNCTION_ARGS){
    int32 input = PG_GETARG_INT32(0);
    PG_RETURN_INT32(input + 1);
}

 이것을 컴파일하여 plus_one.so 파일을 생성하여 PostgreSQL쪽에서 함수를 등록하여 쿼리내에서 실행하는 것도 가능하다.

CREATE or REPLACE FUNCTION plus_one(int4)
    RETURN int4 AS 'plus_one.so', 'plus_one'
    LANGUAGE C IMMUTABLE STRICT;

-- SQLで実行可能
SELECT plus_one(1);
  plus_one
------------
        2

 

노하우5: 컬럼 테트리스

 테이블 생성시에 기재 순서를 궁리하는 것으로 다루는 정보량을 그대로 저장 효율을 높일 수 있다. PostgreSQL에는 "얼라인먼트"이라는 경계가 데이터형 별로 정해져 있어 그 경계를 넘어서 배치할 수 없다(int4에서는 4바이트, int2에서는 2바이트, boolean에서는 1바이트 등).
 예를 들어 X(int4), Y(int2), Z(int4)이라는 순서로 배치하면 X, Y를 계속해서 배치한 후, 계속해서 Z를 배치하는 것이 일반적이지만 Z는 4배수 위치에서 시작할 필요가 있으므로, 자동적으로 2바이트만큼 패딩된 후에 배치되게 된다.
 반면 X, Z, Y순으로 배치하면 패딩이 발생하지 않는다. 

 PostgreSQL에서는 테이블 정의 시 작성한 순서가 그대로 물리적인 배치로 연결되기 때문에 컬럼 이름을 쓰는 순서의 차이에서 이러한 차이가 발생한다.

 행수가 적으면 별로 문제가 되지 않습니다만, 레코드수가 억 단위가 되는 거대한 제휴 데이터(고객으로부터 제휴되는 원시 데이터등)를 로딩할 때는, 패딩을 줄이는 것만으로 몇 GB나 삭감하는 경우가 있다.

이렇게 CREATE TABLE 할 때 얼라인먼트에 따라서 빈틈없이 배치하는 것을 흔히 컬럼 테트리스라고도 하는듯하다. 
 

노하우6: 사전 계산

 온라인에서 행하는 계산이 적어지도록 특히 빈번히 실행되는 계산은 DB 생성시에 미리 준비해두는 것도 효과적이다. 자주 볼 수 있는 예로는 형의 캐스트를 들 수 있다.
 언뜻 보기에 사소한 캐스트도 의외로 처리 시간이 필요한 경우가 있으므로 온라인 쿼리에서 이러한 캐스트가 발생하지 않도록 주의하자.
 특히 모르는 사이에 암묵적 캐스트가 발생해 버리는 경우는 자주 있기 때문에 테이블 작성 시점에서 컬럼의 데이터형과 함수의 인수 틀을 확실히 맞추는 등 평소 암묵적 캐스트를 시키지 않도록 습관을 들일 필요가 있다. 
 그 밖에도 정렬 키나 결합 키가 여러 개 있는 경우에 다시 번호를 매기는 컬럼을 만들어 놓기도 한다.  숙박 시설 정렬 등은 많을 때 4개 5개의 정렬 키가 지정될 수 있지만, 만일 이 중에 2개가 앞쪽에 나열되어 있다면 그것을 통합하여 하나의 키로 만드는 것으로 테이블도 작아지고 온라인 계산도 빨라진다.
 하지만 이것들은 노하우 1와 트레이드오프 되므로 적절하게 검토해야한다.
 

노하우 7 : 인덱스 작성의 궁리

 이것은 일반적으로 자주 언급되지만, 적절한 인덱스를 작성하는 것도 특정행의 추출을 고속화할 수 있다. 한편으로, 당연히 인덱스 자체도 사이즈를 가지기 때문에, 너무 많이 만들면 메모리를 차지해버려, 원래 메모리에 올려 두고 싶은 서치 테이블이 메모리에 올리지 못하게 되어버린다. 따라서 데이터의 편중이나 분포를 보고 정말 유효한 컬럼에 대해서 부여할 필요가 있다. 
 

---

참고자료
https://zenn.dev/forcia_tech/articles/202304_db_rapid_search_strategy