RDB 성능 이슈 1. Index란

RDB 성능 이슈 #1

데이터베이스의 성능 이슈는 디스크 I/O를 어떻게 줄이느냐에서 시작된다.

디스크 I/O란 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미한다. 이 때 데이터를 읽는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 즉 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 따라 결정된다고 볼 수 있다.

그렇기 때문에 순차 I/O가 랜덤 I/O보다 빠를 수 밖에 없다. 하지만 현실에서는 대부분의 I/O 작업이 랜덤 I/O 이다. 랜덤 I/O를 순차 I/O로 바꿔서 실행할 수는 없을까? 이러한 생각에서부터 시작되는 데이터베이스 쿼리 튜닝은 랜덤 I/O자체를 줄여주는 것이 목적이라고 할 수 있다.

웹 애플리케이션의 백엔드 성능을 높이기 위해 종종 실행하는 SQL 튜닝이란,

SQL이 DBMS의 인덱스를 활용하도록 SQL을 수정하는 것이다.

인덱스란 무엇인가?

인덱스는 말 그대로 책의 맨 처음 또는 맨 마지막에 있는 색인이라고 할 수 있다.

이 비유를 그대로 가져와서 인덱스를 살펴본다면 데이터는 책의 내용이고 데이터가 저장된 레코드의 주소는 인덱스 목록에 있는 페이지 번호가 될 것이다. DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다. 그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 두는 것이다.

색인의 비유를 한 번 더 가져오자.

우리나라 책의 색인은 ‘ㄱ,ㄴ,ㄷ, …'의 순서로 되어 있다. 이와 마찬가지로 DBMS의 인덱스도 일정한 기준에 따라 정렬되어 있다. DBMS의 인덱스도 칼럼의 값을 이용해 정렬된 상태를 유지한다. 하지만 실제로 저장된 데이터들은 정렬되어 있지 않다!

DBMS의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는데는 빠르지만 새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다. 결론적으로 DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있다.

그렇다면 DBMS는 인덱스를 어떻게 관리하고 있는가

B-Tree 인덱스 알고리즘

일반적로 사용되는 인덱스 알고리즘은 B-Tree 알고리즘이다. B-Tree 인덱스는 칼럼의 값을 변형하지 않고(사실 값의 앞부분만 잘라서 관리한다.), 원래의 값을 이용해 인덱싱하는 알고리즘이다.

Hash 인덱스 알고리즘

칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 등 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

Fractal-Tree 알고리즘

B-Tree 알고리즘의 단점을 보완하기 위하 고안된 알고리즘으로, 값을 변형하지 않고 인덱싱하며 데이터가 저장되거나 삭제될 때 처리 비용을 상당히 줄일 수 있도록 설계되었다.

빠르다는 검색 어떻게 진행되는가

인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 인터널 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행한다. 이러한 과정을 트리 탐색(Tree Traversal)이라고 한다. 사실 인덱스 트리 탐색은 SELECT 문에서 뿐만 아니라 UPDATE, DELETE 쿼리문을 처리하기 위해 특정 데이터를 검색할 때도 수행된다. B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있고, 값의 뒷부분이 일치하는 경우나 부등호를 사용한 비교로는 검색이 불가능하다.

>> 인덱스 레인지 스캔에 대한 자세한 글 >>

주의해야할 사항은 어떤 것이 있는가

B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다. InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. B-Tree에서 각각의 노드를 구분하는 기준이 바로 페이지 단위이다. 인덱스 키 값의 크기가 커지게 되면 한 페이지에 저장할 수 있는 인덱스의 개수가 적어진다. 하나의 인덱스 페이지 크기는 정해져있는데 저장하려는 값이 커지만 당연히 저장할 수 있는 개수는 적어지는 것이다. 이것은 디스크로부터 읽어야 하는 횟수가 늘어나게 되는 것이고 그만큼 쿼리 수행 속도가 느려진다는 것을 의미한다.

즉, 인덱스 키값의 크기는 가능하면 작게 만드는 것이 좋다.

또한 Index가 부가되는 필드는 NOT NULL 제약조건이 있어야만 한다. 

Reference : Real MySQL 도서

end