프로호스터 > 블로그 > 관리 > SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"

주기적으로 일련의 키로 관련 데이터를 검색하는 작업이 발생합니다. 필요한 총 레코드 수를 얻을 때까지.

가장 "생생한" 예는 표시하는 것입니다. 20 가장 오래된 문제, 나열 직원 목록에 (예를 들어 같은 부서 내에서). 작업 영역에 대한 간략한 요약이 있는 다양한 관리 "대시보드"의 경우 유사한 주제가 자주 필요합니다.

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"

이 기사에서는 그러한 문제를 해결하는 "순진한" 버전, "더 똑똑하고" 매우 복잡한 알고리즘의 PostgreSQL 구현을 고려할 것입니다. 찾은 데이터의 종료 조건이 있는 SQL의 "루프", 일반 개발 및 기타 유사한 경우에 사용하는 데 모두 유용할 수 있습니다.

에서 테스트 데이터 세트를 가져옵니다. 이전 기사. 정렬된 값이 일치할 때 출력 레코드가 때때로 "점프"하지 않도록, 기본 키를 추가하여 주제 인덱스 확장. 동시에 이것은 즉시 고유성을 부여하고 정렬 순서의 고유성을 보장합니다.

CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;

들리는 대로 기록되느니라

먼저, 수행자의 ID를 전달하여 요청의 가장 간단한 버전을 스케치해 보겠습니다. 입력으로 배열:

SELECT
  *
FROM
  task
WHERE
  owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
  task_date, id
LIMIT 20;

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"
[explain.tensor.ru 참조]

조금 슬프다 - 우리는 20개의 레코드만 주문했고 Index Scan이 우리에게 반환되었다 960 라인, 그런 다음 정렬해야했습니다 ... 그리고 덜 읽으십시오.

언네스트 + ARRAY

도움이 될 첫 번째 고려 사항 - 필요한 경우 총 20개 정렬됨 기록, 읽기에 충분하다 각각에 대해 동일한 순서로 정렬된 20개 이하 열쇠. 좋은, 적합한 인덱스 (owner_id, task_date, id)가 있습니다.

추출 및 "열로 변환"의 동일한 메커니즘을 사용합시다. 통합 테이블 항목에서와 같이 마지막 기사. 또한 함수를 사용하여 배열에 컨벌루션을 적용합니다. ARRAY():

