시작
where 조건문을 그냥 쓰지말자. 알고 쓰자.
묵시적 형변환 함정에 빠지지 말기
묵시적 형변환. 조건절의 데이터 타입이 다를 때, 우선 순위가 높은 타입으로 형이 내부적으로 변환되는 것을 말하는 데 이게 개발자들이 모르면서 그냥 넘어가는 경우가 많다. 묵시적으로 변환을 해주기 때문에.
예를 들어, 문자열과 정수값을 비교한다고 하면 당연히 두 타입을 똑같은 형으로 변환을 시키고 비교를 해야하므로 문자열이 정수로 또는 정수가 문자열로 변환이 되어야한다. 현재 우선순위로는 정수 타입이 문자열보다 높으므로 자연스럽게 문자열 타입이 정수 타입으로 묵시적으로 변환이 된다.
우리는 인덱스라는 것을 쓰면서 풀스캔을 방지한다. '친절한 SQL 튜닝'이라는 책에서도 나왔지만 이 인덱스에 대한 필드가 형변환이 내부적으로 일어나면 범위를 좁히지 못하고 풀스캔을 하는 경우가 발생한다.
자. 생각해보자. 그럼 우선 순위가 정수 타입이 높으므로 문자열을 넣는다 해도 정수형이 필요하면 묵시적으로 정수형으로 형변환이 되기 때문에 귀찮음이 많은 개발자라면 '문자열'을 넣어주면 괜찮다.
다만, whitespace라던가 예기치못한 상황이 올수도 있으므로 생각하며 형에 맞춰서 질의를 해주자.
( + 성능 테스트하는 법 : 28 page 참고 )
편리한 함수, 잘못 쓰면 성능에 독이 된다
각 DBMS는 내장된 함수를 제공하고 사용자 정의함수도 제공하기에 빈번하게 사용한다. 하지만 이러한 함수가 시스템 부하를 야기한다. 우리의 DB튜닝 목표 중 하나 DBMS가 이해하기 쉽게 SQL을 작성하는 것도 포함이 된다.
우리는 적절한 인덱스, 테이블 파티셔닝을 통해 데이터를 빨리 찾을 수 있다는 팩트를 알고 있다.
예를 들어보자. DATE_FORMAT 이라는 내장 함수가 있다. 그리고 우리는 cumlative_time 이라는 필드로 인덱스를 걸었다. 과연, 이 DATE_FORMAT을 쓰면 옵티마이저가 cumlative_time의 데이터 분포도를 알 수 있을까? 알지 못한다. 이게 문제다. DATE_FORMAT 내장 함수로 인해 변경될 결과값을 옵티마이저가 예상하지 못하기 때문이다.( 데이터 분포도 )
또한, 이러한 상황이 아니더라도 이 내장 함수를 한 쿼리에 여러번 사용하다보면 시스템적으로 부하가 일어난다 . ( 직관적으로 생각했을 때, 느낌이 오죵? )
즉, 나의 결론을 말하자면 이 함수는 고급언어라고 생각한다. 무슨 말이냐면 DBMS가 이해하기 쉬운 언어가 아니라 개발자, 사용자가 사용하기 쉬운 언어라는 것이다. 우리는 성능이 좋게 튜닝하는 것이 목적이기에 우리가 보기 좋은 것보다는 DBMS가 읽기 쉬운 언어로 쿼리를 짜야한다. DB 입장에서 SQL을 작성하자.
LIKE 검색을 아무 때나 써야 하나?
데이터를 조회할 때 특정 단어가 들어 있는 데이터를 검색하기 위해 LIKE 를 쓴다. 우선 팩트부터 말하자면 대용량 테이블일 경우 위험.
또 정말 중요한 팩트부터 얘기하자면 인덱스는 데이터가 위치한 곳을 지칭한다. ( + 커버링, 클러스터 같은 경우 지칭을 떠나 그 자체로 데이터가 됨) 또한, 실 데이터에 대한 데이터를 다시 읽어야 하기 때문에 중복된 데이터 처리 비용보다는 테이블 풀스캔으로 접근하는 것이 훨씬 빠르다고 옵티마이저가 상황에 따라 판단한다.
또한 인덱스는 메모리도 차지하며 디스크를 소모하고 CPU 연산이 필요한 데이터이다. 즉, 장점만 있는 것이 아니라는 것을 알자.
중요 ! 이 LIKE 같은 경우도 만약 필드가 인덱스에 걸려있다면, '%'위치에 따라서 의미가 있을 수도 또는 없을 수도 있다. 즉, 아이디인 "ggomjae" 를 예를 들어보면 이 아이디 필드가 "ggom%"일 경우에는 인덱스를 탄다. 왜? g 부터 데이터의 시작점을 찾으니까. 근데 만약 "%omjae" 일 경우에는 아무리 아이디 필드가 인덱스여도 타지 않는다. 그 기준점이 모호하기 때문이다. 즉, 인덱스임에도 불구하고 풀스캔을 때린다.
여기까지는 익히 알고 있었던 지식인데 책을 읽으면서 '아! ' 했던 부분이 아무리 앞부분이 살아있다고 하더라도 옵티마이저가 데이터 분포도를 확인했을 때, 전체 데이터의 20%이상이 분포되어있다고 판단했을 때, 인덱스가 아닌 풀스캔을 때려버린다.
이유는 20% 이상이 넘어가면 인덱스를 읽고 다시 데이터 파일로 갈게 아니라 처음 부터 데이터 파일을 읽는게 빠르다고 생각하기 때문이다.
책의 LIKE 부분 마지막 챕터에 중요한 결론이 나오는 데 정리하자면, LIKE 조건이 '검색어%'와 같은 경우 데이터 분포도에 따라 옵티마이저가 판단하며 보편적으로 20%가 넘어가면 풀스캔을 때린다. 만약, 앞부분이 아니라 뒷부분 '%검색어'와 같다면 LIKE가 아닌 다른 조건도 더 붙여서 데이터 범위를 최대한 줄이도록 해보자.
마치며
이 책은 나에게 굉장히 좋은 점이다. '친절한 SQL 튜닝'을 아직 다 읽지는 않았지만 어느 정도 읽은 상태에서 이 책을 보니까 굉장히 조각처럼 있는 지식들이 한곳에 뭉쳐서 정확한 지식이 되는 느낌이다. 조금의 물질적인 것과 접착제같은 느낌이랄까. 즉, DBMS에 대해서 처음 공부를 하는 사람이면 좋다고 생각하지 못할 것이다. 얇은 만큼 설명이 굉장히 축소되어있기 때문이다. 무튼, 이번 챕터도 굉장히 얻어가는 게 많은 챕터였다. 좋다!!
참고 : 실무 예제로 다가가는 MySQL 쿼리작성 MySQL 퍼포먼스 최적화 - 성동찬, 한빛미디어