들어가며

조회 성능을 높이려면 인덱스를 사용해야 한다는 것을 들어보기만 했지, 직접적으로 사용해보진 않았다.
많은 트래픽을 경험해보지도 않았을 뿐더러, 조회 성능에 대한 고민을 해보질 않았다.

그래서 이번에 인덱스에 대한 개념과 적용 방법을 정리해보려고 한다.
그리고 이커머스 서비스의 상품 목록 조회 시 인덱스를 어떻게 적용하는게 좋을지에 대한 고민과 내용을 작성해보려고 한다.

Index 란?

Index(색인)는 많은 영역에서 사용되는 용어인데, 공통적으로 가지고 있는 의미는 어떤 내용에 대해 빨리 찾아보기 위해 먼저 보는 것이다.

예를 들어, 책을 볼 때 원하는 정보를 빨리 얻기 위해 우리는 목차를 보고, 해당 페이지로 바로 책장을 넘긴다.

이 때 목차가 Index에 해당한다.

목차가 없다면 우리는 첫 페이지부터 한 장씩 Scan하며 정보를 찾아야 한다.
데이터베이스에서는 이처럼 전체를 훑는 방식을 Full Table Scan이라고 한다
반면, 목차를 보고 원하는 정보가 있는 페이지로 바로 찾아가는 방식을 Index Scan이라고 한다.

Index Scan의 종류

Index Scan에도 여러 종류가 있다.

Index Full Scan

인덱스를 처음부터 끝까지 다 읽는 방식이다.

Full Table Scan과 어떤 차이가 있나 생각할 수 있지만, 인덱스는 설정한 컬럼의 값만 가지고 있는 테이블이라 용량이 적어 훨씬 빠르게 읽을 수 있다.

WHERE 절이 없어 범위를 좁힐 수 없거나, 인덱스를 이용해 Scan 하는 것이 더 빠르다고 판단될 경우 사용하게 된다.

EXPLAIN을 확인했을 때 type이 index로 표시되어, 인덱스를 사용하고 있다고 생각될 수 있지만,

Index Full Scan은 Full Table Scan의 차선책일 뿐이라, 추가적인 인덱스 설정이 필요하다.

Index Unique Scan

수직적 탐색만으로 데이터를 찾아내는 방식으로 쿼리에 UNIQUE 제약 조건이 설정된 인덱스 컬럼을 등치조건(=)으로 조회할 때 발생한다.

고유한 값 하나만 조회하면 되기 때문에 가장 빠르게 스캔할 수 있다.

EXPLAIN으로 확인하면 type이 const로 표시되거나, 여러 테이블을 조인하는 쿼리에서는 eq_ref로 표시된다.

Index Range Scan

인덱스의 수직 탐색으로 시작점을 찾고, 그 지점부터 필요한 범위까지만 스캔하는 방식이다.

범위 연산자(<, >, BETWEEN, IN, LIKE)나 ORDER BY 컬럼에 인덱스가 있고 LIMIT 으로 범위를 제한할 때 발생한다.

Range Scan을 쓴다고 해서 무조건 빠르게 스캔할 수 있는 것은 아니다. 얼마나 좁은 범위를 스캔할 수 있는지가 중요한데, 선택도 즉, 전체 데이터 수 대비 조건에 맞는 데이터 수의 비율이 낮을수록 빠르게 스캔할 수 있다.

예를 들어, like_count > 1000 이라는 조건을 걸었을 때, 좋아요가 1000개 이상인 상품의 개수가 10개라면 빠르게 스캔할 수 있지만, 8만개라면 Full Scan에 가까울 수 있다.

EXPLAIN시 type은 range로 표시된다.

Index Skip Scan

MySQL 8.0부터 지원하는 기능으로, 복합 인덱스가 설정되어 있을 때, 인덱스의 마지막 순서의 컬럼으로만 조건절을 구성하더라도 인덱스를 이용해 스캔하는 방식이다.

예를 들어, (A, B)로 복합 인덱스가 설정되어 있는데, 쿼리의 조건절이 B로만 구성되어 있더라도 해당 인덱스를 사용할 수 있게 하는 것이다.

하지만 A 컬럼의 카디널리티가 높으면 옵티마이저가 Index Skip Scan을 사용하지 않고 Index Full Scan이 더 빠르다 판단할 수 있다.

