데이터베이스(DB) index

시작하며

Index 정리 및 예시를 통해 학습한 내용을 정리하였다.

인덱스란?

 - 인덱스는 데이터베이스 테이블에 대한 검색 성능 속도를 향상시켜주는 자료 구조이다. 인덱스는 특정 컬럼(여러컬럼의 조합)에 대한 정렬된 값의 집합으로, 데이터베이스 엔진이 데이터를 빠르게 찾을수 있도록 도와준다.

Ex) 책의 목차라고 생각하면 이해가 쉽다. 책의 목차에서는 내가 찾고자 하는 페이지가 어디 있는지 빠르게 찾을 수 있도록 해준다.

 

인덱스는 데이터베이스 테이블의 특정 컬럼(여러 컬럼)의 값을 사전 순서 또는 정렬 순서로 저장하여 데이터 접근과 검색을 최적화한다.

일반적으로 B-트리(B-tree)나 해시 테이블 등의 자료구조를 사용하여 인덱스를 관리한다.

 

실생활에서의 인덱스 사용예제)

 

인덱스의 자료구조

해시 테이블(Hash Table)

- 해시 테이블은 Key, Value를 한쌍으로 데이터를 저장하는 자료구조이다. key값을 이용해 대응된는 value값을 구하는 방식이다. 평균적으로 0(1)의 매우 빠른 시간만에 원하는 데이터를 찾을 수 있지만, 실제 인덱스에서 잘 사용하지 않는다. 해시 테이블은 등호(=) 연산에 최적화 되어있기 떄문이다. 

- 데이터베이스에선 부등호(<. >) 연산이 자주 사용되는데, 해시 테이블 내의 데이터들은 정렬되어 있지 않으므로 특정 기준보다 크거나, 작은 값을 빠른시간내에 찾을 수가 없다.

B-Tree

- B-Tree는 자식 2개만을 갖는 이진트리(Binary Tree)를 확장하여 N개의 자식을 가질 수 있도록 고완된것이다. 

- B-Tree는 생성당시는 균형 트리이지만, 테이블 갱신의 반복을 통해서 균형이 깨지고, 성능이 약화된다.

 

B+ Tree

- B+ Tree는 B-Tree의 확장으로서, 오직 leaf node에만 데이터를 저장하고 leaf node가 아닌 node에서는 자식 포인터만 저장한다.

- Leaf node에만 데이터를 저장하므로, B-Tree에 비해 같은 node에 더 많은 키를 저장 할 수 있다.

- 데이터를 찾기 위해 leaf node 까지 탐색을 해야 하는데, Lined list로 연결되어 있기 때문에 full scan시 leaf node들만 순차 탐색하면
  되기때문에 B-Tree보다 탐색에 유리하다.

 

mysql의 경우 사용하는 엔진에 따라 다른 구조를 가진다.

Clustered Index vs Non Clustered Index

Clusterd Index 특징

• 테이블 당 1개만 존재
• PK 제약조건으로 컬럼을 생성시 자동 적용
• 데이터가 정렬된 상태

Ex) 해당 DB Index 페이지에서 DDD라는 컬럼이 추가가 되는 상황이다.

       현재의 리프페이지에는 더이상 데이터가 추가될수 없기에 페이지 분할이 일어나며 DDD라는 컬럼이 추가가 되었다.

현재에서 KKK라는 컬럼이 추가시 루트페이지 및 리프페이지가 부족하다 리프페이지의 페이지 분할 및 루트추가 작업이 발생한다.

 

Non Clusterd Index 특징

• Secondary Index(보조 인덱스) 라고도 한다.
• 테이블에 여러개 존재 할 수 있다.
• Unique 제약조건으로 컬럼을 생성시 자동 적용
• 정렬되지 않아도 된다.
• 리프페이지에서 데이터가 있는 곳의 주소를 가진다.
• Clusterd Index에 비해 조회 속도가 느리지만, Insert, Update, Delete 시 부하가 적다.

※ PK를 Clusterd Index가 아닌 Non Clusterd Index로 적용도 가능하다.

CREATE TABLE TB_STUDENT (
  ID INT PRIMARY KEY NONCLUSTERED
  ...
)

어떠한 컬럼에 Index를 설정해야하는가?

1. 핵심적인 기준 4가지
   • 카디널리티가 높은(↑) 컬럼
     - 카디널리티가 높다란, 한 컬럼이 갖고 있는 값의 중복도가 낮다는 뜻이다.
     Ex) 사람이라는 테이블을 기준으로 Gender라는 성별은 남, 여 만 존재한다. 이보다, 주민등록번호와 같은 고유값을 설정하는게 좋다.
   • 선택도가 낮은(↓) 컬럼
     - 선택도 = 카디널리티 / 전체레코드수, 선택도가 1이면 모든 데이터가 unique하다는 뜻이다.
   • 조회 활용도가 높은(↑) 컬럼 
     - Where의 대상 컬럼으로 많이 활용되어야 Index설정의 의미가 있다.
   • 수정빈도가 낮은(↓) 컬럼
