Real MySQL [7-9] 쿼리 작성 및 최적화 - WHERE 절의 비교 조건 사용 시 주의사항

MySQL

Real MySQL [7-9] 쿼리 작성 및 최적화 - WHERE 절의 비교 조건 사용 시 주의사항

weicome 2017. 1. 31. 20:28

본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.

NULL 비교

다른 DBMS와는 달리 MySQL 에서는 NULL값이 포함된 레코드도 인덱스로 관리된다. 이는 인덱스에서는 NULL을 하나의 값으로 인정해서 관리한다는 것을 의미한다. SQL 표준에서 NULL 정의는 비교가 불가해서 두 값이 모두 NULL을 가진다고 하더라도, 이 두 값이 같은지 비교하는 것은 불가능하다. 연산이나 비교에서 한쪽이라도 NULL 이면 그 결과도 NULL이 반환되는 이유가 바로 여기에 있다. NULL인지 비교하려면 "IS NULL" 연산자를 사용해야 한다. 그 밖의 방법으로는 칼럼의 값이 NULL 인지 알 수 있는 방법이 없다.

mysql> SELECT NULL=NULL;

// NULL

mysql> SELECT CASE WHEN NULL=NULL THEN 1 ELSE 0 END;

// 0

mysql> SELECT CASE WHEN NULL IS NULL THEN 1 ELSE 0 END;

// 1

다음 예제 쿼리의 NULL 비교가 인덱스를 사용하는 방법을 살펴보자.

SELECT * FROM titles WHERE to_date IS NULL;

위의 쿼리는 to_date 칼럼이 NULL 인 레코드를 조회하는 쿼리지만 to_date 칼럼에 생성된 ix_todate 인덱스를 ref 방식으로 적절히 이용하고 있음을 알 수 있다.

칼럼의 값이 NULL 인지 확인할 때는 ISNULL()이라는 함수를 사용해도 된다. 하지만 ISNULL() 함수를 WHERE 조건에서 사용할 때는 주의해야 할 점이 있다.

SELECT * FROM titles WHERE to_date IS NULL;

SELECT * FROM titles WHERE ISNULL(to_date);

SELECT * FROM titles WHERE ISNULL(to_date)=1;

SELECT * FROM titles WHERE ISNULL(to_date)=true;

위의 나열된 4개의 쿼리는 전부 정상적으로 to_date 칼럼이 NULL 인지 판별할 수 있는 쿼리다. 첫번째와 두번째 쿼리는 titles 테이블의 ix_todate 인덱스를 레인지 스캔으로 사용할 수 있다. 하지만 세 번째와 네 번째 쿼리는 인덱스나 테이블을 풀 스캔하는 형태로 처리된다. NULL 비교를 할 때는 가급적 IS NULL 연산자를 사용하길 권장한다. 세번째나, 네번째 형태의 경우 이러한 비교는 인덱스를 사용하지 못한다.

문자열이나 숫자 비교

문자열 칼럼이나 숫자 칼럼을 비교할 때는 반드시 그 타입에 맞춰 상수를 사용할 것을 권장한다. 비교 대상 칼럼이 문자열 칼럼이면 문자열 리터럴을 사용하고, 숫자 타입이면 숫자 리터럴을 이용하는 규칙만 지켜주면 된다.

SELECT * FROM employees WHERE emp_no=10001;

SELECT * FROM employees WHERE first_name='Smith';

- 위의 두 쿼리는 칼럼의 타입과 비교하는 상수값이 동일한 타입으로 사용되어 인덱스를 이용할 수 있다.

SELECT * FROM employees WHERE emp_no='10001';

- emp_no 칼럼이 숫자 타입이기 때문에 문자열 상수값을 숫자로 타입 변환해서 비교를 수행하므로 성능 저하는 발생하지 않는다.

SELECT * FROM employees WHERE first_name=10001;

- first_name이 문자열 칼럼이지만 비교되는 상수값이 숫자이므로 옵티마이저는 우선순위를 가지는 숫자 타입으로 비교를 수행하려고 실행 계획을 수립한다. first_name 칼럼의 문자열을 숫자로 변환해서 비교를 수행한다. 하지만 first_naem 칼럼의 타입 변환이 필요하기 때문에 ix_firstname 인덱스를 사용하지 못한다.

EXPLAIN

SELECT * FROM employees WHERE first_name=10001;