mysql Index 종류 및 B-Tree 구조

인덱스란?

인덱스란 지정한 칼럼들을 기준으로 메모리 영역에 일종의 목차를 생성해 읽기 속도를 높여준다. insert, update, delete의 성능을 희생하고 대신 select의 성능을 향상시키게 된다.

인덱스 종류

책으로 비유하자면 클러스터형 인덱스는 영어사전이며, 보조 인덱스는 맨 뒷장에 찾아보기가 존재하는 책이다.

- 클러스터형 인덱스 (clustered index)

• 테이블당 1개만 존재
• 테이블은 이 클러스터형 인덱스로 자동정렬이 된다. 즉 특정 컬럼에 pk를 생성하는 순간 해당 칼럼에 클러스터형 인덱스가 자동으로 생성된다.
• 생성과 동시에 오름차순으로 정렬된다.
• 리프 페이지가 곧 데이터 페이지 (실제 데이터가 저장된 위치)

- 보조 인덱스(secondary index)

• 한 테이블에 여러개 설정 가능
• 클러스터링 인덱스와 달리 실제 데이터를 정렬하지 않고, 별도의 장소에 인덱스 페이지를 생성한다.
• 별도로 생성된 인덱스 페이지는 인덱스가 걸린 컬럼 값에 따라 정렬되어 별도의 페이지를 생성한다.
• 즉 원하는 정보를 찾기 위해 보조 인덱스를 찾고, 해당 정보 옆에 표시된 페이지로 다시 이동해 원하는 정보를 찾는다.

 

 

인덱스의 자료구조 및 동작원리

mysql index는 B-Tree(Balanced Tree)구조를 따른다.

- B-Tree (Balanced Tree)

먼저 B-Tree 자료구조는 자식 2개만을 갖는 이진트리(Binary Tree)를 확장해 N개의 자식을 가질 수 있도록 고안된 자료구조이다. 좌우 자식간의 균형을 맞춘다는 의미로 균형트리라고도 한다.

 

B-Tree 는 최상위에 하나의 루트 노드(루트 페이지)가 존재하고, 그 하위에 자식 노드가 붙어있는 형태이다. 트리 구조의 가장 하위를 리프 노드(리프 페이지)라고 하고 , 트리구조에서 루트 노드도 아니고 리프노드도 아닌 중간의 노드를 브랜치 노드라고한다.

mysql은 노드를 페이지라고 부른다. 인덱스는 페이지 단위로 저장되며, 항상 인덱스 키를 바탕으로 정렬된 상태를 유지한다. 페이지 사이즈는 16KB이다.

 

인덱스 탐색은 Root → Branch → Leaf → 디스크 저장소 순으로 진행된다.

따라서 select를 하면 특정 키 값을 찾기 위해 자식 페이지를 계속 타고 들어가 마지막 리프 노드에는 데이터의 레코드 주소를 찾아 하고 이 값으로 테이블 레코드를 찾을 수 있다.

 

 

 

 

하지만 인덱스를 구성하면 데이터 변경 작업 (Insert, Update, Delete)시 성능이 나빠질 수 있는데 이는 페이지 분할 작업이 발생하기 때문이다. 페이지 분할이란, 데이터를 페이지 단위로 저장하는 기본 구조에서 추가적인 페이지가 필요할때 새로운 페이지를 준비해 데이터를 나누는 작업을 말한다. 따라서 insert,update, delete 등의 데이터 작업이 빈번한 테이블에는 성능에 영향을 끼치므로 인덱스 사용을 잘 고려해야한다.

 

인덱싱 적용 구성 및 기준

• 데이터 변경작업이 자주 일어나는지 고려해야 한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다. 테이블다아 4~5개 권장
• 데이터가 많은 테이블에 사용해야한다.
• 사용하지 않는 인덱스는 제거하는 것이 좋다.

 

 

출처

https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index