RDBMS Index

프로젝트 진행 중 데이터를 가져오는 소비 시간이 2초를 넘어 가는 데이터에 대해 고민 하던 중
Index를 써서 빠르게 데이터를 가져 왔었다. 그래서 오늘은 인덱스가 뭐고 인덱싱이 무슨 역할 하는지에 대해
정리 하고자 한다.

Index

Index ?

: RDBMS에서 검색 속도를 높이기 위해서 사용되는 기술

: Table의 컬럼을 따로 파일로 저장하여서, 검색시 해당 Table의 레코드를 full scan하는 것이 아니라, 저장한 Index 파일을 검색하여서 검색 속도를 빠르게한다.

: Array의 index를 알 때, O(1) 의 시간복잡도로 접근할 수 있는 개념과 비슷하다.

• 일반적으로 테이블에 Index를 작성하면 테이블 데이터와 별개로 Index용 데이터가 저장장치에 만들어진다. 이때 이진트리 데이터 구조로 작성될 수 있다.
• Index 작성 할 때, SQL Server나 MySQL에서 Index는 테이블 내의 객체가 되고, 테이블 내에 이름이 중복되지 않도록 지정해서 관리해야한다.
• 흔히 영단어 사전을 보면 목록을 나누는 것이라고 생각하면 된다.

 

Index 작성과 삭제

1. Inde 작성CREATE INDEX 컬럼명 ON 테이블명( 컬럼명 );
   • Where 구에 a열에 대한 조건식을 지정한 경우 SELECT 명령은 Index를 사용해서 빠르게 검색할 수 있다.
   • 그러나, WHERE 구의 조건식에 a열이 전혀 사용되지 않으면 SELECT 명령은 isample62 Index를 사용할 수 없다.
2. SELECT * FROM sample62 WHERE a='a'; //a열 index로 지정했기 때문에, 내부적으로 isample62 Index가 사용

 

최적화

1. SELECT 명령을 실행할 때, Index의 사용 여부를 선택한다.
2. 이는 데이터베이스 내부의 최적화에 의해 처리되는데, 내부 처리에서는 SELECT 명령을 실행하기 앞서 실행계획을 세운다.
3. 실행계획에서 Index의 유무, Index를 사용할 것인지 여부에 대해서 데이터베이스 내부의 최적화 처리를 통해 판단한다.
4. 데이터의 종류가 적을 수록 Index의 효율이 떨어진다.
5. 반대로 서로 다른 값으로 여러 종류의 데이터가 존재하면 효율은 좋아진다.
6. 이렇게 index가 db에 제대로 반영 되는지 확인하기 위해서는 EXPLAIN 문을 이용해서 알 수 있다.
   • SELECT 문 앞에 'EXPLAIN'을 붙여주고, possible_keys와 key 컬럼을 통해 알 수 있다.

 

Django 에서 Index 예제

---

class Blog(models.Model):
   title = models.CharField(max_length=100)
   created_at = models.DateTimeField(auto_now_add=True)
   contents = models.TextField()

class Blog(models.Model):
   title = models.CharField(db_index=True, max_length=100) #title
   created_at = models.DateTimeField(auto_now_add=True)
   contents = models.TextField()

• Django에서는 모델을 정의할 때, Meta Class 를 통해서 정의하는 방법, 위와 같이 각 컬럼에 직접 지정해주는 방법이 있다.
• pk는 데이터베이스에 의해서 Index로 일반적으로 지정되기 때문에, pk를 통한 탐색은 Index를 이용하기 떄문에 효율적 일 수 있다.
• 그러나, 위와같이 그외 컬럼들을 탐색할 때는 데이터가 많이 쌓이면 쌓일 수록, full scan의 비용이 증가하기 때문에, 위와같이 index를 지정해준다.
• django 문서 상에서는 filter, exclude, order_by가 자주 사용되는 컬럼에 db_index를 사용하라고 나와있다.
• Django 에서는 Queryset에 explain method가 존재하는데, 이것을 통해 index 사용 여부를 알 수있다.
• test =Post.objects.filter(user_id=1)
  print(test.explain)
  ​
  #결과 값
  '0 0 0 SEARCH TABLE posts_post USING INDEX posts_post_user_id_id_8907527a (user_id_id=?)'

 

필자는 약 140만개 데이터를 가져 올때 2초 이상가 걸렸었다. 인덱싱 없이..

full_scan 를 통해 상당히 늦어 졌고, 이를 인덱싱으로 추가하여 0.3~0.4초로 가져 올수 있었다.