No Fedex, Yes Index

1) 서론

인터넷에서 상품을 주문했을 때 예상 배송 기간보다 늦은 경험을 해보셨나요?

저는 스무 살 때 데이트를 해보겠다고, 인생 첫 헤어 왁스를 인터넷에서 주문해본 적이 있는데요. 예상보다 늦은 배송으로 인해 정작 데이트 날은 머리를 꾸미지도 못 하고 나갔던 기억이 있습니다. 이유는 택배사 물량 폭주로 인한 배송 지연이었습니다.

하지만 요즘에는 로켓 배송, 샛별 배송, 새벽 배송 등 쿠팡을 시작으로 아주 빠른 배송이 이루어지고 있는데요. 예전에는 3 ~ 5일이 걸렸지만, 요즘은 하루 혹은 반나절이면 충분한 세상이 됐습니다.

쿠팡은 각 지역마다 거대한 물류센터를 지어서 소비자와 물리적인 거리를 줄이고, 자동화 시스템을 통해 효율적인 배송 준비를 하는데요. 물건을 배송한다는 것은 기존의 업체들과 같지만, 몇몇의 개선으로 인해 로켓 배송에 성공했습니다.

아마 업무 중에도 예상치 못 한 대량의 요청 혹은 대용량 데이터를 다뤄야 하는 순간이 올 것 같은데요.

마침, 업무 중 MySQL select 쿼리의 성능 지연이 발생했습니다. 그리고 해당 이슈와 index를 통해 개선한 내용을 공유드립니다.

 

---

2) 문제 상황

업무 중 RDB에 쌓인 거대한 데이터를 페이징 해야 하는 상황이 생겼습니다. 해당 데이터는 과거부터 적재되던 데이터이고, 일부의 데이터만 조회해야 하는데요.

조회의 대상이 되는 테이블의 데이터는 약 몇 만건입니다. 그리고 Type 1 ~ 5 정도로 다양한 종류로 구분되는 데이터들이 존재합니다.

사용된 쿼리는 대략 아래와 같습니다.

SELECT * 
FROM 테이블 
WHERE 조건1 AND 조건2 
ORDER BY 날짜 DESC 
LIMIT 20 
OFFSET 0
;

이때 조회에 데이터 양에 따라서, 약 6 ~ 27초가 걸렸습니다. 아무리 RDB라고 하지만, 고작 몇 만 건을 조회하는데 최대 27초가 걸린다는 것은 이해하기 어려운데요. 실제 구글 검색을 해봐도 RDB의 100만 건 이상일 때의 성능 이슈를 주로 다루는 것을 알 수 있습니다.

해당 기능을 사용하시는 분들도 힘들고, 제공한 저로서도 매우 자존심이 상했는데요.. 그래서 분석해봤습니다.

---

3) 성능 저하 이유

우선 위의 쿼리가 어떻게 실행되는지 알아보기 위해 EXPLAIN을 사용합니다.

EXPLAIN
SELECT * 
FROM 테이블 
WHERE 조건1 AND 조건2 
ORDER BY 날짜 DESC 
LIMIT 20 
OFFSET 0
;

EXPLAIN 쿼리를 사용하면 id, select_type, table 등이 나오는데요.

이때 주목해야 할 부분은 type, rows 입니다.

• type: ALL
• rows: 50000

3. 1) EXPLAIN 쿼리의 결과 포맷 中 type

EXPLAIN의 결과 포맷에는 다양한 형태의 type이 있는데요. 주요한 몇 가지만 다뤄봅니다.

• system: 해당 테이블에 오직 하나의 row만 존재할 때입니다.
• const: 상수 취급을 할 수 있을 정도로 일치하는 row가 하나만 존재할 때입니다. 예) where PK = 1
• eq_ref: 다른 테이블에 일치하는 값을 PK, UNIQUE NOT NULL을 index 하여 조회합니다. 현실적으로 존재하기 힘든 system, const type을 제외하고, 가장 성능적으로 우수합니다. 예) A.PK = B.PK
• ref: eq_ref와 비슷하지만, 조회 대상 테이블의 컬럼이 unique, pk가 아닐 경우입니다. 하지만 조회 대상이 되는 컬럼은 index로 추가된 컬럼일 때입니다. 예) A.indexedColumn = B.indexedColumn
• range: 말 그대로 해당 조건에 맞는 범위의 record를 찾습니다. 예) IN(), BETWEEN()
• index: all과 마찬가지로 full scan이 발생합니다. 하지만 Index tree를 이용해서, 더 빠른 성능을 보여줍니다.
• all: full scan 합니다.

즉 기존 쿼리의 type은 ALL이었고, full scan이 발생했습니다. 일치하는 데이터를 찾기 위해 처음부터 끝까지 scan 했습니다. 이로 인해서 데이터의 양이 많을수록 수행 시간은 선형적으로 증가하고, 성능 문제가 발생했습니다.

3. 2) All vs Index 뭐가 다르죠?

3. 1의 설명대로라면, index와 all 비슷해 보입니다. 똑같이 full scan이 발생하는데요.

그럼에도 불구하고 index가 더 빠른 이유에는 index tree가 핵심입니다.

