MySQL 인덱스 구조에 대해 이야기해보자

이 기사에서는 mysql에 대한 관련 지식을 제공합니다. MySQL의 공식 정의는 인덱스(Index)가 MySQL이 데이터를 효율적으로 얻는 데 도움이 되는 데이터 구조라는 것입니다. 아래에서 인덱스는 데이터 구조입니다. 한번 살펴보시고 모든 분들께 도움이 되었으면 좋겠습니다.

추천 학습: mysql 비디오 튜토리얼

소개

데이터 외에도 데이터베이스 시스템은 특정 검색 알고리즘을 만족하는 데이터 구조를 유지합니다. 이러한 데이터 구조는 어떤 방식으로든 데이터를 참조(지시)합니다. 이러한 데이터 구조에 고급 검색 알고리즘을 구현할 수 있습니다. 이 데이터 구조는 인덱스입니다.

일반적으로 인덱스 자체도 매우 크고 메모리에 완전히 저장할 수 없기 때문에 인덱스 파일 형태로 디스크에 저장되는 경우가 많습니다.

장점:

1. 대학 도서관에서 서지 색인을 구축하는 것과 유사하게 데이터 검색 효율성을 높이고 데이터베이스의 IO 비용을 줄입니다.

2. 인덱스 열을 통해 데이터를 정렬하여 데이터 정렬 비용을 줄이고 CPU 소비를 줄입니다.

단점:

1. 인덱스를 사용하면 쿼리 속도가 크게 향상되지만 테이블에 대한 INSERT, UPDATE, DELETE 등의 업데이트 속도도 느려집니다. 테이블을 업데이트할 때 MySQL은 데이터를 저장할 뿐만 아니라 인덱스 파일도 저장해야 하기 때문입니다. 인덱스 컬럼을 추가하는 필드가 업데이트될 때마다 업데이트로 인한 키 값 변경 후 인덱스 정보가 조정됩니다.

2. 실제로 테이블은 기본 키와 인덱스 필드를 저장하고 엔터티 테이블의 레코드를 가리키므로 인덱스 열도 공간을 차지합니다. 인덱스로서의 트리 구조)

왼쪽은 총 2개의 열과 7개의 레코드로 구성된 데이터 테이블입니다. 가장 왼쪽은 데이터 레코드의 물리적 주소입니다.

Col2 검색 속도를 높이기 위해 오른쪽과 같이 이진 검색 트리를 유지할 수 있습니다. 각 노드에는 인덱스 키 값과 해당 데이터 레코드의 물리적 주소에 대한 포인터가 포함되어 있습니다. 이진 검색을 이용하면 특정 복잡도 내에서 해당 데이터를 얻을 수 있어 조건에 맞는 레코드를 빠르게 검색할 수 있습니다.

인덱스 구조(트리)

인덱스를 통해 데이터베이스 테이블 쿼리 속도를 높이는 방법은 무엇입니까? 설명의 편의를 위해 다음 두 가지 쿼리 요구 사항만 포함하도록 데이터베이스 테이블을 제한합니다.

1, id=1234인 사용자에서 * 선택;

2, id>1234 및 id<인 사용자에서 *선택 ;2345 ;(간격별)

해시 테이블 대신 트리를 사용하는 이유

값별 해시 테이블 쿼리의 성능은 매우 좋고 시간 복잡도는 O(1)이지만 빠른 데이터 검색을 지원하지 못합니다. 간격으로 인해 요구 사항을 충족할 수 없습니다. 마찬가지로 균형 이진 탐색 트리의 질의 성능은 매우 높지만 시간 복잡도는 O(logn)이고 트리의 순차 순회는 정렬된 데이터 시퀀스를 출력할 수 있지만 요구 사항을 충족할 수는 없습니다. 간격에 따라 데이터를 빠르게 찾습니다.

