mysql innodb 인덱스 원리에 대한 자세한 소개(코드 예)-MySQL 튜토리얼-php.cn

이 기사는 mysql innodb 인덱스 원리에 대한 자세한 소개(코드 예제)를 제공합니다. 이는 특정 참조 가치가 있으므로 도움이 될 수 있습니다.

Clustered index

Innodb 스토리지 엔진 테이블은 인덱스로 구성된 테이블이며, 테이블의 데이터는 기본 키 순서로 저장됩니다. 클러스터형 인덱스는 각 테이블의 기본 키 순서대로 B+ 트리를 구성하고, 리프 노드는 테이블 전체의 행 레코드 데이터를 저장하며, 이들 리프 노드는 데이터 페이지가 된다. (관련 권장 사항: MySQL 튜토리얼)

클러스터형 인덱스의 저장은 물리적으로 연속적이지 않고 논리적으로 연속적입니다. 리프 노드는 기본 키 순서로 정렬되고 이중 연결 목록을 통해 연결됩니다. 대부분의 경우 쿼리 최적화 프로그램에서는 클러스터형 인덱스를 사용하는 경향이 있는데, 클러스터형 인덱스는 리프 노드에서 직접 데이터를 찾을 수 있고, 데이터의 논리적 순서가 정의되어 있기 때문에 범위 값에 대한 쿼리에 매우 빠르게 접근할 수 있기 때문입니다.

클러스터형 인덱스의 이 기능은 인덱스 구성 테이블의 데이터도 인덱스의 일부인지 확인합니다. 테이블의 데이터는 B+ 트리에 따라서만 정렬할 수 있으므로 테이블에는 클러스터형 인덱스가 하나만 있을 수 있습니다.

Innodb에서는 기본적으로 클러스터형 인덱스가 기본 키 인덱스입니다. 기본 키가 없는 경우 다음 규칙에 따라 클러스터형 인덱스를 만듭니다.

기본 키가 없으면 비어 있지 않은 고유 인덱스 열이 기본 키로 사용되어 이 테이블의 클러스터형 인덱스가 됩니다. ;
그러한 인덱스가 없으면 InnoDB는 기본 키를 클러스터형 인덱스로 암시적으로 정의합니다.

기본키는 클러스터형 인덱스를 사용하기 때문에 기본키가 자동증가형 ID라면 해당 데이터가 디스크에 인접하게 저장되어 쓰기 성능이 높아집니다. uuid와 같은 문자열 형태인 경우 삽입을 자주 하게 되면 innodb가 디스크 블록을 자주 이동하게 되어 쓰기 성능이 상대적으로 저하됩니다.

B+ 트리(다방향 균형 검색 트리)

innodb 엔진 인덱스가 B+ 트리 구조를 사용한다는 것을 알고 있는데 이진 트리와 같은 다른 유형의 트리 구조는 어떻습니까?

RMB 유통의 최소 단위가 센트인 것처럼 컴퓨터에 데이터를 저장할 때 최소 저장 단위가 있습니다. 파일 시스템의 가장 작은 단위는 블록입니다. (이 값은 시스템에 따라 다르며 설정 가능) InnoDB 스토리지 엔진에도 자체 최소 저장 단위인 페이지(Page)가 있습니다. 페이지 크기는 16K입니다(이 값도 구성 가능).

파일 시스템의 파일은 크기가 1바이트에 불과하지만 디스크에서는 4KB의 공간을 차지해야 합니다. 마찬가지로 innodb의 모든 데이터 파일 크기는 항상 16384(16k)의 정수배입니다.

mysql innodb 인덱스 원리에 대한 자세한 소개(코드 예)

따라서 MySQL에서는 인덱스를 저장하는 블록 노드가 16K를 차지하고 MySQL의 각 IO 작업은 시스템의 미리 읽기 기능을 사용하여 한 번에 16K를 로드합니다. 이렇게 이 노드에 하나의 인덱스 값만 넣는 것은 매우 낭비적이다. 한 번에 하나의 인덱스 값만 얻을 수 있으므로 이진 트리를 사용할 수 없기 때문이다.

B+ 트리는 다중 방향 검색 트리입니다. 하나의 노드는 n 값, n = 16K/각 인덱스 값의 크기를 보유할 수 있습니다.
예를 들어 인덱스 필드 크기가 1Kb인 경우 각 노드는 이론적으로 16개의 인덱스 값을 저장할 수 있습니다. 이 경우 이진 트리는 IO당 하나의 인덱스 값만 로드할 수 있지만 B+ 트리는 16개를 로드할 수 있습니다.

B+ 트리의 방법 수는 n+1입니다. 여기서 n은 각 노드에 존재하는 값의 수입니다. 예를 들어 각 노드는 16개의 값을 저장하므로 이 트리에는 17개의 방법이 있습니다.

