[SQL/DataBase]인덱스(Index)란?

인덱스(Index)란?
인덱스는 관계형 데이터베이스(RDBMS)에서 검색 속도를 높이기 위한 기술이다.

테이블의 컬럼을 색인화(따로 파일로 저장)하여 검색 시 해당 테이블의 레코드를 Full Scan하는게 아닌 색인화 되어있는 Index 파일을 검색하여 검색속도를 향상시킨다.

RDBMS에서 사용하는 인덱스는 B-Tree 에서 파생된 B+Tree를 사용해서 색인화를 한다.

보통 SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을 때 인덱스가 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있다.

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

조금 더 자세히 알아보면, SQL서버에서 데이터의 레코드는 내부적으로 아무런 순서없이 저장된다.
이 때 데이터 저장영역을 Heap이라고 한다.
Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 모두 조회하여 검색 조건과 비교하게 된다.
이러한 데이터 검색방법을 테이블 스캔 또는 풀 스캔이라고 한다.

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

즉 인덱스는 데이터를 SELECT 할 때 빨리 찾기 위해 사용된다.

---

인덱스(Index)를 사용하는 이유
 - WHERE 구문과 일치하는 열을 빨리 찾기 위해
 - 특정 열을 고려 대상에서 빨리 없애 버리기 위해
 - JOIN을 실행 할 때 다른 테이블에서 열을 추출하기 위해
 - 특정하게 인덱스 된 컬럼을 위한 MIN() 또는 MAX()값을 찾기 위해
 - 사용할 수 있는 키의 좌측 접두사를 가지고 정렬 및 급화를 하기 위해
 - 데이터 열을 참조하지 않는 상태로 값을 추출하기 위해서 쿼리를 최적화하는 경우

---

인덱스(INDEX) 알고리즘 종류

1. B+Tree
 - 인덱스에 일반적으로 사용되는 알고리즘은 B+Tree이다.
 - B+ Tree 인덱스는 컬럼의 값을 변형하지 않고(값의 앞부분만 잘라서 관리), 원래의 값을 이용해 인덱싱하는 알고리즘이다.

2. Hash
 - 컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다.
 - 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 등 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

데이터 접근에 시간 복잡도가 O(1)인 HashTable말고 B-Tree를 사용하는 이유는 SELECT 절의 조건에 부등호 연산이 포함될 경우 문제가 발생한다.
HashTable은 동등 연산에 특화되어 있어 데이터베이스의 자료구조에 적합하지 않다.

---

인덱스(INDEX) 구조
인덱스 구조는 논리적/물리적으로 테이블과 독립적이다.

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

Key : 인덱스를 생성하라고 지정한 컬럼의 값

MySQL에서 테이블 생성 시, 아래와 같은 3가지 파일이 생성된다.
 - FRM : 테이블 구조 저장 파일
 - MYD : 실제 데이터 파일
 - MYI : 인덱스 정보 파일(인덱스 사용 시 생성)

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

디스크 공간은 보통 테이블을 저장하는데 필요한 디스크 공간보다 작다.

왜냐하면 보통 인덱스는 KEY-ROWID만 가지고 있고, 테이블의 세부항목을 갖고 있지 않기 때문이다.

---

인덱스(INDEX) 작동 원리

SELECT * FROM EMP WHERE empno=8888;

데이터 파일의 블록이 10만개 일 때, 위 SQL문의 수행 시 

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

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

이 때 두가지 경우로 나눌 수 있는데,

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

DML이 일어 났을 때 
1. INSERT
 - 기존 Block에 여유가 없을 때, 새로운 Data가 입력된다.
 - 새로운 Block을 할당 받은 후, Key를 옮기는 작업을 수행한다.
 - 인덱스 Split 작업 동안, 해당 Block의 Key 값에 대해서 DML이 블로킹 된다.(대기 이벤트 발생)

2. DELETE
<테이블과 인덱스 상황 비교>
 - 테이블에서 데이터가 DELETE 되는 경우 : 데이터가 지워지고 다른 데이터가 그 공간을 사용가능하다.
 - 인덱스에서 데이터가 DELETE 되는 경우 : 데이터가 지워지지 않고 사용 안됨 표시만 해둔다.
   (테이블의 데이터수와 인덱스의 데이터수가 다를 수 있다.)

3. UPDATE
 - 테이블에서 UPDATE가 발생하면 인덱스는 UPDATE 할 수 없다.
 - 인덱스에서는 DELETE가 발생한 후, 새로운 작업의 INSERT 작업 / 2배의 작업이 소요되어 힘들다.

---

인덱스(INDEX)의 종류

Clustered 인덱스
Clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력 시 Clustered 인덱스를 기준으로 입력된다.
따라서 한 테이블에 오직 하나만 존재할 수 있으며 테이블을 열었을 때 Order By를 사용하지 않아도 데이터가
Clustered인덱스에 따라 정렬이 되어 있는 것을 확인 할 수 있다.

물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다.

NonClustered 인덱스
NonClustered 인덱스는 Clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러개를 생성 할 수 있다.
자동 정렬되지 않고, 인덱스를 생성 할 때는 Clustered가 되어 있을 때, INDEX SCAN이 유리하다.

---

인덱스(INDEX)의 장/단점

장점
 - 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
 - 질의나 보고서에서 그룹화 작업의 속도를 향상시킨다.
 - 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.
 - 테이블의 기본 키는 자동으로 인덱스가 된다.

단점
 - 인덱스 생성시 .mdb 파일 크기가 증가한다.
 - 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
 - 인덱스 된 Field에서 Data를 업데이트하거나, Record를 추가 또는 삭제시 성능이 떨어진다.
 - 데이터 변경 작업이 자주 일어나는 경우, 인덱스를 재작성해야 하므로 성능에 영향을 미친다.
 - 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
 - 인덱스가 데이터베이스 공간을 차지해 추가적인 공간이 필요해진다. DB의 10% 내외의 공간이 추가로 필요

인덱스(INDEX)를 남발하면 안되는 이유
데이터베이스 서버에 성능문제가 발생하면 가장 빨리 생각할 수 있는 해결책이 인덱스 추가 생성이다.
문제가 발생할 때 마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래한다.

조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 INSERT, DELETE, UPDATE 시 부하가 발생해 전체적인 데이터베이스 성능을 저하시킨다.

그렇기에 인덱스를 생성하는 것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야한다.