mysql 인덱스에 대한 자세한 설명(요약)-MySQL 튜토리얼-php.cn

mysql 인덱스에 대한 자세한 설명(요약)

위의 "mysql 실행 프로세스 분석"에서는 주로 서버 계층의 SQL 문 실행 프로세스를 소개했습니다.

Index-를 따르는 엔진 계층의 특정 문 실행 단계를 분석해 보겠습니다. 관련하여 먼저 인덱스에 대해 알아보겠습니다

Index

인덱스의 등장은 사실 책의 목차처럼 데이터 쿼리의 효율성을 높이기 위한 것입니다

데이터 구조

일반적인 데이터 구조에는 해시가 포함됩니다 테이블, 순서배열 및 검색트리

해시 테이블은 키-값(key-value)에 데이터를 저장하는 구조인데, 찾고자 하는 값, 즉 키만 입력하면 해당 값을 찾을 수 있습니다. , 이는 값입니다. 해싱의 개념은 매우 간단합니다. 배열에 값을 넣고 해시 함수를 사용하여 키를 위치로 변환한 다음 배열의 해당 위치에 값을 넣습니다. 필연적으로 여러 개의 키 값이 있습니다. 해시 함수를 거치면 동일한 값이 나타납니다. 이 상황을 처리하는 한 가지 방법은 연결된 목록을 꺼내는 것입니다

이 구조는 동등 쿼리만 있는 시나리오에 적합합니다.

순서 배열의 성능은 동등 쿼리 시나리오와 범위 쿼리 시나리오 모두에서 동일합니다. . 매우 훌륭합니다

쿼리 효율성만 본다면 정렬된 배열이 매우 좋습니다. 하지만, 데이터를 갱신해야 할 경우, 중간에 레코드를 모두 옮겨야 하기 때문에 비용이 너무 많이 듭니다. 특징은 각 노드의 왼쪽 아들이 부모 노드보다 작고, 부모 노드가 오른쪽 아들보다 작다는 것입니다. 물론 O(log(N))의 쿼리 복잡도를 유지하려면 다음이 필요합니다. 트리를 균형 잡힌 이진 트리로 유지합니다. 이를 보장하기 위해 업데이트의 시간 복잡도도 O(log(N))

이진 트리가 가장 효율적인 검색이지만 실제로 대부분의 데이터베이스 저장소는 이진 트리를 사용하지 않습니다. 그 이유는 인덱스가 메모리에만 존재하는 것이 아니라 디스크에도 기록되기 때문입니다. 쿼리가 가능한 한 적은 수의 디스크를 읽으려면 쿼리 프로세스가 가능한 한 적은 수의 데이터 블록에 액세스해야 합니다. 그러면 이진 트리가 아닌 "N-ary" 트리를 사용해야 합니다. 여기서 "N-ary" 트리의 "N"은 데이터 블록의 크기에 따라 달라집니다.

N-ary 트리는 읽기 및 쓰기 성능 이점과 디스크 액세스 패턴 적응으로 인해 데이터베이스 엔진에서 널리 사용되었습니다.

InnoDB의 인덱스 모델

InnoDB에서는 기본 키 순서에 따라 테이블이 인덱스 형태로 저장됩니다. 이렇게 저장된 테이블을 인덱스 구성 테이블이라고 합니다. InnoDB는 B+ 트리 인덱스 모델을 사용하므로 데이터는 B+ 트리에 저장됩니다

각 인덱스는 InnoDB의 B+ 트리에 해당합니다

리프 노드의 내용에 따라 인덱스 유형은 기본 키 인덱스와 비키 인덱스로 구분됩니다 -기본 키 인덱스

기본 키 인덱스의 리프 노드에는 전체 데이터 행이 저장됩니다. InnoDB에서는 기본 키 인덱스를 클러스터형 인덱스라고도 합니다. 기본 키가 아닌 인덱스의 리프 노드 내용은 기본 키 값입니다. InnoDB에서는 기본 키가 아닌 인덱스를 보조 인덱스라고도 합니다. 기본 키가 아닌 인덱스를 기반으로 하는 쿼리는 추가 인덱스 트리(테이블 반환)를 스캔해야 합니다. 따라서 애플리케이션에서는 기본 키 쿼리를 최대한 활용하도록 노력해야 합니다

인덱스 유지 관리

B+ 트리 인덱스의 순서를 유지하기 위해서는 새로운 값을 삽입할 때 필요한 유지 관리가 필요합니다

새로 삽입된 ID 값이 원래 값보다 크면 상대적으로 번거롭기 때문에 후속 데이터를 논리적으로 이동하여 공간을 확보해야 합니다

