MySQL 퍼포먼스 최적화를 읽고(3) - WHERE 조건 이해

묵시적 형변환의 함정

---

묵시적 형변환이란?

조건절의 데이터 타입이 다를 때 우선순위가 높은 타입으로 타입이 내부적으로 변환되는 것을 말하며, 정수 타입이 문자열 타입보다 우선순위가 높다.

 

문자열과 정수값을 비교하는 쿼리가 있다면 우선순위가 낮은 문자열은 자연스럽게 정수 타입으로 형변환되어 처리된다.

 

형변환은 언제든지 발생할 수 있다. 하지만 묵시적 형변환이 발생하는 대상이 인덱스 필드라면 조건절을 처리하기 위해 해당 필드(칼럼)의 모든 데이터를 묵시적으로 형변환하기 때문에 인덱스를 사용하지 못하고, 테이블 풀 스캔을 할 수밖에 없다.

테스트 테이블 생성 및 데이터 입력

CREATE TABLE test (
    row_id int unsigned NOT NULL auto_increment,
    integer_field int unsigned NOT NULL,
    character_field varchar(64) NOT NULL,
    datetime_field datetime NOT NULL,
    primary key(row_id)
);

ALTER TABLE test ADD key(integer_field), ADD key(character_field), ADD key(datetime_field);

위와 같은 구조를 가진 테이블을 생성하고 각각의 필드에 인덱스를 설정해주었으며, 임의의 데이터 약 13만 건을 저장해 주었다.

 

정수형 칼럼을 문자열 조건으로 검색

SELECT * FROM test WHERE integer_field = '2250300062';

정수형 칼럼(integer_field)에 대한 조건으로 문자열 데이터를 넣어주었지만, 정수형이 우선순위가 높기 때문에 문자열이 묵시적으로 정수형으로 형변환되었고, 정상적으로 integer_field의 인덱스가 활용되었다.

문자열 칼럼을 정수형 조건으로 검색

SELECT * FROM test WHERE character_field = 34578451129965;

문자열 칼럼(character_field)에 인덱스가 설정되어 있지만, 테이블 풀 스캔이 발생했다.

 

이는 정수형이 우선순위가 더 높기 때문에 character_field 칼럼의 모든 데이터들이 정수형으로 묵시적 형변환 되었다. 기존에 문자열 데이터를 기준으로 인덱스가 걸려 있던 칼럼이기 때문에 정수형으로 형변환 되어 사용할 수 없게 되었다.

 

이것저것 생각하기 싫다면 MySQL에서는 무조건 문자열 조건으로 질의해도 우선순위에 따라 정수형으로 묵시적 형변환되기 때문에 문제없다. 하지만 다른 DBMS로 데이터를 이관할 상황이 온다면 문제가 생길 수 있기 때문에 조건절에서는 칼럼 타입에 맞춰서 질의하기를 강권한다.

 

함수의 함정

---

대부분의 DBMS에서는 함수 기능을 제공한다. 함수는 복잡한 연산을 직관적이고 간편하게 처리할 수 있게 하지만, 잘못 쓰면 불필요한 시스템 부하를 야기할 수 있다.

 

SELECT userid, count(*) AS cnt
FROM user_access_log
WHERE 
    DATE_FORMAT(reg_date, '%Y%m%d') = '20120818'
    AND DATE_FORMAT(reg_date, '%H') >= '18'
    AND DATE_FORMAT(reg_date, '%H') < '21'
GROUP BY userid;

위 쿼리는 특정 날짜, 특정 시간에 사용자별 접속 통계를 추출하는 쿼리 예시이다.

 

reg_date에 인덱스가 설정돼 있고, 테이블 파티셔닝으로 인해 데이터가 적절하게 분포되어 있을지라도 해당 쿼리는 테이블 풀 스캔을 수행하게 된다.

 

reg_date 칼럼 검색 시 DATE_FORMAT 함수를 사용하면 옵티마이저는 reg_date와 연관된 데이터 분포도를 알 수 없게 되기 때문이다. 즉, DATE_FORMAT 함수로 인해 변경된 결괏값을 옵티마이저는 예상하지 못한다.

 

