인덱스의 사용

어떤 SQL 명령문은 예측할 수 있는 일정한 실행 시간을 갖는다.
CREATE TABLE과 GRANT 명령문은 실행 시간을 감소시킬 방법이 없지만,
SELECT, UPDATE, DELETE 명령문은 그 구성에 따라서 몇 초 또는 몇 분이 걸리기도 한다.

SELECT, UPDATE, DELETE 명령문의 실행 시간을 감소시키기 위한 많은 기법들이 있다.
이러한 기법으로는 명령문을 재구성하는 것에서부터 더 빠른 컴퓨터를 구입하는 방법까지.

→인덱스가 존재할 때와 존재하지 않을 때의 실행 시간이 어떻게 영향을 주는 지 알아본다.

SQL은 테이블에 있는 행을 접근하는 방법으로 2가지 방법이 있는데, 

① 순차적 접근 방법 - 행 단위로 테이블을 순서적으로 보여준다. SQL은 테이블에서 각 행을 읽는다. 테이블이 많은 행을 가지고 있을 때 오직 하나의 행만을 찾는다면 많은 시간을 소비하고 비효율적이다. 이는 전화번호부에서 페이지 단위로 원하는 사람을 찾는 것과 유사하다. 만약 이름이 L로 시작되는 사람을 찾는다면 문자A에서부터 순서적으로 찾으려 하지 않을 것이다.
② 직접 접근 방법 - 요구하는 특성을 나타내는 행만을 읽는다. 이렇게 하기 위해서는 인덱스가 필요하다. SQL에서 인덱스는 많은 노드를 가지고 있는 트리처럼 구성된다.

인덱스를 만드는 이유는 가장 큰 이유가 데이터를 빠르게 검색하기 위함 입니다. 

인덱스를 만드는 두번째 이유는 Row의 유일성을 유지하기 위한 것입니다. UNIQUE 인덱스가 여기에 해당합니다. 예를 들어 사원 테이블에는 동일한 사원번호를 갖는 사원 정보가 있어서는 안됩니다. 이런 경우 사원번호에 인덱스를 이용하여 유일성을 부여할 수 있습니다. 

인덱스는 SQL Server Performance 튜닝에 있어서 가장 기본적인 것입니다. 물론 비싼 돈을 드려 빵빵한 서버를 구축하는 것도 성능 향상을 꾀하는 방법이긴 하지만 돈 안들이고 성능을 향상 시키는 방법, 그것이 인덱스 입니다. 

인덱스의 안좋은 점 

인덱스를 만들게 되면 그 정보를 유지하기 위해서 디스크 공간도 필요하게 되고, 인덱스가 걸려 있는 테이블은 인덱스가 없을 때보다 데이터를 추가하거나 변경할 때 너 많은 시간이 소요 됩니다. 이런 이유로 인덱스를 만들 때 해당 테이블의 용도를 정확히 이해해야 합니다. 만일 읽기 전용의 테이블이라면 인덱스를 걸어 주는게 좋고, 수정 및 추가가 엄청나게 이루어지는 테이블이라면 인덱스 거는것이 역효과가 날 수 있습니다. 

 

인덱스 작업
지금 당장 책장에서 아무 책이나 골라 맨 뒤쪽에 있는 인덱스(색인) 부분을 펼쳐보기 바란다. 가나다순(혹은 ABC 순)으로 정렬되었고, 키워드가 같을 땐 페이지 순으로 정렬된 것을 볼 수 있을 것이다. 인덱스를 이용하면 원하는 키워드를 포함한 페이지를 빠르게 찾을 수 있다. 인덱스가 없다면? 책 전체를 한 장씩 훑어가며 찾는 수밖에 없다. 데이터베이스에서 사용하는 인덱스도 다르지 않다. 대용량 테이블에서 우리에게 필요한 데이터를 빨리 찾으려면 인덱스의 도움이 필요하다. 인덱스가 아예 없거나, 적절한 인덱스를 찾지 못하면 테이블 전체를 읽어야 하기 때문에 시간이 오래 걸리는 것은 당연하다. 

