아직까지 서비스를 출시한 경험도 없고, 너무 복잡한 데이터 베이스 구조도 설계한 적이 없어 Slow Query에 대해서 신경쓰지 않고 개발을 하곤 했다. 따라서 Slow Query를 개선하기 위해 로그를 찍거나 쿼리 최적화 및 튜닝에 대해서 고민해본 적이 없는 것 같다. 이번 포스팅을 통해 Slow Query를 개선하기 위해 DB Index이 무엇이고, 어떻게 설정하고 적용해야하는지 공부해보아야 겠다.
DB Index 란
추가적인 쓰기 작업과 저장 공간을 활용하여 데이터 베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다. DB Index의 가장 대표적인 예는 책의 색인이다. 책에서 원하는 내용을 찾는다고 할 때, 책의 모든 페이지를 찾아보는 것 (FULL SCAN) 보다는 책의 맨 앞 혹은 책 맨 뒤에 있는 색인 페이지를 통해 빠르게 찾을 수 있다. 마찬가지로 index를 사용하지 않는 칼럼을 조회하는 상황에서는 테이블 전체를 탐색하는 Full Scan이 수행되어 처리 속도가 떨어진다. 따라서 index 설정을 통해 검색 속도 향상 및 조회 성능을 높일 수 있다.
// 인덱스 설정
CREATE INDEX si_id ON MEMBER (id); // single column index
CREATE INDEX si_name ON MEMBER (name);
CREATE INDEX mi_id_name ON MEMBER (id, name); // multi column index
CREATE INDEX mi_id_name_age ON MEMBER (id, name, age);Index 관리
Index를 관리하기 위해서는 DB의 약 10%에 해당하는 저장공간이 필요하다. 또한 Index는 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있는데, 만약 Index에 적용된 칼럼에 INSERT, UPDATE, DELETE가 수행된다면 Index를 관리하기 위해 추가적으로 연산을 해주어야 하며 그에 따른 오버헤드가 발생한다.
- INSERT : 새로운 데이터에 대한 인덱스를 추가
- DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는 다는 작업 진행
- UPDATE : 기존의 인덱스를 사용하지 않음을 처리하고, 갱신한 데이터에 대해 인덱스를 추가
따라서, DELETE와 UPDATE가 비번하게 일어나는 칼럼에 대해서 index를 설정하면, 인덱스의 크기가 비대해져서 성능을 오히려 저하시킨다. 왜냐하면 DELETE와 UPDATE 시 기존의 인덱스를 삭제하지 않고 '사용하지 않음'처리를 해주기 때문에 인덱스의 크기가 커져, SQL문 처리시 오히려 성능이 떨어질 수 있다.
그럼 어떤 컬럼에 Index를 설정하는 것이 좋을까?
- 수정 빈도 : INSERT, UPDATE, DELETE가 자주 발생하지 않는 칼럼
- 활용도 : JOIN과 WHERE 또는 ORDER BY에 자주 사용되는 칼럼
- 중복도 : 데이터의 중복도가 낮은 칼럼
따라서, 다음과 같은 기준을 통해 INDEX를 설정할 칼럼을 선택하는 것이 바람직하며, 아무 칼럼에 무작정 INDEX를 설정하는 것은 전체적인 데이터 베이스의 성능 부하를 초래한다.
Index의 장단점
장점
- 특정 칼럼에 대해서 조회시, 검색 속도와 그에 따른 성능을 향상시킬 수 있다.
- 전반적인 시스템 부하를 줄일 수 있다.
단점
- 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
- 인덱스를 관리하기 위한 추가 작업이 필요하다.
- 인덱스를 잘못 사용할 경우 오히려 성능 저하되는 역효과가 발생한다.
Index 자료 구조
- 해시 테이블
(Key, Value)로 데이터를 저장하는 자료구조로, 빠른 데이터 검색이 필요할 때 유용하다. 하지만 DB 인덱스에서 해시 테이블을 사용하는 경우는 제한적이라고 한다. 왜냐하면 해시 테이블은 등호(=) 연산에 특화된 자료구조이기 때문이다. 데이터베이스 조회시 등호 연산도 사용하지만, 부동호 연산(>, <)이 자주 일어난다. 해시 함수는 값이 1이라도 달라지면 다른 해시값을 생성할 수 있기 때문에 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않다.
- B+Tree
DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이다.
- 리프 노드만 인덱스와 함께 데이터를 가지고 있고, 나머지 노드는 데이터를 위한 인덱스만 갖는다.
- 리프 노드는 Linked List로 연결되어 있다.
- 데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.
데이터 베이스의 인덱스 칼럼은 부등호를 이용한 순차 검색 연산이 자주 발생한다. 따라서 BTree의 리프 노드들을 Linked List로 연결하여 순차검색을 용이하게 하는 등 BTree를 인덱스에 맞게 최적화한 것이다.
비록 B+Tree는 O(log2𝑛)의 시간복작도를 가지지만, 해시테이블보다 인덱싱에 더욱 적합한 자료구조이다.
참고 자료
https://mangkyu.tistory.com/96
https://velog.io/@jwpark06/효과적인-DB-index-설정하기
