인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다.
각 인덱스의 특성과 차이는 상당히 중요하며, 물리 수준의 모델링을 할 때도 중요한 요소가 될 것이다.
▶ 디스크 읽기 방식
인덱스를 시작하기 전 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건이냐이기 때문에 사전 지식으로 "랜덤(Random) I/O", "순차(Sequential) I/O" 방식에 대해 이해해야 한다.
▶ 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)
컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만 하드 디스크 드라이브는 기계식 장치다. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다. 물리적 방식으로 데이터를 읽고 쓰는 HDD보다 플래시 메모리를 이용한 SSD가 훨씬 빠른 것은 익히 알고 있다. 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있다.
▶ 랜덤 I/O와 순차 I/O
랜덤 I/O라는 표현은 하드 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 사실 순차 I/O 또한 이 작업 과정은 같다.
디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다.
일반적으로 쿼리를 튜닝한다는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다. 랜덤 I/O를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
▶ 인덱스(Index)란?
- DBMS에서 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다. 그래서 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-value pair)으로 삼아 인덱스를 만들어둔다.
- DBMS 인덱스의 가장 중요한 것은 정렬이다. 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
- DBMS에서 인덱스는 데이터의 저장(Insert, Update, Delete) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.
- 인덱스는 데이터를 관리하는 방식(알고리즘)과 중복 값의 허용 여부 등에 따라 여러 가지로 나눠볼 수 있다.
- 프라이머리 키는 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스를 의미한다. 이 칼럼은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 이를 식별자라고도 부른다. 프라이머리 키는 NULL값을 허용하지 않으며 중복을 허용하지 않는다.
- 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스(Secondary Index)로 분류한다. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수도 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고 그냥 세컨더리 인덳로 분류하기도 한다.
▶ B-Tree 알고리즘 vs Hash 인덱스 알고리즘
- B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로서, 상당히 오래 전에 도입된 알고리즘이며 그만큼 성숙해진 상태다. B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
- Hash 인덱스 알고리즘은 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
▶ B-Tree 인덱스
- B-Tree는 칼럼의 원래 값을 변형시키지 않고 (물론 값의 앞부분만 잘라서 관리하기는 한다) 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.
◎ 구조 및 특성
- 트리 자료구조와 비슷한 형태를 가지며 루트(Root) 노드 - 브랜치 노드(Branch node) - 리프 노드(Leaf node)형태이다.
- 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
- 위 그림과 같이 인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다.
- 첫 번째 그림은 MyISAM 테이블의 인덱스와 데이터 파일의 관계를 보여주는데, "레코드 주소"는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT된 순번이거나 데이터 파일 내의 위치(Offset)다.
MyISAM 스토리지 엔진에서 인덱스의 구조는 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용된다. 즉 MyISAM 테이블에 레코드는 프라이머리 키 값과 무관하게 INSERT되는 순서대로 데이터 파일에 저장된다. 그리고 MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.
- 두 번째 그림은 InnoDB 테이블의 인덱스의 데이터 파일의 관계를 보여주는데, InnoDB 스토리지 엔진을 사용하는 테이블에서는 프라이머리 키가 ROWID의 역할을 한다.
→ 두 스토리지 엔진의 인덱스에서 가장 큰 차이점은 세컨더리 인덱스를 통해 데이터 파일의 레코드를 찾아가는 방법이다.
1) MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가진다.
2) InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다.
◎ 인덱스 키 검색
- 트리 구조이기 때문에 트리 탐색 과정으로 진행된다.
- B-Tree 인데스를 이용한 검색은 100% 일치 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있다.
- 부등호("<, >") 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
- 또한 인덱스를 이용한 검색에서 중요한 사실은 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.
- InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 기락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다.
◎ 인덱스 키 값 크기
- InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.
- 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.
- 인덱스도 결국은 페이지 단위로 관리되며, 앞에서 설명한 "루트-브랜치-리프" 구분 기준도 페이지 단위다.
- B-Tree는 자식 노드의 개수가 가변적인 구조다. 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
- 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.
- 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다.
◎ 인덱스를 사용하는 기준 (=읽어야 하는 레코드의 건수)
- 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.
- 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.
- 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.
ex) 전체 100만 건의 레코드 가운데 50만 건을 읽어야 하는 작업은 인덱스의 손익 분기점인 20~25%보다 훨씬 크기 때문에 MySQL 옵티마이저는 인덱스를 이용하지 않고 직접 테이블을 처음부터 끝까지 읽어서 처리할 것이다.
'CS > MySQL' 카테고리의 다른 글
| MySQL 옵티마이저란? MySQL 기본 데이터 처리 [ORDER BY - 소트 버퍼, 정렬 방식] (0) | 2023.07.12 |
|---|---|
| MySQL B-Tree 인덱스 [2/2] (0) | 2023.07.06 |
| MySQL 데이터 압축 (페이지 압축, 테이블 압축) (0) | 2023.07.03 |
| MySQL InnoDB 스토리지 엔진 잠금과 MySQL 격리 수준 (0) | 2023.07.02 |
| MySQL 트랜잭션이란? MySQL 잠금(Lock) (0) | 2023.07.01 |