[MySQL] 탐색할 컬럼에 분명 인덱스를 걸었는데, 슬로우 쿼리?! (묵시적 형 변환)

현재 운영하고 있는 서버로 유저가 조금이라도 몰리게 되면, 전체적인 응답이 심하게 느려지는 현상이 있었습니다.

원인은 DB의 CPU 사용률이 급속도로 치솟게 된 게 원인이었고, 이를 계기로 슬로우 쿼리들을 개선하기로 했습니다.

그 과정에서 MySQL의 묵시적 형 변환에 대해 알게 되었고, 배웠던 과정을 기록하고자 합니다.

 

문제 상황

우선 여러 슬로우 쿼리 중 지연 시간이 가장 컸던 쿼리가 users 테이블에서 kakao_id를 통해 유저를 찾아오는 쿼리였는데, 이상했습니다.

kakao_id는 인덱스가 걸려있는 컬럼임에도 평균 지연 시간이 817.36ms 이상이었거든요.

users 테이블 구조는 다음과 같습니다.

CREATE TABLE users
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100)                           NULL,
    phone_number VARCHAR(20)                            NULL,
    email        VARCHAR(100)                           NULL,
    kakao_id     VARCHAR(11)                            NULL,
    mileage      INT UNSIGNED DEFAULT 0                 NULL,
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at   DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at   DATETIME                               NULL,
    # ...

    CONSTRAINT kakao_id_and_deleted_at UNIQUE (kakao_id, deleted_at)
);

(참고로, users 테이블에는 kakao_id, deleted_at 멀티 유니크 키가 걸려있습니다.)

 

그래서 현재 저희 서버에서 날리고 있는 쿼리에 EXPLAIN을 붙여 실행 계획을 살펴보니, 아래와 같은 결과가 나왔습니다.

EXPLAIN
SELECT *
FROM users
WHERE deleted_at IS NULL AND kakao_id = 1234567890

실행 계획 결과

여기서 주목했던 컬럼은, type, possible_keys, key 세 가지 컬럼입니다.

type (조인 방식)	possible_keys (선택 가능한 인덱스들)	key (실제로 반영된 인덱스)
ALL	kakao_id_and_deleted_at	<null>

보시는 바와 같이, 실제로 연산을 수행할 때, 반영된 인덱스가 없어 풀 스캔을 하고 있는 상황이었던 것입니다.

 

원인

이와 같은 상황이 발생한 원인은 MySQL의 묵시적 형 변환 규칙 때문입니다.

기존에 저희 서버에서는 kakao_id를 통해 유저를 조회할 시, int 타입의 값으로 varchar 타입인 컬럼에 where 조건을 걸고 있었습니다.

그런데 MySQL에서는 문자열과 숫자형 데이터 타입 간의 비교에서는 문자열을 숫자로 변환하여 처리하고 있습니다.

그래서 만약,  숫자 타입의 컬럼을 문자열로 값을 넣어 질의하게 된다면 질의 값이 숫자로 형 변환이 되어 정상적으로 인덱스를 사용했겠지만,

문자열 타입의 컬럼을 숫자 값으로 질의할 땐 테이블의 문자열 값을 숫자로 형 변환을 해주어야 하기 때문에 풀 스캔이 발생하게 된 것입니다.

 

해결

그래서 쿼리를 아래와 같이 변경했습니다.

EXPLAIN
SELECT *
FROM users
WHERE deleted_at IS NULL AND kakao_id = '1234567890'

실행 계획 결과

type (조인 방식)	possible_keys (선택 가능한 인덱스들)	key (실제로 반영된 인덱스)
ref	kakao_id_and_deleted_at	kakao_id_and_deleted_at

쿼리에서 변경된 부분은 kakao_id(varchar)의 검색할 값을 타입에 맞게 변경해 주었더니 원하던 결과를 얻을 수 있었습니다. 실제로 실행 계획 부분에서 조인 방식이 ALL에서 ref로 변경, 실제로 반영한 인덱스가 null이 아님을 확인할 수 있습니다.

 

결론

컬럼 타입과 질의 값의 타입을 맞춰 주었을 뿐인데, 평균 지연 시간이 817.36ms에서 0.28ms으로 떨어져 평균 지연시간이 감소하게 되었습니다.

인덱스에 걸려있는 컬럼의 타입과 같은 타입으로 질의하는 것을 강력히 추천합니다.

 

+ 서버에서 타입 관리 잘하기!!

 

참고

SQL 작성 시 묵시적 형 변환 함정에 빠지지 말자!!