Issue: ✅ Feature

Description

API 엔드포인트

엔드포인트 : api/v1/boards/{board_id}/similar_board

테이블 정보

CREATE TABLE recommendation_similar_board (
      query_item BIGINT NOT NULL,                       // request로 요청된 상품
      recommendation_item BIGINT NOT NULL,    // request로 요청된 상품의 추천 상품
      score DECIMAL(17, 16),                                  // 유사도 점수 
      rank INT NOT NULL,                                       // 노출 순서 
      recommendation_theme VARCHAR(255),      // 어떤 로직의 추천인지
      model_version VARCHAR(255),                      // model의 버전
      created_at datetime(6),                                  // 생성일
      primary key (query_item, `rank`)
);

복합키 선정 이유

query_item과 rank는 고유성을 가지기 때문에 기본 키로 설정했습니다.
또한, rank를 기준으로 정렬(OrderBy)을 수행하므로 효율적인 인덱스를 활용할 수 있습니다.

기타 설명

score는 소수점 16자리까지 저장할 수 있도록 설정해야 합니다.

To do

[x] 테이블 생성
[x] 유사한 데이터 조회

ETC

recommendation_theme와 model_version 컬럼은 Enum 타입 컨벤션에 맞추기 위해 대문자로 작성하도록 요청했습니다.

Explain

생성되는 쿼리문

select
b1_0.id,
s1_0.id,
b1_0.profile,
s1_0.name,
b1_0.title,
b1_0.discount_rate,
b1_0.price,
bs1_0.board_review_grade,
bs1_0.board_review_count,
p1_0.gluten_free_tag,
p1_0.high_protein_tag,
p1_0.sugar_free_tag,
p1_0.vegan_tag,
p1_0.ketogenic_tag,
p1_0.is_soldout,
p1_0.category,
rsb1_0.recommendation_theme
from
recommendation_similar_board rsb1_0
    join
product_board b1_0
on rsb1_0.recommendation_item=b1_0.id
    join
store s1_0
on b1_0.store_id=s1_0.id
    join
board_statistic bs1_0
on b1_0.id=bs1_0.board_id
    join
product p1_0
on b1_0.id=p1_0.product_board_id
where
    rsb1_0.query_item=?
order by
rsb1_0.rank

인덱스를 잘 타고 있는 것을 확인
rank의 인덱스가 안걸려 있으면, 비효율적으로 인덱스를 탐
아직 자세한 내용 문의 중이라 계속해서 수정할 예정

pk auto_increment로 설정하는 게 낫지 않아요? mariadb는 클러스터링 인덱스라 그렇게 생성하는 게 좋아보여요
score decimal (17, 16)으로 생성하신 이유가 있으신가요?
varchar 쪽도 varchar가 동적이긴 한데 그래도 아예 작은 값을 쓰는 게 더 좋다고 알고 있어서 모델 명이나 recommendation_theme이 글자가 길지 않다면 글자수 짧게 하는 것도 좋을 것 같아요

복합키를 설정하면 두 개의 컬럼을 갖는 클러스터링 인덱스를 생성해줘서 괜찮습니다~ 성능 개선을 위해 복합키를 사용했습니다.
DB 예시 데이터를 보니 소수점 16자리까지 점수를 넘겨주더라구요. 이후에도 이렇게 올 것이라 예측해서 17,16 으로 설정했습니다.
만약 이슈가 있다면 그렇게 하는 게 좋을 것 같습니다. 혹시 관련 자료 받을 수 있을까요? 찾아보니 어떤 문제인지 명확히 말하는 글을 못찾았어요..(공부해보고 싶습니다)

1. 클러스터링 인덱스를 생성하는 것보다 auto_increment를 사용하지 않을 때의 성능 문제를 말씀드린 거였습니다. MySQL 과 MariaDB의 PK는 클러스터링 인덱스로 사용되다 보니 insert, update 등에서 auto_increment나 순차적으로 커지는 방향으로 저장하지 않으면 auto_increment를 사용할 때보다 성능이 좋지 않습니다. 그래서 uuid로 pk를 사용하는 경우에도 성능상 문제가 있어 사용하지 않거나 timestamp 등이 있는 버전을 이용해 insert시 시간순서로 정렬된 것을 사용합니다.

이 테이블의 특성상 board가 새롭게 증가할 때마다 새롭게 최대 3개의 insert가 발생하고 새롭게 생긴 board에 맞춰 전체 상품에서 유사도를 다시 계산해서 board를 바꿔주는 update가 발생합니다.

