인덱스란?
💡추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.
데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.
인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다. 그러한 이유는 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문이다.
만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.
[ 인덱스(index)의 관리 ]
DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.
- INSERT: 새로운 데이터에 대한 인덱스를 추가함
- DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
- UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함
📌
만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다. 앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 하였다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 훨씬 많이 존재하게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.
✅ 모든 요소에 인덱스를 걸지 않는 이유
인덱스 테이블이 생성되므로 "1 메모리를 많이 소모"하게 되고 Select를 제외한 Insert, Update, Delete에 대한 성능 저하가 있기 때문에 PK같은 컬럼들을 인덱싱 하도록 하는 것이 좋습니다.
인덱스가 적용된 칼럼에 "2 삽입, 삭제, 수정이 잦다면 인덱스 또한 수정해야 하기 때문에 성능이 낮아지며".
또한 "3 인덱스는 제거되는 것이 아니라 '사용하지 않음'으로 남겨 두어", 인덱스가 과도하게 커질 수 있기 때문에 모든 요소에 인덱스를 걸지 않는습니다.
복합인덱스
두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것이다.
하나의 컬럼으로 인덱스를 만들었을 때 보다 더 적은 데이터 분포를 보여서 탐색할 데이터 수가 줄어든다.
복합 인덱스는 AND 조건으로 검색되는 경우 성능에 중요한 역할을 한다.
두 개 이상의 조건이 OR로 조회되는 경우는 결합 인덱스를 만들면 안된다.
SELECT 카드번호, 결제금액
FROM 거래명세서
WHERE 카드번호 = '1234'
AND 거래날짜 BETWEEN '20230105'
AND '20230105'이럴 때 인덱스를 카드번호로 설정하면 SELECT문의 처리 속도가 빠를 것이다.
하지만 여기서 이 카드의 결제내역이 수도 없이 많고 거래날짜에 대해서 인덱스가 되어 있지 않다면 어떨까?
인덱스를 거쳤지만 뒤의 거래날짜를 필터링 하는 과정에서 많은 처리를 해야 할 것이다.
그러나 이 경우에 카드번호와 거래날짜를 복합 인덱스로 구성을 하면 해당 거래명세서 테이블은 카드번호로 정렬이 한번 되고, 또 그 안에서 거래날짜별로 정렬이 될 것이다.
이 경우 위의 SELECT문을 실행할 때
1.카드번호로 인덱스를 한 번 타고
2.거래날짜로 인덱스를 한 번 더 타므로
더욱 효과적이게 데이터를 탐색할 수 있게 된다.
💡 복합 인덱스의 컬럼 순서
그러면 복합 인덱스를 구성할 때 컬럼의 순서는 어떻게 해야할까?
디스크 I/O를 가장 적게 발생시키는 규칙을 가지고 순서를 구성하면 된다.
위의 SELECT문 예제에서 카드번호 컬럼의 분포도가 매우 좋다고 해보자.
(분포도가 좋다는 것은 카드번호의 1234 에 해당하는 데이터가 적다는 것이다.)
그러면 WHERE 카드번호 = ‘1234’ 에서 이미 많은 데이터가 필터링 되어질 것이다.
그리고 이후의 AND 거래날짜 BETWEEN ~ 문의 디스크 I/O 소모는 얼마 되지 않을것이다.
하지만 이렇다고 해서 카드번호를 항상 복합 인덱스 컬럼의 첫번째 순서로 두는 것이 적합할까?
만약 해당 테이블을 사용하는 SQL문이 다음과 같다면 어쩌면 거래날짜의 분포도가 카드번호에 비해비교적 좋아질
수도 있다.
SELECT 카드번호, 결제금액
FROM 거래명세서
WHERE 카드번호 = '1234' AND '555'
AND 거래날짜 = '20230110' // <-이에 따라 복합 인덱스의 컬럼 순서를 정하는 것은 절대적인 것이 아닌 어떤 데이터를 조회하느냐에따라 달라질 수 있는 것을 볼 수 있다.
Ref. https://mangkyu.tistory.com/96
Ref. https://itsowavy.oopy.io/db/compound-index
