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

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

색인의 크기가 데이터 세트가 증가함에 따라 매우 중요하다는 점을 감안할 때, 캔 누군가가 색인 데이터베이스에 얽매 수준에서 작동하는 방법을 설명?

인덱스에 대한 쿼리에 대한 자세한 내용은 필드는 데이터베이스 열 I 인덱스를 어떻게 확인하십시오.

해결법

1. ==============================

   1.왜 필요한가?

   왜 필요한가?

   데이터가 디스크 기반의 저장 장치에 저장 될 때, 그것은 데이터 블록으로 저장된다. 이 블록은 그 원자 디스크 액세스 작업을하고, 전체가 액세스 할 수 있습니다. 디스크 블록은 연결리스트만큼 같은 방식으로 구성되어 있습니다; 두 데이터 섹션, 다음 노드 (또는 블록)의 위치에 대한 포인터를 포함하고, 둘 필요가 연속적으로 저장되지.

   기록의 수는 하나 개의 필드를 정렬 할 수 있다는 사실로 인해, 우리는 분류되지 않은 필드를 검색하는 것은 N이있다 (평균) N / 2 블록 액세스를 필요로하는 선형 검색을 필요로 명시 할 수 있습니다 테이블 걸치는 블록 수. 그 필드가 아닌 필드가 키 (즉, 고유의 항목을 포함하지 않는다) 인 경우, 전체 테이블은 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 대신에 사용되었다. 이 샘플 데이터베이스 500 만 행을 포함하고 인덱싱되지 않은됩니다. 여러 쿼리의 성능은 이제 분석됩니다. 이러한 ID를 사용하여 쿼리있는 firstName을 (키가 아닌 정렬되지 않은 필드)를 사용하여 하나의 (a는 키 필드를 분류).

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

   R = 204 바이트의 기록 길이를 제공하는 고정 크기 = 5,000,000 기록 R의 고객 샘플 데이터베이스 감안하고 기본 블록 크기 B = 024 바이트를 사용하여 상기의 MyISAM 엔진을 사용하여 테이블에 저장된다. 테이블의 차단 인자 BFR = (B / R) = 디스크 블록 당 204분의 1,024 = 5 기록 될 것이다. 블럭의 갯수는 표 N = (R / BFR) = 5분의 5,000,000 = 1,000,000 블록 떨어져 유지하는데 필요한.

   ID 필드에 선형 검색이 N의 평균 요구 / 2 = 50 블록 ID 필드는 키 필드 주어진 값을 찾기 위해 접근한다. ID 필드는 또한 정렬되므로 그러나, 이진 검색은 LOG2 1,000,000 = 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) = 디스크 블록 당 54분의 1,024 = 18 기록 될 것이다. 블럭의 갯수는 인덱스 N = (R / BFR) = 18분의 5,000,000 = 277,778 블록 떨어져 유지하는데 필요한.

   이제 firstName을 필드를 사용하여 검색이 증가 성능 인덱스를 활용할 수 있습니다. 이는 277,778 = 18.08 = 19 블록 액세스 LOG2의 평균 인덱스의 이진 검색을 허용한다. 1,000,000 블록이 아닌 인덱스 테이블에서 firstName을 매치를 찾기 위해 필요한 액세스에에서가 멀다 19 + 1 = 20 블록의 액세스에 총을 가져, 읽어 상기 블록 액세스를 필요로하는 실제 레코드의 주소를 알려 .

   때 사용되어야 하는가?

   인덱스를 생성하는 (위의 예에서 277,778 블록을 추가하는 ~ 28 % 증가) 추가 디스크 공간이 필요합니다 감안할 때, 그리고 너무 많은 인덱스는 파일 시스템 크기 제한에서 발생하는 문제가 발생할 수 있습니다,주의 생각은 올바른을 선택하는 데 사용되어야합니다 인덱스 필드.

   인덱스 전용 레코드 내의 매칭 필드의 검색 속도를 사용하기 때문에, 인덱싱 필드에 단순히 디스크 공간을 낭비하고, 삽입 또는 삭제 동작을 수행 할 때의 처리 시간, 이렇게 될 경우에만 출력에 사용되는 이유로 약자 피해야합니다. 또한 이진 검색의 특성상, 중요도 또는 데이터의 고유성이 중요하다. 1000의 기수가 약 1,000 레코드를 반환하는 반면 2의 카디널리티와 함께 ​​필드에 색인 것은 반으로 데이터를 분할한다. 이러한 낮은 카디널리티으로 효과는 선형 종류로 감소하고, 쿼리 최적화 프로그램은 카디널리티 효과적으로 인덱스 공간을 낭비하고, 레코드 수의 30 % 미만이면 인덱스를 사용하지 않도록합니다.