그래서 복합키로 PK를 구성했을 때 성능상의 문제가 정말 없는지 다시 확인 부탁드립니다.

2번의 경우 정확한 자릿수 기준을 물어보고 지정하는 게 좋아보입니다 (17,16)이면 소수점 제외하고는 1의 자리수까지만 사용이 가능한데 그렇게 설정해도 괜찮은지 확인이 필요합니다.

3번의 경우 저는 이렇게 생각을 했습니다.

varchar(255)나 사용하는 필드인가? -> 모델 버전이나 어떤 로직의 추천인지를 구분 짓는 것은 많아도 20글자가 아닌가?
varchar(255)로 꼭 사용해야 하는가? varchar(20)이나 char(20) 등으로 제한하면 공간이나 성능에서 이점이 있지 않을까?

관련 블로그와 chat gpt 답변 같이 첨부합니다.

gpt 답변

MySQL에서 `VARCHAR(255)`와 `VARCHAR(20)`을 사용할 때의 차이를 **저장 방식**, **저장 공간**, **성능** 세 가지 측면에서 살펴보겠습니다. ### 1. 저장 방식 차이 - **VARCHAR**는 가변 길이의 문자열 타입이므로, 실제 데이터의 길이와 동일한 크기만큼의 공간이 사용됩니다. 즉, "Hello"라는 문자열을 저장한다면 `VARCHAR(20)`이든 `VARCHAR(255)`이든 **5자에 해당하는 공간만 차지**합니다. - MySQL에서는 `VARCHAR` 타입의 경우 각 문자열의 길이 정보도 저장되는데, 이 길이 정보를 기록하기 위해 **추가 메타데이터 공간**이 필요합니다. - `VARCHAR`의 최대 길이가 **255자 이하일 때는 1바이트**로 길이를 저장하고, - **256자 이상일 때는 2바이트**가 추가로 사용됩니다. ### 2. 저장 공간 차이 - `VARCHAR(20)`과 `VARCHAR(255)`는 데이터 길이에 따라 저장 공간이 다릅니다. 그러나 최대 길이에 따른 **메타데이터 차이**가 존재합니다. - **VARCHAR(20)**의 경우 20자 이하의 문자열을 저장하기 때문에, MySQL이 이 데이터를 저장할 때 최대 1바이트의 길이 정보를 기록합니다. - **VARCHAR(255)**는 최대 255자까지 저장할 수 있지만, 길이 정보로 1바이트만 필요합니다. 예를 들어: - "Hello"라는 단어를 `VARCHAR(20)`과 `VARCHAR(255)` 모두에 저장하면 데이터 자체는 5바이트를 차지하고, 여기에 1바이트의 길이 정보가 추가되므로 총 6바이트를 사용합니다. - 두 컬럼 간의 저장 공간 차이는 실제 데이터가 20자를 넘지 않는 한 미미합니다. ### 3. 성능 차이 MySQL에서는 `VARCHAR(255)`와 `VARCHAR(20)`의 **성능 차이가 크지 않지만**, 몇 가지 미세한 차이가 발생할 수 있습니다. #### 인덱스 성능 - MySQL의 **인덱스 크기**는 컬럼의 최대 길이에 따라 달라집니다. 인덱스를 생성하는 경우, `VARCHAR(255)`는 `VARCHAR(20)`보다 **더 많은 공간**을 차지할 수 있습니다. 이는 인덱스 검색 시 **메모리 사용량**에 영향을 미칠 수 있습니다. - 특히 InnoDB 스토리지 엔진에서는 인덱스가 클수록 성능 저하가 발생할 가능성이 높으므로, 가능한 짧은 길이의 `VARCHAR`를 사용하는 것이 좋습니다. #### 캐싱 및 메모리 효율성 - **MySQL의 캐시 효율성** 면에서도 `VARCHAR(20)`이 `VARCHAR(255)`보다 다소 유리할 수 있습니다. 캐싱을 고려할 때, 작은 데이터를 더 효율적으로 저장하고 관리할 수 있기 때문에 메모리 사용 측면에서 약간의 이점이 생길 수 있습니다. - 그러나 일반적인 쿼리에서는 이 차이가 눈에 띄지 않을 정도로 미미합니다. ### 4. 실제 활용 시 고려 사항 1. **20자 이하로 데이터가 제한될 경우**: `VARCHAR(20)`을 사용하는 것이 적절합니다. 이는 데이터 저장 공간을 절약하고, 데이터 일관성도 유지할 수 있어 성능 면에서 최적화할 수 있습니다. 2. **확장 가능성을 고려해야 하는 경우**: 20자를 넘는 데이터가 추가될 가능성이 있다면 `VARCHAR(255)`로 설정하여 유연성을 확보하는 것도 방법입니다. 3. **인덱스 컬럼으로 사용할 경우**: 가능한 한 짧은 길이를 지정하는 것이 성능에 유리합니다. 인덱스를 적용해야 한다면 `VARCHAR(20)`이 성능 상 더 나은 선택이 될 수 있습니다. ### 요약 | 항목 | VARCHAR(20) | VARCHAR(255) | |-------------------|-------------------------------------|----------------------------------------| | **최대 저장 길이** | 20자 | 255자 | | **메타데이터 크기** | 1바이트 | 1바이트 | | **인덱스 크기** | 작음 | 큼 | | **일반적인 성능** | 데이터 일관성이 중요할 때 유리 | 데이터 확장이 필요할 때 유리 | MySQL 기준에서 보면 **데이터의 일관성**이 중요한 경우 `VARCHAR(20)`, **데이터 확장성**이 필요한 경우 `VARCHAR(255)`을 선택하는 것이 적합하며, 인덱스를 생성할 때는 `VARCHAR(20)`이 다소 유리합니다.

