11.4.3 WHERE 절의 비교 조건 사용 시 주의사항

11.4.3.1 NULL 비교

다른 DBMS와 다르게 MySQL에서는 NULL 값이 포함된 레코드도 인덱스로 관리
- 즉, NULL 을 하나의 값으로 인정해서 관리한다는 의미
SQL 표준에서 NULL은 '비교할 수 없는 값'
- 따라서 두 값이 모두 NULL 이라고 하더라도 동등한지 비교하는 것은 불가능
- 어느 한쪽이 NULL 이라면 결과도 NULL
쿼리에서 NULL 인지 비교하려면 "IS NULL" (또는 <=>) 연산자를 사용해야 함
칼럼의 값이 NULL인지 확인할 때는 ISNULL() 이라는 함수를 사용
- 하지만 ISNULL(to_date)=1, ISNULL(to_date)=true 와 같이 WHERE 조건을 사용할 경우 인덱스를 사용할 수 없게 된다.

11.4.3.1 문자열이나 숫자 비교

비교 대상 칼럼에 맞는 상숫값을 사용하는 것을 권장
- 문자열 칼럼이라면 문자열 리터럴을, 숫자 타입이라면 숫자 리터럴을 이용

11.4.3.3 날짜 비교

11.4.3.3.1 DATE 또는 DATETIME과 문자열 비교

문자열 값을 자동으로 DATETIME 타입의 값으로 변환해서 비교 수행
- hire_date > STR_TO_DATE('2023-01-01', '%Y-%m-%d')와 hire_date > '2023-01-01' 모두 효율적으로 동작
반대로, 위의 예시에서 'hire_date' 값을 DATE_FORMAT을 이용해 문자열로 만들어 조건을 작성할 경우 인덱스를 사용하지 못함

11.4.3.3.2 DATE와 DATETIME의 비교

DATETIME값에서 시간 부분을 빼고 비교하려면 DATE() 함수를 사용하면 됨; DATE(NOW())
DATETIME과 DATE 타입의 비교에서 타입 변환은 인덱스 사용 여부에 영향을 미치지 않으므로 성능 보다는 쿼리 결과에 주의해서 사용

11.4.3.3.3 DATETIME과 TIMESTAMP 비교

UNIX_TIMESTAMP() 함수의 결괏값은 MySQL 내부적으로는 단순 숫자 값에 불과하므로 이 결과값과 DATETIME 값을 비교하는 것은 원하는 결과를 얻을 수 없음
반드시 비교 값으로 사용되는 상수 리터럴을 비교 대상 칼럼의 타입에 맞게 변환해서 사용
- 대상 칼럼이 DATETIME이라면 FROM_UNIXTIME 함수를 이용해서 timestamp 값을 datetime 값으로 변환
- 대상 칼럼이 TIMESTAMP라면 UNIX_TIMESTAMP 함수를 이용해서 datetime 값을 timestamp 값으로 변환

11.4.3 WHERE 절의 비교 조건 사용 시 주의사항

11.4.3.4 Short-Circuit Evaluation

여러 개의 표현식이 AND 또는 OR 논리 연산자로 연결된 경우 선행 표현식의 결과에 따라 후행 표현식을 평가할지 말지 결정하는 최적화

boolean in_transaction;

if ( in_transaction && has_modified() ) {
    commit();
}

in_transaction 값이 TRUE이면 has_modified() 함수를 호출하고 FALSE 이면 has_modified() 결과에 관계 없이 commit()이 실행되지 않는다

MySQL에서 Short-circuit Evaluation 이 쿼리의 성능에 어떻게 영향을 미치는가?

SELECT COUNT(*) FROM salaries;
# 결과 2844047

# 1번 조건
SELECT COUNT(*) FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00')>'1991-01-01';
# 결과 2442943

# 2번 조건
SELECT COUNT(*) FROM salaries
WHERE to_date<'1985-01-01';
# 결과 0

1, 2번 조건은 모두 인덱스를 사용하지 못하기 때문에 풀 테이블 스캔

1번과 2번 조건을 AND로 연결한 3번째 쿼리의 결과는 0건이 될 것이다

WHERE 절에 나열되는 조건의 순서가 쿼리의 성능에 영향을 미칠까?

SELECT COUNT(*) FROM salaries
WHERE 1번 조건 AND 2번 조건;
# (0.73 sec)

SELECT COUNT(*) FROM salaries
WHERE 2번 조건 AND 1번 조건;
# (0.52 sec)

WHERE 절에서 1번 조건을 먼저 사용한 쿼리가 더 오래 걸렸다

