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

질문자 :Xenph Yan

폐쇄 . 이 질문은 더 집중할 필요가 있습니다. 현재 답변을 받고 있지 않습니다.

---

이 질문을 개선하고 싶으십니까? 이 게시물 을 편집 하여 하나 의 문제 에만 초점 을 맞추도록 질문 을 업데이트 하십시오 .

폐쇄 2 년 전 .

이 질문 개선

데이터 세트의 크기가 증가함에 따라 인덱싱이 매우 중요하다는 점을 감안할 때 누군가 데이터베이스에 구애받지 않는 수준에서 인덱싱이 작동하는 방식을 설명할 수 있습니까?

필드를 인덱싱하는 쿼리에 대한 자세한 내용 은 데이터베이스 열을 인덱싱하는 방법 을 확인하세요.

왜 필요한가요?

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

많은 레코드가 하나의 필드에서만 정렬될 수 있기 때문에 정렬되지 않은 필드를 검색하려면 (N+1)/2 블록 액세스(평균)가 필요한 선형 검색이 필요하다고 말할 수 있습니다. 여기서 N 은 테이블에 걸쳐 있는 블록의 수입니다. 해당 필드가 키가 아닌 필드(즉, 고유 항목을 포함하지 않음)인 경우 N 블록 액세스에서 전체 테이블스페이스를 검색해야 합니다.

log2 N 블록 액세스가 있는 이진 검색을 사용할 수 있습니다. 또한 키가 아닌 필드가 주어지면 데이터가 정렬되므로 더 높은 값이 발견되면 나머지 테이블에서 중복 값을 검색할 필요가 없습니다. 따라서 성능 향상이 상당합니다.

인덱싱이란 무엇입니까?

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

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

어떻게 작동합니까?

먼저 샘플 데이터베이스 테이블 스키마의 개요를 살펴보겠습니다.

필드 이름 데이터 유형 디스크의 크기
id(기본 키) 부호 없는 INT 4바이트
firstName Char(50) 50바이트
성 문자(50) 50바이트
이메일 주소 문자(100) 100바이트

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

예 1 - 정렬된 필드와 정렬되지 않은 필드

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

id 필드에 대한 선형 검색은 id 필드가 키 필드인 경우 값을 찾기 위해 N/2 = 500,000 log2 1000000 = 19.93 = 20 블록 액세스가 필요한 바이너리 검색을 수행할 수 있습니다. 즉시 우리는 이것이 급격한 개선임을 알 수 있습니다.

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

인덱스 레코드에 인덱싱된 필드와 원래 레코드에 대한 포인터만 포함되어 있다는 점을 감안할 때 인덱스 레코드가 가리키는 다중 필드 레코드보다 작을 수 있습니다. 따라서 인덱스 자체에는 원래 테이블보다 적은 수의 디스크 블록이 필요하므로 반복하는 데 필요한 블록 액세스 수가 더 적습니다. firstName 필드의 인덱스에 대한 스키마는 아래에 설명되어 있습니다.

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

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

예 2 - 인덱싱

인덱스 레코드 길이가 R = 54 바이트이고 기본 블록 크기 B = 1,024 바이트를 r = 5,000,000 개 레코드의 샘플 데이터베이스가 주어집니다. 인덱스의 차단 요소는 bfr = (B/R) = 1024/54 = 18 디스크 블록당 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% 미만인 경우 쿼리 최적화 프로그램에서 인덱스 사용을 피하여 인덱스를 공간 낭비로 만드는 효과가 있습니다.

Xenph Yan

고전적인 예 "도서의 색인"

1000페이지로 된 "책"을 10개의 장으로 나누고 각 섹션에 100페이지를 포함한다고 가정해 보겠습니다.

간단하죠?

이제 "Alchemist "라는 단어가 포함된 특정 챕터를 찾고 있다고 상상해 보십시오. 색인 페이지가 없으면 전체 책/챕터를 스캔하는 것 외에 다른 옵션이 없습니다. 예: 1000페이지.

이 비유는 데이터베이스 세계에서 "전체 테이블 스캔"으로 알려져 있습니다.

