최적화 - sql index 사용




데이터베이스 인덱싱은 어떻게 작동합니까? (6)

데이터 세트의 크기가 커질수록 인덱싱이 중요하다는 것을 감안할 때 데이터베이스와 무관하게 인덱싱이 어떻게 작동하는지 설명 할 수 있습니까?

필드를 색인화하기위한 조회에 대한 정보 는 데이터베이스 열을 색인화하는 방법을 참조 하십시오.


간단한 설명!

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

예 : Name , AgeAddress 라는 세 개의 열이있는 User 라는 데이터베이스 테이블이 있습니다. User 테이블에 수천 개의 행이 있다고 가정하십시오.

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

SELECT * FROM User 
WHERE Name = 'John'

데이터베이스 소프트웨어는 문자 그대로 User 테이블의 모든 단일 행을보고 해당 행의 Name 이 'John'인지 확인해야합니다. 시간이 오래 걸립니다.

인덱스는 우리를 도와줍니다. 인덱스는 검사해야 할 테이블의 레코드 / 행 수를 줄임으로써 검색 쿼리 속도를 높이는 데 사용됩니다 .

인덱스를 만드는 방법 :

CREATE INDEX name_index
ON User (Name)

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

이제 데이터베이스는 색인을 사용하여 John이라는 직원을 찾습니다. 색인은 아마도 사용자 이름에 따라 알파벳순으로 정렬되기 때문입니다. 그리고 정렬되어 있기 때문에“J”로 시작하는 모든 이름이 색인에서 서로 바로 옆에 있기 때문에 이름 검색이 훨씬 빠릅니다.


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

각 페이지가 X 페이지 인 100 개의 섹션으로 나누어 진 1000 페이지의 "도서"를 고려하십시오.

간단 하죠?

이제 색인 페이지없이 문자 "S"로 시작하는 특정 섹션을 찾으려면 책 전체를 스캔하는 것 외에 다른 옵션이 없습니다. 즉 : 1000 페이지

그러나 시작 페이지에 색인 페이지가 있습니다. 또한 중요한 특정 섹션을 읽으려면 매번 인덱스 페이지를 반복해서 살펴 봐야합니다. 일치하는 색인을 찾은 후에 다른 섹션을 건너 뛰어 섹션으로 효율적으로 이동할 수 있습니다.

그러나 1000 페이지 외에도 색인 페이지를 표시하려면 1010 페이지가 추가로 ~ 10 페이지가 더 필요합니다.

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

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


데이터베이스 색인을 책의 색인으로 생각하십시오.

개에 관한 책이 있고 German Shepherds에 대한 정보를 찾고 싶다면 물론 책의 모든 페이지를 넘겨보고 찾고있는 것을 찾을 수는 있지만 시간이 많이 걸리지 않습니다. 매우 빠릅니다.

또 다른 옵션은 책의 색인 섹션으로 이동 한 다음 찾고있는 엔터티의 이름 (이 예에서는 독일 셰퍼드)을 사용하여 찾고자하는 것을 찾을 수 있다는 것입니다. 원하는 것을 빨리 찾을 수 있습니다.

데이터베이스에서 페이지 번호는 데이터베이스를 엔티티가있는 디스크의 주소로 지정하는 포인터라고합니다. 동일한 German Shepherd 유추를 사용하면 다음과 같은 결과를 얻을 수 있습니다 (“German Shepherd”, 0x77129). 여기서 0x77129 는 German Shepherd의 행 데이터가 저장된 디스크의 주소입니다.

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


이제 '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. 데이터베이스 인덱스 작동 방법

인덱싱에 추가 쓰기 및 저장 공간이 필요하므로 응용 프로그램에 더 많은 삽입 / 업데이트 작업이 필요한 경우 인덱스가없는 테이블을 사용할 수 있지만 더 많은 데이터 검색 작업이 필요한 경우 인덱싱해야합니다. 표.


왜 필요한가요?

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

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

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

인덱싱이란 무엇입니까?

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

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

어떻게 작동합니까?

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

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

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

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

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

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

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

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

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

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

예 2- 인덱싱

인덱스 레코드 길이가 R = 54 바이트이고 기본 블록 크기 B = 1,024 바이트를 사용하는 r = 5,000,000 레코드의 샘플 데이터베이스가 있다고 가정합니다. 인덱스의 차단 계수는 디스크 블록 당 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 % 미만인 경우 쿼리 최적화 프로그램은 인덱스를 공간 낭비로 효과적으로 만들지 않으면 인덱스 사용을 피할 수 있습니다.





database-indexes