[DB] 정렬 필드 값 재정렬

하드웨어를 사랑하는 개발자·2023년 4월 16일
DatabaseMariaDBdbmysql
1

UI에서 어떠한 레코드의 정렬 순서를 변경할 때 여러가지 방법이 있다.

지금 작성하는 글은 정렬 필드를 포함하고 있는 스키마를 활용하여 개발한 흔적이다.

  • STORE_GOODS_PRODUCT_MENU - 상품 메뉴 테이블
PKFKPARENT_KEYMENU_NMDISPLAY_ORDER
111주류1
-----
211소주1
311맥주2
-----
412참이슬1
512진로2
612새로3
-----
713카스1
813하이트2
913테라3
...............
  • 상품 명 (오더번호) 
주류 (1)
	ㄴ 소주 (1)
    	ㄴ 참이슬 (1)
        ㄴ 진로 (2)
        ㄴ 새로 (3)
        ㄴ ... (4)
        ㄴ ... (5)
    ㄴ 맥주 (2)
    	ㄴ 카스 (1)
        ㄴ 하이트 (2)
        ㄴ 테라 (3)
음료(2)
	ㄴ ...
    	ㄴ ...
  • SYSTEM_NAVIGATION_MENU - 시스템 카테고리 메뉴 테이블
PKFKPARENT_KEYMENU_NMDISPLAY_ORDER
111주문1
211주문 현황1
311주문 관리2
-----
414예약2
514예약 현황1
614예약 관리2
...............
  • 네비게이션 메뉴 (오더번호)
주문 (1)
	ㄴ 주문 현황 (1)
    ㄴ 주문 관리 (2)
예약 (2)
	ㄴ 예약 현황 (1)
    ㄴ 예약 관리 (2)

뭐 이런식으로 계층적 구조를 담은 스키마가 있다고 치자

DISPLAY_ORDER라는 필드를 통해
ORDER BY를 하고 있다.

"요즘 새로가 잘 팔리네? 새로를 맨 위로 올려야겠다."

사용자가 노출 순서를 변경하는 기능을 풀어낼 방법은 다양하다.

쉬운 방법 순서로

  1. 첫번째 방법
    해당하는 레코드들을 전체 삭제하고 벌크 인서트 한다.
    참이슬 -> 새로
    진로 -> 진로
    새로 -> 참이슬

장점 : 클라이언트가 순서 변경을 위한 핸들링하지 않아도 되고 서버 입장에서도 그냥 밀고 새로 밀어넣는거라 쉬움

단점 : 한번 생각해보세요.

  1. 두번째 방법
    클라이언트가 순서 변경 정보를 핸들링하여 서버에 요청
    참이슬(정렬 번호 : 1) -> 참이슬(정렬 번호 : 3)
    진로(정렬 번호 : 2) -> 진로(정렬 번호 : 2)
    새로(정렬 번호 : 3) -> 새로(정렬 번호 : 1)

장점 : 정렬 순서를 클라이언트에서 계산하기 때문에 서버입장에서 갱신만 하면되서 편함

단점 : 한번 생각해보세요.

  1. 세번째 방법
    클라이언트에서 레코드의 목록를 받아서 갱신

장점 : 두번째 방법과 다른건 정렬번호를 신경 쓸 필요 없다. 그냥 리스트의 인덱스가 즉 정렬번호다
단점 : 한번 생각해보세요.

  1. 네번째 방법
    변경한 레코드를 기준으로 재정렬
    새로(정렬 번호 : 3) -> 새로(정렬 번호 : 1)
    새로의 기존 정렬번호 3, 바꿀 정렬번호 1 를 가지고
    1~3 Range에 해당하는 목록을 가져와서 비교해서 갱신한다

장점 : 클라이언트 입장에서 재정렬의 부담이 줄어든다.

단점 : 중간에 삭제되거나 갱신되었을때 오더 번호가 틀어질 수 있고 서버에서 계산해야하는 로직이 다소 복잡하다.

제시한 방법은 서버 개발자 입장에서 난이도가 쉬운 순서로 적어봤다.

더 다양한 방법과 고려할 사항이 많은데 쓰다보니 양이 많아졌다..

첫번째 방법의 단점은 constraint와 cascade를 확인해야 한다.

