데이터베이스 - 순서(정렬) 컬럼 두는 전략 비교

순서 컬럼?

일반적으로는 정렬을 id로 하지만, 특정 컬럼을 둬야 하는 경우가 생깁니다.

 

가령, 순서 수정이나 순서 변경 기능을 제공하는 백엔드에서 그렇죠. pk를 바꾸려면, 관련 테이블과 로그를 전부 수정해야 하기 때문이에요.

 

이 때는 순서 컬럼을 어떻게 두면 좋을까요?

 

방법 소개

stackoverflow를 통해, 존재하는 다양한 방법들을 살폈는데요. 대표적으로 4가지 방법이 있습니다.

 

1. 소수를 이용한 방법

 

2. bigint를 사용해서 bit hole을 두는 방법

 

3. next_id를 저장하는 방법

 

4. 기본적인 int를 저장하는 방법

 

 

비교

1. 소수를 이용한 방법의 경우

• 소수를 이용해서, 앞에 올 컬럼의 순서보다 0.01 정도씩 높여서 이동시키는 방법
• 적절하지만, 동작을 보장하지 않습니다.
• float나 double은 정밀도에서 부족해서, 동일한 동작을 반복하다보면 오차가 생길 수 있습니다.

 

2. bigint를 사용해서 적절하게 간격을 두는 방법의 경우

• 첫 레코드는 bigint 길이의 가운데에 삽입 -> order = round(max_bigint / 2)
• 앞쪽에 삽입 시 가장 첫 레코드의 절반 부분에 삽입 -> order = round(min(order) / 2)
• 뒤쪽에 삽입 시 가장 뒷 레코드의 절반 부분에 삽입 -> order = round(max_bigint - (max(order) / 2))
• 가운데 삽입 시 앞 레코드와 뒤 레코드의 절반 부분에 삽입 -> order = round((before_order+after_order) /2)
• 가장 간단하고 빠르고 정확한 방법이라고 함. float의 부족한 정밀 부분을 해결.
• 64개의 행 이상을 삽입할 시 동작이 정확하지 않을 수 있음.

 

3. next_id를 이용해서 다음 컬럼의 순서를 저장하는 경우

• 수정의 경우 아주 간단함 -> 링크 리스트처럼, 앞 레코드의 order 컬럼만 수정해주면 됨.
• 조회의 경우 복잡함 -> next_id밖에 없으므로, order by를 사용해서 정렬할 수 없음.
• 전체 조회를 한 후, 리스트로 연결해서 정렬을 해서 서버에서 내려줘야 함.

 

4. 기본적인 int를 사용하는 방법

• 레코드를 삽입 시 앞 레코드에서 그냥 1을 더해서 INSERT
• 삽입이나 변경 될 때, 이후 레코드의 순서를 전부 1 더함 -> Update product set order = order+1 where order >= @value changed​
• 가장 일반적인 방법이자, 선호되는 방법
• 조회가 간단하고, 수정도 위 방법들보다는 떨어지겠지만, 생각보다 큰 소모가 발생하지 않음. -> 그렇게 많지 않기 때문

 

 

제 경우 프로젝트를 진행하면서, 드래그앤 드랍으로 순서를 변경하는 요구사항이 있었고,

각 카테고리 별로 자원들의 order를 1부터 관리해서 4번 방법을 적용해서 개발했습니다.

 

참고: https://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table%EF%BB%BF