RealMySQL - 인덱스

랜덤 I/O와 순차 I/O

• 랜덤 I/O 표현은 하드 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미

• 순차 I/O 또한 작업 과정은 같다.

• 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용, 풀 테이블 스캔은 순차 I/O를 사용한다.

• 순차 I/O가 랜덤 I/O보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문이다.

인덱스란?

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

• 컬럼의 값을 주어진 순서로 미리 정렬해서 보관한다.

• SortedList는 인덱스와 같은 자료 구조, ArrayList는 데이터 파일과 같은 구조

• SortedList는 이미 정렬돼 있어 아주 빨리 원하는 값을 찾아올 수 있다.
  INSERT, UPDATE DELETE 문장의 처리가 느려진다.

• 인덱스를 역할별로 구분하면 프라이머리 키와 보조키로 구분

• 프라이머리 키는 레코드를 식별할 수 있는 기준값, 식별자, NULL 값을 허용하지 않으며 중복을 허용하지 않는다.

• 데이터 저장 방식 별로 구분할 경우 대표적으로 B-Tree 와 Hash로 구분할 수 있다.

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

• Hash는 컬럼의 값으로 해시값을 계산해서 인덱싱, 매우 빠른 검색을 지원한다. 메모리 기반의 데이터베이스에서 많이 사용한다.

B-Tree 인덱스

• 기본적인 구조는 최상위에 하나의 루트 노드가 존재, 그 하위에 자식 노드가 붙어 있는 형태

• 최하위에 있는 노드를 리프 노드, 중간의 노드를 브랜치 노드 라고 한다.

• 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아긱 위한 주솟값을 가지고 있다.

• 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다.

• MyISAM 테이블은 세턴더리 인덱스가 물리적인 주소를 가지는 반면 InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다.

• InnoDB는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Trre를 다시 한번 검색해야 한다.

인덱스 키 추가

• MyISAM이나 MEMORY 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 변경한다.

• InnoDB 스토리지 엔진은 필요하다면 인덱스 키 추가 작업을 지연시켜 나중에 처리 가능

인덱스 키 삭제

• 삭제하려는 키 값이 저장된 리프 노드를 찾아서 삭제 마크만 하면 작업 완료

• 삭제 마킹된 인덱스 키 공간은 그대로 방치 하거나 재활용할 수 있다.

• 마킹 작업 또한 디스크 쓰기가 필요함

인덱스 키 변경

• 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리

인덱스 키 검색

• 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다.

• InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다.

• 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글 수도 있다.

• InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하다.

B-Tree 인덱스 사용에 영향을 미치는 요소

• B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

인덱스 키 값의 크기

• InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.

• 또한 페이지는 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.

• 인덱스도 결국은 페이지 단위로 관리된다.

• 일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조다.

• 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 느려진다.

선택도(기수성)

• 선택도(Selectivity) 또는 기수성(Cadinality)는 거의 같은 의미
  모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.

• 전체 인덱스 키 값은 100개인데, 그중에서 유니크한 값의 수는 10개라면 기수성은 10이다.

• 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다.

• 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

읽어야 하는 레코드의 건수

• 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.

• 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효울적이다.

인덱스 레인지 스캔

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

• 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다

• 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다.

1. 인데스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이과정을 인덱스 탐색 이라고 한다.

2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔 이라고 한다.

3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다.

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.
• 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다.

루스 인덱스 스캔

• 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리

인덱스 스캔 방향

• 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

• 오름차순으로 생성된 인덱스를 정순으로 읽으면 출력되는 결과 레코드는 자동으로 오름차순으로 정렬된 결과가 되고 역순으로 읽으면 그 결과는 내림차순으로 정렬된 상태가 되는 것이다.

내림차순 인덱스

• 역순 정렬 쿼리가 정순 정렬 쿼리보다 더 시간이 걸리는 것을 확인할 수 있다.

• 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수 밖에 없는 두 가지 이유

1. 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
2. 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

비교 조건의 종류와 효율성

• 쿼리 비교 작업의 범위를 좁히는데 도움을 주냐 안주냐에 따라 효율적으로 인덱스를 이용

• 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지는 못한다. 오히려 쿼리 실행을 더 느리게 만들 때가 있다.