[정리] DBMS 인덱스 기본
서론
Disk 종류, 읽기 방식이 DBMS 성능에 영향을 끼치는 것과, DBMS 인덱스의 핵심 개념을 정리해본다.
디스크 읽기 방식
Disk의 읽기 방식은 Random I/O, Sequential I/O 로 나뉠 수 있다. 데이터에비스의 성능 튜닝은 이 디스크 I/O를 얼마나 줄일수 있느냐가 관건이다.
HDD, SDD
HDD는 기계식, SDD는 전자식으로 작동하는 디스크다.
SSD는 데이터 저장용 플래터(원판)을 제거한 대신 플래시 메모리를 장착했다. D-Ram 보다는 느려도 HDD보다는 빠르다.
단, 디스크 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 Sequential I/O에서는 SSD가 HDD보다 조금 빠르거나 비슷한 성능을 보이기도 한다.
그렇지만 SDD의 최대 장점은 Random I/O의 성능의 우위에 있다. SSD는 HDD보다 Random I/O의 속도가 훨씬 우위에 있다.
데이터베이스 서버에서는 Sequential은 별로 비중이 크지 않고 Random I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이라서 데이터베이스용 스토리지로 SSD가 최적으로 평가받는다.
Random I/O, Sequential I/O
Random I/O란 표현은 HDD의 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미한다.
그런데 사실 Sequential I/O도 이 위 작업과정은 똑같이 수행한다.
Sequential I/O는 3개의 페이지(3 * 16kb)를 디스크에 기록하기 위해 1번만 시스템 콜을 한다. 반면, Random I/O는 3번의 시스템 콜이 발생한다.
즉, Sequential I/O는 헤드 1번, Sequential I/O는 헤드 3번이 물리적으로 움직이게 되는것이다.
데이터베이스의 작업은 대부분 작은 데이터들을 빈번히 읽고 쓰기에 MySQL은 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼등의 기능이 내장되있다.
그런데 여기서 드는 의문이 만약 디스크 원판을 가지지 않는 SSD는 Random I/O나 Sequential I/O이나 거기서 거기가 아닐까?
아니다! SSD에서도 Random I/O는 Sequential I/O보다 전체 처리량(throuput)이 떨어진다.
사실 쿼리를 튜닝하여 Random I/O를 Sequential I/O로 바꿔 실행할 수 있는 방법은 그다지 많지 않다.
일반적으로 쿼리를 튜닝하는 것은 Random I/O 그 자체를 줄여주는것이 목적이라 할 수 있다.(Random I/O를 Sequential I/O로 바꾸는게 아닌)
여기서 Random I/O를 줄인다는 의미는 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선한다는 것을 의미한다.
인덱스 레인지 스캔은 데이터 읽기위해 주로 Random I/O, 풀 테이블 스캔은 Sequential I/O. 그래서 큰 테이블 레코드 대부분을 읽는 작업에선 인덱스 사용않고 풀 테이블 스캔 유도할때있다. 이는 Sequential I/O 가 Random I/O 보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문임. 이런 형태는 OLTP(On-Line Transaction Processing) 성격의 웹 서비스보다는 데이터 웨어하우스나 통계작업에서 자주사용됨.
인덱스
보통 인덱스 언급 시 항상 책의 맨 끝에 있는 "찾아보기"로 설명한다.
책의 마지막에 있는 찾아보기가 인덱스에 비유된다면 책의 내용은 데이터 파일일 것이다.
- 책의 찾아보기 == 인덱스
- 책의 내용 == 데이터 파일
- 책의 페이지 번호 == 데이터 파일에 저장된 레코드 주소
DBMS가 테이블의 모든 데이터를 검색해서 결과를 가져오려면 오래걸린다. 그래서 칼럼 값과 해당 레코드가 저장된 주소를 키-값으로 매핑해 인덱스를 두는 것이다.
또한, 책의 찾아보기와 DBMS 인덱스의 공통점 중 중요한것이 정렬이다.
프로그래밍 언어의 자료 구조를 가져와서 인덱스를 설명해보자면, 언어별로 이름이 조금씩 다르겠지만 SortedList, ArrayList 정도는 그냥 이름만 봐도 예상이 될 것이다.
인덱스는 SortedList 같이 저장되는 칼럼 값을 이용해 항상 정렬 상태를 유지하고, 데이터 파일은 ArrrayList 같이 저장 순서대로 별도 정렬 없이 그대로 저장한다.
SortedList 로 인덱스의 장단점을 알아보자.
저장 마다 항상 값을 정렬해야해서 저장 과정이 복잡하고 느리지만, 이미 정렬이 되어있어서 원하는 값을 매우 빨리 찾아 가져올 수 있다.
다만, 인덱스가 매우 많다면 INSERT, UPDATE, DELETE 시 오래 걸지만 이미 정렬된 데이터가 있기에 SELECT 는 빠르다.
정리하자면 INSERT, UPDATE, DELETE 를 희생하고 SELECT 성능을 얻은 것
인덱스를 세팅 할 때는 인덱스 추가 유무, 저장 속도 희생 정도, 읽기 속도가 얼마나 빨라야 하는지 등을 고려해서 결정해야함을 유념하자.
인덱스 역할 별 구분
프라이머리 키(클러스터드 인덱스)와 보조 키(세컨더리 인덱스)로 구분 가능하다.
프라이머리 키는 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스. 이 칼럼(때로는 칼럼 조합)은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기에 우리는 이를 식별자라 부른다.
PK는 NULL을 허용하지 않으며 또한 중복을 허용하지 않는다.
프라이머리 키를 제외한 나머지 모든 인덱스는 보조 키(세컨더리 인덱스)로 분류 가능하다.
유니크 인덱스는 프라이머리 키와 성격이 비슷하도 프라이머리 키를 대체해 사용 할 수 있다해서 대체키 라고도 한다. 별도로 분류하기도 하고 그냥 세컨더리 인덱스라 하기도 한다.
인덱스 알고리즘 별 구분
여러 알고리즘이 있지만 대표적으로 B-Tree, Hash 로 구분 가능.
B-Tree 는 가장 일반적으로 사용되는 방식이다. 오래되었기에 성숙하다. 칼럼의 값을 변형시키지 않고 원래 값을 이용해 인덱싱을 한다.
Hash 인덱스 알고리즘은 칼럼 값으로 해시값을 계산하여 인덱싱한다. 매우 빠른 검색이 지원된다.
하지만 값을 변형해 인덱싱 하므로 전방(prefix) 일치 같이 값의 일부만 검색하거나 범위 검색시에는 해시 인덱스를 사용 불가한 단점이 있다.
Hash 인덱스는 주로 메모리 기반 데이터베이스에서 많이 쓰인다.
인덱스 중복 허용 여부 별 구분
데이터 중복 여부로 분류하면 유니크 인덱스, 유니크하지 않은 인덱스(Non-Unique)로 구분가능하다.
인덱스가 유니크한지 아닌지는 단순히 같은 값 1개만 존재하는지 1개 이상 존재 가능한지 의미하지만, 실제 DBMS의 쿼리를 실행해야하는 옵티마이저에게는 상당히 중요한 문제가 된다.
유니크 인덱스에 대해 동등 조건을 검색하는 경우, 항상 1건의 레코드만 찾으면 된다는걸 옵티마이저에게 알려주는 효과가 있다. 또한 유니크 인덱스로 인해 MySQL 처리 방식의 변화나 차이점이 상당하다.