다중 버전 동시성 제어

mysql의 대부분의 트랜잭션 스토리지 엔진은 간단한 행 수준 잠금을 구현하지 않습니다. 동시성 성능 향상을 고려하여 일반적으로 다중 버전 동시성 제어를 동시에 구현합니다.

MVCC는 행 수준 잠금의 변형으로 생각할 수 있지만 오버헤드가 낮기 때문에 많은 경우 잠금 작업을 피합니다.

InnoDB의 MVCC는 각 행 레코드의 끝에 저장된 두 개의 숨겨진 열로 구현됩니다. 이 두 열 중 하나는 행의 생성 시간을 저장하고 다른 하나는 행의 만료 시간(또는 삭제 시간)을 저장합니다. 물론 저장은 실제 시간값이 아니라 시스템 버전입니다. 새로운 트랜잭션이 시작될 때마다 시스템 버전 번호가 자동으로 증가됩니다. 트랜잭션 시작 시 시스템 버전 번호는 쿼리된 각 행의 버전 번호를 비교하는 데 사용되는 트랜잭션 버전 번호로 사용됩니다.

REPEATABLE READ 격리 수준, MVCC 구현:

SELECT

InnoDB는 버전이 현재 트랜잭션 버전 번호보다 이전인 데이터 행을 찾습니다. 이를 통해 트랜잭션에서 읽은 행이 다음 위치에 있는지 확인합니다. 트랜잭션 시작 이전에 이미 존재했거나 트랜잭션 자체에 의해 삽입되거나 수정되었습니다.
행의 삭제된 버전이 정의되지 않았거나 현재 트랜잭션 버전 번호보다 큽니다. 이는 트랜잭션이 시작되기 전에 트랜잭션에서 읽은 행이 삭제되지 않았음을 보장합니다.

INSERT

InnoDB는 현재 시스템 버전 번호를 새로 삽입된 각 행의 행 버전 번호로 저장합니다.

DELETE

InnoDB는 현재 시스템 버전 번호를 삭제된 각 행에 대한 행 삭제 식별자로 저장합니다.

UPDATE

InnoDB는 새 레코드를 삽입할 때 현재 시스템 버전 번호를 행 버전 번호로 저장하고, 현재 시스템 버전 번호를 행 삭제 버전 번호로 원래 행에 저장합니다.

MVCC는 REPEATABLE READ와 READ COMMITED라는 두 가지 격리 수준에서만 작동합니다. 다른 두 격리 수준은 MVCC와 호환되지 않습니다. READ UNCOMMITED는 항상 현재 트랜잭션 버전을 준수하는 데이터 행이 아닌 최신 데이터 행을 읽기 때문입니다. SERIALIZABLE은 읽은 데이터의 모든 행을 잠급니다.

스토리지 엔진

InnoDB 스토리지 엔진

InnoDB는 MYSQL의 기본 트랜잭션 엔진이자 가장 중요하고 널리 사용되는 스토리지 엔진이기도 합니다. 다른 스토리지 엔진을 사용해야 하는 아주 특별한 이유가 없다면 InnoDB 엔진에 우선순위를 두어야 합니다.

InnoDB는 MVCC를 사용하여 높은 동시성을 지원하고 4가지 표준 격리 수준을 구현합니다. 기본 레벨은 REPEATABLE READ(반복 읽기)이며, 갭 잠금 + MVCC 전략은 팬텀 읽기 구현을 방지합니다. 갭 잠금을 사용하면 InnoDB가 쿼리 설계의 행을 잠글 뿐만 아니라 인덱스의 갭도 잠가서 방지할 수 있습니다. 팬텀 행 삽입.

간격 잠금: 동등 조건 대신 범위 조건을 사용하여 데이터를 검색하고 공유 또는 배타적 잠금을 요청하면 InnoDB는 조건 범위 내에 있지만 조건을 충족하는 기존 데이터 레코드의 인덱스 항목을 잠급니다. 존재하지 않는 레코드를 "갭"이라고 합니다. InnoDB는 이 "갭"을 잠그기도 합니다. 이 잠금 메커니즘은 소위 갭 잠금(Next-Key 잠금)입니다.
참고: Gap Lock(Next-Key Lock)

메인 인덱스는 클러스터형 인덱스로, 디스크에서 직접 읽는 것을 방지하기 위해 인덱스에 데이터를 저장하여 쿼리 성능을 크게 향상시킵니다.

InnoDB는 디스크에서 데이터를 읽을 때 예측 가능한 미리 읽기, 작업 속도를 높이기 위해 메모리에 해시 인덱스를 자동으로 생성할 수 있는 적응형 해시 인덱스, 삽입 작업 속도를 높일 수 있는 삽입 버퍼를 포함하여 많은 내부 최적화를 수행했습니다. 등.

MyISAM 스토리지 엔진

