[DB] 인덱스, unique index, multicolumn index, hash index

 

 

 

 

 

 

 

 

where name = '홍길동'

name 이라는 컬럼에 인덱스 없으면 100만개 full scan – 시간복잡도 O(N)

인덱스가 걸려있고 B-tree 기반이라면 O(logN)

 

일반 인덱스 : 중복 가능

유니크 인덱스 : 중복 불가능

여러개를 한번에 묶어서 인덱스 생성 가능 = multicolumn index

 

테이블을 생성하면서 만들 수도 있고

테이블 생성 후에 만들 수도 있음

 

primary key에는 RDBMS가 자동으로 인덱스를 생성해준다.

 

 

 

 

 

where a = 7

    and b = 95;

위의 경우 a에 대해서 인덱스를 생성하면 인덱스내에서 a = 7인 것을 찾고 또 a = 7인 것들을 full scan해서 b = 95 인 것을 찾아야 하기 때문에 비효율적이다.

그래서 a, b를 하나로 묶은 인덱스를 만들면 된다.

 

여러 컬럼으로 인덱스를 만들 경우 a 컬럼으로 먼저 정렬이 되고 동일한 a에 대해서 다시 b가 정렬이 된다. 때문에 순서도 중요하다.

 

index(a, b)를 만들었는데 where b = 10; 을 한다면 성능이 나오지 않는다. a를 기준으로 정렬되어 있는 인덱스이기 때문이다. 그래서 b에 대해 full scan 하는 것과 큰 차이가 없다.

 

where a = 5

       or b = 20;

이면 a = 5 에 대해서 index(a, b)를 써서 빠르게 찾을 수 있지만

or 이기 때문에 b = 20 에 대해서는 full scan이 일어난다.

그래서 사용하는 쿼리에 따라 적절하게 index를 걸어줘야 쿼리 성능이 잘 나온다.

 

특정 인덱스를 사용하라고 할 수도 있고

특정 인덱스를 제외할 수도 있다.

 

 

 

 

그럼 인덱스를 최대한 많이 만드는 것이 좋을까?

 

1. 원본 데이터에서 값이 변경(insert, update, delete)가 생긴다면 관련 컬럼을 가지고 있는 인덱스도 모두 변경해주어야 하는 것이다. 또한 B-tree 구조를 재정렬할 때에도 소요가 발생한다.

2. 인덱스마다 추가적인 저장공간을 차지한다.

 

그래서 불필요한 인덱스는 만들지 않아야 한다.

 

 

 

 

 

select a, b

  from player

where a = 10

    and b = 20;

일 때 select 하는 컬럼이 인덱스에 포함되어 있기 때문에 조건을 만족하는 값을 인덱스에서 찾은 후 다시 player 테이블에서 가져오는 것이 아니라 바로 인덱스에서 가져오기 때문에 더 빠르다. 이런 인덱스를 covering index 라고 한다. 의도적으로 이렇게 하기도 한다.

 

 

 

 

 

인덱스 방식은 앞서 말한 B-tree index가 있고 hash index라는 것도 있다.

hash table 사용

시간복잡도 O(1)

데이터가 추가되서 hash table을 늘려주는 rehashing을 할 때 부담이 생긴다.

equality 비교만 가능(=, !=)

range 비교 불가능(<. > 등)

hash index로 multicolumn index인 index(a, b)를 만들면 a, b 세트에 대해서만 인덱스를 사용한다. B-tree index에서 a 하나에 대해서 index(a, b)를 사용할 수 있었던 것과 대비된다.

 

그래서 B-tree index를 많이 사용한다.

 

 

 

 

 

order by나 group by에도 인덱스가 사용될 수 있다.

 

foreign key에는 인덱스가 자동으로 생성되지 않을 수 있다.

 

이미 데이터가 몇백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 심하면 10분 이상 걸릴 수도 있다. 그래서 인덱스가 생성되는 시간 동안 CUD 성능이 안 좋아질 수 있다.

 

 

 

 

 

참고

 

https://www.youtube.com/watch?v=IMDH4iAQ6zM&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=25&pp=iAQB