주문과 예약 테이블에서 현황과 내역 관리를 하는데 정렬 번호를 재정렬하기 위해 첫번째 방법을 사용하면 어떻게 될까?

애초에 제약조건을 설계하기 때문에 그 작업은 불가능하다.
다소 복잡하지 않은 도메인을 가진 토이 프로젝트나, 포트폴리오, 과제 등이라면 첫번째 방법도 쉬운 개발이 될 수 있다.

두번째 방법의 단점은 프론트엔드 개발자가 싫어한다.

클라이언트에서는 빠른 렌더링을 위해 chunk size를 제어하고 lazy loading과 thumbhash 등 어떻게든 리소스를 줄이려고 노력하는데,
이 방법은 클라이언트에서 재정렬을 하기 위한 리소스가 들어가고 무엇보다 클라이언트의 책임이 높아진다.
하지만 수정이 메인 비즈니스도 아니고 요즘 컴퓨팅 파워로는 충분히 수용가능한 리소스라 적용해볼만 하다.
무엇보다 첫번째 방법과는 다르게 목록 전체를 업데이트 치는 것 이기 때문에 constraint와 cascade가 걸린 스키마에도 적용할 수 있고, 개발 난이도가 어렵지 않다.

세번째 방법은 두번째 단점을 해소했지만 여전히 전체 목록의 PK와 FK를 보내야한다.

UPDATE STORE_GOODS_PRODUCT_MENU T, (SELECT PK, (@rank := @rank + 1) AS SORT_ORDER
                                    FROM STORE_GOODS_PRODUCT_MENU T1,
                                         (SELECT @rank := 0) AS T2
                                    WHERE T1.PK =         #{pk}
                                      AND T1.FK =         #{fk} 
                                      AND T1.PARENT_KEY = #{parentKey}
                                    ORDER BY FIELD(PK, 
                                     <foreach collection="pks" item="pk" separator=",">
            							#{pk}
  							         </foreach>)
                                    )) B
SET T.DISPLAY_ORDER = B.SORT_ORDER
WHERE T.PK =         #{pk}
  AND T.FK =         #{fk} 
  AND T.PARENT_KEY = #{parentKey}
  AND T.PK = B.PK;

@rank를 활용해서 들어온 PK의 목록을 정렬한다.
FIELD(PK가 들어온 순서대로) 컬렉팅하고 RANK를 생성한다.

네번째 방법은 귀찮다.. 복잡하다...

애플리케이션에서 트랜잭션을 걸고 조회된 목록을 갖고 지지고 볶아서 업데이트 반영하는 코드는 생각보다 귀찮다. 그래서 DB SQL 레벨에서 해결하고 싶었다.

!!! 작성은 실제 운영 코드가 아닌 테스트 코드를 수기로 작성한 것이기 때문에 문법에 문제가 있을 수도 있습니다.

!!! Mybatis mapper와 Mariadb sql을 기반으로 작성된 코드입니다.

CASE 1 : 첫번째 순위가 세번째로 간다. (선순위 -> 후순위)

