복합 인덱스(Composite Index)를 활용한 쿼리 최적화 (Full Scan → Range Scan)

개요

이전 단계에서 Application Level의 데이터 매핑(Map 활용)을 통해 N+1 문제를 해결하여 쿼리 발생 횟수를 획기적으로 줄였다.
하지만, 근본적인 DB 조회 시의 비효율(Full Table Scan)은 여전히 존재한다.

대량의 더미 데이터를 생성하여 성능 저하 원인을 분석하고, 복합 인덱스(Composite Index)를 적용하여 조회 방식이 Full Table Scan에서 Index Range Scan으로 개선되는 과정을 검증한다.

테스트 환경 구축

성능 차이를 확인하기 위해서 10만 건의 더미 데이터를 생성한다.

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_dummy_todos$$

CREATE PROCEDURE insert_dummy_todos()
BEGIN
    DECLARE i INT DEFAULT 1;
    SET autocommit = 0; 
    
    WHILE i <= 100000 DO
        INSERT INTO todo (user_id, title, scheduled_date, is_done, status, created_at, updated_at)
        VALUES (
            UNHEX(REPLACE(UUID(), '-', '')), 
            CONCAT('Load Test Todo ', i),
            DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 30) DAY), 
            0,
            'ACTIVE',
            NOW(),
            NOW()
        );
        
        SET i = i + 1;
        
        IF i % 1000 = 0 THEN 
            COMMIT; 
        END IF;
    END WHILE;
    
    COMMIT;
    SET autocommit = 1;
END$$

DELIMITER ;

CALL insert_dummy_todos();

인덱스 적용해보기

mysql> EXPLAIN SELECT * FROM todo
    -> WHERE user_id = UNHEX('8FE4708B4C4D404D92AA614ECBE705CD')
    ->   AND scheduled_date BETWEEN '2025-01-01' AND '2025-01-31'
    ->   AND status = 'ACTIVE';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | todo  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100053 |     0.56 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

더미데이터를 넣고 EXPLAIN을 실행시켜본 결과 역시 Full Table Scan(type: ALL)이 발생한다.
10만건을 모두 꺼내오다보니 매우 많은 행을 반환한 것을 볼 수 있다.

이제 인덱스를 적용해보려는데 예상치 못한 결과가 발생하여 정리한다.

트러블슈팅: 인덱스가 적용되지 않는 현상 (Optimizer의 선택)

-- userId(동등) + scheduledDate(범위) 복합 인덱스
CREATE INDEX idx_user_todo_date ON todo (user_id, scheduled_date);

mysql> EXPLAIN SELECT * FROM todo
    -> WHERE user_id = UNHEX('8FE4708B4C4D404D92AA614ECBE705CD')
    ->   AND scheduled_date BETWEEN '2025-01-01' AND '2025-01-31';
+----+-------------+-------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys      | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | todo  | NULL       | ALL  | idx_user_todo_date | NULL | NULL    | NULL | 100053 |    50.00 | Using where |
+----+-------------+-------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.1 문제 상황

8FE4708B4C4D404D92AA614ECBE705CD 의 데이터를 조회하기 위해 복합 인덱스를 생성했지만, EXPLAIN 결과 여전히 Full Table Scan이 발생한다.

• type: ALL, rows: 100053, key: NULL

3.2 원인 분석: 선택도(Selectivity) 문제

테스트 편의를 위해 10만 건의 데이터를 모두 동일한 유저(8FE4708B4C4D404D92AA614ECBE705CD)로 업데이트했던 것이 원인이다.

테이블에 있는 10만 개 모두 다 이 유저꺼니까 인덱스 책갈피를 왔다갔다 하느니, Full Scan이 더 빠르다고 옵티마이저가 인식하는 것이다.
그리 하여 옵티마이저가 자동으로 인덱스를 무시한 것이다.

3.3 해결: 데이터 분포 정상화

모두 똑같은 유저로 되어 있는 데이터를 "대부분은 남의 데이터, 2000개만 내 데이터"로 바꾼다.

SET SQL_SAFE_UPDATES = 0;

-- 1. 일단 10만 개 전부 랜덤한 유저로 초기화 (남의 데이터 만들기)
UPDATE todo
SET user_id = UNHEX(REPLACE(UUID(), '-', ''));

-- 2. 앞쪽 2,000개만 '내 데이터'로 설정 (이걸 조회할 것임)
UPDATE todo
SET user_id = UNHEX('8FE4708B4C4D404D92AA614ECBE705CD')
WHERE id <= 2000;

SET SQL_SAFE_UPDATES = 1;

4. 성능 개선 검증 (Before & After)

데이터 분포를 정상화한 후, 인덱스 유무에 따른 실행 계획을 비교했다.

4.1 개선 전 (Index 미적용)

mysql> DROP INDEX idx_user_todo_date ON todo;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM todo
    -> WHERE user_id = UNHEX('8FE4708B4C4D404D92AA614ECBE705CD')
    ->   AND scheduled_date BETWEEN '2025-01-01' AND '2025-01-31';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | todo  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99997 |     1.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

결과를 보면 인덱스는 현재 미적용했고 테이블 전체인 99997건을 모두 스캔한 것을 볼 수 있다.
type: ALL인 것 까지 확인할 수 있다.

4.2 개선 후 (Composite Index 적용)

다시 인덱스를 걸고 확인한다.

mysql> CREATE INDEX idx_user_todo_date ON todo (user_id, scheduled_date);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM todo
    -> WHERE user_id = UNHEX('8FE4708B4C4D404D92AA614ECBE705CD')
    ->   AND scheduled_date BETWEEN '2025-01-01' AND '2025-01-31';
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | todo  | NULL       | range | idx_user_todo_date | idx_user_todo_date | 19      | NULL | 1465 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

• Before: rows: 99997
• After: rows: 1465
• 효율: 약 68배 덜 읽었다. (데이터가 더 많아지면 격차는 더 벌어짐)

Full Scan(ALL)에서 Range Scan(range)로 변경되었고 스캔 대상도 대폭 감소했다.

5. 결과 분석 및 결론

5.1 정량적 지표 비교

구분	Type (스캔 방식)	Rows (스캔 행 수)	효율 개선
개선 전	ALL (Full Table Scan)	99,997	-
개선 후	range (Index Range Scan)	1,465	약 68배 감소

5.2 실행 시간(Latency)에 대한 고찰

로컬 테스트 결과 실행 시간 차이는 약 3ms 내외로 미미했다.

1. 메모리(RAM) 캐싱: 테스트 데이터(10만 건)의 크기가 작아 DB가 이미 메모리에 적재한 상태이다.
2. 단일 사용자 환경: CPU 경합이 없는 상태에서는 10만 번의 연산도 순식간에 처리된다.

5.3 최종 결론

단건 테스트의 시간 차이는 적었으나, EXPLAIN을 통해 스캔해야 할 데이터 행(Rows)이 98% 이상 감소했음을 입증했다.