[기술면접] DB 문답 | index 상세

💡 index를 어느 column에 사용하는 것이 좋을까요?

 

INDEX는 어떤 컬럼에 설정해야할까?

인덱스는 where 절에서 자주 조회하고 수정 빈도가 낮으며 데이터 중복이 적은 컬럼을 선택하는 것이 좋다.

join 조건으로 자주 사용되는 컬럼도 인덱스로 사용하면 좋다.

 

index는 데이터의 양이 많을수록 성능이 향상된다. 단, 한 테이블에 인덱스가 너무 많으면 데이터 수정 시 소요시간이 커지므로 주의.

또한 성별처럼 데이터 중복이 높은 값은 인덱스를 생성하는 것이 크게 의미가 없다.

왜냐하면 인덱스의 이점보다 추가 저장공간이나 데이터 수정에 대한 성능 저하가 더 크기 때문이다.

 

 

'클러스터형 인덱스'와 '보조 인덱스'

• Clustering index :
  특정 컬럼을 기본키로 지정하면 자동으로 인덱스가 생성되고 테이블 자체가 정렬된 인덱스가 됩니다.
  (컬럼을 기본키로 지정하면 테이블이 기본키를 정렬함)
  
  
• Secondary index :
  별도의 공간에 인덱스를 생성합니다.
  (create index 혹은 고유키를 지정하면 생성)

 

 

 

 

 

💡 데이터를 검색을 할 때 hash table의 시간복잡도는 O(1)이고 b+tree는 O(logn)으로 더 느린데 왜 index는 hash table이 아니라 b+tree로 구현되나요?

 

왜냐하면 hash table은 해시 충돌 우려가 있고, key-value 컬럼:메모리 주소값을 참조하기 위한 추가 메모리 공간이 필요하여 이에 따라 공간 복잡도가 늘어나기 때문입니다.

 

또한 hash table은 = 연산, 검색에 특화되어 시간복잡도가 O(1)로 굉장히 빠르지만 >,< 부등호 연산에는 해시테이블의 특성때문에 훨씬 많은 시간이 걸리게됩니다.

 

b+tree는데이터를 node에 직접 저장하며, 해당 노드는 배열로 구현(linked list와 같은 구조)되어있어 추가 공간이 필요하지 않아 공간 복잡도면에서 유리합니다. 그리고 검색, 생성, 수정 삭제 모두 O(nlogn)으로 같은 시간복잡도를 보여 효율적입니다.

 

 

예시 : 해시테이블
user_name = diane O(1)

user_name < diane O(???)

{diane : 0x1235kxzxxlxa21, john : 0x1235kxzxxldj21, sally : 0x1235kxzxxldj22, takgeun: 0x1215kxddzxxj21}

<- 순서가 없어 부등호연산에 대한 

user

[user_name | password | email]

john aoijeo;ajfa. john@email.com

sally. werawdfa. sally@email.com

diane.  adfadfad  diane@email.com

tan     adafadfae  tan123@email.com

 

 

 

 

 

💡 복합 인덱스란 무엇인지 원리를 설명해주세요

 

복합 인덱스란 인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것이다.

 

주로 SQL 문장에서 WHERE절의 조건 컬럼이 2개 이상 AND로 연결되어 함께 사용되는 경우에 많이 사용하게 된다.

하나의 컬럼으로 인덱스를 만들었을 때 보다 탐색할 데이터 수가 줄어들고, AND조건이 추가되면 성능에 중요한 역할을 한다

 

CREATE INDEX idx_name_age ON user (name, age);

 

복합 인덱스를 사용하는 쿼리는 인덱스의 첫 번째 컬럼부터 순차적으로 적용되며, 해당 컬럼에 대한 정렬을 수행한다.

컬럼의 순서는 카디널리티(cardinality 데이터의 중복되지 않는 정도)가 높은 것부터 낮은 순으로 나열하는 것이 좋다.

 

 

참고: https://bskyvision.com/entry/카디널리티와-인덱스-컬럼

 

 

 

 

💡 모든 요소에 인덱스를 걸지 않는 이유는 무엇일까요?

 

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다.

 

 

모든 테이블에 index를 생성할 경우 2가지 단점이 있습니다.

 

 첫번째로, DB 리소스와 메모리 자원을 낭비하게 됩니다.

모든 요소에 인덱스를 걸 경우 DB에서의 추가 쓰기 작업과 추가 메모리 공간이 필요하게 되고, 사용하지 않는 인덱스에 의한 메모리 점유도 일어나게 됩니다.

 

두번째로, 인덱스의 단점인 정렬상태를 유지해야하는 점에서 DB성능을 저하시킵니다.

데이터의 생성, 변경작업이 일어날 때 INDEX값을 재정렬해야하고, 원본테이블과 index테이블 두 군데를 수정하고 제정렬해야하므로 DB에 악영향을 미치게 됩니다.

 

+ 참고: 트랜잭션과 인덱스 https://cat-minzzi.tistory.com/89