2. ==============================

   2.클래식 예를 들어 "책의 색인"

   클래식 예를 들어 "책의 색인"

   10 장으로 나누어 1000 페이지의 "책", 100 페이지 각 섹션을 고려하십시오.

   단순, 응?

   지금, 당신은 단어 "연금술사"를 포함하는 특정 장을 찾으려는 상상한다. 인덱스 페이지가 없으면 책 전체 / 장을 통해 스캔보다 다른 옵션이 없습니다. 즉 : 1000 페이지.

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

   그러나 인덱스 페이지와 함께, 당신은 어디로 가야 알아! 문제는, 당신은 단지, 또 다시, 인덱스 페이지를 통해 볼 때마다 필요보다, 특정 장을 조회합니다. 일치 지수를 발견 한 후 효율적으로 나머지를 건너 뛰는하여 해당 챕터로 이동할 수 있습니다.

   그러나, 실제 1000 페이지에 추가하여, 당신은 인덱스를 보여주기 때문에 완전히 1,010 페이지를 다른 ~ 10 페이지가 필요합니다.

   상황이이 학교에서 간단한되지입니까? :피

3. ==============================

   3.나는이 글을 읽을 처음 그것은 나에게 매우 도움이되었다. 감사합니다.

   나는이 글을 읽을 처음 그것은 나에게 매우 도움이되었다. 감사합니다.

   그 이후로 나는 색인을 생성하는 단점에 대한 몇 가지 통찰력을 얻었다 : 당신이 하나 개의 인덱스가있는 테이블 (UPDATE 또는 INSERT)로 작성하는 경우, 당신은 파일 시스템에서 실제로 두 쓰기 작업을해야합니다. 테이블 데이터와 인덱스 데이터에 대한 또 다른 하나 한 (그리고 (의 의지와 - 클러스터 된 경우 - 테이블 데이터의 의지)). 테이블과 인덱스가 같은 하드 디스크에있는 경우이 더 많은 시간을 요한다. 따라서 인덱스 (힙)가없는 테이블은 빠른 쓰기 작업을 허용합니다. (두 개의 인덱스가 있다면 당신은 너무 세 쓰기 작업으로 끝낼, 그리고 것입니다)

   그러나, / 감소 시간의 비용 증가의 문제를 제거 할 수있는 인덱스 데이터와 테이블 데이터에 대한 두 개의 서로 다른 하드 디스크에 두 개의 서로 다른 위치를 정의. 이 원하는대로 원하는 하드 디스크 및 테이블 / 인덱스 위치의 정의에 파일을 따라와 추가 파일 그룹의 정의를 필요로한다.

   데이터가 삽입 될 때 인덱스가있는 또 다른 문제는 시간이 지남에 따라 자신의 조각입니다. REORGANIZE 당신이 그것을 짓을하는 루틴을 작성해야하는 데 도움이됩니다.

   특정 시나리오에서 힙은, 인덱스 테이블보다 더 많은 도움이됩니다

   예컨대 : - 당신은 수완이 좋아서 쓰기를 많이 가지고 있지만 하나의 밤보고 외부 업무 시간을 읽는다면.

   또한, 클러스터 및 클러스터되지 않은 인덱스 사이의 차별화는 오히려 중요하다.

   저를 도왔 : - 클러스터 및 비 실제로 평균 인덱스를 클러스터 무엇?

4. ==============================

   4.인덱스는 빠르게 데이터베이스의 특정 열을위한 탐색하게 단지 데이터 구조이다. 이 구조는 일반적으로 B - 트리 또는 해시 테이블이지만 다른 논리 구조가 될 수 있습니다.

   인덱스는 빠르게 데이터베이스의 특정 열을위한 탐색하게 단지 데이터 구조이다. 이 구조는 일반적으로 B - 트리 또는 해시 테이블이지만 다른 논리 구조가 될 수 있습니다.

