권순용의 DB 이야기

인덱스란 무엇인가

인덱스(index)는 SQL 수행 속도를 향상시키기 위해 고안됐다. 그 역할은 사전의 인덱스와 다르지 않다. 사전에서 ‘GIRL’이라는 단어를 찾을 때 어떻게 하는가 가장 먼저 사전의 인덱스에서 ‘G’를 찾고, 다음 두 번째 철자인 ‘I’를 찾을 것이다. 그런데 만약 인덱스가 없다면 어떻게 해야 할까 아마 사전의 전체를 한 장 한 장 넘기며 찾아야 할 것이다. 단지 GIRL이라는 한 단어를 찾기 위해 몇 천 아니 몇 십만 단어를 일일이 봐야 할 것이다.

데이터베이스도 인덱스가 없다면 전체 데이터를 일일이 액세스하고서야 원하는 데이터를 찾을 수 있다. 즉 데이터베이스 테이블에 인덱스가 없다는 것은 사전에 인덱스가 없는 것과 매한가지다. 이처럼 인덱스의 역할을 명확하다. 조회 성능 향상이 그것이다. 조회 성능 향상은 SELECT 절에만 국한되지 않는다. UPDATE, DELETE 수행도 해당 DML(Data Manipulation Language) 대상을 조회해야만 작업할 수 있기 때문이다. 그러므로 UPDATE, DELETE의 조회 성능을 높이는 데에는 인덱스가 필요하다.

<리스트 1> 인덱스가 필요한 UPDATE와 DELETE의 예UPDATE TAB1
SET COL1 = ‘AAA’
WHERE COL2 = ‘100’;

<리스트 1>의 SQL은 SELECT가 아니지만 TAB1 테이블에서 COL2 컬럼의 값이 100인 데이터를 추출해야 UPDATE가 가능할 것이다. 따라서 이러한 경우에도 테이블 조회가 발생하며, 인덱스를 통해 조회 속도를 높일 수 있다. 이처럼 다양한 작업에 쓰이는 인덱스라고 단점이 없는 것은 아니다. 인덱스의 단점은 다음과 같다.

DML 성능 저하

사전에 새로운 단어 하나를 추가하는 경우를 가정해 보자. 사전의 맨 끝에 해당 단어를 추가해서는 안 된다. 해당 단어의 철자를 바탕으로 추가해야 할 위치를 찾아 넣어야만 차후 해당 단어를 인덱스를 통해 빠르게 찾을 수 있기 때문이다. 사전에 단어를 추가하는 것은 생각보다 부담스러운 작업이다. 데이터베이스도 이와 같다. 테이블에 데이터를 INSERT하는 것은 여유 공간에 저장하면 그뿐이다. 그러나 해당 테이블의 인덱스에 정해진 위치값을 추가해야 한다. 그리고 그 위치를 찾는 데에도 부하가 뒤따른다. DELETE의 경우는 어떠할까 DELETE될 데이터의 인덱스 값을 찾아 연결 고리(LINK)를 제거해야 한다. INSERT 시에도 새로운 인덱스 값을 해당 인덱스의 정해진 위치에 저장해야 한다. 이 모두 시스템에 부하를 유발한다. 특히 UPDATE는 DELETE와 INSERT가 동시에 발생하므로 부하량이 더 많을 수밖에 없다.

이 사례들은 모두 DML로 인덱스를 관리하는 데 뒤따르는 성능 저하의 예다. 그렇다면 소량의 데이터에 대한 UPDATE는 어떠할까 인덱스 스캔 없이 테이블 전체를 스캔(FULL SCAN)한다면 이 또한 성능 저하의 원인이 될 것이다. 만약 작은 수의 인덱스로 모든 액세스 경로(ACCESS PATH)를 수용할 수 있다면 DML 수행에 따른 인덱스 관리 시간을 단축할 수 있을 것이다.

잘못된 인덱스 선정으로 인한 스토리지 용량 낭비