당근 테크 블로그(RealMySQL 저자)

전체적으로 말씀을 드리자면 어떤 선택을 내릴 때 기존에 내린 선택과 다를 때 이 방법이 여러 측면에서 유의미한가? 를 한번 더 고민해주시면 좋을 것 같다는 생각이 들었습니다. 저희 RealMySQL을 스터디하는 만큼 개발을 할 때도 데이터 타입과 PK 설정, 인덱스에 대한 고민도 잘 묻어나면 좋을 것 같다는 생각입니다.

추가로 현재 클러스터링 인덱스로 인해 query_item 정도는 인덱스를 탈 것 같아서 지금 정도도 괜찮지 않을까? 하는 인덱스 관련 의견입니다. 그리고 pull request 쪽에도 적었지만 rank 단독으로 인덱싱을 탈 경우에는 인덱스가 안걸릴 것 같은 구조인 것 같고 복합키 사용에 대해서도 성능상의 이점을 강조하신 것만큼 느껴지지 않아 설명이 더 필요할 것 같습니다.

1. Auto Increment와 복합키 선택에 대한 고려 사항 현재 이 테이블의 데이터 삽입 및 업데이트가 빈번히 일어나는 것이 아니라, 일괄적으로 수행된다는 점을 고려할 때, Auto Increment보다는 복합키를 선택하는 것이 적절한 것 같습니다. 말씀하신 바와 같이, UUID를 사용할 경우 공간을 많이 차지할 뿐만 아니라, 랜덤 값 생성으로 인해 B-Tree 인덱스가 Leaf 노드의 맨 끝이 아닌 중간에 생성되기 때문에 성능 저하의 원인이 될 수 있습니다.

또한, 이 테이블에서 특정 query_item을 조회하거나 rank를 기준으로 정렬해야 한다는 점에서, 복합키 사용을 통해 조회 효율성을 높일 수 있습니다.

결과적으로, 삽입과 업데이트 빈도가 낮고 특정 조건과 정렬을 기반으로 조회가 필요하다는 상황을 감안한다면 현재 설계는 적절하다고 판단했습니다!

3. VARCHAR(255) 사용에 대한 판단 근거 과거에는 char와 varchar의 성능 차이가 중요하게 고려되었으나, 최근 소프트웨어 성능이 향상됨에 따라 이 차이가 미미하다는 의견이 있습니다. 확장성을 고려해 varchar(255)를 사용했으며, 길이가 255를 넘지 않는다면 저장 공간 낭비도 줄일 수 있습니다.

추가로, varchar 컬럼에 인덱스를 거는 경우에 발생할 수 있는 잠재적인 문제를 언급해 주셨는데, 이 또한 설계에 있어 중요한 고려사항이라 생각됩니다. 다만, 이 테이블에서는 enum 타입의 두 컬럼을 인덱스로 걸 일이 없다고 판단해 이 문제 역시 크게 우려할 필요는 없어 보입니다. 만약 varchar의 길이가 255를 초과해야 하는 상황이 발생한다면, 다른 저장 방식을 고려하는 것이 바람직하다는 의견에 전적으로 동의합니다

