인덱스을 알기 위해서는 우선 랜덤 I/O 와 순차 I/O 에 대해서 알아야 한다.
순차 I/O, 랜덤 I/O
순차I/O 는 데이터를 순서대로 연속적으로 읽거나 쓰는 것을 의미합니다.
예를 들어, 파일을 처음부터 끝까지 순차적으로 읽거나 파일의 끝에 데이터를 추가하는것이 순차 I/O 의 한 예입니다.
데이터를 순차적으로 읽으므로, 디스크 액세스 및 디스크 헤더 이동과 같은 오버헤드가 줄어듭니다.
랜덤I/O 는 데이터를 순차적으로 읽거나 쓰는 대신, 임의의 위치에 있는 데이터를 읽거나 쓰는 것을 의미합니다.
예를 들어, 파일 내의 특정 블록 또는 섹터를 랜덤하게 접근한거나 변경하는 것이 랜덤I/O 의 한 예입니다.
랜덤I/O 는 데이터 스토리지 장체이 부하를 많이 줄 수 있으며, 작업량이 큰 경우에는 더 많은 디스크 액세스 및 디스크 헤더 이동을 필요로 합니다.
인덱스(index)
인덱스란 추가적인 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다. 만약에 우리가 책에서 어떤 내용을 찾는다고 할때, 책의 모든 페이지를 찾아보게된다면 오랜시간이 걸리게 됩니다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스이 인덱스는 책의 색인과 같습니다.
데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있습니다.
인덱스(index)가 동작하는 과정
인덱스가 생성되었다면, SELECT 쿼리문에서 'Index 생성 컬럼을 WHERE 조건으로 걸거나', 'Index 컬럼으로 OrderBy에 의한 Sort를 적용하는' 등의 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 적용하여 SELECT문이 실행됩니다.
옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진입니다.
예시를 보면 Query문을 통해 TABLE에 'Name이 HOLLY인 데이터를 조회'한다고 했을 때, Name 컬럼을 인덱싱 해놓은 상태라면 해당 Index를 통해 Name을 먼저 조회하고 Name의 Location 값을 통해 실제 TABLE에 있는 값들을 조회하기 때문에 데이터 전체를 비교할 필요가 없게 됩니다.
일반적으로 B-Tree 자료 구조를 사용하여 검색해야 할 값을 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어갑니다. 그리고 리프 노드의 레코드 주소를 사용하여 실제 데이터 파일에 존재하는 레코드를 읽습니다.
효과적인 인덱스 설정
한테이블에스 인덱스를 많은 컬럼에 설정하게 되면, 데이터베이스에 할당된 메모리를 많이 사용하게 되므로, 적절한 인덱스의 설정이 필요하다. 다음 4가지 기준을 사용하여 기준에 부합하는 컬럼을 인덱스로 설정하는 것이 좋다.
1 .카디널리티(Cardinality)
카디널리티가 높으면(↑) 인덱스 설정에 좋은 컬럼이다. (인덱스를 통해 불필요한 데이터의 대부분을 걸러낼 수 있음)
카디널리티가 높다 = 한 컬럼이 갖고 있는 값의 중복도가 낮음. (= 값들이 대부분 다른 값을 가짐)
2 .선택도(Selectivity)
선택도가 낮으면(↓) 인덱스 설정에 좋은 컬럼이다. (일반적으로 5~10%가 적당함.)
선택도가 낮다 = 한 컬럼이 갖고 있는 값 하나로 적은 row가 찾아진다
3 .조회 활용도
조회 활용도가 높으면(↑) 인덱스 설정에 좋은 컬럼이다.
해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값.
(WHERE의 대상 컬럼으로 많이 활용되는지로 판단하면 된다)
4 .수정 빈도
수정 빈도가 낮으면(↓) 인덱스 설정에 좋은 컬럼이다.
인덱스도 테이블이기 때문에, 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블도 새롭게 갱신되어야 하기 때문.
인덱스(index)를 많이 설정하면 안되는 이유
- 인덱스 설정 시, 데이터베이스에 할당된 메모리를 사용하여 테이블 형태로 저장되게 됩니다.
(즉, 인덱스가 많아지면 데이터베이스의 메모리를 많이 잡아먹게 됩니다.) - 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블이 갱신되어야 하므로 느려질 수 있습니다.
커버링 인덱스(Covering index)
커버링 인덱스란, 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 말합니다.
즉, SELECT, WHERE, ORDER BY, LIMIT, GROUP BY 등에서 사용되는 모든 컬럼이 인덱스 컬럼 안에 다 포함되는 경우입니다.
다중 컬럼 인덱스(Multi-column index, 복합 인덱스)
두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스라고 한다.
데이터를 조회할 때 단일 인덱스를 여러 개를 사용해야 하는 경우가 많다면 다중 컬럼 인덱스를 고려해 본다. 예를 들어 A, B 컬럼을 바탕으로 데이터 탐색을 자주 할 경우 A, B에 단일 인덱스에 걸려 있는 상태에서 조회하는 성능보다 A, B에 다중 컬럼 인덱스가 걸려 있는 경우에 데이터 액세스가 줄어들어 성능이 더 좋기 때문이다.
B-Tree 인덱스, B+Tree 인덱스
B-Tree 는 트리의 노드가 한 방향으로 쏠리지 않게 재정렬을 통해, 각 노드 수의 밸런스를 유지하는 트리 형태의 자료구조입니다.
B-Tree는 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘입니다.
B는 Binary(이진)이 아니라, Balanced(균형 잡힌)의 약자입니다.
노드가 균형 있게 구성되어 있어서, 최악의 경우이더라도 일관된 탐색 시간(O(logN))을 갖습니다.
B+Tree 는 B-Tree 의 확장 개념으로, B-tree 의 경우, 노드에 key 와 data를 담을 수 있습니다. 하지만, B+Tree의 경우 브랜치 노드에 key 만 담아두고, data는 담지 않습니다. 오직 리프 로드에만 key 와 data를 저장하고, 리프노드끼리는 linked list로 연결합니다.
B+tree의 장점
1. 리프 노드를 제외하고 데이터를 담아두지 않기 때문에 메모리를 더 확보함으로써 더 많은 key들을 수용할 수 있다. 하나의 노드에 더 많은 key들을 담을 수 있기에 트리의 높이는 더 낮아집니다.(cache hit를 높일 수 있음)
2. 풀 스캔 시, B+tree는 리프 노드에 데이터가 모두 있기 때문에 한 번의 선형탐색만 하면 되기 때문에 B-tree에 비해 빠릅니다. B-tree의 경우에는 모든 노드를 확인해야 합니다.
Hash 인덱스
hash index란 데이터의 위치를 hashing을 통해 index를 저장하는 방식을 말합니다.
hashing이란 특정한 hash function를 정의하여 이를 통해 key 값을 일정한 범위의 수로 변환하는 작업입닏다.
따라서 hash function에 key 값을 통과시키면 바로 index의 위치를 얻을 수 있기 때문에 별도의 공간이 필요하지 않습니다.
클러스터링 인덱스
테이블의 PK에 대해 적용되는 인덱스입니다.
PK 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현합니다.
PK 값에 의해 해당 레코드의 물리적인 저장 위치가 결정되고, 항상 PK 값을 기준으로 정렬 상태를 유지하며, 이 특징으로 인해 물리적으로 행이 재배열됩니다.
