데이터베이스에서 행 순서 필드를 보다 효율적으로 유지 관리할 수 있는 방법

데이터베이스에서 행 순서 필드를 보다 효율적으로 유지 관리할 수 있는 방법

MariaDB(think MySQL) 데이터베이스가 있으며 행에는position필드. 이 위치는 변경될 수 있지만 항상 순차적이어야 하며 1부터 시작해야 합니다.

간이 테이블 스키마:

CREATE TABLE `ordered_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner` int(11) NOT NULL,
  `data` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `position` int(11) NOT NULL
  PRIMARY KEY (`id`)
) 

예를 들어, 사용자는 ID 10의 행을 위치 #1로 이동시킬 수 있습니다.이것은 물론 모든 후속 아이템을 재주문해야 합니다.그렇지 않으면 다음 아이템에 중복이 생깁니다.position필드입니다. 4개 미만의 쿼리에서 이 작업을 수행할 방법을 찾을 수 없습니다.

현재 사용하고 있는 솔루션은 다음과 같습니다.단순히 정리할 수 있지만, 보다 우아한 재주문 방법이 있다고 생각하지 않을 수 없습니다.이 예에서는 ID32의 행을 사용자 20에 의해 위치 3으로 이동합니다.

1. 입수하다currentPosition타고SELECT position FROM ordered_data WHERE id = 32.
2. 곧 만들어질 공백을 메우려면UPDATE ordered_data SET position = position - 1 WHERE position > currentPosition AND owner = 20.
3. 행의 새 위치(3)를 위한 공간을 만듭니다.UPDATE ordered_data SET position = position + 1 WHERE position >= 3 AND owner = 20.
4. 간격에 맞게 행 위치 업데이트UPDATE ordered_data SET position = 3 WHERE id = 32.

어떤 제안도 환영합니다.처음 두 문장을 하위 선택과 결합하면 MySQL이 업데이트 및 선택에 동일한 테이블을 사용하는 것에 대해 불만을 제기하게 됩니다.

소유자당 열 수가 10개를 넘을 가능성은 거의 없습니다.

스텝 순서를 바꾸는 건 생각보다 쉽지 않아요.그러나 한 단계만 더 진행하면 고유한 키를 사용하여 알고리즘이 작동하도록 만들 수 있습니다.(owner, position)스텝 2에서 엔트리에러의 중복을 피하기 위해서, 일시적으로 할당할 수 있습니다.possition = 0이동할 항목으로 이동합니다.완전한 알고리즘은 다음과 같습니다.

set @owner = 20;
set @id = 32;
set @new_pos = 3;

-- 1. get current position
set @old_pos = (select position from ordered_data where id = @id);

-- 1.1 "remove" the item from its old position
update ordered_data set position = 0 where id = @id;

-- 2. close the gap at the old position
update ordered_data
set position = position - 1
where position owner = @owner
  and position > @old_pos
order by position asc -- important for unique key

-- 3. make space at the new position
update ordered_data
set position = position + 1
where position owner = @owner
  and position >= @new_pos
order by position desc -- important for unique key

-- 4. set new position
update ordered_data set position = @new_pos where id = @id;

이 방법(IMHO)은 소규모 그룹에 적합합니다.대용량 데이터 세트의 경우 3단계와 4단계를 최적화하여 한 번에 수행할 수 있습니다.다음의 예를 참조해 주세요.100만 개의 아이템 그룹이 있으며 아이템을 위치 7에서 위치 3으로 이동하려고 합니다.아이템을 '제거'한 후 포지션 8을 1000000으로 업데이트하고 1을 줄여서 갭을 좁힙니다.그런 다음 3의 위치를 999999로 업데이트하고 공간을 확보합니다.이렇게 하면 그룹 전체가 거의 두 번 업데이트되지만, 필요한 것은 포지션 3에서 6으로 증가시키는 것입니다.이를 위해 스텝2와 스텝3을 다음과 같이 대체할 수 있습니다.

if (@new_pos < @old_pos)
    update ordered_data
    set position = position + 1
    where position owner = @owner
      and position between @new_pos and @old_pos
    order by position desc
else if (@new_pos > @old_pos)
    update ordered_data
    set position = position - 1
    where position owner = @owner
      and position between @old_pos and @new_pos
    order by position asc
else
    -- do nothing

주의: 의사 코드입니다.응용 프로그램 사이트에서 올바른 쿼리를 선택해야 합니다.

단일 쿼리에 결합할 수도 있습니다.

    update ordered_data
    set position = position + sign(@new_pos - @old_pos)
    where position owner = @owner
      and position between @new_pos and @old_pos
    order by position * sign(@new_pos - @old_pos) desc

그러나 이 경우 엔진은 GROUP BY 절의 인덱스를 사용할 수 없습니다.

언급URL : https://stackoverflow.com/questions/45472587/more-efficient-way-of-maintaining-row-order-field-in-database