[개발지식] 데이터베이스 인덱스(INDEX)

인덱스(INDEX)

데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조

특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터 물리적 주소와 같이 저장된다.

 

인덱스(INDEX)를 사용하는 이유

조건 검색 WHERE 절의 효율성

테이블 안에 데이터들이 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 뒤죽박죽 저장이 된다. 이렇게 되면 WHERE절의 특정 조건에 맞는 데이터들을 찾아낼때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 한다. 이것을 Full Table Scan이라고 한다. 하지만 인덱스 테이블은 데이터들이 정렬되어 저장이 되어 있어서 해당 조건(WHERE)에 맞는 데이터들을 빠르게 찾아낼 수 있다. 이것이 인덱스(INDEX)를 사용하는 큰 이유다.

정렬 ORDER BY 절의 효율성

인덱스(INDEX)를 사용하면 ORDER BY에 의한 정렬(sort)과정을 피할수 없다. ORDER BY는 굉장히 부하가 많이 걸리는 작업이다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고, 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생된다. 하지만 인덱스(INDEX)를 사용하게 되면 이러한 전반적인 자원의 소모를 하지 않아도 된다. 그 이유는 이미 정렬이 되어있어서 가져오기만 하면 되기 때문이다.

MIN,MAX의 효율적인 처리가 가능하다.

이것 또한 데이터가 정렬되어 있어서 얻을 수 있는 장점이다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 Full Table Scan으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있다.

 

인덱스(INDEX) 단점

인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지를 시켜줘야한다는 단점이 있다. 그렇기 때문에 레코드 내에 데이터값이 바뀌는 부분이라면 악영향을 미친다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 인덱스(INDEX) 테이블 내에 있는 값들을 재정렬을 해야한다. 또한 인덱스(INDEX) 테이블, 원본 테이블 이렇게 두 곳에 데이터 수정 작업을 해야한다는 단점도 있다.

 

인덱스(INDEX) 관리

인덱스(INDEX)는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색을 할 수 있다. 그렇기에 인덱스(INDEX)가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야하고 그에 따른 부하가 발생한다. 이런 부하를 최소화하기 위해서 인덱스(INDEX)는 데이터 삭제라는 개념에서 인덱스(INDEX)를 사용하지 않는다라는 작업으로 이를 대신한다.

• INSERT: 새로운 데이터에 대한 인덱스를 추가한다.
• UPDATE: 기존의 인덱스를 사용하지 않음 처리를 하고, 갱신된 데이터에 대해 인덱스를 추가한다.
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.

 

인덱스(INDEX) 생성 전략

생성된 인덱스(INDEX)를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스(INDEX)로 생성하는 것이 좋다. 인덱스(INDEX)는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스(INDEX)테이블이 생성된다. 이 기준 컬럼은 최대한 중복이 되지 않는 것이 좋다. 가장 최선의 PK로 인덱스(INDEX)를 거는 것이다. 중복 된 값이 없는 인덱스(INDEX) 테이블이 최적의 효울을 발생시키고, 반대로 모든 값이 같은 컬럼이 인덱스(INDEX) 컬럼이 된다면 인덱스(INDEX)로써의 가지치가 없다고 봐야한다.

1. 조건절에 자주 등장하는 컬럼
2. 항상 =로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은 컬럼)
4. ORDER BY 절에서 자주 사용되는 컬럼
5. JOIN 조건으로 자주 사용되는 컬럼

 

출처: https://coding-factory.tistory.com/746