데이터베이스 인덱싱은 어떻게 작동합니까?[닫은]
질문
데이터 세트가 크기가 커짐에 따라 인덱싱이 중요 해지면 누군가가 데이터베이스에 불가지론 수준에서 인덱싱 작동하는 방법을 설명 할 수 있습니까?
쿼리에 대한 정보는 필드를 색인화하려면 어떻게 데이터베이스 열을 인덱싱 할 수 있는지 확인하십시오.
답변
왜 필요합니까?
데이터가 디스크 기반 저장 장치에 저장되면 데이터 블록으로 저장됩니다.이러한 블록은 전체적으로 액세스되어 원자 디스크 액세스 작업을 수행합니다.디스크 블록은 링크 된 목록과 거의 동일한 방식으로 구성됩니다.둘 다에는 데이터 섹션, 다음 노드 (또는 블록)의 위치에 대한 포인터가 있고 둘 다 연속적으로 저장되지 않아도됩니다.
여러 레코드가 한 필드에서만 정렬 될 수 있기 때문에 정렬되지 않은 필드에서 검색하는 것이 (n + 1) / 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
참고 : CHAR는 VARCHAR 대신에 사용되어 디스크 값에서 정확한 크기를 허용합니다. 이 샘플 데이터베이스에는 500 만 개의 행이 포함되어 있으며 unindexed입니다.이제 여러 쿼리의 성능을 분석합니다.이들은 ID (정렬 된 키 필드)와 첫 번째 이름 (키가 아닌 정렬되지 않은 필드)을 사용하는 쿼리입니다.
예제 1 - 정렬 된 vs 정렬되지 않은 필드
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 블록 액세스가 필요가있는 이진 검색을 수행 할 수 있습니다.즉시 우리는 이렇게 볼 수 있습니다. 이는 과감한 개선입니다.
이제 첫 번째 이름 필드는 정렬되지 않거나 키 필드가 아니므로 이진 검색이 불가능하지 않으므로 값은 고유하지 않으며 테이블은 정확한 n = 1,000,000 블록 액세스에 대한 끝을 검색해야합니다.인덱싱을 목표로하는 것은이 상황입니다.
인덱스 레코드가 인덱싱 된 필드와 원래 레코드에 대한 포인터가 포함되어 있음을 감안할 때, 포인트하는 멀티 필드 레코드보다 작아지는 이유가됩니다.따라서 인덱스 자체는 원래 테이블보다 더 적은 디스크 블록을 필요로하므로 반복 할 수있는 블록 액세스가 줄어 듭니다.첫 번째 이름 필드의 인덱스에 대한 스키마는 아래에 설명되어 있습니다.
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 바이트의 인덱스 레코드 길이가있는 R = 5,000,000 개의 레코드의 샘플 데이터베이스를 감안하여 기본 블록 크기 B = 1,024 바이트를 사용합니다.인덱스의 차단 계수는 BFR = (B / R) = 1024/54 = 18 디스크 블록의 레코드가됩니다.인덱스를 보유하는 데 필요한 총 블록 수는 n = (R / BFR) = 5000000/18 = 277,778 블록입니다.
이제 첫 번째 이름 필드를 사용하여 검색은 인덱스를 활용하여 성능을 향상시킬 수 있습니다.이는 평균 LOG2 27778 = 18.08 = 19 블록 액세스로 인덱스의 이진 검색을 허용합니다.읽기에 추가 블록 액세스가 필요한 실제 레코드의 주소를 찾으려면 총 19 + 1 = 20 블록 액세스를 가져 오는 데 필요한 1,000,000 개의 블록 액세스가 필요한 1,000,000 개의 블록 액세스에서 먼저 울 으려면 비 인덱싱 된 테이블에서 첫 번째 이름 일치를 찾아야합니다....에
언제 사용해야합니까?
인덱스를 만드는 것이 추가 디스크 공간 (위의 예제에서 추가로 277,778 블록, ~ 28 % 증가)이 필요하며 너무 많은 지표가 파일 시스템 크기 제한에서 발생하는 문제를 일으킬 수 있으므로 정확한 생각을 선택하는 데주의를 기울여야합니다.인덱스 필드.
인덱스는 레코드 내에서 일치하는 필드에 대한 검색 속도를 높이기 위해서만 사용되므로 출력에만 사용되는 인덱싱 필드가 삽입 또는 삭제 작업을 수행 할 때 디스크 공간 및 처리 시간 낭비 일 것입니다.피해야합니다.또한 이진 검색의 성격을 감안할 때 데이터의 카디널리티 또는 고유성이 중요합니다.2의 카디널리티가 2 인 필드에 색인 생성은 데이터를 절반으로 분리하는 반면, 1,000의 카디널리티는 약 1,000 개의 레코드를 반환합니다.이러한 낮은 카디널리티를 사용하면 효과가 선형 정렬으로 줄어들 며 카디널리티가 기록 번호의 30 % 미만이면 인덱스를 사용하여 인덱스를 사용하여 인덱스를 공간 낭비로 만듭니다.
답변
클래식 예제 "책의 인덱스"
1000 페이지의 "책"을 10 장, 100 페이지의 각 섹션으로 나눈 값을 고려하십시오.
간단한, 응?
이제 "연금술사"라는 단어가 들어있는 특정 장을 찾고 싶습니다.색인 페이지가 없으면 전체 책 / 장을 스캔하는 것보다 다른 옵션이 없습니다.I.E : 1000 페이지.
이 비유는 데이터베이스 세계에서 "전체 테이블 스캔"이라고합니다.
그러나 색인 페이지로 어디로 가야하는지 알 수 있습니다!그리고 더 중요한 경우, 중요한 장을 조회하기 위해 매번 인덱스 페이지를 다시 살펴볼 필요가 있습니다.일치하는 인덱스를 찾은 후에는 나머지를 건너 뛰어 효율적으로 해당 장에 뛰어들 수 있습니다.
그런 다음 실제 1000 페이지 외에도 인덱스를 보여주기 위해 또 다른 ~ 10 페이지가 필요하므로 1010 페이지가 완전히 1010 페이지가 필요합니다.
따라서 인덱스는 인덱스 값의 값을 저장하는 별도의 섹션입니다. 컬럼 + 효율적인 순서로 인덱싱 된 행에 대한 색인 행렬 룩업.
사물은 학교에서 간단합니다. 그렇지 않습니까?:피
답변
색인은 데이터베이스의 특정 열에 더 빠르게 검색을 더 빠르게 만드는 데이터 구조 일뿐입니다.이 구조는 일반적으로 B- 트리 또는 해시 테이블이지만 다른 논리 구조 일 수 있습니다.
답변
처음 읽은 시간은 나에게 매우 도움이되었습니다.고맙습니다.
그 이후로 나는 인덱스 작성의 단점에 대한 통찰력을 얻었습니다. 하나의 인덱스로 테이블 (업데이트 또는 삽입)에 작성하는 경우 실제로 파일 시스템에 두 개의 쓰기 작업이 있습니다.하나는 테이블 데이터 및 인덱스 데이터의 다른 하나 (및 그 중의 의지 (및 - 클러스터 된 경우 테이블 데이터의 리조트)).테이블과 색인이 동일한 하드 디스크에있는 경우이 비용은 더 많은 시간을 할애합니다.따라서 색인 (힙)이없는 테이블은 더 빨리 쓰기 작업을 허용합니다.(두 개의 인덱스가있는 경우 3 개의 쓰기 작업으로 끝나면)
그러나 인덱스 데이터 및 테이블 데이터에 대해 두 개의 다른 하드 디스크에 두 개의 다른 위치를 정의하면 증가 된 비용의 문제를 줄이거 나 제거 할 수 있습니다.이를 위해 원하는대로 원하는 하드 디스크 및 테이블 / 인덱스 위치의 정의에 따라 파일에 따라 추가 파일 그룹의 정의가 필요합니다.
인덱스의 또 다른 문제점은 데이터가 삽입 될 때 시간이 지남에 따라 분열입니다.Reorganize Help를 재구성하면 작업을 수행 할 루틴을 작성해야합니다.
특정 시나리오에서 힙은 인덱스가있는 테이블보다 더 도움이됩니다.
E.G : - 라이벌을 많이 쓰는 경우,보고하기 위해 한 야간 외부 업무 시간 만 읽을 수 있습니다.
또한 클러스터 된 인덱스와 비 인덱스 간의 차별화가 중요합니다.
나를 도왔습니다 : - 클러스터 된 것과 비 클러스터 된 색인은 실제로 의미하는 것은 무엇입니까?
답변
이제 'ABC'라는 이름의 직원의 모든 세부 정보를 찾으려면 쿼리를 실행하고 싶습니까?
SELECT * FROM Employee
WHERE Employee_Name = 'Abc'
인덱스 없이는 어떤 일이 일어날 것입니까?
데이터베이스 소프트웨어는 문자 그대로 직원 테이블의 모든 행을보고 해당 행에 대한 employee_name이 'ABC'인지 확인해야합니다.그리고 우리가 그 안에 'abc'라는 이름으로 모든 행을 원하기 때문에 이름은 이름을 가진 다른 행이있을 수 있기 때문에 우리가 'abc'라는 이름의 행을 찾을 수 없었습니다.따라서 마지막 행을 검색 할 때까지 모든 행을 검색해야합니다.이 시나리오에서 수천 개의 행을 데이터베이스에서 검사하여 'ABC'라는 행을 찾아야합니다.이것은 전체 테이블 스캔이라고 불리는 것입니다
데이터베이스 인덱스가 성능을 어떻게 도울 수 있는지
인덱스를 갖는 전체 지점은 검사 해야하는 테이블의 레코드 / 행 수를 기본적으로 절단하여 검색 쿼리를 가속화하는 것입니다.인덱스는 테이블의 특정 열의 값을 저장하는 데이터 구조 (가장 일반적으로 B 트리)입니다.
B-Trees가 어떻게 작동합니까?
그 이유는 인덱스에 가장 널리 사용되는 데이터 구조입니다. 룩업, 삭제 및 삽입이 모두 로그에서 수행 될 수 있기 때문에 인덱스가 시간 효율적이라는 사실 때문입니다.또한 B- 트리 내부에 저장된 데이터가 정렬 될 수 있기 때문에 다른 주요 이유가 더 일반적으로 사용됩니다.RDBMS는 일반적으로 인덱스에 실제로 사용되는 데이터 구조를 결정합니다.그러나 특정 RDBMS가있는 일부 시나리오에서는 실제로 인덱스 자체를 만들 때 데이터베이스가 사용할 데이터 구조를 사용할 수 있습니다.
해시 테이블 색인은 어떻게 작동합니까?
이유 해시 인덱스는 해시 테이블이 단지 값을 조회 할 때 매우 효율적이기 때문입니다.따라서 동일성을 문자열로 비교하는 쿼리는 해시 색인을 사용하면 값을 매우 빠르게 검색 할 수 있습니다.
예를 들어, 이전에 설명한 쿼리는 Employee_name 열에 생성 된 해시 색인의 이점을 누릴 수 있습니다.해시 인덱스가 작동하는 방식은 열 값이 해시 테이블의 키가되고 해당 키에 매핑 된 실제 값은 테이블의 행 데이터의 포인터 일 것입니다.해시 테이블은 기본적으로 연관 배열이므로 일반적인 항목은 "abc => 0x28939"와 같은 것을 보입니다. 여기서 0x28939는 ABC가 메모리에 저장된 테이블 행에 대한 참조입니다.해시 테이블 인덱스에서 "ABC"와 같은 값을 찾고 메모리의 행에 대한 참조를 다시 가져 오는 것은 테이블을 스캔하는 것보다 훨씬 빠르게 훨씬 빠릅니다.
해시 인덱스의 단점
해시 테이블은 정렬 된 데이터 구조가 아니며 해시 인덱스가 도움이되지 않는 많은 유형의 쿼리가 많이 있습니다.예를 들어, 40 세 미만인 모든 직원을 찾고 싶다고 가정 해보십시오.해시 테이블 색인으로 어떻게 할 수 있습니까?그럼 해시 테이블이 키 값 쌍을 찾는 것에 만족하기 때문에 가능하지 않습니다. 이는 평등을 검사하는 쿼리를 의미합니다.
데이터베이스 색인 내에 정확히 무엇인가? 따라서 데이터베이스 인덱스가 테이블의 열에 만들어지고 인덱스가 해당 특정 열의 값을 저장한다는 것을 알고 있습니다.그러나 데이터베이스 인덱스가 동일한 테이블의 다른 열에 값을 저장하지 않는다는 것을 이해하는 것이 중요합니다.예를 들어, Employee_name 열에 인덱스를 생성하면 Employee_age 및 Employee_Address 열 값도 인덱스에 저장되지 않음을 의미합니다.우리가 인덱스에 다른 모든 열을 저장하면 전체 테이블의 다른 사본을 만드는 것과 같을 것입니다. 이는 너무 많은 공간을 차지하고 매우 비효율적입니다.
색인을 사용할 때 데이터베이스는 어떻게 알 수 있습니까? Employee_name = 'ABC' "를 실행하는"Employee_name = 'ABC "에서"select * from "과 같은 쿼리가 쿼리되는 열에 대한 인덱스가 있는지 확인합니다.Employee_name 열에는 인덱스가 생성 된 인덱스가있는 경우 데이터베이스가 검색되는 값을 찾기 위해 실제로 인덱스를 사용하는지 여부를 결정해야합니다. 실제로 데이터베이스 색인을 사용하는 데 실제로 덜 효율적인 시나리오가 있기 때문에그리고보다 효율적으로 전체 테이블을 스캔하십시오.
데이터베이스 색인의 비용은 얼마입니까?
그것은 공간을 차지하고 테이블이 클수록 색인이 클수록.인덱스가있는 또 다른 성능은 해당 테이블에서 행을 추가, 삭제 또는 업데이트 할 때마다 동일한 작업이 인덱스로 수행되어야한다는 사실입니다.인덱스가 인덱스가 덮은 테이블 열에있는 테이블 열에있는 것과 동일한 분당 데이터와 동일한 것을 포함해야한다는 것을 기억하십시오.
일반적으로 인덱싱 된 열의 데이터가 자주 쿼리되는 경우 테이블에서 인덱스 만 만들어야합니다.
또한보십시오
답변
간단한 설명!
인덱스는 테이블의 특정 열의 값을 저장하는 데이터 구조 만 있지 않습니다.인덱스는 테이블의 열에 생성됩니다.
예 : 우리는 세 개의 열 - 이름, 나이 및 주소가있는 3 개의 열을 가진 사용자라는 데이터베이스 테이블이 있습니다.사용자 테이블에 수천 개의 행이 있다고 가정합니다.
이제 'John'이라는 이름의 사용자의 모든 세부 정보를 찾으려면 쿼리를 실행하려고합니다. 다음 쿼리를 실행하면 다음을 수행하십시오.
SELECT * FROM User
WHERE Name = 'John'
데이터베이스 소프트웨어는 문자 그대로 사용자 테이블의 모든 단일 행을보고 해당 행의 이름이 'John'인지 확인해야합니다.이것은 오랜 시간이 걸릴 것입니다.
이는 인덱스가 우리를 돕는 데 도움이됩니다. 인덱스는 참고 해야하는 테이블의 레코드 / 행 수를 기본적으로 절단하여 검색 쿼리를 가속화하는 데 사용됩니다.
색인을 만드는 방법 :
CREATE INDEX name_index
ON User (Name)
인덱스는 한 테이블에서 열 값 (예 : John)으로 구성되며 해당 값은 데이터 구조에 저장됩니다.
이제 데이터베이스는 John이라는 직원을 찾기 위해 색인을 사용합니다. 인덱스가 아마도 아마도 알파벳순으로 정렬되기 때문에 사용자 이름.그리고 그것은 분류되어 있기 때문에 이름을 검색하는 것을 의미합니다. "j"로 시작하는 모든 이름이 올바르게 될 것이기 때문에 훨씬 더 빠릅니다. 색인에서 서로 옆에!
출처:https://stackoverflow.com/questions/1108/how-does-database-indexing-work
최근댓글