[yoonnsshop] Database Index 활용

yoonn·2024년 8월 1일

yoonnsshop

목록 보기

4/7

Intro

데이터베이스 성능 최적화는 현대 애플리케이션 개발에서 핵심적인 부분입니다. 특히 데이터 양이 증가함에 따라 조회 속도 개선은 더욱 중요해지고 있습니다. 이번 포스팅에서는 데이터베이스 인덱스가 어떻게 조회 성능을 극적으로 향상시킬 수 있는지 실제 예제를 통해 살펴보겠습니다. MySQL 8.4.0을 기반으로 한 몇가지 시나리오에서 인덱스의 효과를 분석하고, 단일 인덱스부터 복합 인덱스, 그리고 조인 쿼리에서의 인덱스 사용까지 폭넓게 다룰 예정입니다.

시나리오


Title	Database Index
Trigger	상품 데이터를 대량으로 삽입
Problem	상품 데이터가 대량으로 증가해서 조회 시간이 오래 걸리는 문제 발생
Action	자주 사용되는 쿼리의 조건절에 사용되는 컬럼에 인덱스를 생성한다.
Benefit	조회 속도 향상

Database index?

데이터베이스 테이블에서 데이터 검색 작업의 속도를 향상시키는 데이터 구조.
인덱스 데이터 구조를 유지하기 위해 쓰기 및 저장 공간을 추가로 확보하는 대신 데이터 검색 속도를 향상시킨다.

Index 성능 비교 분석

Index 적용 유무 비교

Index가 적용되지 않은 쿼리의 실행 결과를 분석해보겠습니다

shop> SELECT * FROM order_items WHERE order_id = 1
[2024-06-18 16:34:09] 4 rows retrieved starting from 1 in 19 s 848 ms (execution: 19 s 828 ms, fetching: 20 ms)

현재 order_items는 order_id에 대한 index를 가지고 있지 않아서 order_id = 1인 4개의 데이터를 조회하기 위해 전체 테이블 스캔(Full Table Scan)이 수행되어 약 270만개 rows가 스캔 대상이 됩니다. 결과적으로 19.8s가 소요되었습니다.

그렇다면 order_id에 Index가 적용된 쿼리의 결과는 어떨까요?

shop> SELECT * FROM order_items WHERE order_id = 1
[2024-06-18 16:27:22] 4 rows retrieved starting from 1 in 27 ms (execution: 6 ms, fetching: 21 ms)

인덱스 적용 후, 쿼리 실행에 걸린 시간은 단 6ms였습니다. 이는 전체 응답 시간 27ms 중 실제 데이터베이스 작업에 소요된 시간입니다.

비교해보자면 19.8s가 6ms로 감소했습니다. 이는 약 99.97%의 성능 개선을 나타냅니다. 적절한 인덱스 사용은 쿼리 실행 속도를 현저히 향상시킬 수 있습니다.

Merge Index

위에서는 단일 Index에 대해서 확인해보았습니다. order_items는 2개의 index를 가지고 있습니다. order_id와 item_id입니다. 두 컬럼에 대한 조건을 한 번에 넣어주겠습니다. 결과는 어떻게 될까요?

shop> SELECT * FROM order_items
      WHERE order_id = 1
      AND  item_id = 822253
[2024-06-25 17:15:52] 4 rows retrieved starting from 1 in 81 ms (execution: 10 ms, fetching: 71 ms)

type은 index_merge로 잡힌 것을 확인할 수 있습니다. index_merge는 단일 테이블 인덱스 스캔만을 병합합니다. 각 단일 인덱스로부터 추출된 인덱스 스캔 결과를 별도의 메모리 공간에 저장하고 결과의 교집합을 찾는 방식입니다. 따라서, 카디널리티가 아주 작은 컬럼이거나 인덱스로 추려낸 데이터가 너무 많으면 굉장히 비효율적인 방식이 될 수도 있을것으로 보입니다.

🍋 Explain의 rows
위에서 Explain을 보고 쿼리 실행 시, 옵티마이저가 어떻게 행동할 지를 들여다볼 수 있었습니다. explain에 보여지는 rows는 실제 쿼리 결과 조회되는 row수와 달랐습니다. (FUll Scan이라고해서 전체 rows를 정확히 반환하는 것이 아니며, Merge Index에서 실제 조회 결과는 4건이지만 EXPLAIN 결과는 rows가 1로 보여졌습니다.) 그렇다면 왜 차이가 나는걸까요?
옵티마이저는 테이블과 인덱스 통계 정보를 기반으로 rows 값을 추정하는데, 이 통계 정보는 항상 정확하지 않습니다. 실제 rows에 대한 연산을 통해 반환되는 값이 아닌 인덱스 선택도(selectivity)를 고려해 추정된 값이기 때문입니다.

Join과 Index

Join 연산을 포함한 쿼리에서 인덱스를 효과적으로 활용하면 큰 성능 개선을 얻을 수 있습니다.