Mysql, 즉 InnoDB에서는 index를 생성하면, index tree를 생성합니다. 그리고 해당하는 컬럼과 데이터들은 index tree에 node로서 추가됩니다.

InnoDB의 기본적인 index 자료구조는 B-Tree를 사용합니다.

출처: shorturl.at/fBLXZ

사실 index tree도 full scan이 발생하는 것은 all과 같습니다. 하지만 모든 컬럼이 아닌, index tree에 추가된 데이터에 한해서 full scan이 발생하는것이 핵심인데요.

컬럼 5개, 데이터 10개, 1개의 컬럼이 index일 때를 가정합니다.

이때

• all 타입은 5 * 10 = 50개를 full scan 해야 하고,
• index 타입은 1 * 10 = 10개만 scan 하면 됩니다.

즉 똑같이 full scan을 하더라도, 조회의 대상이 현저히 줄어듭니다.

---

4) 개선

위의 index를 바탕으로 조회 성능을 개선해봅니다.

우선 업무의 상황을 재현해야 합니다. Kotlin 테스트 코드를 작성해, 무식하게 데이터를 삽입합니다.

• 책 1 ~ 5권까지 존재합니다.
• 각각 10 만권씩, 총 50만 권을 저장합니다.

4. 1) index 추가하지 않은 상태

• Explain 쿼리에 대한 결과입니다.
• Explain 결과 ALL 타입입니다.

• 약 1.8초 소요됩니다.

4. 2) inedx 추가한 상태

ALTER TABLE test.book 
ADD INDEX index_name_author (name, author);

• name, author 컬럼을 index로 추가합니다.

• Explain 쿼리에 대한 결과입니다.
• index 추가 후 ref 타입으로 변경됐습니다.
  • pk, unique 컬럼이 아니지만, index로 추가됐기 때문입니다.

• 약 0.9초 소요됩니다.
• index 추가 전과 비교했을 때 2배의 성능 개선입니다.

쿼리를 전혀 변경하지 않고, 간단히 index 추가로 성능 개선을 할 수 있습니다.

---

5) 그래서 무조건 index 추가하면 되나요?

여기서 의문이 생길 수 있습니다.

"단순히 보기에는 무조건 index 추가하면 되지 않을까"라는 생각이 들 수 있습니다. "컬럼의 수만큼 경우의 수를 따져서, 미리 다 추가해두면 되지 않을까"라는 생각도 듭니다.

아쉽게도 그렇지는 않습니다.

5. 1) Cardinality가 높아야 합니다.

Cardinality는 값의 분산도를 나타냅니다.

A를 조회했을 때 대상이 되는 종류가 100개라면, cardinality는 높습니다. 하지만 종류가 3개라면 낮습니다. 즉 테이블을 조회했을 때 얻을 수 있는 값이 얼마나 분산되어 존재하는가를 나타내는데요.

종류가 100개일 때는 index를 추가하여, 조회 대상을 줄여줄 수 있습니다. 그리고 성능 향상을 기대할 수 있는데요.

종류가 3개일 때는 cardinality가 낮습니다. 그리고 불필요하게 index를 추가하게 되면, 오히려 index tree를 추가하고 연산하는데 시간이 더 소요될 수 있습니다. 이러한 경우에는 eq_ref or ref 타입이 될 수 있게 쿼리를 개선하는 것이 더 효과적이라고 생각합니다.

5. 2) select 외에는 단점으로 작용합니다.

데이터를 Insert 하는 과정은 단순한데요.

우선 데이터베이스에서 저장할 수 있는 공간을 찾습니다. 이때 우리가 화면에서 보는 것처럼 순차적으로 저장하는 것이 아닌, 공간이 있다면 아무 곳에나 data block으로 저장됩니다.

만약 해당 컬럼에 index가 걸려있다면, 추가적인 연산이 필요합니다.

대상이 되는 데이터가 index tree에 추가되어야 합니다. 이때 반드시 순서를 유지하면서 추가되어야 합니다. 만약 아무렇게나 저장이 된다면 기존의 index tree의 순서와 밸런스는 무너지게 될 것입니다.

이게 끝이 아닙니다. 해당 공간에 여유가 있는지 확인해야 합니다. 만약 부족하다면, leaf node를 쪼갭니다. 그리고 해당 leaf node에 존재하는 node들을 다시 정렬해야 합니다.

즉 index가 많으면 많을수록 select 외의 쿼리는 성능에 악영향을 미칩니다.

그래서 대용량 데이터를 다루는 곳에서는 read, write DB를 분리하기도 합니다. read에 맞는 index와 다양한 방법을 사용하고, write에는 그에 맞는 방법을 적용할 수 있기 때문입니다.

---

6) 결론

최근 페이징 쿼리에서 성능 이슈를 겪은 후 index를 통해 해결했습니다. 결과적으로 27초 --> 0.8초로 단축할 수 있었는데요.

이번 이슈를 통해서 막연히 index가 좋다는 것을 실제로 체감할 수 있었습니다. 그뿐만 아니라 index에 대해서 좀 더 깊이 볼 수 있었던 것이 좋았습니다.

이렇게 성능 이슈를 해결할 때마다, 개발이 참 재밌다는 생각을 해봅니다.