mysql5.1 및 이전 버전에서는 MyISAM이 기본 스토리지 엔진입니다. MyISAM은 전체 텍스트 인덱싱, 압축, 공간 함수 등을 포함한 많은 기능을 제공하지만 트랜잭션 및 행 수준 잠금을 지원하지 않으며 충돌 후 안전하게 복구할 수 없다는 데는 의심의 여지가 없습니다.

읽기 전용 데이터 또는 테이블이 상대적으로 작아서 복구 작업을 견딜 수 있는 경우 MyISAM 엔진을 계속 사용할 수 있습니다.

MyISAM 테이블 생성 시 DELAY_KEY_WRITE 옵션을 지정하면 각 수정이 완료될 때 수정된 인덱스 데이터가 즉시 디스크에 기록되지 않고, 메모리의 키 버퍼에 기록됩니다. key 버퍼가 닫히거나 테이블이 닫힐 때만 해당 인덱스 블록이 디스크에 기록됩니다. 이 방법은 쓰기 성능을 크게 향상시킬 수 있지만 데이터베이스나 호스트가 충돌할 때 인덱스가 손상되어 복구 작업이 필요할 수 있습니다.

비교

트랜잭션: InnoDB는 트랜잭션을 지원하지만 MyISAM은 트랜잭션을 지원하지 않습니다.
잠금 세분성: InnoDB는 테이블 수준 잠금과 행 수준 잠금을 지원하는 반면 MyISAM은 테이블 수준 잠금만 지원합니다.
외래 키: InnoDB는 외래 키를 지원합니다.
백업: InnoDB는 핫 백업을 지원하지만 도구가 필요합니다.
Crash 복구: MyISAM 충돌 후 손상 확률은 InnoDB보다 훨씬 높으며 복구 속도도 느립니다.
기타 기능: MyISAM은 전체 텍스트 인덱싱, 압축, 공간 기능 및 기타 기능을 지원합니다.

백업 유형

콜드 백업: mysql 서비스를 꺼야 하며 읽기 및 쓰기 요청은 허용되지 않습니다.
웜 백업: 서비스는 온라인이지만 읽기 요청만 지원됩니다. 쓰기 요청은 허용되지 않습니다.
핫 백업: 백업 중에는 비즈니스에 영향을 미치지 않습니다.

Index

Index("키"라고도 함)는 레코드를 빠르게 찾기 위해 스토리지 엔진에서 사용하는 데이터 구조입니다.

B-트리 인덱스

대부분의 mysql 엔진은 이 인덱스를 지원합니다.

"B-Tree"라는 용어가 사용되더라도 다른 스토리지 엔진은 다른 스토리지 구조를 사용할 수 있습니다. NDB 클러스터 스토리지 엔진은 실제로 내부적으로 T-Tree를 사용하는 반면 InnoDB는 B+Tree를 사용합니다.

B-트리 인덱스는 스토리지 엔진이 필요한 데이터를 얻기 위해 전체 테이블 스캔을 수행할 필요가 없기 때문에 데이터 액세스 속도를 높일 수 있습니다. 대신 인덱스의 루트 노드부터 검색을 시작하므로 검색 속도가 훨씬 빨라집니다. 더 빠르게.

B-Tree는 인덱스 컬럼을 순차적으로 정리하여 저장하는데, 이는 범위 데이터 검색에 매우 적합합니다. 인덱스 트리는 정렬되어 있기 때문에 사용자 검색 외에도 정렬 및 그룹화에도 사용할 수 있습니다.

여러 열을 인덱스 열로 지정할 수 있으며, 여러 인덱스 열이 함께 인덱스 키를 형성합니다. B-Tree 인덱스는 전체 키 값, 키 값 범위 또는 키 접두사 검색에 적합하며, 여기서 키 접두사 검색은 가장 왼쪽 접두사 기반 검색에만 적용됩니다. 검색은 인덱스의 가장 왼쪽 열부터 시작해야 합니다.

B-Tree 인덱스의 데이터 구조

B-Tree

B-Tree를 설명하기 위해 먼저 데이터 레코드를 튜플 [키, 데이터]로 정의하고, 키를 레코드의 키 값으로 정의합니다. , 다른 경우 데이터 레코드와 키는 서로 다르며 데이터는 키를 제외한 데이터 레코드의 데이터입니다.

모든 노드의 깊이가 동일합니다. 이는 B-Tree가 균형을 이루고 있음을 의미합니다.
노드의 키는 왼쪽에서 오른쪽으로 감소하지 않고 배열됩니다.
포인터의 왼쪽 및 오른쪽 인접 키가 각각 keyi 및 keyi+1이고 null이 아닌 경우 포인터가 가리키는 노드의 모든 키는 keyi보다 크거나 같고 같거나 작습니다. keyi+1과 같습니다.