2. 그 밖의 Index 명시 사항
   • WHERE에 자주 사용되는 컬럼
   • LIKE와 사용할 경우에는 %가 뒤에 사용되도록 하기(%가 앞에 사용될 경우 Full Table Scan을 함.)
     Ex) LIKE 'index%'
   • ORDER BY에 자주 사용되는 컬럼에 사용하기
   • JOIN에 자주 사용되는 컬럼에 사용하기
   • 데이터의 변경이 없는 컬럼에 사용하기
   • WHERE 절 컬럼에 연산을 사용할 경우 Index를 사용하지 않는다.
     Ex) SELECT * FROM TB_TABLE WHERE COLUMN * 10 < 100              (X)
            SELECT * FROM TB_TABLE WHERE COLUMN < 100 * 10              (O)

그렇다면 Index는 몇개 설정해야 좋을까?

Index 설정 시 데이터베이스에 할당된 메모리를 사용하여 테이블 형태로 저장하게된다. 그렇기 때문에 무분별한 Index 설정은 메모리를 많이 사용하게되며, Index로 지정된 컬럼의 값이 바뀌게 되면 Index 테이블이 갱신되어야 하므로 느려 질 수 있다.

그렇기에 Index는 한 테이블당 3~5개가 적당하다.

Index 설정 및 삭제

-- Clusterd Index 설정
ALTER TABLE '테이블명' ADD CONSTRAINT '인덱스명' PRIMARY KEY ('컬럼명');

-- Non Clusterd Index 설정
ALTER TABLE '테이블명' ADD CONSTRAINT '인덱스명' UNIQUE ('컬럼명');

-- Clusterd Index 설정(단 PK가 아닌 값을 지정하기 때문에 Unique여야만 한다.) 
CREATE FULLTEXT INDEX '인덱스명' ON '테이블명'('컬럼명');

-- Non Clusterd Index 설정(중복 허용) 
CREATE INDEX '인덱스명' ON '테이블명' ('컬럼명');

-- Non Clusterd Index 설정(중복 비허용)
CREATE UNIQUE INDEX '인덱스명' ON '테이블명' ('컬럼명');

-- Non Clusterd Index 설정(다중 컬럼 인덱스 생성)
CREATE UNIQUE INDEX '인덱스명' ON '테이블명' ('컬럼명', '컬렴명');

-- Index 삭제
DROP INDEX '인덱스명' ON '테이블명'
ALTER TABLE '테이블명' DROP INDEX '인덱스명'

Index 성능 비교

예제)

CREATE TABLE TB_STUDENT(
ID INT AUTO_INCREMENT PRIMARY KEY, -- ID : PK(Clusterd Index)
NAME VARCHAR(20),
AGE INT,
CTN VARCHAR(20) UNIQUE -- CTN : Unique(Non Clusterd Index)
);

테이블에 등록된 Index 확인

show index from '테이블명';

테이블의 index 크기 확인

show table status like '테이블명';

SELECT

-- Clusterd Index
SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';
select * from TB_STUDENT ts WHERE ID = 52345;
SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';

Clusterd Index의 경우 하나의 데이터를 찾기까지 20건 이하의 페이지를 찾았다.

-- Non Clusterd Index
SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';
select * from TB_STUDENT ts WHERE CTN = 9965-8467-0135;
SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';

반면, Non Clusterd Index의 경우 상당히 많은 페이지를 찾았다.

해당 Test를 통해 Clusterd Index의 조회성능을 알 수 있다.

 

단일 컬럼 인덱스와 다중 컬럼 인덱스 차이

-- 단일 컬럼 인덱스 테이블
CREATE TABLE TB_STUDENT10(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
CTN VARCHAR(20) NOT NULL,
PRIMARY KEY(ID),
INDEX idx_name(NAME),
INDEX idx_CTN(CTN)
);

-- 다중 컬럼 인덱스 테이블
CREATE TABLE TB_STUDENT20(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
CTN VARCHAR(20) NOT NULL,
PRIMARY KEY(ID),
INDEX idx_NAME_CTN(NAME, CTN)
);

각각의 테이블에 프로시저를 이용하여 100만건의 데이터를 삽입하였다.

이후 SELECT 실행에 따른 EXPLAIN 결과이다.

EXPLAIN SELECT * FROM TB_STUDENT10 ts 
WHERE NAME = 'NAME934540' AND CTN = 'CTN934540';

EXPLAIN SELECT * FROM TB_STUDENT20 ts 
WHERE NAME = 'NAME934540' AND CTN = 'CTN934540';

다중 컬럼 인덱스가 단일 컬럼인덱스에 비해 검색 소요시간이 더욱 짧다.