Database 인덱스(Index)

인덱스(Index)

인덱스란 추가적인 쓰기 작업과 공강을 활용하여 데이터 베이스의 테이블의 검색 속도를 향샹시키기 위한 자료구조 데이터베이스에서 테이블의 모든 데이터를 검색하면 시간이 오래걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회하는 방법이다.

특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터를 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 이렇게 인덱스가 생성되었다면 앞으로 쿼리문에 인덱스 생성 컬럼을 where조건으로 거는 등의 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수 가 있다. 만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작하여 검색속도를 향상할 수 있다. 인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE, DELETE의 성능이 함께 향상된다. 그런 이유는 연산을 수행하려면 해당 대상을 조회해야만 작업 할 수 있다.

옵티마이저 옵티마이저는 가장 효율 적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진 컴퓨터의 두뇌가 CPU인 것 처럼 DBMS의두뇌는 옵티마이저라고 할 수 있다.

// kwak이라는 이름을 업데이트 하기위해 조회하기에 때문
UPDATE USER NAME = "taemin" where NAME = "kwak"

인덱스의 장점과 단점

• 장점
  1. 테이블을 조회하는 속도와 그에 따른 성능 향상
  2. 전반적인 시스템 부하를 줄일 수 있음
  3. 조건 검색 where 절의 효율성
     • 인덱스가 없으면 처음 부터 모든 행을 읽어서 하게되는 Full Table Scan 하게 된다.
     • 인덱스 테이블 스캔(Index Table Scan) 시 인덱스 테이블은 데이터들이 정렬되어 저장되 있기에 해당 조건에 맞는 데이터들을 빠르게 찾아 낼 수 있다.
  4. 정렬 ORDER BY 절의 효율성
     • 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크I/O도 추가적으로 발생된다.
     • 인덱스를 사용하게 되면 전반적인 자원의 소모를 줄일 수 있다.
  5. MIN,MAX의 효율 적인 처리가 가능
     • 데이터가 정렬되어 있기에 얻을수 있는 장점
• 단점
  1. 인덱스를 관리하기 위해 DB의 약 10% 해당하는 저장공간 필요하다.
  2. 인덱스를 관리하기 위해 추가 작업이 필요
  3. 인덱스를 잘 못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
  4. DML에 취약
     • INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 인덱스 테이블 내에 있는 값들을 다시 정렬해야 한다.
  5. 무조건 인덱스 스캔이 좋은것은 아니다.
     • 검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만 무조건 검색 시에도 인덱스가 좋은 것으 ㄴ아니다.
     • 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫다.
     • 직관적인 예시를 들자면 1개의 데이터가 있는 테이블과 100만 개의 데이터가 들어 있는 테이블이 있다고 하자, 100만 개의 데이터가 들어있는 테이블이라면 Full Scan보다는 Index Scan이 유리하겠지만, 1개 데이터가 들어있는 테이블은 굳이 인덱스 스캔 없이 풀 스캔이 빠를 것이다.
  6. 속도 향상을 위해 인덱스를 많이 만드는 것은 좋지 않다.
     • 인덱스를 관리하기 위해서는 데이터 베이스의 약 10% 해당하는 저장공간이 추가로 필요하다.
     • 무턱대고 인덱스를 많이 만들어서는 결코 안된다.
     • 즉, 속도 향상에 비해 단점들의 COST랑 비교해서 인덱스를 만들지 말지를 정해야 한다.

인덱스를 남발하지 말아야 하는 이유 데이터 베이스 서버에 성능 문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성이다. 문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만, 전체적인 데이터베이스의 성능 부하를 초래한다. 그렇기에 인덱스를 생성하는 것보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야 한다. 인덱스 생성은 마지막 수단으로 강구해야 할 문제이다.

인덱스의 관리

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

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

인덱스 생성 전략

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

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

인덱스를 사용하면 좋은 경우

• 규모가 작지 않는 테이블
• INSERT, UPDATE, DELETE 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼

인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요하다. 그러므로 사용되지 않는 인덱스는 바로 제거해야 한다.

인덱스의 자료구조

1. 헤시 테이블 해시 테이블은 (key, value)로 데이터를 저장하는 자료구조 중 하나로 빠른 데이터 검색이 필요할 때 유용하다. 해시 테이블은 key 값을 이용해 고유한 index를 생성하여 그 index에 저장된 값을 꺼내오는 구조이다. 해시 테이블 기반의 DB 인덱스는 (key, value)로 사용하여 컬럼의 값으로 생성된 해시를 통해 인덱스를 구현하였다. 해시 테이블의 시간 복잡도는 O(1)이며 매우 빠른 검색을 지원한다.
   하지만, DB인덱스에서 해시 테이블이 사용되는 경우는 제한적인데, 그러한 이유는 해시가 등호(=) 연산에만 특화되어있기 때문이다. 해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생성하는데, 이러한 특성에 의해 부등호 연산(>)이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않다. 즉, 예를 들면 “나는” 으로 시작하는 모든 데이터를 검색하기 위한 쿼리문은 인덱스의 혜택을 전혀 받지 못하게 된다. 이러한 이유로 데이터베이스의 인덱스는 B+Tree가 일반적으로 사용된다.

2. B+Tree B+Tree는 DB 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이다. B+Tree는 모든 노드에 데이터(Value)를 저장했던 BTree와 다른 특성을 가지고 있다.

   • 리프노드(데이터노드)만 인덱스와 함께 데이터(Value)를 가지고 있고, 나머지(인덱스 노드)들은 데이터를 위한 인덱스(key)만을 갖는다.
   • 리프노드들은 LinkedList로 연결되어있다.
   • 데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.

데이터 베이스의 인덱스 컬럼은 부등호를 이용한 순차 검색 연산이 자주 발생될 수 있다. 이러한 이유로 BTree의 리프 노드들을 LinkedList로 연결하여 순차 검색을 용이하게 하는 등 BTree를 인덱스에 맞게 최적화하였다. (물론 Best Case에 대해 리프 노드까지 가지 않아도 탐색할 수 있는 BTree에 비해 무조건 리프노드까지 가야한다는 단점도 있다.) 이러한 이유로 비록 B+Tree는 O(log2N)의 시간 복잡도를 갖지만 해시 테이블보다 인덱싱에 더욱 적합한 자료구조가 되었다.

InnoDB에서의 B+Tree는 일반적인 구조보다 더욱 복잡하게 구현이 되었다. InnoDB에서는 같은 레벨의 노드들끼리는 Linked List가 아닌 Double Linked List로 연결되어있으며, 자식 노드들은 Single Linked List로 연결되어있다.

참조

• [Database] 인덱스(index)란?
• [DB] 데이터베이스(DB) 인덱스(Index) 란 무엇인가?