말씀해주신 부분 확인하고 있는데 복합키로 생성한 인덱스와 query_item만을 인덱스로 사용한 결과(explain)이 동일하게 나타나고 있습니다 좀 더 확인해봐야겠지만 rank의 범위로 인한 문제일 가능성이 큽니다. 작은 범위이기 때문에 MySQL 효율적이라고 판단하지 않아 타지 않는 것으로 보입니다(1~3까지의 rank만이 존재) 그리고 업데이트 될 일이 거의 없다고 말씀해주셨는데 이건 확실하진 않습니다

판매자 유입이 되어 판매자가 게시글을 올리는 경우 그 때마다 유사한 상품의 기준이 변경될 수 있음
기획상 나오지 않은 부분이긴 하지만 업데이트 주기가 짧다라고 단정짓기는 어려움
업데이트 시 rank 가 변경되는 요소이기 때문에 이 부분에 대한 판단이 필요
위에서 rank가 인덱스를 타고 있지 않다면 rank를 포함한 인덱스를 굳이 만들어야 하는가에 대한 의문

제가 uuid를 pk로 만들 경우에 대한 문제점을 복합키를 사용할 때 동일하게 나타날 수 있다고 언급은 했는데 varchar 컬럼에 인덱스를 거는 경우에 발생하는 문제점을 언급을 했었나요? enum과 관련해서? 다시 읽어봐도 못찾겠어서 질문드립니다.

추가로 varchar(255)의 사용 같은 경우 미미하다라고 판단을 할 수는 있지만 바로 디폴트 값을 쓰지 말고 한번 생각해볼 필요는 있다를 더 강조하고 싶습니다. 어떤 값이나 선택을 했을 때 대안을 생각을 해보고 결정하는 것과 바로 결정하는 것과는 차이가 있다고 생각해서 다시 강조드립니다. 255글자까지 안쓰는데 20글자로 제한하는 게 더 효율적이지 않나? 라는 생각을 해보고 실제로 돌려보는 것과 돌려보지 않는 것에는 큰 차이가 있다고 생각합니다.

1. id를 AutoIncrease 했을 경우

id가 PK로 잡히며, 클러스터링 인덱스를 생성합니다

2. query_item를 PK로 설정하는 경우

고유하지 않아 PK 설정이 불가능합니다

3. rank는 서버 단에서 순위를 결정하지 않습니다

제가 볼 수 있는 히스토리는 피그마, 슬랙이어서 이해한 내용이 다를 수 있습니다.
업데이트는 일어나지 않으며 delete 후 insert됩니다
임시로 받은 csv 데이터에 rank도 함께 적재해서 전달을 주고 있습니다
- 추후 어떻게 진행되는지 더 공유가 필요함

4. 판매자 유입이 되어 판매자가 게시글을 올리는 경우

삽입 쪽은 적게, 조회 쪽은 빈번하게 일어날 것으로 예상하여 조회 기준으로 작성하긴 했습니다. 확실히 이건 고려 대상인 것 같습니다.

5. 작은 범위이기 때문에 MySQL 효율적이라고 판단

id에 만 PK를 거는 경우 rank에 인덱스가 없기 때문에 filtersort가 발생하는 것입니다.
실제 rank를 복합키로 잡았을 때와 아닐 때 explanin을 찍어본 결과 filtersort가 발생하는 것을 확인했습니다.
- 복합키를 잡지 않고 rank를 따로 인덱스 거는 경우도 filtersort를 방지할 수 있습니다. 다만, query_item을 필터로 사용 시 FullScan이 발생할 수 있습니다.

6. 게시글 업로드 시 3개의 추천 데이터 insert

이러한 경우 id를 PK로 사용한다면, query_item의 정렬은 이뤄지지 않으며, where로 query_item을 equle 할 경우 해당 값이 테이블 첫번째, 그리고 마지막에 있다면 처음부터 쭉 훑어보아. 풀 스캔이 발생할 수 있습니다.

7. varchar 컬럼에 인덱스를 거는 경우에 발생하는 문제점을 언급을 했었나요?

중원님께서 올려주신 GPT 답변에 대해 답을 드렸습니다.
GPT 대답과 저희 상황을 대입하자면 varchar(255)로 문제가 될 수 있는 것은 이 값을 인덱스로 사용했을 때의 경우라고 생각합니다.

8. varchar(255)의 사용 같은 경우 미미하다라고 판단을 할 수는 있지만 바로 디폴트 값을 쓰지 말고 한번 생각해볼 필요는 있다를 더 강조하고 싶습니다.