그러나 색인 페이지를 사용하면 어디로 가야 하는지 알 수 있습니다! 또한 중요한 특정 챕터를 조회하려면 매번 인덱스 페이지를 계속해서 살펴봐야 합니다. 일치하는 색인을 찾은 후 나머지는 건너뛰어 해당 장으로 효율적으로 이동할 수 있습니다.

그러나 실제 1000페이지 외에도 인덱스를 표시하려면 10페이지가 더 필요하므로 총 1010페이지가 필요합니다.

따라서 인덱스는 효율적인 조회를 위해 인덱스된 열의 값 + 인덱스된 행에 대한 포인터를 정렬된 순서로 저장하는 별도의 섹션입니다.

학교에서는 일이 간단하지 않습니까? :NS

Sankarganesh Eswaran

인덱스는 데이터베이스의 특정 열을 더 빠르게 검색할 수 있도록 하는 데이터 구조일 뿐입니다. 이 구조는 일반적으로 b-트리 또는 해시 테이블이지만 다른 논리 구조일 수 있습니다.

hcarreras

처음 이 글을 읽었을 때 많은 도움이 되었습니다. 감사합니다.

그 이후로 인덱스 생성의 단점에 대한 통찰력을 얻었습니다. UPDATE 또는 INSERT )에 쓰는 경우 파일 시스템에서 실제로 두 개의 쓰기 작업이 있습니다. 하나는 테이블 데이터를 위한 것이고 다른 하나는 인덱스 데이터를 위한 것입니다. 테이블과 인덱스가 동일한 하드 디스크에 있는 경우 시간이 더 많이 소요됩니다. 따라서 인덱스(힙)가 없는 테이블은 더 빠른 쓰기 작업을 허용합니다. (두 개의 인덱스가 있는 경우 세 번의 쓰기 작업으로 끝나는 식입니다.)

그러나 인덱스 데이터와 테이블 데이터에 대해 두 개의 다른 하드 디스크에 두 개의 다른 위치를 정의하면 시간 비용 증가 문제를 줄이거나 없앨 수 있습니다. 이를 위해서는 원하는 하드 디스크에 있는 파일에 따라 추가 파일 그룹을 정의하고 원하는 대로 테이블/인덱스 위치를 정의해야 합니다.

인덱스의 또 다른 문제는 데이터가 삽입될 때 시간이 지남에 따라 단편화된다는 것입니다. REORGANIZE 도움이 되며 완료하려면 루틴을 작성해야 합니다.

특정 시나리오에서는 힙이 인덱스가 있는 테이블보다 더 유용합니다.

예:- 라이벌 쓰기가 많이 있지만 보고를 위해 업무 시간 외에 밤에 한 번만 읽는 경우.

또한 클러스터형 인덱스와 비클러스터형 인덱스의 구분이 다소 중요합니다.

도움이 되었습니다:- 클러스터형 인덱스와 비클러스터형 인덱스는 실제로 무엇을 의미합니까?

Der U

이제 'Abc'라는 직원의 모든 세부 정보를 찾기 위해 쿼리를 실행하려고 한다고 가정해 보겠습니다.

 SELECT * FROM Employee WHERE Employee_Name = 'Abc'

인덱스가 없으면 어떻게 될까요?

데이터베이스 소프트웨어는 말 그대로 Employee 테이블의 모든 단일 행을 살펴보고 해당 행의 Employee_Name이 'Abc'인지 확인해야 합니다. 우리가 그 안에 이름 'ABC'와 모든 행을 원하기 때문에 우리가 이름을 'ABC'와 하나의 행을 발견하면 이름 ABC 방송과 다른 행이있을 수 있기 때문에, 우리는 그냥보고 중지 할 수 없습니다. 따라서 마지막 행까지의 모든 행을 검색해야 합니다. 즉, 이 시나리오에서 수천 개의 행을 데이터베이스에서 검사하여 이름이 'Abc'인 행을 찾아야 합니다. 이것을 풀 테이블 스캔 이라고 합니다.

데이터베이스 인덱스가 성능에 도움이 되는 방법

인덱스를 갖는 요점은 본질적으로 검사해야 하는 테이블의 레코드/행 수를 줄임으로써 검색 쿼리의 속도를 높이는 것입니다. 인덱스는 테이블의 특정 열에 대한 값을 저장하는 데이터 구조(가장 일반적으로 B-트리)입니다.