WITH T AS (
  SELECT
    unnest(ARRAY(
      SELECT
        t
      FROM
        task t
      WHERE
        owner_id = unnest
      ORDER BY
        task_date, id
      LIMIT 20 -- ограничиваем тут...
    )) r
  FROM
    unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
  (r).*
FROM
  T
ORDER BY
  (r).task_date, (r).id
LIMIT 20; -- ... и тут - тоже

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"
[explain.tensor.ru 참조]

오, 이미 훨씬 나아졌습니다! 40% 더 빠르고 4.5배 더 적은 데이터 읽어야했다.

CTE를 통한 테이블 레코드 구체화참고하겠습니다 어떤 경우에는 CTE에서 "래핑"하지 않고 하위 쿼리에서 검색한 후 레코드 필드로 즉시 작업하려는 시도는 다음과 같은 결과를 초래할 수 있습니다. "곱셈" InitPlan 이러한 동일한 필드의 수에 비례합니다.

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Result  (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
  Buffers: shared hit=16
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t  (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_1  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 3 (returns $2)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_2  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4"
  InitPlan 4 (returns $3)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_3  (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4

동일한 레코드를 4번 "검색"했습니다. PostgreSQL 11까지는 이 동작이 정기적으로 발생하며 솔루션은 이러한 버전의 옵티마이저에 대한 무조건적인 경계인 CTE에서 "래핑"하는 것입니다.

재귀 누산기

이전 버전에서는 전체적으로 읽었습니다. 200 라인 필요한 20을 위해. 이미 960은 아니지만 더 적습니다. 가능합니까?

필요한 지식을 활용해보자 총 xnumx 기록. 즉, 필요한 양에 도달할 때까지만 데이터 빼기를 반복합니다.

1단계: 목록 시작

분명히 20개 항목의 "대상" 목록은 owner_id 키 중 하나에 대한 "첫 번째" 항목으로 시작해야 합니다. 그러므로 우리는 먼저 그러한 각 키에 대해 "최초" 목록에 넣고 원하는 순서(task_date, id)로 정렬합니다.

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"

2단계: "다음" 레코드 찾기

이제 목록에서 첫 번째 항목을 가져오고 시작하면 색인을 더 아래로 "단계" owner_id-key를 저장하면 발견된 모든 레코드는 결과 선택의 다음 레코드일 뿐입니다. 물론, 만 적용된 키를 교차할 때까지 목록의 두 번째 항목입니다.

두 번째 항목을 "교차"한 것으로 밝혀지면 마지막 읽기 항목이 첫 번째 항목 대신 목록에 추가되어야 합니다. (동일한 owner_id 사용), 그 후에 목록이 다시 정렬됩니다.

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"

즉, 우리는 항상 목록에 각 키에 대해 하나 이상의 항목이 없다는 것을 얻습니다(항목이 끝났고 "교차"하지 않은 경우 첫 번째 항목은 단순히 목록에서 사라지고 아무 것도 추가되지 않습니다) ), 그리고 그들은 항상 정렬 애플리케이션 키(task_date, id)의 오름차순으로.

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"

3단계: 레코드 필터링 및 확장

재귀 선택의 행 부분에서 일부 레코드 rv 중복 - 먼저 "목록의 두 번째 항목의 경계를 넘어"와 같은 것을 찾은 다음 목록에서 첫 번째로 대체합니다. 따라서 첫 번째 항목은 필터링해야 합니다.

끔찍한 최종 쿼리

WITH RECURSIVE T AS (
  -- #1 : заносим в список "первые" записи по каждому из ключей набора
  WITH wrap AS ( -- "материализуем" record'ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan
    WITH T AS (
      SELECT
        (
          SELECT
            r
          FROM
            task r
          WHERE
            owner_id = unnest
          ORDER BY
            task_date, id
          LIMIT 1
        ) r
      FROM
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    SELECT
      array_agg(r ORDER BY (r).task_date, (r).id) list -- сортируем список в нужном порядке
    FROM
      T
  )
  SELECT
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  FROM
    wrap
UNION ALL
  -- #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го
  SELECT
    CASE
      -- если ничего не найдено для ключа 1-й записи
      WHEN X._r IS NOT DISTINCT FROM NULL THEN
        T.list[2:] -- убираем ее из списка
      -- если мы НЕ пересекли прикладной ключ 2-й записи
      WHEN X.not_cross THEN
        T.list -- просто протягиваем тот же список без модификаций
      -- если в списке уже нет 2-й записи
      WHEN T.list[2] IS NULL THEN
        -- просто возвращаем пустой список
        '{}'
      -- пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных
      ELSE (
        SELECT
          coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
        FROM
          unnest(T.list[3:] || X._r) r
      )
    END
  , X._r
  , X.not_cross
  , T.size + X.not_cross::integer
  FROM
    T
  , LATERAL(
      WITH wrap AS ( -- "материализуем" record
        SELECT
          CASE
            -- если все-таки "перешагнули" через 2-ю запись
            WHEN NOT T.not_cross
              -- то нужная запись - первая из спписка
              THEN T.list[1]
            ELSE ( -- если не пересекли, то ключ остался как в предыдущей записи - отталкиваемся от нее
              SELECT
                _r
              FROM
                task _r
              WHERE
                owner_id = (rv).owner_id AND
                (task_date, id) > ((rv).task_date, (rv).id)
              ORDER BY
                task_date, id
              LIMIT 1
            )
          END _r
      )
      SELECT
        _r
      , CASE
          -- если 2-й записи уже нет в списке, но мы хоть что-то нашли
          WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
            TRUE
          ELSE -- ничего не нашли или "перешагнули"
            coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
        END not_cross
      FROM
        wrap
    ) X
  WHERE
    T.size < 20 AND -- ограничиваем тут количество
    T.list IS DISTINCT FROM '{}' -- или пока список не кончился
)
-- #3 : "разворачиваем" записи - порядок гарантирован по построению
SELECT
  (rv).*
FROM
  T
WHERE
  not_cross; -- берем только "непересекающие" записи

SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향"
[explain.tensor.ru 참조]

따라서 우리는 50% 실행 시간 동안 20% 데이터 읽기 거래. 즉, 읽기가 길 수 있다고 믿을 만한 이유가 있는 경우(예를 들어, 데이터가 캐시에 없는 경우가 많고 이를 위해 디스크로 이동해야 하는 경우) 이러한 방식으로 읽기를 줄이는 데 의존할 수 있습니다.

어쨌든 실행 시간은 "순진한"첫 번째 옵션보다 더 나은 것으로 나타났습니다. 그러나 이 3가지 옵션 중 어느 것을 사용할지는 귀하에게 달려 있습니다.

출처 : habr.com

코멘트를 추가