검색 알고리즘: 먼저 루트 노드에 대해 이진 검색을 수행하고, 발견되면 해당 노드의 데이터를 반환합니다. 그렇지 않으면 해당 간격에서 포인터가 가리키는 노드를 재귀적으로 검색합니다.

새 데이터 레코드를 삽입하고 삭제하면 B-Tree의 속성이 파괴되므로 삽입 및 삭제할 때 B-Tree의 속성을 유지하려면 트리를 분할, 병합, 회전하는 등의 작업을 수행해야 합니다.

MYSQL_다중 버전 동시성 제어, 스토리지 엔진 및 인덱싱 소개

B+Tree

B-Tree와 비교하여 B+Tree는 다음과 같은 특징을 갖습니다.

각 노드의 포인터 상한은 2d+1이 아닌 2d입니다(d는 B입니다) -나무의 지출).
내부 노드는 데이터를 저장하지 않으며, 외부 노드는 포인터만 저장하지 않습니다.

MYSQL_다중 버전 동시성 제어, 스토리지 엔진 및 인덱싱 소개

순차 액세스 포인터가 있는 B+Tree

데이터베이스 시스템이나 파일 시스템에서 일반적으로 사용되는 B+Tree 구조는 클래식 B+Tree를 기반으로 최적화되어 순서 액세스 포인터가 추가되었습니다.

MYSQL_다중 버전 동시성 제어, 스토리지 엔진 및 인덱싱 소개

이 최적화의 목적은 간격 액세스에 대한 성능을 제공하는 것입니다. 예를 들어 그림에서 키가 18부터 49까지인 모든 레코드를 쿼리하려는 경우입니다.

장점

레드-블랙 트리와 같은 균형 트리를 사용하여 인덱스를 구현할 수도 있지만 파일 시스템과 데이터베이스 시스템은 일반적으로 다음 두 가지 이유로 B-Tree를 인덱스 구조로 사용합니다.

더 나은 검색 시간: 균형 트리에서 데이터 검색의 시간 복잡도는 트리 높이 h와 같고 트리 높이는 대략 O(h) = O(logN)입니다. 여기서 d는 각 노드의 진출 차수입니다. 레드-블랙 트리의 아웃 차수는 2인 반면, B-트리의 아웃 차수는 일반적으로 매우 큽니다. 레드-블랙 트리의 트리 높이 h는 분명히 B-트리의 트리 높이보다 훨씬 높습니다. 그래서 검색량이 더 많아졌네요. B+Tree는 B-Tree보다 외부 메모리 인덱스에 더 적합합니다. B+Tree의 노드에서 데이터 필드가 제거되어 더 큰 out-degree를 가질 수 있고 검색 효율성이 더 높기 때문입니다.
컴퓨터 미리 읽기 기능 사용: 디스크 I/O를 줄이기 위해 디스크는 필요할 때 엄격하게 읽지 않고 매번 미리 읽는 경우가 많습니다. 이에 대한 이론적 근거는 컴퓨터 과학에서 잘 알려진 지역성 원칙입니다. 즉, 데이터 조각이 사용될 때 일반적으로 근처의 데이터가 즉시 사용됩니다. 미리 읽기 과정에서 디스크는 순차적으로 읽습니다. 순차 읽기는 디스크 탐색이 필요하지 않으며 회전 시간도 짧아서 속도가 매우 빠릅니다. 운영체제는 일반적으로 메모리와 디스크를 견고한 크기의 블록으로 나누고, 각 블록을 페이지라고 하며, 메모리와 디스크는 페이지 단위로 데이터를 교환합니다. 데이터베이스 시스템은 인덱스에 있는 노드의 크기를 페이지 크기에 맞춰 설정하므로 하나의 I/O에 해당 노드를 완전히 로드할 수 있고, 미리 읽기 기능을 사용하여 인접한 노드도 미리 로드할 수 있습니다.

참조: MySQL 인덱스의 데이터 구조 및 알고리즘 원리

해시 인덱스

InnoDB 엔진에는 "적응형 해시 인덱스"라는 특수 기능이 있습니다. 인덱스 값이 매우 자주 사용되는 경우 B+Tree 인덱스 위에 해시 인덱스가 생성되므로 B+Tree 인덱스는 해시 인덱스의 몇 가지 장점을 갖습니다. 빠른 해시 조회와 같은 것입니다.

해시 인덱스는 O(1) 시간 내에 검색할 수 있지만, 다음과 같은 제한 사항이 있습니다.

Ha A 해시 인덱스에는 해시 값과 행 포인터만 포함되어 있고 필드 값은 저장되지 않으므로 행 손실을 방지하기 위해 인덱스의 값을 사용할 수 없습니다.
은 정렬 및 그룹화에 사용할 수 없습니다.
은 정밀 검색만 지원하며 부분 검색, 범위 검색에는 사용할 수 없습니다.
해시 충돌이 발생하면 스토리지 엔진은 연결된 목록의 모든 행 포인터를 순회해야 합니다.