[DB] DB 인덱스란?

🫧 DB 인덱스란?

 

데이터베이스의 검색 속도를 향상시키기 위한 자료구조를 말한다.

→ 책에서 목차를 보면 페이지 번호만으로 원하는 내용의 부분을 한 번에 찾을 수 있는 것처럼 검색을 빠르고 쉽게 만들어주는 의미이다

📃 Full Table Scan

full table scan이란 데이터베이스를 조회할 때 where문을 이용해서 테이블의 전체를 조건과 비교해야되는 작업이다.

그래서 굉장히 번거롭고 시간이 오래걸림!

 

⚙️ B-Tree

B-Tree는 Balanced Tree의 줄임말이다.

MySQL의 DB 엔진인 InnoDB는 B+Tree로 이루어져있으며 이것은 B-Tree의 확장된 개념이다.

B-Tree

B-Tree는 데이터가 정렬된 상태로 유지되어 있다는 것이 핵심이다.

차례대로 루트노드, 브랜치 노드, 리프노드로 이루어져있다.

• B-Tree는 한 노드 당 자식 노드가 2개 이상이 가능하다.
• key값을 이용해 찾고자 하는 데이터를 트리 구조를 이용해 찾는다.
• 균형잡힌 트리이다.
• 정렬을 사용한다.

항상 균형잡힌 트리 모양을 유지하고, 정렬을 이용하기 때문에 새로운 값이 들어왔을 경우 정렬 상태와 균형을 유지하기 위해서 트리의 모양을 바꿔야 한다.

👉 따라서 쓰기 성능은 줄어들고 검색 성능은 매우 올라간다!!

B+Tree

B+Tree는 B-Tree의 확장 개념으로 B-Tree의 경우에는 브랜치 노드에 key와 data를 모두 담을 수 있다면 B+Tree에는 브랜치노드에 key만 담아두고 리프노드에만 key와 data를 저장한다.

여기서 리프노드끼리는 linked list로 연결되어있다. 

장점

• 리프 노드를 제외하고 데이터를 담아두고 있지 않기 때문에 메모리를 확보함으로써 더 많은 key를 수용할 수 있다.
• 하나의 노드에 여러 key를 담을 수 있기 때문에 트리의 높이는 더 낮아진다 → cache hit를 높일 수 있음 (cache hit란 CPU가 참조하고자 하는 메모리가 캐시에 존재하고 있을 경우를 말한다.)
• B+Tree의 경우에는 리프노드에 모든 데이터가 있기 때문에 한 번의 선형탐색만 하면 돼서 B-Tree보다 빠르다.

⚙️인덱스 구조

인덱스는 물리적/논리적으로 테이블하고 독립적이다.

테이블은 컬럼과 데이터가 정렬되지 않고 입력된 순서대로 들어가지만 인덱스는 key컬럼(인덱스를 생성하라고 지정한 컬럼의 값)과 rowid컬럼 두 개로 이루어져있으며 정렬이 가능하다.

MySQL에서 테이블 생성할 때 아래의 세개 파일이 생성된다.

• FRM : 테이블 구조 저장 파일
• MYD : 실제 데이터 파일
• MYI : 인덱스 정보 파일 (인덱스를 사용할 때 생성된다)

→ MYI 디스크 공간은 실제 테이블을 저장할 때 사용되는 디스크 공간보다 작은데 보통 인덱스는 테이블의 세부항복을 가지고 있지 않기 때문이다.

⚙️작동 원리

SELECT * FRO EMP WHERE empno = 7902;

1. 서버 프로세스가 파싱 과정을 마친 후 DB 버퍼 캐시에 empno가 7902 정보가 있는지 확인한다.
2. 정보가 없으면 하드 디스크 파일에서 7902정보를 가진 블록을 복사해서 DB 버퍼 캐시로 가져온 후 7900정보만 골라내서 사용자에게 보여준다.

이때, 인덱스를 사용하는 경우에는 WHERE절의 컬럼이 인덱스가 만들어져있는지 확인 후 인덱스에 먼저 가서 7902정보가 어떤 rowid를 가지고 있는지 확인 후 rowid에 있는 블록만 찾아가서 DB버퍼 캐시에 복사한다.

없으면, 다 복사한 후 하나하나 찾음

⚙️ 인덱스 방식

Clustered Index

책으로 비유하자면 페이지를 알고있어서 바로 해당 페이지를 펼치는 것과 같음

→ 테이블의 데이터를 지정된 컬럼에 대해 물리적으로 데이터를 재배열한다. 

키 값과 데이터 페이지 번호로 구분하고, 검색하고자하는 데이터의 키 값으로 페이지 번호를 검색해서 데이터를 찾는다.

• 테이블에서 인덱스를 걸면 가장 효율적일 것 같은 칼럼을 cluster index로 지정한다. 

  

 

Non-Clustered Index

책으로 비유하자면 목차에서 찾고자하는 내용의 페이지를 찾고나서 해당 페이지로 이동하는 것

• 물리적으로 데이터를 배열하지 않은 상태로 데이터 페이지가 구성된다.

 

• 데이터 페이지를 건들이지 않고 별도의 장소에 인덱스 페이지를 생성한다.
• 여기서 구분 기준을 다르게 바꾸더라도 데이터페이지는 움직이지 않고 그대로 존재하게 됨

인덱스를 어디에 활용하면 좋을까?

• where, join 조건에 자주 발생하는 칼럼
• insert, update, delete이 적게 발생하는 컬럼
• 중복도가 낮은 컬럼
• 범위 검색이 적은 컬럼
• 데이터가 많은 테이블