[DBMS] Index를 통한 SELECT 쿼리 성능 개선

1. INDEX

1-1. INDEX란?

데이터베이스 인덱스(index)는 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블에 저장된 데이터의 검색 속도를 향상시키기 위한 자료구조이다.

 

인덱스는 데이터베이스 내의 특정 컬럼(열)이나 컬럼들의 조합에 대한 값과 해당 값이 저장된 레코드(행)의 위치를 매핑하여 데이터베이스 쿼리의 성능을 최적화하는 데 중요한 역할을 한다.

 

예를 들어, 책에서 원하는 내용을 찾는다고 가정하면, 책의 모든 페이지를 넘기면서 원하는 내용이 나올 때까지 찾는 것보다 목차 또는 저자가 남긴 색인(index)을 통해 찾는 것이 더욱 빠를 것이다. 데이터베이스의 인덱스가 책의 목차와 색인과 같은 역할을 한다.

 

간단하게 정리하면 인덱스의 개념은 아래와 같다.

1. 테이블에 대한 검색의 속도를 높여주는 자료 구조
2. 색인이고 메모리 영역의 일종의 목차를 생성하는 개념
3. 이런 목차를 이용하여 검색 범위를 줄여 속도를 높일 수 있다.

 

1-2. INDEX 장점/단점

 

장점

1. 검색 대상 레코드의 범위를 줄여 검색 속도를 빠르게 할 수 있다.
2. 중복 데이터를 방지하거나 특정 컬럼의 유일성(Unique)을 보장할 수 있다.
3. ORDER BY 절과 GROUP BY 절, WHERE 절 등이 사용되는 작업이 더욱 효율적으로 처리된다.

 

단점

1. 인덱스 생성에 따른 추가적인 저장 공간이 필요하다
2. CREATE(삽입), DELETE(삭제), UPDATE(수정) 작업 시에도 인덱스를 업데이트해야 하므로 성능 저하가 발생할 수 있다.
3. 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
4. 인덱스 생성 시간이 오래 걸릴 수 있다.

 

인덱스를 잘 활용한다면 데이터베이스 내 필요한 데이터를 검색할 때 좋은 성능 향상에 도움이 된다.

하지만, 적절한 컬럼에 대한 인덱스를 생성하지 않고 무작위로 인덱스를 생성한다면 오히려 데이터 베이스의 용량을 차지하기만 하고 추가적으로 쓸모없는 인덱싱을 통해 성능이 떨어 질 수 있다.

 

따라서, 적절한 컬럼에 대한 인덱스를 선택하고 생성하는 것이 중요하다.

1-3. 복합 INDEX

복합 인덱스 : 데이터베이스에서 여러 개의 컬럼(열)들을 조합하여 인덱스를 생성하는 것을 말한다.

위에서 설명한 인덱스는 하나의 컬럼을 선택했다면 복합 인덱스는 여러 개의 컬럼을 사용하여 인덱스를 생성하는 것이다.

장점

• 여러 개의 컬럼을 동시에 검색하기 때문에 검색 속도를 개선할 수 있다.
• 인덱스를 생성할 때 여러 개의 컬럼을 사용하여 정렬을 더 효율적으로 할 수 있다.
• 하나의 컬럼을 선택한 인덱스 여러개를 선택할 때 보다 여러개 컬럼을 선택한 복합 인덱스를 사용하면 인덱스가 차지하는 용량이 줄어든다.
• 복합 인덱스는 여러 개의 컬럼을 함께 사용하기 때문에, 쿼리가 여러 개의 조건을 가지고 있을 때 최적화된 실행 계획을 수립할 수 있다.

 

복합 인덱스는 아래와 같은 상황에서 사용하면 효율적이다.

• where절에서 and 조건으로 자주 통합되어 사용되면서 각각의 분포도 보다 두 개 이상의 컬럼이 통합될 때 분포도가 좋아지는 컬럼들
• 다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들
• order by에서 자주 사용되는 컬럼들
• 하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때

하지만 복합인덱스의 장점만 있는 것은 아니다.

아래와 같은 상황에서는 복합 인덱스를 사용하는 것에 주의해야 한다.

 

• 복합 인덱스는 일반적으로 WHERE 절에 자주 사용되는 컬럼들로 구성되는데, 인덱스를 생성하는 컬럼의 개수가 많아질수록 인덱스의 성능은 떨어질 수 있다.
• 복합 인덱스를 생성할 때는 인덱스 생성 순서도 고려해야 한다.
• 쿼리문 작성 시 복합 인덱스를 사용하고자 한다면 반드시 복합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여 사용하여야 한다. 주로 자주 이용되는 순서대로 복합 인덱스 컬럼의 순서 결정한다.
• 인덱스 생성 순서는 WHERE 절에서 구분이 가장 빠르게 되는 것들을 먼저 써야 한다. 이렇게 함으로써, 인덱스를 탐색할 때 필요한 레코드 수가 최소화시킬 수 있다.

2. INDEX 생성 및 삭제

아래 포스팅하는 모든 예제에 관한 것들은 mariaDB에서 사용한 예시이다.

사실 인덱스를 생성하고 삭제하는 명령은 매우 간단하고 이번 포스팅의 주 목적은 실제로 인덱스를 사용해서 데이터 베이스 내에서 검색을 진행할 때 속도 향상을 정리하고 싶기 때문에 길게 포스팅하지 않고 간단하게 포스팅할 예정이다.

 

2-1. INDEX 생성