B-트리 인덱스는 어떻게 작동합니까?

B-트리가 인덱스에 대해 가장 널리 사용되는 데이터 구조인 이유는 조회, 삭제 및 삽입이 모두 로그 시간에 수행될 수 있기 때문에 시간 효율적이기 때문입니다. 그리고 B-트리가 더 일반적으로 사용되는 또 다른 주요 이유는 B-트리 내부에 저장된 데이터를 정렬할 수 있기 때문입니다. RDBMS는 일반적으로 인덱스에 실제로 사용되는 데이터 구조를 결정합니다. 그러나 특정 RDBMS가 있는 일부 시나리오에서는 인덱스 자체를 생성할 때 데이터베이스에서 사용할 데이터 구조를 실제로 지정할 수 있습니다.

해시 테이블 인덱스는 어떻게 작동합니까?

해시 인덱스를 사용하는 이유는 해시 테이블이 값을 조회할 때 매우 효율적이기 때문입니다. 따라서 문자열과 같은지 비교하는 쿼리는 해시 인덱스를 사용하는 경우 값을 매우 빠르게 검색할 수 있습니다.

예를 들어, 앞에서 논의한 쿼리는 Employee_Name 열에 생성된 해시 인덱스의 이점을 얻을 수 있습니다. 해시 인덱스가 작동하는 방식은 열 값이 해시 테이블의 키가 되고 해당 키에 매핑된 실제 값이 테이블의 행 데이터에 대한 포인터가 된다는 것입니다. 해시 테이블은 기본적으로 연관 배열이므로 일반적인 항목은 "Abc => 0x28939"와 같이 보일 것입니다. 여기서 0x28939는 Abc가 메모리에 저장된 테이블 행에 대한 참조입니다. 해시 테이블 인덱스에서 "Abc"와 같은 값을 찾고 메모리의 행에 대한 참조를 다시 얻는 것은 Employee_Name 열에서 "Abc" 값을 가진 모든 행을 찾기 위해 테이블을 스캔하는 것보다 분명히 훨씬 빠릅니다.

해시 인덱스의 단점

해시 테이블은 정렬된 데이터 구조가 아니며 해시 인덱스가 도움이 되지 않는 쿼리 유형이 많이 있습니다. 예를 들어, 40세 미만의 모든 직원을 찾고 싶다고 가정합니다. 해시 테이블 인덱스로 어떻게 그렇게 할 수 있습니까? 해시 테이블은 키 값 쌍을 찾는 데만 적합하기 때문에 불가능합니다.

데이터베이스 인덱스 내부에 정확히 무엇이 있습니까? 이제 데이터베이스 인덱스가 테이블의 열에 생성되고 인덱스가 해당 특정 열에 값을 저장한다는 것을 알게 되었습니다. 그러나 데이터베이스 인덱스는 동일한 테이블의 다른 열에 값을 저장하지 않는다는 점을 이해하는 것이 중요합니다. 예를 들어 Employee_Name 열에 인덱스를 생성하면 Employee_Age 및 Employee_Address 열 값도 인덱스에 저장되지 않는다는 의미입니다. 인덱스에 다른 모든 열을 저장하기만 하면 전체 테이블의 다른 복사본을 만드는 것과 같을 것입니다. 이는 너무 많은 공간을 차지하고 매우 비효율적입니다.

데이터베이스는 인덱스를 언제 사용해야 하는지 어떻게 알 수 있습니까? "SELECT * FROM Employee WHERE Employee_Name = 'Abc'"와 같은 쿼리가 실행되면 데이터베이스는 쿼리 중인 열에 인덱스가 있는지 확인합니다. Employee_Name 열에 생성된 인덱스가 있다고 가정하면 데이터베이스는 인덱스를 사용하여 검색되는 값을 찾는 것이 실제로 의미가 있는지 여부를 결정해야 합니다. 데이터베이스 인덱스를 사용하는 것이 실제로 덜 효율적인 일부 시나리오가 있기 때문입니다. , 그리고 전체 테이블을 스캔하는 것이 더 효율적입니다.

