작성일자 글쓴이 thkmon

인덱스(index)에 대한 메모

인덱스(index)에 대한 메모

(인덱스에 대해 잘 아시는 분은 댓글을 통해 지식을 공유해주시기 바랍니다. 모호한 부분에 대한 지적 부탁드립니다. 감사합니다.)

아직 초보 개발자라 잘은 모르겠지만 인덱스에 대해 아는 대로 메모해둔다.

인덱스는 사전의 색인이라고 번역한다. 말 그대로 뭔가를 더 빠르게 찾아주기 위한 역할을 하는데, 따라서 where 문과 order by 에 쓰이는 듯 하다.

먼저 Primary Key 는 인덱스를 주지 않아도 된다. PK는 이미 인덱스가 걸려있기 때문이다. 그러나 PK가 아니라면?

예를 들어서 user_id 라는, PK가 아닌 컬럼이 있다고 해보자. select * from table user_id = ‘admin’ 이런 식으로 가져오겠지. 이 때, 인덱스가 없다면 테이블의 모든 로우를 풀 서치 해야한다.

그런데 user_id 가 미리 정렬되어 있다면 어떨까? 첫 글자가 a인 구간만 돌고, b인 구간에 들어서면 반복문을 빠져나가면 된다. 내 생각엔 인덱스를 걸어두면, 해당 컬럼을 미리 정렬해두는 것 같다. 그리고 insert를 할 때 마다 정렬이 되면서 삽입되겠지.

그러니까 인덱스는 where 문에도 쓰이고, order by 에도 쓰이는 것 같다. 다른 곳에 또 쓰이는지는 아직 모르겠다.

어디에 또 쓰이는지는 아직 잘 모르겠다.

인덱스는 이런 식으로 만든다.

​

CREATE INDEX IDX_테이블명_인덱스명 ON 테이블명
(컬럼명)
NOLOGGING
NOPARALLEL;

목록 쿼리가 select * from table user_id = ‘admin’ 로 쓰이고 있다면 다음과 같이 만들면 되는 것 같다.

CREATE INDEX IDX_TABLE_USERID ON TABLE
(USER_ID)
NOLOGGING
NOPARALLEL;

이 때, 아래와 같이 정렬 기준을 줘도 된다.

CREATE INDEX IDX_테이블명_컬럼명 ON 테이블명
(컬럼명 DESC)
NOLOGGING
NOPARALLEL;

정렬 순서는 DESC 라고 명시하지 않으면, 기본값은 무조건 ASC로 들어가는 것 같다.

이렇게 인덱스에는 이미 정렬 순서가 있기 때문에, 해당 인덱스를 타면 ORDER BY 를 주지 않아도 기본적으로 정렬되어 출력된다고 한다. (USER_ID DESC) 로 쓴 경우에는 사용자 아이디 내림차순으로 돌겠지.

where 문에 컬럼 두 개가 들어간다면 아래와 같은 방식으로 쓴다. 

CREATE INDEX IDX_테이블명_인덱스명 ON 테이블명
(컬럼1 DESC, 컬럼2 ASC)
NOLOGGING
NOPARALLEL;

​

NOPARALLEL 말고 다른 건 없을까? PARALLEL 을 쓰면 된다. 기본값은 병렬 처리를 하지 않는 NOPARALLEL 이다. 병렬처리에 대해서는 아직 잘 모르겠다. 다만, 아래와 같은 식으로 쓰는걸 확인했다.

CREATE INDEX IDX_테이블명_인덱스명 ON 테이블명
(컬럼1 DESC, 컬럼2 ASC)
NOLOGGING

PARALLEL ( DEGREE 8 INSTANCES 1 );


이렇게 인덱스는 컬럼 1개 짜리, 2개 짜리, 3개 짜리 식으로 쓸 수 있기 때문에 (아직 4개 짜리는 보지 못했다.), 어떤 인덱스를 타냐에 따라서 효율이 달라진다. 그냥 select 를 하면 DB 옵티마이저가 인덱스를 자동으로 선택해주는 것으로 알고 있다. 이럴 때 여러 개의 인덱스 중 원하는 인덱스를 항상 타도록 만드는 문구가 힌트(Hint)다. 여러 줄 주석처럼 생겼지만(/* ~ */) 이게 힌트다.

select   /*+ index( idx_col_1 ) */

           name, age, hobby

from     member

인덱스는 사전의 색인처럼 검색과 정렬을 빠르게 해준다. 그렇기 때문에 값이 0 아니면 1이라던가, 중복된 값이 많은 컬럼에는 사용해봤자 별 소용이 없다. 밸류 하나하나가 유니크한 컬럼에 쓰면 효과가 좋다.

마지막으로 인덱스를 써도 느릴 경우는?

첫째, 전체 데이터 수가 별로 없다면 인덱스를 써서 오히려 느릴 수가 있다. 풀 스캔이 더 빠른데 괜시리 인덱스 영역을 건너갔다 오는 것이기 때문이다.

둘째, DB 마이그레이션(기존 시스템의 데이터를 새 시스템에 옮기는 일)을 여러 번 수행한 경우에 느릴 수 있다. 한 마디로 n개의 컬럼을 insert했다가, delete했다가, insert했다가, delete 했다가… 식으로 반복하면 인덱스에 가비지 값이 많이 쌓이면서 효율이 느려지는 것 같다. 이럴 때 인덱스를 리빌드(Rebuild)하면 다시 속도가 빨라진다.

2016년 11월 29일 화요일 흑곰 씀.

(초보 개발자입니다. 인덱스에 대해 잘 아시는 분은 댓글을 통해 지식을 공유해주시기 바랍니다. 모호한 부분에 대한 지적 부탁드립니다. 감사합니다.)