5. ==============================

   5.자,하자 우리가 'ABC'를 명명하는 모든 직원의 모든 세부 사항을 찾을 수있는 쿼리를 실행하고 싶은 말?

   자,하자 우리가 'ABC'를 명명하는 모든 직원의 모든 세부 사항을 찾을 수있는 쿼리를 실행하고 싶은 말?

   SELECT * FROM Employee 
   WHERE Employee_Name = 'Abc'
   

   인덱스없이 어떻게 될까요?

   데이터베이스 소프트웨어는 말 그대로 해당 행에 대한 EMPLOYEE_NAME는 'ABC'가 있는지 확인하기 위해 직원 테이블의 모든 단일 행을보고해야합니다. 우리가 그 안에 이름 'ABC'와 모든 행을 원하기 때문에 우리가 이름을 'ABC'와 하나의 행을 발견하면 이름 ABC 방송과 다른 행이있을 수 있기 때문에, 우리는 그냥보고 중지 할 수 없습니다. 그래서, 마지막 행까지 모든 행 최대 검색해야 - 이름 'ABC'와 행을 찾기 위해 데이터베이스에 의해 검토되어야 할 것이다이 시나리오의 행의 수천을 의미한다. 이는 전체 테이블을 스캔이라고합니다 것입니다

   데이터베이스 인덱스는 성능을 도울 수있는 방법

   인덱스를 갖는 요점은 기본적으로 필요가 검사 할 수있는 테이블의 레코드 / 행의 수를 삭감하여 검색 쿼리 속도를하는 것입니다. 인덱스는 데이터 구조 (가장 일반적으로 B- 트리)는 그 테이블의 특정 항목에 대한 값을 저장한다.

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

   룩 - 업, 삭제 및 삽입은 모든 로그 시간에 수행 할 수 있기 때문에 - 그 이유 B- 트리 인덱스에 대한 가장 인기있는 데이터 구조가 효율적으로 시간이 있다는 사실에 기인한다. B- 트리 내부에 저장되어있는 데이터를 정렬 할 수 있기 때문에, B- 나무가 더 일반적으로 사용되는 또 다른 주요 이유입니다. RDBMS는 일반적으로 실제로 인덱스에 사용되는 데이터 구조를 결정한다. 그러나, 특정 RDBMS의 일부 시나리오에서, 당신은 실제로 당신이 인덱스 자체를 만들 때 사용 데이터베이스를하고자하는 데이터 구조를 지정할 수 있습니다.

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

   해시 인덱스가 사용되는 이유는 단지 값을보고 할 때 해시 테이블은 매우 효율적이기 때문이다. 그들이 해시 인덱스를 사용한다면, 문자열로 지 어떤지를 비교 쿼리는 매우 빠르게 값을 검색 할 수 있습니다.

   예를 들어, 우리는 앞에서 설명한 쿼리는 EMPLOYEE_NAME 열을 만든 해시 인덱스 혜택을 누릴 수 있습니다. 해시 인덱스가 작동 할 방법은 열 값이 해시 테이블에 키 단지 테이블의 행 데이터에 대한 포인터가 될 것이라고 키에 매핑 된 실제 값이 될 것입니다. 해시 테이블은 기본적으로 결합 배열이기 때문에, 일반적인 항목 0x28939 ABC의 메모리에 저장되는 테이블의 행에 대한 참조 "ABC => 0x28939"과 같을 것이다. 해시 테이블 인덱스에 "ABC"와 같은 값을 찾고 메모리의 행에 대한 참조를 가져 오는 것은 분명 훨씬 빨리 EMPLOYEE_NAME 열에서 "ABC"의 값을 가진 모든 행을 찾기 위해 테이블을 스캔보다.

   해시 인덱스의 단점

   해시 테이블은 데이터 구조를 정렬되지 않습니다, 그리고 어떤 해시 인덱스 사항이없는 경우에도 도움이 될 수 있습니다 쿼리의 많은 종류가 있습니다. 예를 들어, 이하 40 세있는 모든 직원을 찾으려면 가정합니다. 어떻게 해시 테이블 인덱스와 함께 그 일을 할 수 있습니까? 해시 테이블은 키 값 쌍을 찾는에만 좋기 때문에 글쎄, 그건 불가능 해요 - 어떤 수단 질의 평등에 대한 확인

   정확히 무엇이 인덱스 내부는? 그래서, 지금 당신은 인덱스는 테이블의 컬럼에 생성되는 것을 알고, 인덱스 저장하는 특정 컬럼의 값. 그러나, 인덱스가 같은 테이블의 다른 컬럼의 값을 저장하지 않는 것을 이해하는 것이 중요하다. 예를 들어, 우리는 EMPLOYEE_NAME 열에 인덱스는 Employee_Age 및 Employee_Address 열 값은 또한 인덱스에 저장되지 않도록 수단이 작성하는 경우. 우리가 인덱스에 다른 모든 열을 저장 않은 경우, 그것은 단지 전체 테이블의 다른 복사본을 만드는 것과 같다 - 방법에게 너무 많은 공간을 차지 것이 매우 비효율적 일 것이다.

   인덱스를 사용하는 경우 어떻게 데이터베이스를 알고 있나요? 때 같은 쿼리 "SELECT * EMPLOYEE_NAME이 = 'ABC'는 직원 FROM"실행, 데이터베이스 컬럼 (들)에 대한 인덱스가있는 경우 조회되는 확인합니다. 데이터베이스 인덱스를 사용하기 위해 실제로 덜 효율적입니다 몇 가지 시나리오가 있기 때문에 - EMPLOYEE_NAME 열을 가정하면 그것을 만든 인덱스가 않습니다 데이터베이스는 실제로 검색되는 값을 찾기 위해 인덱스를 사용하는 것이 합리적 여부를 결정해야 할 것이다 ,보다 효율적인 단지 전체 테이블을 스캔합니다.

   데이터베이스 지수를 갖는 비용은 무엇입니까?

   그리고 더 큰 테이블, 더 큰 색인 - 그것은 공간을 차지합니다. 인덱스 또 다른 성능 저하는 동일한 작업이 색인에 수행해야합니다 당신이 해당 테이블의 추가, 삭제, 또는 업데이트 행마다 사실이다. 테이블 컬럼 (들)에있는 인덱스 커버하는 것이 무엇으로 인덱스 필요가 분 데이터에 동일한까지를 포함하는 것을 기억하십시오.

   인덱싱 된 열에있는 데이터는 빈도 쿼리 할 경우 일반적으로, 인덱스 만 테이블에 생성되어야한다.

   또한보십시오

