본 게시물의 내용과 이미지는 도서 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;
칼럼의 타입에 맞게 상수 리터럴을 비교 조건으로 사용하는 것이 중요하다.
DATE나 DATETIME 과 문자열 비교
DATE나 DATETIME 타입의 값과 문자열을 비교할 때는 문자열 값을 자동으로 DATETIME 타입의 값으로 변환해서 비교를 수행한다.
SELECT COUNT(*) FROM employees
WHERE hire_date>STR_TO_DATE('2017-01-26', '%Y-%m-%d');
- 위의 쿼리는 DATE 타입의 hire_date 칼럼 비교를 위해 '2017-01-26' 을 STR_TO_DATE() 함수를 이용해 일자를 형변환했다. 하지만 위와 같이 명시적으로 변환하지 않더라도 MySQL이 내부적으로 변환을 수행한다.
SELECT COUNT(*) FROM employees
WHERE hire_date>'2017-01-26'
- 첫 번째 예제와 동일하게 처리된다. '2017-01-26'은 MySQL이 내부적으로 형변환을 수행한다.
SELECT COUNT(*) FROM employees
WHERE DATE_FORMAT(hire_date, '%Y-%m-%d') > '2017-01-26';
- 위의 쿼리는 hire_date 타입을 강제적으로 문자열로 변경시키기 때문에 인덱스를 효율적으로 이용하지 못한다. 가능하면 DATE, DATETIME 타입의 칼럼을 변경하지 말고, 상수를 변경하는 형태로 조건을 사용하는 것이 좋다.
SELECT COUNT(*) FROM employees
WHERE DATE_ADD(hire_date, INTERVAL 1 YEAR) > '2017-01-26';
- 날짜 타입의 포맷팅뿐 아니라 칼럼의 값을 더하거나 빼는 함수로 변형한 후 비교해도 마찬가지로 인덱스를 이용할 수 없다.
DATE와 DATETIME의 비교
DATETIME 값에서 시간 부분만 떼어 버리고 비교하려면 아래와 같이 쿼리를 작성하면 된다.
SELECT COUNT(*) FROM employees
WHERE hire_date>DATE(NOW());
DATETIME 타입의 값을 DATE 타입으로 만들지 않고 그냥 비교하면 MySQL 서버가 DATE 타입의 값을 DATETIME 으로 변환해서 같은 타입을 만든 다음 비교를 수행한다. 예를 들면 DATE 타입의 값 "2017-01-26" 과 DATETIME 타입의 값 "2017-01-26 00:00:00"을 비교하는 과정에서는 "2017-01-26" 을 "2017-01-26 00:00:00" 으로 변환해서 비교를 수행한다.
