인덱스

인덱스 

- 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스로 만들어 둠

- 칼럼의 값을 주어진 순서로 미리 정렬해서 보관함

- 정렬의 과정을 거치기 때문에, 인덱스가 많은 테이블은 insert, update, delete 문장의 처리가 느려짐.

단, select문장은 매우 빠르게 처리할 수 있다

- 인덱스의 종류를 역할 별로 구분하면,

PK (Primary Key) 와 보조 키 (Secondary Index)로 구분할 수 있음

-> PK : 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스. 

-> PK를 제외한 나머지 모든 인덱스는 세컨더리 인덱스라고도 분류

 

- 데이터 저장방식 별로 구분하면, B-Tree인덱스와 Hash 인덱스가 있다.

- B-Tree 인덱스 : 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘

- Hash 인덱스 : 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘, 매우 빠른 검색을 지원함. => 주로 메모리 기반 디비에서 많이 사용함

 

- 중복 허용 여부로 분류하면, 유니크 인덱스와, 유니크하지 않은 인덱스로 구분할 수 있음

=> 실제 DBMS의 쿼리를 실행해야하는 옵티마이저에게 상당히 중요한 역할 수행

 

B-Tree 인덱스

- 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용되는 알고리즘

- 주로 B+Tree알고리즘, B-Tree알고리즘이 사용됨

-  B-Tree는 칼럼의 원래 값을 변형시키지 않고, 항상 정렬된 상태로 유지함

B-Tree 인덱스의 구조

- 트리 구조의 최상위에 하나의 루트 노드가 존재하고, 그 하위에 자식 노드가 붙어있는 형태

- 리프노드 : 트리 구조의 가장 하위에 있는 노드 => 실제 데이터 레코드를 찾아가기 위한 주값을 가지고 있음.

- 브랜치 노드 : 리프노드도 아니고, 루트노드도 아닌 중간 노드

 

B-Tree 인덱스 키 추가, 변경, 삭제

- 새로운 키 값이 B-Tree에 저장될 때, 레코드 키 값과 주소 정보가 리프노드에 저장된다

- 리프노드가 꽉 차면, 리프 노드가 분리되어야 하는데, 이는 상위 브랜치 노드까지 처리 범위가 넒어져서 이러한 작업때문에 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 든다고 한다. 

- 삭제의 경우, 해당 키 값이 저장된 비트리의 리프노드를 찾아서, 삭제 마크만 하면 작업이 완료된다. 

- 인덱스의 키 값 변경은, 그 값에 따라 저장될 리프노드 위치가 결정되므로ㅡ 먼저 키 값을 삭제후, 다시 새로운 키 값을 추가하는 형태로 처리된다.

 

B-Tree 인덱스를 통한 데이터 읽기

인덱스 레인지 스캔

- 인덱스 레인지 스캔 : 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식. 루트노드에서부터 비교를 시작해, 브랜치 노드를 거치고, 최종적으로 리프 노드까지 찾아들어가서 쭉 읽는 스캔방식 취함

=> 이때, 리프노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어오는데 이때 레코드 한 건 단위로 랜덤 I/O가 일어나서 비용이 많이 든다.

=> 정리하자면, 다음과 같은 절차이다.

 

1. 인덱스 탐색 : 인덱스에서 조건을 만족하는 값이 저장되는 위치를 찾음.
2. 인덱스 스캔 : 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 쭉 읽는다. 
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽는다

 

 

SHOW STATUS LIKE 'Handler_%';

-> mysql서버에서는 인덱스 탐색과 인덱스 스캔이 얼마나 수행되었는지를 확인할 수 있는 상태를 제공함. 

위의 명령어를 실행시켜서,

Handler_read_key 로 1번단계가 실행된 횟수,

Handler_read_next, Handler_read_prev 로 2번 단계로 읽은 레코드 건수를 찾아올 수 있다.

 

인덱스 풀 스캔

