2026-08-11,
적절한 인덱스가 생성되어 있지만 다른 인덱스를 사용하여 DB 서버에 점차 부하를 일으키고 있는 쿼리가 있었다. 해결하고나서 보니 아주 흔한 문제이지만 원인 자체는 덜 흔한 것 같아 테스트 데이터를 통해 근본 원인과 조치 방법을 공유해보려고 한다.
아래 test 테이블에는 (user_id, user_type), (user_id, test_id) 구성의 세컨더리 인덱스가 생성되어 있다.
CREATE TABLE `test` (
`test_id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`user_type` VARCHAR(10) NOT NULL DEFAULT 'A' COMMENT '(''A'',''B'',''C'',''D'',''E'')',
`user_name` char(60) NOT NULL,
`remark` char(200) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`test_id`),
KEY `ix_userid_usertype` (`user_id`, `user_type`),
KEY `ix_userid_testid` (`user_id`, `test_id` DESC)
);
테스트 데이터를 생성하기 위해 프로시저를 작성하고,
-- add_test 프로시저
DELIMITER ;;
CREATE PROCEDURE add_test(
IN p_count INT
)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= p_count DO
INSERT INTO test (
user_id,
user_type,
user_name,
remark,
created_at
)
VALUES (
FLOOR(1 + RAND() * 100000),
ELT(
1 + MOD(i - 1, 5),
'A', 'B', 'C', 'D', 'E'
),
LEFT(UUID(), 20),
CONCAT('test-', LEFT(UUID(), 20)),
NOW() - INTERVAL FLOOR(RAND() * 365) DAY
);
SET i = i + 1;
END WHILE;
END;;
DELIMITER ;
50,000건의 데이터를 INSERT 한다.
call add_test(50000);
그리고 user_id 단일 조건의 쿼리 실행계획을 확인한다.
EXPLAIN SELECT * FROM test WHERE user_id = 41293 ORDER BY test_id DESC LIMIT 4;
+----+-------------+------+-------------------------------------+--------------------+---------+-------+------+----------+----------------+
| id | select_type | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------+-------------------------------------+--------------------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | ref | ix_userid_usertype,ix_userid_testid | ix_userid_usertype | 4 | const | 6 | 100.00 | Using filesort |
+----+-------------+------+-------------------------------------+--------------------+---------+-------+------+----------+----------------+
실행계획 결과가 어떠한가. 아마 대부분은 이 쿼리가 ix_userid_testid 인덱스를 사용할거라고 예상했을 것이다. 그런데 왜 이런 결과가 나왔을까. filesort를 감내하면서까지 ix_userid_usertype 인덱스를 사용해야만 하는 이유가 있었을까.
이번엔 옵티마이저 트레이스를 살펴보도록 하자. bast_access_path를 보니 두 인덱스의 실행계획 코스트가 동일한데, chosen 필드 값이 true인 것은 ix_userid_usertype 인덱스뿐이다. 왜 일까.
...(omitted)
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "ix_userid_usertype",
"rows": 6,
"cost": 2.1,
"chosen": true
},
{
"access_type": "ref",
"index": "ix_userid_testid",
"rows": 6,
"cost": 2.1,
"chosen": false
},
{
"access_type": "range",
"range_details": {
"used_index": "ix_userid_usertype"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 6,
"cost_for_plan": 2.1,
"chosen": true
}
]
},
...(omitted)
실행계획 코스트가 동일한 경우의 tie-breaker 조건
MySQL 서버는 테이블에 인덱스가 추가될 때마다 인덱스 목록을 이중 연결 리스트 끝에 append를 하면서 관리한다(인덱스 목록을 이중 연결 리스트로 관리하는 이유는 인덱스 drop을 생각해보면 납득될 것이다). 예를 들어 특정 테이블에 100개의 인덱스를 생성하면 배열의 슬롯 번호가 1부터 100까지 순차적으로 증가하게 된다.
그런데 이렇게 순서대로 등록된 인덱스들은 쿼리의 실행계획 코스트를 계산할 때도 순서대로 평가된다. 예를 들어 위의 test 테이블은 ix_userid_usertype -> ix_userid_testid 인덱스순으로 실행계획이 평가된다.
여기서 잠깐 아래 코드를 살펴보자. 아래 코드는 쿼리의 실행계획을 생성하는 코드(sql_planner.cc)의 일부분을 가져온 것이다. 코드의 이해를 돕기 위해 현재의 실행계획 평가 시점이 test 테이블의 ix_userid_testid 인덱스를 평가할 때라고 가정한다. 인덱스 순서상 이전에 평가된 ix_userid_usertype 인덱스는 현재 시점의 인덱스를 평가하기 전까지는 best 실행계획이다.
...(omitted)
bool new_candidate = false;
if (best_found_keytype >= NOT_UNIQUE && cur_keytype >= NOT_UNIQUE)
new_candidate = cur_ref_cost < best_ref_cost; // 1
else if (best_found_keytype == cur_keytype)
new_candidate = cur_ref_cost < best_ref_cost; // 2
else if (best_found_keytype > cur_keytype)
new_candidate = true; // 3
if (new_candidate) {
*ref_depend_map = table_deps;
*used_key_parts = cur_used_keyparts;
best_ref = start_key;
best_ref_cost = cur_ref_cost;
best_found_keytype = cur_keytype;
}
...(omitted)
return best_ref;
}
3번째줄의 best_found_keytype(enum)에는 ix_userid_usertype 인덱스의 타입이 이미 담겨있다. 당연히 인덱스 타입은 세컨더리 인덱스이기 때문에 best_found_keytype >= NOT_UNIQUE 조건에 부합된다. 현재 평가 시점의 인덱스 타입 또한 세컨더리 인덱스이기 때문에 cur_keytype >= NOT_UNIQUE 조건에 부합된다. if문을 통과해서 4번째줄로 들어가면 new_candidate 값을 true로 변경하는 또 다른 조건이 보인다(cur_ref_cost < best_ref_cost). 우리는 아까 ix_userid_usertype 인덱스와 ix_userid_testid 인덱스의 실행계획 코스트가 동일하다는 것을 옵티마이저 트레이스를 통해 확인했다. 그렇다면 이전 코스트와 현재 코스트가 동일하므로 new_candidate는 false이다.
당연하게도 9번째줄의 if문 조건은 false이므로, 12번째줄의 best_ref(ix_userid_usertype)=start_key(ix_userid_testid)는 수행되지 않고 그대로 17번째줄에서 반환된다.
그리고 다음 회차에는 더이상 평가할 인덱스가 없으므로 코스트는 동일했지만 가장 먼저 평가된 ix_userid_usertype 인덱스가 최종적으로 선택된다.(ix_userid_usertype 인덱스를 drop하고 재생성하면 배열 순서에 따라 이번에는 ix_userid_testid 인덱스가 쿼리에 사용된다)
결국 세컨더리 인덱스의 실행계획 비용이 동일하면 tie-breaker 조건이 인덱스의 생성 순서가 되기 때문에 쿼리 효율과는 관계없이 먼저 생성된 ix_userid_usertype 인덱스가 쿼리에 사용되는 것이다.
이 내용을 시각화한 형태로 확인해보면 아래 동작과 같다.
이제 원인은 알았다. 그럼 쿼리는 어떻게 튜닝하면 될까.
쿼리 수정
쿼리를 튜닝할 수 있는 방법은 2가지이다.
첫 번째는 쿼리 힌트로 인덱스를 사용하도록 유도하는 방법이고,
SELECT /*+ INDEX(t ixx_userid_testid) */
*
FROM test AS t
WHERE user_id = 41293
AND user_type <> ''
ORDER BY test_id DESC
LIMIT 4;
두 번째는 성능에 영향이 없는 쿼리 조건을 추가하는 방법이다. 성능에 영향이 없는 쿼리 조건을 추가하는 이유는 조건절을 추가해서 실행계획 코스트의 변경을 최소로 유도하기 위함이다.
SELECT *
FROM test
WHERE user_id = 41293
AND user_type <> ''
ORDER BY test_id DESC
LIMIT 4;
첫 번째 방법을 사용한다면 쿼리 힌트에 사용될 인덱스명을 ixx_userid_testid와 같이 명시적으로 구분하는 것을 권장한다. 이 인덱스는 쿼리 힌트에도 사용되고 있어요 라는 것을 명시적으로 설정해놓으면 테이블 스키마만 보고도 이 테이블은 쿼리 힌트를 사용하는 서비스 쿼리가 존재하는구나를 알 수 있다. 별거 아닌 것 같지만 이 작은 노력이 누군가가 실수할 확률을 1%라도 줄여줄 것이다.
두 번째 방법을 사용한다면 애플리케이션 코드 또는 쿼리 주석에 히스토리를 적어두는 것을 권장한다. 단순 쿼리만 봤을 때 없어도 될 것 같은 where절 조건이 추가되었기 때문에 히스토리를 모르는 누군가는 해당 조건을 제거해서 배포해버릴지도 모른다. 혹 추가된 이유를 묻는다 할지라도 전부 커뮤니케이션 비용이다.
우리가 최소의 노력만 기울이면 DB 부하나 장애가 발생했을 때 최대한의 정보를 얻을 수 있다는 점을 명시하자.
댓글 남기기