이 글의 목적?
이전 글에서는 MySQL 아키텍처와 관련된 내용을 정리했었다. 그리고 인덱스가 얼마나 중요한 역할을 하는지에 대해서도 감을 잡을 수 있었다.
이번에는 인덱스는 어떻게 설정해야할까? 라는 질문에서 시작해보기로 했는데, 이번 글도 역시 10분 테크톡 - 라라, 제로의 데이터베이스 인덱스 영상을 시청하면서 정리해보기로 했다.
인덱스 정의
인덱스는 사전적 의미로 색인이라고 한다. 색인은 쉽게 찾아볼 수 있도록 일정한 순서에 따라 정리한 목록을 의미한다.
데이터베이스에서의 인덱스는 데이터의 기준을 의미한다. 만약 인덱스가 잡혀있지 않은 상태에서 조회 쿼리를 요청할 경우에는 테이블의 모든 데이터를 순차적으로 확인하기 때문에 느리지만, 데이터가 특정 기준으로 정렬되어있을 경우에는 검색을 빠르게 처리할 수 있다.
영상에서는 하단의 이미지처럼 WHERE 절에 인덱스가 적용된 대상을 조건으로 걸어두어 빠르게 검색하는 예시를 들었다.
반대로 인덱스를 지정했음에도 인덱스를 WHERE 조건의 대상으로 지정하지 않았을 경우에는 속도가 저하 될 것임을 보여주었다.
결국 데이터베이스 테이블에 대한 검색 성능을 향상시키는 자료 구조이며 WHERE 절 등을 통해 활용하는 것이 인덱스다!
💡 PK vs 인덱스?
참고로 PK와 인덱스는 다르다. PK는 개념적인 값으로 - 유일한 레코드임을 보장하며, 일반적인 DBMS에서는 PK는 자동으로 인덱스가 적용된다. 반대로 인덱스는 유일성을 보장하지 않고 오로지 테이블에서 레코드를 빠르게 찾기 위해서 사용된다.
인덱스 알고리즘
Full Table Scan
Full Table Scan은 말 그대로 원하는 데이터를 찾을 때까지 처음부터 끝을 모두 순차적으로 확인하는 것을 의미한다.
여기서 집중해야할 부분은 언제 Full Table Scan이 사용되는지다.
- 적용 가능한 인덱스가 없을 경우
- 인덱스 처리 범위가 넓은 경우
- 크기가 작은 테이블에 엑세스하는 경우
B-Tree
영상에서는 B-Tree 알고리즘이 나온 배경부터 간단하게 설명했다.
이진 탐색 트리는 균형이 있을 경우와 균형이 없을 경우로 분리된다. 균형이 있는 이진 탐색 트리의 시간복잡도는 O(logn)이지만 균형이 없는 이진 탐색 트리의 시간복잡도는 O(n)이라는 결과가 나온다. 즉, 아무리 이진 탐색 트리라고 해도 비효율적인 경우가 존재하기 때문에 이를 극복한 다양한 자료구조가 나오게 되는데, 그 중 하나가 바로 B-Tree 알고리즘이라는 것이다.
B-Tree(Balanced-Tree)는 트리 높이가 같고 자식 노드를 2개 이상 가질 수 있으며 기본 데이터베이스 인덱스 구조를 가진다. 데이터베이스에서의 B-Tree는 하단의 의미지처럼 크게 루트 페이지, 브랜치 페이지, 리프 페이지로 나뉘어 진다.
SELECT
Full Table Scan에서의 이미지를 B-Tree 형태로 표현해보면 B-Tree 구조가 조회 쿼리에서 얼마나 효율적인지를 알 수 있다.
INSERT
데이터를 삽입할 때의 B-Tree는 페이지에 여유가 있을 경우 페이지 내부에서 작업이 되어 큰 부담은 없다.
하지만 반대로 페이지가 가득 차있을 때는 비어있는 페이지를 확보하고 문제가 있는 페이지의 데이터를 공평하게 나누어 저장하기 때문에 비효율적으로 동작하는 것을 볼 수 있다.
즉, 페이지에 새로운 데이터를 추가할 여유공간이 없어 페이지에 변화가 발생하는 것을 페이지 분할이라고 하며, 데이터베이스가 느려지고 성능에 영향을 주는 단점이 생긴다.
DELETE
데이터를 삭제할 때는 인덱스의 데이터를 실제로 지우지 않고 사용하지 않음을 표시한다.
UPDATE
데이터의 업데이트는 위의 두 가지 과정을 수행한다. 먼저 기존의 값을 사용하지 않는다는 표시를 수행(DELETE)하고 변경된 값을 삽입(INSERT)하는 과정을 거친다.
💡 INSERT, DELETE, UPDATE의 WHERE 절에 인덱스를 적용하면 되지 않는가?
WHERE 절로 처리할 대상을 찾기 위한 조회 성능은 향상되지만, 사용하지 않는 인덱스가 적용되었을 경우 불필요한 처리량이 늘고 사용하지 않음 표시로 페이지 낭비 및 인덱스 조각화가 심해질 수 있다. 즉, B-Tree 알고리즘은 조회의 성능이 향상된다는 점에서 큰 이점을 가지지만, 삽입/삭제/수정의 경우 성능이 저하된다는 단점이 있다.
인덱스 종류
인덱스는 크게 클러스터링 인덱스와 논-클러스터링 인덱스로 나뉜다. 클러스터링 인덱스는 실제 데이터와 같은 무리의 인덱스를 의미하는데 - 대표적인 예시로 실제 데이터가 정렬된(ㄱ,ㄴ,ㄷ,ㄹ 등) 사전을 생각할 수 있다. 반대로 논-클러스터링 인덱스는 실제 데이터와 다른 무리의 별도의 인덱스를 의미하는데 - 실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지를 생각할 수 있다.
영상에서는 PK 컬럼을 클러스터링 인덱스, UNIQUE 컬럼을 논-클러스터링 인덱스라 했다.
클러스터링 인덱스
영상에서는 예시를 통해 클러스터링 인덱스에 대해 설명했다.
1. 아무런 제약 조건 없이 테이블을 생성하여 데이터를 삽입
2. id 컬럼에 PK를 적용하거나 혹은 NOT NULL과 UNIQUE 조건을 적용
- id를 기준으로 데이터를 정렬
- B-Tree 구조 적용
3. id가 7인 데이터 검색
클러스터링 인덱스를 정리하자면 다음과 같다.
- 실제 데이터 자체가 정렬되어있음
- 테이블당 단 1개만 존재 가능
- 리프 페이지가 데이터 페이지와 동일
- PK나 NOT NULL + UNIQUE 조건을 설정하면 되며, 하나의 컬럼에 두 조건이 함께 있을 경우 PK가 우선순위를 가지고 인덱스를 생성
논-클러스터링 인덱스
1. 아무런 제약 조건 없이 테이블을 생성하여 데이터를 삽입
2. name 컬럼에 UNIQUE만을 적용하거나 중복을 허용하지 않는 UNIQUE INDEX를 생성하거나 중복을 허용하는 INDEX를 적용
- 실제 데이터가 저장되어있는 페이지는 정렬이나 변경되지 않음
- 별도의 name에 대한 인덱스 페이지가 생성됨
3. name이 라라인 데이터 검색
논-클러스터링 인덱스를 정리하자면 다음과 같다.
- 실제 데이터 페이지는 정렬되거나 변경되지 않음
- 별도의 인덱스 페이지를 생성하기에 추가 공간이 필요
- 테이블당 여러 개 존재
- 리프 페이지에 실제 데이터 페이지 주소를 담고 있음
- UNIQUE 제약 조건 적용시 자동 생성
- 직접 인덱스 생성시 논-클러스터링 인덱스 생성
클러스터링 인덱스 + 논-클러스터링 인덱스
클러스터링 인덱스와 논-클러스터링 인덱스가 같이 적용되어있을 경우에는 결론적으로 논-클러스터링 인덱스의 리프 페이지에 데이터 페이지의 주소값이 아닌 클러스터링 인덱스가 적용된 컬럼의 실제 값이 들어가게 된다.
위의 예시에서 id 컬럼에는 클러스터링 인덱스를 설정하고 name 컬럼에는 논-클러스터링 인덱스를 동시에 지정할 경우에 라라 데이터를 검색시 다음과 같이 동작한다.
데이터 페이지의 주소값이 들어가지 않은 이유는 데이터를 삽입하거나 삭제할 때를 생각하면 된다. 위의 예시에서 새로운 데이터를 삽입한다고 가정했을 때, id를 기준으로 정렬되기 때문에 페이지 분할이 일어날 것이고 그에 따라 분할이 일어난 주소값을 또 수정해야하는 문제가 발생하기 때문이다.
인덱스 적용 기준
인덱스를 적용하는 기본적인 기준은 카디널리티를 보면 된다. 즉, 카디널리티가 높은 = 중복도가 낮은 것을 기준으로 적용해주면 된다. 하단의 이미지로 친다면 id, 이메일이 될 것이다.
그 외의 기준들과 정리해보면 다음과 같다.
- 카디널리티가 높은 컬럼
- WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
- INSERT / UPDATE / DELETE가 자주 발생하지 않는 컬럼
- 규모가 작지 않은 테이블
인덱스 사용시 주의사항
무작정 인덱스를 설정한다고 해도 좋은 것만은 아니다. 따라서 영상에서는 인덱스를 사용할 때 주의사항을 이야기했다.
1. 잘 활용되지 않는 인덱스는 과감하게 제거
- WHERE 절에 사용되더라도 자주 사용해야 가치가 있음
- 불필요한 인덱스로 성능저하가 발생할 수 있음
2. 데이터 중복도가 높은 컬럼은 인덱스 효과가 적음
3. 자주 사용되더라도 INSERT / UPDATE / DELETE가 자주 일어나는지 고려
- 일반적인 웹 서비스와 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 혹은 1:9
- 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법
너무 좋은 영상을 발표해주신 라라님과 제로님 감사합니다. 🥹
이 글의 레퍼런스?
