Index를 활용한 10만건의 레코드 Join쿼리를 최적화하자

 

마이페이지에서 유저가 발급받은 쿠폰 이력을 조회하는 기능을 구현해야 했습니다 

해당 기능 구현 시 쿼리 최적화를 고려했던 과정을 포스팅 해보고자 합니다.

 

📌 무조건 쿼리 속도가 빠르면 되는 걸까?

쿼리 최적화를 고려한 기능을 만들면서 궁금한 점이 생겼습니다.

• 어떤걸 기준으로 쿼리 최적화가 됐다 라고 하는 것일까?
• 레코드 건수가 제일 작은게 드라이빙 테이블로 되는 것 같은데, 각 테이블 당 레코드 건수를 몇개를 넣어야 하는 것일까?

실행 계획을 보면서 인덱스를 이것저것 설정해보니, 속도는 빠르지만 ALL인 상황, 속도는 느리지만 index인 상황, filtered가 높아지거나 낮아지거나 등 여러 가지 상황에 따라 실행 계획이 바뀌는 것 같습니다. 그래서 처음에는 어떤 걸 기준으로 쿼리가 최적화되었다라고 봐야 할지 이해가 가지 않았습니다.

 

우선 쿼리 최적화를 하기 전에 쿼리 튜닝이라는 행위를 "왜"하는 것인지 먼저 생각해봐야 합니다.

DB의 자원이 무한하지 않기 때문에 쿼리 튜닝이라는 행위가 필요합니다.

실행 계획을 보고 분석하는 것은 해당 쿼리가 문제가 있는지 확인하거나 or 문제가 있기 때문입니다.

 

일반적으로 쿼리가 느리기 때문에 개선 대성으로 잡히는 경우가 많습니다.

 

쿼리 튜닝은 동일한 DB에서 동일한 데이터를 조회하지만 수행속도가 빨라지는 것을 의미합니다.

DB는 쿼리 하나하나 마다 많은 리소스를 투여합니다. 

별도의 블로킹이 걸리는 작업이 많지 않기 때문에 아래와 같은 상황이 됩니다.

쿼리 수행 시간이 오래 걸린다 = DB의 리소스를 많이 쓴다

 

즉, 오래 걸리는 쿼리 일 수록 DB의 리소스(CPU, 메모리 등)을 많이 쓰게 되는 것이고 이것을 개선하는 걸 목표로 해야 합니다.

 

실행 계획에서 풀 테이블 스캔이 나오더라도 쿼리 수행 속도가 빠르다면 아무런 문제가 없습니다.

실행 계획에서 ref가 나오더라도 쿼리 수행 속도가 느리다면 개선 대상입니다.

 

즉, 실행 계획 자체를 바꾸는걸 목적으로 두지 말고 우선 데이터를 많이 넣고 join문을 복잡하게 만들어서 "Slow query"를 만든 후 개선하는 걸 목적으로 하는게 좋습니다.

 

📌 사전 준비

쿠폰 이력을 조회하는 API는 아래와 같은 Json응답으로 반환하려고 합니다.

{
    "status": "SUCCESS",
    "data": [
        {
            "couponId": 1,
            "category": "바디케어",
            "description": "바디케어 전품목 이벤트",
            "discount": "50% 할인",
            "validateStartDate": "2024-03-01T00:00:00",
            "validateEndDate": "2024-03-30T00:00:00",
            "couponStatus": "NOT_ACTIVE"
        },
        ....
        {
            "couponId": 2,
            "category": "주방용품",
            "description": "주방용품 전품목 이벤트",
            "discount": "5000원 할인",
            "validateStartDate": "2024-03-01T00:00:00",
            "validateEndDate": "2024-03-30T00:00:00",
            "couponStatus": "NOT_ACTIVE"
        }
    ],
    "message": "요청 성공"
}

 

현재의 데이터 모델링으로 관련된 칼럼을 모두 조회하기 위해서는 3개의 테이블을 조인해야 했습니다.

 

