[성능 개선] 데이터베이스 튜닝의 시작, 인덱스 활용하기

데이터베이스의 성능을 향상시키기 위한 자료구조인 인덱스는 테이블의 특정 컬럼으로 생성된다. 이 때 카디널리티와 선택률은 어떤 컬럼에 대해 인덱스를 작성할 것인지 기준이 되는 지표이다.

1) 카디널리티가 높을수록

카디널리티란 값의 균형을 나타내는 개념이다. 모든 레코드에 다른 값이 들어가 있는 유일 키 컬럼인 경우 카디널리티가 높아 인덱스로 생성하기 적절하다고 판단한다. 반대로 모든 레코드에 같은 값이 들어가 있다면 카디널리티가 낮은 컬럼이다. 가령 데이터 타입이 Boolean인 컬럼을 기준으로 인덱싱을 한다면 모든 값이 TRUE OR FALSE라 인덱싱의 효율이 떨어진다. 이름, 아이디, 주소같은 데이터가 높은 카디널리티를 갖는다.

2) 선택률이 낮을수록

선택률은 특정 컬럼 값을 지정했을 때 테이블 전체에서 몇 개의 레코드가 선택되는지를 나타내는 개념이다. 최근 DBMS에서는 5~10% 이하의 선택률을 가질 경우 해당 컬럼을 인덱스로 생성하기 적절하다고 판단한다. (이는 저장소의 성능 향상과 반비례하여 성능이 좋아질수록 선택률 기준은 줄어들고 있다)

---

인덱스 스캔 방법

인덱스 온리 스캔

일반적으로 인덱스는 필요한 데이터를 읽을 데이터 레코드를 빠르게 찾는데만 사용되지만 인덱스 자체로 커버할 수 있는 경우 테이블 접근을 생략할 수 있다.

이처럼 인덱스 자체가 SQL 구문에서 필요한 컬럼을 포함하는 경우 테이블 접근을 생략하는 고속화 방법을 인덱스 온리 스캔이라 한다. 그리고 이러한 인덱스를 커버링 인덱스라고 한다.

Before

EXPLAIN
SELECT ORDER_ID, ORDER_APPROVED_AT
FROM ORDERS O
WHERE ORDER_STATUS = 'invoiced';

결과

Seq Scan on orders o  (cost=0.00..3052.01 rows=302 width=41)
  Filter: ((order_status)::text = 'invoiced'::text)

→ where 절이 존재하지만 ORDER_STATUS 컬럼에 대해 인덱스로 생성되지 않은 상태이기 때문에 테이블 풀 스캔이 수행된다.

After

CREATE INDEX CoveringIndex ON ORDERS (ORDER_STATUS, ORDER_ID, ORDER_APPROVED_AT);

EXPLAIN
SELECT ORDER_ID, ORDER_APPROVED_AT
FROM ORDERS O
WHERE ORDER_STATUS = 'invoiced';

결과

Index Only Scan using coveringindex on orders o  (cost=0.42..17.70 rows=302 width=41)
  Index Cond: (order_status = 'invoiced'::text)

→ 이처럼 select절의 필드를 커버하는 커버링 인덱스를 생성하여 인덱스 온리 스캔을 사용할 수 있다.

---

루스 인덱스 스캔 

루스 인덱스 스캔은 불필요한 인덱스는 무시하고 필요한 인덱스만 스캔하는 방식이다. MIN 또는 MAX와 같은 집계 함수가 사용된 쿼리를 최적화할 때 사용된다.

예를 들어 NAME, DATE에 대해 인덱스가 생성되어 있는 테이블에서 MAX(DATE)를 구하는 쿼리를 실행한 실행 결과는 다음과 같다.

CREATE INDEX LOOSE_INDEX ON ORDERS (NAME, DATE);

EXPLAIN
SELECT NAME, MAX(DATE) 
FROM ORDERS
GROUP BY 1;

결과

MariaDB [test]> EXPLAIN SELECT NAME, MAX(DATE) FROM ORDERS GROUP BY 1;
+------+-------------+-------------+-------+---------------+------+---------+------+------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+------+-------------+-------------+-------+---------------+------+---------+------+------+--------------------------+
|    1 | SIMPLE      | loose_index | range | NULL          | name | 103     | NULL |    3 | Using index for group-by |
+------+-------------+-------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

→ DATE는 NAME에 의존하여 정렬되므로, NAME 그룹별 마지막 DATE만 스캔한다.

loose index scan이 가능한 조건은 아래와 같습니다.
 
1. 인덱스가 SELECT 리스트의 모든 부분을 커버하는 경우. 즉, 커버링 인덱스가 적용되는 경우
2. SELECT DISTINCT, SELECT ... GROUP BY 또는 단일 투플 SELECT 문인 경우
3. MIN/MAX 함수를 제외한 모든 집계 함수가 DISTINCT를 포함하는 경우
4. COUNT(*)가 사용되어선 안 됨
5. 부분 키(subkey)의 카디널리티(고유 값의 개수)가 전체 인덱스의 카디널리티보다 100배 작은 경우
 
부분 키는 복합 인덱스(composite index)에서 앞 쪽 부분에 해당하는 것으로,
예를 들어 INDEX(a, b, c, d)로 구성되어 있는 경우 (a), (a, b) 또는 (a, b, c)가 부분 키에 해당합니다.

출처: loose index scan을 활용한 효과적인 쿼리 튜닝 방안

---

위 내용은 <SQL 레벨업 DB 성능 최적화를 위한 SQL 실전 가이드>을 참고하여 작성한 내용입니다

 

참고

loose index scan

[MySQL] B-Tree로 인덱스(Index)에 대해 쉽고 완벽하게 이해하기