Database Index란
데이터베이스는 파일들의 집합으로 저장되고, 각 파일은 일반적으로 동일한 유형의 레코드들의 모임으로 이루어진다.
이 파일들은 일반적으로 디스크와 같은 보조 기억 장치에 저장된다.
Index는 DBMS가 파일 내의 특정 레코드들을 빠르게 찾을(특히 SELECT) 수 있도록 하는 데이터 구조이므로 인덱스를 통하여 질의를 수행하면 응답시간이 향상된다.
인덱스의 유형은 크게 단일 단계 인덱스와 다단계 인덱스로 구분된다.
Index의 장/단점
1) 장점
- 검색 속도가 빨라질 수 있다.
- 그 결과 해당 쿼리의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상된다.
2) 단점
- 인덱스가 데이터 베이스의 공간을 차지하기 때문에 대략 데이터 베이스 크기의 10% 정도의 추가적인 공간이 필요하다.
- 데이터의 변경 작업(INSERT, UPDATE, DELETE)가 자주 일어나는 경우에는 오히려 성능이 나빠질 수 있다.
단, 소수의 데이터들을 수정하거나 삭제하는 연산의 속도는 향상되기도 한다. 왜냐하면 데이터를 수정하거나 삭제하려면 먼저 해당 데이터를 찾아야 하기 때문이다.
정리하면,
릴레이션이 매우 크고, 질의에서 릴레이션의 튜플들 중에서 일부를 검색하고, WHERE절이 잘 표현되어있을 때는 인덱스가 성능에 무척 도움이 된다.
Index의 종류
MySQL에서 사용되는 인덱스의 종류는 클러스터형 인덱스와 보조 인덱스로 나눌 수 있고, 다른 DBMS에서는 클러스터형 인덱스와 비클러스터형 인덱스로 종류를 나누기도 한다. 즉, 보조 인덱스와 비클러스터형 인덱스는 거의 비슷한 개념이다.
클러스터형 인덱스는 '영어 사전'과 같은 책처럼 알파벳 순으로 정렬되어있고, 해당 알파벳에 가보면 각 단어 옆에 뜻이 적혀 있는 형식에 비유 할 수 있고, 보조 인덱스는 '책 뒤에 있는 찾아보기'와 같이 되어있는 것에 비유 할 수 있다.
다만 여기서는 이러한 분류보다 <데이터베이스 배움터 개정 3판>에서 인덱스를 분류한 것에 따라 각 인덱스가 무엇인지를 정리해보았다.
1. 단일 단계 인덱스
단일 단계 인덱스의 각 엔트리는 <탐색 키, 레코드에 대한 포인터(주소)> 로 이루어며 각 엔트리들은 탐색 키 값의 오름차순으로 정렬된다.
탐색 키란 인덱스가 정의된 필드로, 탐색 키의 값들은 후보 키처럼 각 튜플마다 반드시 고유하지는 않다. 즉, 두 개 이상의 튜플들이 동일한 탐색 키 값을 가질 수 있다는 것이다. 또한 탐색 키로 사용되는 것은 키를 구성하는 애트리뷰트 뿐만 아니라 어떤 애트리뷰트도 탐색 키로 사용될 수 있다.
탐색 키 값 => 인덱스 => 포인터 => 레코드들을 포함한 블록 => 만족하는 레코드(들) 의 순서로 인덱스를 통한 레코드 검색이 이루어진다.
앞서 각 인덱스의 각 엔트리가 탐색 키 값의 오름차순으로 정렬되어 있다고 하였는데, 그렇기 때문에 이진 탐색을 사용할 수 있고 탐색 시간이 훨씬 적게 소요된다.
1) 기본 인덱스 (primary Index)
탐색 키가 데이터 파일의 기본 키인 인덱스를 기본 인덱스라고 하고, 기본 인덱스는 기본 키의 값에 따라 정렬된 데이터 파일에 대해 정의된다.
즉 데이터 파일은 기본 키 값이 증가하는 순서로 정렬되어 있고, 각 데이터 블록의 첫 번째 레코드의 기본 키 값이 인덱스 엔트리에 포함된다.
즉 전체 파일에 대한 탐색이 아니라 각 블록당 하나의 엔트리(블록의 앵커 엔트리에 대한 키 값) 을 가지므로, 기본 인덱스는 희소 인덱스 이다.
2) 클러스터링 인덱스 (clustering Index)
클러스터링 인덱스는 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의된다.
클러스터링 인덱스는 범위 질의에 유용하다. 범위의 시작 값에 해당하는 인덱스 엔트리를 먼저 찾고, 범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때 디스크에서 읽어오는 블록 수가 최소화된다.
3) 보조 인덱스 (secondary Index)
보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 화일에 대해서 정의된다. 보조 인덱스는 일반적으로 밀집 인덱스이므로, 같은 수의 레코드들을 접근할 때 보조 인덱스를 통하면 기본 인덱스를 통하는 경우보다 디스크 접근 회수가 증가한다.
※ 희소 인덱스 (sparse Index)와 밀집 인덱스 (dense Index)
희소 인덱스는 일부 키 값에 대해서만 인덱스에 엔트리를 유지하는 인덱스로, 일반적으로 각 데이터 블록마다 한 개의 탐색 키 값을 가진다. 밀집 인덱스는 각 데이터의 키 값마다 한 개의 엔트리를 가진다.
2. 다단계 인덱스
인덱스 자체가 큰 경우에는 인덱스를 탐색하는 시간도 오래걸린다. 그래서 인덱스 엔트리를 탐색하는 시간을 줄이기 위해 단일 단계 인덱스를 디스크 상의 하나의 순서 파일로 간주하고, 단일 단계 인덱스에서 다시 인덱스를 정의할 수 있다.
이러한 과정은 가장 상위 단계의 모든 인덱스(마스터 인덱스) 엔트리들이 한 블록에 들어갈 수 있을 때까지 이 과정을 반복한다.
각 인덱스들이 오름차순으로 정렬되어 있어야 하므로 인덱스의 갱신은 단일 단계 인덱스의 경우보다 복잡하다. 그러나 대부분의 데이터베이스 응용에서 갱신보다는 검색이 훨씬 자주 되므로 모든 DBMS에서는 인덱스를 다단계 인덱스로 사용하고, 보통 B+Tree 동작 방식을 사용한다.
SQL의 인덱스 정의문
※ SHOW INDEX FROM [릴레이션 이름] 하면 인덱스를 확인 할 수 있다.
1) SQL의 인덱스 정의문
SQL의 CREATE TABLE 문에서 PRIMARY KEY 절로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 기본 인덱스를 생성한다.
그리고 UNIQUE로 명시한 애트리뷰터에 대해서는 DBMS가 자동적으로 보조 인덱스를 생성한다.
2) 다수의 애트리뷰트를 사용한 인덱스 정의
한 릴레이션의 두 개 이상의 애트리뷰트들의 조합으로 하나의 인덱스를 정의하고 싶을 때,
자동으로 생성되는 인덱스가 아닌 다른 애트리뷰트에 추가로 인덱스를 정의하기 위해서는 DBMS마다 다소 구문이 다른 CREATE INDEX문을 사용해야 한다.
※ 이렇게 복합 애트리뷰트에 인덱스를 정의할 때 애트리뷰트들의 순서가 중요하다. 왜인지는 예시를 통해 알 수 있다.
예를 들어, EMPLOYEE 릴레이션의 (DNO, SALARY) 애트리뷰트에 대해 인덱스를 생성했다면,
CREATE INDEX EmpIndex ON EMPLOYEE (DNO, SALARY);인덱스가 사용될 수 있는 경우는
1. 인덱스가 정의된 두 애트리뷰트를 참조하는 WHERE절
SELECT *
FROM EMPLOYEE
WHERE DNO=3 AND SALARY=400;2. 인덱스가 정의된 두 애트리뷰트를 참조하는 범위 질의
SELECT *
FROM EMPLOYEE
WHERE DNA>=2 AND DNO<=3 AND SALARY>=300 AND SALARY<=400;3. 첫 번째 애트리뷰트만 참조하는 WHERE절
SELECT *
FROM EMPLOYEE
WHERE DNA>=2;인덱스가 사용될 수 없는 경우는
1. 두 번째 이후의 애트리뷰트만 참조하는 WHERE절
SELECT *
FROM EMPLOYEE
WHERE SALARY>=200;reference
<데이터 베이스 배움터 개정 3판> - 홍의경 지음
[이것이 MySQL이다] 09. MySQL 인덱스(1) ~ (3) 한빛미디어
'Computer Science > DB & SQL' 카테고리의 다른 글
| [Database] 무결성 제약조건 (0) | 2021.02.24 |
|---|