참고한 영상
[10분 테코톡] 라라, 제로의 데이터베이스 인덱스
🌱 인덱스란?
색인 : 쉽게 찾아볼수 있도록 일정한 순서에 따라 놓은 목록
원하는 값을 빠르게 찾는다! → SELECT
데이터베이스 인덱스란?
데이터가 기준 없이 저장된 상태일 경우 조회를 위해 많은 시간이 소요된다.
데이터가 특정 기준으로 정렬되어있다면, 검색을 빠르게 할 수 있을 것이다.
이메일을 인덱스로 정한 경우 → 빠르게 찾을 수 있다!
SELECT * FROM member
WHERE email = ‘abc@gmail.com’
[1] 인덱스가 적용된 대상 (email 로 정렬된 데이터)
[2] WHERE 절을 통해 검색
- 인덱스가 적용되었다 하더라도, WHERE절을 이용해 검색을 하지 않으면 인덱스는 사용되지 않는다.
- 인덱스는 DB 테이블에 대한 검색 성능을 향상시키는 자료 구조이며 WHERE절 등을 통해 활용 된다.
인덱스의 특징
- 인덱스는 항상 최신의 오름차순 정렬 상태 유지
- 데이터가 정렬되어있기 때문에 테이블에서 검색, 정렬의 속도를 향상시킴
- 인덱스도 하나의 데이터베이스 객체
- 데이터베이스 크기의 약 10% 정도의 저장공간 필요
🤖 인덱스 알고리즘
페이지 : 데이터가 저장되는 단위 (MySQL - 16 Kbyte)
Full Table Scan
특징 : 순차적으로 처음부터 페이지에 접근하기 때문에 접근 비용 감소
Full Table Scan이 사용되는 경우
- 적용 가능한 인덱스가 없는 경우
- 인덱스 처리 범위가 넓은 경우
- 크기가 작은 테이블에 엑세스 하는 경우
B-Tree (Balanced-Tree)
- Binary Seach Tree (이진 탐색 트리) 란?
- 이진 탐색과 연결리스트의 장점이 합쳐져 만든 자료구조
- 균형있는 이진탐색트리의 경우 검색 시간복잡도는 Big-O(Log n), 균형 없는 이진탐색트리의 시간복잡도는 Big-O(n) 이다.
이러한 이진 탐색 트리의 단점을 극복하기 위해 나온 자료구조 중 하나 B-Tree (Balanced-Tree)
B-Tree
- 비트리 높이가 같음
- 자식 노드를 2개 이상 가질 수 있음
- 기본 데이터베이스 인덱스 구조
인덱스 알고리즘
- 루트 페이지 : 자식 페이지의 정보
- 브랜치 페이지 : 자식 페이지의 정보
- 리프 페이지 : 실제 데이터 페이지 (클러스터링 인덱스), 실제 데이터의 주소 페이지(논-클러스터링 인덱스)
SELECT
- 성능이 향상된다.
INSERT로 인한 페이지 분할
-
페이지에 새로운 데이터를 추가할 여유공간이 없을 경우 INSERT를 위해 페이지 확보
-
문제가 발생한 페이지의 데이터를 공평하게 저장한다
⇒ 페이지 분할 특징
- 페이지에 변화가 발생
- DB가 느려지고 성능에 영향을 준다.
DELETE
- 인덱스의 데이터를 실제로 지우지 않고 사용안함 표시를 한다.
UPDATE
- 실제로 인덱스는 UPDATE 개념이 없고 먼저 DELETE를 통해 기존 값을 ‘사용 안함’ 표시하고,
다음으로 INSERT 를 통해 변경된 값을 삽입하여 UPDATE를 진행한다고 한다
DELETE와 UPDATE에 WHERE 조건절 사용하게 되면?
- 처리할 대상을 찾기 위한 조회 부분의 성능은 향상된다
- 사용하지않는 인덱스가 적용되었다면 불필요한 처리량이 증가하고
- 사용안함 표시로 페이지 낭비 및 인덱스 조각화 심해져 성능이 저하된다.
🏈 인덱스 종류
데이터베이스에서 클러스터링이란 실제 데이터와 무리를 이룬다는 것을 의미하고,
클러스터링 인덱스는 실제 데이터와 같은 무리의 인덱스를 의미한다. 예) 실제 데이터가 정렬되어 있는 사전
논-클러스터링 이란 실제 데이터와 무리를 이루지 않음을 의미하고, 논-클러스터링 인덱스란 실제 데이터와 다른 무리의 별도의 인덱스를 의미한다. 예) 실제 데이터 탐색에 도움을 주는 책 뒤의 별도의 찾아보기 페이지
CREATE TABLE member (
id int primary key,
name varchar(255),
email varchar(255) unique
);위에 코드로 테이블을 생성하면 인덱스가 두개 생성된다. 한 컬럼에 primary key를 적용하면 클러스터링 인덱스가 자동생성 된다. 그리고 unique 제약조건을 걸면 논-클러스터링 인덱스가 자동생성된다.
1. 클러스터링 인덱스
- 클러스터링 인덱스 추가 방법 테이블을 생성할 때 제약조건을 걸지 않으면 인덱스는 생성되지 않는다. 데이터에 테이블을 넣고 pk를 적용하거나, 한 컬럼에 NOT NULL과 UNIQUE 제약 조건을 한번에 걸어주면 클러스터링 인덱스를 추가할 수 있다.
[방법1]
ALTER TABLE 테이블명 ADD CONSTRAINT pk_id **PRIMARY KEY** (id) ;[방법2]
ALTER TABLE 테이블명 MODIFY COLUMN id int **NOT NULL**;
ALTER TABLE 테이블명 ADD CONSTRAINT nuq_id **UNIQUE** (id) ;클러스터링 인덱스 추가 → 적용한 컬럼 기준 데이터 정렬 → 정렬된 데이터 기준으로 루트 페이지, 리프 페이지 생성 (b-tree 구조) → 리프 페이지가 데이터 변화에 맞추어 동기화된다.
- 특징
- 실제 데이터 자체가 정렬
- 테이블당 1개만 존재 가능
- 리프 페이지가 데이터 페이지
- primary key , unique + not null 조건으로 자동 생성 (pk 우선순위)
2. 논-클러스터링 인덱스
- 생성 방법 ⇒ 직접 인덱스 생성
[방법1] 한 컬럼에 UNIQUE 제약조건을 걸어 생성한다
ALTER TABLE 테이블명 ADD CONSTRAINT unq_name **PRIMARY KEY** (name) ;[방법2] UNIQUE INDEX를 생성한다. (중복 허용 X)
CREATE UNIQUE INDEX unq_idx_name ON 테이블명 (name) ;[방법3] 디폴트 INDEX를 생성한다. (중복 허용)
CREATE INDEX unq_idx_name ON 테이블명 (name) ;실제 데이터가 저장된 데이터 페이지는 어떠한 정렬이나 변경도 일어나지 않는다
데이터 페이지가 리프 페이지였던 클러스터링 인덱스와 달리 별도의 name에 대한 인덱스 페이지가 추가로 생성된다.
특징
- 실제 데이터 페이지는 그대로
- 별도의 인덱스 페이지 생성 → 추가 공간 필요
- 테이블당 여러개 존재 가능
- 리프 페이지에 실제 데이터 페이지 주소를 담고 있음
- unique 제약조건 적용시 자동 생성
- 직접 index 생성시 논-클러스처링 인덱스 생성
클러스터링 인덱스 + 논클러스터링 인덱스 구성
- 논-클러스터링 인덱스에서 리프 페이지에 실제 데이터 페이지 주소가 아닌 클러스터링 인덱스가 적용된 실제 값이 존재하게 된다.
🧩 인덱스 적용 기준
- 카디널리티가 높은 컬럼 ( = 중복 수치가 낮은 컬럼)
- 카디널리티란 그룹 내 요소의 개수를 뜻함
- WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
- 조건절이 없다면 인덱스는 사용되지 않는다.
- 인덱스는 추가 공간이 필요로 된다.
- INSERT / UPDATE / DELETE가 자주 발생하지 않는 컬럼
- 성능 저하에 대한 영향을 미칠 수 있다.
- 규모가 작지 않은 테이블
💣 인덱스 사용시 주의사항
-
잘 활용되지 않는 인덱스는 제거하자
- WHERE 절에 사용되더라도 자주 사용해야 가치가 있으며, 불필요한 인덱스는 성능저하로 이어질 수 있다
-
데이터 중복도가 높은 컬럼은 인덱스 효과가 작다
-
자주 사용되더라도 INSERT/UPDATE/DELETE가 자주 일어나는지 고려해야 한다.
- 일반적인 웹 서비스에서 쓰기:읽기 = 2 : 8 정도 된다고 한다. 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 방법이다.