인덱스를 생성 방법은 아래와 같다.

CREATE INDEX "인덱스명" on "테이블명" ("인덱스로 사용할 테이블의 컬럼명")

 

실제로 내가 사용한 인덱스 생성 쿼리은 아래와 같다.

 

2-2. INDEX 삭제

인덱스를 삭제 방법은 아래와 같다.

ALTER TABLE "테이블명" DROP INDEX "인덱스명"

 

실제로 내가 사용한 인덱스 삭제 쿼리은 아래와 같다.

 

3. 실제 INDEX를 사용한 SELECT 쿼리 성능 개선 예시

성능 개선을 실행했던 환경은 아래와 같다.

• 100만개의 여러 개의 컬럼을 가지는 테이블
• 테이블 내 원하는 조건 만족하는 데이터 검색
• 조건(시간, id, 이름, 설명, 조회 기간...) 중 여러 개의 컬럼 값에 인덱스를 통한 성능 개선 확인

 

3-1. INDEX를 사용하기 전 성능

먼저 인덱스를 사용하기 전 기본 값에 대한 SELECT 쿼리는 17.7초가 걸렸다.

물론 사용하는 환경 자체의 CPU나 메모리에 과부하가 걸린 이유도 있었겠지만(좋은 환경에서는 이렇게까지 오래 걸리지는 않음...)

17초라는 시간은 정말 말도 안되는 시간이라고 생각했다.

 

따라서 원인을 분석해보고 fetch join, batch size 조정 등 다양한 방법을 통해서 성능을 개선해보려 하고 원인에 대해서 여러가지 분석을 해봤지만 개발적으로는 해결하기 어려운 문제라고 생각했다.

그래서 데이터 베이스를 확인해보면서 인덱스가 있는지 없는지 확인해보니 인덱스가 기본키말고는 없고 조회를 진행할 때 full scan이 일어난 것을 확인 했다.

 

3-2. INDEX 사용 한 후 성능

3-2-1.  event name 컬럼을 인덱스로 선택

• 17.7초 -> 6.85초로 시간은 반 이상이 줄었지만 사실 자주 사용되는 조회 쿼리에서 6.8초가 걸리는 것도 매우 느리다고 생각했다.
• 또한 쿼리 앞에 ANALYZE 를 붙여서 실행 계획을 분석해봤을 때, 인덱스를 사용하지만 249988개의 데이터에 대해서는 모두 스캔을 해야하기 때문에 효율적인 인덱싱은 아니라고 보인다.

3-2-2. nation 컬럼을 인덱스로 선택

 

• nation 인덱스 또한, 17.7초 -> 6.96초로 시간은 반 이상이 줄었지만 위의 이유와 동일하게 원하는 목표치를 달성하지 못해 인덱스로는 적합하지 못하다고 생각했다.

3-2-3. detect_dt 컬럼을 인덱스로 선택

• 17.7초 -> 79ms로 시간이 매우 단축되었다.
• 인덱스에 관해 찾아봤을 때 DATETIME/DATE는 인덱스로 사용하는 것을 권장하지 않는다고 했지만 효과가 너무 크기 때문에 문제는 이것이라고 생각했다.
• 또한 ANALYZE를 통한 실행 계획을 분석했을 때 같은 조건에 대해서 1820개의 데이터에 대해서만 스캔을 진행하면 되기 때문에 매우 효율적인 인덱스라고 확인되었다.

3-2-4. 결론

• detect_dt(DATETIME) 컬럼을 인덱스로 사용했을 때 가장 효과가 많이 보였던 이유는 카디널리티가 높았기 때문이라고 생각되었다.
• 또한, 더 효율적인 인덱스 활용을 위한 detect_dt와 더불어 사용할 수 있는 컬럼을 복합인덱스로 구성해보았지만 성능 향상에 큰 영향을 미치지 못하여 사용하지 않기로 하였다.

---

 

4. 결론

1. 다양한 조건들을 필터로 사용하여 데이터를 조회할 때 인덱스를 사용하면 쿼리 속도의 향상을 경험할 수 있다.
2. 하지만, 매번 모든 컬럼에 대해서 인덱스를 사용하는 것은 오히려 속도가 저하될 수 있는 원인이 되기도 한다.
3. 따라서, 적절한 컬럼(카디널리티가 높은)에 대한 인덱스를 선택하여 생성하여야 한다.
4. 또한, 인덱스가 존재해도 데이터베이스의 옵티마이저가 자체적으로 풀 스캔이 효율적이라고 생각한다면 풀 스캔이 이루어질 수 있다.
   • 이것에 대해서는, 가용할 수 있는 메모리가 많다면 인덱스를 사용하는 것보다 풀 스캔을 하는 경우도 있었다.
5. 인덱스를 생성할 때 데이터 베이스의 용량을 확인하면서 생성해야 한다.
   • 데이터 베이스의 용량을 확인할 때 이유는 모르겠지만 인덱스가 사라진다고 바로 인덱스가 차지하던 용량이 비워지는 것은 아니다.

 

REF

https://velog.io/@kwontae1313/%EB%B3%B5%ED%95%A9%EC%9D%B8%EB%8D%B1%EC%8A%A4

복합인덱스(Composite Index)

https://ittrue.tistory.com/331

[DB] 데이터베이스 인덱스(index) 개념 정리

https://programmer7895.tistory.com/89

데이터베이스 인덱싱의 이해: 인덱스의 장단점 깊게 알아보기