| Postgresql 9.3.25 문서 | ||||
|---|---|---|---|---|
| 무지개 토토 PostgreSQL : 문서 : 9.3 : 상속 | 스포츠 토토 베트맨 : 문서 : 9.3 : 데이터 정의 | 5 장 데이터 정의 | PostgreSQL : 문서 : 9.3 : 외국 범퍼카 토토 | |
PostgreSQL기본 테이블을 지원합니다 분할. 이 섹션에서는 구현 이유와 방법에 대해 설명합니다 데이터베이스 디자인의 일부로 분할.
스포츠 토토 베트맨은 논리적으로 하나의 큰 것을 분할하는 것을 말합니다 작은 물리적 조각으로 테이블. 분할이 제공 할 수 있습니다 몇 가지 이점 :
쿼리 성능을 크게 향상시킬 수 있습니다 상황, 특히 대부분의 심하게 액세스 한 행이 테이블은 단일 스포츠 토토 베트맨 또는 소수의 스포츠 토토 베트맨. 선행 열을위한 분할 대체물 인덱스, 인덱스 크기를 줄이며 인덱스의 많은 부분이 메모리에 맞습니다.
쿼리 또는 업데이트가 단일 비율의 많은 비율에 액세스 할 때 스포츠 토토 베트맨, 성능은 이용하여 개선 될 수 있습니다 인덱스를 사용하는 대신 해당 스포츠 토토 베트맨의 순차적 스캔 및 임의의 액세스는 전체 테이블에 흩어져 있습니다.
벌크 하중 및 삭제를 추가하거나 제거하여 수행 할 수 있습니다. 파티션, 해당 요구 사항이 스포츠 토토 베트맨에 계획된 경우 설계.ALTER 테이블 없음 상속and드롭 테이블는 모두 벌크보다 훨씬 빠릅니다 작업. 이 명령은 또한 전적으로를 피합니다.진공벌크로 인한 오버 헤드삭제.
거의 사용하지 않는 데이터를 저렴하고 느린 스토리지로 마이그레이션 할 수 있습니다. 메디아.
혜택은 일반적으로 테이블이있을 때만 가치가 있습니다. 그렇지 않으면 매우 큽니다. 테이블의 정확한 지점 분할의 혜택은 응용 프로그램에 따라 다르지만 a 경험의 규칙은 테이블의 크기가 데이터베이스 서버의 물리적 메모리.
현재,PostgreSQL지원 테이블 상속을 통한 분할. 각 스포츠 토토 베트맨을 만들어야합니다 단일 부모 테이블의 자식 테이블로. 부모 테이블 자체 일반적으로 비어 있습니다. 전체 데이터 세트를 나타 내기 만하면 존재합니다. 상속에 익숙해야합니다 (참조섹션 5.8) 설정을 시도하기 전에 분할.
다음 형식의 스포츠 토토 베트맨을 구현할 수 있습니다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은 다음과 같습니다.
범위와 구문에는 차이가 없습니다. 목록 분할; 이 용어는 설명만이 있습니다.
각 스포츠 토토 베트맨의 경우 키 열에서 인덱스를 만듭니다. 당신이 원하는 다른 인덱스. (주요 인덱스는 아닙니다 엄격하게 필요하지만 대부분의 시나리오에서는 도움이됩니다. 당신이 핵심 값이 고유 한 경우 항상 각 스포츠 토토 베트맨에 대한 고유 또는 기본 키 제약 조건.)
선택적으로 삽입 된 데이터 리디렉션 트리거 또는 규칙을 정의합니다. 적절한 스포츠 토토 베트맨으로 마스터 테이블로.
제약 _exclusion구성 매개 변수가 비활성화되지 않았습니다postgresql.conf. 그렇다면 쿼리는 그렇지 않습니다 원하는대로 최적화.
예를 들어, 대형 데이터베이스를 구성한다고 가정 해 아이스크림 회사. 회사는 매일 최대 온도를 측정합니다 각 지역의 아이스크림 판매뿐만 아니라. 개념적으로, 우리는 a를 원합니다 테이블 :
테이블 측정 생성 (
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 작성
현재 추가 인덱스를 추가하지 않기로 선택합니다.
우리는 응용 프로그램을 말할 수 있기를 원합니다18495_18524그리고 데이터가 있습니다 적절한 스포츠 토토 베트맨 테이블로 리디렉션됩니다. 우리는 준비 할 수 있습니다 마스터 테이블에 적합한 트리거 함수를 연결함으로써. 데이터가 최신 스포츠 토토 베트맨에만 추가되면 매우 간단한 트리거 기능 :
함수 생성 또는 교체 측정 _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 SORMSARTAINT_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제약. 그러한 경우를 처리 해야하는 경우 스포츠 토토 베트맨 테이블에 적절한 업데이트 트리거를 넣을 수 있지만 그것은 구조의 관리를 훨씬 더 복잡하게 만듭니다.
매뉴얼을 사용하는 경우진공또는분석명령, 당신을 잊지 마십시오 각 스포츠 토토 베트맨에서 개별적으로 실행해야합니다. 명령 좋다:
측정 분석;
마스터 테이블 만 처리합니다.
다음 경고는 제약 제외에 적용됩니다 :
제약 제외는 쿼리의 경우에만 작동합니다여기서절에 상수 (또는 외부 적으로는 포함됩니다
제공된 매개 변수). 예를 들어, a
와 같은 비 면도 할 수없는 함수current_timestamp이후로 최적화 할 수 없습니다
플래너는 기능 값이 떨어질 수있는 스포츠 토토 베트맨을 알 수 없습니다.
런타임에.
분할 제약 조건을 간단하게 유지하십시오 스포츠 토토 베트맨을 방문 할 필요가 없다는 것을 증명할 수 없습니다. 사용 목록 분할을위한 간단한 평등 조건 또는 간단한 범위 이전에 설명 된 바와 같이 범위 스포츠 토토 베트맨에 대한 테스트 예. 경험의 좋은 규칙은 분할 제약 조건입니다 스포츠 토토 베트맨 칼럼과의 비교 만 포함해야합니다 B- 트리-지연 연산자를 사용하는 상수.
마스터 테이블의 모든 스포츠 토토 베트맨에 대한 모든 제약은 다음과 같습니다. 제약 제외 중에 검사를 받았으므로 많은 수의 스포츠 토토 베트맨은 쿼리 계획 시간을 상당히 증가시킬 것입니다. 이러한 기술을 사용하는 분할은 최대 최대 잘 작동합니다. 아마도 백 스포츠 토토 베트맨; 수천 명을 사용하려고하지 마십시오 스포츠 토토 베트맨.