| Postgresql 9.2.24 문서 | ||||
|---|---|---|---|---|
| 토토 : 문서 : 9.2 : 상속 | 스포츠 토토 : 문서 : 9.2 : 데이터 정의 | 5 장. 데이터 정의 | PostgreSQL : 문서 : 9.2 : 외국 토토 사이트 순위 | |
postgresql기본을 지원합니다 테이블 파티셔닝. 이 섹션에서는 이유와 방법에 대해 설명합니다 데이터베이스 디자인의 일부로 파티셔닝 구현.
윈 토토은 논리적으로 하나의 큰 것을 분할하는 것을 말합니다 작은 물리적 조각으로 테이블. 분할이 제공 할 수 있습니다 몇 가지 이점 :
쿼리 성능을 크게 향상시킬 수 있습니다 특정 상황, 특히 대부분의 경우 테이블의 액세스 행은 단일 윈 토토 또는 소수의 윈 토토. 분할 대체물 인덱스의 주요 열의 경우 인덱스 크기를 줄입니다 무겁게 사용되는 부분이 메모리에 적합한 색인.
쿼리 또는 업데이트가 많은 비율에 액세스 할 때 단일 윈 토토, 성능을 개선 할 수 있습니다 대신 해당 윈 토토의 순차적 스캔의 장점 인덱스와 임의의 액세스를 사용하여 전체 테이블.
벌크 하중 및 삭제는 추가 또는 해당 요구 사항이 계획된 경우 윈 토토 제거 윈 토토 디자인.Alter Table No 상속and드롭 테이블are 대량 작동보다 훨씬 빠릅니다. 이 명령도 있습니다 완전히 피하십시오진공오버 헤드 벌크로 인해삭제.
거의 사용하지 않는 데이터를 저렴하고 느리게 마이그레이션 할 수 있습니다 스토리지 미디어.
혜택은 일반적으로 테이블 일 때만 가치가 있습니다. 그렇지 않으면 매우 클 것입니다. 테이블의 정확한 지점 분할의 혜택은 응용 프로그램에 따라 다릅니다. 경험의 규칙은 테이블의 크기가 데이터베이스 서버의 물리적 메모리를 초과합니다.
현재,PostgreSQL테이블 상속을 통한 파티셔닝을 지원합니다. 각 윈 토토 단일 부모 테이블의 자식 테이블로 만들어야합니다. 그만큼 부모 테이블 자체는 일반적으로 비어 있습니다. 그것은 단지 존재합니다 전체 데이터 세트를 나타냅니다. 당신은 익숙해야합니다 상속 (참조토토 : 문서 : 9.2 : 상속) 윈 토토을 설정하기 전에.
다음 형태의 파티셔닝은 구현 될 수 있습니다.postgresql:
테이블이 분할되어"Ranges"키 열 또는 세트로 정의됩니다 값의 범위에 겹치지 않는 열 다른 윈 토토에 할당됩니다. 예를 들어, 하나는 할 수 있습니다 날짜 범위 또는 식별자 범위별로 분할 특정 비즈니스 개체.
테이블은 명시 적으로 나열하여 윈 토토됩니다 각 윈 토토에 주요 값이 나타납니다.
분할 된 테이블을 설정하려면 다음을 수행하십시오.
생성"마스터"테이블, 모든 윈 토토이 상속 될 것입니다.
이 테이블에는 데이터가 포함되지 않습니다. 수표를 정의하지 마십시오 이 테이블의 제약 조건, 당신이 그들을 의도하지 않는 한 모든 윈 토토에 똑같이 적용됩니다. 요점이 없습니다 인덱스 또는 고유 한 제약 조건을 정의하고 어느 하나.
여러 생성"child"테이블 마스터 테이블에서 각각 상속합니다. 일반적으로, 이것들 테이블은 상속 된 세트에 열을 추가하지 않습니다. 마스터.
우리는 하위 테이블을 윈 토토이라고합니다. 그들은 모든면에서 정상입니다postgresql테이블.
윈 토토 테이블에 테이블 제약 조건을 추가하여 정의합니다 각 윈 토토에서 허용 된 주요 값.
일반적인 예는 다음과 같습니다.
점검 (x = 1) Check (County in County ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire')))) Check (outletid = 100 및 outletid <200)
제약 조건에 No가 있음을 보장하십시오 다르게 허용되는 키 값간에 중첩됩니다 윈 토토. 일반적인 실수는 범위 제약 조건을 설정하는 것입니다 좋다:
점검 (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 SORMSARTAINT_EXCLUSION = ON; Logdate = Date '2008-01-01'; 측정에서 count (*)를 선택하십시오.
제약 제외가 없으면 위의 쿼리가 스캔됩니다 의 각 윈 토토측정테이블. 제약 제외 활성화 된 플래너는 각각의 제약 조건을 조사합니다 윈 토토이 윈 토토이 될 필요가 없음을 증명하려고 노력합니다. 줄을 섭취 할 수 없기 때문에 스캔했습니다. 쿼리여기서절. 플래너시기 이것을 증명할 수 있고, 쿼리에서 윈 토토을 제외합니다. 계획.
당신은를 사용할 수 있습니다설명명령으로 계획의 차이점제약 _exclusionon 및 계획. 이 유형의 테이블 설정에 대한 일반적인 최적화 된 계획은 다음과 같습니다.
SET SERPARTAINT_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':: 날짜)
일부 또는 전부는 대신 인덱스 스캔을 사용할 수 있습니다. 전체 테이블 순차적 스캔이지만 여기서 요점은 대답하기 위해 이전 윈 토토을 전혀 스캔 할 필요가 없습니다. 이 쿼리. 제약 제외를 활성화하면 a를 얻습니다 동일하게 제공 할 상당히 저렴한 계획 답변:
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제약. 그러한 경우를 처리 해야하는 경우 윈 토토 테이블에서 적절한 업데이트 트리거이지만 구조의 관리를 훨씬 더 많이 만듭니다 복잡한.
매뉴얼을 사용하는 경우진공또는분석명령, 잊지 마십시오 각 윈 토토에서 개별적으로 실행해야합니다. 에이 명령 :
측정 분석;
마스터 테이블 만 처리합니다.
다음 경고는 제약 제외에 적용됩니다 :
제약 제외는 쿼리의 경우에만 작동합니다여기서절에 상수가 포함됩니다
(또는 외부로 제공된 매개 변수). 예를 들어, a
와 같은 상상할 수없는 함수와 비교current_timestamp할 수 없습니다
플래너는 어떤 윈 토토을 알 수 없으므로 최적화되었습니다
함수 값은 실행 시간에 속할 수 있습니다.
분할 제약 조건을 간단하게 유지하십시오 플래너는 윈 토토이 필요하지 않다는 것을 증명하지 못할 수 있습니다. 방문합니다. 목록에 간단한 평등 조건을 사용하십시오 파티셔닝 또는 범위 분할을위한 간단한 범위 테스트, 앞의 예에 설명 된 바와 같이. 좋은 규칙 엄지 손가락은 분할 제약 조건에만 포함되어야한다는 것입니다 분할 열의 상수와 비교 B- 트리-지연 연산자 사용.
마스터 테이블의 모든 윈 토토에 대한 모든 제약 제약 제외 중에 검사를 받으므로 많은 숫자입니다 윈 토토의 쿼리 계획 시간이 증가 할 가능성이 높습니다 상당히. 이러한 기술을 사용하는 파티셔닝이 작동합니다 아마도 100 개의 윈 토토으로; 시도하지 마십시오 수천 개의 윈 토토을 사용하십시오.