DB

[MySQL]MySQL 벼락치기(10) - 쿼리최적화(1)

강씨아저씨 2018. 10. 14. 16:00

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

이번에는 쿼리최적화에 대해서 알아볼 예정이고 오늘은 그중에서도 MySQL 연산자와 내장함수를 사용할때의 알아두면 좋은 정보에 대해서 알아볼 예정이다. 

숫자

숫자 값과 문자열 값을 비교할때는 한 가지 주의할 사항이 있다. 서로 다른 타입으로 WHERE 조건 비교가 수행되는 다음 쿼리를 잠깐살펴보자. 

SELECT * FROM tab_test WHERE number_column = '10001'
SELECT * FROM tab_test WHERE string_column = 10001;

위 쿼리와 같이 두 비교 대상이 문자열과 숫자 타입으로 다를때는 자동으로 타입의 변환이 발생한다. MySQL은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시 하므로 문자열 값을 숫자 값으로 변환후 비교를 수행한다. 
첫번째 쿼리는 주어진 상수값을 숫자로 변환하는데, 이때는 상수값 하나만 변환(10001)하므로 성능과 관련된 문제가 발생하지 않는다. 
두번째 쿼리는 주어진 상수값이 숫자 값인데 비교되는 컬럼은 문자열 컬럼이다. 이때 MYSQL은 문자열 컬럼을 숫자로 변환해서 비교한다. 즉, string_column 컬럼의 모든 문자열 값을 숫자로 변환해서 비교를 수행하므로 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 필드를 int로 바꾸면서 쿼리를 실행하기때문에 기대대로 인덱스를 사용하지 못하고 전체 데이터를 다 읽는 테이블 풀 스캔을 실행하게 된다. ( https://idea-sketch.tistory.com/48 )

동등비교(<=> , = )

MySQL 에서는 동등비교를 위해 <=> 연산자도 제공한다. <=> 연산자는 = 연산자와 같으며 부가적으로 NULL 값에 대한 비교까지 수행한다. MySQL 에서는 이 연산자를 NULL-Safe 비교 연산자라고 하는데 = 연산자와 <=> 의 차이를 알아보자. 

위 예제 결과에서도 알 수 있듯이 NULL 은 IS NULL 연산자 이외에는 비교할 방법이 없다. 그래서 첫번째 쿼리에서 한쪽이 NULL 이면 비교 결과도 NULL 로 반환한 것이다. 하지만 NULL-Safe 비교 연산자를 이용해 비교한 결과를 보면 양쪽 비교 대상 모두 NULL 이라면 TRUE를 반환하고, 한쪽만 NULL 이라면 FALSE 를 반환한다. 

현재시각 조회(NOW, SYSDATE)

NOW, SYSDATE 두 함수 모두 현재의 시간을 반환하는 함수로서 같은 기능을 수행한다. 하지만 NOW 와 SYSDATE 함수는 작동 방식에서 큰 차이가 있다. NOW는 하나의 SQL에서 같은 값을 가지지만 SYSDATE는 하나의 SQ내에서도 호출되는 시점에 따라 결과 값이 달라진다. 

NOW 함수를 사용한 첫 번째 예제에서는 두 번의 NOW 함수 결과가 같은 값을 반환했다.
하지만 두번째 예제에서 사용된 SYSDATE 함수는 SLEEP 함수의 대기 시간인 2초 동안의 차이가 있음을 알 수 있다. 

SYSDATE 함수는 이러한 특성 탓에 두 가지 잠재적인 문제가 있다.

  1. SYSDATE 함수가 사용된 SQL은 복제가 구축된 MySQL의 슬레이브에서 안정적으로 복제 되지 못한다. 
  2. 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();

위의 예제에서 보면 첫번째 쿼리는 code_serial_num 과 update_at 컬럼 모두 적절히 인덱스를 사용했기 때문에 인덱스의 전체 길이인 11바이트를 모두 사용했지만 두번째 쿼리는 code_serial_num 컬럼만 인덱스를 사용했기 때문에 인덱스 중에서 code_serial_num 에 속하는 5바이트만 동등조건 검색으로 사용되었다. ( https://idea-sketch.tistory.com/48 )

SYSDATE 함수는 위에서도 언급했듯이 함수가 호출될때마다 다른 값을 반환하므로 사실은 상수가 아니다. 그래서 인덱스를 스캔할 때도 매번 비교되는 레코드마다 함수를 실행한다. 하지만 NOW 함수는 쿼리가 실행되는 시점에서 실행되고 값을 할당받아서 그 값을 SQL 문장의 모든 부분에서 사용하게 되기 때문에 쿼리가 1시간 동안 실행되더라도 실행되는 위치나 시점에 관계없이 항상 같은 값을 보장할 수 있다. 

값의 비교와 대체(CASE WHEN ... THEN ... END)

CASE WHEN 절이 일치하는 경우에만 THEN 이하의 표현식을 실행된다는 점을 이용하면 상황에 따라 쿼리를 조금더 효율적으로 실행할 수 있다. 

다음 쿼리는 Marketing 부서에 소속된 적이 있는 모든 사원의 가장 최근 급여를 조회하는 쿼리다. 이 쿼리는 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 eemp_no = de.emp_no
  AND de.dept_no = 'd001'

그런데 만약 성별이 여자인 경우에만 최종급여 정보가 필요하고 남자이면 그냥 이름만 필요한 경우를 한번 생각해보자. 물론 이 쿼리를 그대로 사용하고 남자인 경우에만 last_salary 컬럼을 그냥 버리면 된다. 하지만 남자인 경우에는 salaries 테이블을 조회할 필요가 없는데 서브 쿼리는 실행되므로 불필요한 작업을 하는 것이다. 이러한 불필요한 작업을 제거하기 위해 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 eemp_no = de.emp_no
  AND de.dept_no = 'd001'

이렇게 쿼리를 변경하면 여자인 경우에만 서브 쿼리가 실행될 것이다. 물론 그 덕분에 남자 사원의 수 만큼 서브 쿼리의 실행 횟수를 줄일 수 있게 된다. 

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~