DDL쿼리와 Primary Key로 인해 생성된 인덱스는 아래와 같습니다.

 

쿠폰 이력 테이블

CREATE TABLE coupon_issue
(
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '쿠폰 발행 기록',
    member_id       BIGINT UNSIGNED NOT NULL COMMENT '쿠폰 ID',
    coupon_id       BIGINT UNSIGNED NOT NULL COMMENT '회원 ID',
    coupon_status              VARCHAR(50)     NOT NULL DEFAULT 'NOT_ACTIVE' COMMENT '유효일 전 : NOT_ACTIVE / 유효기간 : ACTIVE / 사용완료 : USED / 만료 : EXPIRED',
    created_at      DATETIME        NOT NULL COMMENT '데이터 생성일',
    updated_at      DATETIME        NOT NULL COMMENT '데이터 변경일',
    check_related_issued_quantity  BOOLEAN DEFAULT FALSE COMMENT '쿠폰 발행시 발행량 체크 여부'
);

 

쿠폰 테이블

CREATE TABLE coupon
(
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '쿠폰 식별자',
    event_id            BIGINT UNSIGNED NULL COMMENT '이벤트 식별자 / NULL일 경우 이벤트와 관련 없는 쿠폰(e.g. 회원가입 쿠폰)',
    discount_type       VARCHAR(40)     NOT NULL COMMENT '정액, 정률 등',
    discount_rate       BIGINT UNSIGNED NULL COMMENT '정률 할인',
    discount_price      BIGINT UNSIGNED NULL COMMENT '정액 할인',
    coupon_type         VARCHAR(50)     NOT NULL COMMENT '선착순 쿠폰, 회원가입 쿠폰 등..',
    max_quantity        BIGINT UNSIGNED NULL COMMENT '무제한 발행일 경우 NULL',
    issued_quantity     BIGINT UNSIGNED NULL COMMENT '무제한 발행일 경우 NULL',
    validate_start_date DATETIME        NOT NULL COMMENT '모든 쿠폰은 유효 시간이 있어야한다는 제약 사항 존재',
    validate_end_date   DATETIME        NOT NULL COMMENT '모든 쿠폰은 유효 시간이 있어야한다는 제약 사항 존재',
    created_at          DATETIME        NOT NULL COMMENT '데이터 생성일',
    updated_at          DATETIME        NOT NULL COMMENT '데이터 변경일'
);

 

이벤트 테이블

CREATE TABLE event
(
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '이벤트 식별자',
    category               VARCHAR(100) NOT NULL COMMENT '이벤트 카테고리',
    description            VARCHAR(255) NOT NULL COMMENT '이벤트 설명',
    start_date             DATE         NULL COMMENT '이벤트 시작일 / null일 경우 무제한 이벤트',
    end_date               DATE         NULL COMMENT '이벤트 종료일 / null일 경우 무제한 이벤트',
    daily_issue_start_time VARCHAR(20)  NULL COMMENT '당일 쿠폰 발행 시작시간 e.g. "13:00:00"/ null일 경우 무한 발행',
    daily_issue_end_time   VARCHAR(20)  NULL COMMENT '당일 쿠폰 발행 종료시간 e.g. "15:00:00" / null일 경우 무한 발행',
     created_at DATETIME NOT NULL COMMENT '데이터 생성일',
     updated_at DATETIME NOT NULL COMMENT '데이터 변경일'
);

 

위에서 언급했던 Json응답에 데이터를 담기 위해서 어떤 쿼리가 필요한지 고민했해봤고 데이터가 적을때 기준으로 10번의 시도 중 평균적으로 제일 낮은 2번 쿼리를 사용하는 걸로 선택했습니다.

 

테스트를 위해 각 테이블에 csv파일을 이용해서 10만건의 레코드를 INSERT해줬습니다.

• coupon 테이블 : 10만건
• event 테이블 : 10만건
• coupon_issue 테이블  : 10만건

 

레코드 10만건을 채워야 하는 이유는 무엇일까요?

 

