[sql] 데이터베이스 인덱싱은 어떻게 작동합니까? [닫은]

왜 필요한가요?

데이터가 디스크 기반 저장 장치에 저장되면 데이터 블록으로 저장됩니다. 이 블록들은 전체적으로 액세스되어 원자 디스크 액세스 작업이됩니다. 디스크 블록은 링크 된 목록과 거의 같은 방식으로 구성됩니다. 둘 다 데이터 섹션을 포함하고 다음 노드 (또는 블록)의 위치에 대한 포인터를 포함하며 둘 다 연속적으로 저장할 필요는 없습니다.

여러 레코드를 한 필드에서만 정렬 할 수 있기 때문에 정렬되지 않은 필드를 검색하려면 N/2블록 액세스 (평균) 가 필요한 선형 검색이 필요 하며 여기서 N블록 수는 테이블이 확장됩니다. 해당 필드가 키가 아닌 필드 인 경우 (즉, 고유 한 항목을 포함하지 않는 경우) N블록 액세스 시 전체 테이블 스페이스를 검색해야합니다 .

정렬 된 필드의 경우 이진 검색을 사용할 수 log2 N있으며이 액세스 는 차단됩니다. 또한 키가 아닌 필드를 기준으로 데이터가 정렬되므로 더 높은 값을 찾으면 나머지 테이블에서 중복 값을 검색 할 필요가 없습니다. 따라서 성능이 크게 향상됩니다.

인덱싱이란 무엇입니까?

인덱싱은 여러 필드에서 여러 레코드를 정렬하는 방법입니다. 테이블의 필드에서 인덱스를 작성하면 필드 값을 보유하는 다른 데이터 구조와 관련 레코드에 대한 포인터가 작성됩니다. 그런 다음이 인덱스 구조를 정렬하여 이진 검색을 수행 할 수 있습니다.

인덱싱의 단점은 MyISAM 엔진을 사용하여 테이블에 인덱스가 함께 저장되므로 인덱스에 디스크에 추가 공간이 필요하다는 것입니다. 동일한 테이블 내의 많은 필드가 인덱스 된 경우이 파일은 기본 파일 시스템의 크기 제한에 빠르게 도달 할 수 있습니다 .

어떻게 작동합니까?

먼저 샘플 데이터베이스 테이블 스키마를 간략히 설명하겠습니다.

필드 이름 데이터 형식 디스크 크기
id (기본 키) 부호없는 INT 4 바이트
firstName Char (50) 50 바이트
성 Char (50) 50 바이트
emailAddress Char (100) 100 바이트

참고 : 디스크 값에서 정확한 크기를 허용하기 위해 varchar 대신 char을 사용했습니다. 이 샘플 데이터베이스에는 5 백만 개의 행이 있으며 색인화되지 않았습니다. 이제 여러 쿼리의 성능이 분석됩니다. 이들은 id (정렬 된 키 필드)를 사용하는 쿼리 이고 firstName (키가 아닌 정렬되지 않은 필드)을 사용하는 쿼리 입니다.

예 1 – 분류되지 않은 필드 대 분류

r = 5,000,000고정 길이 의 레코드 레코드 샘플이 주어진 레코드 길이는 R = 204바이트이며 기본 블록 크기 B = 1,024바이트를 사용하는 MyISAM 엔진을 사용하여 테이블에 저장 됩니다. 테이블의 차단 요인은 bfr = (B/R) = 1024/204 = 5디스크 블록 당 레코드입니다. 테이블을 보유하는 데 필요한 총 블록 수는 N = (r/bfr) = 5000000/5 = 1,000,000블록입니다.

N/2 = 500,000id 필드가 키 필드 인 경우 id 필드에서 선형 검색을 수행하려면 값을 찾기 위해 평균 블록 액세스 가 필요 합니다. 그러나 id 필드도 정렬되므로 평균 log2 1000000 = 19.93 = 20블록 액세스가 필요한 이진 검색을 수행 할 수 있습니다 . 즉시 우리는 이것이 대폭 개선되었음을 알 수 있습니다.

이제 firstName 필드는 정렬되거나 키 필드가 아니므로 이진 검색이 불가능하거나 값이 고유하지 않으므로 테이블에서 정확한 N = 1,000,000블록 액세스 를 위해 끝까지 검색해야합니다 . 인덱싱이 바로이 상황입니다.

인덱스 레코드에 인덱싱 된 필드와 원래 레코드에 대한 포인터 만 포함되어 있으면 해당 레코드가 가리키는 다중 필드 레코드보다 작은 이유가 있습니다. 따라서 인덱스 자체에는 원래 테이블보다 적은 수의 디스크 블록이 필요하므로 반복되는 블록 액세스가 더 적습니다. firstName 필드 의 인덱스 스키마 는 다음과 같습니다.

필드 이름 데이터 형식 디스크 크기
firstName Char (50) 50 바이트
(레코드 포인터) 특수 4 바이트

참고 : MySQL의 포인터 길이는 테이블 크기에 따라 2, 3, 4 또는 5 바이트입니다.

예 2 – 색인

r = 5,000,000인덱스 레코드 길이가 R = 54바이트이고 기본 블록 크기 B = 1,024바이트를 사용하는 레코드 의 샘플 데이터베이스가 제공 됩니다. 인덱스의 차단 요소는 bfr = (B/R) = 1024/54 = 18디스크 블록 당 레코드입니다. 인덱스를 보유하는 데 필요한 총 블록 수는 N = (r/bfr) = 5000000/18 = 277,778블록입니다.

firstName 필드를 사용한 검색 은 색인을 사용하여 성능을 향상시킬 수 있습니다. 이를 통해 평균 log2 277778 = 18.08 = 19블록 액세스로 인덱스를 이진 검색 할 수 있습니다 . 읽기 위해 추가 블록 액세스가 필요한 실제 레코드의 주소를 찾으려면 총 인덱스 19 + 1 = 20액세스를 가져 오십시오 . 인덱스되지 않은 테이블에서 firstName 일치 를 찾는 데 필요한 1,000,000 블록 액세스와는 거리가 멀 습니다.

언제 사용해야합니까?

인덱스를 만들려면 추가 디스크 공간이 필요하고 (위의 예에서 277,778 개의 블록이 추가로 ~ 28 % 증가) 너무 많은 인덱스가 파일 시스템 크기 제한으로 인해 문제를 일으킬 수 있으므로 올바른 선택을 위해 신중한 생각을 사용해야합니다 색인 할 필드.

인덱스는 레코드 내에서 일치하는 필드를 빠르게 검색하는 데만 사용되므로 출력에만 사용되는 인덱싱 필드는 단순히 삽입 또는 삭제 작업을 수행 할 때 디스크 공간과 처리 시간을 낭비하게되므로 추론 할 수 있습니다. 피해야한다. 또한 이진 검색의 특성상 데이터의 카디널리티 또는 고유성이 중요합니다. 카디널리티가 2 인 필드에서 인덱싱하면 데이터가 절반으로 분할되고 카디널리티가 1,000이면 약 1,000 개의 레코드가 반환됩니다. 이와 같은 카디널리티가 낮 으면 효율성이 선형 정렬로 감소하고 카디널리티가 레코드 수의 30 % 미만인 경우 쿼리 최적화 프로그램은 인덱스를 공간 낭비로 효과적으로 만들 수 있습니다.

답변

SELECT * FROM Employee
WHERE Employee_Name = 'Abc'

SELECT * FROM User
WHERE Name = 'John'

CREATE INDEX name_index
ON User (Name)