인덱스 적용기 (개념편)

인덱스란

인덱스의 사전적 의미는 색인이라는 뜻으로 쉽게 비유해서 책의 마지막의 찾아보기를 생각하면 쉬울 것 같다.

DBMS의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸리기 때문에 칼럼의 값과 해당 레코드가 저장된 주소를 key, value 한 쌍으로 삼아 인덱스를 만들어 두는 것이다.

인덱스는 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.

인덱스는 데이터의 저장(Insert, Update, Delete) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.

B-Tree 인덱스 (Balanced Tree)

B-Tree는 트리 구조의 최상위에 하나의 루트 노드 가 존재하고 그 하위에 자식 노드가 붙어 있는 형태 가장 하위에 있는 노드를 리프 노드라고 하고, 맨 위 맨 끝도 아닌 중간에 있는 노드를 브랜치 노드라고 한다.

• MyISAM의 인덱스

리프 노드에서 인덱스 키의 직접적인 레코드 주소를 가지고 있다.

• InnoDB의 인덱스

InnoDB의 인덱스에서는 리프 노드에서 직접적인 레코드 주소를 가지고 있는 것이 아니라 프라이머리키를 가지고 있다.

위 그림과 같이 세컨더리 인덱스의 탐색이 끝난 후 프라이머리 키 인덱스를 한번 더 검색하여 레코드를 읽는다. 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 레코드를 읽기 위해서는 프라이머리 키 인덱스를 한번더 검색해야한다.

• B-Tree 인덱스의 키 추가

저장될 키 값을 이용해 B-Tree 상의 적절한 위치를 검색한 후, 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.

• B-Tree 인덱스 키 삭제

B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료된다.

• B-Tree 인덱스 키 변경

B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.

• B-Tree 인덱스 키 검색

where 조건을 사용하여 검색할 때 인덱스가 적용된다.

update나 delete를 처리할 때도 해당 레코드를 먼저 검색해야할 경우에도 사용된다.

인덱스 레인지 스캔

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.

리프노드까지 탐색한 후 시작해야 될 위치를 찾으면 리프 노드의 레코드만 순서대로 읽으면 된다. 그리고 스캔을 멈춰야 하는 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다.

인덱스 풀 스캔

인덱스의 처음부터 끝가지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 보통 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.

인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드의 처음부터 끝까지 스캔하는 방식이다. 인덱스의 크기가 테이블의 크기보다 작으므로 더 효율적이고, 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우에 사용된다.

루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하게 동작하나, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GRROUP BY 또는 MAX, MIN 함수에 대해 최적화를 하는 경우에 사용된다.

다중 칼럼 인덱스

두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스라고 한다.

인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬되어 있다. 쉽게 말해, 두번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다는 것이다.

클러스터링 인덱스 (클러스터링 테이블)

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되며, 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 한다.

프라이머리 키 값에 의해 레코드의 저장 위치가 결정되기 때문에, 프라이머리 키 값으로 클러스터링 된 테이블은 신중히 프라이머리 키를 결정해야한다.

위 그림을 보면, 클러스터링 테이블의 구조는 세컨더리 인덱스의 리프 노드와 달리 레코드의 모든 칼럼이 같이 저장돼 있다. 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리된다.

프라이머리 키가 없는 InnoDB 테이블은 다음 우선순위대로 프라이머리 키를 대체할 칼럼을 선택한다.

• 프라이머리 키를 클러스터링 키로 선택
• Not NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
• 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가 후, 클러스터링 키로 선택

세컨더리 인덱스에 미치는 영향

세컨더리 인덱스가 프라이머리키가 아닌 실제 레코드가 저장된 주소를 가지고 있다면 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되게 되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 되므로 비용이 많이 들게 된다.

참고

Real MySQL 8.0