저도 아무 이유없이 255를 사용한 것은 아닙니다.
varchar를 사용할 때 "고정적인 데이터가 insert 되는지", varchar의 데이터 크기를 지정할 땐, 데이터 길이의 유효 범위가 요구사항으로 존재하는지 를 고려해서 사용합니다.
varchar(255)를 사용한 이유는 "데이터 팀에서 작업 후 전달하기에 악의적으로 255개의 길이를 가진 텍스트를 집어넣는 경우가 없음", "255가 넘어가지 않는 이상 20과 255의 공간에 대한 용량 차이가 없음", "확장성 고려"
- varchar는 글자 길이를 따로 가지고 있어 255로 설정해도 글자수에 따라 저장 용량이 달라짐. 즉, varchar(20), varchar(255)의 저장 공간의 차이는 없음 혹은 미미함
위 과정들을 고려해서 varchar(255)를 선정하게 됐습니다.
만약 저희가 따로 정한 규칙, 원칙이 있다면 크기에 맞게 설정했을 것입니다.

이러한 문제 때문에 이렇게 사용하는 게 좋겠다 라고 전달을 드리고 있지만 글이다 보니 감정이 제대로 전달되는 것 같지 않아 남깁니다! 혹시 감정 상하셨다면 그렇게 느끼지 않으셨으면 좋겠습니다!

이전에 제가 댓글에서 작성한 부분에서는 직접 예찬님이 말씀하신대로 인덱스를 타는지 보며 성능 이점이 있는지 저도 체크하고 전달하는 과정에서 설명해주신 부분과 다른 부분이 있어서 확인을 부탁드렸던 거에요!

이전 댓글에서 rank도 index를 타는 부분

또한, 이 테이블에서 특정 query_item을 조회하거나 rank를 기준으로 정렬해야 한다는 점에서, 복합키 사용을 통해 조회 효율성을 높일 수 있습니다.

복합키로 설정했을 때 query_item은 타지만 rank 쪽은 타지 않는 것으로 보여 다시 확인을 요청드린 부분입니다. 실제로 pk auto_increment 생성하고 query_item 에만 index를 생성했을 때의 결과와 같아(explain을 사용해서 봤을 때) 실제로 위 방법이 효율적이더라도 예찬님이 적어주신 부분과 다른 부분이 있어 확인이 필요하다는 것을 언급드린 부분이었습니다.

그리고 실제로 query_item에만 index를 걸면 되는 부분이라면 복합키를 걸어서 삽입 시 손해를 보는 부분과 pk를 auto_increment로 만들고 query_item에만 index를 거는 방식과 다시 비교해 생각해볼 필요는 있다고 생각해서 첫 번째로 이전 댓글에 언급했던 건데 작성 중에 태스크가 들어와서 조금 급하게 마무리를 짓느라 제대로 전달이 안된 부분이 있는 것 같아 보입니다.

varchar 부분에 대해서는 제가 적절한 예시를 전달하지 못한 것 같아 조금 더 찾아보았습니다. DB를 설계하는 입장에서 가능하면 효율을 보기 위해, 그리고 DB 삽입의 규칙을 서비스에 맞게 정의하기 위해 일반적으로 varchar(255)를 사용하기 보단 서비스에 맞게 정의된 글자수를 권장하고 있더라고요.

MySQL varchar(255)를 사용하는 이유

위에서 제가 답변한 부분에서 예찬님의 전체 스토리를 듣지 못하고 예찬님이 처음 varchar에 대해 언급해주신 답변을 봤을 때 고민의 흔적이 마지막에 적어주신 것만큼 보이지 않아 세게 강조하게 된 것 같습니다.(이 부분은 전달 방식을 좀 더 개선해볼게요 죄송합니당🙏)

말씀 감사합니다:)

저도 제 생각으로만 전달 드리다보니 합의점을 찾기가 어려웠던 것 같아요 그래서 테스트를 진행해보았습니다! (다음부턴 테스트해서 같이 합의점을 찾아보는 것도 좋을 것 같아요)

https://github.com/eco-dessert-platform/backend/issues/305 저희의 논점이었던 삽입, 조회 기준으로 작성했습니다

아침부터 계속 책읽고 작업하다보니 집중력이 많이 떨어져서 MySQL varchar(255)를 사용하는 이유 이거는 내일 읽고 판단해보겠습니다

감사합니다~

eco-dessert-platform / backend

✅ 상품 추천 기능 구현 #302

Issue: ✅ Feature

Description

API 엔드포인트

테이블 정보

To do

ETC