데이터베이스 인덱스(Index)

1. Index란?

테이블 검색 속도를 높여주는 자료구조. 

특정 칼럼에 인덱스를 부여하면 별도의 메모리 공간에 칼럼의 데이터와 각 데이터의 물리적 주소가 함께 저장된다.

이 때 데이터는 정렬해서 저장하므로 이진 탐색을 사용할 수 있다. => 탐색 속도가 빠르다(O(logN))

primary key 또는 unique key가 걸린 칼럼에는 자동으로 PRIMARY라는 인덱스가 부여된다.

PRIMARY 인덱스는 clustered index라고 한다.

PRIMARY 이외의 인덱스는 non-clustered index 또는 secondary index라고 한다.

CREATE TABLE customers(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   account VARCHAR(100) NOT NULL,
   name VARCHAR(100) NOT NULL,
   city VARCHAR(100) NOT NULL,
   country VARCHAR(100) NOT NULL
);

-- 일반 인덱스 생성
CREATE INDEX indx_name ON customers(name);
CREATE INDEX origin ON customers(city, country);

-- 유니크 인덱스 생성
-- 중복되지 않는 값만 저장함.
-- account 칼럼에 중복된 데이터가 있는 경우 유니크 인덱스 생성 불가(오류남)
-- 유니크 인덱스 생성 후 중복된 데이터 넣으려고 하면 오류남
CREATE UNIQUE INDEX account ON customers(account);

-- 인덱스 삭제
ALTER TABLE customers DROP INDEX indx_name

-- 테이블에 걸린 인덱스 확인
SHOW INDEXES FROM customers;

2. Index 장점

1) 조건 검색 시 효율적(WHERE 절에 사용)

테이블에 INSERT문으로 데이터를 입력하면 정렬되지 않은 상태로 데이터가 저장된다.

따라서 테이블에서 특정 데이터를 찾으려면 전체 테이블을 다 읽어봐야 한다(=Full Table Scan).

하지만 인덱스를 만들면 데이터가 인덱스 테이블에 정렬된 상태로 들어가므로

즉 특정 데이터를 찾을 때 이진 탐색으로 더 빨리 찾을 수 있다. 

 

2) 정렬 시 효율적(ORDER BY 절에 사용)

정렬되지 않은 데이터를 정렬해서 가져오는 것은 부하가 큰 작업이다.

하지만 인덱스를 사용한 칼럼은 데이터가 이미 정렬되어 있기 때문에 부하가 적다.

 

3) MIN, MAX 처리가 효율적

데이터가 이미 정렬되어 있기 때문에 레코드의 시작 값 또는 끝 값만 찾으면 된다. 

3. Index 단점

1) 데이터 추가,수정, 삭제가 빈번한 경우 자원 소모 큼

INSERT, UPDATE, DELETE가 자주 사용되는 경우 INDEX 테이블도 수정 필요.

- INSERT : 새로운 데이터에 대한 인덱스 추가

- DELETE: 삭제할 데이터의 인덱스는 사용하지 않는다고 처리

- UPDATE: 기존의 인덱스를 사용하지 않는다고 처리, 수정된 데이터를 인덱스에 추가

 

2) 추가 저장 공간 필요

인덱스 테이블을 위해 데이터베이스의 10%에 해당하는 추가 저장공간 필요.

 

3) 많은 데이터 처리 시 비효율적

전체 데이터 중 10~15% 이하의 데이터 처리 시에만 효율적.

모든 데이터에 인덱스가 걸려있으면 비효율적임. 

4. Index 생성 전략

중복값이 없는 칼럼에서 인덱스 사용이 가장 효율적.

모든 값이 같은 칼럼에서는 인덱스 사용 의미가 없음.

 

1) 조건절에 자주 사용하는 칼럼(WHERE)

2) = 으로 비교하는 칼럼

3) 중복되는 데이터가 많지 않은 칼럼(=분포도가 좋은 칼럼)

4) ORDER BY에서 자주 사용하는 칼럼

5) JOIN시 자주 사용하는 칼럼

 

 

참고

[코딩팩토리] 데이터베이스 인덱스(Index)란 무엇인가?