MySQL의 파티션 테이블에 대한 자세한 소개

이 글은 MySQL의 파티션 테이블에 대한 자세한 소개를 제공합니다. 이는 특정 참조 값을 가지고 있습니다. 도움이 필요한 친구들이 참고할 수 있기를 바랍니다.

사용자에게 파티션 테이블은 독립적인 논리적 테이블이지만, 하단에는 여러 개의 물리적 하위 테이블로 구성됩니다. 파티셔닝을 구현하는 코드는 실제로 기본 테이블 집합의 핸들 개체를 캡슐화한 것입니다. 파티션 테이블에 대한 요청은 핸들 개체를 통해 스토리지 엔진에 대한 인터페이스 호출로 변환됩니다.

Meaning

MySQL은 테이블 생성 시 PARTITION BY 절을 사용하여 각 파티션에 저장된 데이터를 정의할 수 있습니다. 쿼리를 실행할 때 최적화 프로그램은 파티션 정의를 기반으로 필요한 데이터가 없는 파티션을 필터링하므로 쿼리가 모든 파티션을 검색할 필요가 없습니다. 필요한 데이터가 포함된 파티션만 찾을 수 있습니다.

파티셔닝의 주요 목적 중 하나는 더 거친 세부 수준으로 여러 테이블에 데이터를 저장하는 것입니다. 이렇게 하면 관련된 데이터를 함께 저장할 수 있으며, 전체 파티션의 데이터를 한 번에 일괄 삭제하려는 경우에도 매우 편리합니다.

파티셔닝은 다음 시나리오에서 큰 역할을 할 수 있습니다.

• 테이블이 너무 커서 메모리에 모두 담을 수 없거나 테이블의 마지막 부분에만 데이터가 있고 나머지는 과거 데이터입니다

• 파티션된 테이블 데이터는 유지 관리가 더 쉽습니다

#🎜 🎜#
• 파티션 테이블의 데이터는 다양한 물리적 장치에 배포될 수 있습니다

• 파티션 테이블을 사용하면 일부 특별한 병목 현상을 피할 수 있습니다

  # 🎜🎜 #

필요한 경우 별도의 파티션을 백업하고 복원할 수 있습니다
파티션 테이블 자체에도

제한사항#🎜🎜 #, 다음 사항이 특히 중요합니다.

테이블은 최대 1024개의 파티션만 가질 수 있습니다.

• #🎜🎜 # MySQL5.1에서 파티션 표현식은 정수이거나 정수를 반환하는 표현식이어야 합니다. MySQL5.5에서는 일부 시나리오에서 파티셔닝에 열을 직접 사용할 수 있습니다

• 파티션된 테이블에서는 외래 키 제약 조건을 사용할 수 없습니다

#🎜 🎜#
• 파티션 필드에 기본 키 또는 고유 인덱스 열이 있는 경우 모든 기본 키 열 및 고유 인덱스 열이 포함되어야 합니다

• #🎜 🎜# 파티션 테이블의 원리

파티션 내 각 기본 테이블의 스토리지 엔진 관리와 일반 테이블 관리에는 차이가 없습니다(모든 기본 테이블은 동일한 스토리지 엔진을 사용해야 함)

, 분할된 테이블의 인덱스는 각 기본 테이블에 추가된 동일한 인덱스일 뿐입니다. 스토리지 엔진의 관점에서 보면 기본 테이블과 일반 테이블 사이에는 차이가 없으며, 스토리지 엔진은 그것이 일반 테이블인지 또는 분할된 테이블의 일부인지 알 필요가 없습니다.

파티션된 테이블에 대한 작업은 다음 작업 로직에 따라 수행됩니다.

SELECT 쿼리

파티션된 테이블을 쿼리할 때 파티셔닝 레이어가 먼저 열리고 모든 기본 테이블을 잠그면 최적화 프로그램이 먼저 일부 파티션을 필터링할 수 있는지 확인한 다음 해당 스토리지 엔진 인터페이스를 호출하여 각 파티션의 데이터에 액세스합니다

INSERT 작업#🎜🎜 #

레코드를 쓸 때 파티션 계층은 먼저 모든 기본 테이블을 열고 잠근 다음 어떤 파티션이 레코드를 수신하는지 결정한 다음 해당 기본 테이블에 레코드를 씁니다

DELETE 작업 #🎜🎜 #

레코드 삭제 시 파티션 계층은 먼저 모든 기본 테이블을 열고 잠근 다음 데이터에 해당하는 파티션을 결정하고 마지막으로 해당 기본 테이블을 삭제합니다.

UPDATE 작업

레코드를 업데이트할 때 파티션 계층은 먼저 모든 기본 테이블을 열고 잠급니다. MySQL은 먼저 레코드를 업데이트해야 할 파티션을 결정한 다음 데이터를 꺼내 업데이트한 다음 무엇을 결정합니다. 업데이트된 데이터는 어느 파티션에 배치되어야 하며 최종적으로 기본 테이블이 기록되고 원본 데이터가 있는 기본 테이블이 삭제됩니다.

이러한 작업은 모두 필터링을 지원합니다.

각 작업은 "먼저 모든 기본 테이블을 열고 잠그지만"

이

처리 중에 파티션 테이블이 전체 테이블을 잠근다는 의미는 아닙니다. 스토리지 엔진이 행 수준 잠금을 자체적으로 구현할 수 있는 경우 해당 테이블 잠금은 파티션 수준에서 해제됩니다. 이 잠금 및 잠금 해제 프로세스는 일반 InnoDB의 쿼리와 유사합니다.

