기본 인덱스 (primary index)와 보조 인덱스 (secondary index)의 차이점

유사점

• 두 인덱스 구조들은 데이터베이스에서 분리된 객체이다. 이는 테이블과 그에 대응되는 인덱스 (기본 또는 보조 인덱스)는 서로 분리된 구조로 존재한다.
• 모두 질의문들은 먼저 인덱스를 찾아보고, 그것이 가리키는 레코드들을 적재하여 결과로 사용한다.
• 두 종류의 인덱스들 안에 존재하는 인덱스 블록들은 정렬된 엔트리들을 보관한다; 즉, 실제 인덱스 엔트리가 어떻든 (일반적으로 <인덱스 키, 행 주소> 형태임), 인덱스 블록 안에 존재하는 엔트리들은 인덱스/검색 키에 대하여 정렬되어있다.

차이점

기본 인덱스 (primary index)

• 기본 인덱스는 데이터 블록 안의 행들의 조직과 저장소에 영향을 미친다. 데이터 블록들은 실제 행 데이터를 저장하는 디스크 블록들이다 (다중 칼럼).
• 기본 인덱스는 데이터 블록들 안의 행들을 통해 인덱스 키를 정렬한다.
• 기본 인덱스는 데이터 블록들 안의 행들의 정렬을 강요한다.
• 아래의 다이어그램은 "Garcia Molina et al"에 의한 데이터베이스 시스템 구현"으로부터 빌린 것이다. 이는 인덱스 블록 (왼쪽)에서 어떻게 인덱스 엔트리가 데이터 블록 (오른쪽)의 대응되는 행들을 가리키는 포인터들을 포함하는지 보여주고 있다. 각각의 데이터 블록은 인덱스 키 당 정렬된 순서에서의 행들을 포함한다.

• 기본 인덱스는 키와 비키 칼럼에서 생성될 수 있다. 기본 인덱스는 오직 기본 키만 사용가능하지는 않다. 물론, 일반적으로 기본 키를 기반으로 생성된다.
• 하나의 테이블에 오직 하나의 기본 인덱스만 가질 수 있다.

보조 인덱스 (secondary index)

• 보조 인덱스는 데이터 블록에서 실제로 조직화된 행들에 전혀 영향을 미치지 않는다.
• 보조 인덱스는 순서를 가지지 않는다 (정렬되지 않아도 상관없다). 인덱스 블록의 인덱스 키만 정렬되어야 한다.

• "Garcia Molina et al에 의한 데이터베이스 시스템 구현"으로부터 빌린 아래의 다이어그램은 어떻게 인덱스 블록 (왼쪽)의 인덱스 엔트리들이 데이터 블록 (오른쪽)의 대응되는 행들을 가리키는 포인터들을 포함하는지 보여준다. 데이터 블록은 인덱스 키에 대하여 정렬된 행들을 가지지 않는다.

비교

• 먼저, 사용자는 다중 보조 인덱스를 정의할 수 있다. 왜냐하면 그것들은 테이블 안의 행들의 구조에 전혀 영향을 미치지 않는다. 그러나, 오직 하나의 기본 인덱스만 가질 수 있다.
• 기본 인덱스는 기본 키를 가질 필요는 없으므로 기본 인덱스는 중복된 인덱스 키들을 가질 수 있다. 사실 위에서 보여주는 보조 인덱스는 중복된 키이다.
• 명백하게 만약 기본 인덱스가 기본 키를 기반으로 생성되었다면, 중복된 인덱스 키를 가질 수 없다.
• 기본 인덱스와 보조 인덱스는 범위 질의문들과 점 조회에 사용될 수 있다. 그러나, 범위 질의문은 모든 경우에서 기본 인덱스에서 더 빠를 것으로 예상된다. NON UNIQUE에서 점 조회는 기본 인덱스에서 더 빠를 것으로 예상된다. 그러나, 만약 인덱스가 UNIQUE라면 기본 인덱스와 보조 인덱스에서 점 조회는 이상적으로 같은 시간을 가지게 된다 - 최소 같은 양의 I/O를 가진다.
• 그 이유는 기본 인덱스는 데이터 블록의 행들을 정렬되도록 요구하기 때문이다. 따라서 만약 사용자가 WHERE KEY=>20 AND KEY <=40에 대한 행들을 찾고자 한다면, 적은 디스크 블록만 읽어들이면 되고 이는 적은 I/O를 유발시킨다. 키와 대응되는 행들은 같은 데이터 블록에 있기 때문이다 (인덱스가 유니크하지 않더라도)
• 반대로, 보조 인덱스는 행들의 구조를 전혀 통제하지 않는다. 따라서 20과 40 사이의 모든 레코드들을 찾는 예시에서, 각 키에 대응되는 레코드들은 그 데이터 블록에 놓여있게 도니다. 이는 명백히 더 많은 I/O를 유발시키고 질의문들은 보조 인덱스에서 더 비효율적이라는 것이다.
• 유니크한 인덱스 키들에 대한 점 조회에 대하여, 인덱스가 기본인지 보조인지 중요하지 않다. 조회의 결과는 행의 주소이고 데이터베이스는 실제 레코드를 가지도록 한다. 따라서 단일 I/O는 발생한다.
• 기본 인덱스는 데이터 블록의 행들이 정렬되어 있도록 요구하기 때문에 DML들은 비효율적이게 된다. DML들은 데이터 블록에서 정렬된 순서로 행들을 보관할 필요가 있기 때문에, INSERT/UPDATE는 삽입이 순차적 순서에 따라 이루어지므로 이를 사용자가 잘 살펴지 않으면 행의 빈번한 이동을 유발시킨다.
• 데이터 블록 내의 행의 이동에 대한 결과와 관련된 모든 DML은 대응되는 기본 인덱스 구조를 갱신하는 것을 요구한다. 왜냐하면 인덱스 엔트리 <인덱스 키, 행 주소> 새로운 행 주소로 업데이트해야 하기 때문이다.
• 행 이동은 마찬가지로 보조 인덱스 구조를 갱신해야 하지만, 행 이동을 유발하는 INSERT의 확률은 보조 인덱스에서 상대적으로 적은데 그 이유는 INSERT는 데이터 블록에서 정렬된 순서로 행을 보관해야 할 필요가 없기 때문이다.

https://www.quora.com/What-is-difference-between-primary-index-and-secondary-index-exactly-And-whats-advantage-of-one-over-another