DB Index

정의

RDB에서 조회 속도 개선을 위해서 사용하는 방법입니다.

자주 조회되는 column에 대한 index table을 따로 만들어서 데이터 조회 시 실제 데이터의 위치를 들고 있는 index table을 먼저 조회하게 함으로써 탐색 속도를 높이는 방법입니다.

 

자료 구조

특징

일반 트리는 최대 2개의 자식노드를 가질 수 있지만, b-tree는 n개의 자식 노드를 가질 수 있습니다.

일반 트리의 경우 좌우 자식간의 균형이 맞지 않으면, 매우 비효율적인데, b-tree는 이렇게 되지 않도록 균형을 유지하는 tree입니다.

사용 이유

B+tree는 리프 노드에 이르기 까지에 대한 자식 노드에 포인터가 저장되어 있다. 즉, B+트리의 검색은 루트 노드에서 어떤 리프 노드에 이르는 한 개의 경로만 검색하면 되므로 매우 효율적이다.

Q: hash table이 더 좋지 않나요?

A: 단순히 동등 조건 비교만 사용하면 hash table이 더 빠르겠지만, 부등호 연산도 검색 조건으로 사용하기 때문에 b-tree 연산이 더 효율적입니다.

 

주의할 점

Q: 인덱스를 많이 만들면 좋은가요?

A: 인덱스를 많이 만들수록 옵티마이저가 적절한 인덱스를 선택하기 위한 결정 시간이 늘어날 수 있고, 인덱스를 관리하는 테이블의 수도 많아지기 때문에 불리합니다.

 

Q: 인덱스를 적용하면 무조건 검색 속도가 빨라지나요?

A: 데이터의 갯수에 따라서 달라집니다. 인덱스를 통해 레코드를 읽는 것이 full-scan해서 읽는 것 보다 4 ~ 5배 정도 비용이 더 들기 때문에, 읽어야 하는 레코드의 수가 전체 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않는 것이 더 효율적입니다.

 

Q: 인덱스를 만들 때, 단점은?

A: 조회 성능은 좋을 수 있지만, 쓰기나 삭제 연산의 경우 불리합니다.

인덱스는 정렬된 상태를 유지해야하므로 새로운 row가 추가될 때마다 정렬하여 추가해줘야하고,

수정 및 삭제할 때는 테이블에서만 삭제 되고 인덱스 테이블에서는 남아있어서 쿼리 수행 속도가 저하됩니다.

 

고려 사항

1. 테이블의 레코드의 갯수가 충분히 많은지 고려해야 합니다.
2. insert, update, delete가 자주 발생하지 않는 칼럼에 적용하는 것이 좋고, join이나 where 절에 사용되는 컬럼에 적용되는 것이 좋습니다.
3. 카니널리티가 높은 값을 위주로 걸어야 하고, 다중 컬럼을 인덱싱할 때는 카디널리티가 높은 순에서 낮은 순으로 인덱싱 해야 효율적입니다.
4. 실행 계획을 보고, 실제로 인덱스를 타는 지 확인해봐야 합니다.(type을 보면 알 수 있다.)

 

내가 만든 쿼리가 과연 인덱스를 타는지 체크해보자.

CREATE TABLE heehee_Member(                               
  id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(20) NOT NULL,
  ouccupation VARCHAR(20) NULL,
  height SMALLINT,
  gender bit,
  date  DATETIME,
  CONSTRAINT heehee_Member_PK PRIMARY KEY(id)
);

desc heehee_Member;

show index from heehee_Member;

-- height에 index를 검
create index height_idx on heehee_Member(height);

-- type이 null, possible keys가 null 이면 안 탐
explain select * from heehee_Member;
explain select * from heehee_Member where height = 180;
explain select * from heehee_Member where height = 180 and gender = 1;

쿼리 실행계획을 따서 type과 possible_keys 칼럼을 확인해보면 된다.

인덱스를 안 타는 경우

type을 보면 ALL로 표시되어있다. Full Scan한다는 뜻

 

인덱스를 타는 경우

type과 possible_keys를 보면 이전과는 달라진 것을 확인할 수 있다.

 

Reference

https://helloinyong.tistory.com/296

데이터베이스 인덱스는 왜 'B-Tree'를 선택하였는가

https://mangkyu.tistory.com/286

[MySQL] B-Tree로 인덱스(Index)에 대해 쉽고 완벽하게 이해하기

https://mangkyu.tistory.com/96

[Database] 인덱스(index)란?

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