6. ==============================

   6.인덱스 것도 있지만 데이터 구조 없다고 테이블의 특정 컬럼에 대한 값을 저장한다. 인덱스는 테이블의 열을 생성한다.

   인덱스 것도 있지만 데이터 구조 없다고 테이블의 특정 컬럼에 대한 값을 저장한다. 인덱스는 테이블의 열을 생성한다.

   예 : - 이름, 나이, 주소 우리는 세 개의 열이 사용자라는 데이터베이스 테이블이있다. 사용자 테이블 행의 수천을 가지고 있다고 가정합니다.

   자,하자 우리는 '존'라는 이름하는 모든 사용자의 모든 세부 사항을 찾을 수있는 쿼리를 실행할 것을 말한다. 우리는 다음 쿼리를 실행하는 경우 :

   SELECT * FROM User 
   WHERE Name = 'John'
   

   데이터베이스 소프트웨어는 문자 그대로 행의 이름은 '존'이 있는지 여부를 확인하기 위해 사용자 테이블의 모든 단일 행을보고해야합니다. 시간이 오래 걸릴 것입니다.

   인덱스가 도움이 곳은 다음과 같습니다 지수는 본질적 요구를 조사 할 것을 테이블의 레코드 / 행의 수를 삭감하여 검색 쿼리 속도를하는 데 사용됩니다.

   어떻게 인덱스를 만드는 방법 :

   CREATE INDEX name_index
   ON User (Name)
   

   색인은 열 값 (예 : 존)으로 구성되어 하나 개의 테이블에서, 그 값들은 데이터 구조에 저장된다.

7. ==============================

   7.그냥 책의 색인과 같은 데이터베이스 인덱스 생각합니다.

   그냥 책의 색인과 같은 데이터베이스 인덱스 생각합니다.

   당신은 개에 대한 책을 가지고 당신은 당신이 책의 모든 페이지를 통해 코스 플립의 수와 당신이 찾고있는 것을 발견,하자의 말, 셰퍼드에 대한 정보를 찾으려면 - 그러나 물론 이것은 시간이 소요되지 않으며, 매우 빠르게.

   또 다른 옵션은 그냥 책의 색인 섹션으로 이동 한 후 당신은 당신이 찾고있는 엔티티 (이 경우에, 셰퍼드)의 이름을 사용하여 찾고도에 페이지 번호에서 찾고있는 것을 찾을 수있는, 즉이다 빨리 당신이 찾고있는 것을 찾을 수 있습니다.

   데이터베이스에서 페이지 번호가 실체가있는 디스크의 주소로 데이터베이스를 지시하는 포인터라고합니다. 같은 독일 셰퍼드 비유를 사용하여, 우리는 0x77129는 독일 셰퍼드의 행 데이터가 저장되는 디스크의 주소 같은 것을 ( "독일 셰퍼드", 0x77129)를 가질 수있다.

   즉, 인덱스는 데이터 구조가되도록 질의 검색 속도 등의 테이블 저장 특정 열의 값.

8. ==============================

   8.그냥 응용 프로그램이 더 삽입 / 업데이트 작업을 필요로하는 경우 빠른 제안 .. 인덱싱 비용으로 사용하면 추가 쓰기 및 저장 공간, 그래서, 당신은 인덱스없이 테이블을 사용 할 수 있습니다, 그러나 검색 작업을 더 많은 데이터를 필요로하는 경우, 당신은 인덱스 가야 표.

   그냥 응용 프로그램이 더 삽입 / 업데이트 작업을 필요로하는 경우 빠른 제안 .. 인덱싱 비용으로 사용하면 추가 쓰기 및 저장 공간, 그래서, 당신은 인덱스없이 테이블을 사용 할 수 있습니다, 그러나 검색 작업을 더 많은 데이터를 필요로하는 경우, 당신은 인덱스 가야 표.

from https://stackoverflow.com/questions/1108/how-does-database-indexing-work by cc-by-sa and MIT license