상품 Index 설정

현재 상품 목록 조회의 경우 brand_id를 통해 필터링할 수 있고, latest(최신순), price(가격순), like_count(좋아요순)으로 정렬할 수 있다.

그 중 좋아요순 정렬 조회 쿼리의 실행계획(EXPLAIN)을 확인하며 어떤 인덱스를 설정하는게 좋을지 고민해봤다.

상품 테이블에 상품 샘플 데이터 10만개가 들어있으며, 15% 정도는 삭제 처리된 데이터이다.
브랜드별로 0 ~ 300개 정도의 상품을 가지도록 데이터를 구성했다.
각 상품의 좋아요 수는 0 ~ 10000개 사이로 설정하였다.

조회 쿼리는 모든 상품을 조회하는 경우와 특정 브랜드의 상품만 조회하는 경우로 진행했다.
사용된 쿼리는 아래와 같다.

# 모든 상품 조회
SELECT * FROM tb_product 
WHERE deleted_at IS NULL 
ORDER BY like_count DESC 
LIMIT 20;

# 특정 브랜드 상품 조회
SELECT * FROM tb_product 
WHERE deleted_at IS NULL AND brand_id = 1 
ORDER BY like_count DESC 
LIMIT 20;

인덱스 적용 전

type	rows	Extra
ALL	98254	Using where; Using filesort;

인덱스를 적용하기 전에는 인덱스가 없으므로 type이 ALL로 Full Table Scan을 사용하는 것을 확인할 수 있다.

10만개의 데이터 중 98254개의 데이터를 조회할 것을 예상하며, Using filesort로 인덱스를 사용하지 못해 DB가 정렬을 한다는 것을 볼 수 있다.

like_count 인덱스 적용

type	rows	Extra
Index	20	Using where;

like_count에 인덱스를 적용하니 type이 Index로 Index Scan을 사용하는 것을 볼 수 있다.

deleted_at 컬럼에 인덱스가 적용되지 않았기 때문에 Using where로 별도의 필터링 작업을 진행한 것을 확인할 수 있다.

rows가 20개로 확실히 성능은 좋아졌지만, Index Scan을 사용하는 부분이 문제가 될 수 있다.

예를 들어, 만약에 데이터의 deleted_at의 대부분이 NULL이 아니라면, Full Table Scan과 다를바가 없을 것이다.

해당 내용을 테스트하기 위해 deleted_at이 NULL인 데이터의 수를 20개로 설정하고 좋아요 수를 골고루 분포되도록 데이터를 구성한 뒤 실행계획을 분석해보았다.

EXPLAIN ANALYZE 결과

	대부분 NULL인 경우	대부분 NULL이 아닌 경우
actual rows	24	95002

actual rows가 95002개로 늘어난 걸 확인할 수 있다. 즉 거의 Full Scan을 하고 있는 것이다.
이러한 경우는 드물지만 그래도 Index Full Scan은 피하는게 좋다고 생각한다.

deleted_at, like_count 복합 인덱스 적용

type	rows	Extra
ref	49127	Using Index Condition;

type이 ref로 변경되었고, Extra는 Using Index Condition으로 인덱스에서 최대한 많은 데이터를 필터링한 것을 알 수 있다.

그렇다면 복합 인덱스의 순서를 like_count, deleted_at 순으로 하면 어떨까?

type	rows	Extra
Index	20	Using where;

다시 type은 Index가 되었고, Extra는 Using where로 변경되었다.

like_count 인덱스를 먼저 사용하니 정렬된 like_count 데이터를 읽을 때 마다 deleted_at을 체크하는 것이다. 즉, 체크할 범위는 정렬만 되어 있을 뿐이지 그대로이다.

deleted_at이 먼저 인덱싱되어 있을 경우에는 deleted_at이 NULL인 데이터를 먼저 걸러내고 그 중 like_count를 체크하니 범위가 줄어들어 훨씬 효율적으로 작동한다.

즉, WHERE 절을 통해 필터링되는 컬럼의 인덱스가 앞쪽에 있을수록 효율적으로 작동한다는 것을 확인할 수 있다.

ORDER BY 절에서 사용된 컬럼의 인덱스는 WHERE 절에서 사용된 컬럼의 인덱스보다 앞에 있으면 안된다.