SELECT userid, count(*) AS cnt
FROM user_access_log
WHERE 
    reg_date >= '2012-08-18 18:00:00'
    AND reg_date < '2012-08-18 21:00:00'
GROUP BY userid;

불필요한 함수를 없애고, 원하는 시간대에 해당하는 값만 가져오도록 쿼리를 재작성했다. reg_date의 인덱스를 통해 빠르게 데이터를 가져올 수 있다.

 

LIKE 검색의 함정

---

LIKE 검색은 % 문자 위치에 따라 다르게 수행되며, 대용량 테이블에서 LIKE 조건은 위험할 수 있다. 

 

 

인덱스는 위 그림과 같이 인덱스 키 값 순서로 정렬되어 관리되고 있기 때문에 단어의 중간 부분 또는 뒷부분부터 검색하면 인덱스를 활용하지 못한다.

 

%가 조건 뒤에 오는 경우

SELECT * FROM test WHERE character_field LIKE '345%';

+--+-----+-----+---------------+-------+----+---------------------+
|id|table|type |key            |key_len|rows|Extra                |
+--+-----+-----+---------------+-------+----+---------------------+
|1 |test |range|character_field|258    |259 |Using index condition|
+--+-----+-----+---------------+-------+----+---------------------+

 

%가 조건 앞에 오는 경우

SELECT * FROM test WHERE character_field LIKE '%345';

+--+-----+----+----+-------+------+-----------+
|id|table|type|key |key_len|rows  |Extra      |
+--+-----+----+----+-------+------+-----------+
|1 |test |ALL |null|null   |130940|Using where|
+--+-----+----+----+-------+------+-----------+

 

%가 조건 앞뒤에 오는 경우

SELECT * FROM test WHERE character_field LIKE '3%';

+--+-----+----+----+-------+------+-----------+
|id|table|type|key |key_len|rows  |Extra      |
+--+-----+----+----+-------+------+-----------+
|1 |test |ALL |null|null   |130940|Using where|
+--+-----+----+----+-------+------+-----------+

 

% 위치에 따른 쿼리 실행 계획을 봤을 때 %가 조건 뒤에 오는 경우, 즉 단어의 앞부분부터 검색했을 때 인덱스를 활용하는 알 수 있다. 그렇다면 조건 뒤에 %를 붙이면 무조건 인덱스를 잘 활용할 수 있는 걸까?

 

SELECT * FROM test WHERE character_field LIKE '3%';

+--+-----+----+----+-------+------+-----------+
|id|table|type|key |key_len|rows  |Extra      |
+--+-----+----+----+-------+------+-----------+
|1 |test |ALL |null|null   |130940|Using where|
+--+-----+----+----+-------+------+-----------+

동일하게 LIKE를 통해 3으로 시작되는 데이터를 찾기 위한 쿼리를 실행했지만, 테이블 풀 스캔이 실행되었다. 이유가 무엇일까?

 

대부분의 DMBS는 옵티마이저가 있고, 옵티마이저는 데이터 분포도를 따져서 내부적으로 SQL를 최적화한다.

 

SELECT COUNT(*) FROM test; -- 131072
SELECT COUNT(*) FROM test WHERE character_field LIKE '3%'; -- 27501

COUNT 쿼리 결과 3으로 시작하는 데이터 비율이 전체의 20% 이상 차지한다.

 

인덱스를 읽고 다시 데이터 파일로 가는 것보다, 바로 데이터 파일을 읽고 필요한 데이터를 선별하는 것이 더 빠르다고 옵티마이저가 판단했기 때문에 테이블 풀 스캔을 수행한 것이다.

 

이처럼 LIKE 조건이 '검색어%' 라면 데이터 분포도를 따져서 인덱스를 사용할지 테이블 풀 스캔을 할 지 결정한다. 만약 LIKE 조건을 ‘%검색어’, ‘%검색어%’ 형태로 반드시 수행해야 한다면 다른 조건으로 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.