[SQL] 인덱스

인덱스

 

인덱스는 테이블에 붙여진 색인이라 할 수 있으며,  검색속도의 향상을 위해 사용된다.

여기서 '검색'이란 SELECT 명령에 WHERE 구로 조건을 지정하고 그에 일치하는 행을 찾는 일련의 과정을 말한다. 검색은 탐색이라고도 불린다. 테이블에 인덱스가 지정되어 있으면 효율적으로 검색할 수 있으므로 WHERE로 조건이 지정된 SELECT 명령의 처리 속도가 향상된다.

인덱스의 구조도 목차나 색인과 비슷하다. 목차나 색인에 제목/키워드별 페이지 번호가 적혀있듯, 데이터베이스의 인덱스에는 검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.

인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성된다. 인덱스는 테이블에 의존하는 객체라 할 수 있다. 대부분의 데이터베이스에서는 테이블을 삭제하면 인덱스도 같이 삭제된다.

 

 

검색에 사용하는 알고리즘

 

데이터베이스의 인덱스에 쓰이는 대표적인 검색 알고리즘으로는 '이진 트리(binary search)'가 있으며, 그 다음으로는 '해시'가 유명하다.

이진 트리는 정확히 말하면 탐색 방법이라기보다 데이터 구조에 가깝다. 탐색 방법으로 말하자면 '이진탐색(binary search)'이 된다. 이때 이진탐색에서 검색하기 쉬운 구조로 한 것이 이진 트리이다.

 

1. 풀 테이블 스캔(full table scan)

인덱스가 지정되지 않은 테이블을 검색할 때는 풀 테이블 스캔이라 불리는 검색방법을 사용한다. 처리방법은 단순한데, 테이블에 저장된 모든 값을 처음부터 차례로 조사해나가는 것이다. 

 

2. 이진 탐색(binary search)

이진 탐색은 차례로 나열된 집합에 대한 유효한 검색 방법이다. 처음부터 순서대로 조사하는 것이 아니고 집합을 반으로 나누어 조사하는 검색방법이다. 실제 데이터베이스에는 수만, 수천만 건의 행이 있다. 풀 테이블 스캔을 한다면 데이터 수에 비례해 비교횟수도 늘어나지만 이진 탐색은 데이터 수가 배가 되어도 비교 횟수는 1회밖에 늘어나지 않는다. 이런 점에서 이진 탐색이 우위에 있는 것이다.

 

3. 이진 트리(binary tree)

이진 탐색은 고속으로 검색할 수 있는 탐색 방법이지만 데이터가 미리 정렬되어 있어야 한다. 하지만 테이블 내의 행을 언제나 정렬된 상태로 두는 것은 힘든 작업이다. 

일반적으로는 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어진다. 이때 이진 트리라는 데이터 구조로 작성된다.

트리는 노드(node)라는 요소로 구성된다. 각 노드는 두 개의 가지로 나뉘는데, 노드의 왼쪽 가지는 작은 값으로 오른쪽 가지는 큰 값으로 나뉘어져 있다. 두 개의 가지로 분기하는 구조라서 이진트리라 불리는 것이다.

검색은 이진 트리의 가지를 더듬어 가면서 행해진다. 이진 탐색에서는 가운데 값부터 검색하기 시작했지만, 이진 트리의 경우에는 트리의 루트 노드부터 시작하여 원하는 수치와 비교해서 더 크면 오른쪽 가지를, 작으면 왼쪽의 가지를 조사해 나간다.

이진 트리에서는 구조 자체가 검색하기 쉬우므로 가지를 따라 이동하기만 하면 된다.

하지만, 이진 트리에서 '같은 값을 가지는 노드를 여러 개 만들 수 없다'라는 특성은 키에 대하여 유일성을 가지게 할 경우에만 유용하다. 그래서 기본키 제약은 이진 트리로 인덱스를 작성하는 데이터베이스가 많은 것 같다. 

 

 

인덱스 작성

 

인덱스는 CREATE INDEX 명령으로 만든다.

인덱스에 이름을 붙여 관리하는데, 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다르다. Oracle이나 DB2 등에서 인덱스는 스키마 객체가 된다. 따라서 스키마 내에 이름이 중복되지 않도록 지정해 관리한다.

한편 SQL Server나 MySQL에서 인덱스는 테이블 내의 객체가 된다. 따라서 테이블 내에 이름이 중복되지 않도록 지정해 관리한다.

-- 형식
CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...)

-- 예시
CREATE INDEX isample1 ON sample1(no);

 

 

인덱스 삭제

 

인덱스는 DROP INDEX 명령으로 삭제한다. DROP 할 때는 다른 객체와 동일하게 인덱스 이름만 지정하면 된다. 다만 테이블 내 객체로서 작성하는 경우에는 테이블 이름도 지정한다.

인덱스는 테이블에 의존하는 객체이다. DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제된다. 인덱스만 삭제하는 경우에는 DROP INDEX를 사용한다.

-- 형식(스키마 객체의 경우)
DROP INDEX 인덱스명

-- 형식(테이블 내 객체의 경우)
DROP INDEX 인덱스명 ON 테이블명

-- 예시
DROP INDEX isample1 ON sample1;

 

 

EXPLAIN

 

실제로 인덱스를 사용해 검색하는지를 확인하려면 EXPLAIN 명령을 사용한다.

EXPLAIN에 뒤이어 확인하고 싶은 SELECT 명령 등의 SQL 명령을 지정하면 된다. 

-- 형식
EXPLAIN SQL 명령

-- 예시
EXPLAIN SELECT * FROM sample1 WHERE a = 'a';