그리고 더 나쁜 상황은 데이터 페이지가 꽉 찼다는 것입니다. B+ 트리 알고리즘을 사용하려면 현재 데이터 페이지에서 새 알고리즘을 적용한 다음 일부 데이터를 그곳으로 이동해야 합니다. 이 프로세스를 페이지 분할이라고 합니다. 이 경우 성능은 당연히 저하됩니다.

페이지 분할 작업은 성능 외에도 데이터 페이지 활용도에 영향을 미칩니다. 원래 한 페이지에 있던 데이터가 이제 두 페이지로 분할되어 전체 공간 활용도가 약 50% 감소합니다.

물론 분할과 합병도 있을 겁니다. 인접한 두 페이지가 삭제된 데이터로 인해 활용도가 낮은 경우 데이터 페이지가 병합됩니다. 병합 프로세스는 분할 프로세스의 역 프로세스로 간주할 수 있습니다. 자동 증가 기본 키의 데이터 삽입 모드는 앞서 언급한 증분 삽입 시나리오와 일치합니다. 새 레코드가 삽입될 때마다 이는 추가 작업이며 다른 레코드 이동을 포함하지 않으며 리프 노드 분할을 트리거하지도 않습니다.

하지만 비즈니스 로직이 있는 필드를 기본 키로 사용하는 경우 순서대로 삽입하기가 쉽지 않은 경우가 많으므로 데이터 쓰기 비용이 상대적으로 높습니다

기본 키 길이가 짧을수록 리프 노드도 작아집니다. 일반 인덱스가 차지하는 공간도 작을수록

그래서 성능이나 저장 공간 측면에서 기본 키를 자동 증가시키는 것이 더 합리적인 선택인 경우가 많습니다

어떤 시나리오가 있을까요? 비즈니스 분야를 기본 키로 직접 사용하는 것이 적합합니까? 예를 들어 일부 비즈니스 시나리오 요구 사항은 다음과 같습니다.

1. 인덱스는 하나만 있습니다.

이것은 일반적인 KV 시나리오입니다

커버링 지수

실행된 문장이 t에서 ID를 선택하는 경우에는 ID의 값만 확인하면 되며, ID의 값은 이미 k 인덱스 트리에 있으므로 쿼리 결과를 테이블로 리턴하지 않고 바로 제공할 수 있습니다. 즉, 이 쿼리에서 인덱스 k는 쿼리 요구 사항을 "커버"했습니다. 커버 인덱스는 트리 검색 수를 줄이고 쿼리 성능을 크게 향상시킬 수 있으므로 커버 인덱스를 사용하는 것은 A입니다. 일반적인 성능 최적화 방법

index pushdown

가장 왼쪽 접두사 원칙이 충족되면 가장 왼쪽 접두사를 사용하여 인덱스에서 레코드를 찾을 수 있습니다. 이때, 가장 왼쪽의 접두어와 일치하지 않는 부분은 어떻게 되는지 궁금하실 것입니다.

MySQL 5.6에 도입된 인덱스 푸시다운 최적화는 인덱스 순회 과정에서 인덱스에 포함된 필드를 먼저 판단하고 조건에 맞지 않는 레코드를 직접 필터링하여 테이블 반환 횟수를 줄일 수 있습니다

왼쪽 접두사 원칙

인덱스의 모든 정의뿐만 아니라 가장 왼쪽의 접두사만 만족하면 인덱스를 사용하여 검색 속도를 높일 수 있습니다

공동 인덱스를 설정할 때 인덱스의 필드 순서를 어떻게 정렬하나요?

여기서 우리의 평가 기준은 인덱스 사용 능력의 복잡성입니다. 가장 왼쪽의 접두사를 지원할 수 있기 때문에 이미 (a, b)의 결합 인덱스가 있는 경우에는 일반적으로 a에 별도의 인덱스를 생성할 필요가 없습니다. 따라서 첫 번째 원칙은 순서를 조정하여 인덱스를 하나 적게 유지하면 이 순서가 우선순위가 필요한 경우가 많다는 것입니다. 인덱스로 정의할 수 있습니다. 기본적으로 인덱스를 생성하는 문에서 접두사 길이를 지정하지 않으면 인덱스에 전체 문자열이 포함됩니다

그러나 이로 인해 발생하는 손실은 동일한 인덱스에 더 많은 레코드가 필요하기 때문에 추가 레코드 검색 수가 늘어날 수 있다는 것입니다. 비교

접두사 인덱스를 사용하고 길이를 정의하면 추가 쿼리 비용을 많이 추가하지 않고도 공간을 절약할 수 있습니다.

인덱스에 몇 가지 다른 값이 있는지 계산하여 접두사를 얼마나 오래 사용할지 판단할 수 있습니다. 스캔 횟수

프리픽스 인덱스가 커버링 인덱스에 미치는 영향