[SQL] Index

Index란

인덱스는 RDBMS에서 검색 속도를 높이기 위한 기술이다.

Table의 컬럼을 색인화(따로 파일로 저장) 하여 검색 시 해당 테이블의 레코드를 Full Scan 하는 게 아니라 색인화 되어있는 인덱스 파일을 검색하여 검색 속도를 빠르게 한다. => B-Tree 구조

 

보통 SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을 때 인덱스가 사용된다.

DELETE, INSERT, UPDATE 쿼리에는 해당 사항이없으며 INDEX 사용 시 오히려 느려진다.

 

SQL 서버에서 데이터의 레코드는 내부적으로 아무런 순서없이 저장되는데 이때 데이터 저장 영역을 Heap이라고 한다.

Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 모두 조회하여 검색 조건과 비교하게 된다. => Table Scan, Full Scan

이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다.

 

Index 구조

Index는 논리적/물리적으로 테이블과 독립적이다.

테이블은 컬럼에 데이터가 정렬되지 않고 입력된 순서대로 들어가지만, Index는 KEY 컬럼(인덱스 생성을 지정한 컬럼의 값)과 ROWID 컬럼 두 개로 이루어져 있고 오름차순, 내림차순으로 정렬이 가능하다.

 

MySQL에서 테이블 생성 시, 3가지 파일이 생성된다.

• FRM : 테이블 구조 저장 파일
• MYD : 실제 데이터 파일
• MYI : Index 정보 파일 (Index 사용 시 생성)

사용자가 쿼리를 통해 Index를 사용하는 컬럼을 검색하게 되면, 이때 MYI 파일의 내용을 활용한다.

디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다. 보통 인덱스는 KEY-ROWID만 가지고 있고, 테이블의 세부항목들은 갖고 있지 않기 때문이다.

 

 

Index의 동작 원리

SELECT *
FROM EMP
WHERE empno=808;

데이터 파일의 블록이 10만 개라고 가정했을 때, 위 SQL문을 실행하면

1. 서버 프로세스가 파싱 과정을 마친 후 DB buffer cache에 empno 가 808인 정보가 있는지 확인한다.

2. 정보가 없으면 하드 디스크 파일에서 808 정보를 가진 블록을 복사해서 DB buffer cache로 가져온 후 808 정보만 골라내서 사용자에게 보여준다.

 

이 때 Index가 있는 경우와 없는 경우로 나뉜다.

• Index가 없는 경우
  808 정보가 어떤 블록에 들어 있는지 모르므로 10만 개 전부 db buffer cache로 복사한 후 하나하나 찾는다.
• Index가 있는 경우
  where 절의 컬럼이 index가 만들어져 있는지 확인 후, 인덱스에 먼저 가서 808 정보가 어떤 ROWID를 가지고 있는지 확인한 후 해당 ROWID에 있는 블록만 찾아가서 db buffer cache에 복사함.

 

Index 사용 방법

• 인덱스 생성

CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......);

CREATE[UNIQUE] INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......);

=> UNIQUE를 붙이면 컬럼 값에 중복을 허용하지 않겠다는 뜻

ALTER TABLE  테이블명 ADD INDEX(필드명(크기));

 

• 인덱스 삭제

DROP INDEX [인덱스 명]

 

• 인덱스 확인

SHOW INDEX FROM 테이블이름

 

---

위 내용만 놓고 본다면 index를 이용하는 것이 성능 문제 해결을 위한 좋은 해결책이라고 생각할 수 있다.

문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만, 조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 Insert, Delete, Update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하한다.

그렇기 때문에 인덱스를 생성하는 것보다는 SQL문을 효율적으로 짜는 방향으로 문제를 해결해야 한다. => 인덱스는 마지막 수단