대규모 PostgreSQL 테이블에서 무작위 행을 효율적으로 선택하는 방법은 무엇입니까?

PostgreSQL 임의 행 선택 방법

기존의 무작위 행 선택 방법은 수백만 또는 수십억 개의 레코드가 포함된 대규모 테이블을 처리할 때 비효율적이고 속도가 느립니다. 두 가지 일반적인 방법은 다음과 같습니다.

• random()을 사용하여 필터링:

  <code class="language-sql">  select * from table where random() < 0.001;</code>

• order by random() 및 limit 사용:

  <code class="language-sql">  select * from table order by random() limit 1000;</code>

그러나 전체 테이블 스캔이나 정렬이 필요하기 때문에 이러한 방법은 행 수가 많은 테이블에는 최선의 선택이 아니며 성능 병목 현상을 일으킬 수 있습니다.

대형 테이블의 최적화 방법

다음 유형의 테이블의 경우 훨씬 더 빠른 다음 최적화 방법을 고려하십시오.

• 작은 또는 중간 간격의 숫자 ID 열(더 빠른 조회를 위해 색인화됨)
• 선택 중 쓰기 작업이 없거나 최소한입니다

쿼리:

<code class="language-sql">WITH params AS (
  SELECT 1 AS min_id, -- 可选：自定义最小ID起始值
       5100000 AS id_span -- 近似ID范围（最大ID - 最小ID + 缓冲）
)
SELECT *
FROM (
  SELECT DISTINCT 1 + trunc(random() * p.id_span)::integer AS id
  FROM params p, generate_series(1, 1100) g
  GROUP BY 1
) r
INNER JOIN big ON r.id = big.id
LIMIT 1000;</code>

작동 방식:

• ID 범위 추정:

  • 정확히 알 수 없는 경우 테이블을 쿼리하여 ID 열의 최소, 최대 및 전체 범위(최대 - 최소)를 추정하세요.

• 임의의 ID 생성:

  • 예상 ID 범위 내에서 다른 난수 세트를 생성합니다.

• 중복 및 중복 제거:

  • 생성된 번호를 그룹화하여 중복된 번호를 제거하면 누락된 행이나 이미 선택한 행을 선택할 가능성이 줄어듭니다.

• 테이블 조인 및 제한:

  • ID 열을 사용하여 실제 테이블과 난수를 결합합니다(인덱싱해야 함). 이 효율적인 조인은 선택한 행에 해당하는 데이터를 검색합니다.
  • 마지막으로 제한을 적용하여 필요한 행 수를 검색합니다.

빠른 이유:

• 최소 인덱스 사용:

  • 쿼리는 ID 열에 대해서만 인덱스 스캔을 수행하며 이는 전체 테이블 스캔이나 정렬 작업보다 훨씬 빠릅니다.

• 최적화된 난수 생성:

  • 생성된 난수는 추정 ID 범위에 걸쳐 분포되어 행이 누락되거나 겹칠 가능성을 최소화합니다.

• 중복 및 중복 제거:

  • 생성된 숫자를 그룹화하면 고유한 행만 선택되므로 중복을 제거하기 위해 추가 필터링이나 결합이 필요하지 않습니다.

기타 옵션:

• 간격 처리를 위한 재귀 CTE:

  • ID 순서에 공백이 있는 테이블의 경우 추가 CTE를 추가하여 이러한 공백을 처리하세요.

• 재사용을 위한 함수 래퍼:

  • 한도 및 간격 비율을 매개변수로 사용하는 함수를 정의하여 다른 테이블에서 쉽게 구성하고 재사용할 수 있도록 합니다.

• 모든 테이블에 대한 범용 기능:

  • 정수 열이 있는 모든 테이블을 매개변수로 받아들이는 일반 함수를 만듭니다.

• 속도를 위한 뷰 구체화:

  • 임의로 선택된 (준) 행을 더 빠르게 검색하려면 최적화된 쿼리를 기반으로 구체화된 뷰를 생성하는 것이 좋습니다.
• PostgreSQL 9.5의

  TABLE SAMPLE:

  • PostgreSQL의 "TABLE SAMPLE SYSTEM" 기능을 활용하여 더 빠르지만 무작위가 적은 행 샘플링 방법을 구현하여 정확한 수의 행이 반환되도록 합니다. 그러나 클러스터링 효과로 인해 표본이 완전히 무작위가 아닐 수도 있다는 점에 유의하세요.

위 내용은 대규모 PostgreSQL 테이블에서 무작위 행을 효율적으로 선택하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!