[SQL] INDEX란

SQL을 공부하다 보면 INDEX를 들어봤을 것이다.

단순히 "검색을 빠르게 해주는 것" 이라고만 이해하고 있었는데, 이번에 포스팅을 하면서 좀 더 자세히 알아보고자 한다.

 

1. Index란

예를 들어, 1000장의 사전에서 내가 원하는 단어를 찾아보고 싶을 때 1000장을 모두 훑어보는 것이 아니라 '찾아보기' 라는 기능을 통해 해당 단어를 찾는 것이 더 빠를 것이다.

이럴 경우 데이터의 양이 많은 테이블에서 일부분의 데이터를 조회하고 싶을 때 모두 훑어보게 된다면 성능이 현저하게 떨어질 것이다.

 

데이터를 조회할 때 테이블에 데이터의 양이 적다면 빠른 시간 내로 조회가 가능하지만, 데이터가 많아질수록 시간이 오래 소요된다.

Index는 이런 경우에서 결과 도출 시간을 대폭 줄일 수 있다.

즉, Index는 RDBMS에서 검색 속도를 높이기 위한 기술인 것이다.

테이블을 조회할 때 해당 테이블의 값들을 Full Scan(or Table Scan)하는 것이 아니라, 따로 저장되어 있는 Index를 검색하여 조회 속도를 향상시킬 수 있다.

 

2. Index의 작동 원리

SELECT *
FROM USER
WHERE USER_ID = 9876;

 

데이터 파일의 블록이 10,000개라고 가정할 때 위의 쿼리를 실행하면,

Index가 없는 경우에는 USER_ID가 9876인 정보가 어느 블록에 해당하는지 알 수 없으므로 10,000개의 데이터를 DB BUFFER CACHE로 복사한 후 Full Scan하여 검색한다.

 

Index가 있는 경우에는 Where절의 컬럼(USER_ID)이 Index가 생성되었는지 확인한 후, Index에서 USER_ID가 9876인 정보가 어떤 ROWID를 가지고 있는지 확인하고 해당 ROWID에 있는 블록만 찾아 DB BUFFER CACHE로 복사하게 된다.

 

DB BUFFER CACHE란 데이터를 읽고 수정하기 위해 디스크에 존재하는 데이터를 읽어 저장하는 공간이다.

 

3. Index의 장점

1) 테이블에서 검색과 정렬 속도를 향상

2) 데이터의 그룹화 및 집계 속도 향상

3) 테이블 간 JOIN 성능 향상

4) 데이터 무결성 보장

 

4. Index의 단점

1) 데이터베이스에 추가적인 데이터 구조 생성하므로 디스크 공간 사용 증가

2) 데이터 변경에 따른 오버헤드

3) 인덱스 생성과 수정의 비용

 

5. 무조건 "Index"?

조회 성능을 극대화 하기 위해서는 무조건 Index를 써야 할까?

데이터베이스 서버에 문제가 발생할 때마다 Index를 생성한다면 전체적인 데이터베이스 성능 부하를 초래할 수 있다.

데이터의 삽입, 수정, 삭제 시에 Index 역시 갱신되어야 하므로 성능에 부담을 줄 수 있다는 것이다.

그렇기 때문에 무조건 Index를 생성하는 것보다 효율적인 쿼리문을 짜는 것이 좋은 방법이다.

 

6. 사용 방법

어떤 경우에 사용하는지 알게 되었으니, 직접 사용해보도록 하자.

 

CREATE TABLE indexTest (name VARCHAR(14), age int, phone_number VARCHAR(14));

 

우선 테스트 테이블을 생성한다.

 

insert into indexTest values ("김길동", 13, "010-9203-1234");
insert into indexTest values ("이길동", 23, "010-1234-1234");
insert into indexTest values ("박길동", 19, "010-5678-1234");
insert into indexTest values ("홍길동", 21, "010-3461-1234");
insert into indexTest values ("천길동", 28, "010-4532-1234");
insert into indexTest values ("송길동", 34, "010-5671-1234");
insert into indexTest values ("윤길동", 49, "010-2641-1234");
insert into indexTest values ("양길동", 31, "010-5317-1234");
insert into indexTest values ("최길동", 25, "010-7415-1234");
insert into indexTest values ("나길동", 99, "010-0900-1234");

 

이후 만든 테이블에 데이터를 넣어준다.

현업에서는 몇십만건의 데이터가 오가지만 그 데이터를 다 만들 수는 없기에 간단하게 10개만 만들었다.

 

그럼 테이블에서 name이 "나길동" 인 유저를 찾아보자.

select * from indexTest where name = "나길동";

 

위 사진을 보면 비록 10개의 데이터만 조회했지만, 모든 테이블의 내용을 조회했다.

만약 데이터가 100,000건이 넘어간다면, 검색 시간은 훨씬 많이 소요될 것이다.

 

그렇다면 이제 Index를 생성해보자.

create INDEX idx_indexTest_name on indexTest(name);

 

위와 같이 Index를 생성해주고 name이 "나길동"인 유저를 다시 조회해보자.

 

select * from indexTest where name = "나길동";

 

10 rows에서 1row로 줄어들었고, 검색 시간도 1.25 에서 0.35로 줄어든 것을 확인할 수 있다.

이처럼 Index는 검색에 필요한 속도를 현저히 상승시켜줄 수 있다는 것을 알 수 있다.

 

---

사실 빠른 검색을 위해 Index를 남발하는 경우가 많았는데, 이번에 포스팅을 하게 되면서 더욱 자세히 알게된 것 같다.

꼭 필요한 경우에만 Index를 사용하도록 하자. 

그 전에 쿼리부터 잘 짜도록 하자...