- 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모두 읽는 방식

- 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우, 인덱스 풀 스캔이 사용됨

- 예컨대, 인덱스는 (A, B, C) 컬럼의 순서이지만, 쿼리의 조건절을 B컬럼이나 C컬럼으로 검색하는 경우이다.

- 인덱스의 전체 크기는 테이블 자체의 크기보다 훨씬 작으므로, 인덱스 풀 스캔은 테이블 전체를 읽는 것보다 적은 디스크 I/O를 사용한다.

 

루스 인덱스 스캔

-  루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.

- 일반적으로 GROUP BY 혹은 집합 합수 가운데 MAX()나 MIN()함수에 대해 최적화를 하는 경우 사용된다,

 

다중 컬럼 인덱스(복합 인덱스)

- 두 개 이상의 컬럼으로 구성된 인덱스를 복합 인덱스라고 하며, i+1번째 인덱스는 i번째 인덱스에 의존해서 정렬된다.

- 따라서 인덱스 내에서 각 칼럼의 순서가 중요해지며, 이를 신중히 결정해야한다.

 

+) 그 외에도 가상 컬럼 인덱스, 멀티 밸류 인덱스 (json값 기반)을 지원한다. 

B-Tree 인덱스의 정렬 및 스캔 방향

- MySQL8.0부터 혼합해서 인덱스 정렬이 가능해짐.

- 인덱스 생성 시점에서 오름차순, 내림차순으로 정렬이 결정되지만, 사용한느 시점에서는 인덱스를 읽는 방향에 따라 오름차순, 내림차순 정렬효과를 얻을 수 있다.

- MySQL 옵티마이저는 인덱스의 읽기방향을 전환해서 사용하도록 실행계획을 만들어낸다.

 

가용성과 효율성 판단

B-Tree인덱스는 다음 조건에서 사용할 수 없다. 

 

- NOT-EQUAL로 비교된 경우 (ex) <>, NOT IN, NOT BETWEEN, IS NOT NULL)

- LIKE '%??' 앞 부분이 아닌 뒷 부분 일치 형태로 문자열 패턴이 비교된 경우

- 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우

- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

- 데이터 타입이 서로 다른 비교일 경우

 

클러스터링 인덱스 

- 테이블의 레코드를 비슷한 것들끼리 묶어서 저장하는 형태

- 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안함

- InnoDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않음 

- 클러스터링 인덱스는 테이블의 PK에 대해서만 적용되는 내용 

- PK값이 비슷한 레코드끼리 묶어서 저장하는 것

- PK값에 의해 레코드 저장 위치가 결정됨. 

 

장점 :

1. PK로 검색할 때 처리 성능이 매우 빠름

2. 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에, 인덱스만으로 처리될 수 있는 경우가 많음. (이를 커버링 인덱스라고도 함) 

 

단점 : 

1. 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에, 클러스터링 키값의 크기가 클 경우 전체적으로 인덱스 크기가 커짐

2. pk를 변경할때 레코드를 delete하고, insert하는 작업이 필요해서 처리 성능이 느림 

3. 세컨더리 인덱스를 통해 검색할 때, PK로 다시 한 번 검색해야함.

 

주의사항 :

1. 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 PK키를 포함하기 때문에, PK의 키가 커지면 세컨더리 인덱스도 자동으로 크기가 커짐

2. PK를 반드시 명시하자.  => PK가 없으면 내부적으로 innodb 테이블상에서 만들어주는데, 이렇게 자동으로 추가된 컬럼은 사용자에게 보이지 않으므로, 하다 못해 auto_increment컬럼을 만들어서라도 PK로 생성해두자.

 

외래키

- InnoDB 스토리지 엔진에서만 생성 가능하며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됨

주요한 특징은 다음과 같다. 

1. 테이블의 변경이 발생하는 경우에만, 잠금 경합이 발생한다. 

2. 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.