[MySQL] 인덱스

Database

폐프 2025. 1. 14. 13:15

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다.

그전에 인덱스에만 의존적인 용어는 아니지만 자주 언급되는 디스크 읽기 방식인 랜덤 I/O, 순차 I/O에 대해 간단히 알아보자.

컴퓨터의 CPU나 메모리처럼 전기적 특성을 띤 장치는 빠르게 성장했지만 디스크같은 기계식장치의 성능은 제한적으로 발전했다.

최근 SSD드라이브가 활용되고 있지만 여전히 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이다.

따라서 데이터베이스의 성능 튜닝은 디스크 I/O를 얼마나 줄이느냐가 관건일 때가 상당히 많다.

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O의 경우 SSD가 하드디스크에 비해 거의 비슷한 성능을 보이지만,

결국 SSD의 장점은 랜덤 I/O가 빠르다는 것이다. (데이터베이스의 작업은 대부분 작은 데이터를 읽고 쓰는 랜덤 I/O)

랜덤 I/O와 순차 I/O의 차이를 운영체제의 관점에서 보면,

순차I/O는 3개의 페이지를 디스크에 기록하기 위해 1번의 시스템 콜을 요청하지만, 랜덤I/O는 3번의 시스템 콜을 요청한다.

중요한 것은 디스크의 성능은 디스크의 헤더 위치 이동 없이 얼마나 많은 데이터를 한번에 기록하느냐가 좌우하므로,

랜덤 I/O의 작업 부하가 훨씬더 크다. (대부분의 작업이 랜덤 I/O이므로 MySQL에 로그 버퍼 기능이 내장되어있는 이유이기도 하다)

사실상 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 실행할 방법은 없다.

따라서 일반적인 쿼리 튜닝이라 함은, 랜덤 I/O를 줄이는 것 즉 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선한다고 보면 된다.

흔히 인덱스를 언급할 때 책의 색인을 통해 설명한다.
책의 색인은 인덱스, 책의 내용은 데이터 파일, 페이지 번호는 레코드의 주소에 비유될 것이다.

DBMS도 똑같다. 원하는 데이터를 가져오기 위해 모든 데이터를 검색할 순 없기 때문에 칼럼의 값 : 해당 레코드의 주소 를 Key-value 형태로 삼아 인덱스를 만들어두는 것이다.

또한 DBMS의 인덱스도 책의 색인처럼 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.

DBMS의 인덱스는 SortedList와 같은 자료구조이다.

SortedList의 특징은 데이터가 저장될 때마다 항상 값을 정렬해야하므로 저장하는 과정은 느리고 복잡하지만,

이미 정렬된 데이터에서 원하는 값을 찾아오는 건 빠르다.

당연히 DBMS도 인덱스가 많은 테이블은 INSERT, UPDATE, DELETE 문장의 처리가 느려지지만 SELECT는 빠르게 처리할 수 있다.

결론적으로 DBMS에서 인덱스는 저장 성능을 희생하고 데이터 읽기 속도를 높이는 기능이다.

만약 SELECT쿼리의 WEHRE 절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 오히려 역효과를 불러올 수도 있다.

인덱스는 데이터를 관리하는 방식과 중복 값의 허용 여부 등에 따라 여러 가지로 나눠볼 수 있다.

프라이머리 인덱스는 테이블의 프라이머리 키로 만들어진 인덱스로, Null값과 중복값을 허용하지 않는다.

세컨더리 인덱스는 프라이머리 키를 제외한 나머지 인덱스를 칭하는데, 쿼리에서 검색에 자주 사용되는 칼럼에 대해 인덱스를 지정하는 경우가 이에 해당한다.

인덱스는 데이터 저장 방식(알고리즘)별로 구분하면 크게 B-Tree 인덱스, Hash 인덱스로 구분할 수 있다.

B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘인데, 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱한다.

추가적으로 인덱스를 데이터의 중복 허용 여부로 구분한다면 유니크 인덱스, 논-유니크 인덱스로 구분할 수 있다.

인덱스의 유니크 여부는 말그대로 같은 값에 대한 존재여부인데 별거아니여보여도 실제 쿼리를 실행하는 옵티마이저에게 중요한 문제이다.

(옵티마이저 입장에서 유니크 인덱스에 대해 "="를 통해 검색한다는 것은 하나의 레코드만 찾으면 더 찾지 않아도 된다는 사실을 의미한다.)

기능별로 분류한다면 전문 검색용 인덱스, 공간 검색용 인덱스를 예를 들 수 있다.