테스트하는 데이터 양이 실제 운영 환경과 유사해야 하고, 레코드가 적으면 실행 계획이나 성능에 대한 신뢰성이 떨어집니다.

왜냐하면 인덱스를 설정해도 Full Table Scan을 하게 되고, 인덱스를 사용하는 것보다 그게 더 빠르기 때문입니다.

 

일반적으로 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업으로 예측하기 때문입니다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블의 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리합니다.

 

 

본격적으로 테스트하기 전에 이번 포스팅에서 실행 계획의 칼럼들 중 어떤 내용을 위주로 살펴볼 것인지 간단하게 알아보겠습니다.

 

• type칼럼 : 각 테이블의 접근 방법으로 어떤 방식으로 레코드를 읽었는지 나타낸다. (차례대로 빠른 순서)
  • eq_ref : 여러 테이블이 조인되는 쿼리 실행계획에서만 표시된다
    • 조인에서 처음 읽은 칼럼 값을 이용해 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼으로 동등(Equal)조건 검색할 때 사용된다.
    • 두 번째 테이블은 반드시 1건의 레코드만 반환
    • where e.id = c.event_id
  • ref : 조인의 순서와 인덱스의종류와 관계 없이 동등(Equal)조건으로 검색할 때 사용된다.
    • 1건의 레코드만 반환된다는 보장이 없다.
    • where c.member_id = 1
  • range : 인덱스 레인지 스캔 형태의 접근 방법이다.
    • 인덱스를 하나의 값이 아닌 범위로 검색하는 경우를 의미한다.
    • 주로 "<, > , IS NULL, BETWEEN, IN ,LIKE"등의 연산자를 이용해 인덱스를 검색할때 사용된다.
    • 애플리케이션 쿼리에서 가장 많이 사용되는 접근 방법으로, 이 접근 방법만 사용해도 최적의 성능이 보장된다.
    • where ci.created_at BETWEEN '2024-01-01' AND '2024-03-01'
  • index : 인덱스를 처음부터 끝가지 읽는 인덱스 풀 스캔을 의미한다.
    • 인덱스의 필요한 부분만 읽는 range 접근방식과 달리 효율적으로 인덱스를 사용하지 않는다.
    • 테이블을 처음부터 끝가지 읽는 풀 테이블 스캔 방식과 비교했을 때 레코드 건수는 같지만,
      • 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔 시 풀 테이블 스캔보다 빠르게 처리 된다.
      • 쿼리 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있다.
    • ORDER BY created_at DESC LIMIT 10
      • 처음부터 끝까지 index를 읽는 방식이지만, limit조건이 있기 때문에 효율적이다 
  • ALL  : 테이블을 처음부터 끝까지 전부 읽는 풀 테이블 스캔 방식이다.
    • 풀테이블 스캔이나 인덱스 풀 스캔 과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어들이는 Read Ahead기능을 제공한다.
    • 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니다.

 

📌 인덱스가 없을때 쿼리 속도

 

해당 쿼리 실행해보면0.078sec이 나왔습니다.

 

EXPLAIN ANALYZE를 사용하면 쿼리가 실제로 어떻게 처리되는지 그리고 소요 시간이 얼마인지를 볼 수 있습니다.

(actual time=11..32.8)의 경우 11밀리초에서 32.8밀리초 사이의 시간이 소요되었음을 의미합니다.

mysql> explain SELECT e.category, e.description, c.discount_type, c.discount_rate, c.discount_price, c.validate_start_date, c.validate_end_date, ci.coupon_status FROM coupon_issue ci JOIN coupon c on ci.coupon_id = c.id JOIN event e on c.event_id = e.id WHERE ci.member_id = 1;
mysql> explain analyze SELECT e.category, e.description, c.discount_type, c.discount_rate, c.discount_price, c.validate_start_date, c.validate_end_date, ci.coupon_status FROM coupon_issue ci JOIN coupon c on ci.coupon_id = c.id JOIN event e on c.event_id = e.id WHERE ci.member_id = 1;

 

 

