날짜검색 시 주의사항 -- MySQL




대용량의 테이블에서 날짜를 통한 검색을 할 때,

(reg_date는 datetime형이고, 인덱스가 걸려있다고 가정한다.)

SELECT *
FROM    Test
WHERE   date_format(reg_date, '%Y-%m-%d') BETWEEN '2013-01-01' AND '2013-01-31';

SELECT *
FROM    Test
WHERE   date(reg_date) BETWEEN '2013-01-01' AND '2013-01-31';



위와 같이 쿼리를 하면 데이터가 늘어날수록 부하가 걸리는 쿼리가 된다.

- WHERE절의 왼쪽은 가공하지 않는다. (인덱스를 타지 않음)
- BETWEEN 절도 함수이므로 함수는 되도록 쓰지 않는다. 

댓글에도 썼지만 미니어스님의 얘기를 듣고 정정합니다.
BETWEEN절도 댓글의 예처럼 키워드여서 내부에서 약간의 공수가 있는 것 같습니다.


날짜를 검색할 때 올바른 검색방법은 아래와 같다.

SELECT *
FROM    Test
WHERE reg_date >= '2013-01-01'
AND     reg_date < '2013-02-01'


검색속도는 상당히 차이가 난다.

덧글

  • 궁그매 2016/08/10 17:58 # 삭제 답글

    제가 하기엔 동일한거같은데 ㅠㅠ 혹시 날짜에 index가 있을때를 말씀하시는건가요

    어떤 분은 between을 하면 index역할을 해준다고 하는데....

    다들 말이 다르니 ㅠㅠㅠㅠㅠㅠ
  • 어린왕자 2016/08/12 00:50 # 답글

    우선 dbms마다 차이가 있을 순 있을 수 있어요. 날짜 필드에 인덱스는 걸려있고 경험상 mysql에서는 많은 속도 차이가 생깁니다. 데이터양이 많을 경우가 아니라면 비슷하겠죠 ㅎ
  • 어린왕자 2016/08/12 00:52 # 답글

    - WHERE절의 왼쪽은 가공하지 않는다. (인덱스를 타지 않음)
    - BETWEEN 절도 함수이므로 함수는 되도록 쓰지 않는다.

    이것이 중요한 포인트입니다.
  • 궁그매 2016/08/17 13:57 # 삭제 답글

    그러면, datetime에 index가 있을때,
    조회할때 where문의 조건값을 'yyyy-MM-dd'으로 하는것과
    'yyyy-MM-dd HH:mm:ss'로 조회할때의 속도가 다를까요??
  • 어린왕자 2016/08/17 16:03 # 답글

    오른쪽 인수에 가공은 무리 없습니다. 인덱스가 걸려 있으므로 포멧은 크게 신경안써도 될 것 같습니다.
  • 궁그매 2016/08/23 14:36 # 삭제 답글

    지금 2000만건의 datetime에 index넣고 조회해보니 속도가같은 느낌이네요ㅎㅎ..ㅠㅠ
    mysql 버전이 낮아서 그런가아..ㅠㅠ흑흑


    친절한 답변 감사합니다^^




  • 어린왕자 2016/08/23 22:49 # 답글

    천만 이상이면 인덱스에 의한 차이가 실감나던데 쿼리가 잘되어 있으신 건지 ^^ 경험상으로 말씀드린 것이니 추후 차이가 나면 고려해보세요.
  • 궁그매 2016/09/07 15:58 # 삭제 답글

    쿼리가 잘못됐었네요ㅎㅎ..

    친절한 답변 감사합니다^^
  • 어린왕자 2016/09/07 20:36 # 답글

    그랬군요 ㅎ
    즐거운 코딩하시길 바랍니다 ㅎ
  • 재키 2016/09/21 10:32 # 삭제 답글

    과장님~~ 후배 재키입니다 ㅋㅋ
    구글 검색하다가 떠서 글 보다 들러요~~~
  • 어린왕자 2016/09/23 21:21 # 답글

    오 잘 다니고 있나요 ㅎ
  • 미니어스 2016/12/01 19:55 # 삭제 답글

    BETWEEN이 함수라고 어디서 말하던가요?
    dev.mysql.com에서는 최상의 결과를 얻기 위해 DATA, DATETIME 등과 같이 BETWEEN을 쓰라고 매뉴얼에 명시 시켜놨는데 말이죠?

    블로그의 정보를 믿지 못하니 사람들이 점점 stack over flow 같은 외국 사이트로 가게 되고,
    초급 개발자들이나 블로그 내용 긁어다가 사용했다가 회사 짤리고 그러는게지요 ㅡㅡ;

    http://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

    For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

    http://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html
    http://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-single-part
  • 어린왕자 2016/12/02 23:03 # 답글

    미니어스님,

    [1] WHERE절 왼쪽 가공

    SELECT *
    FROM Test
    WHERE date(reg_date) BETWEEN '2013-01-01' AND '2013-01-31';

    <WHERE절의 왼쪽은 가공하지 않는다. (인덱스를 타지 않음)>

    이것이 가장 큰 비용을 발생하며 이 글의 취지가 바로 이것이므로 오해 없길 바랍니다.


    [2] 범위검색과 BETWEEN

    BETWEEN과 범위검색 시 이유는 정확히 알 수 없으나, 레코드가 많은 경우 일수록 범위검색이 약간 이득 같습니다.
    이것은 본문에서도 얘기했지만 우선 경험적으로 MYSQL의 얘기입니다.

    MYSQL에서 범위검색과 BETWEEN의 실행계획이 같으므로 함수(사용자함수가 아닌 내부 키워드 ㅎ)를 다시 변경하는 약간의 차이가 나지 않을까 생각이 듭니다.

    비슷한 경험을 테스트하신 분이 계셔서 링크 남겨봅니다.

    http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=169726

    "간단한 테스트를 좀 해봤는데, [BETWEEN]과 [>= AND <=]는 내부적으로 동일한 방식으로
    옵티마이징되지 않는 것 같습니다.

    MySQL의 Optimizer에 의해서 최적화 (변환)된 쿼리를 확인하는 방법은 아래 URL참조하세요.
    http://intomysql.blogspot.com/2010/12/explain-extended.html

    천만건이 들어있는 테이블에 아래와 같이 천만건 전부를 카운팅하는 쿼리를 작성해서 실행해봤습니다.

    select sql_no_cache count(*) from sbtest where id>=0 and id<=83389129;
    select sql_no_cache count(*) from sbtest where id between 0 and 83389129;

    이 두 쿼리의 실행결과 10번 정도를 평균으로 계산해봤는데,

    [x BETWEEN $1 AND $2] 은 평균 4.118333 초가 소요되었으며,
    [x>=$1 AND x<=$2] 는 평균 3.442857 초가 소요되었습니다.

    두개 쿼리의 실행계획이 동일했고, 모든 데이터가 Buffer pool 에 cache(Warming up)된
    상태에서 실행했기 때문에 상태는 거의 동일했다고 보여집니다.

    이 두 조건의 처리 속도가 다르게 나왔다는 것은
    [x BETWEEN $1 AND $2] 방식의 비교가 [x>=$1 AND x<=$2] 방식의 비교보다
    CPU cycle을 적게 소모했다는 결과로 보여집니다.

    지금 제가 실행했던 테스트에서는 인덱스의 접근 방식이나 실행계획의 차이로
    발생한 성능 차이는 아닌 것으로 보이며, 단순히 두 비교 조작의 효율성 차이인 것으로 판단됩니다.

    그럼 도움이 되셨기를.."


    PS) 날짜만의 검색이라면 범위검색, BETWEEN 선택의 문제라기보단 아래처럼 하는게 더 이득이네요.

    http://tokyogoose.tistory.com/304
  • gogo 2020/02/06 09:29 # 삭제 답글

    결론, between은 사람이 읽기 좋게 하기 위한 함수
댓글 입력 영역