최근에 MySQL 서버에서 수행되는 쿼리 중에 특이하게 수행되는 쿼리를 하나 발견했다. 실행계획을 수립하는데만 몇 초가 걸리는 아주 특이한 쿼리였다.
- 테이블
- 1.2TB 크기의 테이블
- 레코드 건 수는 약 7억건 정도
- 날짜 레인지 파티셔닝
- DB 서버
- CPU는 48 Core
- Memory는 384GB이고
- QPS는 Writer 500(sec), Reader 10(sec)
- 버퍼풀은 276GB
- CPU 사용률은 0~1% (트래픽 전환 예정이라서 낮은 상태이다)
## 테이블 스키마
CREATE TABLE `heavy_tbl` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint DEFAULT NULL,
`type` enum('Apple','Banana','Tomato') NOT NULL DEFAULT 'Apple',
`title` varchar(255) DEFAULT NULL,
...
...
...omitted(컬럼 총 67개)
PRIMARY KEY (`id`,`xxxx_at`),
KEY `ix_userid_xxxx1_xxxx2` (`user_id`,`xxxx1`,`xxxx2`),
KEY `ix_xxxx3_xxxx1_xxxx2` (`xxxx3`,`xxxx1`,`xxxx2`),
KEY `ix_userid_xxxxat` (`user_id`,`xxxx_at`),
KEY `ix_xxxx9_xxxxat_xxxx7_xxxxat2_xxxx5` (`xxxx9`,`xxxx_at`,`xxxx7`,`xxxx_at2`,`xxxx5`),
KEY `ix_xxxxat2_xxxx5_xxxx7_xxxxat_xxxx9` (`xxxx_at2`,`xxxx5`,`xxxx7`,`xxxx_at`,`xxxx9`),
KEY `ix_xxxxat2_xxxx5_xxxx9_xxxxat_xxxx7_userid` (`xxxx_at2`,`xxxx5`,`xxxx9`,`xxxx_at`,`xxxx7`,`user_id`),
KEY `ix_userid_xxxxat2_xxxxat_xxxx6_xxxx10_xxxx8_type` (`user_id`,`xxxx_at2`,`xxxx_at`,`xxxx6`,`xxxx10`,`xxxx8`,`type`),
KEY `ix_xxxxat3_id` (`xxxx_at3`,`id`),
KEY `ix_xxxxat4_id` (`xxxx_at4`,`id`),
KEY `ix_xxxxat2_xxxx5_xxxx2_xxxxat3` (`xxxx_at2`,`xxxx5`,`xxxx2`,`xxxx_at3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(xxxx_at)
(PARTITION p2020 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p2021 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p2023 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p2024 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p2025 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
DB 서버와 테이블 스펙은 이정도고, 문제가 되었던 쿼리를 한 번 봐보자.
(실제 서비스에 사용되는 쿼리라서 테이블과 컬럼명은 다르게 변경했다)
## 쿼리
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND `user_id` NOT IN (7954, 29421, 35048, 39183, ... omitted) /* 600~700개 */
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715,745789441);
이 쿼리를 수행하면 최초에는 xx초 정도 소요되고, 한 번 캐싱되고 나면 0.2초 내로 끝난다.
실행계획도 봐보자.
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: heavy_tbl
partitions: p2020,p2021,p2022,p2023,p2024,p2025,pMAX
type: range
possible_keys: PRIMARY, 사용 가능한 인덱스 6개
key: PRIMARY
key_len: 8
ref: NULL
rows: 20
filtered: 0.25
Extra: Using where; Using filesort
사용 가능한 후보군 인덱스들이 많지만 최종적으로는 PRIMARY(클러스터링 인덱스)가 사용된다. 다들 예상하겠지만 PK 컬럼이 조건에 존재하기 때문에 이렇게까지 오래걸릴 쿼리가 아니다. 그럼 대체 왜 이렇게 오래도록 수행되는 것일까?
이 쿼리에 대해서 옵티마이저 트레이스를 떠보았다. 보면 의심갈만한 부분들이 많기는 한데, 정말 의심가는 곳은 딱 이부분이었다.
"analyzing_range_alternatives": {
...
...
{
"index": "ix_userid_xxxxat2_xxxxat_xxxx6_xxxx10_xxxx8_type",
"ranges": [
"NULL < user_id < 1197727",
"1197727 < user_id < 6377744",
"6377744 < user_id < 9087185",
"9087185 < user_id < 12624043",
"12624043 < user_id < 13124349",
"13124349 < user_id < 16997434",
"16997434 < user_id < 18174760",
"18174760 < user_id < 19415582",
"19415582 < user_id < 25161262",
"25161262 < user_id < 25203211",
"25203211 < user_id < 27976608",
"27976608 < user_id < 31601842",
"31601842 < user_id < 31906737",
"31906737 < user_id < 32407938",
"32407938 < user_id < 38434315",
"38434315 < user_id < 39216157",
"39216157 < user_id < 40237549",
"40237549 < user_id < 43477676",
"43477676 < user_id < 46258832",
"46258832 < user_id < 47776718",
"47776718 < user_id < 49433675",
"49433675 < user_id < 50366565",
"50366565 < user_id < 51060006",
"51060006 < user_id < 52621276",
"52621276 < user_id < 66166241",
"66166241 < user_id < 66793519",
"66793519 < user_id < 72880437",
"72880437 < user_id < 76622653",
"76622653 < user_id"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": -1,
"rows": 1497719851,
"cost": 1.64749e+09,
"chosen": false,
"cause": "cost"
},
...
...
}
rows랑 cost를 보면 장난이 아니다.
맞다. 이 user_id 조건이 이 쿼리의 문제점이었다. 실제로 user_id NOT IN 조건을 제거하고 쿼리를 수행하면 0.x초 내로 쿼리가 빨리 끝난다. (캐싱된거 아닌가라는 의문을 가질 수 있는데, 캐싱되지 않은 쿼리 조건으로 수행했다) 그리고 NOT IN 조건이 위 트레이스처럼 대안 가능한 range로 실행계획이 풀리면서 상당히 많은 rows와 cost를 갖게 된다.
이게 왜 그런 것일까? chosen: false 인데도 어떻게 이 실행계획으로 쿼리가 느려지는 걸까?
결론부터 이야기하면 옵티마이저(정확히 말하면 range analyzer)가 저 user_id 조건에 대한 TREE 객체(SEL_TREE)를 생성하면서 오랜 시간이 걸리는 것이다.
옵티마이저는 실행계획 생성 과정에서 대안 가능한(alternative) 인덱스를 찾게 되는데, 이 때 SEL_TREE 라는 클래스(tree.h 파일에 정의되어 있어)를 통해서 대안 가능한 인덱스들을 TREE 객체로 생성하게 된다. user_id 조건과 같은 NOT IN (c1, c3, c4…) 형태의 조건은 아래와 같이 Range 형태로 TREE 객체가 생성된다고 보면 된다.
($MIN<t.key<c1) OR (c1<t.key<c2) OR (c2<t.key<c3) OR ...생각해보면 만약에 NOT IN 조건에 들어가는 user_id 값이 무수히 많다면(아까 보여준 옵티마이저 트레이스처럼), Range로 변형하는 작업이 CPU와 메모리를 엄청나게 소모하게 되는 일이 될 것이다.
그래서 코드 상에는 NOT IN 조건에 들어가는 개수를 제한하는 상수 하나가 존재한다. 바로 아래 NOT_IN_IGNORE_THRESHOLD 라는 상수 값이다. NOT IN 조건 개수가 1000개 이하면 SEL_TREE를 생성하고, 1000개 초과이면 SEL_TREE를 생성하지 않는다.
const uint NOT_IN_IGNORE_THRESHOLD = 1000;그래 좋다. 1000개 초과이면 대용량 NOT IN 목록이다 라고 보고, 트리 객체를 생성하지 않는다는 것은 그래 그냥 그렇다고 치고 넘어가자. 하지만 heavy_tbl 테이블과 같이 7억건 가까이 되는 대용량 테이블은 개수 제한에 의미가 있을까? NOT IN (c1, c2…)일 때 c1과 c2 사이에 얼마나 많은 레코드가 있을줄 알고 말이다. 뭔가 좀 더 나이스한 알고리즘이 적용되어야 하지 않을까 라는게 내 생각이다. OMG!
그럼 이 쿼리는 어떻게 해야할까? 그대로 놔두어야 할까? 사실 코드 상에 상수로 박혀있는 조건이다 보니까 우리는 이 쿼리를 다른 방식으로 회피해야 한다.
NOT_IN_IGNORE_THRESHOLDMySQL 회피 방법
이 문제를 회피할 수 있는 핵심은 user_id 컬럼으로 구성되어 있는 세컨더리 인덱스를 사용하지 않도록 하는데 있다. 그럼 총 4가지의 수정 방안을 공유해보도록 하겠다.
1. (user_id × 1) 연산
첫 번째로는 user_id 컬럼에 연산을 발생시켜서 인덱스를 사용하지 못하도록 막는 방법이다. 아래와 같이 WHERE절의 user_id를 user_id*1로 수정하면 user_id 조건을 인덱스에 사용하지 못하게 만들고, 옵티마이저의 TREE 객체 생성을 막을 수 있다. 수정 후에 쿼리를 수행하면 0.x초 내로 빠르게 수행 완료된다.
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND `user_id`*1 NOT IN (7954, 29421, 35048, 39183, ... omitted) /* 600~700개 */
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715,745789441);
2. Row Constructor 사용
MySQL 서버 8.0에 추가된 기능인 Row Constructor 방식을 사용하면 NOT EXISTS문으로 이 문제를 회피할 수 있다.
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND (user_id) NOT EXISTS (SELECT 1
FROM (VALUES ROW( 7954),
ROW( 29421),
ROW( 35048),
ROW( 39183),
ROW( 169802),
...omitted) AS `excluded_users` (user_id) /* 600~700개 */
WHERE `heavy_tbl`.`user_id` = `excluded_users`.`user_id`
)
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715,745789441);
요렇게 하면 user_id 조건 값들을 파생 테이블(Derived Table)로 사용하면서, user_id 인덱스를 대안 가능한 인덱스로 찾지 않게 된다.
Covering index lookup on excluded_users using <auto_key0> (user_id=heavy_tbl.user_id)3. (1001-n) 적용
아까 이 문제가 비롯된 것이 특정한 상수 값 NOT_IN_IGNORE_THRESHOLD=1000 때문이라고 했었다. 그러면 이 조건 값을 쿼리 조건의 임계값으로 사용하는 것이다.
user_id 조건 값이 1000개 이하이면, (1001-n)개수만큼 0 또는 무의미한 값으로 user_id NOT IN 조건 값을 채우고(1001개가 되도록), user_id 조건 값이 1001개 이상이면 기존 로직 그대로 놔두는 것이다.
## user_id 조건 값이 1000개 이하일 경우
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND `user_id` NOT IN (7954, 29421, 35048...0, 0, 0...omitted...0) /* 총 1001개 */
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715,745789441);
## user_id 조건 값이 1001개 이상일 경우
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND `user_id` NOT IN (7954, 29421, 35048...omitted) /* 총 1001개 이상 */
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715,745789441);
4. user_id 응용 프로그램 필터링
네 번째 방법은 user_id NOT IN 조건을 쿼리에서 없애고 응용 프로그램 코드단에서 필터링 하도록 만드는 것이다. 최후의 보루처럼 느껴지기는 하는데, 이것도 문제를 회피할 수 있는 하나의 방법이 될 수 있다.
## user_id NOT IN 조건을 제외한 쿼리
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715,745789441);
## Python 코드
query = """
SELECT *
FROM heavy_tbl
WHERE `type` = 'Apple'
AND `xxxx_at2` IS NULL
AND `xxxx5` = TRUE
AND `xxxx8` > 0
AND `xxxx10` IN (10, 20)
AND `xxxx8` != 0
AND `id` != 731337975
AND `id` IN (737546715, 745789441, 751374123, ... omitted) /* 20~30개 */
ORDER BY field(id, 737546715, 745789441);
"""
df = pandas.read_sql(query, DB 연결 객체)
excluded_user_ids = {7954, 29421, 35048}
## user_id 필터링
df_filtered = df[~df['user_id'].isin(excluded_user_ids)]
결론
쿼리에 NOT IN 조건이 들어가 있고 실행계획상에 문제가 없는데 쿼리가 느리다면, 일단 옵티마이저 트레이스를 확인해보고 NOT IN 조건이 Range Analyzer에 의해서 범위 조건으로 변경되었는지 확인해봐야 한다. rows랑 cost도 같이 봐야할 것이다. 동일한 현상을 겪는 것이라면 위에서 제시한 4가지 방법을 비교해가면서 문제를 해결해야 할 수도 있다.
Database Internals 
댓글 남기기