MYSQL WHERE 조건 이해

묵시적 형변환 함정

서비스 쿼리를 작성하다 보면 이상은 없는데 성능이 이상하게 좋지 않을 때가 있다.

명시적 쿼리로 보기힘들다 (애플리케이션에서 변수를 바인딩하여 SQL 실행 등 여러 요인) 

 

묵시적 형변환이 발생하여 테이블을 처음부터 끝까지 전부 체크해야 하는 테이블 풀스캔에 따른 현상을 방지하기 위한 팁에 대한 설명.

 

묵시적 형변환이란?

묵시적 형변환이란 조건절의 데이터 타입이 다를 때 우선순위가 높은 타입으로 형이 내부적으로 변환되는 것을 말한다.

정수 타입이 문자열 타입보다 우선순위가 높다. 만약 문자열과 정수값을 비교하는 쿼리가 들어오면 두개의 칼럼을 비교하고 우선순위가 높은 정수 타입으로 형변환 한다.

 

형 변환은 언제든지 발생할수 있지만... 만약 묵시적 형변환이 일어나는 대상이 인덱스라면??

조건절을 처리하기 위해 모든 데이터를 묵시적으로 형변환하기 때문에 테이블 풀스캔을 할 수밖에 없으며, 이 경우 성능이 심각하게 저하된다.

 

테스트 준비

 

테이블 생성

## 테이블 생성 

CREATE TABLE test (
i int unsigned NOT NULL auto_increment, j int unsigned NOT NULL,
s varchar(64) NOT NULL,
d datetime NOT NULL,
primary key(i)
);

## 인덱스 추가
ALTER TABLE test ADD key(j), ADD key(s), ADD key(d);

 

테스트할 데이터 만들기

INSERT INTO test (j, s, d) values
(
crc32(rand()),
crc32(rand())*12345,
date_add(now(), interval -crc32(rand())/5 second)
);

(아 래코드는 여러번 많이실행해서 데이터를 채우도록 하자)

INSERT INTO test (j, s, d) SELECT
crc32(rand()),
crc32(rand()) * 12345,
date_add(now(), interval -crc32(rand())/5 second)
FROM test;

테스트 할 쿼리를 무작위로 하나 가져온다.

SELECT * FROM test ORDER BY rand() LIMIT 1;

 

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

칼럼이 정수형인 상태에서 문자열로 조건을 주었을 때 쿼리 실행 계획의 결과다

EXPLAIN
SELECT * FROM test WHERE j = '3718433650';

각각의 타입에대한 정보는 이전 세션에서 확인 하자

https://redbinalgorithm.tistory.com/717

MySQL 쿼리 성능 진단

 

어쨋든 정수형 형변환되어서 실행 시 큰 문제없이 신속하게 결과를 얻는 것을 알 수 있다.

 

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

EXPLAIN
SELECT * FROM test
WHERE s = 17424528721665;

s 칼럼에 인덱스가 있으므로 일반적으로는 빠른 처리를 기대하겠지만 묵시적인 형변환이 발생하여 테이블 풀스캔이 발생한다.

지금은 단건이지만 천만건의 많은 데이터라면 성능저하가 일어난다.

 

파라미터 바인딩을 사용해서 문자열 칼럼에 정수형을 넣으면 소스 코드만 보고서는 묵시적 형변환이 발생한 위치를 파악하기 어렵다.

java에서 다음과 같이 호출한다면?

String sql = "SELECT * FROM test WHERE s = ?"; 
PreparedStatement pstmt = conn.prepareStatement(sql); 
pstmt.setInt(1, 10895559612495);

사실 이것저것 생각하기 싫다면 MySQL에서 무조건 문자열 조건으로 질의해도 큰 문제는 없다.

(하지만 오라클에서 PostgreSQL이관하거나 하면 문제가 생길 수 있음)

 

 

편리한 함수, 잘못쓰면??

 

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 칼럼 검색 시 DATE_FORMAT 함수를 사용하면 옵티마이저는 reg_date와 연관된 데이터 분포도를 알 수 없게 된다.

DATE_FORMAT 함수로 인해 변경될 결과값을 옵티마이저가 예상하지 못하기 때문이다. 물론 인덱스가 없는 경우에도 시스템적으로 부하가 있기는 마찬가지다. 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에 인덱스가 있거나 reg_date에 파티셔닝을 적용하면 빠르게 필요한 데이터를 접근하여 데이터를 가져온다.

 

함수를 쓰면 머릿속에 있는 내용을 쉽게 쿼리로 풀어쓸 수 있겠지만, 이를 DB가 잘 알아듣기 쉬운 언어로 조금만 변환해준다면 불필요한 시스템 부하는 발생하지 않는다. 사소한 부분부터 조금이라도 DB 입장에서 SQL을 작성하여 DB 성능을 올리자.

 

LIKE 검색을 아무 때나 써야 하나?

 

특정 단어가 들어 있는 데이터를 검색하기 위해 LIKE 검색을 사용하지만, LIKE 검색은 상당히 편리하지만 대용량 테이블인 경우에는 위험하다.

 

LIKE는 세가지가 있다.

[col LIKE 'abc%'] 인덱스 서칭 || 풀스캔

[col LIKE '%abc%'] 풀스캔

[col LIKE 'abc%'] 풀스캔

 

LIKE 검색 시 '%' 위치에 따라서는 칼럼에 해당 인덱스가 있을지라도 의미가 없을 수도 있다.

DB의 자료는 인덱스 키 값 순서로 정렬, 관리된다. 중간 또는 뒷부분부터 검색하면 인덱스의 의미가 없어진다.

 

해당 쿼리 빠르게 실행한다. 

EXPLAIN
SELECT * FROM test WHERE s LIKE '1311%';

아래 쿼리는 앞과 동일하지만 '1%'로 변경했을 뿐인데, 테이블 풀스캔으로 변경되었다

EXPLAIN
SELECT * FROM test WHERE s LIKE '1%';

 

이유가 무었일까? 대부분의 DBMS에 옵티마이저가 있다. 옵티마이저는 데이터 분포도를 따져서 내부적으로 20% 이상을 차지하므로, 인덱스를 읽고 다시 데이터 파일로 가는 것보다 처음부터 전체 데이터를 읽고 필요한 데이터를 선별하는 것이 더 빠르다고 옵티마이저가 판단한다.

 

인덱스는 데이터가 위치한 곳을 지칭한다. 인덱스도 데이터고 지칭하는 데이터로 찾아가려면 실 데이터에 대한 데이터를 다시 읽어야 하기 때문에 중복된 데이터 처리 비용보다는 테이블 풀스캔으로 접근하는 것이 훨씬 빠르다고 옵티마이저가 판단.

 

인덱스도 데이터라는 사실.

 

결론

1. LIKE 조건이 '검색어%'와 같이 검색어가 앞 단에 있다면 데이터 분포도를 따져서 수행한다.(20% 주의)

2. LIKE 조건이 '%검색어'와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적극 활용하여 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.

 

인덱스는 필요한 데이터를 지칭한다는 점에서 데이터에 접근하기 위한 효율적 요소인 것은 분명하다. 그러나 인덱스 또한 메모리를 차지하고, 디스크를 소모하며, CPU 연산이 필요한 데이터라는 것을 기억해야한다. 

 

DB에서 처리하는 데이터 범위를 최대한 줄이는 것이 성능 최적화의 가장 기본적인 요소이다.