-- 재정렬
UPDATE STORE_GOODS_PRODUCT_MENU
SET DISPLAY_ORDER = IF(DISPLAY_ORDER > #{changeOrder}, DISPLAY_ORDER + 1, DISPLAY_ORDER - 1)
WHERE
    FK = #{fk}
    AND PARENT_KEY = #{parentKey}
    AND DISPLAY_ORDER BETWEEN #{currentOrder} + 1 AND #{changeOrder}

-- 재정렬 후 요청한 레코드 정렬 갱신
UPDATE STORE_GOODS_PRODUCT_MENU    
SET DISPLAY_ORDER = #{changeOrder}
WHERE
    PK = #{pk}
    AND PARENT_KEY = #{parentKey};

선순위에서 후순위로 갈 때는 작거나 같아야 -1을 해줘야한다.

1에서 4로 간다면

UPDATE STORE_GOODS_PRODUCT_MENU
SET DISPLAY_ORDER = IF(DISPLAY_ORDER > 4, DISPLAY_ORDER + 1, DISPLAY_ORDER - 1)
WHERE
    FK = #{fk}
    AND PARENT_KEY = #{parentKey}
    AND DISPLAY_ORDER BETWEEN 2 AND 4
    
    
UPDATE STORE_GOODS_PRODUCT_MENU    
SET DISPLAY_ORDER = #{changeOrder}
WHERE
    PK = #{pk}
    AND PARENT_KEY = #{parentKey};

WHERE 절에 걸린 ORDER (currentOrder+1) 2 ~ 4 사이의 레코드를 업데이트 칠 것이다.
2 <= 4 = -1 => 1
3 <= 4 = -1 => 2
4 <= 4 = -1 => 3

currentOrder가 1이었던 레코드는 4로 업데이트하면 된다.

CASE 2 : 네번째 순위가 두번째로 간다. (후순위 -> 선순위)

UPDATE STORE_GOODS_PRODUCT_MENU
SET DISPLAY_ORDER = IF(DISPLAY_ORDER >= #{changeOrder}, DISPLAY_ORDER + 1, DISPLAY_ORDER - 1)
WHERE
    FK = #{fk}
    AND PARENT_KEY = #{parentKey}
    AND DISPLAY_ORDER BETWEEN #{changeOrder} - 1 AND #{currentOrder}

후순위에서 선순위로 갈 때는
크거나 같아야 +1을 해줘야한다.
5에서 2로 간다면

2 < 5 = +1 => 3
3 < 5 = +1 => 4
4 < 5 = +1 => 5

currentOrder가 5였던 레코드는 2로 업데이트

범위를 지정하기 위해 currentOrder를 알아야한다.

클라이언트에서는 현재 currentOrder를 보내줘야하나?
보내줄 수 도 있지, 근데 안보내주는 방법으로 하고 싶은데...

{
  "pk" : 4,
  "fk" : 1,
  "parentKey" : 2,
  "changeOrder" : 3
}

DB에서 조회 해서 currentOrder를 굳이 갖고와야하나?

현재 currentOrder를 가져오는 subquery와 IF 함수의 향연이 펼쳐졌다.

예제코드로 쓰기 힘에 겨워 생략한다.

핵심은

앞에서 뒤로 갈 때는 currentOrder가 changeOrder보다 '<', 즉 작을 때 -1를 한다.

뒤에서 앞으로 갈 때는 currentOrder가 changeOrder보다 '<=', 즉 작거나 같을 때 -1를 한다.

이 방식도 증분에 대한 처리가 되어있지 않다.

중간에 이빨이 빠진다면 인덱스번호도 빵꾸가 나는 것이다.

세번째 방법과 네번째 방법 둘 다 완벽하지 않은 쿼리인 셈이다.. ㅠㅠ

조금 더 갈무리 한다면 썩 괜찮은 쿼리가 나올 것 같다고 붙잡다가 6시간 넘어서야 정신차렸다..

내가 지금 해야할일이 산더미인데 정렬에 6시간을 태워 ㅠㅠ?

세번째 방법은 상품 이미지와 같이 몇개 안되는 목록을 수정할 때 사용중이고

네번째 방법은 맨처음으로 가져오기 맨 뒤로 보내기할 때와 드래그 앤 드롭 할 때 사용하고 있다.

마치며

엄청 간단하게도 풀어볼 수 있고 많은 것들을 고려해서 퀄리티를 높힐 수도 있었습니다.

어떤가요..? 겨우 정렬하는거 하나도 방법이 엄청 다양하고 알맞는 기법들을 활용하는 엔지니어링이 필요할 것 같지 않은가요?

뭐라고? 이게 6시간짜리 SQL이라고? ㄹㅇㅋㅋ

애플리케이션에서 처리하다가
"SQL에서 하면 더 편하겠는데?"
라는 오만한 생각을 했다가 조금만 더 보완해보자 하다가
6시간 잡아먹은 SQL이라 분해서 박제합니다..

나중엔 이럴때도 있었지 하며 웃고싶네요.

성능이 잘나오는 쿼리인지는 모르겠습니다.. SQL 넘 어려워요..
더 좋은 정렬 방법이 있다면 알려주세요

profile
하드웨어를 사랑하는 개발자
🐶같이 코딩
이전 포스트

캡슐화

다음 포스트

[JAVA] 달력 만들기

0개의 댓글