스마트 웹개발/SQL 활용

05. 인덱스 활용

Hweb 2021. 5. 18. 14:36

인덱스 활용

테이블 조회 시간을 단축하기 위해 사용하는 인덱스의 개념을 이해하고, 인덱스를 생성하는 DDL(Data Definition Language) 명령문을 작성할 수 있다.

인덱스 개요

1. 인덱스 개념

인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주 는 자료구조를 일컫는다. 인덱스는 다음 그림과 같이 테이블의 특정 레코드 위치를 알려주는 용도로 사용하는데, 이러한 인덱스는 자동으로 생성되지 않는다.

PK 컬럼은 PK를 생성할 때 자동으로 인덱스가 생성된다. 즉, PK 컬럼은 PK를 생성할 때 자동으로 PK 인덱스가 생성된다. 예를 들어 위의 그림과 같은 테이블에서 일련번호를 기본키(Primary Key)로 하는 경우, 일련번호에 대한 인덱스는 자동으로 생성되나, 생년월일이나 이름을 기준으로 하는 인 덱스는 자동으로 생성되지 않는다. 다음 질의문을 보자.

select * from table_great_men where 이름 = ‘이순신’;

조건문 where 절에서 `이름'을 비교하고 있다. 이 경우 해당 테이블의 `이름' 컬럼에 인덱 스가 없는 경우, 테이블의 전체 내용을 검색하(Table full scan)게 된다. 반면, 인덱스가 생성되어 있다면 테이블의 일부분을 검색(Range scan)하여 데이터를 빠르 게 찾을 수 있다. 조건절에 `='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도 를 높일 수 있다. 하지만 자동으로 생성되지 않기 때문에 DB 사용자는 질의문을 분석하여 인덱스를 생성해야 한다.

인덱스 사용

1. 인덱스 사용 주체

[그림 2-1]에서 `이름' 컬럼에 대한 인덱스가 생성되어 있다면 데이터를 빠르게 찾을 수 있다. 이때 빠르게 찾는 행위의 주체는 DBMS이다. 즉, DBMS는 인덱스를 사용하여 빠른 검색을 수행한다. 이를 위해 DB 사용자는 DBMS가 인덱스를 사용할 수 있게 준비해 주어 야 한다. 따라서 DB 사용자 입장에서는 인덱스를 사용하는 개념보다는 준비하는 개념으로 접근해야 한다. 인덱스 준비 방법에 대해 알아보도록 하자.

2. 인덱스 준비

DB 사용자가 인덱스에 대해 조작할 수 있는 방법으로는 `생성, 삭제 그리고 변경' 조작이 있다. 참고로 인덱스 조작 명령은 SQL 표준화에 포함되지 않아 DBMS 제품 공급사마다 사용법이 약간씩 다르다. 여기서는 각 조작에 대한 개념을 익히도록 하자

(1) 인덱스 생성

인덱스 생성 문법은 다음과 같다.

CREATE [UNIQUE] INDEX ON ();

여기서 각각의 파라미터가 의미하는 내용은 다음과 같다

(2) 인덱스 삭제

인덱스 삭제 명령 형식은 다음과 같다.

DROP INDEX ;

여기서 각각의 파라미터가 의미하는 내용은 다음과 같다.

(2) 인덱스 삭제

인덱스 삭제 명령 형식은 다음과 같다. DROP INDEX ;

index_name은 생성된 인덱스 이름을 의미한다. 인덱스 관련 명령어에 대한 SQL 표 준이 없기에 제품별 Drop 명령문의 사용법은 약간씩 다르다. 보통 인덱스를 테이블의 종속 구조로 생각하여 인덱스를 삭제하기 위해 테이블의 변경을 가하는 형식의 명령 을 사용한다. 즉, ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사 용된다.

(3) 인덱스 변경

인덱스에 대한 정의를 변경하는 명령문 형식은 다음과 같다.

ALTER [UNIQUE] INDEX ON

();

한 번 생성된 인덱스에 대해 변경이 필요한 경우는 드물다. 또 인덱스 관련 SQL문은 표준화가 안 되었으므로 인덱스 변경에 대한 명령문 지원 여부 및 방법은 벤더별로 다르다. 일부 제품은 인덱스에 대한 변경 SQL문이 없다. 이 경우 기존 인덱스를 삭제 하고 신규 인덱스를 생성하는 방식으로 사용이 권고되고 있다.

반응형