인덱스(Index) 작성과 삭제

안녕하세요. 오늘은 '색인'이라고도 불리는 인덱스(index)에 대해서 알아볼 것이며, 인덱스는 데이터베이스 객체 중 하나입니다. 이번 포스팅에서는 인덱스가 무엇인지, 그 역할과 구조는 어떻게 이루어졌는지 알아보겠습니다.

인덱스?

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

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

인덱스를 생성과 삭제는 아래와 같은 문법을 사용합니다. (인덱스는 데이터베이스 객체 중 하나로 DDL을 사용하여 작성하거나 삭제)

CREATE INDEX    // 인덱스 생성
DROP INDEX      // 인덱스 삭제

인덱스 작성

인덱스는 CREATE INDEX 명령으로 생성합니다. 인덱스에 이름을 붙여 관리하는데, 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다릅니다. 따라서 테이블 내의 이름이 중복되지 않도록 지정해야됩니다. 

인덱스를 작성할 때는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정할 필요가 있으며, 이때 열은 복수로도 지정이 가능합니다. 인덱스의 네임스페이스가 데이터베이스 제품마다 다르다는 점만 주의하면 됩니다.

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

그럼 아래의 간단한 예제를 통해 알아보도록 하겠습니다.

위의 예제를 보시면, sample54 테이블의 no 열에 isample65라는 인덱스를 지정합니다. 인덱스를 작성할 때는 저장장치에 색인용 데이터가 만들어집니다. 테이블 크기에 따라 인덱스 작성시간도 달라지는데, 행이 대량으로 존재할 경우 시간도 많이 걸리고 저장 공간도 많이 소비합니다.

인덱스 삭제

인덱스는 DROP INDEX 명령으로 삭제합니다. DROP 할 때는 다른 객체와 동일하게 인덱스 이름만 지정하면 됩니다. 다만, 테이블 내 객체로서 작성하는 경우에는 테이블 이름도 지정합니다.(이때 인덱스를 구성하는 열은 지정할 필요가 없습니다.)

DROP INDEX 인덱스 명                // 스키마 객체의 경우
DROP INDEX 인덱스 명 ON 테이블 명   // 테이블 내 객체의 경우

인덱스 삭제 또한 간단한 예제를 통해 살펴보겠습니다.

EXPLAIN 명령

인덱스 작성을 통해 쿼리의 성능 향상을 기대할 수 있습니다. 이 때, 실제로 인덱스를 사용해 검색하는지를 확인하려면 EXPLAIN 명령을 사용하면 됩니다.

EXPLAIN SQL 명령

EXPLAIN 명령의 문법은 간단합니다. EXPLAIN에 뒤이어 확인하고 싶은 SELECT 명령 등의 SQL 명령을 지정하면 됩니다. 다만, 이 SQL 명령은 실제로 실행되지 않습니다. EXPLAIN은 표준 SQL에는 존재하지 않는 데이터베이스 제품 의존형 명령이지만, 어떤 데이터베이스 제품이라도 이와 비슷한 명령을 지원합니다.

그럼 간단한 예제를 통해 EXPLAIN 사용법을 알아보겠습니다.

sample54테이블의 no열에는 isample65라는 인덱스가 작성되어 있습니다. EXPLAIN 뒤를 잇는 SELECT 명령은 no 열의 값을 참조해 검색하므로 isample65를 사용해 검색합니다.

그럼, WHERE 조건을 바꾸면 어떻게 변하는지 알아보겠습니다. no열을 사용하지 않도록 조건을 변경해서 실행해보겠습니다.

no열을 사용하지 않도록 조건을 변경하면 인덱스를 사용할 수 없는 것을 확인할 수 있습니다.

이것으로 기본적인 인덱스의 작성과 삭제 및 EXPLAIN 명령의 사용법에 대해서 알아보았스며, 포스팅을 마치도록 하겠습니다. : )