[스터디] 12. 인덱스

목차

8. 인덱스

인덱스는 데이터베이스 쿼리의 성능을 언급하면 자주 나오는 부분이다. 이번 장에서는 MySQL에서 사용 가능한 인덱스의 종류 및 특성을 간단히 살펴본다.

8.1 디스크 읽기 방식

컴퓨터의 CPU나 메모리처럼 전기적 특성을 띤 장치의 성능은 짧은 시간 동안 매우 빠른 속도로 발전했지만, 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전했다. 데이터베이스나 쿼리 튜닝 사용자가 절감하고 있듯이 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건일 때가 상당히 많다.

8.1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

• 컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만, 하드 디스크 드라이브는 기계식 장치임. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 됨. 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD(Solid State Drive)가 출시됨. SSD도 기존 하드 디스크 드라이브와 같은 인터페이스(SATA나 SAS)를 지원하므로 내장 디스크, DAS, SAN에 사용할 수 있음.
  • SSD: 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)를 제거한 대신에 플래시 메모리를 장착함. 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 빠르게 데이터를 읽고 쓸 수 있음. 기존 하드 디스크 드라이브보다 랜덤 I/O가 훨씬 빠름.

8.1.2 랜덤 I/O와 순차 I/O

• 랜덤 I/O: 하드 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것. 순차 I/O 또한 작업 과정은 같음.
• 랜덤 I/O와 순차 I/O 비교
• 예) 순차 I/O는 3개의 페이지(3×16KB)를 디스크에 기록하기 위해 1번 시스템 콜을 요청했지만, 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청했음. 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크의 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직였음. 즉, 순차 I/O는 랜덤 I/O보다 거의 3배 정도 빠름.
• 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정됨. 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 큼. 데이터베이스 대부분의 작업은 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장됨.
• SSD 드라이브에서도 랜덤 I/O는 순차 I/O보다 전체 스루풋(Throughput)이 떨어짐. 그래서 SSD 드라이브의 사양에도 항상 순차 I/O와 랜덤 I/O의 성능 비교를 구분해서 명시함.

*이 책에서 소개하는 순차 I/O와 랜덤 I/O의 비교는 쉽게 이해할 수 있게 단순하게 설명한 것임.

8.2 인덱스란?

• DBMS는 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸림. 그래서 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key- Value pair)으로 삼아 인덱스를 만들어 두는 것임. 그리고 최대한 빠르게 찾아갈 수 있게 DBMS의 인덱스에서는 칼럼의 값을 주어진 순서로 미리 정렬해서 보관함.
• 자료 구조
  • SortedList: DBMS의 인덱스와 같은 자료 구조. 저장되는 값을 항상 정렬된 상태로 유지함. DBMS의 인덱스도 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지함.
  • ArrayList: 데이터 파일과 같은 자료 구조. 값을 저장되는 순서 그대로 유지함. 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장함.
• 인덱스의 장단점
  • SortedList 자료 구조는 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있음.
  • DBMS의 인덱스도 인덱스가 많은 테이블은 INSERT나 UPDATE, DELETE 문장의 처리가 느리지만, 정렬된 인덱스를 가지고 있기 때문에 SELECT 문장은 매우 빠르게 처리할 수 있음.
• 데이터를 관리하는 방식(알고리즘)과 중복 값의 허용 여부 등에 따라 저자가 임의로 인덱스를 분류한 방식은 다음과 같음. 책에서는 키(Key)와 인덱스(Index)를 같은 의미로 사용함. 인덱스를 역할별로 구분하면 프라이머리 키(Primary key)와 보조 키(세컨더리 인덱스, Secondary key)로 구분할 수 있음.
  • 프라이머리 키: 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스. 이 칼럼(또는 칼럼의 조합)은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 식별자라고도 함. 프라이머리 키는 NULL 값을 허용하지 않으며 중복을 허용하지 않음.
  • 세컨더리 인덱스: 프라이머리 키를 제외한 나머지 모든 인덱스. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수도 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고 세컨더리 인덱스로 분류하기도 함.
• 데이터 저장 방식(알고리즘)별로 구분할 경우 많은 분류가 가능하지만, 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있음. 수많은 알고리즘이 있지만 대표적으로 RDBMS에서 많이 사용하는 알고리즘임.
  
  • B-Tree 알고리즘: B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘. 오래 전부터 도입되었고, 일반적으로 많이 사용함. B-Tree의 응용 알고리즘으로, MySQL 서버에서는 위치 기반 검색을 지원하기 위한 R-Tree 인덱스 알고리즘도 있음.
  • Hash 인덱스 알고리즘: 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘. 매우 빠른 검색을 지원함. 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없음. 주로 메모리 기반의 데이터베이스에서 많이 사용함.
• 데이터의 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique)로 구분할 수 있음.
  • 인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 1개 이상 존재할 수 있는지를 의미하지만, 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 중요한 문제임.
  • 유니크 인덱스에 대해 동등 조건(Equal, =)으로 검색하는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 것임. 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많음.
• 인덱스의 기능별로 분류해보면 전문 검색용 인덱스나 공간 검색용 인덱스 등을 예로 들 수 있음. 수없이 많은 인덱스가 있겠지만, MySQL을 사용할 때는 이 두 가지만으로도 충분함. 전문 검색이나 공간 검색용 인덱스는 뒤에서 설명함.

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.