데이터베이스 인덱스를 보유하는 데 드는 비용은 얼마입니까?

공간을 차지하며 테이블이 클수록 인덱스도 커집니다. 인덱스의 또 다른 성능 저하는 해당 테이블의 행을 추가, 삭제 또는 업데이트할 때마다 인덱스에 대해 동일한 작업을 수행해야 한다는 사실입니다. 인덱스는 인덱스가 포함하는 테이블 열에 있는 것과 동일한 최대 분 데이터를 포함해야 함을 기억하십시오.

일반적으로 인덱싱된 열의 데이터가 자주 쿼리되는 경우에만 테이블에 인덱스를 생성해야 합니다.

또한보십시오

1. 일반적으로 좋은 인덱스를 만드는 열은 무엇입니까?
2. 데이터베이스 인덱스는 어떻게 작동합니까?

Somnath Muluk

간단한 설명!

인덱스는 테이블 의 특정 열에 대한 값을 저장 하는 데이터 구조일 뿐입니다. 테이블의 열에 인덱스가 생성됩니다.

Name , Age 및 Address 라는 세 개의 열이 있는 User 라는 데이터베이스 테이블이 있습니다. User 테이블에 수천 개의 행이 있다고 가정합니다.

이제 'John'이라는 사용자의 모든 세부 정보를 찾기 위해 쿼리를 실행한다고 가정해 보겠습니다. 다음 쿼리를 실행하면:

 SELECT * FROM User WHERE Name = 'John'

User 테이블의 모든 단일 행을 살펴보고 Name 이 'John'인지 확인해야 합니다. 시간이 오래 걸립니다.

index 도움이 되는 부분입니다 . 인덱스는 본질적으로 검사해야 하는 테이블의 레코드/행 수를 줄임으로써 검색 쿼리 속도를 높이는 데 사용됩니다 .

인덱스를 만드는 방법:

 CREATE INDEX name_index ON User (Name)

index 는 한 테이블의 열 값(예: John)으로 구성 되며 해당 값은 데이터 구조에 저장됩니다.

이제 데이터베이스는 인덱스를 사용하여 John이라는 직원을 찾습니다. 인덱스가 사용자 이름을 기준으로 알파벳순으로 정렬될 것이기 때문입니다. 그리고 정렬되어 있기 때문에 "J"로 시작하는 모든 이름이 색인에서 서로 바로 옆에 있기 때문에 이름 검색이 훨씬 빠릅니다!

ProgrammerPanda

간단한 제안입니다. 인덱싱에는 추가 쓰기 및 저장 공간이 필요하므로 애플리케이션에 더 많은 삽입/업데이트 작업이 필요한 경우 인덱스가 없는 테이블을 사용하고 싶을 수 있지만 더 많은 데이터 검색 작업이 필요한 경우 인덱싱된 테이블.

Raza

Database Index는 책의 Index라고 생각하시면 됩니다.

개에 관한 책이 있고 예를 들어 독일 셰퍼드에 대한 정보를 찾고 싶다면 책의 모든 페이지를 넘기고 원하는 것을 찾을 수 있습니다. 하지만 이것은 물론 시간이 많이 걸리고 그렇지 않습니다 매우 빠릅니다.

또 다른 옵션은 책의 색인 섹션으로 이동한 다음 찾고 있는 개체의 이름(이 경우 저먼 셰퍼드)을 사용하고 페이지 번호를 보고 원하는 것을 찾을 수 있다는 것입니다. 당신이 찾고 있는 것을 빨리 찾으십시오.

데이터베이스에서 페이지 번호는 엔티티가 있는 디스크의 주소로 데이터베이스를 지시하는 포인터라고 합니다. 동일한 저먼 셰퍼드 비유를 사용하여 다음과 같은 것을 가질 수 있습니다("저먼 셰퍼드", 0x77129). 여기서 0x77129 는 저먼 셰퍼드에 대한 행 데이터가 저장된 디스크의 주소입니다.

간단히 말해서 인덱스는 쿼리 검색 속도를 높이기 위해 테이블의 특정 열에 대한 값을 저장하는 데이터 구조입니다.

Alf Moh

출처 : http:www.stackoverflow.com/questions/1108/how-does-database-indexing-work