#034. 쿼리개선: "연산"을 해서 index를 사용하지 못한 쿼리 개선

개념

WHERE 절에 사용되는 비교 조건식의 표현식은 상당히 중요하다. 쿼리가 최적으로 실행되려면 적합한 인덱스와 함께 WHERE 절에 사용되는 비교 조건의 표현식을 적절하게 사용해야한다. 문자열 칼럼이나 숫자 칼럼을 비교할 때는 반드시 그 타입에 맞는 상숫값을 사용할 것을 권장한다. 날짜 값을 비교할 때는 DB 내부적으로 문자열 값을 자동으로 DATETIME 타입의 값으로 변환해서 비교를 수행한다. 
이 때 아래와 같이 날짜 타입의 포맷을 변환하는 형태를 포함해 날짜 타입 칼럼의 값을 더하거나 뺴는 함수로 변형한 후 비교하면 인덱스를 사용할 수 없다.

// hire_date 타입을 강제로 문자열로 변경해 인덱스를 사용하지 못하는 예시

SELECT COUNT(*)
FROM emp
WHERE DATE_FORMAT(hire_date, '%Y-%m-%d'))> '2022-01-01';

아래 예시는 hire_date 칼럼을 함수로 변형하기 때문에 인덱스를 사용하지 못한다. 따라서 두번째 쿼리 같이 칼럼이 아니라 상수를 변형하는 형태로 작성해야한다. 

-- 인덱스 X
SELECT COUNT(*)
FROM emp
WHERE DATE_ADD(hire_date, INTERVAL 1 YEAR)>'2022-01-01';

-- 인덱스 O
SELECT COUNT(*)
FROM emp
WHERE hire_date > DATE_SUB('2022-01-01', INTERVAL 1 YEAR);

 

적용

아래의 예시는 "craeted_at"에 index가 있다고 전제하고 있다.

예시 1을 보면 "create_at"에 AT TIME ZONE, 즉 타임존 연산을 하고 있기 때문에 인덱스를 사용할 수 없다. 따라서 아래와 같이 "created_at" 컬럼 자체를 비교하도록 수정한다.

JavaScript  Date 객체는 1970년 1월 1일 UTC(협정 세계시) 자정과의 시간 차이를 밀리초로 나타내는 정수 값을 담기 때문에 이미 timezone의 정보가 포함되어 있다.

JavaScript에서 생성한 Date 객체는 자동으로 ISO 8601 형식의 문자열로 변환되며 MySQL에서 지원하는 DATETIME 데이터 유형과 호환되는 형식으로 자동 변환된다. 따라서 아래와 같이 Date객체를 사용해 쿼리를 작성할 경우 이미 타임존의 정보가 포함되어 비교되기 때문에 정상적으로 비교된다.

// example 1
"created_at" AT TIME ZONE 'Asia/Seoul' > NOW() - INTERVAL ${SQL([
    `'${parseInt(days * 2)}'`,
])} DAY


// improvement
import { subDays } from 'date-fns';

SQL`"created_at" > ${subDays(new Date(), parseInt(days * 2))}`

 

예시 2를 보면 예시 2과 같이 "created_at"에 타임존 정보를 반영하기 위한 연산을 수행하는 것을 알 수 있다. 따라서 이 연산을 date 객체를 이용해 상수 쪽에서 하도록 사용하면 index를 사용하도록 수정할 수 있다. 

// example 2
const date_string = `${year}-${month}-01 00:00:00`;
return SQL`"created_at" AT TIME ZONE 'Asia/Seoul'
BETWEEN TO_TIMESTAMP(${date_string}, 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP(${date_string}, 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' MONTH`;


// improvement
const date = new Date(`${year}-${month}-01 00:00:00`);
return SQL`"created_at"
BETWEEN ${date}
AND ${addMonths(date, 1)}`;

 

참고:
MySQL 8.0 11장 p.66~