Oracle - Index 및 쿼리 성능 평가

 사용자의 입장에서 웹에 접속할 때 가장 짜증나는 부분이 뭐가 있을까요? 제가 약 20년간 인터넷을 하면서 가장 짜증났던 때가 두 가지 있는데, 첫 번째는 끌도 없이 깔라고 하는 Active X와 다른 하나는 답답하고 느려터진 로딩이었습니다. 프로그래머로 정말 기똥찬 로직을 짜서 서비스 하더라도 사용자는 아 이런 것도 되네? 라고 생각하지 이게 진짜 기똥찬지 아닌지는 모르는 법이죠. 막상 잘 짜여진 서비스를 사용하는 것보단 눈 앞에서 깔라고 하는 Active X와 로딩 속도가 사용자의 주 평가라고 할 수 있겠습니다. 여기서 이상한 에러코드가 터지면서 브라우져가 꺼지면 사용자는 정말 속 터지는 상태가 된다고 할 수있죠. 
 일단, Active X는 뭐 HTML5에 들어서면서 없어지는 추세이니 차치하더라도 속도를 개선하기 위한 방안으론 어떤 것이 있을까요? Java와 Javascript에서 짜는 서비스 로직 상에서 속도를 개선하는 방법이라면 최대한 코딩수를 줄여서 덜 인코딩하도록 하는 방법이겠죠? 하지만, 근본적인 대책은 되지 않습니다. 왜냐하면 Java와 Javascript를 개선하더라도 속도에 영향을 주는 부분이 미미하기 때문입니다. 그러면 어떤 점을 개선해야 속도가 향상될까요? 바로 데이터베이스의 성능을 개선하는 것입니다.

  이를 위해서 필요한 것이 Index입니다. 정보처리기사를 공부하셨고 취득하셨으면 들어본 적이 있으실 겁니다. Index는 영어 단어로 색인을 의미하는데, 색인이란 것은 어떤 자료를 뒤져서 찾아내는 것을 의미합니다. 보통 오라클에서는 SELECT 문을 통해서 테이블에서 자료를 조회할 때 Full Scan을 타도록 되어 있습니다. Full Scan이란 것은 테이블 내에 있는 모든 자료를 조회하는 방식이죠. 그런데, 여기서 데이터가 점점 많아지면 많아질수록 Full Scan은 성능이 떨어지도록 되어있습니다. 만약에 10만건의 자료가 있는데 그걸 처음부터 끝까지 읽어서 자료 하나만 찾아온다면 그것만큼 비효율적인 방법도 없을 겁니다. 이런 점을 개선하기 위해서 나온 것이 Index입니다. Index를 테이블에 만들어주면 검색 속도를 향상시키고 사용하는 자원을 적게 소모하게 됩니다. 그럼 Index를 어떤 식으로 만들어야 하는지에 대해 알아 보겠습니다.

Index를 만들 때 테이블에 대해서 고려해야 할 점은 다음과 같습니다.

 1. 테이블에 자료가 많은가?
 2. 테이블에 Null값이 많은가?
 3. Where절이나 Join절에서 자주 사용되는 컬럼인가?
 4. 테이블이 자주 변형되지 않는 테이블인가?

 이를 반대로 말하면 테이블의 자료가 적고 컬럼수가 적으면서, Null값이 별로 없는데 구조가 자주 변형된다면 Index를 만들 필요가 없다는 점이죠. 테이블에 자료가 적어도 10000건 이상되지 않는다면 성능 상에 영향을 주는 것이 미미하기에 필요가 없고, Null값이 허용되는 테이블이여야 Index를 통해서 자료를 찾기 쉬우며, SELECT문에만 사용되기에 Where절이나 Join절에 자주 사용되는 컬럼이여야 합니다. 또한, 테이블이 자주 변형된다면 그 때마다 Index를 고쳐야 하기 때문에 구조가 자주 변형되지 않는 것에 유리합니다. 그럼 먼저 쿼리부터 보도록 하겠습니다.
 

SELECT * FROM ZIPCODE WHERE SIDO = '서울';

  이번에 사용한 테이블은 쌍용교육센터때 받은 ZIPCODE라는 주소가 저장 된 테이블입니다. 약 48000건의 데이터가 저장되어 있습니다. 토드에서 Crtl + E를 눌러보면

 

 

 

 총 136의 자원을 소모하고 Full Scan으로 자료를 찾고 있음을 볼 수 있습니다. 그리고 실제로 쿼리를 실행해보면 약 16초의 시간이 걸립니다. 16초나 시간이 걸리면 사용자 입장에서 속 터지고 해탈하는 시간이죠. 그럼 Index를 생성해서 검색해보도록 하겠습니다.

CREATE INDEX ZIPCODE_INDEX01 ON ZIPCODE (SIDO);

 

 Index를 만드는 법은 위에 쿼리를 참조해보시면 알겠지만 아래와 같습니다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);

 Index를 생성하고 나서 다시 토드에서 Ctrl + E를 눌러보면

 

 

 

 테이블이 Index를 타고 있는 것을 확인할 수 있으며 훨씬 적은 자원을 소모하고 있음을 알 수 있습니다. 40~50정도의 Cost가 줄어 들었으며 쿼리를 실제 실행해보면 약 24 밀리초가 걸립니다. 24 밀리초면 사용자한테는 충분히 마음의 안정을 줄 수 있는 시간이죠. 그러므로 데이터가 많고 Where절이나 Join문에서 자주 사용되는 컬럼을 가진 테이블이 있다면 꼭 Index를 생성해서 성능 개선을 해줘야 함을 알 수 있습니다.
 
 이런 Index에도 다양한 종류가 있으며 종류는 아래와 같습니다.

 1. 유일성 여부
 - Unique Index(CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (UNIQUE 컬럼))
 - Non-unique Index(unique인덱스가 아닌 나머지 인덱스)

2. 구성 컬럼 개수
 - 단일 컬럼 Index
 - 다중 컬럼 Index(CREATE INDEX 인덱스명 ON 테이블명 (컬럼1, 컬럼2... 컬럼n))

3. 인덱스 생성에 따라
 - 사용자 생성 수동 Index
 - 시스템 생성 Index(오라클에서 자동으로 만들어주는 Index로 SYS_로 시작한다.)

  이렇게 종류 별로 있으니 때에 따라 적절하게 Index를 생성하면 되겠습니다. 따로 테스트하고 싶은 분이 있다면 연락 주시면 ZIPCODE 데이터가 있는 TEXT파일을 보내드리도록 하겠습니다.