인덱스는 언제, 왜, 어떻게 사용해야 좋을까?

언제 : 읽기 작업 속도 향상이 필요할 때

왜 : 인덱스를 통해 비용이 가장 많이 요구되는 디스크 랜덤 I/O를 최대한 줄일 수 있기 때문이다.

 

기본적으로, 인덱스를 통한 접근이 디스크 직접 접근보다 비용이 많이 듦. 일단 한 번 인덱스 탐색을 더 거쳐야하는 것이기 때문에.. 하지만, 데이터가 대규모이고 찾는 레코드의 건수가 전체 레코드의 20% 이하일 경우

• 인덱스를 통한 탐색
  • 인덱스는 정렬 상태이기 때문에, 빠르게 탐색을 완료할 수 있음. -> 인덱스틀 통해 정확한 데이터 위치를 찾았음으로, 디스크 접근 시 한 번의 랜덤 I/O가 추가 발생. 만약, 인덱스에 있는 칼럼만으로도 결과 반환이 가능하면 디스크 접근까지 하지 않아도 된다.
• 직접 디스크 탐색
  
  • 디스크에 있는 실제 데이터는 정렬되어 있지 않다. 따라서 모든 디스크 블록을 탐색해서 위치를 찾아가야 함으로, 최악의 경우에 모든 데이터를 탐색하게 될 수도 있다.
• BUT!!!!! 읽어야 할 레코드 건수가 전체 레코드의 20~25%이상이면 인덱스를 통해 읽는 것보다 직접 읽는게 더 효율적

어떻게 적용?: 읽기 작업이 많이 요구되는 레코드에, 인덱싱 키로 적절한 칼럼을 선택해서 적용한다.

인덱스는 정렬된 상태를 유지한다. 즉, 쓰기 작업(INSERT, UPDATE, DELETE) 빈번하게 발생하는 레코드에 인덱싱을 하면, 인덱스도 다시 정렬해야 하기 때문에, 읽기 작업이 많이 요구되는 상황에, 인덱싱 키로 적절한 칼럼을 선택해서 사용한다. N번째 키 값은 N-1 키 값에 대해 정렬되기 때문이다. 

 

cf) update, delete처리할 때도 레코드를 먼저 'select'하고 작업을 수행하기 때문에 이 경우에도 index는 유용하다.

 

대표적인 인덱스 알고리즘

B-TREE

- 가장 일반적으로 사용되며, 루트-브랜치-리프 노드의 형태를 가지고 있고, 리프 노드에 실제 데이터 주소가 저장된다. 칼럼 값을 변경하지 않고 원래 값을 이용하기 때문에, 키 값을 변형한 형태로 검색을 진행할 때는 효과를 보지 못한다. 또한,칼럼의 앞부분이 부분일치 or 100% 일치해야한다. 

 

cf) 인덱스가 새로 추가 될 때.. leaf 노드 꽉 차 있으면 분기해야하고 이 분기는 상위 브랜치 노드에도 영향을 끼쳐서 쓰기 작업에 비용이 많이 드는 것.

-> InnoDB에서는 이 쓰기 작업을 지연처리할 수 있다.

• 체인지버퍼 : 인덱스 저장을 위한 임시메모리, 여기에 저장하고 사용자에게 결과 먼저 반환한다.
  • !!! 유니크 인덱스일 경우,사용자에게 반환 전에 중복검사를 먼저 해야해서 이 경우에는 지연처리 안 된다.

HASH

- 칼럼 값을 해시값으로 계산해서 인덱싱하며 매우 빠른 검색에 사용된다. 칼럼 값 변형하기 때문에 범위 검색에는 사용하지 못한다. 

 

 

인덱스의 크기

B-TREE의 자식 노드 크기는 인덱스 key 값에 의해 결정된다. 

인덱스 구성하는 key 값이 커지면 -> 자식 노드 개수가 줄어듦 -> 디스크로부터 읽어야 하는 횟수, 메모리에 캐시할 수 있는 레코드 수가 감소하면서 I/O 비용이 증가함

-> 인덱스 key 값을 최대한 작게 가져가는 것이 좋다. 

 

인덱스 사용 경우

1) Range Scan

 

인덱스는 정렬이 되어있음으로, 범위에 대해 스캔할 수 있다

1. index 조건 만족하는 위치 찾음
2. 1에서 탐색한 위치부터 필요한 만큼 범위 스캔
3. 디스크로부터 데이터 읽어옴 (인덱스 칼럼만으로 반환 가능하면 생략)

2) Full Scan

 

쿼리가 인덱스에 명시된 칼럼만으로 처리 가능할 때 사용, 디스크 읽어야 하면 절대 사용 X

쿼리의 where절(조건문)에 명시된 칼럼이 index의 첫번째 칼럼이 아닌 경우에 풀 스캔을 해서 찾아온다.

인덱스의 N번째 칼럼은 N-1번째 칼럼에 대해 정렬되기 때문에, 첫번째 칼럼이 아닌 조건으로 검색하면 정렬된 인덱싱을 사용할 수 없어서 풀 스캔이 발생하는 것.

 

3) Loose Scan

범위 스캔과 풀 스캔은 타이트 스캔이기도 하다.

루스 스캔은 범위 스캔과 비슷하게 작동하는데, 조건이 아닌 인덱스 키 값은 skip 하고 넘어간다. 

Group By, MIN, MAX 사용시에 주로 사용된다.

 

언제 효율적?

• 조건절에 명시 되지 않은 선행 칼럼의 유니크 값 개수가 적을 때
  • 옵티마이저가 선행 인덱스 칼럼에 대해 조건문을 추가해서 쿼리 최적화를 진행하기 때문에, 유니크한 값 개수가 적을수록 빠르게 탐색 가능하다. 
• 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능할 때