쿼리 실행 순서과 실행 계획의 분석은 아래와 같습니다.

 

1. coupon_issue테이블에서 member_id가 1인 레코드를 선택합니다.
   • 실행계획에서 coupon_issue테이블이 먼저 나타는데, 이는 조인에서 사용되는 첫 번째 테이블인 드라이빙 테이블로서 사용되었고, 그 결과 집합에 다른 테이블을 조인합니다.
   • 서브쿼리를 사용해서 데이터를 필터링한 결과를 기반으로 나머지 테이블과 조인합니다.
   • coupon_issue테이블은 적절한 인덱스가 없기 때문에 풀 테이블 스캔이 발생했습니다(type=ALL)
2. 해당하는 coupon_id를 가진 레코드를 coupon테이블에서 찾습니다
   • coupon테이블은 드라이빙 테이블과 조인되는 드리븐 테이블입니다.
   • coupon테이블은 eq_ref타입으로 접근되고 있으며, Primary키를 사용해 조인을 수행하고 있습니다.
3. 이에 해당하는 event정보를 가져옵니다.
   • 조인 버퍼(해시 조인)을 사용하여coupon 테이블과 event테이블을 조인하고 있습니다.
   • event테이블은 적절한 인덱스가 없기 때문에 풀 테이블 스캔이 발생했습니다(type=ALL)

 

해당 쿼리에서 coupon_issue테이블을 조회할때 Full Table scan으로 접근하는데, member_id가 1일 레코드를 찾기 위해 테이블을 처음부터 끝까지 읽고 있습니다. 여기서 member_id에 인덱스를 걸어주면 어떻게 될까요?

 

📌 인덱스가 있을때 쿼리 속도

mysql> ALTER TABLE coupon_issue ADD INDEX idx_member_id (member_id);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from coupon_issue;
+--------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| coupon_issue |          0 | PRIMARY       |            1 | id          | A         |       99955 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| coupon_issue |          1 | idx_member_id |            1 | member_id   | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

 

인덱스를 설정한 상태에서 다시 쿼리를 날려주면 0.030sec의 속도가 걸린 것을 확인할 수 있습니다.

 

실행계획에서는 coupon_issue테이블이 ALL에서 ref로 접근방식이 바뀌었고, ref의 칼럼값은 const로 변경되었습니다.

• 효율적인 접근 방식 : ref접근 방식은 인덱스를 사용해 테이블을 검색하기때문에 Full table scan보다 훨씬 빠르며, where절 족너에 해당하는 레코드를 빠르게 찾을 수 있습니다.
• 상수 조건 사용 : const조건은 검색 시 상수 값으로 이미 알려진 조건을 했기 때문에 빠르게 인덱스를 사용해 찾을 수 있습니다.
• filterd : 기존 10%에서 100%로 변경된 것을 보아 인덱스를 효율적으로 사용했다는 것을 의미합니다.

mysql> explain SELECT e.category, e.description, c.discount_type, c.discount_rate, c.discount_price, c.validate_start_date, c.validate_end_date, ci.coupon_status FROM coupon_issue ci JOIN coupon c on ci.coupon_id = c.id JOIN event e on c.event_id = e.id WHERE ci.member_id = 1;
mysql> explain analyze SELECT e.category, e.description, c.discount_type, c.discount_rate, c.discount_price, c.validate_start_date, c.validate_end_date, ci.coupon_status FROM coupon_issue ci JOIN coupon c on ci.coupon_id = c.id JOIN event e on c.event_id = e.id WHERE ci.member_id = 1;

 

 

Analyze에서도 최종적으로 걸린 실제 실행 시간은 0.895ms에서 0.0808ms로 이전보다 약 10배 이상 성능이 개선되었습니다.

 

그러면 실제로 여러번 테스트했을때 평균적으로도 인덱스 설정 했을때와 안했을때의 차이를 비교해보겠습니다.

우선 현재는 coupon_issue테이블에 인덱스를 걸어주었기 때문에, 인덱스가 없는 coupon_issue_no_idx 테이블을 만들고 똑같이 10만건의 레코드를 채워주겠습니다.

 