먼저, 모든 컬럼을 선택하는 쿼리를 살펴봅니다.

EXPLAIN SELECT * FROM order_items
JOIN orders ON orders.order_id = order_items.order_id
WHERE orders.member_id = 1;

+--+-----------+-----------+----------+----+-----------------------------------+---------------------------+-------+--------------------+------+--------+-----+
|id|select_type|table      |partitions|type|possible_keys                      |key                        |key_len|ref                 |rows  |filtered|Extra|
+--+-----------+-----------+----------+----+-----------------------------------+---------------------------+-------+--------------------+------+--------+-----+
|1 |SIMPLE     |orders     |null      |ref |PRIMARY,FK2vq7lo4gkknrmghj3rqpqqg6s|FK2vq7lo4gkknrmghj3rqpqqg6s|9      |const               |161820|100     |null |
|1 |SIMPLE     |order_items|null      |ref |FKbioxgbv59vetrxe0ejfubep1w        |FKbioxgbv59vetrxe0ejfubep1w|9      |shop.orders.order_id|2     |100     |null |
+--+-----------+-----------+----------+----+-----------------------------------+---------------------------+-------+--------------------+------+--------+-----+

이 실행 계획에서는 orders 테이블에 대해 별도의 인덱스 사용이 명시되지 않았습니다.

그리고 이제는 필요한 컬럼만 선택하도록 쿼리를 수정합니다. 현재는 order_items의 데이터만 있으면 되는 상황이라는 가정입니다.

EXPLAIN SELECT order_items.* FROM order_items JOIN orders ON orders.order_id = order_items.order_id WHERE orders.member_id = 1;

+--+-----------+-----------+----------+----+-----------------------------------+---------------------------+-------+--------------------+------+--------+-----------+
|id|select_type|table      |partitions|type|possible_keys                      |key                        |key_len|ref                 |rows  |filtered|Extra      |
+--+-----------+-----------+----------+----+-----------------------------------+---------------------------+-------+--------------------+------+--------+-----------+
|1 |SIMPLE     |orders     |null      |ref |PRIMARY,FK2vq7lo4gkknrmghj3rqpqqg6s|FK2vq7lo4gkknrmghj3rqpqqg6s|9      |const               |161820|100     |Using index|
|1 |SIMPLE     |order_items|null      |ref |FKbioxgbv59vetrxe0ejfubep1w        |FKbioxgbv59vetrxe0ejfubep1w|9      |shop.orders.order_id|2     |100     |null       |
+--+-----------+-----------+----------+----+-----------------------------------+---------------------------+-------+--------------------+------+--------+-----------+

이 실행 계획에서 주목할 점은 orders 테이블에 대해서 Extra: Using index 가 표시된 것입니다. 이는 쿼리 실행 시 인덱스만을 사용해 필요한 데이터를 가져온다는 의미입니다.

shop> SELECT * FROM order_items JOIN orders ON orders.order_id = order_items.order_id WHERE orders.member_id = 1
[2024-07-30 11:41:23] 1,262,640 rows retrieved starting from 1 in 1 m 35 s 717 ms (execution: 15 ms, fetching: 1 m 35 s 702 ms)

shop> SELECT order_items.* FROM order_items JOIN orders ON order_items.order_id = orders.order_id WHERE orders.member_id = 1
[2024-07-30 12:38:59] 1,262,640 rows retrieved starting from 1 in 54 s 897 ms (execution: 14 ms, fetching: 54 s 883 ms)

쿼리 실제 시간을 비교해보면 실제로 1m 35s -> 54s로 약 40% 더 빠른 실행 시간을 보여줍니다.

Conclusion

이번 포스트를 통해 데이터베이스 인덱스의 중요성과 그 효과에 대해 상세히 살펴보았습니다. 주요 내용을 요약하면 다음과 같습니다:

적절한 인덱스 사용으로 쿼리 실행 시간을 19.8s에서 6ms로 단축, 약 99.97%의 성능 개선을 달성했습니다.
복합 조건에서의 인덱스 병합(index merge) 동작 방식을 확인했습니다.
EXPLAIN 명령어를 통해 쿼리 실행 계획을 분석하는 방법을 익혔습니다.
조인 쿼리에서 인덱스 활용 시 약 40%의 성능 향상을 확인했습니다.

이러한 결과는 데이터베이스 설계 시 인덱스 전략의 중요성을 잘 보여줍니다. 하지만 인덱스가 항상 최선의 해결책은 아니며, 데이터의 특성과 쿼리 패턴을 고려한 신중한 설계가 필요합니다. 데이터베이스 구조를 설계할 때 초기 단계부터 효율적인 인덱스 설계를 고려하는 것이 성능 최적화에 크게 기여할 수 있습니다.

Reference

database index: https://en.wikipedia.org/wiki/Database_index

yoonn

이전 포스트