B+ 트리 노드는 여러 값을 저장할 수 있으므로 B+ 트리 인덱스는 주어진 키 값을 가진 특정 행을 찾을 수 없다는 것도 여기서 알 수 있습니다. B+ 트리는 데이터 행이 저장된 특정 페이지만 찾은 다음 해당 페이지를 메모리로 읽어온 다음 메모리에서 지정된 데이터를 검색할 수 있습니다.

첨부: B-트리와 B+ 트리의 차이점은 B+ 트리의 리프가 아닌 노드에는 탐색 정보만 포함되고 실제 값은 포함되지 않는다는 점입니다. 모든 리프 노드와 연결된 노드는 연결 목록을 사용하여 연결되므로 간격 검색이 용이합니다. 그리고 횡단.

보조 인덱스

는 비클러스터형 인덱스라고도 합니다. 리프 노드에는 행 레코드의 모든 데이터가 포함되어 있지 않습니다. 리프 노드에는 키 값 외에도 각 리프의 인덱스 행에 대한 책갈피가 포함되어 있습니다. 북마크는 해당 행의 클러스터형 인덱스 키입니다.

다음 그림은 보조 인덱스와 클러스터형 인덱스 간의 관계를 나타낼 수 있습니다(그림은 인터넷에서 가져온 것이므로 일반적인 의미만 보세요).

mysql innodb 인덱스 원리에 대한 자세한 소개(코드 예)

보조 인덱스를 통해 데이터를 검색할 때 innodb 저장소 엔진은 보조 인덱스 리프 노드를 통해 이를 얻습니다. 기본 키 인덱스의 기본 키를 원하는 다음 기본 키 인덱스를 통해 전체 행 레코드를 찾습니다.

예를 들어 높이가 3인 보조 인덱스 트리에서 데이터를 검색하려면 지정된 기본 키를 찾기 위해 보조 인덱스 트리에서 3번의 IO를 수행해야 합니다. 또한 3번의 경우 클러스터형 인덱스 트리에서 IO를 수행해야 하며 3번의 검색 후에 전체 데이터 행이 포함된 페이지가 최종 발견되므로 최종 데이터 페이지를 얻으려면 총 6번의 IO 액세스가 필요합니다.

공동 인덱스, 고유 인덱스 등 생성되는 인덱스는 모두 Non-Clustered 인덱스입니다.

조인트 인덱스

조인트 인덱스는 테이블의 여러 열을 인덱싱하는 것을 말합니다. 조인트 인덱스도 B+ 트리인데, 차이점은 조인트 인덱스에 포함된 키 값의 개수가 1이 아니라 2보다 크거나 같다는 점입니다.

예를 들어, id, age, name 필드가 있는 사용자 테이블이 있는데, 이제 다음 두 SQL이 가장 자주 사용되는 것으로 나타났습니다.

Select * from user where age = ？ ;
Select * from user where age = ? and name = ?;

로그인 후 복사

이때에는 두 개의 별도 인덱스를 만들 필요가 없습니다. age 및 name 다음과 같은 공동 인덱스만 작성하면 됩니다. 하지만:

create index idx_age_name on user(age, name)

로그인 후 복사

공동 인덱스의 또 다른 이점은 두 번째 키 값이 정렬되어 있어 때로는 추가 정렬 작업을 피할 수 있다는 것입니다.

커버링 인덱스

커버링 인덱스는 클러스터형 인덱스의 레코드를 쿼리하지 않고도 쿼리에 필요한 모든 필드 값을 보조 인덱스에서 얻을 수 있다는 의미입니다. Covering Index의 장점은 보조 인덱스가 전체 행 레코드의 모든 정보를 포함하지 않기 때문에 클러스터형 인덱스에 비해 크기가 훨씬 작기 때문에 많은 IO 작업을 줄일 수 있다는 점입니다.

예를 들어 위에 공동지수(나이, 이름)가 있다면 다음과 같다면

select age，name from user where age=？

로그인 후 복사

커버링 지수를 활용하시면 됩니다.

인덱스를 다루는 또 다른 이점은 다음과 같은 통계 문제에 대한 것입니다.

select count(*) from user

로그인 후 복사

innodb 스토리지 엔진은 통계를 위해 클러스터형 인덱스를 쿼리하도록 선택하지 않습니다. 사용자 테이블에는 보조 인덱스가 있는데, 보조 인덱스는 클러스터형 인덱스에 비해 크기가 훨씬 작기 때문에 보조 인덱스를 선택하면 IO 작업을 줄일 수 있습니다.

주의 사항

중복 인덱스가 아닌 적절한 인덱스만 구축하세요

데이터가 추가되거나 삭제될 때마다 B+ 트리를 조정해야 하기 때문에 여러 개의 인덱스가 생성되면 여러 B+ 트리를 조정해야 하며, 트리가 많아질수록 조정이 필요합니다. 구조가 클수록 이러한 조정에 더 많은 시간과 리소스가 소요됩니다. 이러한 불필요한 인덱스를 줄이면 디스크 사용량이 크게 줄어들 수 있습니다.