[DB] Index, 인덱스 사용하는 이유

 

 

만약 조건을 줘서 어떤 값을 찾아야 될때 테이블에 데이터가 100만개가 있다면,,? 

full scan으로 찾아야 된다. 이럼 시간 복잡도가 O(N)이 된다. 하지만 만약 first_name에 인덱스가 걸려있다면 시간 복잡도는 O(logN) 이 된다. (b-tree 기준)

 

📑 인덱스란 ? 

 

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다. 만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.

데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.

 

 

index를 쓰는 이유는 조건을 만족하는 튜플을 빠르게 조회하기 위해, 빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해서이다. 

 

 

📑 인덱스 활용해보기

 

 

 

 

1) 이미 생성 되어 있는 테이블에 인덱스를 생성할 때 다음과 같이 한다.

 

🎈 create index player_name_idx on player(name); 
 --  player 테이블의 name 속성 값을 player_name_idx라는 이름의 인덱스로 생성하겠다.

🎈 create unique index team_id_backnumber_idx on player (team_id, backnumber); 
-- player 테이블에 team_id와 , backnumber라는 속성으로 하나의 인덱스 유니크한, 중복이 될 수 없는 인덱스를 만들겠다. 그 인덱스 이름이 바로 team_id_backnumber_idx

 

 

2) 테이블을 생성하면서 같이 인덱스를 걸어주고 싶다면 다음과 같이 한다.

 

create table player(
  id int primary key,
  name varchar(20) not null,
  team_id int,
  backnumber int,
  index player_name_idx(name),
  unique index team_id_backnumber_idx (team_id, backnumber)
);

 

위와 같이 2개의 속성이 있는 경우 multicolumn index, composite index 라고 한다. 그리고 보통 pk를 설정하면 index가 자동 생성됨. 

 

 

 

3) 인덱스 정보 보고싶을 때

show index from player;

 

 

 

 

📑 정렬된 인덱스 살펴 보기 - 싱글 튜플

 

 

ptr은 원래 테이블의 어느 튜플인지 알려주는 정보값이 담겨있다. 

 

🎈 그렇다면 만약 where a=9;인 값을 index(a)에서 찾아보려고 한다. 그렇다면 어떻게 찾을 수 있을까? binary-tree 방식을 찾는다 만약 9를 찾으려고 할때 만약 임의로 5라는 값을 선택했다 그럼 9가 5보다 크기 때문에 5보다 작은 값들은 다 삭제 되는 방식이다. 

 

🎈 2번째로 where =7 and b=95;  라는 조건이 있을 때 일단 a=7을 기준으로 7에 해당하는 값을 찾고 index에는 b가 없으니 members에서 a가 7인 b의 값들을 비교해서 찾아온다. 

 

 

 

 

📑  정렬된 인덱스 살펴 보기 - 멀티 튜플

 

 

🎈 인덱스에 기준이 되는 튜플이 2개 이상일 경우 순서가 중요한데, 먼저 적은 튜플을 기준으로 정렬이 된다. 여기선 a먼저 정렬된 후 b의 값이 정렬되는 걸 확인할 수 있다. 이것도 ( and 일경우에만 같은 인덱스로 2개의 값을 조회할 수 있고 or은 첫번째 값은 멀티 인덱스로 조회할 수 있지만 2번째 값은 성능이 나지 않는다 따라서 2번째 값은 full scan으로 하거나 따로 인덱스를 만드는 게 좋다) 

 

🎈 만약 where b=95인 값을 찾으려고 하면 index(a,b)는 좋지 않은 인덱스다. 성능이 나오지 않는다. 따라서 그냥 full scan으로 찾거나 따로 index(b)를 만들어서 다시 인덱싱 해야한다. 

 

 

 

 

 

출처 : 쉬운코드님 유튜브 

https://www.youtube.com/watch?v=IMDH4iAQ6zM