brand_id, deleted_at, like_count 복합 인덱스 적용

brand_id, deleted_at, like_count 순으로 복합 인덱스를 생성했다.

type	rows	Extra
ref	130	Using Index Condition;

type은 ref, Extra는 Using Index Condition으로 인덱스가 잘 적용되었다.

brand_id와 deleted_at 모두 WHERE 절을 통해 필터링하기 위해 인덱스를 사용하므로 like_count 보다 앞쪽에 인덱스 순서를 두는게 효율적이다.

그렇다면, brand_id와 deleted_at의 순서는 어떻게 하는게 좋을까?

상황에 따라 다를거라고 생각되지만, brand_id와 deleted_at의 경우 상품 조회 시 WHERE 절에서 둘 다 등치 조건(=)으로 사용되는 것은 동일하므로 카디널리티를 비교해서 순서를 정해볼 때 brand_id가 카디널리티가 높아 brand_id를 앞쪽에 두는 것이 효율적이다.

왜냐하면, 브랜드의 경우 종류가 많아 필터링 시 브랜드로 상품을 먼저 걸러내어 상품의 범위를 확 줄이면 삭제 여부를 확인할 상품의 수도 줄어들기 때문이다.

위에서 Range Scan에 대한 설명을 하며 선택도가 낮을수록 빠르게 스캔할 수 있다고 했다.

즉, 전체 브랜드 대비 특정 브랜드의 상품 수가 전체 상품 대비 삭제되지 않은 상품 수보다 낮아 brand_id를 먼저 인덱싱하는 것이 효율적이다.

서비스가 오래되고 커지면서 삭제된 상품의 수가 많아져 우항의 선택도가 낮아질 수 있지만, 브랜드의 상품 수가 전체 상품 수에 비해 매우 작다고 생각한다.

결론

최종적으로 상품 전체 조회 시에는 (deleted_at, like_count) 복합 인덱스를 사용하였고,
특정 브랜드 상품 조회 시에는 (brand_id, deleted_at, like_count) 복합 인덱스를 사용하였다.

좋아요 수로 정렬된 상품 조회 쿼리의 실행 계획을 살펴보았을 때 복합 인덱스 설계 시 WHERE 절에 사용된 컬럼이 ORDER BY 절에 사용된 컬럼보다 순서를 앞으로 가져가도록 설계를 해야한다.

그리고 같은 절의 각 컬럼에 인덱스를 걸어야할 때는 카디널리티를 비교하여 순서를 정하는 것이 조회 성능을 개선할 수 있는 방법이란 것도 알게되었다.

추가적으로 범위 조건을 추가하여 조회를 할 경우를 생각해봤다.

BETWEEN, IN, <, >, LIKE와 같은 절이 추가된다면 복합 인덱스 설계 시 맨 뒤에 인덱스를 설정하는게 효율적이라고 생각했다.
왜냐하면, 등치 조건을 통해 필터링을 하고 정렬을 다 끝낸 다음 범위의 시작점을 찾아 조회를 하는 것이 범위를 제일 좁게 만들 수 있는 방법이기 때문이다.

상품 가격의 범위를 10000원 이상(price > 10000)으로 설정했을 때 만약 10000원 이상인 상품이 10개 밖에 없다면 상관 없겠지만, 상품의 90%가 10000원 이상이라면 범위 조건에 사용할 컬럼의 인덱스를 앞으로 둔다면 범위를 좁히는 효율이 떨어진다.

해당 가격 범위의 상품 중에서 특정 브랜드의 상품을 찾는 것보다 특정 브랜드의 상품을 먼저 찾고 그 상품에서 가격 범위를 찾는 것이 더 효율적이기 때문이다.

조회 성능을 높이기 위한 인덱스 설계 시 여러가지 상황이 있을거라 생각한다.
그 때마다 데이터의 특성을 잘 파악하고 고민해서 설계한다면 조회 성능을 개선할 수 있을거란 자신감을 얻은 것 같다.

마지막으로 인덱스를 설계하는 것은 조회 시 데이터의 범위를 좁혀나가는 방법을 정하는 것이라 생각한다.

techGm

인덱스 설계.. 어떻게 하는거죠??...