간격에 따른 데이터의 빠른 검색을 지원하기 위해 이진 검색 트리를 변환하고 연결 목록으로 이진 검색 트리의 리프 노드를 문자열로 묶습니다. 간격의 시작 값을 사용합니다. 순서화된 연결 목록에서 노드를 찾은 후 이 노드에서 시작하여 순서화된 연결 목록의 노드 데이터 값이 간격 끝보다 클 때까지 순회합니다. 값.

그리고 트리에 대한 많은 작업의 시간 복잡도는 트리 높이에 비례하기 때문에 트리 높이를 줄이면 디스크 IO 작업이 줄어들 수 있습니다. 따라서 우리는 m-ary 트리(m>2)에 인덱스를 구축합니다. 자세한 내용은 다음 문서를 참조하세요.

BTree Index

B+ 트리를 소개하기 전에 먼저 B 트리에 대해 알아보겠습니다.

1. 초기화 소개

B-트리에서 연한 파란색 블록을 디스크 블록이라고 합니다. 각 디스크 블록에는 여러 데이터 항목(짙은 파란색으로 표시)과 포인터((그림에 표시)가 포함되어 있습니다. 노란색), 예를 들어 디스크 블록 1에는 데이터 항목 17과 35가 포함되어 있으며 포인터 P1, P2 및 P3이 포함되어 있습니다. P1은 17보다 작은 디스크 블록을 나타내고, P2는 17에서 35 사이의 디스크 블록을 나타내며, P3은 35보다 큰 디스크 블록을 나타냅니다.

참고:

실제 데이터는 리프 노드, 즉 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99에만 존재합니다. (그리고 여러 개의 데이터로 구성된 데이터 간격입니다: 3~5,...,90~99)

논리프 노드는 실제 데이터를 저장하지 않고 검색 방향을 안내하는 데이터 항목만 저장합니다. 예를 들어 17과 35는 데이터 테이블에 실제로 존재하지 않습니다.

2. 검색 과정

데이터 항목 29를 찾으려면 먼저 디스크에서 디스크 블록 1을 메모리로 로드합니다. 이때 메모리에서 이진 검색을 사용하여 29가 17과 35 사이에 있는지 확인합니다. 디스크 블록 1의 P2 포인터를 잠급니다. (디스크 IO에 비해) 매우 짧기 때문에 메모리 시간은 무시할 수 있습니다. 디스크 블록 3은 디스크 블록 1의 P2 포인터의 디스크 주소를 통해 디스크에서 메모리로 로드됩니다. 두 번째 IO가 발생하는데, 29는 26과 30 사이이다. 디스크 블록 3의 P2 포인터를 잠그고, 이 포인터를 통해 디스크 블록 8을 메모리에 로드하며, 동시에 세 번째 IO가 발생한다. 29개를 찾기 위해 메모리를 검색하고 총 3개의 IO가 종료됩니다.

B+Tree index

B+트리는 B트리와 유사하며, B+트리는 B트리의 개선된 버전입니다. 즉, m-fork 검색 트리와 순서 연결 리스트로 구성된 트리가 B+ 트리이며, 이는 저장될 트리 인덱스입니다.

그림과 같이 B+ 트리의 주요 차이점은 다음과 같습니다. B-트리는 다음 두 지점입니다.

1. B+ 트리의 리프 노드는 연결 목록을 사용하여 직렬로 연결됩니다. 특정 간격의 데이터를 찾으려면 트리에서 검색할 간격의 시작 값만 사용하면 됩니다. 순서 연결 리스트에서 노드를 찾은 후 이 노드에서 시작하여 다음까지 순서 연결 리스트를 따라 뒤로 이동합니다. 순서 연결 목록의 노드 데이터 값이 간격 끝 값보다 큽니다.

2. B+ 트리의 모든 노드는 실제 데이터를 저장하지 않고 인덱싱에만 사용됩니다. B-트리는 리프 노드를 통해 직접 데이터를 얻는 반면, B+ 트리의 각 리프 노드에는 데이터 행의 키 값과 주소 정보가 저장되어 있으며, 특정 리프 노드를 쿼리하면 리프 노드의 주소를 통해 실제 데이터 정보를 찾습니다. .