인덱스를 생성하는 순간에는 디스크 영역을 사용하게 된다. 이는 인덱스 생성 자체가 디스크에 실제 인덱스 데이터를 저장하기 때문이다. 테이블에 인덱스를 계속 추가하면 테이블에 따라 인덱스의 크기가 테이블보다 더 큰 경우도 더러 있다. 예컨대 100GB 디스크에 50GB 미만의 데이터가 담겨 있는데, 만약 데이터보다 인덱스 크기가 크다면 사용할 수 있는 디스크 공간이 거의 없게 된다. 이 경우 담당자는 디스크 증설을 요청할 것이다. 이는 생각보다 주변에서 흔히 볼 수 있는 일이다. 그런데 과연 디스크 증설만이 이 문제의 해답일까 상황에 따라 다르지만 이처럼 테이블 크기보다 인덱스 크기가 더 큰 것은 분명 어딘가에 비효율적인 문제가 있는 것이다. 해당 인덱스 중 몇 개만 제거해도 DML 성능 향상과 스토리지 용량 절감이라는 두 마리 토끼를 잡을 수 있다. 그러나 인덱스가 많다고 해서 일부 인덱스를 제거하는 것에는 고려해야 할 것이 많다. 이에 대해서는 추후 인덱스 튜닝 연재에서 보다 자세히 다룬다.

조회 성능 저하

앞서 설명했듯 인덱스의 목적은 조회 성능 향상이다. 그런데 다음과 같은 상황에서는 조회 성능이 오히려 저하될 수 있다.

- 잘못된 인덱스 사용
- 의도하지 않은 인덱스 사용

<리스트 2> ZT_LOG 테이블SQL> SELECT COUNT(*) SVC_LINE_CNT
FROM ZT_LOG
WHERE RGST_DT BETWEEN TO_CHAR(SYSDATE, 'YYYYMMDD') || '000000'
AND TO_CHAR(SYSDATE, 'YYYYMMDD') || '235959'
AND MGMT_NUM = :1
AND SVC_NUM = :2
AND LINE_NUM = :3;

<리스트 2>에서 ZT_LOG 테이블에 다음과 같은 인덱스가 있다고 가정하자.

- MGMT_NUM+SVC_NUM+LINE_NUM+RGST_DT 인덱스
- RGST_DT+MGMT_NUM+SVC_NUM+LINE_NUM 인덱스

이 두 인덱스 중 <리스트 2>에 최적의 SQL은 무엇일까 바로 MGMT_NUM+SVC_NUM+LINE_NUM+RGST_DT 인덱스다. 그러나 해당 SQL은 RGST_DT+ MGMT_NUM+SVC_NUM +LINE_NUM 인덱스를 이용할 수도 있다. 그렇다면 각각의 인덱스를 이용하는 경우는 어떻게 될까

● MGMT_NUM+SVC_NUM+LINE_NUM+RGST_DT 인덱스를 이용하는 경우
해당 인덱스를 이용하는 순간은 점 조건+점 조건+점 조건+선분 조건의 형태이므로 모든 조건에 대한 처리 범위를 줄일 수 있다.

● RGST_DT+MGMT_NUM+SVC_NUM+LINE_NUM 인덱스를 이용하는 경우
선분 조건+점 조건+점 조건+점 조건 형태이므로 앞의 선분 조건인 RGST_DT 컬럼의 경우에만 처리 범위가 줄어든다. 나머지 컬럼의 경우 체크 조건으로 동작한다.

그러므로 처리 범위를 최소화해 최적의 성능을 얻을 수 있는 경우는 MGMT_NUM+ SVC_NUM+LINE_NUM +RGST_DT 인덱스를 이용하는 것이다. 그러나 옵티마이저는 두 인덱스 중 어떤 것이든 이용 가능하므로, RGST_DT+MGMT_NUM +SVC_NUM +LINE_NUM 인덱스를 이용할 때 성능 저하가 발생하게 된다.

이처럼 비효율적인 인덱스가 존재할 경우 조회하는 SQL이 어떤 인덱스를 이용하는가에 따라 조회 성능에 차이가 발생한다. 결과적으로 인덱스의 목적은 조회 성능 향상이지만 잘못된 인덱스는 조회 성능과 DML 작업 성능을 하락시킨다. 또한 잘못된 인덱스 선정은 스토리지 용량을 낭비함을 유의하자.

출처 : 마이크로소프트웨어 1월호

제공 : 데이터전문가 지식포털 DBguide.net