📌 인덱스 생성 전 후 쿼리 실행 시간 비교

@Test
@DisplayName("[쿼리 실행 시간 측정] 인덱스 있는 쿼리 실행 시간 비교")
public void testQueryWithIndex() throws SQLException {
    for (int i = 0; i < queryCount; i++) {
        long startTime = System.currentTimeMillis(); // 시작 시간 기록
        try (Statement statement = connection.createStatement()) {
            String sql = "SELECT e.category, e.description, c.discount_type, c.discount_rate, c.discount_price, c.validate_start_date, c.validate_end_date, ci.coupon_status FROM coupon_issue ci JOIN coupon c on ci.coupon_id = c.id JOIN event e on c.event_id = e.id WHERE ci.member_id = 1;";
            ResultSet resultSet = statement.executeQuery(sql);
            // 쿼리 실행 결과 사용하지 않음 (예제에서는 단순 실행 시간 측정 목적)
        }
        long endTime = System.currentTimeMillis(); // 종료 시간 기록
        long executionTime = endTime - startTime; // 실행 시간 계산
        totalExecutionTimeWithIndex += executionTime; // 총 실행 시간 누적
        System.out.println("Query with index " + (i + 1) + ": Execution time " + executionTime + " milliseconds");
    }
}

@Test
@DisplayName("[쿼리 실행 시간 측정] 인덱스 없는 쿼리 실행 시간 비교")
public void testQueryWithoutIndex() throws SQLException {
    for (int i = 0; i < queryCount; i++) {
        long startTime = System.currentTimeMillis(); // 시작 시간 기록
        try (Statement statement = connection.createStatement()) {
            String sql = "SELECT e.category, e.description, c.discount_type, c.discount_rate, c.discount_price, c.validate_start_date, c.validate_end_date, ci.coupon_status FROM coupon_issue_no_idx ci JOIN coupon c on ci.coupon_id = c.id JOIN event e on c.event_id = e.id WHERE ci.member_id = 1;";
            ResultSet resultSet = statement.executeQuery(sql);
            // 쿼리 실행 결과 사용하지 않음 (예제에서는 단순 실행 시간 측정 목적)
        }
        long endTime = System.currentTimeMillis(); // 종료 시간 기록
        long executionTime = endTime - startTime; // 실행 시간 계산
        totalExecutionTimeWithoutIndex += executionTime; // 총 실행 시간 누적
        System.out.println("Query without index " + (i + 1) + ": Execution time " + executionTime + " milliseconds");
    }
}

@Test
@DisplayName("[평균 실행 시간 계산] 인덱스 생성 전후 쿼리 실행 시간 비교")
public void testAverageExecutionTime() {
    // 평균 실행 시간 계산
    double averageExecutionTimeWithIndex = (double) totalExecutionTimeWithIndex / queryCount;
    double averageExecutionTimeWithoutIndex = (double) totalExecutionTimeWithoutIndex / queryCount;
    System.out.println("Average execution time with index: " + averageExecutionTimeWithIndex + " milliseconds");
    System.out.println("Average execution time without index: " + averageExecutionTimeWithoutIndex + " milliseconds");
    assertTrue(averageExecutionTimeWithIndex < averageExecutionTimeWithoutIndex);
}

 

총 10번의 쿼리를 날렸을때 인덱스를 사용한 테이블이 훨씬 더 빨랐고, 평균적으로 약 19.6배가 개선되었습니다.

• 인덱스를 사용하지 않은 경우
  • 평균 실행 시간 : 19.6ms
• 인덱스를 사용한 경우
  • 평균 실행 시간 : 1ms

 

이번 시간에는 간단한 쿼리로 인덱스 적용에 대한 기본기를 읽혔지만 다음 기능에서 집계함수를 활용한 복잡한 join쿼리를 만들어서 인덱스 적용 시 얼만큼의 성능 개선이 이루어질 수 있는지 포스팅하겠습니다.