파티션 테이블 유형

MySQL은 다양한 파티션 테이블을 지원합니다. 파티션은 특정 범위에 속하는 레코드를 저장합니다. 파티션 표현식은 열이거나 열을 포함하는 표현식일 수 있습니다. 예를 들어 다음 테이블은 각 연도 매출을 서로 다른 파티션에 저장합니다.

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION 파티션 절에서는 다양한 함수를 사용할 수 있습니다. 그러나 요구 사항이 있습니다. 표현식에서 반환되는 값은 명확한 정수여야 하며 상수일 수 없습니다.

MySQL은 키 값, 해시 및 목록 파티셔닝 등도 지원합니다.

파티션된 테이블 사용 방법

매우 큰 테이블에서 일정 기간 동안의 레코드를 쿼리하려면 어떻게 쿼리해야 하나요? 이 테이블, 어떻게 하면 더 효율적일 수 있을까요?

데이터의 양이 매우 많기 때문에 쿼리할 때마다 테이블 전체를 스캔할 수는 없습니다. 공간 소모와 인덱스 유지 관리를 고려하면 인덱스를 사용하지 않습니다. 인덱스를 사용하더라도 데이터가 원하는 방식으로 집계되지 않아 많은 양의 조각화가 발생하고 결국 쿼리에서 수천 개의 임의 I/O가 생성된다는 사실을 알게 됩니다. 실제로

데이터의 양이 너무 많아지면 B-Tree 인덱스가 더 이상 작동하지 않게 됩니다.

그래서 우리는 대량의 데이터에서 해당 메타데이터의 작은 부분만 색인화하는 것과 같이 데이터를 검색하는 좀 더 대략적이지만 비용이 적게 드는 방법을 선택할 수 있습니다.

이것이 바로 파티셔닝이 하는 일입니다. 파티셔닝을 이해하는 것이 인덱스의 초기 형태라고 볼 수 있습니다. 파티션은 각 파티션에 데이터를 기록하기 위해 추가 데이터 구조가 필요하지 않기 때문에 파티션은 각 데이터 조각의 위치를 ​​정확하게 찾을 필요가 없으므로 추가 데이터 구조가 필요하지 않으므로 비용이 매우 많이 듭니다. 낮은. 각 파티션에 어떤 데이터가 저장되어 있는지 표현하려면 간단한 표현만 필요합니다.

대량 데이터 볼륨의 확장성을 보장하기 위해 일반적으로 두 가지 전략이 있습니다.

1. Full 볼륨 인덱스 없이 데이터 스캔: WHERE 조건을 사용하여 필요한 데이터를 몇 개의 파티션으로 제한할 수 있는 한 효율성은 매우 높습니다. 이 전략을 사용하면 데이터가 메모리에 완전히 배치될 필요가 없으며 필요한 모든 데이터가 디스크에 있다고 가정합니다. 메모리가 상대적으로 작기 때문에 데이터가 메모리에서 빠르게 압착되므로 캐시는 아무런 역할을 하지 않습니다. 이 전략은 대량의 데이터를 일반적인 방식으로 액세스할 때 적합합니다.

2. 데이터 색인 및 별도의 핫스팟: 데이터에 명백한 "핫스팟"이 있고 데이터의 이 부분과는 별도로 다른 데이터에 액세스하는 경우가 거의 없다면 핫스팟 데이터의 이 부분을 별도의 파티션에 배치하여 이 파티션의 데이터를 메모리에 캐시할 수 있습니다. 이러한 쿼리는 작은 분할 테이블에만 액세스할 수 있고 인덱스를 사용할 수 있으며 캐시를 효과적으로 사용할 수도 있습니다.

어떤 상황에서 문제가 발생하나요?

위에 소개된 두 가지 파티션 전략은 두 가지를 기반으로 합니다. 매우 중요한 가정: 쿼리는 많은 추가 파티션을 필터링할 수 있으며 파티션 자체는 추가 비용을 많이 발생시키지 않습니다.

다음 두 가지 가정은 일부 시나리오에서 문제가 될 수 있습니다.

• 파티션 열과 인덱스 열이 일치하지 않습니다. : 정의된 인덱스 컬럼과 파티션 컬럼이 일치하지 않으면 쿼리에서 파티션 필터링을 수행할 수 없습니다.

• 파티션을 선택하는 데 비용이 많이 들 수 있습니다. 다양한 유형의 파티션이 다르게 구현되므로 성능도 다릅니다. 특히 범위 분할의 경우, 적합한 행이 속한 파티션을 쿼리하는 비용은 서버가 정답을 찾기 위해 모든 파티션 정의 목록을 스캔해야 하기 때문에 매우 높을 수 있습니다.

• 모든 ​​기본 테이블을 열고 잠그는 비용은 높을 수 있습니다. 쿼리가 분할된 테이블에 액세스할 때 MySQL은 lock it 모든 기본 테이블이 저장되는데, 이는 분할된 테이블의 또 다른 오버헤드입니다.

• 파티션 유지 비용이 높을 수 있습니다. 파티션 추가 또는 삭제와 같은 일부 파티션 유지 관리 작업은 매우 빠릅니다. 파티션 재구성이나 유사한 ALTER 문과 같은 일부 작업에는 데이터 복사가 필요하므로 비용이 매우 많이 들 수 있습니다.

위 내용은 MySQL의 파티션 테이블에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!