1번 조건이 먼저 사용된 쿼리의 경우

MySQL 서버는 salaries 테이블 전체 레코드에서 CONVERT_TZ(from_date, ...) 함수를 실행하고 그 결과에 대해 to_date 칼럼의 비교 작업을 한다
CONVERT_TZ() 함수가 2844047번(전체) 실행하고 to_date 칼럼 비교 작업이 2442943번 실행되어야 함

2번 조건이 먼저 사용된 쿼리의 경우

to_date<'1985-01-01 조건을 만족하는 레코드가 한 건도 없기 때문에 to_date 칼럼의 비교 작업만 28844047(전체)번 실행하면 되고 CONVERT_TZ() 함수는 한 번도 호출되지 않음

하지만 WHERE 절의 조건 중에서 인덱스를 사용할 수 있는 조건이 있다면 short-circuit evaluation 과는 무관하게 MySQL 서버는 그 조건을 가장 최우선으로 사용한다

그래서 WHERE 조건절에 나열된 조건의 순서가 인덱스의 사용 여부를 결정하지는 않는다

SELECT * FROM employees
WHERE last_name='Aamodt'
AND first_name='Matt';

last_name 칼럼은 인덱스를 사용할 수 없지만 first_name 칼럼은 인덱스를 효율적으로 사용할 수 있다

WHERE 절에 나열된 조건의 순서와 무관하게 MySQL 서버는 인덱스를 사용할 수 있는 조건을 먼저 평가한다

그래야만 employees 테이블의 ix_firstname(first_name) 인덱스를 이용해서 꼭 필요한 레코드만 빠르게 가져올 수 있다

그러고 나서 first_name 조건을 제외한 나머지 조건을 순서대로 평가한다

75p 상단에 있는 쿼리에서 EXIST (subquery) 부분은 GROUP BY ... HAVING ... 절을 가지고 있기 때문에 세미 조인 최적화를 활용할 수 없다

first_name 칼럼의 조건은 인덱스를 사용할 수 있으므로 최우선으로 인덱스를 사용해 필요한 데이터의 범위를 최소화 할 것이고

그 결과 에서 last_name ='Aamodt' 조건과 서브쿼리 조건을 만족하는 레코드만 걸러서 결과를 반환한다

이 때 WHERE 절에 서브쿼리 조건이 먼저 나열됐기 때문에 last_name 칼럼의 조건보다 EXISTS (subquery) 조건을 먼저 평가한다

75p~76p 쿼리에서 last_name 비교 조건이 먼저 나오는 1번 쿼리와 EXISTS (subquery) 조건이 먼저 나오는 2번 쿼리에서

1번 쿼리는 ix_firstname 인덱스를 통해 233건의 레코드를 가져온 다음 last_name 조건을 만족하는지를 먼저 평가했고 이 둘을 만족하는 레코드 1건에 대해 EXISTS (subquery) 조건의 만족 여부를 평가했다

2번 쿼리는 반대로 ix_firstname 인덱스를 통해 가져온 233건에 대해 복잡한 EXISTS (subquery) 조건을 평가하면서 상당히 많은 레코드를 읽고 쓰는 작업을 처리 했다

임시 테이블에 레코드 쓰기 작업도 상당히 많이 실행됐다

결론

WHERE 절에서 복잡한 연산 또는 다른 테이블의 레코드를 읽어야 하는 서브쿼리 조건 등은 가능한 뒤쪽으로 배치하는 것이 성능상 도움이 된다
WHERE 조건 중에서 인덱스를 사용할 수 있는 조건은 WHERE 절의 어느 위치에 나열되도 순서에 관계없이 가장 먼저 평가된다

11.4.4 DISTINCT

특정 컬럼의 유니크한 값을 조회할 때 사용

많은 사용자가 조인을 하는 경우 레코드가 중복해서 출력되는 것을 막기 위해 DISTINCT를 남용하는 경향이 있다

성능적인 문제도 있지만 쿼리의 결과가 의도한 바와 달라 질 수 있으니 주의

9.2.5장 참고

여러 테이블을 조인하는 쿼리에서 조인 조건에 따라 레코드가 몇 배씩 불어나기도하는데

각 테이블의 간의 조인이 1:1, 1:M 조인인지와 같이 업무적인 연결 조건을 이해하지 못하고 쿼리를 작성하는 경우

주로 DISTINCT가 남용하는 경우가 발생

mojh7 / real-mysql-study

2023/01/05 ~ 2023/01/12 #36

다음 스터디

학습 범위

정리 범위