[SQL] 인덱스를 사용하여 쿼리 성능 향상시키기

인덱스 

 인덱스는 테이블의 데이터를 보다 빠르게 검색할 수 있는 객체로써, 쿼리의 처리 속도를 향상하기 위해서 사용됩니다.

기존의 SELECT문은 테이블의 모든 자료를 검색했지만 인덱스를 사용하면 특정 데이터만을 검색하여 처리속도가 빨라지고 시스템 전체 성능을 향상하는 효과를 만들 수 있습니다. 마치 우리가 책에서 특정 내용을 찾을 때 목차를 보고 빠르게 찾는 것과 같습니다.

 

 그 대신, 인덱스를 사용하려면 인덱스를 생성할 추가 공간이 필요하고, 데이터 변경 작업이 자주 일어날 경우 오히려 성능 저하가 일어날 수 있습니다.

 

인덱스의 구조 B+-trees

 인덱스는 위와 같은 B+-trees의 형태로, 데이터를 순차적으로 정렬한 다음 where 조건의 범위 내에 있는 데이터들을 빠르게 검색합니다. 이를 인덱스 범위 스캔(Index range scan)이라고 합니다. 마치 이분 탐색과 같은 탐색 알고리즘 사용해 처리 속도를 늘리는 원리입니다.  

 

인덱스 생성하기

기본 사용법

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

어떤 테이블, 칼럼에 인덱스를 설정할 것인지를 결정합니다.

 

예시

CREATE INDEX idx_column1
          ON t_table (column1);

 

인덱스를 사용해야 하는 경우

 인덱스를 사용하는 것에는 장점과 단점이 있기 때문에 아래와 같이 여러 가지 상황에 맞혀 인덱스를 사용해야 합니다.

인덱스를 사용해야 하는 경우	인덱스를 사용하지 말아야 하는 경우
테이블에 행의 수가 많을 때	테이블에 행의 수가 적을 때
where문에 해당 컬럼이 많이 사용될 때	whrere 문에 해당 컬럼이 자주 사용되지 않을 때
검색 결과가 전체 데이터의 2 ~ 4%일때	검색 결과가 전체 데이터의 10% ~ 15% 이상 일 때
join에 자주 사용되는 컬럼이나 null을 포함하는 컬럼이 많은 겨우	테이블에 select, insert, update, delete 작업이 많은 경우

 

인덱스 사용하기

 인덱스를 적용하기 위해서는 where 조건에 <, >, = 와 같은 범위 조건을 사용되도록 바꾸어 주어야 합니다. 이렇게 바꾸어 주어 성능을 향상하는 것을 과정을 쿼리 튜닝이라고 합니다. 

튜닝 전

SELECT name, email_addr FROM student WHERE email_addr IS NULL;

is null, != 같은 연산자를 사용하면 인덱스가 적용되지 않기 때문에 where 조건을 바꾸어 줍니다.

 

튜닝 후

SELECT name,email_addr FROM student WHERE email_addr = '';

 

인덱스의 종류

 인덱스에는 여러 가지 종류가 있습니다.

 

고유, 비고유 인덱스

CREATE UNIQUE INDEX idx_name
                 ON t_table (column1);

비고유 인덱스는 중복된 값을 허용하는 칼럼에 생성하는 인덱스이고, 고유 인덱스는 유일한 값을 갖는 칼럼에 대해 생성하는 인덱스입니다. 위와 같이 UNIQUE 키워드를 붙여줍니다. 

 

결합 인덱스

CREATE INDEX idx
          ON t_table (column1, column2);

결합 인덱스는 두 개 이상의 칼럼에 생성하는 인덱스입니다.

 

함수 기반 인덱스

CREATE INDEX idx
          ON t_table(column * 10);