인덱스

인덱스란?

인덱스는 데이터를 빠르게 검색할 수 있게 해주는 객체 입니다.
컬럼을 오름차순 혹은 내림차순으로 정렬한 후 빠르게 찾을 수 있도록 도와줍니다.

 

 

인덱스 구조

Index는 논리적/물리적으로 테이블과 독립적입니다.

테이블은 컬럼에 데이터가 정렬되지 않고 입력된 순서대로 들어가지만, Index는 KEY 컬럼과 ROWID 컬럼 두개로 이루어져 있고 오름차순, 내림차순으로 정렬이 됩니다.

Key : 인덱스를 생성하라고 지정한 컬럼의 값

MySQL에서 테이블 생성 시, 아래와 같은 3가지 파일이 생성됩니다.

FRM : 테이블 구조 저장 파일
MYD : 실제 데이터 파일
MYI : Index 정보 파일 (Index 사용 시 생성)

• 사용자가 쿼리를 통해 Index를 사용하는 칼럼을 검색하게 되면, 이때 MYI 파일의 내용을 활용합니다.
• 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작습니다.
  (보통 인덱스는 KEY-ROWID만 가지고 있고, 테이블의 세부항목들은 갖고 있지 않기 때문)

 

장점

• 검색 속도 향상
• 시스템의 부하를 줄여, 시스템 전체 성능향상에 기여가 가능합니다.

단점

• 인덱스를 위한 추가 공간이 필요합니다.
• 생성에 시간이 소요 될 수 있습니다.
• 추가, 수정, 삭제에 대한 성능이 하락할 수 있습니다.

 

주의사항

• 무분별한 인덱스는 용량을 차지하고, 옵티마이저의 최적화를 낮추는 결과를 얻게 됩니다.
• 특정 컬림들을 조건으로 걸 때, 정렬된 인덱스의 순서와 맞지않는다면 추가적인 비용이 발생합니다.

Index 생성 가이드

항목	설명
대상 테이블	- 데이터 양이 많고 증가량이 큰 테이블
대상 컬럼	- WHERE절에서 상수 조건으로 빈번하게 사용되는 컬럼 - PK 및 FK - 테이블 조인에 사용되는 컬럼 - 선택성이 높은 컬럼은 단독적으로 생성하여 활용도를 향상시킬 수 있음 - 자주 같이 사용되는 조건 컬럼을 결함하여 결합 인덱스 생성(컬럼 순서 유의) - 가능한 수정이 빈번하지 않는 컬럼
고려사항	- 최소의 인덱스로 최대의 효과를 나타낼 수 있도록 액세스 패턴을 분석하여 생성 - 컬럼의 분포도가 10~15% 이내인 경우 B*트리 인덱스로 - 넓은 범위를 인덱스로 처리 시 부하를 가중시켜 성능을 감소시킬 수 있음 - 새로 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음

Mysql Index 종류

• Clusted Index(클러스터형 인덱스)
• Non-Clusted Index(비클러스터형 인덱스)

 

Clusted Index(클러스터형 인덱스)

• 테이블당 1개씩만 허용됩니다.
• 물리적으로 행을 재배열 합니다.
• PK설정 시 그 칼럼은 자동으로 클러스터드 인덱스가 만들어집니다.
• 인덱스 자체의 리프 페이지가 곧 데이터로 테이블 자체가 인덱스입니다. (따로 인덱스 페이지를 만들지 않는다.)
• 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지합니다.
• 비 클러스형 인덱스보다 검색 속도는 더 빠르지만 데이터의 입력. 수정, 삭제는 느립니다.

 

Non-Clusted Index(비클러스터형 인덱스)

• 테이블당 약 240개의 인덱스를 만들 수 있습니다.
• 인덱스 페이지는 로그파일에 저장됩니다.
• 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬됩니다.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터형보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠릅니다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지합니다.
  
  

기준	클러스터 인덱스	비 클러스터 인덱스
속도	빠르다	느리다
사용메모리	적다	많다
인덱스	인덱스가 주요 데이터	인덱스가 데이터의 사본(Copy)
개수	한 테이블에 한개	한 테이블에 여러개
리프 노드	리프 노드 자체가 데이터	리프 노드는 데이터가 저장되는 위치
저장값	데이터를 저장한 블록의 포인터	값과 데이터의 위치를 가리키는 포인터
정렬	인덱스 순서와 물리적 순서가 일치	인덱스 순서와 물리적 순서가 불일치

인덱스 구조

인덱스의 자료구조는 해시 테이블, B-Tree 등 다양하게 있었지만 현재에는 주로 B+Tree 가 사용됩니다.

B+Tree

기존의 B-Tree는 어느 한 데이터의 검색은 효율적이지만, 모든 데이터를 한 번 순회하는 데에는 트리의 모든 노드를 방문해야 하므로 비효율적입니다. 이러한 B-Tree의 단점을 개선시킨 자료구조가 B+Tree입니다.

• B-Tree의 경우, internal 또는 branch 노드에 key와 data를 담습니다. 하지만, B+Tree의 경우 브랜치 노드에 key만 담아두고, data는 담지않습니다. 오직 리프 노드에만 key와 data를 저장합니다. (B+Tree는 key값이 중복 될 수 있다.)
• 모든 리프 노드는 연결리스트로 연결 되어있습니다.

B+Tree의 장점

• 리프 노드에만 데이터를 담아두기에 메모리를 적게 사용함으로써 많은 key들을 수용 할 수 있습니다.
  (하나의 노드에 더 많은 key를 담을 수 있기에 트리의 높이가 더 낮아진다.)
• 풀 스캔시, 리프 노드가 연결 되어있으므로 한 번의 탐색만 하면 되기에 B-Tree보다 빠르다.