[Database] Index

인덱스를 쓰는 이유

• 조건을 만족하는 튜플(들)을 빠르게 조회하기 위해 -> WHERE 절, JOIN ON 절
• 빠르게 정렬(ORDER BY)하거나 그룹핑(GROUP BY)하기 위해

인덱스 유무에 따른 성능 차이

• 없을 때: FULL SCAN -> O(N)
• 있을 떄: B-tree Based Index인 경우, O(log N)

인덱스 관련 명령어

인덱스 거는 문법

• 이미 사영되고 있는 테이블에 인덱스를 추가하고 싶을 때
• CREATE (UNIQUE) INDEX [인덱스명] ON [테이블명] (attribute, ..);
• 테이블을 생성할 때 인덱스를 함께 걸고 싶을 때
• CREATE TABLE [테이블명] ( ... (UNIQUE) INDEX [인덱스명] ON [테이블명] (attribute, ..); );
• UNIQUE 키워드가 붙으면 중복을 허용하지 않는 인덱스를 만들 수 있다.
• attribute를 여러개 전달하여 복합 인덱스를 생성할 수 있다.
  • 복합 인덱스: 두 개 이상의 attribute로 구성된 인덱스
• Primary Key에는 인덱스가 자동으로 생성된다!

테이블에 걸려있는 인덱스 파악하기

SHOW INDEX FROM [테이블명];

B-tree 기반의 인덱스가 동작하는 방식

• B-tree 기반이므로 값들이 정렬된 형태로 저장된다.
• 포인터라는 데이터를 갖고 있는데, 이는 실제 테이블의 튜플을 가리킨다.
• 인덱스에서 조건 탐색 시 이진 탐색을 진행한다
• 하지만, 조건에 인덱스가 걸려있는 속성 뿐만 아니라, 인덱스가 걸려있지 않은 다른 속성까지 결합해서 찾으려고 한다면, 걸려있는 부분에 대해서는 빠르게 찾지만, 걸려있지 않은 부분에 대해서는 ptr 값을 활용해 실제 테이블로 가서 하나하나 확인해보는 작업이 필요하다. 이를 위해서는 복합 인덱스를 활용해야 한다.
  

  

• 인덱스는 attribute가 주어진 순서대로 데이터를 정렬하므로, 조건절에서 순서를 올바르게 전달하지 않을 경우 성능이 거의 나오지 않거나 오히려 안 좋을 수도 있다.
  • (a, b)에 대한 인덱스가 걸려있는데, where b = 95;와 같은 조건 탐색을 할 경우, 성능이 좋지 못하다 -> b에 대한 인덱스가 따로 필요하다.
  • 사용되는 쿼리에 맞춰서 적절하게 인덱스를 걸어줘야 쿼리가 빠르게 처리될 수 있다.

쿼리 앞에 EXPLAIN 키워드를 붙이면 실제 쿼리가 인덱스를 사용하는지 확인할 수 있다.

인덱스 선택

DBMS의 optimizer가 알아서 적절하게 사용할 index를 선택해준다.

하지만, 간혹 optimizer가 이상한 선택을 하는 경우가 있다.

→ 특정 index를 사용하도록 명시해주고 싶다.

• USE INDEX
  • 이는 권장사항 느낌이라 얘를 안 쓰면 full scan으로 동작
  • SELECT * FROM playeraUSE INDEX (backnumber_idx) WHERE backnumber = 7;
• FORCE INDEX
  • 강제적으로 이 index를 사용하라는 명령어
  • 하지만, optimizer가 판단하기에 해당 인덱스로는 원하는 데이터를 가져올 수 없다고 판단하면, full scan을 수행
  • SELECT * FROM player FORCE INDEX (backnumber_idx) WHERE backnumber = 7;
• IGNORE INDEX
  • 특정 인덱스를 제외하라는 명령어

Covering Index

• 조회하는 attribute(s)를 index가 모두 포함하는 경우, 실제 테이블에 가서 데이터를 가져올 필요없이 인덱스만으로도 쿼리를 커버할 수 있다
• 조회 성능이 더 빠르다

Hash Index

• hash table을 사용하여 index를 구현
• 시간복잡도 O(1)의 성능
• 단점
  • rehashing에 대한 부담
    • rehashing: hash table로 구현이 되어 있다보니 array로 구현이 되어 있음. 데이터가 추가되다보면 어느 순간 array size를 늘려주어야 하는데 이를 rehashing이라고 함.
  • equality 비교만 가능, range 비교 불가능
  • multicolumn index의 경우 전체 attributes에 대한 조회만 가능
  • …

MySQL의 경우 어떤 인덱스를 사용할지 선택할 수 있다

Full Scan이 더 좋은 경우..?

다음과 같은 상황에서는 인덱스를 사용하는 것보다 Full Scan이 더 성능이 좋을 수 있다

• table에 데이터가 조금 있을 때 (몇 십, 몇 백건 정도)
• 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
  • SK를 사용하는 고객을 모두 조회하려고 하는데, 그 수가 상당히 많을 것으로 예상되는 경우
  • 하지만 이때도, optimizer가 알아서 적절히 선택해줌.

그 외

• order by나 group by에도 index가 사용될 수 있다
• foriegn key에는 index가 자동으로 생성되지 않을 수 있다(Join 관련)
  • mysql은 자동 생성되지만 다른 DBMS는 다를 수도
• 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있다.
  • 트래픽이 적은 시각에 이 작업을 수행하자