모든 DBMS는 나름의 다양한 인덱스를 제공하는데, 저장방식과 구조, 탐색 알고리즘이 조금씩 다르긴 해도 원하는 데이터를 빨리 찾도록 돕는다는 근본적인 목적은 같다. 여기서, 가장 일반적으로 사용되는 B*Tree 인덱스 구조부터 살펴보자. 좀 더 다양한 인덱스 구조는 뒤에서 보게 될 것이다.

 [그림 Ⅲ-4-1]에 예시한 인덱스 칼럼은 양의 정수만 저장할 수 있는 데이터 타입이라고 가정하고 그린 것이다. 이름에서 알 수 있듯이 B*Tree 인덱스는 나뭇잎으로 무성한 나무를 뒤집어 놓은 듯한 모습이다. 나무를 뒤집어 놓았으므로 맨 위쪽 뿌리(Root)에서부터 가지(Branch)를 거쳐 맨 아래 나뭇잎(Leaf)까지 연결되는 구조다. 처음에는 단 하나의 루트 블록에서 시작하겠지만 데이터가 점점 쌓이면서 루트, 브랜치, 리프 노드를 모두 갖춘 풍성한 나무로 성장한다. 중간에 물론, 루트와 리프만으로 구성된 2단계 구조를 거친다. 참고로, 루트에서 리프 블록까지의 거리를 인덱스 깊이(Height)라고 부르며, 인덱스를 반복적으로 탐색할 때 성능에 영향을 미친다. 루트와 브랜치 블록은 각 하위 노드들의 데이터 값 범위를 나타내는 키 값과, 그 키 값에 해당하는 블록을 찾는 데 필요한 주소 정보를 가진다. 리프 블록은 인덱스 키 값과, 그 키 값에 해당하는 테이블 레코드를 찾아가는 데 필요한 주소 정보(ROIWD)를 가진다. 키 값이 같을 때는 ROWID 순으로 정렬된다는 사실도 기억하기 바란다. 리프 블록은 항상 인덱스 키(Key) 값 순으로 정렬돼 있기 때문에 ‘범위 스캔(Range Scan, 검색조건에 해당하는 범위만 읽다가 멈추는 것을 말함)’이 가능하고, 정방향(Ascending)과 역방향(Descending) 스캔이 둘 다 가능하도록 양방향 연결 리스트(Double linked list) 구조로 연결돼 있다. 아래는 null 값을 인덱스에 저장하는 데 있어 Oracle과 SQL Server의 차이점을 설명한 것이다.

 

인덱스 탐색

인덱스 탐색 과정을 수직적 탐색과 수평적 탐색으로 나눠서 설명할 수 있다. 수평적 탐색은 인덱스 리프 블록에 저장된 레코드끼리 연결된 순서에 따라 좌에서 우, 또는 우에서 좌로 스캔하기 때문에 ‘수평적’이라고 표현한다. 수직적 탐색은 수평적 탐색을 위한 시작 지점을 찾는 과정이라고 할 수 있으며, 루트에서 리프 블록까지 아래쪽으로 진행하기 때문에 ‘수직적’이다. [그림 Ⅲ-4-1]에서 키 값이 53인 레코드를 찾아보자.

① 우선 루트 블록에서 53이 속한 키 값을 찾는다. 두 번째 레코드가 선택될 것이므로 거기서 가리키는 3번 블록으로 찾아간다. ② 3번 블록에서 다시 53이 속한 키 값을 찾는다. 여기서는 첫 번째 레코드가 선택될 것이므로 9번 블록으로 찾아간다. ③ 찾아간 9번은 리프 블록이므로 거기서 값을 찾거나 못 찾거나 둘 중 하나다. 다행히 세 번째 레코드에서 찾아지므로 함께 저장된 ROWID를 이용해 테이블 블록을 찾아간다. ROWID를 분해해 보면, 오브젝트 번호, 데이터 파일번호, 블록번호, 블록 내 위치 정보를 알 수 있다. ④ 테이블 블록에서 레코드를 찾아간다.

사실 ④번이 끝은 아니다. [그림 Ⅲ-4-1] 인덱스가 Unique 인덱스가 아닌 한, 값이 53인 레코드가 더 있을 수 있기 때문이다. 따라서 9번 블록에서 레코드 하나를 더 읽어 53인 레코드가 더 있는지 확인한다. 53인 레코드가 더 이상 나오지 않을 때까지 스캔하면서 ④번 테이블 액세스 단계를 반복한다. 만약 9번 블록을 다 읽었는데도 계속 53이 나오면 10번 블록으로 넘어가서 스캔을 계속한다.


인덱스의 생성과 삭제 

추가하여 만들기
CREATE INDEX <인덱스명> ON <테이블명> ( 칼럼명1, 칼럼명2, ... );

테이블 생성시 만들기
테이블 생성 명령문 끝에....
INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )
UNIQUE INDEX <인덱스명> ( 칼럼명 ) --> 항상 유일해야 함.

이렇게도 생성한다
ALTER TABLE <테이블명> ADD INDEX <인덱스명> ( 칼럼명1, 칼럼명2, ... );

인덱스 보기
SHOW INDEX FROM <테이블명>;

인덱스 삭제
ALTER TABLE <테이블명> DROP INDEX <인덱스명>;

MySQL과 인덱스
MySQㅣ에서 CREATE INDEX 명령문만이 인덱스를 생성하는 유일한 방법이 아니다. 만약 기본 키 또는 대체가 CREATE TABLE 명령문에 포함된다면 MySQL는 유일한 인덱스를 자동적으로 생성한다. 

UNIQUE 인덱스
- PRIMARY KEY나 UNIQUE 제약을 설정하면 자동적으로 UNIQUE 인덱스가 만들어집니다.
- 만일 테이블에 데이터가 존재하는 상태라면 UNIQUE 인덱스를 만들 때 중복되는 값이 있는지 체크 합니다. 만일 중복된 값이 있다면 UNIQUE 인덱스를 만들수 없습니다.
- UNIQUE 인덱스가 설정되어 있으면 INSERT나 UPDATE시 데이터 값의 중복 여부를 확인해서 중복되면 값을 받아들이지 않고 에러메세지를 표시합니다.