클러스터드 인덱스와 비클러스터드 인덱스

클러스터드 인덱스는 별도의 인덱스 종류가 아닌 데이터를 저장하는 방식입니다. '클러스터형'이라는 용어는 데이터 행과 인접한 키 값 클러스터를 함께 저장하는 것을 의미합니다.

클러스터드 인덱스의 장점:

클러스터드 인덱스 배열 순서에 따라 일정 범위의 데이터를 조회하고 표시할 때 데이터가 밀접하게 연결되어 있으므로 데이터베이스가 여러 데이터 블록에서 데이터를 추출할 필요가 없으므로 IO가 많이 작동합니다.

클러스터형 인덱스의 제한 사항:

1. mysql 데이터베이스의 경우 현재 innodb 데이터 엔진만 클러스터형 인덱스를 지원하고 Myisam은 클러스터형 인덱스를 지원하지 않습니다.

2. 데이터에 대한 물리적 저장소 정렬 방법은 하나만 있을 수 있으므로 각 MySQL 테이블에는 클러스터형 인덱스가 하나만 있을 수 있습니다. 일반적으로 테이블의 기본 키입니다.

3. 클러스터형 인덱스의 클러스터링 특성을 최대한 활용하려면 innodb 테이블의 기본 키 열에 순서가 지정된 순차 ID를 사용하는 것이 가장 좋습니다. uuid와 같이 순서가 지정되지 않은 ID는 사용하지 않는 것이 좋습니다.

아래 그림과 같이 왼쪽의 인덱스는 디스크의 데이터 행 배열이 인덱스 정렬과 일치하므로 클러스터형 인덱스입니다.

인덱스 분류

단일 값 인덱스

즉, 인덱스는 단일 열만 포함하고 테이블은 여러 개의 단일 열 인덱스를 가질 수 있습니다.

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
 
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name  on customer;

고유 인덱스

인덱스 열의 값은 고유해야 합니다. , 그러나 null 값은 허용됩니다

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
  
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

기본 키 인덱스

기본 키를 설정한 후 데이터베이스는 자동으로 인덱스를 생성합니다. Innodb는 클러스터형 인덱스입니다

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (
id INT(10) UNSIGNED   ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
 单独建主键索引：
ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer 
 drop PRIMARY KEY ;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

복합 인덱스

즉, 인덱스에는 여러 열이 포함됩니다.

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

성능 분석

인덱스 생성 시나리오

어떤 상황에서 인덱스를 생성해야 하는지

1. 기본 키는 자동으로 고유 인덱스를 생성합니다

2. indexed

3. 쿼리의 다른 테이블과 외래 키 관계에 연결된 필드가 인덱스됩니다

4. Single 키/조합 인덱스 선택 문제, 조합 인덱스가 쿼리의 필드 정렬에 더 효과적입니다. , 인덱스를 통해 정렬 필드에 액세스하면 정렬 속도가 크게 향상됩니다

6. 쿼리의 통계 또는 그룹화 필드

필요하지 않은 경우 인덱스 생성

1.

2. 자주 추가, 삭제, 수정되는 테이블이나 필드 이유: 쿼리 속도는 향상되지만 동시에 테이블에 대한 INSERT, UPDATE, DELETE 등의 테이블 업데이트 속도가 느려집니다. 테이블을 업데이트할 때 MySQL은 데이터뿐만 아니라 인덱스 파일도 저장해야 하기 때문입니다

3. Where 조건에서 사용되지 않는 필드는 인덱스를 생성하지 않습니다

4. 필터링이 약한 필드는 빌드에 적합하지 않습니다. indexes

추천 학습:

mysql 비디오 튜토리얼

위 내용은 MySQL 인덱스 구조에 대해 이야기해보자의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!