MySQL - query-optimization(1)¶
이번 포스팅은 사내 MySQL 관련 내용 발표를 위해 Real MySQL 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다.
MySQL 역시 내용이 방대하기 때문에 시리즈로 나누어 정리할 예정이며, 이번에는 쿼리 최적화 중에서도 MySQL 연산자와 내장 함수를 사용할 때 알아두면 좋은 정보를 살펴본다.
1. 숫자와 문자열의 타입 변환과 인덱스¶
숫자 값과 문자열 값을 비교할 때는 한 가지 주의할 사항이 있다. 서로 다른 타입으로 WHERE 조건 비교가 수행되는 다음 쿼리를 살펴보자.
SELECT * FROM tab_test WHERE number_column = '10001';
SELECT * FROM tab_test WHERE string_column = 10001;
위 쿼리와 같이 두 비교 대상이 문자열과 숫자 타입으로 다를 때는 자동으로 타입 변환(Type Casting)이 발생한다. MySQL은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다.
- 첫 번째 쿼리: 주어진 상수값(
'10001')을 숫자로 변환한다. 이때는 상수값 하나만 변환(10001)하므로 성능과 관련된 문제가 발생하지 않는다. - 두 번째 쿼리: 주어진 상수값은 숫자인데 비교되는 컬럼은 문자열 컬럼이다. 이때 MySQL은 문자열 컬럼의 모든 레코드 값을 숫자로 변환해서 비교를 수행한다. 즉,
string_column에 인덱스가 걸려 있더라도 인덱스를 전혀 이용하지 못하고 풀 테이블 스캔을 하게 된다.
예제를 통한 실행 계획 확인¶
다음과 같은 zipcode 테이블과 인덱스가 있다고 가정하자.
인덱스 ix_code는 varchar 타입의 code 컬럼을 인덱스로 갖고 있고, ix_code_serial_num은 int 타입의 code_serial_num을 인덱스로 갖고 있다.
① INT 타입 컬럼을 String 포맷으로 비교했을 때¶
EXPLAIN SELECT * FROM zipcode WHERE code_serial_num = '10000001';
상수값 '10000001'을 int 타입으로 변경한 후 실행하기 때문에 기대한 대로 ix_code_serial_num 인덱스를 정상적으로 이용한다.
② String 타입 컬럼을 INT 포맷으로 비교했을 때¶
EXPLAIN SELECT * FROM zipcode WHERE code = 10000001;
string 타입이었던 code 컬럼 데이터를 레코드마다 숫자로 변환하면서 비교하기 때문에, 인덱스를 사용하지 못하고 전체 데이터를 다 읽는 테이블 풀 스캔(Full Table Scan)을 수행하게 된다.
2. 동등 비교 연산자 (<=> vs =)¶
MySQL에서는 일반적인 동등 비교를 위한 = 연산자 외에 <=> 연산자도 제공한다.
<=> 연산자는 = 연산자와 같으며, 부가적으로 NULL 값에 대한 비교까지 수행한다. MySQL에서는 이 연산자를 NULL-Safe 비교 연산자라고 부른다.
위 예제 결과에서도 알 수 있듯이, 일반적인 비교에서 NULL은 IS NULL 연산자 외에는 정상 비교할 방법이 없다. 그래서 일반 = 연산자는 한쪽이 NULL이면 결과도 NULL로 반환한다.
하지만 NULL-Safe 비교 연산자(<=>)를 이용하면:
- 양쪽 비교 대상 모두 NULL인 경우: TRUE (1)를 반환
- 한쪽만 NULL인 경우: FALSE (0)를 반환
3. 현재 시각 조회 함수 (NOW() vs SYSDATE())¶
NOW()와 SYSDATE()는 모두 현재 시간을 반환하는 함수로서 겉보기에는 동일한 기능을 수행한다. 하지만 두 함수는 작동 방식에서 매우 큰 차이가 있다.
NOW(): 하나의 SQL 문장 내에서 항상 동일한 시각 값을 유지한다.SYSDATE(): 하나의 SQL 문장 내에서도 함수가 실제로 호출되는 시점마다 결과 값이 달라진다.
위 테스트 결과에서 보듯이, NOW()는 문장 시작 시점의 시간을 그대로 반환하지만, SYSDATE()는 SLEEP(2) 함수 대기 시간인 2초 동안의 시간 차이가 발생함을 확인할 수 있다.
SYSDATE 함수의 잠재적인 문제점 2가지¶
- 복제(Replication) 불일치:
SYSDATE()가 포함된 SQL은 마스터와 슬레이브 간 실행 시점 차이로 인해 데이터 불일치가 발생할 수 있다. - 인덱스 활용 불가 (비결정적 특성):
SYSDATE()와 비교되는 컬럼은 인덱스를 효율적으로 사용하지 못한다.
실행 계획 비교¶
① NOW() 함수를 사용하는 경우¶
EXPLAIN SELECT code, code_serial_num, update_at
FROM zipcode_time
WHERE code_serial_num = 10000001 AND update_at > NOW();
② SYSDATE() 함수를 사용하는 경우¶
EXPLAIN SELECT code, code_serial_num, update_at
FROM zipcode_time
WHERE code_serial_num = 10000001 AND update_at > SYSDATE();
- 차이 분석:
NOW()를 사용한 첫 번째 쿼리는code_serial_num과update_at컬럼 모두 인덱스를 효율적으로 사용하여 전체 키 길이 11바이트를 모두 활용했다. - 반면
SYSDATE()는 호출될 때마다 값이 달라지므로 상수로 취급되지 않는다. 따라서 매 레코드 비교 시점마다 함수를 재실행해야 하므로update_at인덱스 범위를 제대로 타지 못한다.
💡 핵심 요약:
NOW()는 쿼리가 1시간 동안 실행되더라도 쿼리 시작 시점의 동일한 값을 보장하므로 인덱스 최적화와 복제 안정성 측면에서 항상NOW()를 우선적으로 사용해야 한다.
4. 값의 비교와 대체 (CASE WHEN ... THEN ... END)¶
CASE WHEN 절이 조건이 일치하는 경우에만 THEN 이하의 표현식을 실행한다는 특성을 이용하면, 서브쿼리의 실행 횟수를 획기적으로 줄일 수 있다.
최적화 전 쿼리¶
다음 쿼리는 Marketing 부서에 소속된 적이 있는 모든 사원의 가장 최근 급여를 조회하는 쿼리다. 2만여 건의 레코드를 조회할 때 급여 테이블을 조회하는 상관 서브쿼리도 2만 번 실행된다.
SELECT de.dept_no, e.first_name, e.gender,
(SELECT s.salary FROM salaries s
WHERE s.emp_no = e.emp_no
ORDER BY from_date DESC LIMIT 1) AS last_salary
FROM dept_emp de, employees e
WHERE e.emp_no = de.emp_no
AND de.dept_no = 'd001';
최적화 후 쿼리 (CASE WHEN 적용)¶
만약 성별이 여자인 경우에만 최종 급여 정보가 필요하고 남자는 이름만 필요하다면, 불필요한 서브쿼리 조회를 CASE WHEN으로 감싸서 방지할 수 있다.
SELECT de.dept_no, e.first_name, e.gender,
CASE
WHEN e.gender = 'F' THEN
(SELECT s.salary FROM salaries s
WHERE s.emp_no = e.emp_no
ORDER BY from_date DESC LIMIT 1)
ELSE 0
END AS last_salary
FROM dept_emp de, employees e
WHERE e.emp_no = de.emp_no
AND de.dept_no = 'd001';
이렇게 쿼리를 변경하면 여성 사원인 경우에만 서브쿼리가 실행되며, 남성 사원의 수만큼 불필요한 서브쿼리 실행 횟수를 아낄 수 있어 전체 조회 성능이 향상된다.
마치며¶
이번 포스팅에서는 MySQL 쿼리 작성 시 흔히 놓치기 쉬운 데이터 타입 일치 여부, NULL-Safe 비교 연산자, 시각 함수(NOW vs SYSDATE)의 인덱스 영향, 그리고 CASE WHEN을 활용한 서브쿼리 절약 기법을 살펴보았다. 사소해 보이는 차이지만 대용량 데이터를 다룰 때 쿼리 속도에 결정적인 영향을 미치므로 숙지해 둘 필요가 있다.