Real Mysql 8 Index
8.1 디스크 읽기 방식
8.1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)
컴퓨터에서 CPU나 메모리 같은 주요 장치는 전자식이지만 하드 디스크 드라이브는 기계식이라 데이터베이스 서버의 병목이 되곤 한다.
이러한 하드 디스크를 대체하기 위한 전자식 저장 매체인 SSD(Solid State Drive)는 하드 디스크에서 데이터 저장용 플래터(원판)를 제거하고 그 대신 플래시 메모리를 장착한다.
디스크 원판을 기계적으로 회전시킬 필요가 없으므로 빠르게 읽고 쓸 수 있다.
디스크 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차(Sequential) I/O는 SSD가 HDD보다 조금 빠르거나 거의 비슷한 수준이지만 랜덤 I/O는 훨씬 빠르다.
데이터베이스 서버에서는 순차 I/O보다 랜덤 I/O 작업이 더 많다.
8.1.2 랜덤 I/O와 순차 I/O
3개의 페이지를 디스크에 기록하려면 순차 I/O는 1번 시스템 콜을 요청하지만 랜덤 I/O는 3번 요청해 디스크 헤드를 3번 이동해야 한다.
여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 부하가 더 큰 이유다.
랜덤 I/O나 순차 I/O 모두 파일에 쓰기를 실행하면 동기화(fsync 또는 flush)가 필요하다.
그런데 순차 I/O의 경우에도 동기화 작업이 빈번히 발생하면 비효율적인 형태로 처리될 때가 많다.
기업용으로 사용하는 데이터베이스 서버에는 캐시 메모리가 장착된 RAID 컨트롤러가 보통 사용된다. 이는 빈번한 동기화 작업이 호출되는 순차 I/O를 효율적 처리로 변환하는 역할을 한다.
HDD뿐만 아니라 SDD에도 여전히 RAID 컨트롤러는 중요하게 작동한다.
인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며 풀 테이블 스캔은 순차 I/O를 사용한다.
그래서 큰 테이블 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 많은 레코드를 읽어올 수 있기 때문인데 이런 형태는 OLTP(On-Line Transaction Processing) 성격의 웹 서비스보다 데이터 웨어하우스나 통계 작업에서 자주 사용된다.
8.2 인덱스란?
DBMS의 중요한 점은 정렬이다. DBMS의 인덱서는 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다. 데이터 파일은 ArrayList와 같이 별도 정렬 없이 저장한다.
SortedList 자료 구조는 데이터가 저장될 때마다 값을 정렬해야 하므로 저장과정이 느리지만 이미 정렬돼 있어서 빨리 원하는 값을 찾는다.
DBMS의 인덱스도 인덱스가 많은 테이블은 INSERT, UPDATE, DELTE 처리가 느려진다. 그러나 이미 정렬된 "찾아보기"용 표(인덱스)를 가지고 있기 때문에 SELECT 문장은 빠르게 처리한다.
인덱스(Key)를 역할별로 나눈다면 프라이머리 키(Primary key)와 보조 키(세컨더리 인덱스, Secondary key)로 구분할 수 있다.
- 프라이머리 키는 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스다. NULL 값과 중복을 허용하지 않는다.
- 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스다.
데이터 저장 방식(알고리즘)별로 나눈다면 B-Tree 인덱스와 Hash로 구분할 수 있다.
- B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
- Hash 인덱스 알고리즘은 칼럼의 값으로 해시값을 계산해 인덱싱하는 알고리즘으로 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 전방 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다. Hash 인덱스는 주로 메모리 기반 데이터베이스에서 많이 사용한다.
B-Tree 인덱스
B-Tree의 B는 Balanced를 의미한다. 이는 원래 값을 변형시키지 않고(앞부분만 잘라서 관리하지만) 인덱스 구조체 내에서는 항상 정렬된 상태를 유지한다. 전문 검색 등 요건이 아닌 이상 대부분 인덱스는 거의 B-Tree를 사용한다.
구조 및 특성
B-Tree는 트리 구조의 최상위에 하나의 루트 노드가 있고 트리 중간을 브랜치 노드 가장 하위를 리프 노드(Leaf node)라 한다.
데이터베이스에서 인덱스와 실제 저장된 데이터는 따로 관리되는데 인덱스의 리프 노드는 실제 데이터 레코드를 찾아가기 위한 주솟값을 지니고 있다.
인덱스의 키 값은 모두 정렬돼 있지만 데이터 파일의 레코드는 임의의 순서로 저장돼 있다. 레코드가 삭제돼 빈 공간이 생기면 INSERT는 가능한 한 삭제된 공간을 재활용하도록 설계되기 때문이다.
그림 8.5는 MyISAM 테이블의 인덱스와 데이터 파일의 관계를 보여주는데 레코드 주소는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT된 순번이거나 데이터 파일 내의 위치(Offset)다.
그림 8.6은 InnoDB 테이블의 인덱스와 데이터 파일의 관계를 보여주는데 InnoDB 스토리지 엔진을 사용하는 테이블에서는 프라이머리 키가 ROWID 역할을 한다.
두 스토리지 엔진의 차이점은 MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지는 반면 InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.