| 젠 토토 : 문서 : 9.6 : 젠 토토 9 | |||
|---|---|---|---|
| 스포츠 토토 베트맨 PostgreSQL : 문서 : 9.6 : 상속 | 토토 베이 : 문서 : 9.6 : 데이터 정의 | 5 장. 데이터 정의 | PostgreSQL : 문서 : 9.6 : 외국 스포츠 토토 결과 |
PostgreSQL기본 테이블 윈 토토셔닝을 지원합니다. 이 섹션에서는 데이터베이스 디자인의 일부로 윈 토토셔닝을 구현하는 이유와 방법에 대해 설명합니다.
윈 토토션은 논리적으로 하나의 큰 테이블을 작은 물리적 조각으로 분할하는 것을 말합니다. 분할은 몇 가지 이점을 제공 할 수 있습니다.
특정 상황에서, 특히 테이블의 많은에 액세스 된 행의 대부분이 단일 윈 토토션 또는 소수의 윈 토토션에있을 때 특정 상황에서 쿼리 성능을 크게 향상시킬 수 있습니다. 분할은 인덱스의 선행 열을 대체하여 인덱스 크기를 줄이고 인덱스의 많은 부분이 메모리에 적합 할 가능성이 높아집니다.
쿼리 또는 업데이트가 단일 윈 토토션의 많은 비율에 액세스하면 전체 테이블에 흩어져있는 인덱스 및 랜덤 액세스 판독 값을 사용하는 대신 해당 윈 토토션의 순차적 스캔을 활용하여 성능을 향상시킬 수 있습니다.
윈 토토션 설계에 해당 요구 사항이 계획된 경우 윈 토토션을 추가하거나 제거하여 벌크 하중 및 삭제를 수행 할 수 있습니다..ALTER 테이블 없음 상속and드롭 테이블는 모두 벌크 작업보다 훨씬 빠릅니다. 이 명령은 또한 전적으로를 피합니다.진공벌크로 인한 오버 헤드삭제.
거의 사용하지 않는 데이터를 저렴하고 느린 스토리지 미디어로 마이그레이션 할 수 있습니다.
혜택은 일반적으로 테이블이 매우 큰 경우에만 가치가 있습니다. 테이블의 크기가 데이터베이스 서버의 물리적 메모리를 초과해야한다는 것입니다.
현재,PostgreSQL테이블 상속을 통한 파티셔닝을 지원합니다. 각 윈 토토션은 단일 상위 테이블의 자식 테이블로 만들어야합니다. 부모 테이블 자체는 일반적으로 비어 있습니다. 전체 데이터 세트를 나타 내기 만하면 존재합니다. 상속에 익숙해야합니다 (참조스포츠 토토 베트맨 PostgreSQL : 문서 : 9.6 : 상속) 윈 토토셔닝을 설정하기 전에.
다음 형식의 윈 토토셔닝은에서 구현할 수 있습니다.PostgreSQL:
테이블이 분할되어"Ranges"다른 윈 토토션에 할당 된 값의 범위간에 겹치지 않고 키 열 또는 열 세트로 정의됩니다. 예를 들어, 날짜 범위 또는 특정 비즈니스 오브젝트에 대한 식별자 범위에 의해 분할 될 수 있습니다.
테이블은 각 윈 토토션에 어떤 키 값이 나타나는지 명시 적으로 나열하여 분할됩니다.
분할 된 테이블을 설정하려면 다음을 수행하십시오.
생성"마스터"테이블, 모든 윈 토토션이 상속 될 것입니다.
이 테이블에는 데이터가 포함되지 않습니다. 모든 윈 토토션에 똑같이 적용되지 않는 한이 표의 체크 제약 조건을 정의하지 마십시오. 인덱스 또는 고유 한 제약 조건을 정의 할 필요는 없습니다.
여러 생성"Child"각각 마스터 테이블에서 상속되는 테이블. 일반적 으로이 테이블은 마스터에서 상속 된 세트에 열을 추가하지 않습니다.
우리는 자식 테이블을 윈 토토션이라고합니다.PostgreSQL테이블 (또는 아마도 외국 테이블).
윈 토토션 테이블에 테이블 제약 조건을 추가하여 각 윈 토토션에서 허용 된 키 값을 정의합니다.
일반적인 예는 다음과 같습니다.
점검 (x = 1) Check (County in County ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire')))) Check (outletid = 100 및 outletid <200)
제약 조건이 다른 윈 토토션에서 허용되는 주요 값 사이에 중첩이 없음을 보장합니다. 일반적인 실수는 다음과 같은 범위 제약 조건을 설정하는 것입니다.
점검 (100에서 200 사이의 outletid) 확인 (200에서 300 사이의 outletid)
이것은 어떤 윈 토토션의 키 값 200이 속한 지 명확하지 않기 때문에 잘못되었습니다.
범위와 목록 분할 사이의 구문에는 차이가 없습니다. 이 용어는 설명만이 있습니다.
각 윈 토토션마다 키 열의 인덱스와 원하는 다른 색인을 만듭니다. (주요 인덱스는 엄격하게 필요하지 않지만 대부분의 시나리오에서는 도움이됩니다. 핵심 값이 고유 한 경우 항상 각 윈 토토션에 대해 고유하거나 1 차 키 제약 조건을 생성해야합니다.).
선택적으로 마스터 테이블에 삽입 된 데이터를 적절한 윈 토토션으로 리디렉션하기위한 트리거 또는 규칙을 정의합니다.
제약 _exclusion구성 매개 변수가 비활성화되지 않았습니다postgresql.conf. 그렇다면 쿼리는 원하는대로 최적화되지 않습니다.
예를 들어, 대형 아이스크림 회사의 데이터베이스를 구성한다고 가정 해 봅시다. 이 회사는 매일 최대 온도를 측정하고 각 지역의 아이스크림 판매를 측정합니다. 개념적으로 우리는 다음과 같은 테이블을 원합니다.
테이블 측정 생성 (
city_id int null not,
로그 데이트 날짜는 null이 아닙니다.
PeakTemp int,
단위 int
);
우리는 대부분의 쿼리가 지난 주, 월 또는 분기의 데이터에만 액세스 할 것임을 알고 있습니다.이 표의 주요 사용은 관리를 위해 온라인 보고서를 준비하는 것이기 때문입니다. 저장 해야하는 이전 데이터의 양을 줄이기 위해 가장 최근 3 년간의 데이터 만 유지하기로 결정했습니다. 매월 초에 우리는 가장 오래된 달의 데이터를 제거 할 것입니다.
이 상황에서는 분할을 사용하여 측정 테이블에 대한 다양한 요구 사항을 충족시킬 수 있습니다. 위에서 설명한 단계에 따라 윈 토토셔닝은 다음과 같이 설정할 수 있습니다.
마스터 테이블은입니다.측정테이블, 위와 같이 정확히 선언되었습니다.
다음으로 우리는 활성 달마다 하나의 윈 토토션을 만듭니다 :
테이블 생성 _y2006m02 () 상속 (측정); 테이블 측정 _y2006m03 () 상속 (측정)을 작성합니다. ... 테이블 측정 _y2007m11 () 상속 (측정)을 만듭니다. 테이블 측정 _y2007m12 () 상속 (측정)을 만듭니다. 테이블 측정 _y2008m01 () 상속 (측정); 생성
각 윈 토토션은 자체적으로 완전한 테이블이지만의 정의를 상속받습니다.측정테이블.
이것은 우리의 문제 중 하나를 해결합니다 : 이전 데이터 삭제. 매달 우리가해야 할 일은 a를 수행하는 것입니다.드롭 테이블가장 오래된 어린이 테이블에서 새 달의 데이터에 대한 새 자식 테이블을 만듭니다.
우리는 겹치지 않는 테이블 제약 조건을 제공해야합니다. 위와 같이 윈 토토션 테이블을 만드는 대신 테이블 작성 스크립트는 다음과 같아야합니다.
테이블 측정 생성 _y2006m02 (
check (logdate = date '2006-02-01'및 logdate <날짜 '2006-03-01')
) 상속 (측정);
테이블 측정 _y2006m03 생성 (
check (logdate = date '2006-03-01'및 logdate <날짜 '2006-04-01'))
) 상속 (측정);
...
테이블 측정 값 생성 _y2007m11 (
check (logdate = date '2007-11-01'및 logdate <날짜 '2007-12-01'))
) 상속 (측정);
테이블 측정 _y2007m12 생성 (
check (logdate = date '2007-12-01'및 logdate <날짜 '2008-01-01')
) 상속 (측정);
테이블 측정 _y2008m01 생성 (
check (logdate = date '2008-01-01'및 logdate <날짜 '2008-02-01'))
) 상속 (측정);우리는 아마도 주요 열에 인덱스가 필요할 것입니다 :
Measurement_Y2006M02 (logdate)에서 인덱스 측정 _y2006M02_LOGDATE 만들기; Measurement_Y2006M03 (logdate)에서 인덱스 측정 _y2006M03_LOGDATE를 만듭니다. ... Measurement_Y2007M11 (logdate)에서 색인 측정 _y2007m11_logdate를 만듭니다. Measurement_Y2007M12 (logdate)에서 색인 측정 _y2007m12_logdate를 만듭니다. Measurement_Y2008M01 (logdate);에서 색인 측정 _y2008m01_Logdate 작성
현재 추가 인덱스를 추가하지 않기로 선택합니다.
우리는 응용 프로그램을 말할 수 있기를 원합니다측정에 삽입 ...데이터를 적절한 윈 토토션 테이블로 리디렉션하도록합니다. 적절한 트리거 함수를 마스터 테이블에 첨부하여이를 배열 할 수 있습니다. 데이터가 최신 윈 토토션에만 추가되면 매우 간단한 트리거 기능을 사용할 수 있습니다.
함수 측정 만들기 또는 교체 _insert_trigger ()
트리거를 $$로 반환합니다
시작하다
측정 _y2008m01 값 (new.*)에 삽입;
널 리턴;
끝;
$$
언어 plpgsql;
함수를 작성한 후 트리거 함수를 호출하는 트리거를 만듭니다.
Trigger insert_measurement_trigger 작성
측정에 삽입하기 전에
각 행마다 Execute Procedure Measurement_Insert_trigger ();
매달 트리거 함수를 재정의하여 항상 현재 윈 토토션을 가리킬 수 있도록해야합니다. 그러나 트리거 정의를 업데이트 할 필요는 없습니다.
데이터를 삽입하고 서버가 행을 추가 해야하는 윈 토토션을 자동으로 찾을 수 있습니다. 예를 들어보다 복잡한 트리거 기능 으로이 작업을 수행 할 수 있습니다.
함수 측정 또는 교체 _insert_trigger ()
트리거를 $$로 반환합니다
시작하다
if (new.logdate = date '2006-02-01'및
new.logdate <날짜 '2006-03-01')
측정 _y2006m02 값 (new.*)에 삽입;
elsif (new.logdate = date '2006-03-01'및
new.logdate <날짜 '2006-04-01')
측정 _y2006m03 값 (new.*)에 삽입;
...
elsif (new.logdate = date '2008-01-01'및
new.logdate <날짜 '2008-02-01')
측정 _y2008m01 값 (new.*)에 삽입;
또 다른
예외 '범위를 벗어난 날짜를 올리십시오. 측정 _insert_trigger () 함수! ';
끝 IF;
널 리턴;
끝;
$$
언어 plpgsql;
트리거 정의는 이전과 동일합니다. 각각if테스트는 정확히 일치해야합니다check윈 토토션에 대한 제약 조건.
이 기능은 단일 월 경우보다 더 복잡하지만 분기가 필요하기 전에 추가 할 수 있기 때문에 자주 업데이트 할 필요는 없습니다..
참고 :실제로 대부분의 인서트가 해당 윈 토토션에 들어가면 최신 윈 토토션을 먼저 확인하는 것이 가장 좋습니다. 단순화를 위해이 예제의 다른 부분에서 트리거 테스트를 동일한 순서로 보여주었습니다.
우리가 볼 수 있듯이 복잡한 분할 체계에는 상당한 양의 DDL이 필요할 수 있습니다. 위의 예에서는 매달 새 윈 토토션을 만들 것이므로 필요한 DDL을 자동으로 생성하는 스크립트를 작성하는 것이 좋습니다.
일반적으로 테이블을 처음 정의 할 때 설정된 윈 토토션 세트는 정적을 유지하기위한 것이 아닙니다. 오래된 데이터를 제거하고 새로운 데이터에 대한 새로운 윈 토토션을 주기적으로 추가하는 것이 일반적입니다. 윈 토토셔닝의 가장 중요한 장점 중 하나는 많은 양의 데이터를 물리적으로 움직이지 않고 윈 토토션 구조를 조작 하여이 고통스러운 작업을 거의 즉시 실행할 수 있다는 것입니다..
오래된 데이터를 제거하기위한 가장 간단한 옵션은 더 이상 필요하지 않은 윈 토토션을 삭제하는 것입니다.
드롭 테이블 측정 _y2006m02;
이것은 모든 레코드를 개별적으로 삭제할 필요가 없기 때문에 수백만 레코드를 매우 빠르게 삭제할 수 있습니다.
종종 바람직한 또 다른 옵션은 윈 토토션 된 테이블에서 윈 토토션을 제거하지만 자체 오른쪽으로 테이블로 액세스 할 수있는 것입니다.
ALTER 테이블 측정 _Y2006M02 상속 측정 없음;
이를 통해 데이터가 삭제되기 전에 추가 작업을 수행 할 수 있습니다. 예를 들어, 이것은 종종를 사용하여 데이터를 백업하는 데 유용한 시간입니다.COPY, pg_dump또는 유사한 도구. 또한 데이터를 더 작은 형식으로 집계하거나 다른 데이터 조작을 수행하거나 보고서를 실행하는 데 유용한 시간 일 수도 있습니다.
마찬가지로 새로운 데이터를 처리하기 위해 새 윈 토토션을 추가 할 수 있습니다. 위에 원래 윈 토토션이 생성 된 것처럼 윈 토토션 된 테이블에서 빈 윈 토토션을 만들 수 있습니다.
테이블 생성 측정 _y2008m02 (
check (logdate = date '2008-02-01'및 logdate <날짜 '2008-03-01'))
) 상속 (측정);
대안으로서, 윈 토토션 구조 외부에 새 테이블을 만들고 나중에 적절한 윈 토토션으로 만드는 것이 더 편리합니다. 이를 통해 윈 토토션 된 테이블에 표시되기 전에 데이터를로드, 점검 및 변환 할 수 있습니다.
테이블 측정 _y2008m02 생성 (제약 조건을 포함한 기본값을 포함한 측정과 같은); Alter Table Measurement_Y2008M02 추가 제약 조건 Y2008M02 check (logdate = date '2008-02-01'및 logdate <날짜 '2008-03-01'); \ '측정 _y2008m02'에서 측정 _y2008m02 복사 - 아마도 다른 데이터 준비 작업 Alter Table Measurement_Y2008M02 상속 측정;
제약 제외는 위에서 설명한 방식으로 정의 된 분할 된 테이블의 성능을 향상시키는 쿼리 최적화 기술입니다. 예를 들어 :
SET SERPARTAINT_EXCLUSION = ON; Logdate = Date '2008-01-01'; 측정에서 count (*)를 선택하십시오.
제약 제외없이 위의 쿼리는의 각 윈 토토션을 스캔합니다.측정테이블. 제약 제외가 활성화되면 플래너는 각 윈 토토션의 제약 조건을 검사하고 쿼리를 충족하는 행을 포함 할 수 없기 때문에 윈 토토션을 스캔 할 필요가 없음을 증명하려고합니다.여기서절. 플래너가 이것을 증명할 수 있으면 쿼리 계획에서 윈 토토션을 제외합니다.
를 사용할 수 있습니다설명계획의 차이를 보여주는 명령제약 _exclusionon 및 계획. 이 유형의 테이블 설정에 대한 일반적인 최적화 된 계획은 다음과 같습니다.
SET SUNSTAINT_EXCLUSION = OFF;
로그 데이트 = 날짜 '2008-01-01'에서 측정에서 선택한 수 (*)를 설명하십시오.
쿼리 계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
집계 (비용 = 158.66..158.68 행 = 1 너비 = 0)
- Append (비용 = 0.00..151.88 행 = 2715 너비 = 0)
- 측정시 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
- 측정 _y2006m02 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
- 측정 _y2006m03 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
...
- 측정 _y2007m12 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
- 측정 _y2008m01 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
윈 토토션 중 일부 또는 전부는 전체 테이블 순차 스캔 대신 인덱스 스캔을 사용할 수 있지만 여기서는이 쿼리에 응답하기 위해 이전 윈 토토션을 전혀 스캔 할 필요가 없다는 것입니다. 제약 제외를 활성화하면 동일한 답변을 전달할 상당히 저렴한 계획을 얻습니다.
SET SORMSARTAINT_EXCLUSION = ON;
로그 데이트 = 날짜 '2008-01-01'에서 측정에서 선택한 수 (*)를 설명하십시오.
쿼리 계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
집계 (비용 = 63.47..63.48 행 = 1 너비 = 0)
- Append (비용 = 0.00..60.75 행 = 1086 너비 = 0)
- 측정시 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
- 측정 _y2008m01 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2008-01-01':: 날짜)
제약 제외는에 의해서만 구동됩니다.check인덱스의 존재에 의한 것이 아니라 제약. 따라서 키 열의 인덱스를 정의 할 필요는 없습니다. 주어진 윈 토토션에 대해 인덱스를 만들어야하는지 여부는 윈 토토션을 스캔하는 쿼리가 일반적으로 윈 토토션의 많은 부분을 스캔 할 것이라고 기대하는지 여부에 따라 다릅니다. 색인은 후자의 경우에 도움이되지만 전자는 아닙니다.
기본 (및 권장) 설정제약 _exclusion실제로는 아닙니다onNOROFF, 그러나 중간 설정은윈 토토션,이 기술은 분할 된 테이블에서 작동 할 수있는 쿼리에만 적용됩니다. 그만큼on설정 설정 플래너가 검사하게됩니다check모든 쿼리의 제약 조건, 혜택이없는 간단한 쿼리조차도
삽입물을 적절한 윈 토토션 테이블로 리디렉션하는 다른 접근법은 마스터 테이블에서 트리거 대신 규칙을 설정하는 것입니다. 예를 들어:
규칙 생성 _insert_y2006m02 AS를 작성하십시오
측정 값으로 삽입하십시오
(logdate = date '2006-02-01'및 logdate <날짜 '2006-03-01'))
대신하십시오
측정 _y2006m02 값 (new.*)에 삽입;
...
규칙 측정 _insert_y2008m01을 만듭니다
측정 값으로 삽입하십시오
(logdate = date '2008-01-01'및 logdate <날짜 '2008-02-01'))
대신하십시오
측정 _y2008m01 값 (new.*);에 삽입하십시오.
규칙은 트리거보다 오버 헤드가 훨씬 많지만 오버 헤드는 행당 한 번보다 쿼리 당 한 번 지불 되므로이 방법은 벌크 삽입 상황에 유리할 수 있습니다. 그러나 대부분의 경우 트리거 방법은 더 나은 성능을 제공합니다.
COPY규칙을 무시합니다. 사용하고 싶다면COPY데이터를 삽입하려면 마스터가 아닌 올바른 윈 토토션 테이블에 복사해야합니다.COPY화재 트리거를 사용하므로 트리거 접근법을 사용하면 정상적으로 사용할 수 있습니다.
규칙 접근법의 또 다른 단점은 규칙 세트가 삽입 날짜를 다루지 않으면 오류를 강제하는 간단한 방법이 없다는 것입니다. 데이터는 대신 마스터 테이블로 조용히 들어갑니다.
윈 토토셔닝도 a를 사용하여 배열 할 수 있습니다.Union All테이블 상속 대신보기. 예를 들어,
보기 측정을 만듭니다
Measurement_y2006m02에서 *를 선택하십시오
Union All Select * From Measurement_Y2006M03
...
Union All Select * From Measurement_Y2007M11
Union All Select * From Measurement_Y2007M12
Union All Select * From Measurement_Y2008M01;
그러나 뷰를 재현 할 필요가 있으면 데이터 세트의 개별 윈 토토션을 추가하고 삭제하는 데 추가 단계가 추가됩니다. 실제로이 방법은 상속을 사용하는 것과 비교할 때 권장 할 것이 거의 없습니다.
다음 경고는 분할 된 테이블에 적용됩니다 :
모든 것을 확인하는 자동 방법이 없습니다check제약은 상호 배타적입니다. 윈 토토션을 생성하고 각각을 손으로 쓰는 것보다 관련 객체를 생성 및/또는 수정하는 코드를 만드는 것이 더 안전합니다.
여기에 표시된 체계는 행의 윈 토토션 키 열이 변경되지 않거나 최소한 다른 윈 토토션으로 이동하도록 요구할 정도로 변경되지 않는다고 가정합니다.업데이트그렇게하려고 시도하는 것은 때문에 실패 할 것입니다.check제약. 이러한 사례를 처리 해야하는 경우 윈 토토션 테이블에 적절한 업데이트 트리거를 넣을 수 있지만 구조 관리가 훨씬 더 복잡해집니다..
매뉴얼을 사용하는 경우진공또는분석명령, 각 윈 토토션에서 개별적으로 실행해야한다는 것을 잊지 마십시오. 명령 :
측정 분석;
마스터 테이블 만 처리합니다.
삽입진술충돌클로스는 예상대로 작동하지 않을 것입니다.충돌행동은 아동 관계가 아니라 지정된 대상 관계에 대한 고유 한 위반의 경우에만 취해집니다.
다음 경고는 제약 제외에 적용됩니다 :
제약 제외는 쿼리의 경우에만 작동합니다어디절은 상수 (또는 외부로 제공된 매개 변수)를 포함합니다. 예를 들어,와 같은 상상할 수없는 함수에 대한 비교current_timestamp플래너가 런타임에 기능 값이 어떤 윈 토토션에 빠질 수 있는지 알 수 없으므로 최적화 할 수 없습니다.
분할 제약 조건을 간단하게 유지하십시오. 그렇지 않으면 플래너가 윈 토토션을 방문 할 필요가 없음을 증명하지 못할 수도 있습니다. 목록 분할에 간단한 평등 조건을 사용하거나 앞의 예제와 같이 범위 분할을위한 간단한 범위 테스트를 사용하십시오. 경험의 좋은 규칙은 분할 제약 조건에 B- 트리-무인 연산자를 사용하여 상수와 분할 열의 비교 만 포함해야한다는 것입니다..
마스터 테이블의 모든 윈 토토션에 대한 모든 제약은 제약 제외 중에 검사되므로 많은 윈 토토션이 쿼리 계획 시간을 상당히 증가시킬 수 있습니다. 이러한 기술을 사용하는 분할은 아마도 백 윈 토토션까지 잘 작동합니다. 수천 개의 윈 토토션을 사용하려고하지 마십시오.